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.

Practice

1. What is the main purpose of the surrogate_key macro in dbt-utils?

easy

A. To create a unique identifier by combining multiple columns

B. To convert rows into columns for summarization

C. To change column names in a table

D. To remove duplicate rows from a dataset

Solution

1. Step 1: Understand surrogate_key purpose

   The surrogate_key macro combines values from multiple columns to create a unique ID.

2. Step 2: Compare with other options

   Pivoting changes rows to columns, renaming columns is unrelated, and removing duplicates is different.

3. Final Answer:

   To create a unique identifier by combining multiple columns -> Option A

4. Quick Check:

   surrogate_key = unique ID creation [OK]

Hint: surrogate_key always combines columns to make unique IDs [OK]

Common Mistakes:

• Confusing surrogate_key with pivot or unpivot
• Thinking it renames columns
• Assuming it removes duplicates

2. Which of the following is the correct syntax to use the pivot macro in dbt-utils?

easy

A. {% set pivoted = dbt_utils.unpivot(table=ref('sales'), column='month', value='revenue') %}

B. {% set pivoted = dbt_utils.surrogate_key(columns=['id', 'date']) %}

C. {% set pivoted = dbt_utils.pivot(table=ref('sales'), column='month', value='revenue') %}

D. {% set pivoted = dbt_utils.pivot(table='sales', row='month', value='revenue') %}

Solution

1. Step 1: Identify correct macro and parameters

   The pivot macro requires a table, a column to pivot on, and a value column.

2. Step 2: Check syntax correctness

   {% set pivoted = dbt_utils.pivot(table=ref('sales'), column='month', value='revenue') %} uses table=ref('sales'), column='month', and value='revenue', which is correct.

3. Final Answer:

   {% set pivoted = dbt_utils.pivot(table=ref('sales'), column='month', value='revenue') %} -> Option C

4. Quick Check:

   pivot syntax = table, column, value [OK]

Hint: pivot needs table, column, and value parameters [OK]

Common Mistakes:

• Using unpivot macro instead of pivot
• Passing wrong parameter names like 'row' instead of 'column'
• Not using ref() for table argument

3. Given the following data in a table sales_data:

product | month | revenue
-------|-------|--------
A      | Jan   | 100
A      | Feb   | 150
B      | Jan   | 200
B      | Feb   | 250

What will be the output of this dbt-utils pivot macro call?

{% set pivoted = dbt_utils.pivot(table=ref('sales_data'), column='month', value='revenue') %}

medium

A. A table with columns: product, Jan, Feb and values filled with revenue

B. A table with columns: month, product, revenue repeated

C. A table with columns: product, revenue only

D. An error because pivot needs a group_by column

Solution

1. Step 1: Understand pivot behavior

   Pivot converts unique values in the 'month' column into new columns and fills them with 'revenue' values grouped by 'product'.

2. Step 2: Apply to given data

   For product A, Jan=100 and Feb=150; for B, Jan=200 and Feb=250. So columns are product, Jan, Feb with revenue values.

3. Final Answer:

   A table with columns: product, Jan, Feb and values filled with revenue -> Option A

4. Quick Check:

   pivot turns rows to columns = A table with columns: product, Jan, Feb and values filled with revenue [OK]

Hint: Pivot turns unique column values into new columns [OK]

Common Mistakes:

• Thinking pivot repeats original rows
• Expecting only product and revenue columns
• Assuming pivot requires explicit group_by parameter

4. You wrote this dbt-utils unpivot macro call:

{% set unpivoted = dbt_utils.unpivot(table=ref('sales_summary'), columns=['Jan', 'Feb'], name='month', value='revenue') %}

But you get an error. What is the most likely cause?

medium

A. The 'columns' parameter should be a string, not a list

B. The 'name' parameter must be 'column_name' instead of 'month'

C. The table reference is missing the schema

D. The 'columns' parameter must be passed as a string with comma-separated column names

Solution

1. Step 1: Check unpivot columns parameter format

   The unpivot macro expects the columns argument as a string listing columns separated by commas, not a list.

2. Step 2: Identify error cause

   Passing a Python list causes a syntax or runtime error; it must be a string like 'Jan, Feb'.

3. Final Answer:

   The 'columns' parameter must be passed as a string with comma-separated column names -> Option D

4. Quick Check:

   unpivot columns = comma-separated string [OK]

Hint: Pass columns as comma-separated string, not list, in unpivot [OK]

Common Mistakes:

• Passing columns as Python list instead of string
• Using wrong parameter names like 'name' instead of 'column_name'
• Ignoring table reference format

5. You have a table employee_sales with columns: employee_id, region, Jan, Feb, Mar. You want to create a unique surrogate key combining employee_id and region, then unpivot the monthly sales columns into rows with columns month and sales. Which dbt-utils macros and steps should you use?

hard

A. Use only pivot macro on employee_id and region columns

B. First use surrogate_key(['employee_id', 'region']) to create ID, then unpivot(table=ref('employee_sales'), columns='Jan, Feb, Mar', name='month', value='sales')

C. Use unpivot macro first, then surrogate_key on month and sales

D. Create surrogate key by concatenating Jan, Feb, Mar columns, then pivot on region

Solution

1. Step 1: Create unique surrogate key

   Use surrogate_key on employee_id and region to get a unique ID per employee-region.

2. Step 2: Unpivot monthly sales columns

   Apply unpivot macro with columns='Jan, Feb, Mar', name='month', and value='sales' to convert months into rows.

3. Final Answer:

   First use surrogate_key(['employee_id', 'region']) then unpivot with columns='Jan, Feb, Mar' -> Option B

4. Quick Check:

   surrogate_key then unpivot = First use surrogate_key(['employee_id', 'region']) to create ID, then unpivot(table=ref('employee_sales'), columns='Jan, Feb, Mar', name='month', value='sales') [OK]

Hint: Create surrogate_key first, then unpivot monthly columns [OK]

Common Mistakes:

• Using pivot instead of unpivot for monthly columns
• Creating surrogate_key on month and sales columns
• Concatenating month columns for surrogate_key