Slowly changing dimensions (SCD Type 2) in dbt - Mini Project: Build & Apply

Slowly Changing Dimensions (SCD Type 2) with dbt

📖 Scenario: You work as a data analyst for an online store. The store keeps track of customer information like their city and membership level. Sometimes customers move to a new city or change their membership level. You want to keep a history of these changes in your data warehouse.This is called Slowly Changing Dimensions Type 2 (SCD Type 2). It means you keep old records and add new records when customer info changes.

🎯 Goal: Build a dbt model that implements SCD Type 2 for a customers table. Your model will detect changes in customer city or membership level and keep historical records with start and end dates.

📋 What You'll Learn

Create a source table called raw_customers with customer_id, city, membership_level, and effective_date columns.

Create a variable current_date to represent the current processing date.

Write a dbt model SQL query that compares new data with existing data to detect changes in city or membership_level.

Output the full SCD Type 2 table with columns: customer_id, city, membership_level, start_date, end_date, and is_current.

💡 Why This Matters

🌍 Real World

SCD Type 2 is used in data warehouses to keep full history of changes in important data like customers, products, or employees.

💼 Career

Data engineers and analysts use SCD Type 2 to build reliable historical datasets for reporting and analysis.

1

Create the source table raw_customers

Create a source table called raw_customers with these exact rows:
(1, 'New York', 'Gold', '2024-01-01'), (2, 'Los Angeles', 'Silver', '2024-01-01'), (1, 'Boston', 'Gold', '2024-06-01').
Use columns customer_id, city, membership_level, and effective_date.

dbt

# Create the raw_customers source table with the exact rows
# Your code here

Hint

Use a CTE named raw_customers with union all to combine rows.

2

Define the current_date variable

Create a variable called current_date and set it to the date '2024-07-01'.

dbt

with raw_customers as (
    select 1 as customer_id, 'New York' as city, 'Gold' as membership_level, '2024-01-01'::date as effective_date
    union all
    select 2, 'Los Angeles', 'Silver', '2024-01-01'
    union all
    select 1, 'Boston', 'Gold', '2024-06-01'
)

-- Define current_date variable
-- Your code here

Hint

Create a CTE named current_date with a single date value.

3

Write the SCD Type 2 logic to detect changes

Write a SQL query that:
- Joins raw_customers with the existing SCD table (assume empty for first run)
- Detects changes in city or membership_level
- Assigns start_date as effective_date from raw_customers
- Sets end_date to NULL for current records
- Sets is_current to TRUE for current records
Use customer_id, city, membership_level, start_date, end_date, and is_current columns.

dbt

with raw_customers as (
    select 1 as customer_id, 'New York' as city, 'Gold' as membership_level, '2024-01-01'::date as effective_date
    union all
    select 2, 'Los Angeles', 'Silver', '2024-01-01'
    union all
    select 1, 'Boston', 'Gold', '2024-06-01'
),
current_date as (
    select '2024-07-01'::date as current_date
)

-- Write SCD Type 2 logic below
-- Your code here

Hint

For this beginner project, assume the existing SCD table is empty and just select from raw_customers with added columns.

4

Output the final SCD Type 2 table

Write a select statement to output all columns from the scd CTE: customer_id, city, membership_level, start_date, end_date, and is_current.

dbt

with raw_customers as (
    select 1 as customer_id, 'New York' as city, 'Gold' as membership_level, '2024-01-01'::date as effective_date
    union all
    select 2, 'Los Angeles', 'Silver', '2024-01-01'
    union all
    select 1, 'Boston', 'Gold', '2024-06-01'
),
current_date as (
    select '2024-07-01'::date as current_date
),
scd as (
    select
        customer_id,
        city,
        membership_level,
        effective_date as start_date,
        null::date as end_date,
        true as is_current
    from raw_customers
)

-- Output the final SCD table
# Your code here

Hint

Use select customer_id, city, membership_level, start_date, end_date, is_current from scd.

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