[Solved] Which of the following is the correct syntax to mark a record as expired in SCD Type 2 using SQL? - Ans: UPDATE dim_table SET end_date = CURRENT_DATE WHERE id = 123 AND end_date IS... | dbt

dbt - Advanced Patterns

Which of the following is the correct syntax to mark a record as expired in SCD Type 2 using SQL?

ASELECT * FROM dim_table WHERE end_date IS NULL;

BINSERT INTO dim_table VALUES (123, 'John', CURRENT_DATE, NULL);

CDELETE FROM dim_table WHERE id = 123;

DUPDATE dim_table SET end_date = CURRENT_DATE WHERE id = 123 AND end_date IS NULL;

Step-by-Step Solution

Solution:

Step 1: Understand expiration in SCD Type 2 and check SQL options
To expire a record, we set its end_date to the current date, marking it no longer active. UPDATE dim_table SET end_date = CURRENT_DATE WHERE id = 123 AND end_date IS NULL; updates the end_date correctly. Others either insert, delete, or select data, not expire.
Final Answer:
UPDATE dim_table SET end_date = CURRENT_DATE WHERE id = 123 AND end_date IS NULL; -> Option D
Quick Check:
Expire record = update end_date [OK]

Quick Trick: Expire by updating end_date, not deleting [OK]

Common Mistakes:

MISTAKES

Master "Advanced Patterns" in dbt

9 interactive learning modes - each teaches the same concept differently

More dbt Quizzes

Which of the following is the correct syntax to mark a record as expired in SCD Type 2 using SQL?