dbt-utils (surrogate_key, pivot, unpivot) - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the surrogate_key macro in dbt-utils do?

The surrogate_key macro creates a unique key by combining one or more columns. It helps identify each row uniquely, like a fingerprint for data records.

beginner

How does the pivot macro in dbt-utils transform data?

The pivot macro turns rows into columns. It reshapes data so that unique values from one column become new columns, making data easier to compare side by side.

beginner

What is the purpose of the unpivot macro in dbt-utils?

The unpivot macro does the opposite of pivot. It turns columns back into rows, which helps when you want to analyze data in a long, tidy format.

intermediate

Why is using a surrogate key useful in data modeling?

Surrogate keys provide a simple, consistent way to identify rows uniquely, even if natural keys change or are missing. This helps keep data clean and relationships clear.

intermediate

When would you use pivot and unpivot in your data transformations?

Use pivot to create wide tables for easier comparison across categories. Use unpivot to create long tables for easier filtering and aggregation.

What does the surrogate_key macro generate in dbt-utils?

A A visualization of data

B A unique identifier combining columns

C A new table with aggregated data

D A summary statistic of a column

The surrogate_key macro creates a unique key by combining specified columns.

Which macro would you use to turn multiple rows into columns?

A pivot

B surrogate_key

C unpivot

D join

The pivot macro reshapes data by turning rows into columns.

What is the main use of the unpivot macro?

A To turn columns into rows

B To combine columns into a single unique key

C To filter data by a condition

D To create a new table

The unpivot macro converts columns back into rows.

Why might surrogate keys be preferred over natural keys?

A They are easier to read

B They reduce the size of the data

C They automatically create indexes

D They never change and are always unique

Surrogate keys provide stable, unique identifiers even if natural keys change.

If you want to analyze data by categories side by side, which macro helps?

A surrogate_key

B unpivot

C pivot

D filter

The pivot macro creates columns for each category to compare data easily.

Explain how the surrogate_key macro works and why it is useful in data modeling.

Think about how you give each row a unique ID.

Describe the difference between pivot and unpivot macros and when to use each.

Consider how you reshape data for different analysis needs.