CERTIFIED-DATA-ENGINEER-PROFESSIONAL · Question #100
The view updates represents an incremental batch of all newly ingested data to be inserted or updated in the customers table. The following logic is used to process these records. MERGE INTO…
The correct answer is C. The customers table is implemented as a Type 2 table; old values are maintained but marked as. The provided MERGE statement is a classic implementation of a Type 2 SCD in a data warehousing context. In this approach, historical data is preserved by keeping old records (marking them as not current) and adding new records for changes. Specifically, when a match is found…
Question
The view updates represents an incremental batch of all 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_ey, 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 = mergekey 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?
Options
- AThe customers table is implemented as a Type 2 table; old values are overwritten and new
- BThe customers table is implemented as a Type 1 table; old values are overwritten by new values
- CThe customers table is implemented as a Type 2 table; old values are maintained but marked as
- DThe customers table is implemented as a Type 0 table; all writes are append only with no
How the community answered
(21 responses)- A5% (1)
- B5% (1)
- C81% (17)
- D10% (2)
Explanation
The provided MERGE statement is a classic implementation of a Type 2 SCD in a data warehousing context. In this approach, historical data is preserved by keeping old records (marking them as not current) and adding new records for changes. Specifically, when a match is found and there's a change in the address, the existing record in the customers table is updated to mark it as no longer current (current = false), and an end date is assigned (end_date = staged_updates.effective_date). A new record for the customer is then inserted with the updated information, marked as current. This method ensures that the full history of changes to customer information is maintained in the table, allowing for time-based analysis of customer data.
Topics
Community Discussion
No community discussion yet for this question.