Databricks-DE-Assoc Data Transformation and Modeling Practice Question
A data engineer is building a Gold aggregate table that summarizes daily sales by product category. The Silver source is a streaming Delta table that receives late-arriving events up to 48 hours old. The engineer needs the Gold table to always reflect the most accurate aggregates, including corrections for late data, without full recomputation. Which approach is most appropriate?
⚠ Common exam trap
The trap here is assuming Structured Streaming output modes alone can maintain an accurate aggregate table, when late-arriving corrections require explicit upsert logic in `foreachBatch`.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use `foreachBatch` with a MERGE that upserts the recomputed aggregates for affected date and category keys from the latest micro-batch.
`foreachBatch` combined with a keyed MERGE gives per-micro-batch control, so only aggregates for keys touched by the batch are recomputed and upserted. This accommodates late-arriving events within the batch window without rewriting the entire Gold table. Complete mode, append mode, or watermark-bounded update mode either recompute everything or fail to persist corrections durably.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a watermark of 48 hours with `update` output mode to emit only changed aggregate rows.
Why it's wrong here
`update` output mode emits changed rows but only for stateful aggregations within the watermark window, and it does not maintain a durable aggregate table with corrections for arbitrarily late data beyond state retention. Once state expires, late events are dropped. It also cannot perform the keyed upsert into a Gold Delta table that the scenario requires.
- ✗
Use a Structured Streaming query with `complete` output mode writing to the Gold Delta table.
Why it's wrong here
Complete output mode rewrites the entire result table on every micro-batch, which is effectively full recomputation and becomes unsustainable as the aggregate grows. It also holds all state in memory and cannot easily incorporate late data beyond the watermark. This does not meet the no-full-recomputation requirement.
- ✗
Use `append` output mode and let the BI layer sum the raw daily rows at query time.
Why it's wrong here
Appending raw events pushes aggregation to the BI layer, which is slower and can double-count late-arriving corrections. It also means the Gold table is not actually an aggregate, contradicting the modeling requirement. The approach avoids recomputation but at the cost of correctness and performance.
- ✓
Use `foreachBatch` with a MERGE that upserts the recomputed aggregates for affected date and category keys from the latest micro-batch.
Why this is correct
`foreachBatch` lets you apply arbitrary logic per micro-batch, and a MERGE keyed on date and category updates only the affected aggregate rows. This handles late-arriving events by recomputing and upserting just the keys present in the batch. It avoids full table recomputation and preserves Delta Lake ACID guarantees on the Gold table.
About these practice questions
Courseiva writes every Databricks-DE-Assoc question from scratch — 276 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-DE-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-DE-Assoc exam.