Noos
PT EN
RAN-DBDEA-00144
Ranvier · DBDEA

Exam DBDEA Topics: transformation Difficulty: hard

A reconciliation job must align catalog.sales.silver_orders with the source extract catalog.sales.latest_orders in one atomic pass: matched orders get their status refreshed, matched orders whose source status is 'CANCELLED' must be removed from the target, and unmatched source orders must be inserted. ```sql MERGE INTO catalog.sales.silver_orders AS t USING catalog.sales.latest_orders AS s ON t.order_id = s.order_id -- WHEN clauses omitted ``` Which approach meets all three requirements in this single atomic pass?

Options (Reading Mode) Click to filter discussion →
  1. A. Run two MERGE statements in sequence: one that updates matched rows and inserts new ones, then one that deletes the cancelled orders
  2. B. Use a single MERGE with WHEN MATCHED THEN UPDATE SET * and WHEN NOT MATCHED THEN INSERT *, issuing a separate DELETE for the cancelled orders
  3. C. Use a single MERGE with WHEN MATCHED AND s.status = 'CANCELLED' THEN DELETE, then WHEN MATCHED THEN UPDATE SET *, then WHEN NOT MATCHED THEN INSERT *
  4. D. Replace the MERGE with an UPDATE on the matched rows followed by REPLACE WHERE, rewriting the table with the surviving and new orders
Reveal answer

Correct answer: Use a single MERGE with WHEN MATCHED AND s.status = 'CANCELLED' THEN DELETE, then WHEN MATCHED THEN UPDATE SET *, then WHEN NOT MATCHED THEN INSERT *

Reference: https://docs.databricks.com/aws/en/delta/merge

Train this exam on Ranvier →

02 — The Myelin Layer · Canonical Discussion

Where the exam tries to trick you — and how those who passed solved it.

0 Passed

contributed to this question

Answer Distractor Matrix Click an option to filter or open its dissection.
✦ Consolidated Synthesis (Wiki) rev. #1 (baseline) · CC BY-SA 4.0
Start synthesis for this question →

No community synthesis consolidated for this question yet. Below is the verifiable official starting point — if you passed the exam, open the option dissection.

Official Answer (C): Use a single MERGE with WHEN MATCHED AND s.status = 'CANCELLED' THEN DELETE, then WHEN MATCHED THEN UPDATE SET *, then WHEN NOT MATCHED THEN INSERT *

⚓ Official Documentation Anchor: https://docs.databricks.com/aws/en/delta/merge
Ranvier · DBDEA

Contribute to this Question

Your contribution builds your Orbital Sheath

Peer contributions

No public contribution yet. Be the first who passed to explain why the distractors trick candidates — your contribution builds your Orbital Sheath.