dbt-utils (surrogate_key, pivot, unpivot) - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of surrogate_key with multiple columns

Given the following dbt SQL snippet using dbt_utils.surrogate_key, what is the output of the surrogate key for the row where id = 2 and date = '2024-01-01'? Assume the hash function produces lowercase hex strings.

select {{ dbt_utils.surrogate_key(['id', 'date']) }} as sk from (select 2 as id, '2024-01-01' as date) as t

dbt

select {{ dbt_utils.surrogate_key(['id', 'date']) }} as sk from (select 2 as id, '2024-01-01' as date) as t

A '2|2024-01-01' hashed as 'e3b0c44298fc1c149afbf4c8996fb924'

B '2|2024-01-01' hashed as 'a7f5f35426b927411fc9231b56382173'

C '2-2024-01-01' hashed as 'd2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2'

D '2,2024-01-01' hashed as 'a7f5f35426b927411fc9231b56382173'

💡 Hint

The surrogate_key macro concatenates columns with a pipe '|' and hashes the string.

The dbt_utils.surrogate_key macro concatenates the values with '|' and then applies a hash function. For id=2 and date='2024-01-01', the string is '2|2024-01-01'. The hash of this string is 'a7f5f35426b927411fc9231b56382173'.

❓ data_output

intermediate

Result of pivot macro on sales data

Consider a table sales with columns region, product, and sales_amount. Using dbt_utils.pivot to pivot product values into columns with sum of sales_amount, what is the resulting table output for this data?

Data:
region | product | sales_amount
North | A | 100
North | B | 150
South | A | 200
South | B | 50

dbt

select * from {{ dbt_utils.pivot('sales', 'product', 'sales_amount', 'region', 'sum') }}

A [{'region': 'North', 'A': 100, 'B': 0}, {'region': 'South', 'A': 200, 'B': 50}]

B [{'region': 'North', 'A': 250, 'B': 0}, {'region': 'South', 'A': 0, 'B': 200}]

C [{'region': 'North', 'A': 100, 'B': 150}, {'region': 'South', 'A': 200, 'B': 50}]

D [{'region': 'North', 'A': 100, 'B': 150}, {'region': 'South', 'A': 50, 'B': 200}]

💡 Hint

Pivot creates columns for each product with aggregated sales_amount per region.

The pivot macro sums sales_amount grouped by region and product. For North, A=100 and B=150; for South, A=200 and B=50.

🔧 Debug

advanced

Error raised by unpivot macro misuse

What error will this dbt SQL code raise?

select * from {{ dbt_utils.unpivot('sales', ['A', 'B'], 'product', 'sales_amount') }}

Assume sales table has columns region, A, B.

dbt

select * from {{ dbt_utils.unpivot('sales', ['A', 'B'], 'product', 'sales_amount') }}

A Runtime error: column 'region' not found in unpivoted output

B No error, returns unpivoted table with columns region, product, sales_amount

C Syntax error: invalid macro usage

D Compilation error: missing group by columns

💡 Hint

Unpivot requires specifying columns to keep besides the unpivoted columns.

The unpivot macro does not automatically include columns like 'region' unless specified. This causes a runtime error when referencing 'region' after unpivoting.

🚀 Application

advanced

Using surrogate_key in incremental model

You want to create an incremental dbt model that uses dbt_utils.surrogate_key on columns user_id and event_date to uniquely identify rows. Which of the following SQL snippets correctly creates the surrogate key and uses it as the primary key for incremental updates?

A select *, {{ dbt_utils.surrogate_key(['user_id', 'event_date']) }} as id from source_table

B select {{ dbt_utils.surrogate_key(['user_id', 'event_date']) }} as id, * from source_table

C select *, {{ dbt_utils.surrogate_key(['user_id', 'event_date']) }} as surrogate_key from source_table

D select {{ dbt_utils.surrogate_key(['user_id', 'event_date']) }} as surrogate_key, * from source_table

💡 Hint

The surrogate key should be added as a new column, not replace existing columns.

Option A adds the surrogate key as a new column named 'id' while keeping all original columns, which is correct for incremental models.

🧠 Conceptual

expert

Understanding pivot and unpivot transformations

Which statement correctly describes the relationship between dbt_utils.pivot and dbt_utils.unpivot macros?

A Pivot aggregates data without changing shape; unpivot splits columns into multiple tables.

B Pivot removes duplicate rows; unpivot adds calculated columns.

C Pivot and unpivot both create new columns but differ in aggregation methods.

D Pivot converts rows into columns; unpivot converts columns back into rows, reversing the pivot operation.

💡 Hint

Think about how data shape changes with pivot and unpivot.

Pivot reshapes data by turning unique values from one column into multiple columns. Unpivot reverses this by turning columns back into rows.