How to Load Data into Snowflake: Simple Steps and Examples
To load data into Snowflake, use the
COPY INTO command to copy data from a stage (like an internal or external storage) into a Snowflake table. First, prepare your data file and stage it, then run COPY INTO your_table FROM @your_stage to load the data.Syntax
The basic syntax to load data into Snowflake is:
- COPY INTO <table_name>: Specifies the target table to load data into.
- FROM <stage_location>: Specifies the data source location, which can be an internal or external stage.
- FILE_FORMAT = (TYPE = <format>): Defines the format of the data file, such as CSV or JSON.
- ON_ERROR = <action>: Defines what to do if errors occur during loading (e.g., skip file, continue, abort).
sql
COPY INTO my_table FROM @my_stage/data.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1) ON_ERROR = 'CONTINUE';
Example
This example shows how to load a CSV file from an internal stage into a Snowflake table named customers. It assumes the file is already uploaded to the stage @my_stage.
sql
CREATE OR REPLACE TABLE customers ( id INT, name STRING, email STRING ); COPY INTO customers FROM @my_stage/customers.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1) ON_ERROR = 'ABORT_STATEMENT';
Output
COPY executed successfully, 100 rows loaded.
Common Pitfalls
- File not staged: Forgetting to upload the data file to the stage before running
COPY INTOcauses errors. - Incorrect file format: Mismatched file format options (like wrong delimiter or missing header skip) lead to wrong data loading or errors.
- Data type mismatch: Loading data that doesn't match the table schema causes load failures.
- Ignoring errors: Using
ON_ERROR = 'CONTINUE'without checking logs can hide data issues.
sql
/* Wrong way: Missing SKIP_HEADER causes header to load as data */ COPY INTO customers FROM @my_stage/customers.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ','); /* Right way: Skip header row */ COPY INTO customers FROM @my_stage/customers.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);
Quick Reference
| Step | Description |
|---|---|
| Stage your data | Upload files to an internal or external stage using PUT or cloud storage. |
| Create target table | Define the table schema to match your data structure. |
| Use COPY INTO | Run COPY INTO command specifying table, stage, file format, and error handling. |
| Check load results | Verify rows loaded and review any errors in the load history. |
| Handle errors | Adjust file format or data and reload if needed. |
Key Takeaways
Use COPY INTO command to load staged data files into Snowflake tables.
Always match file format options to your data file structure.
Upload your data files to a stage before loading.
Check load results and error logs to ensure data quality.
Skip headers in CSV files to avoid loading them as data.
