A team lands CSV files in a cloud storage folder each hour and loads them into a Delta table with a COPY INTO statement that is rerun on a schedule against the same folder. Files are retained in the folder for thirty days. What happens to a file that a previous run has already loaded successfully?
COPY INTO prod.raw.events
FROM 's3://landing/events/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true')- AIt is read again on every run, so the table accumulates a duplicate copy of each retained file until the folder is emptied.
- BIt is skipped, because COPY INTO records the files it has loaded for that target table and ingests each one at most once. Correct
- CIt is read again unless the statement sets a rescued data column, which is the setting that suppresses files loaded by an earlier run.
- DIt is read again unless a checkpoint location is supplied, since COPY INTO stores its list of loaded files in that checkpoint directory.
Why A is wrong: This is the behaviour of a plain read of the folder followed by an append, and candidates often assume COPY INTO works the same way. COPY INTO differs because it maintains its own record of loaded files against the target table.
Why B is correct: COPY INTO tracks the files it has already ingested for the target table, so rerunning the same statement loads the files that have appeared since the previous run and leaves the rest alone.
Why C is wrong: The rescued data column captures fields that do not match the expected schema, and it plays no part in deciding which files are ingested. Skipping loaded files is built into COPY INTO itself.
Why D is wrong: Checkpoint locations belong to Structured Streaming sources such as Auto Loader, and COPY INTO does not take one. Its idempotency comes from metadata held against the target table.