Snowflake SQL Patterns

COPY INTO — errors, reloads, and silent skips

Default ON_ERROR aborts the load, FORCE can duplicate rows, and already-loaded files are skipped unless you ask otherwise.

The problem

A COPY INTO looked like it succeeded, but a file you restaged is missing, or a file you meant to reload was skipped, or one bad row aborted forty clean files. Official Snowflake COPY INTO <table> documentation covers all three, and the defaults are easy to miss.

This page is documentation-only. No COPY INTO was executed here.

A working example

-- Snowflake syntax from official documentation (not executed)

COPY INTO orders
  FROM @my_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  ON_ERROR = ABORT_STATEMENT;   -- official default for bulk COPY

Useful official knobs:

-- Check parsing without loading (VALIDATION_MODE)
COPY INTO orders
  FROM @my_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  VALIDATION_MODE = RETURN_ERRORS;

-- Keep going past bad rows (know that you will be missing those rows)
COPY INTO orders
  FROM @my_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  ON_ERROR = CONTINUE;

-- Skip a whole file after errors, rather than aborting the statement
COPY INTO orders
  FROM @my_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  ON_ERROR = SKIP_FILE;

-- Reload files that COPY already considers loaded — can duplicate rows
COPY INTO orders
  FROM @my_stage/orders/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
  FORCE = TRUE;

Official ON_ERROR values are CONTINUE, SKIP_FILE, SKIP_FILE_<num>, 'SKIP_FILE_<num>%', and ABORT_STATEMENT. Official defaults: bulk COPY uses ABORT_STATEMENT; Snowpipe uses SKIP_FILE. A setting that is reasonable in a pipe can be surprising in a worksheet, and the other way around.

If you pass FILES = (…) and a listed file cannot be found, official docs say the default ON_ERROR = ABORT_STATEMENT aborts the load unless you set a different ON_ERROR. The maximum number of named files is 1000.

Official docs also warn that Snowflake does not insert a / between a stage URL and the names in FILES. You have to put the separator on the URL, or on each file name, yourself.

The gotcha

Already-loaded files are skipped. Official usage notes: unless you specify FORCE = TRUE, COPY ignores staged files that were already loaded into the table. To reload, use FORCE = TRUE or modify the file and stage it again so the checksum changes. Official text on FORCE = TRUE: this option reloads files, potentially duplicating data in the table. Default is FALSE.

That skip is usually what you want in a daily incremental load. It is disastrous if you “fixed the file in place” without changing its checksum and are waiting for the new rows to appear.

Load status can become unknown on old files. Official LOAD_UNCERTAIN_FILES documentation describes files whose load status is unknown when all of these are true: the file’s LAST_MODIFIED (when it was staged) is older than 64 days; the initial load into the table was more than 64 days ago; and, if the file had loaded successfully, that event was also more than 64 days ago. COPY skips those files by default. LOAD_UNCERTAIN_FILES = TRUE loads them; FORCE = TRUE loads all files regardless of known status. Both can duplicate if the data was actually loaded once already.

VALIDATION_MODE does not load data, which is the point, but it is also not a complete dress rehearsal of every copy option (official docs restrict it in some transformation and Iceberg cases).

PURGE = TRUE is documented to try to remove successfully loaded files, and to not return an error if the purge fails. Official recommendation: list the stage periodically and remove leftovers yourself. Default PURGE is FALSE.

When not to use this

Verification

All COPY options, defaults, the already-loaded skip, FORCE duplication warning, 64-day uncertain-load behavior, FILES path-separator note, and PURGE failure behavior are from official Snowflake COPY INTO documentation. No COPY INTO was executed.

Official docs