Databricks Certified Data Engineer Associate free question 3 / 10 · Domain 2: Data Ingestion and Loading
Every night the same COPY INTO (PATTERN = '.csv', no COPY_OPTIONS) loads the CSV files in an external location folder into a Unity Catalog Delta table. A partner overwrote sales_0930.csv, which was loaded last night, with corrected contents under the same name and with the same columns. What happens to sales_0930.csv when the usual COPY INTO runs tonight?
- It is read again as a file of the same name, and the rows that went in last night are replaced with the corrected rows
- It is read again because its modification time is newer than last night's load, and the corrected rows are appended to the table
- A file whose contents changed is treated as a different file, and the corrected rows are appended to the table
- It is skipped as an already loaded file, the corrected rows do not enter the table, and last night's rows remain
Show the answer and explanation
Answer: D
Why this is correct
COPY INTO is re-triggerable and idempotent: files in the source location that have already been loaded are skipped on the next run, and the documentation says explicitly that this is true even if the files have been modified since they were loaded. sales_0930.csv was loaded last night, so tonight it is skipped even though the overwrite changed its contents, and the table keeps the rows that went in last night.
Why the other options are wrong
"It is read again because its modification time is newer than last night's load, and the corrected rows are appended to the table" … The documentation says: "Files in the source location that have already been loaded are skipped. This is true even if the files have been modified since they were loaded." Even though overwriting changed the modification time, an already loaded file is skipped and not read again.
"A file whose contents changed is treated as a different file, and the corrected rows are appended to the table" … The same sentence applies: "This is true even if the files have been modified since they were loaded." A change in contents does not make the file a different file to be read again; it is skipped as already loaded.
"It is read again as a file of the same name, and the rows that went in last night are replaced with the corrected rows" … Since already loaded files are skipped, no re-read happens at all. COPY INTO loads file data into a Delta table; it has no behavior of looking up rows from a file of the same name and replacing them.
Common trap
If you assume that an overwritten file is reflected in the next load, the table stays out of date while you believe the corrected rows went in. The setting that deliberately re-reads already loaded files is force in COPY_OPTIONS: it disables idempotency and loads files regardless of whether they have been loaded before.