Data Ingestion & Acquisition: Databricks Data Engineer Professional Practice Questions

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?

1. AUse Databricks Auto Loader with spark.readStream.format("cloudFiles"), set cloudFiles.format, configure a persistent checkpointLocation, and set cloudFiles.schemaLocation (or use Lakeflow Spark Declarative Pipelines to manage these). Enable schema evolution (default addNewColumns) or rescue mode to retain unexpected columns in the rescued data column while maintaining exactly-once processing into Delta.
2. BUse spark.readStream.format("json").load(path) directly and rely on directory listing each trigger; store already-processed file names in a custom Delta table to ensure exactly-once semantics.
3. CSchedule COPY INTO every minute with COPY_OPTIONS('force'='true') so that modified files are always reloaded; this guarantees the latest data and prevents duplicates.
4. 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.

A data engineer builds an Auto Loader stream that ingests semi-structured JSON files from a cloud storage location into a Bronze Delta table. Upstream producers occasionally add new fields and, more rarely, send records where an existing field arrives with an unexpected data type (e.g., a numeric amount field arriving as a quoted string). The requirement is that the pipeline must never silently drop data, must automatically pick up genuinely new columns without a manual restart loop that loses records, and must preserve any value that does not match the inferred schema so it can be inspected later. Which Auto Loader configuration best satisfies all three requirements?

1. ASet cloudFiles.schemaEvolutionMode to none and define the full schema by hand with .schema(...), so that any record that does not conform is written unchanged to the target table and flagged by a downstream expectation. A fixed schema is the only way to guarantee no data loss because Auto Loader never has to guess column types.
2. BUse mergeSchema set to true on the streaming write instead of configuring a schema location, because mergeSchema on the Delta write is the mechanism Auto Loader uses to evolve schemas. Type mismatches are automatically upcast to the wider type at write time, so a rescued data column is unnecessary and no record is dropped.
3. CEnable cloudFiles.inferColumnTypes set to false so that every column is ingested as STRING, which removes the possibility of a type mismatch entirely. Because all data lands as strings, no rescued data column is required and schema evolution can be disabled to keep the stream stable.
4. DSet cloudFiles.schemaEvolutionMode to rescue, which routes every incoming record into the _rescued_data column as raw JSON and leaves the typed columns empty. A downstream job then parses _rescued_data on a schedule, giving full control over type handling while guaranteeing that no field is ever lost during ingestion.
5. EConfigure cloudFiles.schemaLocation for a persisted schema, keep the default addNewColumns evolution mode, and read the _rescued_data column. New columns trigger a managed stream restart that resumes from the checkpoint without data loss, and any value that does not match the inferred type is captured in _rescued_data rather than being dropped.

A Structured Streaming job reads from Kafka using .option('startingOffsets', 'latest') 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?

1. ASet startingOffsets='latest' and also set endingOffsets='latest' so that each run only reads the newest Kafka data.
2. BEnsure 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.
3. CSet 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.
4. DDisable failOnDataLoss so that the query never fails; this guarantees no reprocessing occurs after redeployments.

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?

1. ASet the output mode to append and remove the checkpoint location; Delta Lake will deduplicate identical rows automatically.
2. BUse COPY INTO inside foreachBatch with COPY_OPTIONS('force'='true') so that the same files can be reloaded safely.
3. CEnable mergeSchema=true on the writes so that schema evolution prevents duplicates when a batch is retried.
4. DFor each DataFrame write inside foreachBatch, set option('txnAppId', <stable_app_id>) and option('txnVersion', batch_id) so Delta can detect and ignore duplicate writes for the same (appId, version) pair.

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?

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

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?

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

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?

1. 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().
2. 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.
3. 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.
4. 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.
5. 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.

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?

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.