Data Engineer Associate · 22% of the exam

Incremental data processing: free practice questions

5 sample questions from our 55-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Databricks exam.

1. A data engineer is working with a large Delta table `transactions` that is frequently queried with filters on `customer_id` and `transaction_date`. The table currently has many small Parquet files due to frequent streaming micro-batch writes. The engineer runs: ```sql OPTIMIZE transactions ZORDER BY (customer_id, transaction_date); ``` Which TWO outcomes should the engineer expect after this command completes? (Select TWO.)

A. Small Parquet files are compacted into larger files, reducing the number of files in the table.✓ Correct
B. Old versions of the table are automatically removed and storage is immediately reclaimed.
C. Data is co-located within each compacted file so that records with similar `customer_id` and `transaction_date` values are stored together, improving data skipping.✓ Correct
D. The command rewrites all data in fully sorted order globally across all files, like a traditional database sort.
E. Future streaming micro-batch writes will automatically apply Z-ORDER to new files as they arrive.
F. Query performance for filters on `customer_id` and `transaction_date` is improved by allowing the engine to skip more files using column statistics.

Explanation

**Option A** is correct: OPTIMIZE compacts small files into larger, more efficiently sized Parquet files, which directly addresses the small-file problem caused by streaming micro-batches. **Option C** is also correct and closely related: Z-ORDER co-locates data within compacted files so that records with similar values of the Z-ORDER columns are physically near each other, enabling the Delta engine to skip more files during queries filtered on those columns. Option B is incorrect — OPTIMIZE does not delete old file versions; that requires a separate VACUUM command. Option D is incorrect — Z-ORDER is a multi-dimensional clustering technique, not a global sort; it co-locates similar values within files but does not produce a globally sorted table. Option E is incorrect — OPTIMIZE/Z-ORDER is a one-time operation on existing data; new files written by future streaming jobs are not automatically Z-ORDERed. Option F is partially correct in describing the *benefit* but is essentially a restatement of C — the mechanism (co-location enabling file skipping via statistics) is what C describes, so C captures the correct underlying outcome.

2. Which of the following statements about **output modes** in Structured Streaming are correct? (Select TWO.)

A. `append` mode writes only new rows added since the last trigger and is valid for streaming queries without aggregations or with aggregations that use watermarks✓ Correct
B. `complete` mode rewrites the entire result table to the sink on every trigger and is typically used with stateful aggregations✓ Correct
C. `update` mode is valid for all query types including those writing to Delta tables with file-based sinks that do not support row-level updates
D. `complete` mode can be used with append-only streaming sources that have no aggregation to minimize write amplification
E. `update` mode writes only the rows that were updated or newly added since the last trigger, and requires the sink to support upserts

Explanation

Option A is correct: `append` mode emits only new rows and is valid for non-aggregated streaming queries and for aggregated queries that use watermarks (because the watermark guarantees no future updates to finalized windows). Option B is correct: `complete` mode rewrites the full result table each trigger and is the standard choice for global aggregations where every row could theoretically change. Option C is incorrect: `update` mode is NOT valid for all sinks; file-based sinks that cannot perform row-level updates (e.g., plain Parquet) do not support `update` mode — only Delta and a few other sinks that support upserts can handle it. Option D is incorrect: using `complete` mode on a non-aggregated append-only source would require Spark to buffer all rows in state indefinitely, which is not supported and would cause an error. Option E is partially true in its description of `update` mode behavior but overstates the requirement — Delta tables support `update` mode without requiring the user to implement custom upsert logic; the requirement is on the sink capability, not necessarily explicit user-coded upserts.

3. A data engineer is implementing Change Data Capture in a DLT pipeline using the following command: ```sql APPLY CHANGES INTO LIVE.silver_customers FROM STREAM(LIVE.bronze_cdc_customers) KEYS (customer_id) SEQUENCE BY change_timestamp IGNORE NULL UPDATES STORED AS SCD TYPE 2; ``` What does the `STORED AS SCD TYPE 2` clause change about how records are maintained in the `silver_customers` table compared to the default behavior?

A. It enables out-of-order CDC event handling by buffering events and applying them after sorting by `change_timestamp`.
B. Instead of overwriting existing rows with updates (SCD Type 1), it retains the full history of changes by adding new rows for each update and marking previous rows with an end date, allowing point-in-time queries.✓ Correct
C. It causes the target table to be stored as a Materialized View instead of a Streaming Table, enabling full recomputation on each pipeline run.
D. It partitions the target table by `change_timestamp` to improve the performance of historical range queries.

Explanation

**Correct: B.** By default, `APPLY CHANGES INTO` implements SCD Type 1 behavior — incoming changes overwrite existing rows for the matching key. Adding `STORED AS SCD TYPE 2` changes this to retain history: each update creates a new row with the new values, and the previous row is closed out with an end timestamp (via `__END_AT` column), enabling full historical tracking and point-in-time queries. **A** is wrong because out-of-order handling is controlled by the `SEQUENCE BY` clause and is available in both SCD Type 1 and Type 2; it is not specific to `STORED AS SCD TYPE 2`. **C** is wrong because `STORED AS SCD TYPE 2` has no effect on whether the target is a Streaming Table or Materialized View; `APPLY CHANGES INTO` always targets a Streaming Table. **D** is wrong because `STORED AS SCD TYPE 2` does not add any partitioning to the target table.

4. A data engineer uses Auto Loader to ingest CSV files into a Bronze Delta table. The source files occasionally include new columns that were not present in the initial schema. The engineer wants Auto Loader to automatically detect new columns and add them to the target table without failing the pipeline. Which configuration option enables this behavior?

A. Set `cloudFiles.schemaEvolutionMode` to `addNewColumns`✓ Correct
B. Set `cloudFiles.inferColumnTypes` to `true`
C. Set `cloudFiles.schemaEvolutionMode` to `rescue`
D. Set `mergeSchema` to `true` in the write options

Explanation

**Correct: A.** Setting `cloudFiles.schemaEvolutionMode` to `addNewColumns` instructs Auto Loader to automatically detect newly introduced columns in incoming files and add them to the target Delta table schema, allowing the pipeline to continue without interruption. **B** is wrong because `cloudFiles.inferColumnTypes` controls whether column data types are inferred from the data, not whether new columns are added to the schema. **C** is wrong because `rescue` mode captures unmatched columns into a special `_rescued_data` column instead of adding them as new columns to the schema. **D** is wrong because while `mergeSchema` is a valid Delta write option that handles schema evolution at write time, Auto Loader's schema evolution for new columns is controlled via its own `cloudFiles.schemaEvolutionMode` setting, and relying solely on `mergeSchema` without the Auto Loader config will cause the stream to fail when new columns appear.

5. A data engineer is building a Silver layer DLT pipeline that reads from a Bronze streaming table. They want the Silver table to support incremental processing — only consuming new records appended to Bronze — rather than reprocessing the entire Bronze table on every pipeline update. How should the Silver table be declared?

A. As a Streaming Table using `dlt.create_streaming_table()` or `@dlt.table` with `spark.readStream`✓ Correct
B. As a Materialized View using `@dlt.table` with a batch `spark.read` source
C. As a standard Delta table created with `CREATE TABLE AS SELECT` outside the DLT pipeline
D. As a Streaming Table, but only if the Bronze source is a Kafka topic; Delta table sources must use Materialized Views

Explanation

A Streaming Table in DLT consumes data incrementally using Structured Streaming semantics. By reading from the Bronze source with `spark.readStream`, the Silver table only processes new records since the last pipeline run, which is the incremental processing behavior required. Option B is incorrect: a Materialized View always re-executes its defining query against the full source on each pipeline update — it does not maintain streaming offsets and therefore cannot do incremental processing in the same sense. Option C is incorrect: creating a table outside DLT with CTAS loses all DLT pipeline management, lineage, and incremental processing capabilities. Option D is a fabrication — DLT Streaming Tables work perfectly well with Delta table sources via `spark.readStream`; there is no restriction that limits Streaming Tables to Kafka sources.

50 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free