A data engineering team ingests raw JSON and CSV drops landing every minute in a cloud object storage path. During peak hours, millions of small files arrive per hour, and the schema occasionally gains new columns. The pipeline must (1) process new files incrementally, (2) provide exactly-once processing guarantees when writing to Delta, and (3) avoid dropping unexpected columns. Which approach best fits these requirements with the least custom state management?
Answer & explanation
Correct answer: C
WHY C: Auto Loader (cloudFiles, with cloudFiles.format set for the source files) is designed for incremental ingestion at scale, including very large file counts. It tracks progress in the streaming checkpoint and provides exactly-once guarantees when writing to Delta. With cloudFiles.schemaLocation (or Lakeflow Spark Declarative Pipelines managing these settings), it can infer and evolve the schema (default addNewColumns) or use rescue mode to retain unexpected fields in the rescued data column instead of silently dropping them.
WHY NOT B: The plain file source relies on directory listing and does not provide Auto Loader's scalable file discovery or built-in schema evolution/rescue patterns. A custom state table is extra operational burden and easy to get wrong.
WHY NOT A: COPY INTO is idempotent by default, but force=true disables that idempotency and reloads files, which causes duplicate ingestion. It also does not provide the streaming-style continuous file arrival handling of Auto Loader.
WHY NOT D: Overwriting the target on each run is not incremental processing: it re-reads the whole path, increases cost, and can introduce race conditions with concurrent readers. It is a heavy-handed way to handle duplicates and schema drift.