Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Updating a Table In Snowflake- Work flow runs for ever and not updating.

VahidGoli
8 - Asteroid

Hi Experts,

Please let me give you some information about the situation:

1- I have created a database in Snowflake.

2- I have set up the ODBC connection to upload my data into Snowflake.

3- I have created a new table by using Alteryx, and I have uploaded my data into that table by using the Output tool.

4- I have set a Primary Key field by Altering the table inside the Snowflake, and it is confirmed when I use the input tool, and the yellow key indicates next to the field name.

 

Now the problem is when I want to update my table with new data after 2 hours (which will be less than 10 rows!), it takes forever, and the workflow does not stop.

I am using "Update: Insert if New" in the Output options selection. Am I doing something wrong? Would you please guide me with this?

 

Would you please let me know the best practice for updating my data in Snowflake by using Alteryx?

 

Thank you in advance for your time and help.

7 REPLIES 7
TrevorS
Alteryx Alumni (Retired)

Hello @VahidGoli 

Thank you for posting to the Community.

Are you still having issues with your connection?

If so, can you provide a copy of your workflow so the Community can see what your setup looks like?
Thanks!
TrevorS

Community Moderator
ArtApa
Alteryx
Alteryx

Hi @VahidGoli - I suggest you to download and review the Snowflake Starter Kit. It contains not only sample workflows, but also training videos on how to setup connections: https://www.alteryx.com/starter-kit/snowflake 

 

If you will still experience issues you may need to reach out to our support: support@alteryx.com 

VahidGoli
8 - Asteroid

Hi @TrevorS and @ArtApa ,

 

As I tried to explain in my original post, I have no problem connecting my workflows to Snowflake to retrieve data or uploading data into Snowflake. I can use both via ODBC or Bulk. I have watched all the available videos and read all the documents (including StarterKit).

 

My problem is how I can update my existing tables after initially loading my data into Snowflake? 

 

My tables in all my databases need to be updated daily (say adding 100 new records). Still, there is no option for an update in bulk, and the Update option in ODBC takes ages to update a table with existing 100k records as it seems it goes through all the records to see IF NEW INSERT...

 

Any idea how I can fix this issue?

 

Thank you in advance for your help.

jjaiston
5 - Atom

Hi VahidGoli,

 

I have exactly the same problem as you described, so if anyone else has a real solution I would also appreciate it. There is workaround though. At the bottom of this community post Treyson points out that:

 


The "Update;Insert if new" function in Alteryx is wicked slow. essentially what happens is that it is creating a merge/insert statement for each record.

He then says the solution is:

 

to write from Alteryx to a temp table and then run your own merge/insert once it's populated 

So I've extracted the existing dataset from Snowflake into Alteryx, then joined it with my workflow data and used a formula to combine all the records into the relevant fields, before writing back to Snowflake with a "Overwrite Table (Drop)" output like so:

 

jjaiston_0-1634124116151.png

 

 

It feels messy, but it runs in a matter of seconds for me vs the hours the "Update; Insert if new" output tool would run for.

VahidGoli
8 - Asteroid

Hi @jjaiston ,

Thanks for your reply. Actually, I have been doing that since then, but the issue is it will increase your Snowflake cost, as you are calling your table and you need to use your warehouse.

 

Thanks for your reply.

VahidGoli
8 - Asteroid

@jjaiston , just another tip, make sure your table is not Permanent if you drop that every time, as it will increase your storage cost significantly. The reason is Snowflake needs to keep the dropped table for the time you want to backdate the change. So, if you drop the table so often, it's better to be Temporary.

 

Cheers,

Vahid

tareque
6 - Meteoroid

Bumping this question.
Just wondering if any progress has been made with just updating a small number of rows using the Snowflake ODBC quicker.

Labels