How does Snowflake's COPY INTO avoid reloading a file, and when does that protection lapse?

## The mechanism When `COPY INTO <table>` runs, Snowflake writes an entry into the target table's **load metadata** for every file it loaded: file path, load timestamp, row counts, and a content signature derived from the file's ETag and last-modified time. On the next `COPY`, each candidate file is checked against that metadata. If the path and signature match an entry, the file is skipped and reported as `LOAD_SKIPPED`/already loaded rather than re-read. That is why the canonical bulk-load loop — drop files into a stage, run the same `COPY` statement on a schedule — is safe to retry. A `COPY` that fails halfway, or a job that fires twice because an orchestrator retried it, does not duplicate data. ```sql COPY INTO orders FROM @raw/orders/ FILE_FORMAT = (FORMAT_NAME = my_csv); -- second run over the same files: 0 rows loaded, files reported as already loaded ``` ## Where it lapses **Expiry.** Load metadata for a table is retained for 64 days. After that, the entry for an old file is gone; if that file is still visible in the stage and still matches the `COPY`'s path or `PATTERN`, it is loaded again — a silent duplicate. This bites teams who never clean their stage and point `COPY` at a broad prefix. **FORCE.** `FORCE = TRUE` tells `COPY` to ignore load metadata and load everything it sees. It exists for genuine reloads (you truncated the table and want the same files back), and it is the single most common way people duplicate a fact table by accident. **Modified files.** If a file is overwritten at the same path with different content, the changed signature makes it a new file and it loads again — appending, not replacing. Snowflake does not diff contents. **No key enforcement.** Snowflake accepts `PRIMARY KEY` in DDL but does not enforce it. Nothing downstream stops a duplicate; you find it with a `GROUP BY ... HAVING COUNT(*) > 1` after the fact. ## Error handling interacts with this `ON_ERROR` decides what a bad row does to the load: - `ABORT_STATEMENT` (the default for bulk `COPY`) — the whole statement fails and nothing is committed, so load metadata records nothing. - `CONTINUE` — bad rows are skipped, good rows load, and the file is marked loaded. The rejected rows are gone unless you go looking for them. - `SKIP_FILE`, `SKIP_FILE_<n>`, `SKIP_FILE_<n>%` — the file is abandoned once the error count or percentage threshold is reached, and it is not marked as loaded, so a later run will retry it. Snowpipe defaults to `SKIP_FILE` instead, which matters when you compare the two paths. To see what went wrong, `VALIDATE(table_name, job_id => '_last')` returns the errors from a prior `COPY`, and `VALIDATION_MODE = RETURN_ERRORS` performs a dry run that parses the files and reports problems **without loading anything**. `VALIDATION_MODE` cannot be combined with a transformation-style `COPY`. ## Auditing what landed `INFORMATION_SCHEMA.COPY_HISTORY(TABLE_NAME => 'ORDERS', START_TIME => ...)` is a table function over recent loads; `SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY` is the account-wide view with a longer retention and a latency of some hours. Both give file name, row counts, errors and status, and both are how you prove whether a suspect file was loaded once or twice. ## Designing so it cannot go wrong 1. **Write files into dated, immutable paths** — `@raw/orders/dt=2026-08-20/part-0001.csv.gz` — and point each `COPY` at that day's prefix, not at the table root. The load then cannot see anything from 64 days ago. 2. **Expire staged files** with `PURGE = TRUE` (internal stages) or a bucket lifecycle rule (external), so old files physically cannot be re-read. 3. **Never write producers that overwrite a path in place.** New content means a new file name. 4. **Treat `FORCE = TRUE` as a manual, reviewed operation**, usually paired with truncating or deleting the affected partition first. 5. **Land into a staging table, then MERGE** into the target on a business key when the source genuinely can re-deliver rows. Idempotency then comes from your key rather than from file names. ## What interviewers are actually testing They want to know whether you believe "Snowflake dedupes for me" without qualification. The right answer is: yes, by **file identity**, for 64 days, unless you tell it not to — and never by row content.