A data engineer must incrementally load CSV files that land a few times per day in an S3 path into a Unity Catalog managed table. The load runs on a schedule from a Lakeflow Job, must be idempotent (re-running must not double-load files already processed), and the team wants the simplest possible SQL-only implementation with no streaming checkpoint to manage. Which approach best fits?
Answer & explanation
Correct answer: D
WHY D: COPY INTO is a retriable, idempotent SQL command that records which files it has already loaded (by path) and skips them on later runs. That is ideal for periodic, modest-volume incremental file loads scheduled from a Lakeflow Job, with no streaming checkpoint to operate.
WHY NOT A: SQL-only incremental loading is fully supported through COPY INTO. Auto Loader suits high file volumes but is not required here, it brings the checkpoint the team wants to avoid, and the claim that file tracking needs streaming state is false.
WHY NOT B: A blanket INSERT INTO over the whole directory reloads every file on each run and creates duplicates, which is the opposite of idempotent; pushing the cleanup to the silver layer does not make the load itself correct.
WHY NOT C: Rebuilding the table on each run is a full replace, not an incremental load: it reprocesses every file each time and overwrites what was loaded before instead of adding the new files.
WHY NOT E: Lakeflow Connect targets enterprise applications, databases and SaaS sources. It is not the required path for cloud files; COPY INTO and Auto Loader are the standard file-ingestion tools.