Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Export Data from Snowflake: Simple Steps and Examples

To export data from Snowflake, use the COPY INTO command to unload data from a table or query into external storage like Amazon S3 or Azure Blob Storage. This command lets you specify file format, compression, and destination path for the exported files.
📐

Syntax

The COPY INTO command exports data from Snowflake to an external location. You specify the source table or query, the destination path, and options like file format and compression.

  • source: Table name or SQL query to export data from.
  • location: External storage path (e.g., S3 bucket URL).
  • file_format: Format of exported files (CSV, JSON, Parquet, etc.).
  • compression: Optional compression type (GZIP, NONE, etc.).
sql
COPY INTO 's3://mybucket/export/'
FROM my_table
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1)
COMPRESSION = GZIP
HEADER = TRUE;
💻

Example

This example exports data from the customers table to an Amazon S3 bucket in compressed CSV files with headers.

sql
COPY INTO 's3://mybucket/exports/customers/'
FROM customers
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1)
COMPRESSION = GZIP
HEADER = TRUE
CREDENTIALS = (AWS_KEY_ID='your_aws_key' AWS_SECRET_KEY='your_aws_secret');
Output
Data exported successfully to s3://mybucket/exports/customers/ as compressed CSV files.
⚠️

Common Pitfalls

  • Not setting proper permissions on the external storage can cause export failures.
  • Forgetting to specify CREDENTIALS or using incorrect keys will block access.
  • Using incompatible file formats or missing required options like FIELD_DELIMITER can corrupt output files.
  • Not including HEADER = TRUE when needed can make CSV files harder to read.
sql
/* Wrong: Missing credentials */
COPY INTO 's3://mybucket/exports/' FROM my_table FILE_FORMAT = (TYPE = CSV);

/* Right: Include credentials */
COPY INTO 's3://mybucket/exports/' FROM my_table FILE_FORMAT = (TYPE = CSV) CREDENTIALS = (AWS_KEY_ID='key' AWS_SECRET_KEY='secret');
📊

Quick Reference

OptionDescription
COPY INTOCommand to export data from Snowflake
FROMSource table or query
's3://bucket/path/'Destination external storage path
FILE_FORMATDefines output file type and options
COMPRESSIONOptional compression type (GZIP, NONE)
HEADERInclude column headers in output (TRUE/FALSE)
CREDENTIALSAccess keys for external storage
✅

Key Takeaways

Use COPY INTO to export data from Snowflake to external storage like S3.
Always specify file format and compression options for correct output.
Provide valid credentials to access the external storage location.
Include HEADER = TRUE for CSV exports to add column names.
Check permissions on storage to avoid export failures.