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.