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

Need a 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

Need a 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

Need a 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

Need a hint?

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