Once you’ve got your models, snapshots, and seeds up and running, you might start noticing some repeated SQL patterns in your dbt project.

Maybe you’re calculating currency-converted revenue in multiple models. Or doing the same timestamp casting again and again. That’s your signal: it’s time to reach for macros.

Macros are dbt’s way of letting you write reusable SQL logic, powered by Jinja templating. If you're familiar with SQL functions or Python utilities, macros are dbt’s equivalent for transformations.

In this post, we’ll:

Write your first custom macro
Use it inside your models
Show real examples based on our SaaS Stripe + HubSpot use case
Share tips for keeping macros readable and useful

Let’s dive in.

🤔 What Are dbt Macros?

Think of macros as custom SQL functions written using Jinja. They can:

Accept arguments (like table names or column names)
Generate reusable SQL blocks
Be shared across models, tests, and snapshots

Macros live in the /macros/ folder and are defined using macro blocks.

-- macros/my_macro.sql

{% macro my_macro(argument) %}
  select {{ argument }}
{% endmacro %}

Then you call it in a model like:

{{ my_macro('1 as one') }}

📁 Folder Structure Update

We now add a new folder to your project:

/macros
  convert_currency.sql
  safe_cast_timestamp.sql

💱 Macro Example 1: Convert Amounts to USD

Let’s say you’re converting revenue to USD using exchange rates stored in a seed table. This logic might appear in several models.

Instead of duplicating:

amount_usd * e.exchange_rate_to_usd

You can write:

-- macros/convert_currency.sql

{% macro convert_currency(amount_column, currency_column, exchange_rate_table='currency_exchange_rates') %}
  {{ amount_column }} * (
    select exchange_rate_to_usd
    from {{ ref(exchange_rate_table) }}
    where currency = {{ currency_column }}
    limit 1
  )
{% endmacro %}

How to Use It in a Model

sqlCopyEdit-- fct_revenue.sql

with exchange_rates as (
  select * from {{ ref('currency_exchange_rates') }}
)

select
  p.customer_id,
  date_trunc('month', p.payment_time) as revenue_month,
  sum({{ convert_currency('p.amount_usd', 'p.currency') }}) as mrr_usd,
  c.lifecycle_stage
from {{ ref('stg_stripe_payments') }} p
left join {{ ref('dim_customer') }} c
  on p.customer_id = c.customer_id
group by 1, 2, 4

This makes your code cleaner, less error-prone, and easier to update in one place if the logic changes.

⏱️ Macro Example 2: Safe Timestamp Cast

You might want to standardize how timestamps are parsed and casted, especially across staging models. Here’s a macro to do that:

-- macros/safe_cast_timestamp.sql

{% macro safe_cast_timestamp(column_name) %}
  try_cast({{ column_name }} as timestamp)
{% endmacro %}

Use it like this:

-- stg_stripe_payments.sql

select
  id as payment_id,
  customer_id,
  amount / 100.0 as amount_usd,
  currency,
  {{ safe_cast_timestamp('created_at') }} as payment_time
from {{ source('stripe', 'stripe_payments') }}
where status = 'paid'

This protects you from failing builds if timestamp formats vary slightly or are malformed in raw data.

Bonus: Macros for Metadata

You can even use macros to dynamically generate column lists, run model tests, or build SELECT * statements with field exclusions.

Example: exclude audit columns from a select:

{% macro select_star_excluding(table, exclude_columns=[]) %}
  {% set columns = adapter.get_columns_in_relation(ref(table)) %}
  {% set filtered = columns | rejectattr('name', 'in', exclude_columns) | list %}
  {{ filtered | map(attribute='name') | join(', ') }}
{% endmacro %}

Final Thoughts

Macros are the glue between clean logic and reusable, maintainable dbt projects. Start small. Wrap something you’ve repeated twice. Before long, you’ll have your own toolkit of utilities that power your models with clarity and consistency.

Writing Your First Macro in dbt – Practical Templates