Slowly changing dimensions (SCD Type 2) in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this SCD Type 2 merge logic?

Given the following dbt SQL snippet for SCD Type 2, what will be the count of active records after running this merge?

dbt

merge into target_table as tgt
using source_table as src
on tgt.customer_id = src.customer_id and tgt.is_current = true
when matched and (tgt.address != src.address) then
  update set tgt.is_current = false, tgt.end_date = current_date
when not matched then
  insert (customer_id, address, start_date, end_date, is_current)
  values (src.customer_id, src.address, current_date, null, true);

A The count of active records equals the total rows in target_table before merge.

B The count of active records equals the number of unique customers in source_table.

C The count of active records equals zero after the merge.

D The count of active records equals the number of rows in source_table plus target_table.

💡 Hint

Think about how SCD Type 2 keeps one active record per customer.

SCD Type 2 logic marks old records as inactive and inserts new active records for changed data. So, after merge, active records correspond to unique customers in source.

❓ data_output

intermediate

How many records will be in the target after this SCD Type 2 update?

Assume target_table has 3 customers with 1 record each (all active). source_table has 2 customers with changed addresses and 1 unchanged. After running the SCD Type 2 merge, how many total records will target_table have?

A 3 records

B 4 records

C 6 records

D 5 records

💡 Hint

Each changed customer adds a new record, unchanged stays the same.

Two customers changed, so their old records become inactive and new records inserted. One unchanged customer keeps one record. Total = 3 original + 2 new = 5.

🔧 Debug

advanced

Identify the error in this SCD Type 2 dbt model SQL

This dbt model attempts to implement SCD Type 2 but has a bug. What error will occur when running it?

dbt

with source as (
  select * from {{ ref('source_table') }}
),
updates as (
  select src.customer_id, src.address
  from source src
  join {{ ref('target_table') }} tgt
    on src.customer_id = tgt.customer_id
  where src.address != tgt.address and tgt.is_current = true
)

select * from updates;

A Runtime error due to ambiguous column reference in join.

B Syntax error due to missing alias in join.

C No error, query runs and returns changed customers.

D Empty result because join condition misses is_current filter on source.

💡 Hint

Check join conditions and filters carefully.

The query correctly joins source and target on customer_id and filters for changed addresses where target is current. No syntax or runtime errors occur.

🚀 Application

advanced

Which dbt configuration best supports SCD Type 2 incremental model?

You want to build an incremental dbt model for SCD Type 2. Which configuration option is most appropriate to handle updates and inserts correctly?

A Use incremental strategy with 'merge' and define unique key on customer_id and is_current flag.

B Use full-refresh strategy to reload entire table every run.

C Use incremental strategy with 'insert_overwrite' partitioned by customer_id.

D Use ephemeral materialization to avoid storing data.

💡 Hint

SCD Type 2 needs to update existing records and insert new ones.

Incremental with merge and unique keys allows updating old records and inserting new ones, which fits SCD Type 2 logic.

🧠 Conceptual

expert

Why is SCD Type 2 important for historical data analysis?

Choose the best explanation for why SCD Type 2 is used in data warehouses.

A It preserves full history of changes by keeping multiple records per entity with time validity.

B It overwrites old data to keep only the latest snapshot for simplicity.

C It compresses data by removing duplicates and keeping one record per entity.

D It stores only the changes as separate delta tables for faster queries.

💡 Hint

Think about how historical changes are tracked over time.

SCD Type 2 keeps old records with end dates and inserts new records for changes, preserving full history for analysis.