Home / DE Associate practice test / Data Ingestion and Loading / Question

Databricks Data Engineer Associate · free practice question

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…

Exam-style question on Data Ingestion and Loading from the free DE Associate practice test, with the answer and a full explanation below.

Updated November 10, 2026

Data Ingestion and Loading

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?

  1. AUse Auto Loader with the availableNow trigger and a streaming checkpoint, on the basis that SQL has no incremental file-loading command and file tracking has to live in Structured Streaming state.
  2. BRun INSERT INTO ... SELECT * FROM read_files(...) over the whole directory on each scheduled run, and remove the duplicate rows this produces with deduplication logic in the silver layer.
  3. CRun CREATE OR REPLACE TABLE ... AS SELECT against the directory on each run, replacing the table with whatever files are currently in the path and reprocessing the full history each time.
  4. DUse COPY INTO, which records the files it has already loaded and skips them on later runs, giving idempotent incremental loads from one retriable SQL command with no checkpoint to operate.
  5. EUse a Lakeflow Connect managed connector, on the basis that it is the supported mechanism for loading raw files from cloud object storage such as S3 into Unity Catalog managed tables.
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.

Take the full DE Associate practice test free →

Would you pass today?

This question is one of 45 in the free, timed Databricks Data Engineer Associate practice test. Take it and get a section-by-section score.

Start the free test →