Answer-first summary for fast verification
Answer: The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.
The provided MERGE logic implements a Type 2 Slowly Changing Dimension (SCD). Here's why: 1. **Type 2 SCD** tracks historical changes by inserting new rows for updates and marking old rows as inactive (e.g., setting `current = false` and `end_date`). 2. The `staged_updates` subquery uses `UNION ALL` to handle two scenarios: - **First part**: Selects new records (from `updates`) to insert or update existing records via `MERGE`. - **Second part**: For records where `address` changes (compared to the current `customers`), it generates rows with `merge_key = NULL` to trigger inserts for new versions. 3. **WHEN MATCHED**: Updates existing rows (matching `customer_id`) by setting `current = false` and `end_date`, effectively closing the old record. 4. **WHEN NOT MATCHED**: Inserts new rows with updated values (e.g., new `address`) and marks them as current (`current = true`, `end_date = null`). This aligns with **Type 2 SCD** (Option B), where old values are retained as historical records (marked inactive) and new values are inserted as current records. Options A, C, D, and E incorrectly describe Type 3, Type 0, Type 1, or a flawed Type 2 implementation.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
The view updates represents an incremental batch of newly ingested data to be inserted or updated in the customers table.
The following logic is used to process these records:
MERGE INTO customers
USING (
SELECT updates.customer_id AS merge_key, updates.*
FROM updates
UNION ALL
SELECT NULL AS merge_key, updates.*
FROM updates JOIN customers
ON updates.customer_id = customers.customer_id
WHERE customers.current = true AND updates.address <> customers.address
) staged_updates
ON customers.customer_id = staged_updates.merge_key
WHEN MATCHED AND customers.current = true AND customers.address <> staged_updates.address THEN
UPDATE SET current = false, end_date = staged_updates.effective_date
WHEN NOT MATCHED THEN
INSERT (customer_id, address, current, effective_date, end_date)
VALUES (staged_updates.customer_id, staged_updates.address, true, staged_updates.effective_date, null)
MERGE INTO customers
USING (
SELECT updates.customer_id AS merge_key, updates.*
FROM updates
UNION ALL
SELECT NULL AS merge_key, updates.*
FROM updates JOIN customers
ON updates.customer_id = customers.customer_id
WHERE customers.current = true AND updates.address <> customers.address
) staged_updates
ON customers.customer_id = staged_updates.merge_key
WHEN MATCHED AND customers.current = true AND customers.address <> staged_updates.address THEN
UPDATE SET current = false, end_date = staged_updates.effective_date
WHEN NOT MATCHED THEN
INSERT (customer_id, address, current, effective_date, end_date)
VALUES (staged_updates.customer_id, staged_updates.address, true, staged_updates.effective_date, null)
Which statement describes this implementation?
A
The customers table is implemented as a Type 3 table; old values are maintained as a new column alongside the current value.
B
The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.
C
The customers table is implemented as a Type 0 table; all writes are append only with no changes to existing values.
D
The customers table is implemented as a Type 1 table; old values are overwritten by new values and no history is maintained.
E
The customers table is implemented as a Type 2 table; old values are overwritten and new customers are appended.