📘 Introduction

In this hands-on dbt tutorial, we’ll walk you step-by-step through how to create, use, and understand macros — one of dbt’s most powerful features for reusing logic and keeping your SQL clean, modular, and DRY (Don’t Repeat Yourself). By the end, you’ll be able to write your own custom macro and call it inside your dbt models like a pro.

✅ Prerequisites

☑️ A dbt project set up
☑️ Set up Medallion Architecture
☑️ Source data loaded into your data warehouse
☑️ Source configurations defined in sources.yml

💡What is a macro in dbt?

A macro in dbt is a reusable piece of SQL logic written in Jinja, similar to a function in Python or a stored procedure in SQL. Instead of repeating the same SQL logic across multiple models, you can define it once in a macro and call it anywhere.

💡
Jinja is a templating language that lets you add programming logic (like variables, loops, and conditionals) inside your SQL in dbt. It makes your SQL dynamic and reusable, allowing you to write cleaner, smarter transformations.

🧠 Practical Example

In our dbt project, the dbt model dim_student currently looks like this:

WITH cte_read_student AS (
SELECT
    id,
    name,
    major,
    number,
    source_name
FROM {{ ref("transformed_student") }}
),

cte_extract_columns AS (
SELECT
    id,
    SUBSTRING(name, 1, STRPOS(name, ' ') - 1) AS first_name,
    SUBSTRING(name, STRPOS(name, ' ') + 1) AS last_name,    
    major,
    number
FROM cte_read_student
),

SELECT * FROM cte_extract_columns

The table in the warehouse contains the following data:

Now, we want to extend the table with a new column that should contain the email address in the format: firstname.lastname@university.com.

To generate the email address in a reusable and maintainable way, we’ll create a macro that dynamically constructs the email based on the student’s first and last name.

💡
Since the macro can be used across multiple dbt models, the main advantage is maintainability. If the email format ever needs to change — for example, from firstname.lastname@university.com to firstname_lastname@university.com— you don’t have to update each model individually. Instead, you simply modify the macro in one place, and the change automatically applies everywhere it’s used.

🗂️1️⃣ Create .sql file inside the macros/ folder

Inside the macros/ folder, create a new file called generate_email.sql.

✍️2️⃣ Define macro

Add the following code to your file:

You can view this post with the tier: Academy Membership

Join academy now to read the post and get access to the full library of premium posts for academy members only.

Join Academy Already have an account? Sign In