Slowly changing dimensions (SCD Type 2) in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Slowly Changing Dimension (SCD) Type 2?

SCD Type 2 is a method to track historical changes in data by creating new records for each change instead of overwriting old data. This keeps full history of changes.

beginner

Why use SCD Type 2 in data warehousing?

To keep a full history of changes in dimension data, allowing analysis of data as it was at any point in time.

intermediate

In SCD Type 2, what columns are commonly added to track changes?

Columns like 'effective_date', 'end_date', and 'current_flag' are added to mark when a record is valid and if it is the current version.

intermediate

How does dbt help implement SCD Type 2?

dbt automates SCD Type 2 using snapshots to detect changes and create new records.

beginner

What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites old data with new data (no history), while Type 2 keeps old data by adding new records for changes (full history).

What does SCD Type 2 do when a dimension attribute changes?

A Ignores the change

B Deletes the old record

C Updates the existing record

D Creates a new record with the change

SCD Type 2 creates a new record to keep the history of changes.

Which column is NOT typically used in SCD Type 2?

A password

B effective_date

C current_flag

D end_date

Password is unrelated to tracking changes in SCD Type 2.

In dbt, what feature helps implement SCD Type 2?

A Incremental models

B Snapshots

C Seeds

D Tests

Snapshots are designed to capture historical data changes, implementing SCD Type 2.

What is the main benefit of SCD Type 2?

A Keeping full history of changes

B Less storage space

C Faster queries

D Simpler data models

SCD Type 2 keeps full history by storing multiple versions of records.

Which SCD type overwrites old data without keeping history?

A Type 4

B Type 2

C Type 1

D Type 3

Type 1 overwrites old data, losing history.

Explain how Slowly Changing Dimension Type 2 works and why it is useful.

Think about how you keep old versions of a document instead of deleting them.

Describe how you would implement SCD Type 2 using dbt.

Consider how dbt can add new rows instead of replacing existing ones.

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