Snowflake Bulk Loader Specify File Format

[MattBenj]

I am using the output tool to bulkload data into an existing Snowflake table. Is there a way to specify which file format the load should use? I have tried adding FILE_FORMAT = to the string, but I can't tell if it is doing anything. If I use a file name that doesn't exist it doesn't throw an error so 

snowbl:DSN=Snowflake;UID=xxx;PWD=xxx;Staging=local;Method=user|||DW.STAGING."Opportunity_test"

Designer 2023.1

[apathetichell]

I could be off here - but my take is FILE_FORMAT would be used for Snowpipe or loading data from an external storage integration or other file based integration.

Snowflake bulk loader is writing your data directly to your Snowflake table - not to a file in a storage integration. FILE_FORMAT should be irrelevant -> your data schema is controlled by Snowflake and Alteryx MUST match it or create it in a net new situation. If you are trying to write to an S3 or external storage integration -> you would write to a file type - and Snowflake would control ingestion. Does that sound reasonable?

[MattBenj]

I think you are referring to the PUT portion of the data load, but file formats are part of the COPY INTO step. I guess Alteryx must know how it loads the file into the user stage and know which file format it needs for the copy.

Based on this knowledge base article (Snowflake Bulk Loader - How Local Staging Works)

[apathetichell · Accepted solution]

The loading is done as csv -> the copy into uses the default csv file format. If you have created a new file format in snowflake named csv you could entirely mess things up. Specifying a secondary file_format parameter does not work and does not make sense to me. Alteryx expects you to want your formatting in Alteryx to match what you want in Snowflake. Note -> Alterys see the bulk loader as an ephemeral activity -> vs Databricks where the underlying csv/parquet files remain on the dbfs. For new tables -> I believe Alteryx 1) creates the table -> using the formats of your Alteryx data stream 2) loads the file 3) copies in the file.

If you are looking to load raw files -> send to snowflake and use your specific file format -> you should be loading the files from Alteryx to a stage and then running the snowpipe api (or python connector/or whatever).

[MattBenj]

After some testing, we discovered that too. We are actually doing this to move away from our snowpipe process and take advantage of a one tool solution.