dbt-utils (surrogate_key, pivot, unpivot) - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a surrogate key using dbt-utils.

dbt

SELECT [1]('id', 'name') AS surrogate_key FROM customers

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

Asurrogate_key

Bsurrogate_key()

Attempts:

3 left

💡 Hint

Common Mistakes

Adding parentheses after the macro name incorrectly.

Using incorrect macro names.

Not passing any columns to the macro.

The dbt-utils macro 'surrogate_key' is called without parentheses and takes column names as arguments.

2fill in blank

medium

Complete the code to pivot data using dbt-utils.

dbt

SELECT * FROM [1](relation=ref('sales'), index='region', columns='product', values='revenue')

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

Agenerate_surrogate_key

Bunpivot

Csurrogate_key

Dpivot

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'unpivot' instead of 'pivot'.

Using surrogate_key macro for pivoting.

Incorrect macro names.

The dbt-utils macro 'pivot' is used to transform rows into columns.

3fill in blank

hard

Fix the error in the code to unpivot data using dbt-utils.

dbt

SELECT * FROM [1](relation=ref('sales'), index='region', columns=['product', 'category'], values='revenue')

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

Aunpivot

Bpivot

Csurrogate_key

Dgenerate_surrogate_key

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'pivot' instead of 'unpivot'.

Passing columns as a list instead of a string.

Using surrogate_key macro for unpivoting.

The dbt-utils macro 'unpivot' is used to transform columns into rows.

4fill in blank

hard

Fill both blanks to create a surrogate key and then pivot the data.

dbt

WITH keyed_data AS (
  SELECT *, [1]('customer_id', 'order_id') AS surrogate_key
  FROM [2]('orders')
)
SELECT * FROM keyed_data

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

Asurrogate_key

Bpivot

Cref

Dunpivot

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'pivot' instead of 'ref' for model reference.

Using 'unpivot' instead of 'pivot' or 'ref'.

Not using surrogate_key macro correctly.

Use 'surrogate_key' macro to create keys and 'ref' to reference the 'orders' model.

5fill in blank

hard

Fill all three blanks to unpivot data, create surrogate keys, and reference the source table.

dbt

WITH unpivoted AS (
  SELECT *, [1]('product', 'month') AS surrogate_key
  FROM [2](
    relation=[3]('sales_data'),
    index='region',
    columns='product',
    values='amount'
  )
)
SELECT * FROM unpivoted

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

Asurrogate_key

Bunpivot

Cref

Dpivot

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up 'pivot' and 'unpivot'.

Not using 'ref' to reference models.

Incorrect macro usage for surrogate keys.

Use 'surrogate_key' to create keys, 'unpivot' to reshape data, and 'ref' to reference the source table.

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