Best practice for Snowflake & Alteryx

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

Tips and Tricks

Best Practices

Database Connection

Accepted answers

cmcclellan

Hopefully these answers will help 🙂

1) Yes, I'm pretty sure you can do this. In the file format you can specify that the PK field is not in the file and Snowflake should create the value. I can dig out some details from an old project if you need more info 🙂

2) I don't think Snowflake support UPSERT when loading. Usually I've uploaded to an empty table and used SQL to achieve the same thing

3) Depends on your situation. Snowflake isn't designed for single record maintenance, you can do it, but don't try it for a large number of records and expect good performance. It can take seconds to bulk load millions of rows, but hours to edit a thousand records individually. If you understand how Snowflake works then you can design a really fast workflow

4) The same as every other database. InDB means that the data stays in the database. It's VERY fast and efficient and if you're just transforming data (not loading or unloading) then InDB will make things really fast.

All comments

cmcclellan

Hopefully these answers will help 🙂

2) I don't think Snowflake support UPSERT when loading. Usually I've uploaded to an empty table and used SQL to achieve the same thing

knozawa

@cmcclellan,

Thank you very much for your detailed reply!

About the #1 question (auto increment PK using bulk loader), when I tried to upload data into snowflake without a primary key field using bulk loading, I've received following error message:

The COPY failed with error: Number of columns in file (2) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error

I've asked the same question on the stack overflow as well:https://stackoverflow.com/questions/60326474/autoincrement-primary-key-for-snowflake-bulk-loading/60337574#60337574

It seems like there is a way to work around the issue like you said. Do you know if we can achieve it using an output tool modifying the pre/post SQL or do we need to use snowsql and cmd tool?

Sincerely,

knozawa

steven4320555

In @cmcclellan 's point 3: "It can take seconds to bulk load millions of rows, but hours to edit a thousand records individually. If you understand how Snowflake works then you can design a really fast workflow"

This is a very interesting insight! Snowflake is optimised for bulk operations and analytics, not good for operation.

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:

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)
Run the workflow in Alteryx, not including the field with the default value
Output to a separate temp table (table2) (which by the way Alteryx doesn't let you specify, does it, the table type?)
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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335