Snowflake COPY INTO fails or loads 0 rows: reading load errors with VALIDATION_MODE, VALIDATE and COPY_HISTORY

A scheduled bulk load ran COPY INTO and either failed with a parsing error, finished with LOAD_FAILED / PARTIALLY_LOADED files, or reported Copy executed with 0 files processed while the files are clearly in the bucket. This guide is for bulk COPY INTO <table> run by a scheduler or a person. If the files arrive through a pipe, see the Snowpipe troubleshooting post; the error-reading techniques below still apply.

#First five minutes

1. Find the failed statement and its query ID in QUERY_HISTORY or your orchestrator log.
2. Check per-file results in COPY_HISTORY (below). It tells you which file failed, the first error message, its line and column.
3. Do not rerun with FORCE = TRUE until you know which files already loaded. That is the fastest way to create duplicates.

SELECT file_name, status, row_count, row_parsed, error_count,
       first_error_message, first_error_line_number, first_error_column_name,
       last_load_time
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
       TABLE_NAME => 'RAW.SALES.ORDERS',
       START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAMP())))
ORDER BY last_load_time DESC;

STATUS is one of Loaded, Load failed, Partially loaded or Load skipped. The Information Schema function covers the last 14 days; for older loads use SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY, which has some latency (check the current documented latency).

#Symptom → cause → fix

#Checks

#Is the file where COPY is looking?

LIST @raw.sales.landing_stage/orders/2026/10/30/;

-- Test the same PATTERN that the COPY uses
LIST @raw.sales.landing_stage/orders/ PATTERN = '.*orders_.*[.]csv[.]gz';

PATTERN is a regular expression matched against the path, not a shell glob. *.csv does not mean what it does in a shell; use .*[.]csv.

#Was it already loaded?

Snowflake keeps load metadata per table for 64 days and skips files whose name and checksum it has already loaded. A file with the same name and content is skipped silently; that is why the result says 0 files processed. Files older than 64 days whose load status is unknown are also skipped unless you set LOAD_UNCERTAIN_FILES = TRUE.

SELECT file_name, status, last_load_time
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
       TABLE_NAME => 'RAW.SALES.ORDERS',
       START_TIME => DATEADD(days, -14, CURRENT_TIMESTAMP())))
WHERE file_name ILIKE '%orders_20261030%';

#Look at the raw file through the file format

Query the staged file directly. This shows you exactly how Snowflake splits the columns:

SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, $1, $2, $3, $4, $5
FROM @raw.sales.landing_stage/orders/2026/10/30/
     (FILE_FORMAT => 'raw.sales.csv_orders', PATTERN => '.*orders_.*[.]csv[.]gz')
LIMIT 20;

If $5 holds half a customer name, you have an unquoted delimiter. If row 1 is the header, SKIP_HEADER is missing.

#Find every bad row, not just the first

#Before loading: VALIDATION_MODE

VALIDATION_MODE runs the COPY as a dry run and returns errors without loading anything:

COPY INTO raw.sales.orders
FROM @raw.sales.landing_stage/orders/2026/10/30/
FILE_FORMAT = (FORMAT_NAME = 'raw.sales.csv_orders')
VALIDATION_MODE = RETURN_ERRORS;

Options are RETURN_n_ROWS (for example RETURN_10_ROWS), RETURN_ERRORS and RETURN_ALL_ERRORS (also includes files partially loaded earlier with ON_ERROR = CONTINUE). VALIDATION_MODE does not work with a COPY that transforms data in a SELECT; validate those by querying the stage as shown above.

#After a load: VALIDATE

For a COPY that already ran, VALIDATE returns the rejected rows from that job:

-- Last COPY run in this session
SELECT * FROM TABLE(VALIDATE(raw.sales.orders, JOB_ID => '_last'));

-- A specific query ID from the scheduler log
SELECT * FROM TABLE(VALIDATE(raw.sales.orders, JOB_ID => '01b7c2d4-0000-1a2b-0000-00045e2f1c3a'));

The output includes ERROR, FILE, LINE, CHARACTER, COLUMN_NAME and REJECTED_RECORD. Save it to a table and send it to the data owner:

CREATE OR REPLACE TABLE ops.load_errors_orders_20261030 AS
SELECT * FROM TABLE(VALIDATE(raw.sales.orders, JOB_ID => '01b7c2d4-0000-1a2b-0000-00045e2f1c3a'));

VALIDATE does not support COPY statements that transform data during the load.

#Fix

#Correct the file format

Most failures are file-format settings. A sturdy CSV format:

CREATE OR REPLACE FILE FORMAT raw.sales.csv_orders
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL', '\\N')
  EMPTY_FIELD_AS_NULL = TRUE
  TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
  ENCODING = 'UTF8'
  ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE;

If the source adds columns now and then, load by header name instead of position. This needs PARSE_HEADER = TRUE in place of SKIP_HEADER:

COPY INTO raw.sales.orders
FROM @raw.sales.landing_stage/orders/2026/10/30/
FILE_FORMAT = (TYPE = CSV PARSE_HEADER = TRUE FIELD_OPTIONALLY_ENCLOSED_BY = '"'
               ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Table columns with no matching header are loaded as NULL and extra file columns are ignored, so watch for silently empty columns after an upstream rename.

#Choose ON_ERROR on purpose

Warning

ON_ERROR = CONTINUE hides data loss. The COPY reports success while rows are discarded. If you use it, alert on error_count > 0 in COPY_HISTORY and keep the VALIDATE output.

#Reload safely

Once the file format is fixed, a normal COPY picks up the files that failed, because failed files are not marked as loaded. For files that loaded partially or that you deliberately need to reload:

Caution

FORCE = TRUE loads every matching file again, whether or not it loaded before. Delete the affected rows first or you will have duplicates.

BEGIN;
DELETE FROM raw.sales.orders WHERE _source_file = 'orders/2026/10/30/orders_20261030.csv.gz';
COPY INTO raw.sales.orders (order_id, customer_id, amount, order_ts, _source_file)
FROM (SELECT $1, $2, $3, $4, METADATA$FILENAME
      FROM @raw.sales.landing_stage/orders/2026/10/30/)
FILE_FORMAT = (FORMAT_NAME = 'raw.sales.csv_orders')
PATTERN = '.*orders_20261030[.]csv[.]gz'
FORCE = TRUE;
COMMIT;

Writing METADATA$FILENAME into a column is what makes this targeted delete possible. Note that a transforming COPY like this one cannot be checked with VALIDATION_MODE or VALIDATE.

#Prevent it next time

• Store METADATA$FILENAME and a load timestamp on every raw table, so reloads are a delete plus a copy rather than a guess.
• After each scheduled load, query COPY_HISTORY and fail the pipeline if any file is Load failed or Partially loaded, or if error_count > 0.
• Treat 0 files processed as a failure when files were expected; it usually means the upstream job didn't deliver or the path changed.
• Keep file formats as named objects in version control, not inline in each COPY.
• Agree the file layout (delimiter, quoting, encoding, header, timestamp format) with the producer as a data contract.
• Don't rename or rewrite files in the landing area after delivery. A changed checksum makes Snowflake treat the file as new.