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.

Practice

1. What is the main purpose of implementing Slowly Changing Dimension (SCD) Type 2 in a data warehouse?

easy

A. To keep full history of changes by adding new rows for each change

B. To overwrite old data with new data without keeping history

C. To delete old records when data changes

D. To aggregate data for faster queries

Solution

1. Step 1: Understand SCD Type 2 concept

   SCD Type 2 tracks changes by adding new rows instead of overwriting existing data.

2. Step 2: Compare options with concept

   Only To keep full history of changes by adding new rows for each change describes adding new rows to keep full history, which matches SCD Type 2.

3. Final Answer:

   To keep full history of changes by adding new rows for each change -> Option A

4. Quick Check:

   SCD Type 2 = Add new rows for history [OK]

Hint: Remember: SCD Type 2 adds rows, not overwrites [OK]

Common Mistakes:

• Confusing SCD Type 2 with Type 1 which overwrites data
• Thinking SCD deletes old records
• Assuming SCD aggregates data

2. Which SQL clause is essential in a dbt model to implement incremental loading for SCD Type 2?

easy

A. GROUP BY customer_id

B. HAVING COUNT(*) > 1

C. ORDER BY created_at DESC

D. WHERE updated_at > (SELECT MAX(updated_at) FROM target_table)

Solution

1. Step 1: Identify incremental loading condition

   Incremental models use a WHERE clause to filter new or changed rows based on a timestamp.

2. Step 2: Match clause to SCD Type 2 incremental logic

   WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) filters rows with updated_at greater than the max in the target, enabling incremental load.

3. Final Answer:

   WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) -> Option D

4. Quick Check:

   Incremental WHERE clause = WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) [OK]

Hint: Use WHERE with max timestamp for incremental loads [OK]

Common Mistakes:

• Using GROUP BY instead of filtering new rows
• Ordering data without filtering
• Using HAVING without aggregation context

3. Given this simplified SQL snippet in a dbt model for SCD Type 2:

SELECT
  id,
  name,
  effective_from,
  effective_to,
  is_current
FROM customer_scd
WHERE is_current = TRUE

What will this query return?

medium

A. Only records where effective_to is NULL

B. All historical records including old versions

C. Only the latest active records for each customer

D. Records with duplicate customer IDs

Solution

1. Step 1: Analyze WHERE clause filtering

   The query filters rows where is_current = TRUE, meaning only the latest active records.

2. Step 2: Understand SCD Type 2 flags

   In SCD Type 2, is_current marks the latest version; older versions have FALSE.

3. Final Answer:

   Only the latest active records for each customer -> Option C

4. Quick Check:

   is_current = TRUE returns latest records [OK]

Hint: Filter is_current = TRUE for latest records [OK]

Common Mistakes:

• Assuming it returns all history
• Confusing effective_to NULL with is_current TRUE
• Ignoring the is_current flag meaning

4. 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?

medium

A. Incorrect WHERE clause in new_data filtering

B. Missing logic to expire old current records before inserting new ones

C. Using UNION ALL instead of UNION

D. Not selecting distinct rows in updated_records

Solution

1. Step 1: Understand SCD Type 2 update process

   Old current records must be marked expired (is_current = FALSE) before adding new versions.

2. Step 2: Identify missing expiration step

   The query selects current records but does not update their is_current flag to FALSE, causing duplicates.

3. Final Answer:

   Missing logic to expire old current records before inserting new ones -> Option B

4. Quick Check:

   Expire old records before insert = Missing logic to expire old current records before inserting new ones [OK]

Hint: Expire old records before inserting new versions [OK]

Common Mistakes:

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

5. You want to implement SCD Type 2 in dbt for a customer dimension. Which combination of columns should you include to correctly track history and identify current records?

hard

A. customer_id, name, effective_from, effective_to, is_current

B. customer_id, name, last_updated, deleted_flag

C. customer_id, name, created_at, updated_at

D. customer_id, name, version_number, is_active

Solution

1. Step 1: Identify key SCD Type 2 columns

   SCD Type 2 needs effective_from and effective_to timestamps plus a flag like is_current to mark active rows.

2. Step 2: Compare options for completeness

   customer_id, name, effective_from, effective_to, is_current includes customer_id, name, effective_from, effective_to, and is_current, covering history and current record tracking.

3. Final Answer:

   customer_id, name, effective_from, effective_to, is_current -> Option A

4. Quick Check:

   Effective dates + is_current = customer_id, name, effective_from, effective_to, is_current [OK]

Hint: Use effective dates and is_current flag for SCD Type 2 [OK]

Common Mistakes:

• Using only timestamps without current flag
• Confusing deleted_flag with is_current
• Using version_number without effective dates