[Solved] You wrote this incremental dbt model SQL for SCD Type 2 but it duplicates current records instead of updating:WITH new_data AS ( SELECT * FROM source_table WHERE updated_at > (SELECT MAX(updated_at)... | dbt

dbt - Advanced Patterns

You wrote this incremental dbt model SQL for SCD Type 2 but it duplicates current records instead of updating:

WITH new_data AS (
  SELECT * FROM source_table WHERE updated_at > (SELECT MAX(updated_at) FROM target_table)
),
updated_records AS (
  SELECT * FROM target_table WHERE is_current = TRUE
)
SELECT * FROM updated_records
UNION ALL
SELECT * FROM new_data

What is the main issue causing duplicates?

AIncorrect WHERE clause in new_data filtering

BMissing logic to expire old current records before inserting new ones

CUsing UNION ALL instead of UNION

DNot selecting distinct rows in updated_records

Step-by-Step Solution

Solution:

Step 1: Understand SCD Type 2 update process
Old current records must be marked expired (is_current = FALSE) before adding new versions.
Step 2: Identify missing expiration step
The query selects current records but does not update their is_current flag to FALSE, causing duplicates.
Final Answer:
Missing logic to expire old current records before inserting new ones -> Option B
Quick Check:
Expire old records before insert = Missing logic to expire old current records before inserting new ones [OK]

Quick Trick: Expire old records before inserting new versions [OK]

Common Mistakes:

MISTAKES

Thinking UNION ALL causes duplicates here
Believing WHERE clause is wrong without checking logic
Assuming distinct needed without expiration logic

Master "Advanced Patterns" in dbt

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More dbt Quizzes

You wrote this incremental dbt model SQL for SCD Type 2 but it duplicates current records instead of updating:

Step 1: Understand SCD Type 2 update process

Step 2: Identify missing expiration step

Final Answer:

Quick Check:

Want More Practice?