Snowflake COPY INTO reports 0 rows loaded: files skipped because of load metadata, and how to reload safely

#Symptoms

• COPY INTO raw.orders FROM @landing/orders/ returns Copy executed with 0 files processed.
• Or the result shows files with status LOAD_SKIPPED.
• You deleted rows from the target to reload a day, re-ran COPY, and nothing came back.
• An upstream team overwrote a file with corrected data under the same name, and Snowflake ignored it.

#Why it happens: load metadata

For each table, Snowflake keeps load metadata for 64 days: which files were loaded, with their ETag/checksum. By default, COPY skips files it has already loaded, which makes reruns idempotent.

For files older than 64 days whose load status is unknown, COPY skips them by default if their LAST_MODIFIED date is older than 64 days and the table's initial set of data was loaded more than 64 days ago. LOAD_UNCERTAIN_FILES = TRUE changes that.

#Step 1: See what COPY decided

COPY INTO raw.orders
FROM @landing/orders/2026/11/01/
FILE_FORMAT = (FORMAT_NAME = ff_csv)
VALIDATION_MODE = RETURN_ERRORS;   -- dry run for parse errors only

VALIDATION_MODE checks parsing, not load metadata. To see past loads:

SELECT file_name, status, row_count, row_parsed, first_error_message, last_load_time
FROM   TABLE(information_schema.copy_history(
         table_name => 'RAW.ORDERS',
         start_time => DATEADD(day, -14, CURRENT_TIMESTAMP())))
WHERE  file_name ILIKE '%2026/11/01%'
ORDER  BY last_load_time DESC;

For longer history (up to a year, with some latency) use SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY.

LOADED with a row count means the file was loaded before; that's why it's skipped now.

#Step 2: Check the files in the stage

LIST @landing/orders/2026/11/01/;

Look at name, size, md5/ETag and last_modified. Confirm:

• The path and PATTERN in your COPY match these names (a case or prefix mismatch also gives 0 files).
• The file is really the corrected version you expect (size/ETag changed?).

#Step 3: Reload on purpose with FORCE

If you need to reload files that were already loaded (after deleting the bad rows):

BEGIN;

DELETE FROM raw.orders
WHERE  src_file LIKE 'orders/2026/11/01/%';

COPY INTO raw.orders (order_id, customer_id, amount, order_ts, src_file)
FROM (
  SELECT $1, $2, $3, $4, METADATA$FILENAME
  FROM   @landing/orders/2026/11/01/
)
FILE_FORMAT = (FORMAT_NAME = ff_csv)
FORCE = TRUE;

COMMIT;

Caution

FORCE = TRUE loads every matching file again regardless of history. If you don't delete the earlier rows first, you get duplicates. Scope the path or PATTERN to exactly the files you mean to reload.

Storing METADATA$FILENAME (and optionally METADATA$FILE_LAST_MODIFIED) in the table makes targeted deletes like the one above possible. Without it, you're guessing which rows came from which file.

#Step 4: Old files and LOAD_UNCERTAIN_FILES

When backfilling files older than 64 days into a table that has existed for a while:

COPY INTO raw.orders
FROM @landing/orders/2025/
FILE_FORMAT = (FORMAT_NAME = ff_csv)
LOAD_UNCERTAIN_FILES = TRUE;

This loads files whose status is unknown, and still skips files Snowflake knows it loaded. If some of those were loaded before (more than 64 days ago), you can get duplicates; dedupe on a business key afterwards or load into a staging table first.

#Step 5: Snowpipe has its own history

Snowpipe uses pipe load history (kept for 14 days) rather than the table's COPY metadata:

SELECT SYSTEM$PIPE_STATUS('raw.orders_pipe');

ALTER PIPE raw.orders_pipe REFRESH PREFIX = 'orders/2026/11/01/';

REFRESH only queues files staged within the last 7 days that the pipe hasn't loaded. To reload files the pipe already loaded, use a manual COPY ... FORCE = TRUE into the table (after removing the old rows).

#Overwritten files: the hidden duplicate risk

When an upstream system overwrites orders_20261101.csv with corrected data, the ETag changes, so COPY loads it again: the old rows and the new rows are both in the table.

Options:

• Ask upstream to write a new file name (_v2) and never overwrite.
• Load into a staging table, then MERGE into the target on a business key.
• Keep src_file and METADATA$FILE_LAST_MODIFIED in the target and delete the older version's rows after loading.

#Quick reference

#Prevent it next time

• Always capture METADATA$FILENAME in raw tables.
• Make reload a documented procedure (delete by src_file, then FORCE), not an ad hoc command.
• Alert when a scheduled COPY processes 0 files on a day files were expected.
• Agree with upstream teams on immutable, uniquely named files.