Getting files into and out of Snowflake tables

Snowflake supports bulk loading via COPY INTO <table> from internal or external stages and continuous loading via Snowpipe, while data can be unloaded to stages using COPY INTO <location>. Both loading and unloading rely on file formats, stages, and virtual warehouses (bulk) or serverless compute (Snowpipe) to move data efficiently between Snowflake and external storage.

1 · Learn the must-know

• 1COPY INTO <table> loads data from staged files and requires a running virtual warehouse, while COPY INTO <location> unloads table data to files in a stage, both supporting formats like CSV, JSON, Parquet, ORC, XML, and Avro.
• 2Internal stages (user, table, or named) and external stages (S3, Azure Blob, GCS) can be used for both loading and unloading; named stages store reusable file format and location metadata.
• 3Snowpipe enables continuous, serverless micro-batch loading triggered by cloud event notifications (e.g., S3 event notifications) or REST API calls, and is billed separately from warehouse compute based on actual usage.
• 4The COPY command tracks load history metadata for 64 days by default and prevents reloading the same file into the same table unless FORCE=TRUE or the file content changes, avoiding accidental duplicate loads.
• 5The VALIDATION_MODE parameter and COPY INTO error handling options (ON_ERROR, SIZE_LIMIT) let you validate or control how many errors are tolerated during a load without failing the entire operation.
• 6When unloading, Snowflake can split output into multiple files (using SINGLE=FALSE, the default) for parallelism, and supports compression (e.g., gzip) automatically unless overridden.

2 · Check your understanding