Data Transformation and Modeling
Free Databricks Certified Data Engineer Associate practice — 6 questions on Data Transformation and Modeling, with explanations. No sign-up. Full 12-question mixed test →
A data engineer needs to update a Delta table `customers_silver` from a CDC feed `customers_updates`. Records with `operation = 'DELETE'` should remove matching rows from the target, updates should overwrite existing rows, and new records should be inserted — all in a single MERGE operation. Which statement correctly implements this?
Ordering the WHEN MATCHED clauses so the DELETE condition is evaluated first (with an AND predicate on operation) correctly routes delete records to DELETE while all other matches fall through to UPDATE, and unmatched rows are INSERTed — all atomically in one MERGE.
Table `events_raw` contains duplicate rows per `event_id` due to at-least-once delivery. To keep exactly one row per `event_id` — the one with the maximum `event_ts` — while preserving all other columns, which PySpark snippet is correct?
row_number() over a window partitioned by event_id and ordered by event_ts descending assigns a unique rank per partition even when ties exist, guaranteeing exactly one row per event_id is kept while retaining all original columns.
A Lakeflow Job runs a Structured Streaming query reading from a bronze Delta table that must upsert aggregated results into a gold Delta table. Because MERGE is not natively supported as a streaming sink, which implementation correctly achieves incremental upserts?
foreachBatch exposes each micro-batch as a static DataFrame, allowing the MERGE API to be called against it; this is the documented pattern for performing upserts from a streaming source into a Delta sink.
Column `tags` is an ARRAY<STRING>. You need a new column `tags_upper` containing only the tags longer than 3 characters, converted to uppercase, computed in a single SQL expression. Which expression is correct?
filter() first removes elements not meeting the length condition, then transform() applies upper() to each remaining element, correctly composing the two higher-order functions to meet both requirements in one expression.
In a Lakeflow Declarative Pipeline, a data engineer chooses CREATE OR REFRESH MATERIALIZED VIEW for one object and CREATE OR REFRESH STREAMING TABLE for another. What is the key operational difference between these two object types?
This is the documented distinction: materialized views maintain correctness of a query result over batch semantics (which may involve recomputation), whereas streaming tables leverage Structured Streaming's incremental, append-only processing model for near-real-time ingestion of new records.
A silver-layer query LEFT JOINs `orders` to `customers` on `customer_id`. Some orders have a NULL `customer_id` from guest checkouts. When the result is grouped by `c.customer_name` to sum order totals, all guest orders disappear from the output instead of appearing as a distinct 'Guest' bucket. Without changing the join type, what fixes this?
GROUP BY treats NULL as its own group already, so guest orders were not actually being dropped by the join — they were grouped under a NULL customer_name label that is easy to overlook or filter out downstream. Wrapping the grouping/select column in COALESCE explicitly labels that NULL group as 'Guest', making it visible and correctly aggregated as a distinct, identifiable bucket.
Ready for the real thing?
The full course has two full-length practice tests, video lessons for every exam domain, hands-on labs and detailed answer explanations.
Start my full course on Udemy →