DP-750 Certification Practice Question #58

Question

You are consolidating two Delta tables in Unity Catalog that have **identical schemas**: `silver.crm.customers_eu` and `silver.crm.customers_us`. For four separate downstream deliverables, you must choose the correct Spark SQL set operator or join for each requirement. All result sets must be **deduplicated** (DISTINCT) unless a join is required. HOTSPOT — For each requirement, select the correct operation from its dropdown. ```mermaid flowchart TD subgraph Requirements R1["R1: All customers from BOTH tables
combined into one deduplicated list"] R2["R2: Only customers that appear
in BOTH tables (overlap)"] R3["R3: Customers in customers_eu
that are NOT in customers_us"] R4["R4: Enrich orders with each order's
customer attributes by matching customer_id"] end subgraph Dropdowns["Operation per row"] D1["UNION / INTERSECT / EXCEPT / JOIN"] D2["UNION / INTERSECT / EXCEPT / JOIN"] D3["UNION / INTERSECT / EXCEPT / JOIN"] D4["UNION / INTERSECT / EXCEPT / JOIN"] end R1 --> D1 R2 --> D2 R3 --> D3 R4 --> D4 ```

Accepted Answer

Set operators act on two subqueries with matching column counts/types and combine their rows: `UNION [DISTINCT]` returns all rows from both with duplicates removed (R1), `INTERSECT` returns only rows present in both inputs (R2), and `EXCEPT` returns rows in the first input not in the second (R3), all defaulting to DISTINCT. R4 is fundamentally different — it widens each order row with attributes from a related table by matching `customer_id`, which is the definition of a JOIN, not a set operator.

More DP-750 practice questions