Explanation:
The provided MERGE logic implements a Type 2 Slowly Changing Dimension (SCD). Here's why:
current = false and end_date).staged_updates subquery uses UNION ALL to handle two scenarios:
updates) to insert or update existing records via MERGE.address changes (compared to the current customers), it generates rows with merge_key = NULL to trigger inserts for new versions.customer_id) by setting current = false and end_date, effectively closing the old record.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.
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.
No comments yet.