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.