DP-750 Certification Practice Question #48

Question

A bronze streaming table `customers_cdc_clean` contains change data capture records emitted by Debezium from a MySQL `customers` table. Each record has columns `id`, `operation` (`INSERT`, `UPDATE`, or `DELETE`), `operation_date`, and the customer attributes. You must materialize a `customers` streaming table in a Lakeflow Spark Declarative Pipeline that:

- Upserts inserts and updates keyed on `id`.
- Deletes a row from the target when `operation = "DELETE"`.
- Resolves out-of-order events using `operation_date`.
- Keeps only the current version of each record (no history).

Which SQL flow definition correctly implements these requirements?

Accepted Answer

The `AUTO CDC ... INTO` API (formerly `APPLY CHANGES INTO`) declaratively processes a change data feed into a target streaming table. `KEYS (id)` defines the upsert key, `APPLY AS DELETE WHEN operation = "DELETE"` removes matched rows, `SEQUENCE BY operation_date` orders out-of-sequence events, and `STORED AS SCD TYPE 1` keeps only the current record (no history). Option C's SCD TYPE 2 would retain history and lacks delete handling; an imperative MERGE (D) would require custom code to deduplicate and order events, which is exactly what AUTO CDC eliminates.

More DP-750 practice questions