COPY INTO for Loading and Unloading, with ON_ERROR and Validation

1.COPY INTO <table>: loading staged files

COPY INTO <table> loads files into a table that already exists. The files can be in a named internal stage, a table stage (@%t), the user stage (@~), a named external stage, or an external location given as a quoted s3://, gcs:// or azure:// URL. Loading requires a running warehouse.

You narrow down which files to load in three ways. A path after the stage name is treated as a case-sensitive prefix. FILES = ( ... ) lists exact file names. PATTERN takes a regular expression. The format comes from FILE_FORMAT = (FORMAT_NAME = ...), from an inline TYPE = ... with options, or from the stage when the stage already has a file format attached.

COPY INTO mytable FROM @my_ext_stage PATTERN='.*sales.*.csv';

A second form of the command transforms data during the load: FROM ( SELECT $1, $2 ... FROM @stage ). In the syntax, this form reads only from internal or external stages, not from a bare external URL. It also can't be combined with MATCH_BY_COLUMN_NAME. Two copy options control what happens to files across repeated loads. PURGE = TRUE deletes files after they load successfully; the default leaves them where they are. FORCE = TRUE loads files that haven't changed since an earlier load, which produces duplicate rows.

COPY INTO load1 FROM @%load1/data1/ FILES=('test1.csv', 'test2.csv')  ? =TRUE;

Sources12

2.COPY INTO <location>: unloading to files

Unloading is loading in reverse. COPY INTO @stage or COPY INTO 's3://...' writes data from a table, or from any SELECT (joins included), into files. After that, you download the files with GET from an internal stage, or with the cloud provider's own tools from S3 or Azure. Unloads support only three formats, CSV, JSON and PARQUET. PARTITION BY splits the output by an expression.

File names are generated. If the target path ends in a prefix, Snowflake puts that prefix on every file. Without one, the names start with data_, followed by a suffix that keeps names unique across parallel threads, such as data_stats_0_1_0. When an ad hoc unload writes to S3 or GCS with KMS server-side encryption, KMS_KEY_ID picks the key. If you omit it, the bucket's default KMS key is used.

Sources34

3.ON_ERROR: what a load does when a row is bad

ON_ERROR is a copy option for loading only. It controls what happens when a file contains bad rows. If you set it to CONTINUE, the result reports only one error per file. To see the rest, compare ROWS_PARSED with ROWS_LOADED, or use validation. SKIP_FILE reads the whole file into a buffer whether it has errors or not, so it is slower than the other two options. Skipping a large file over a handful of bad rows also wastes credits.

The result of a COPY statement has one row per file. If a monitoring job only cares about failures, set RETURN_FAILED_ONLY = TRUE so the result contains only the files that failed to load. The default is FALSE.

Sources1

4.Validating before and after a load, and reading load history

You can check files without loading them. VALIDATION_MODE makes COPY parse the files and return results without loading any data. RETURN_<n>_ROWS checks the first n rows and fails on the first error it hits. RETURN_ERRORS and RETURN_ALL_ERRORS list errors with the file, line, column and error code. In the syntax, the transformation form of COPY doesn't list VALIDATION_MODE. The troubleshooting guide pairs validation with an unload, so the rejected records end up in a file you can fix:

COPY INTO mytable
  FROM @mystage/myfile.csv.gz
  VALIDATION_MODE=RETURN_ALL_ERRORS;

SET qid=last_query_id();

COPY INTO @mystage/errors/load_errors.txt FROM (SELECT rejected_record FROM TABLE(result_scan($qid)));

After a load, the VALIDATE table function returns every error from an earlier COPY, not just the first. Pass it JOB_ID => '_last' or a query ID. It returns nothing for loads that ran with ABORT_STATEMENT, which is the default, and it fails for loads that transformed data with SELECT.

For history, Information Schema LOAD_HISTORY covers COPY loads from the last 14 days, up to 10,000 rows. The Account Usage view goes back 365 days. Neither includes Snowpipe loads. A load stopped by ABORT_STATEMENT leaves no record in either.

Sources5678

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Bulk COPY INTO <table> skips bad files by default, the same way Snowpipe does.Why is that wrong?

   The defaults differ. Bulk COPY uses ABORT_STATEMENT and Snowpipe uses SKIP_FILE.

   Covered in ON_ERROR: what a load does when a row is bad

2. 2.After a default COPY fails, the VALIDATE function will list all the errors.Why is that wrong?

   VALIDATE returns nothing for ABORT_STATEMENT loads, and ABORT_STATEMENT is the default. Use VALIDATION_MODE before loading instead.

   Covered in Validating before and after a load, and reading load history

3. 3.Information Schema LOAD_HISTORY shows every file ever loaded into a table, Snowpipe loads included.Why is that wrong?

   It covers COPY INTO loads from the last 14 days, it excludes Snowpipe, and aborted loads leave no record in it.

   Covered in Validating before and after a load, and reading load history

Practise it for real

Load a JSON file through a named internal stage that carries a file format, validating the file before the real load

1. 1.Run CREATE OR REPLACE FILE FORMAT json_format TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE;

   Why: If the documents are wrapped in an outer array, stripping it makes each document its own row.

   You should see: A file format json_format exists in the current schema.

2. 2.Run CREATE OR REPLACE STAGE mystage FILE_FORMAT = json_format;

   Why: There is no URL, so this creates an internal stage, and later COPY statements inherit its format.

   You should see: An internal stage mystage that uses SNOWFLAKE_FULL encryption by default.

3. 3.From SnowSQL, run PUT file:///tmp/sales.json @mystage AUTO_COMPRESS=TRUE; then CREATE OR REPLACE TABLE house_sales (src VARIANT);

   Why: PUT moves the local file into the internal stage, and the VARIANT column holds each JSON document.

   You should see: LIST @mystage shows sales.json.gz.

4. 4.Run COPY INTO house_sales FROM @mystage/sales.json.gz VALIDATION_MODE = 'RETURN_ERRORS';

   Why: This checks the file without loading anything.

   You should see: An empty result if the file is clean, or a list of errors. The table stays empty either way.

5. 5.Run COPY INTO house_sales FROM @mystage/sales.json.gz; then SELECT * FROM house_sales;

   Why: This is the real load. ON_ERROR is left at its default, ABORT_STATEMENT.

   You should see: One row per JSON document in the src column.