Running Snowflake Loads and Unloads: COPY Error Options, Reloads, Schema Evolution and COPY INTO Location

1.COPY parameters for validating and handling bad data

Once a loading path is chosen, the next design question is what happens when a file is bad. COPY INTO <table> has three separate tools for this.

Dry runs with VALIDATION_MODE. This parameter tells COPY "to validate the data files instead of loading them into the specified table". The files are checked for errors but nothing is written. RETURN_n_ROWS (for example RETURN_10_ROWS) validates that many rows and fails at the first error it finds. RETURN_ERRORS and RETURN_ALL_ERRORS return error output rather than sample rows. Use VALIDATION_MODE before a large first load of an unfamiliar batch.

Tolerating errors with ON_ERROR. The values are CONTINUE, SKIP_FILE, SKIP_FILE_<num>, 'SKIP_FILE_<num>%' and ABORT_STATEMENT. These let you choose between failing the statement and accepting partial loads. Snowpipe refers to "partially loaded files (based on the ON_ERROR copy option setting)".

Column matching. MATCH_BY_COLUMN_NAME (CASE_SENSITIVE, CASE_INSENSITIVE or NONE) maps file fields to table columns by name, not by position. It is also a prerequisite for schema evolution, which comes later on this page.

COPY options only apply to COPY. For DML statements such as INSERT, DML error logging keeps a failing statement from rolling back the whole set of changes. Valid rows are committed and rejected rows are written to an error table that you query with ERROR_TABLE(). Logging is on only when the table's ERROR_LOGGING property is TRUE and the session's OPT_OUT_ERROR_LOGGING is FALSE. One listed use case comes straight from OLTP/RDBMS migrations: "Migration of third-party data that relies on DML error logging, such as data from an Oracle database."

CREATE TABLE test_dml_error_logging( n NUMBER(4, 0) NOT NULL, t VARCHAR(5) )  ?  = true;

Sources123

2.Load history, reloads, and incremental vs full updates

Load metadata is what makes repeated COPY runs safe. Bulk loads record which files went into each target table and keep that record for 64 days. Snowpipe keeps its own record per pipe. As a result, rerunning the same COPY over the same stage is incremental by default: files already loaded are skipped.

The edge case is older files. COPY can't definitively tell whether a file was already loaded when both the file's LAST_MODIFIED date and the original load are more than 64 days old. By default it skips such files. There are two ways to override that, and they behave very differently:

- LOAD_UNCERTAIN_FILES = TRUE still checks load metadata where it exists, but also tries to load files whose metadata has expired. - FORCE = TRUE loads every file and ignores load metadata. The docs warn that this "reloads files, potentially duplicating data in a table."

The same reasoning applies to incremental versus full updates more generally. Truncating and reloading a whole table repeats work each cycle, while the loading options above are designed for increments. Snowpipe delivers micro-batches "and incrementally make[s] them available". File-based CDC pipelines use streams to process only what changed. Snowpipe Streaming Named Channels give exactly-once CDC ingestion. The sources describe these incremental mechanisms but give no detailed procedure for full refreshes. In practice, a full reload with COPY means deliberately bypassing load metadata, and FORCE is the documented way to do that.

Sources243

3.Schema detection and table schema evolution for changing sources

Data sources change shape over time. "Systems that generate data add new columns to accommodate additional information, which requires downstream tables to evolve accordingly." Snowflake handles this with two features that work together. Schema detection reads column definitions from a set of files in cloud storage, which lets a pipeline create a table from those files. Table schema evolution then changes that table as new files arrive. It can add new columns automatically and drop the NOT NULL constraint from columns that are missing in new files.

Turning on ENABLE_SCHEMA_EVOLUTION is not enough by itself. A file load only evolves the table when all three of these are true:

1. The table has ENABLE_SCHEMA_EVOLUTION = TRUE, set with CREATE TABLE or ALTER TABLE. 2. The COPY INTO <table> statement uses MATCH_BY_COLUMN_NAME. 3. The loading role has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.

For CSV used with MATCH_BY_COLUMN_NAME and PARSE_HEADER, you also need ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE.

There are limits on scope. Schema evolution works for Avro, Parquet, CSV, JSON and ORC, and only for COPY INTO <table> and Snowpipe loads. INSERT can't evolve a schema, and tasks don't support schema evolution. Snowpipe Streaming on the high-performance architecture supports it for standard tables and Snowflake-managed Iceberg tables, though for Iceberg only new top-level columns can be added. By default, each COPY can add at most 100 columns or evolve one schema. Changes are recorded in the SchemaEvolutionRecord field, which you can see with DESCRIBE TABLE and SHOW COLUMNS.

Sources5

4.Data unloading with COPY INTO <location>

Unloading reverses loading. You use COPY INTO <location> to "copy the data from the Snowflake database table into one or more files in a Snowflake or external stage". From an internal stage you then download the files with GET. From S3 or Azure you use the provider's own tools.

The source of an unload can be a whole table or a SELECT statement. That SELECT supports full Snowflake SQL, including JOINs, so one unload can combine data from several tables into a single export. COPY INTO <location> writes multiple files by default (SINGLE = FALSE), and SINGLE = TRUE writes a single file. Each file gets a unique name. If you don't give a filename prefix in the path, the files are prefixed with data_.

COPY INTO @my_s3compat_stage/unload/ FROM t2;

Sources6

Exam traps

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

1. 1.FORCE = TRUE is the safe way to pick up files whose load metadata expired.Why is that wrong?

   FORCE ignores load metadata entirely and can duplicate data. LOAD_UNCERTAIN_FILES still uses existing metadata while also trying expired files.

   Covered in Load history, reloads, and incremental vs full updates

2. 2.VALIDATION_MODE loads the clean rows and reports the bad ones.Why is that wrong?

   VALIDATION_MODE only tests the files. Nothing is loaded into the table.

   Covered in COPY parameters for validating and handling bad data

3. 3.Setting ENABLE_SCHEMA_EVOLUTION on a table lets any load path, including INSERT, add columns.Why is that wrong?

   Schema evolution is limited to COPY INTO <table> and Snowpipe loads, and only when MATCH_BY_COLUMN_NAME and the right privilege are also in place.

   Covered in Schema detection and table schema evolution for changing sources

Practise it for real

See DML error logging keep good rows and capture bad ones, then compare the behaviour with logging switched off.

1. 1.Run: CREATE TABLE test_dml_error_logging( n NUMBER(4, 0) NOT NULL, t VARCHAR(5) ) ERROR_LOGGING = true;

   Why: ERROR_LOGGING = true makes this the base table for an error table.

   You should see: The table is created.

2. 2.Run: INSERT INTO test_dml_error_logging VALUES ('invalid_cast', '1'), (10, 'valid'), (NULL, 'toolong');

   Why: Mixes one valid row with a bad cast and a NULL into a NOT NULL column.

   You should see: 1 row inserted instead of the whole statement failing.

3. 3.Run: SELECT * FROM ERROR_TABLE(test_dml_error_logging);

   Why: Rejected rows and their error metadata go to the error table.

   You should see: Two rows, with error codes 100038 and 100072.

4. 4.Run: ALTER TABLE test_dml_error_logging SET ERROR_LOGGING = false; then repeat the INSERT.

   Why: Shows the default behaviour, where one bad row fails the statement.

   You should see: Error 100038 is returned and nothing is logged.