Databricks Data Engineer Professional Question 198
Select 3You are tasked with implementing a Slowly Changing Dimension (SCD) Type 2 table for a customer data lakehouse in Databricks. The requirements are as follows:
- Maintain a full history of changes to customer records.
- Each version of a record must have a unique identifier and valid start/end dates.
- The most recent record for a customer should have an open-ended end date.
Which of the following steps should you include in your implementation process?
- A
Generate unique keys for each record version and use these keys as primary identifiers.
- B
Update the existing record's end date to the current timestamp when a new version is inserted.
- C
Overwrite the record with the latest values whenever an update is received.
- D
Insert a new version of the record with updated values and an open-ended end date.
- E
Delete all old records and retain only the latest version of the record to save storage space.
Show answer and explanation
Correct answers: A, B, D
Explanation
SCD Type 2 is designed to maintain a full history of changes for a dimension table. This involves generating unique identifiers for each record version, updating the end date of historical records when new versions are inserted, and ensuring the most recent version has an open-ended end date. Overwriting or deleting records contradicts the purpose of an SCD Type 2 implementation.
- A. Correct.
Correct: SCD Type 2 requires maintaining a full history of changes, and generating unique keys for each record version helps track historical data.
- B. Correct.
Correct: Updating the end date of the current record marks it as historical and is a key step in implementing SCD Type 2.
- C. Incorrect.
Incorrect: Overwriting records is a characteristic of SCD Type 1, not SCD Type 2. SCD Type 2 requires maintaining historical records.
- D. Correct.
Correct: Inserting a new version of the record with updated values and an open-ended end date ensures the latest version is always identifiable.
- E. Incorrect.
Incorrect: Deleting old records violates the fundamental principle of SCD Type 2, which is to maintain the full history of changes.