COPY INTO loads staged files into a Snowflake table. It is the ingestion step between an internal or external stage and a staging table, before later merges, transformations, or scheduled pipeline work. section 2.0 section 2.6
Start by creating a staging table whose columns match the file data. Add operational columns such as the source filename and load timestamp to preserve lineage. A SELECT inside COPY can read staged columns with positional notation ($1, $2, and so on), include metadata$filename, and add CURRENT_TIMESTAMP(). section 2.3.1 section 3.1.4
Load a CSV file from an internal stage
COPY INTO can select five CSV fields plus filename and load time from a stage. FILE_FORMAT skips the header; ON_ERROR = ABORT_STATEMENT stops on errors; PURGE = TRUE removes an internal-stage file after loading. copy into ORDERS_STG from ( select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() from @ORDERS_STAGE ) file_format = (type = CSV, skip_header = 1) on_error = abort_statement purge = true; section 2.3.1
COPY is primarily a loading command, but its stage SELECT supports limited shaping: select or reorder columns, convert types, and truncate text. Use later SQL steps for broader transformations. section 2.3.1
The same pattern loads external-stage files. PURGE can be used there only when the storage integration’s Snowflake service principal has permission to remove files. Leaving loaded files in place is safe from ordinary repeats because Snowflake tracks per-table file metadata and skips files already loaded. section 3.1.4 section 3.1.5
Control repeat and uncertain loads deliberately:
- Rerun COPY normally to skip files already recorded as loaded for the destination table. section 3.1.5
- Use FORCE = TRUE when intentionally reloading all files regardless of load metadata. section 3.1.5
- Use LOAD_UNCERTAIN_FILES = TRUE to attempt files whose load metadata has expired. section 3.1.5
- Query INFORMATION_SCHEMA.LOAD_HISTORY to verify recent table loads; it retains 14 days of history. section 3.1.5
COPY also loads semistructured files. For JSON, a stage query can extract selected first-level values, retain nested data in a VARIANT column, and attach filename and load timestamp. COPY may be run in scheduled bulk or micro-batch pipelines; Snowpipe instead stores a COPY statement in a pipe and executes it when file-arrival events are received. section 5.2.2 section 5.5
Figure 2.1 Data pipeline that takes CSV files from a Snowflake internal stage, loads the data into a staging table, merges the data from the staging table into the target table, and transforms the data for downstream consumption
Trace COPY’s position in a complete flow: staged CSV files load into a staging table, then move through merge and transformation steps for downstream use. section 2.0
For incremental ingestion, COPY can populate an extraction table; a stream then records the new rows for insertion into a staging table. In task orchestration, run the COPY task before the dependent INSERT task. section 12.3.1 section 13.1.2
