Snowflake - Upload File to Table from Shipyard

Overview

Upload one or more CSV files to any table in Snowflake. With the file data, you can:

• Append Data - Add the contents of your file to the end of the table.
• Replace Data - Drop the existing table and create a new table that contains the entire contents of your file.
• Add Data Only if Table is Empty - Add data to the table if no data exists.

Column names are inferred from the header row of your CSV file. If the table already exists, the header values are matched to the table column names. The header names must match the existing column names in your table, case insensitive. If a header name doesn't match, only the data associated with that specific column will not be uploaded.

Column names and table names are automatically made into upper case values, a standard for Snowflake.

Data is inserted into the table by PUTing the file on Snowflake temporary staging for the named table, running a COPY INTO function, then purging the file from Snowflake staging. If there are any errors with this method, we revert to a slow but consistent method that creates INSERT statements for 10,000 rows at a time.

In all instances, if the table name does not already exist, a new table will be created with datatypes inferred from the CSV contents.

Note: This Vessel cannot be used to upload a local file from your computer.

Recommended Setup:

1. A Vessel built with this Blueprint should typically run after a Vessel that either downloads a file to Shipyard or generates a file with code.

Variables

Name	Reference	Type	Required	Default	Options	Description
Username	SNOWFLAKE_USERNAME	Alphanumeric	✅	-	-
Password	SNOWFLAKE_PASSWORD	Password	✅	-	-	Password for the provided username
Account Name	SNOWFLAKE_ACCOUNT	Alphanumeric	✅	-	-
Warehouse	SNOWFLAKE_WAREHOUSE	Alphanumeric	➖	-	-
Database	SNOWFLAKE_DATABASE	Alphanumeric	✅	-	-
Schema	SNOWFLAKE_SCHEMA	Alphanumeric	➖	-	-
Shipyard Folder Name	SNOWFLAKE_SOURCE_FOLDER_NAME	Alphanumeric	➖	-	-	Folder where the file to upload can be found. Leaving blank will search in the current working directory.
Shipyard File Name Match Type	SNOWFLAKE_SOURCE_FILE_NAME_MATCH_TYPE	Select	✅	exact_match	Exact Match: exact_match Regex Match: regex_match	Determines if the text in "File Name" will match to one or multiple files.
Shipyard File Name	SNOWFLAKE_SOURCE_FILE_NAME	Alphanumeric	✅	-	-	Name of the file to upload to the specified table
Table Name	SNOWFLAKE_TABLE_NAME	Alphanumeric	✅	-	-	Name of the table where you want data inserted
Insertion Method	SNOWFLAKE_INSERT_METHOD	Select	✅	append	Append Data: append Replace Data: replace Add Data Only if Table is Empty: fail	Determines how the data in your file will be added to the table
Snowflake Data Types	SNOWFLAKE_DATA_TYPES	Alphanumeric	➖	-	-	The option to declare the Snowflake datatypes. The input needs to be similar to a list of lists, which will be used to form a CREATE OR REPLACE TABLE statement. For more information on Snowflake data types, visit https://docs.snowflake.com/en/sql-reference/intro-summary-data-types.html

YAML

Below is the YAML template for this Blueprint and can be used in the Fleet YAML Editor.

source:
  blueprint: Snowflake - Upload CSV to Table
  inputs:
    SNOWFLAKE_USERNAME: null ## REQUIRED
    SNOWFLAKE_PASSWORD: null ## REQUIRED
    SNOWFLAKE_ACCOUNT: null ## REQUIRED
    SNOWFLAKE_WAREHOUSE: null 
    SNOWFLAKE_DATABASE: null ## REQUIRED
    SNOWFLAKE_SCHEMA: null 
    SNOWFLAKE_SOURCE_FOLDER_NAME: null 
    SNOWFLAKE_SOURCE_FILE_NAME_MATCH_TYPE: exact_match ## REQUIRED
    SNOWFLAKE_SOURCE_FILE_NAME: null ## REQUIRED
    SNOWFLAKE_TABLE_NAME: null ## REQUIRED
    SNOWFLAKE_INSERT_METHOD: append ## REQUIRED
    SNOWFLAKE_DATA_TYPES: null 
  type: BLUEPRINT
guardrails:
  retry_count: 1
  retry_wait: 0s
  runtime_cutoff: 1h0m0s
  exclude_exit_code_ranges:
    - "200-207"