Databricks Certified Data Engineer Associate Practice Questions: Incremental Data Processing Domain

Maintaining a separate watermark table that tracks the latest fully processed event-time allows training jobs to read `user_daily_features` up to a consistent event-time boundary for all users. The streaming job can continue to update features and incorporate late-arriving events within the 3-day window. Reading the latest data without coordination can yield inconsistent snapshots, processing-time windows break the event-time and late-data requirements, and disabling late-event handling violates the explicit 3-day inclusion requirement.

Because only about 1% of rows change daily in a very large table, incremental processing is preferred to avoid reprocessing 99% unchanged data. Using CDC to capture only inserts, updates, and deletes directly reduces load time and compute while maintaining the daily SLA. Scaling up or parallelizing a full refresh still processes all 5 TB, and reducing frequency breaks the daily refresh requirement.

A CDC-based pipeline should apply inserts, updates, and deletes to maintain a correct current state. Using MERGE on order_id with operation_type allows the table to reflect one current row per order and to remove cancelled orders, while remaining incremental and rerun-safe. Option B ignores deletes. Option C does not correctly interpret operation types. Option D abandons CDC benefits and is costly for large tables.

With at-least-once delivery, retries can re-send the same events. Making the write idempotent requires using a stable key and merge semantics so that re-sent events do not create new rows. An upsert/merge keyed by event ID, combined with deduplication within each micro-batch, ensures that each event ID appears at most once. A unique constraint (A) would cause write failures on retries. At-most-once (B) avoids duplicates by risking data loss. A daily cleanup (D) is reactive, leaves duplicates in place for long periods, and is more error-prone than designing idempotent writes.

A Lakeflow STREAMING TABLE that reads from the bronze table’s CDF stream processes only new or changed rows (inserts, updates, deletes) incrementally and supports near real-time propagation without full scans. A standard streaming read (A) sees appended data but does not expose change types as efficiently as CDF for updates/deletes. A materialized view with full scans (C) is inefficient and contradicts the no-full-scan requirement. A daily batch rebuild (D) violates the near real-time requirement.

Adding promo_code as a nullable, additive column with a default null value is a backward-compatible schema change. Existing queries continue to work, historical data naturally has nulls for promo_code, and new loads can start populating the field incrementally. Rewriting all historical files (B) or dropping and recreating the table (C) is costly and unnecessary. Making the column required (D) would break ingestion for historical or partial data that legitimately lacks promo_code.

Filtering by event_timestamp aligns the silver table with the business date of the click. Reprocessing a bounded historical window (e.g., last few days of event_timestamp) captures late-arriving events while keeping processing efficient. Option A still attributes events by arrival time. Option C continues to rely solely on ingestion order and can miss out-of-order events. Option D uses event_id, which is not guaranteed to be time-ordered.

A watermark with a 2-day delay matches the observed late-arrival pattern and allows the system to keep windows open long enough to capture most late events. The job can then incrementally update only the affected daily aggregates, providing accurate counts while bounding recomputation to a 2-day window. Using processing time (A) misattributes late events to the wrong day. Full nightly recomputation (C) is unnecessarily expensive. Increasing cluster size (D) does not address the logical issue of late-arriving data.

Partitioning on a stable, commonly filtered column like `event_date` enables effective partition pruning for incremental reads and allows better control over file sizes to avoid small-file problems. Further partitioning by `user_id` would worsen small-file issues and still not align with query filters. Removing partitioning entirely can hurt performance for large time-series datasets, and indexing `user_id` does not fix the mismatch between partitioning and query patterns in a lakehouse context.

Using updated_at > last_successful_run_timestamp reads only rows changed since the last successful run and applies them via MERGE, which correctly handles inserts and updates without scanning the full table. Option B compares to the current run time and will usually return no rows. Option C degenerates to a full refresh scan. Option D repeatedly reprocesses the entire table and appends duplicates while not handling updates.