dbt-utils (surrogate_key, pivot, unpivot) - Mini Project: Build & Apply

Using dbt-utils Macros: surrogate_key, pivot, and unpivot

📖 Scenario: You work as a data analyst in a retail company. You have sales data with customer names, product categories, and sales amounts. You want to create a unique surrogate key for each sale, then transform the data to see sales by category in columns (pivot), and finally convert it back to a long format (unpivot) for detailed analysis.

🎯 Goal: Build a dbt model that uses dbt-utils.surrogate_key to create unique keys, dbt-utils.pivot to reshape data from long to wide format, and dbt-utils.unpivot to reshape data back from wide to long format.

📋 What You'll Learn

Create a source table with sales data including customer_name, category, and sales_amount.

Use dbt-utils.surrogate_key macro to generate a unique sale_id for each row.

Use dbt-utils.pivot macro to pivot sales amounts by category.

Use dbt-utils.unpivot macro to unpivot the pivoted data back to long format.

💡 Why This Matters

🌍 Real World

Retail and sales data often need reshaping to analyze sales by category or customer. Surrogate keys help uniquely identify transactions.

💼 Career

Data analysts and engineers use dbt and dbt-utils macros to clean, transform, and prepare data efficiently for reporting and analytics.

1

Create the initial sales data table

Create a dbt model called sales_data with a SELECT statement that returns these exact rows: customer_name as 'Alice', 'Bob', 'Alice'; category as 'Electronics', 'Clothing', 'Clothing'; and sales_amount as 100, 50, 75 respectively.

dbt

-- Create the sales_data model with the exact rows
-- Your code here

Hint

Use values to create rows with customer_name, category, and sales_amount.

2

Add a surrogate key column

Add a new column called sale_id using the dbt_utils.surrogate_key macro on customer_name, category, and sales_amount in the sales_data model.

dbt

select
  dbt_utils.surrogate_key(array[customer_name, category, cast(sales_amount as text)]) as sale_id,
  customer_name,
  category,
  sales_amount
from (values
  ('Alice', 'Electronics', 100),
  ('Bob', 'Clothing', 50),
  ('Alice', 'Clothing', 75)
) as sales(customer_name, category, sales_amount)
-- Your code here

Hint

Use dbt_utils.surrogate_key with an array of columns to create sale_id.

3

Pivot sales amounts by category

Use the dbt_utils.pivot macro to pivot the sales_amount values by category, grouping by customer_name. Select customer_name and the pivoted columns for 'Electronics' and 'Clothing' as electronics_sales and clothing_sales.

dbt

select
  customer_name,
  -- Use dbt_utils.pivot here to pivot sales_amount by category
  -- Your code here
from (
  select
    customer_name,
    category,
    sales_amount
  from (values
    ('Alice', 'Electronics', 100),
    ('Bob', 'Clothing', 50),
    ('Alice', 'Clothing', 75)
  ) as sales(customer_name, category, sales_amount)
) base
group by customer_name

Hint

Use dbt_utils.pivot with relation, column, values, and value_column parameters.

4

Unpivot the pivoted sales data

Use the dbt_utils.unpivot macro to convert the pivoted sales columns electronics_sales and clothing_sales back into long format with columns category and sales_amount. Select customer_name, category, and sales_amount from the unpivoted result.

dbt

-- Use dbt_utils.unpivot here to unpivot the pivoted sales data
-- Select customer_name, category, and sales_amount
-- Your code here

Hint

Use dbt_utils.unpivot with relation, columns, names, value_column_name, and name_column_name.

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