1
Data Ingestion & Acquisition
Medium
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?
- ASchedule
COPY INTO each minute with COPY_OPTIONS('force'='true') to reload files on each run; this picks up the latest data and prevents duplicates in the target Delta table. - BUse
spark.readStream.format("json").load(path) directly and rely on directory listing each trigger; store processed file names in a custom Delta table for exactly-once semantics. - CUse Auto Loader with
spark.readStream.format("cloudFiles"), a persistent checkpointLocation and cloudFiles.schemaLocation, keeping schema evolution on its default addNewColumns mode. - DUse batch
spark.read.format("csv").load(path) with recursiveFileLookup=true every minute and overwrite the target Delta table to avoid duplicates and handle schema drift.
Show 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.
2
Data Ingestion & Acquisition
Medium
A streaming pipeline ingests events and uses foreachBatch to write the same micro-batch into two Delta tables (a raw table and an audit table). During intermittent failures, the job retries a batch and occasionally writes duplicates. Which configuration best makes the batch writes idempotent so retries do not duplicate data?
- ASet the output mode to
append and remove the checkpoint location, letting Delta Lake deduplicate identical rows on each retried write to both tables. - BUse
COPY INTO inside foreachBatch with COPY_OPTIONS('force'='true'), letting Delta reload the same files safely whenever the batch is retried. - COn each write inside
foreachBatch, set option('txnAppId', <stable_app_id>) and option('txnVersion', batch_id), letting Delta skip repeated writes. - DEnable
option('mergeSchema', 'true') on both writes inside foreachBatch, letting schema evolution reconcile the rows written when a batch is retried.
Show answer & explanation
Correct answer: C
WHY C: Delta supports idempotent writes using the (txnAppId, txnVersion) pair. When a batch is retried, reusing the same app ID and batch ID lets Delta identify duplicate writes for that pair and skip them. This is especially important when doing multiple writes inside foreachBatch.
WHY NOT A: Checkpoints are required for Structured Streaming correctness; Delta does not deduplicate rows by itself, and removing checkpoints makes reprocessing more likely.
WHY NOT B: COPY INTO is for loading from file locations; force=true disables its idempotency and can introduce duplicates.
WHY NOT D: Schema evolution settings do not address at-least-once retry behavior; duplicates are a write idempotency problem, not a schema problem.
3
Data Ingestion & Acquisition
A data engineer runs an Auto Loader job that ingests from a cloud storage container which already holds tens of millions of small files and receives hundreds of thousands of new files per hour. The current stream uses the default directory listing mode, and the team observes that each micro-batch spends most of its time enumerating the storage directory before any data is processed, making latency grow as the directory fills. The engineer wants to reduce the per-batch discovery cost without moving files or changing the folder layout. Which change most directly addresses the bottleneck?
- ASwitch the stream to file notification mode by setting
cloudFiles.useNotifications to true (configuring the cloud queue/event subscription), so that new files are discovered from cloud storage event notifications instead of by repeatedly listing the entire directory. - BReduce
cloudFiles.maxFilesPerTrigger to a small number so that each micro-batch enumerates fewer files, which lowers the listing time proportionally and keeps latency bounded even as the directory grows. - CSet
cloudFiles.includeExistingFiles to false and enable cloudFiles.validateOptions, which tells Auto Loader to skip the directory scan entirely and rely on the Delta checkpoint to know which files remain, eliminating listing cost for both existing and new files. - DIncrease the trigger interval to several minutes so the listing operation runs less frequently; because directory listing is the fixed cost, spreading it over longer batches reduces its total contribution to latency.
- EEnable
cloudFiles.backfillInterval and repartition the source by ingest date, which lets Auto Loader list only the current date's subfolder per batch and therefore scales independently of the total number of files in the container.
Show answer & explanation
Correct answer: A
WHY A: File notification mode subscribes to cloud storage events (via a managed queue) so newly arrived files are pushed to Auto Loader rather than discovered by scanning the whole directory each batch. This is the designed remedy when directory listing dominates latency at high file counts, and it requires no change to the folder layout. WHY NOT B: Lowering maxFilesPerTrigger caps how many files are processed per batch but does not reduce the cost of listing the directory to find candidate files; the enumeration still scans the growing directory. WHY NOT C: includeExistingFiles=false only ignores files present before the stream started; it does not stop Auto Loader from listing for new files, and there is no option that skips discovery while still ingesting new data. WHY NOT D: A longer trigger interval reduces how often listing happens but each listing still scans the entire growing directory, so latency per batch keeps rising; it treats the symptom, not the cause. WHY NOT E: backfillInterval schedules periodic re-listing to catch missed notifications; it does not partition discovery by date, and the described date-subfolder scanning is not how Auto Loader listing works without a layout change.
4
Data Ingestion & Acquisition
A data engineer must ingest a large volume of scanned invoice image files (mixed PNG and PDF) from cloud storage into a Delta table so that each row carries the raw file bytes plus metadata (path, size, modification time) for a downstream OCR process. The engineer wants to use a single, supported Spark read approach that captures the binary content and file metadata directly, works with Auto Loader for incremental ingestion, and does not require parsing the file contents at ingest time. Which format and read strategy is correct?
- ARead the files with
format('text') and wholetext set to true, which loads each entire file into a single STRING column; base64-encode the string on write so the binary content survives, and capture metadata using input_file_name(). - BRead the files with
format('image'), which decodes each PNG and PDF into a standardized pixel array plus height/width/mode metadata, and persist that decoded representation to Delta so the OCR step can consume normalized image tensors directly. - CRead the files with
format('binaryFile'), which returns each file as a row containing path, modificationTime, length, and a content column of raw bytes, and use it as the Auto Loader cloudFiles.format so new image files are ingested incrementally without parsing their contents. - DRead the files with
format('avro') because Avro is the container format Databricks uses for arbitrary binary blobs; map each image into an Avro bytes field and let Auto Loader infer the metadata columns automatically from the Avro header. - EUse
spark.read.format('parquet') with recursiveFileLookup enabled, which allows Parquet to wrap non-columnar binary payloads; the file bytes land in a _binary column and the metadata is added from the Parquet footer statistics.
Show answer & explanation
Correct answer: C
WHY C: The binaryFile data source is purpose-built for this: it reads each file as one row with path, modificationTime, length, and a content binary column, does no content parsing, and is supported as an Auto Loader cloudFiles.format for incremental ingestion of arbitrary file types like images and PDFs. WHY NOT B: The image data source decodes standard image formats into pixel arrays but is oriented at ML feature loads, does not handle PDFs, and decoding at ingest is exactly what the requirement wants to avoid. WHY NOT A: The text source is for text/line data; forcing binary through it and base64-encoding is a fragile workaround that can corrupt bytes and is not the supported binary ingestion path. WHY NOT D: Avro is a row-based serialization format, not a generic 'wrapper for image files'; it does not auto-capture file path/size/mtime metadata for arbitrary blobs. WHY NOT E: Parquet is a columnar analytics format; it does not natively wrap opaque binary files into a _binary column with file metadata, and recursiveFileLookup only controls directory traversal.
5
Data Ingestion & Acquisition
A data engineer must build an append-only ingestion pipeline that lands raw events from a message bus into a Delta Bronze table. The pipeline is a Structured Streaming job that must be able to fail and be restarted at any time without producing duplicate rows in the Bronze table, and it must not require a downstream deduplication step. The source provides an at-least-once delivery guarantee. Which combination of mechanisms provides exactly-once append semantics for the Bronze write under restarts?
- AWrite with
.mode('append') in a foreachBatch and, inside the batch function, run a DELETE on the Bronze table for the current batch's key range before inserting, so each restart cleans up any partial prior write. This makes the append idempotent because the delete-then-insert pair is atomic per batch. - BUse a Structured Streaming write to the Delta table with a configured
checkpointLocation. Delta's streaming sink records each committed batch id in its transaction log, so on restart Delta skips micro-batches that were already committed, giving exactly-once appends without a manual dedup step. - CSet the Delta table property
delta.enableChangeDataFeed to true and write in complete output mode. Change Data Feed tracks every committed version, so on restart the sink replays only the uncommitted deltas, guaranteeing no duplicate rows are appended. - DAdd
.option('txnAppId', 'bronze').option('txnVersion', current_timestamp()) to the streaming write. The txnVersion derived from the current timestamp guarantees a unique transaction id per batch so that Delta rejects any replayed write with an older timestamp. - EEnable
spark.databricks.delta.autoCompact and optimizeWrite, then write in append mode without a checkpoint. Auto-compaction rewrites duplicate rows into a single file during ingestion, so restarts that re-append the same records are automatically collapsed to one copy.
Show answer & explanation
Correct answer: B
WHY B: A Structured Streaming write to a Delta sink with a persistent checkpointLocation gives exactly-once semantics: Delta's transaction log durably records the highest committed streaming batch id, so when the job restarts it does not re-commit an already-committed micro-batch, and it resumes source offsets from the checkpoint. No downstream dedup is needed. WHY NOT A: Delete-then-insert per batch is not generally atomic against arbitrary key ranges for append-only raw events (there may be no clean key range), adds heavy rewrite cost, and is unnecessary given Delta's built-in idempotent streaming commits. WHY NOT C: complete output mode rewrites the whole result each batch (used for aggregations), which is wrong for append-only raw ingestion, and Change Data Feed tracks row changes for consumers — it does not provide the sink's restart idempotency. WHY NOT D: The txnAppId/txnVersion idempotent-write API is designed for batch writers using a monotonically increasing version per source; deriving txnVersion from a timestamp is not the documented pattern and streaming already handles this via the checkpoint. WHY NOT E: Auto-compaction only rewrites small files into larger ones; it does not detect or remove duplicate rows, and writing a stream without a checkpoint loses exactly-once guarantees entirely.
6
Data Ingestion & Acquisition
Medium
A Structured Streaming job reads from Kafka using .option('startingOffsets', 'earliest') and writes to a Delta sink with a checkpoint. After an incident, the job is redeployed and unexpectedly reprocesses old Kafka data from the beginning. Holding the Kafka topic and code constant, which change most directly prevents this behavior in the future?
- ADisable
failOnDataLoss so that the query never fails; this guarantees no reprocessing occurs after redeployments. - BSet
startingOffsets='latest' and also set endingOffsets='latest' so that each run only reads the newest Kafka data. - CEnsure the streaming query uses the same, persistent
checkpointLocation across restarts; startingOffsets applies only when a new query starts, but on restart the query resumes from offsets stored in the checkpoint. - DSet
kafka.group.id to a fixed value so Kafka permanently stores offsets for the stream; this is the recommended way to coordinate multiple concurrent streaming queries.
Show answer & explanation
Correct answer: C
WHY C: For Kafka Structured Streaming, the checkpoint is the durable source of truth for progress. startingOffsets is only used when starting a brand-new query; if the checkpoint is changed or deleted, the stream can start from the configured starting offsets (or earliest, depending on config) and reprocess old data. Keeping a stable checkpoint location is the direct fix. WHY NOT B: endingOffsets is for batch reads, not a typical streaming configuration, and doesn’t solve checkpoint loss. WHY NOT D: Setting kafka.group.id can cause interference between concurrently running queries using the same group ID and is recommended only with extreme caution; it’s not a substitute for Structured Streaming checkpoints. WHY NOT A: failOnDataLoss=false allows a query to continue despite missing Kafka data; it does not prevent replaying old offsets when checkpoint state is lost.
7
Data Ingestion & Acquisition
A data engineer's Auto Loader stream must catch up on a large backlog of already-landed files after a multi-day outage, but the downstream Silver transformation cluster can only sustainably process a bounded number of records per micro-batch. During backfill, uncontrolled batch sizes cause executor OOM and long, unstable batches. The engineer wants Auto Loader to cap how much data each trigger admits so batches stay a predictable size during both backfill and steady state, without dropping any backlogged files. Which option correctly bounds per-trigger intake?
- AReduce
cloudFiles.fetchParallelism to 1, forcing single-threaded file fetch; serial fetching naturally limits how many records enter a batch and prevents OOM without needing an explicit size cap. - BSet
spark.sql.shuffle.partitions to a high value so the backlog is spread across more tasks; more partitions reduce per-task memory and therefore bound the effective batch size during backfill. - CSwitch the trigger to
Trigger.Once so the entire backlog is processed in exactly one batch; running it repeatedly drains the backlog while guaranteeing each run reads all outstanding files at once. - DSet
cloudFiles.includeExistingFiles to true and maxFilesPerTrigger to 0, which tells Auto Loader to admit files as fast as storage can list them but throttle memory via adaptive query execution instead of a fixed batch cap. - ESet
cloudFiles.maxBytesPerTrigger (and/or cloudFiles.maxFilesPerTrigger) to cap the volume admitted per micro-batch; Auto Loader then processes the backlog across many bounded batches, keeping batch size predictable while eventually ingesting every file.
Show answer & explanation
Correct answer: E
WHY E: maxBytesPerTrigger and maxFilesPerTrigger are the rate limiters that cap how much data each Auto Loader micro-batch admits. They keep batch sizes predictable and memory bounded during a large backfill while still processing every backlogged file across successive batches. WHY NOT B: More shuffle partitions changes task granularity for shuffles but does not limit how many source records/files enter a micro-batch, so batches can still be huge and OOM at read time. WHY NOT C: Trigger.Once (or availableNow) processes all available data — with a large backlog that means one enormous batch, which is exactly the OOM scenario; availableNow respects rate limits, but plain 'one batch for everything' does not bound size. WHY NOT D: maxFilesPerTrigger=0 is not a valid throttle setting, and AQE does not cap source intake; this does not bound batch size. WHY NOT A: fetchParallelism tunes listing/fetch concurrency, not the number of records admitted per batch; serial fetch slows discovery but does not cap batch size or prevent OOM.
8
Data Ingestion & Acquisition
A data engineer ingests nested XML documents where each file contains a single root element <orders> wrapping many repeating <order> elements, and each <order> should become one row with its child elements as columns. Using the built-in Spark XML reader, the first attempt produces a single row per file (the whole document) instead of one row per order. Which configuration change correctly maps each <order> element to its own row while preserving nested child fields as struct/array columns?
- AConvert the XML to JSON first with
from_xml applied to a binaryFile read, then explode the resulting orders.order array; the XML reader itself cannot split a single document into multiple rows. - BSet the reader option
rootTag to order and leave rowTag unset, because rootTag defines the element that Spark iterates over to produce rows, while rowTag only names the wrapper written on output. - CEnable
multiLine set to true and inferSchema set to true; with multiline inference on, the XML reader automatically detects the most frequently repeated element (<order>) and uses it as the implicit row boundary. - DSet the reader option
rowTag to order, so the XML source treats every <order> element as a record boundary and infers each order's child elements into typed columns, including nested structs and arrays for repeated children. - ESet
rowTag to orders (the root) and add explode on the order array afterward, because rowTag must always point at the outermost element and per-record splitting is done with a subsequent explode.
Show answer & explanation
Correct answer: D
WHY D: The Spark XML reader's rowTag option defines which element is treated as a record; setting rowTag='order' yields one row per <order>, with child elements inferred as columns and repeated/nested children represented as arrays/structs. WHY NOT B: rootTag names the document's outer wrapper (mainly relevant for writes); it is rowTag that controls the per-row boundary, so this reverses the two options' roles. WHY NOT C: multiLine affects how records that span multiple lines are parsed and inferSchema controls typing; neither auto-detects a row boundary — you must specify rowTag. WHY NOT A: The XML reader can absolutely split a document into multiple rows via rowTag; the from_xml + explode route is an unnecessary detour and mischaracterizes the reader's capability. WHY NOT E: Pointing rowTag at the root produces one row per file (the observed bug), and while a later explode could work, it is not the correct or idiomatic fix when rowTag='order' does it directly.
Take the full DE Professional practice test →