Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 INTO causes 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

StepDescription
Stage your dataUpload files to an internal or external stage using PUT or cloud storage.
Create target tableDefine the table schema to match your data structure.
Use COPY INTORun COPY INTO command specifying table, stage, file format, and error handling.
Check load resultsVerify rows loaded and review any errors in the load history.
Handle errorsAdjust 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.