Noos
PT EN
RAN-DBDEA-00049
Ranvier · DBDEA

Exam DBDEA Topics: transformation Difficulty: medium

Every night, catalog.ops.order_updates arrives with changed rows for existing order_id keys plus brand-new orders. catalog.ops.silver_orders must reflect the updates in place and contain the new orders — with no duplicate order_id values. All tables are Delta tables governed by Unity Catalog. Which SQL statement is designed for this requirement?

Options (Reading Mode) Click to filter discussion →
  1. A. MERGE INTO catalog.ops.silver_orders AS t USING catalog.ops.order_updates AS s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET t.status = s.status, t.amount = s.amount WHEN NOT MATCHED THEN INSERT *
  2. B. INSERT INTO catalog.ops.silver_orders SELECT * FROM catalog.ops.order_updates
  3. C. DELETE FROM catalog.ops.silver_orders WHERE order_id IN (SELECT order_id FROM catalog.ops.order_updates); then INSERT INTO catalog.ops.silver_orders SELECT * FROM catalog.ops.order_updates
  4. D. CREATE OR REPLACE TABLE catalog.ops.silver_orders AS SELECT * FROM catalog.ops.silver_orders UNION ALL SELECT * FROM catalog.ops.order_updates
Reveal answer

Correct answer: MERGE INTO catalog.ops.silver_orders AS t USING catalog.ops.order_updates AS s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET t.status = s.status, t.amount = s.amount 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 (A): MERGE INTO catalog.ops.silver_orders AS t USING catalog.ops.order_updates AS s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET t.status = s.status, t.amount = s.amount 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.