Macros for reusable SQL logic in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a simple macro call in dbt

Given the following dbt macro and model SQL, what is the output SQL after compilation?

dbt

{% macro filter_active_users() %}
  WHERE status = 'active'
{% endmacro %}

SELECT * FROM users {{ filter_active_users() }}

A SELECT * FROM users WHERE status = 'inactive'

B SELECT * FROM users WHERE status = active

C SELECT * FROM users WHERE status = 'active'

D SELECT * FROM users

💡 Hint

Remember that the macro inserts the exact WHERE clause with the string 'active' in quotes.

The macro inserts the WHERE clause with status = 'active' exactly as defined, so the compiled SQL includes that condition.

🧠 Conceptual

intermediate

Purpose of macros in dbt

What is the main purpose of using macros in dbt projects?

A To visualize data in dashboards

B To create reusable SQL snippets that can be called multiple times to avoid repetition

C To schedule dbt runs automatically

D To store data permanently in the database

💡 Hint

Think about how macros help with writing SQL code efficiently.

Macros allow you to write SQL code once and reuse it many times, making your project cleaner and easier to maintain.

🔧 Debug

advanced

Identify the error in this macro usage

What error will occur when running this dbt model code?

dbt

{% macro get_date_filter(date_col) %}
  WHERE {{ date_col }} > '2023-01-01'
{% endmacro %}

SELECT * FROM sales {{ get_date_filter() }}

A TypeError: get_date_filter() missing 1 required positional argument: 'date_col'

B SyntaxError: Unexpected token in macro

C No error, runs successfully

D RuntimeError: Undefined variable date_col

💡 Hint

Check if the macro is called with the required argument.

The macro requires one argument 'date_col', but it is called without any arguments, causing a TypeError.

❓ data_output

advanced

Resulting SQL from nested macros

Given these macros and model code, what is the final compiled SQL?

dbt

{% macro base_filter() %}
  status = 'active'
{% endmacro %}

{% macro full_filter() %}
  WHERE {{ base_filter() }} AND created_at > '2023-01-01'
{% endmacro %}

SELECT * FROM users {{ full_filter() }}

A SELECT * FROM users WHERE status = 'active' AND created_at > '2023-01-01'

B SELECT * FROM users WHERE status = active AND created_at > '2023-01-01'

C SELECT * FROM users WHERE status = 'active' OR created_at > '2023-01-01'

D SELECT * FROM users WHERE created_at > '2023-01-01'

💡 Hint

Macros can call other macros and insert their code exactly where called.

The full_filter macro calls base_filter, inserting "status = 'active'" inside the WHERE clause combined with AND and the date condition.

🚀 Application

expert

Using macros to dynamically generate SQL conditions

You want to create a macro that accepts a list of columns and generates a SQL condition checking if any of those columns are NULL. Which macro code produces the correct SQL condition for columns ['col1', 'col2', 'col3']?

dbt

{% macro null_check(cols) %}
  {{- cols | map('string') | join(' IS NULL OR ') }} IS NULL
{% endmacro %}

SELECT * FROM table WHERE {{ null_check(['col1', 'col2', 'col3']) }}

A col1, col2, col3 IS NULL

B IS NULL col1 OR IS NULL col2 OR IS NULL col3

C col1 IS NULL AND col2 IS NULL AND col3 IS NULL

D col1 IS NULL OR col2 IS NULL OR col3 IS NULL

💡 Hint

Think about how to join column names with ' IS NULL OR ' and add ' IS NULL' only once at the end.

The macro joins column names with ' IS NULL OR ' and adds ' IS NULL' at the end, producing the correct SQL condition checking if any column is NULL.