Skip to main content

Writing/Updating to Panoply from Alteryx

Comments

2 comments

  • Alon Brody

    Thanks for the post Louisa.

    An update to anyone reading this:
    Louisa managed to make Alteryx write and update data in Panoply by simplifying the update process that Alteryx needed to perform in Panoply.

    Louisa, it would be great if you could add a general explanation on the solution.

    Thanks!
    Gal

    0
  • l.obrien

    Thanks Gal,

    We got to the bottom of this by identifying two things about the underlying architecture of Panoply, which relates to Redshift tables.

    1. Primary Keys are not the same kind of constraint as they are in other SQL deployments, in that they are informational rather than enforced as constraints.
    2. Redshift doesn’t support “Upsert” operations, wherein an update and an insert command are merged within the same query. The “Update; Insert if New”) output option.

    This means that Alteryx’s output connection set up like this will error. You can work around this in Alteryx Designer instead in a number of ways, e.g. a pattern involving a join and transpose tool to compare fields which have been edited in the workflow, which rows they belong to, and only updating those rows, adding new rows in a separate branch of the workflow. It essentially means that the work of enforcing the constraint of a primary key must be shifted to your workflow instead of the database itself.

    0

Please sign in to leave a comment.

Powered by Zendesk