Best practice for Snowflake & Alteryx

[knozawa]

Hello,

I have several questions about Snowflake connecting from Alteryx.

1. Bulk loader without primary key field

Would it be possible to upload data into snowflake using bulk loader that does not have primary key field?

Background: There is a primary key field with AUTOINCREMENT in snowflake

2. Insert new data into snowflake using bulk load

It seems like "Update: Insert if new" is not available for bulk load.  In that case, probably "Append existing" is the best way.  If there is any suggestions, please let me know!

3. Modify database by fill in value

What is the best way to modify data in snowflake?

Background:

There are some rows that are missing values.  I would like to modify/fill-in data in snowflake.   Would it be better deleting specific rows and append modified data?  OR modify data using sql statement in Alteryx output tool? OR clone original table --> create modified table --> replace table?

4. Normal input and output tool vs In-Database connection tools

What is the advantage/disadvantage using In-Database connection tools?

Sincerely,

knozawa

[knozawa]

@8088 

Yes, I use a regular output tool with SF bulk loader.

Thank you for the detailed info.
I reviewed my workflow and it seems like I ended up creating a recordID (primary key) in Alteryx rather than using an autoincrement in snowflake.  It's good to know that autoincrement works on ODBC connection.

Sincerely,

knozawa

[Ludokoelman]

A year and a half later. It's still pretty painful to get this to work. I was facing the same issue: wanting to use Snowflake default's value. It would be great if you could...

• specify the COPY INTO options
• just stage the file and run the copy into as a post-sql statement
• or not have Alteryx execute the rm @ statement that removes the stages files

Anyway, I had the same issue, needed to have Snowflake create the load timestamp ts_load for me, not Alteryx. So what I did:

1. Create the table manually (table1), including the default value statement (I did get caught by the fact that you can only do this in the CREATE TABLE statement, not in an ALTER TABLE statement)
2. Run the workflow in Alteryx, not including the field with the default value
3. Output to a separate temp table (table2) (which by the way Alteryx doesn't let you specify, does it, the table type?)
4. run a post-sql statement:

insert into table1 (select *, current_timestamp as ts_load from table2);
drop table table2;

• you could replace the current_timestamp with sequence.nextval or whatever

[LisaD]

Is my observation correct that when using Snowflake BULK load (Delete Data and Append), you must have all the columns and they must be in the exact order they are in Snowflake?

[cmcclellan]

@LisaD yes that's how Snowflake works.  There are exceptions (like autoincrement fields, etc), but on the whole "bulk load" means all fields in that field order.

That said, there's ways around it if you need, but that depends on what you're trying to do and how much access you have in the database.

[Ludokoelman]

In Alteryx you can do custom mapping (option 5 on the output tool) to overcome that. But basically yes.

In the Snowflake History you can see that the Alteryx Bulk Load tool performs 3 steps:

• PUT files into the stage
• COPY data INTO table
• RM (remove) files from the stage

In native Snowflake, you can use a specific file format for the COPY INTO statement, allowing:

ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE

But since Alteryx goes through all those steps by itself, you cannot use that option directly. 

So as @cmcclellan mentioned there are ways around this by for example having Alteryx just stage the files and then using a pre/post sql statement on a separate output tool to run a specific COPY INTO statement

[Samantha_Jayne]

Hi all, I randomly came across this page, not sure if you have seen this blog post but its all about writing effectively. I am sure you have by now figured this out but if not: Alteryx * Snowflake = Mindblown Speeds