Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Unload Data from Snowflake: Syntax and Examples

To unload data from Snowflake, use the COPY INTO command to export query results or table data to external locations like cloud storage. Specify the target location, file format, and options within the command to control the output.
📐

Syntax

The COPY INTO command exports data from Snowflake to an external location. Key parts include:

  • COPY INTO <location>: Destination path or stage where data will be unloaded.
  • FROM <table_or_query>: Source table or query to export data from.
  • FILE_FORMAT = (TYPE = <format> ...): Defines the output file format like CSV or JSON.
  • HEADER = TRUE/FALSE: Whether to include column headers in output files.
  • MAX_FILE_SIZE = <bytes>: Maximum size of each output file.
sql
COPY INTO 's3://mybucket/data/'
FROM my_table
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' HEADER = TRUE)
MAX_FILE_SIZE = 50000000;
💻

Example

This example unloads data from the sales table to an S3 bucket in CSV format with headers included.

sql
COPY INTO 's3://mybucket/sales_data/'
FROM sales
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' HEADER = TRUE)
MAX_FILE_SIZE = 100000000;
Output
Statement executed successfully.
⚠️

Common Pitfalls

Common mistakes when unloading data include:

  • Not setting up the external stage or storage permissions correctly, causing access errors.
  • Forgetting to specify FILE_FORMAT, which leads to default formats that may not match your needs.
  • Ignoring file size limits, which can cause many small files or failures.
  • Not including HEADER = TRUE when headers are needed for clarity.
sql
/* Wrong: Missing FILE_FORMAT causes default format issues */
COPY INTO 's3://mybucket/data/'
FROM sales;

/* Right: Specify file format and header */
COPY INTO 's3://mybucket/data/'
FROM sales
FILE_FORMAT = (TYPE = CSV HEADER = TRUE);
📊

Quick Reference

ParameterDescriptionExample
COPY INTOCommand to unload dataCOPY INTO 's3://bucket/path/'
FROMSource table or queryFROM my_table
FILE_FORMATOutput file format and optionsFILE_FORMAT = (TYPE = CSV HEADER = TRUE)
HEADERInclude column headersHEADER = TRUE
MAX_FILE_SIZEMax size per output file in bytesMAX_FILE_SIZE = 50000000
✅

Key Takeaways

Use the COPY INTO command to unload data from Snowflake to external storage.
Always specify FILE_FORMAT to control output file type and structure.
Ensure external storage permissions and stages are properly configured.
Include HEADER = TRUE if you want column names in your output files.
Set MAX_FILE_SIZE to manage output file sizes and avoid too many small files.