Macros for reusable SQL logic in dbt - Mini Project: Build & Apply

Macros for reusable SQL logic

📖 Scenario: You work as a data analyst who often writes similar SQL queries to calculate sales metrics for different regions. To save time and avoid repeating code, you want to create reusable SQL logic using dbt macros.

🎯 Goal: Build a dbt macro that calculates total sales for a given region, then use this macro in a model to get total sales for the 'East' region.

📋 What You'll Learn

Create a macro named total_sales_by_region that takes a parameter region_name

The macro should return a SQL snippet that sums the sales_amount from the sales table filtered by region_name

Create a dbt model that uses the macro to calculate total sales for the region 'East'

Print the resulting SQL query output

💡 Why This Matters

🌍 Real World

In real companies, analysts often write similar SQL queries for different filters or metrics. Macros let you write the logic once and reuse it, saving time and reducing errors.

💼 Career

Knowing how to create and use macros in dbt is valuable for data analysts and engineers to build scalable, maintainable data transformation pipelines.

1

Create the sales data table

Create a dbt model named sales with columns region_name and sales_amount. Insert these exact rows: ('East', 100), ('West', 200), ('East', 150).

dbt

# Create the sales model with the specified rows
-- Your code here

Hint

Use a CTE with union all to create the sales data rows.

2

Create the macro for total sales by region

Create a macro named total_sales_by_region that takes a parameter region_name and returns a SQL snippet to sum sales_amount from the sales model filtered by region_name.

dbt

with sales_data as (
    select 'East' as region_name, 100 as sales_amount
    union all
    select 'West', 200
    union all
    select 'East', 150
)

-- Define the macro below
-- Your code here

Hint

Use {% macro %} and {% endmacro %} tags. Use {{ ref('sales') }} to refer to the sales model.

3

Use the macro in a model to get total sales for 'East'

Create a dbt model that uses the macro total_sales_by_region with the argument 'East' to get total sales for the East region.

dbt

{% macro total_sales_by_region(region_name) %}
    select sum(sales_amount) as total_sales
    from {{ ref('sales') }}
    where region_name = '{{ region_name }}'
{% endmacro %}

-- Use the macro below to get total sales for 'East'
-- Your code here

Hint

Call the macro inside a select * from ( ... ) statement.

4

Print the total sales result

Print the result of the query that uses the macro to show total sales for the 'East' region.

dbt

{% macro total_sales_by_region(region_name) %}
    select sum(sales_amount) as total_sales
    from {{ ref('sales') }}
    where region_name = '{{ region_name }}'
{% endmacro %}

select * from ({{ total_sales_by_region('East') }}) as east_sales

-- Print the total sales below
-- Your code here

Hint

Run the model to see the total sales output for the East region, which should be 250.

Practice

1. What is the main purpose of using macros in dbt?

easy

A. To create visualizations from data

B. To store data permanently in the database

C. To reuse SQL code and make it easier to maintain

D. To write Python scripts inside dbt

Solution

1. Step 1: Understand what macros do

   Macros are reusable pieces of SQL code that help avoid repetition.

2. Step 2: Identify the main benefit

   Using macros makes SQL cleaner and easier to maintain by reusing code.

3. Final Answer:

   To reuse SQL code and make it easier to maintain -> Option C

4. Quick Check:

   Macros = reusable SQL code [OK]

Hint: Macros = reusable SQL snippets for cleaner code [OK]

Common Mistakes:

• Thinking macros store data
• Confusing macros with visualization tools
• Assuming macros run Python code

2. Which of the following is the correct way to define a macro in dbt?

easy

A. {% macro my_macro() %} SELECT * FROM table {% endmacro %}

B. CREATE MACRO my_macro AS SELECT * FROM table;

C. def my_macro(): SELECT * FROM table

D. macro my_macro() { SELECT * FROM table }

Solution

1. Step 1: Recall dbt macro syntax

   dbt macros use Jinja templating with {% macro %} and {% endmacro %} tags.

2. Step 2: Match syntax to options

   {% macro my_macro() %} SELECT * FROM table {% endmacro %} correctly uses Jinja syntax for defining a macro.

3. Final Answer:

   {% macro my_macro() %} SELECT * FROM table {% endmacro %} -> Option A

4. Quick Check:

   dbt macros use {% macro %} ... {% endmacro %} [OK]

Hint: Look for {% macro %} and {% endmacro %} tags [OK]

Common Mistakes:

• Using SQL CREATE MACRO syntax (not dbt)
• Writing Python function syntax
• Using curly braces without Jinja tags

3. Given this macro definition:

{% macro select_active_users() %}
SELECT * FROM users WHERE active = true
{% endmacro %}

What will be the output SQL of this dbt model?

SELECT id, name FROM ({{ select_active_users() }})

medium

A. SELECT id, name FROM {{ select_active_users() }}

B. SELECT id, name FROM users WHERE active = true

C. Error: Macro call syntax incorrect

D. SELECT id, name FROM (SELECT * FROM users WHERE active = true)

Solution

1. Step 1: Understand macro expansion

   The macro returns the SQL: SELECT * FROM users WHERE active = true.

2. Step 2: Substitute macro call in model SQL

   The model SQL becomes: SELECT id, name FROM (SELECT * FROM users WHERE active = true)

3. Final Answer:

   SELECT id, name FROM (SELECT * FROM users WHERE active = true) -> Option D

4. Quick Check:

   Macro expands SQL inside parentheses [OK]

Hint: Macros insert SQL code where called [OK]

Common Mistakes:

• Forgetting parentheses around macro SQL
• Thinking macro returns table name only
• Using macro call without {{ }}

4. Identify the error in this macro definition:

{% macro get_orders() %}
SELECT * FROM orders

medium

A. Macro name cannot have underscores

B. Missing {% endmacro %} tag to close the macro

C. SELECT statement must be inside double quotes

D. Macro cannot return SELECT statements

Solution

1. Step 1: Check macro syntax

   Macros must start with {% macro %} and end with {% endmacro %} tags.

2. Step 2: Identify incorrect closing tag

   The macro incorrectly uses instead of {% endmacro %}.

3. Final Answer:

   Missing {% endmacro %} tag to close the macro -> Option B

4. Quick Check:

   Macro must end with {% endmacro %} [OK]

Hint: Always close macros with {% endmacro %} [OK]

Common Mistakes:

• Using HTML-like closing tags
• Thinking underscores are invalid
• Believing SELECT needs quotes in macros

5. You want to create a macro that accepts a table name and a column name, then returns SQL to count distinct values in that column. Which macro definition correctly achieves this?

hard

A. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT {{ column }}) FROM {{ table }} {% endmacro %}

B. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT column) FROM table {% endmacro %}

C. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT '{{ column }}') FROM '{{ table }}' {% endmacro %}

D. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT $column) FROM $table {% endmacro %}

Solution

1. Step 1: Understand macro parameters usage

   Parameters must be inserted with {{ }} to inject SQL identifiers.

2. Step 2: Check each option for correct syntax

   {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT {{ column }}) FROM {{ table }} {% endmacro %} correctly uses {{ column }} and {{ table }} without quotes, so SQL identifiers are injected properly.

3. Final Answer:

   {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT {{ column }}) FROM {{ table }} {% endmacro %} -> Option A

4. Quick Check:

   Use {{ }} to insert parameters as SQL code [OK]

Hint: Use {{ }} to insert macro parameters in SQL [OK]

Common Mistakes:

• Putting parameters inside quotes (makes them strings)
• Using $variable syntax (not Jinja)
• Not wrapping parameters with {{ }}