Slowly changing dimensions (SCD Type 2) in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the source table.

dbt

SELECT [1] FROM source_table

Drag options to blanks, or click blank then click option'

A*

Ball

Ccolumns

Deverything

Attempts:

3 left

💡 Hint

Common Mistakes

Writing 'all' instead of '*'

Using 'columns' which is not valid SQL syntax

Using '*' selects all columns from the table.

2fill in blank

medium

Complete the code to add a new column 'is_current' with value 1 for current records.

dbt

SELECT *, 1 AS [1] FROM source_table

Drag options to blanks, or click blank then click option'

Acurrent_flag

Bis_current

Cactive

Dflag

Attempts:

3 left

💡 Hint

Common Mistakes

Using generic names like 'flag' that are unclear

Using 'current_flag' which is less common in dbt SCD patterns

The column 'is_current' is commonly used to mark current records in SCD Type 2.

3fill in blank

hard

Fix the error in the WHERE clause to filter only current records.

dbt

SELECT * FROM dimension_table WHERE is_current [1] 1

Drag options to blanks, or click blank then click option'

A!=

B==

C=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' which causes syntax errors

Using '<>' which means 'not equal'

In SQL, '=' is used for equality comparison, not '==' which is invalid.

4fill in blank

hard

Complete the code to add valid_from and valid_to columns for new records in SCD Type 2.

dbt

SELECT *, [1] AS valid_from, [2] AS valid_to FROM source_table

Drag options to blanks, or click blank then click option'

ACURRENT_TIMESTAMP()

B'9999-12-31'

CCURRENT_DATE()

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL for valid_to (used for expired records instead)

Using CURRENT_DATE() which lacks time precision

'CURRENT_TIMESTAMP()' sets the start of validity, and a far-future date like '9999-12-31' marks current records in SCD Type 2.

5fill in blank

hard

Fill all three blanks to generate a surrogate key for SCD Type 2 using dbt_utils.

dbt

{{ dbt_utils.surrogate_key([[1], [2], [3]]) }} AS surrogate_key

Drag options to blanks, or click blank then click option'

A'customer_id'

B'name'

C'email'

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting single quotes around column names (e.g., customer_id)

Including irrelevant fields like phone instead of changing attributes

dbt_utils.surrogate_key requires a list of quoted column names (natural key + changing attributes) to generate a unique surrogate key.

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