DRY (Don't Repeat Yourself) is a fundamental principle of software development. The opposite is WET (Write Everything Twice).
So how can you DRY your WET SQL? If you have to convert cents to dollars across twenty different models, calculate a complex metric, or write the same CASE WHEN block for ten different columns, traditional SQL forces you to copy and paste that logic repeatedly. If the business logic then changes, you have to change every single instance across your codebase to update it - which can increase scope for error.
When you use Jinja within dbt (a Python based templating language) you can write modular, reusable SQL functions called Macros and use programming logic directly inside your data models, such as using control structures like loops and if statements within a SQL statement.
1. The Jinja Syntax Basics
dbt uses Jinja to compile your models into raw SQL before sending them to your data warehouse (like Snowflake). Jinja uses three primary delimiter types:
- Expressions
{{ ... }}: Used when you want to output a value, reference a table viaref(), or call a macro. - Statements
{% ... %}: Used for logic, such as setting variables, runningif/elseconditionals, or looping withfor. - Comments
{# ... #}: Used for internal Jinja comments that won't show up in the compiled SQL.
Here is a look at how Jinja templating works in practice:
{% set friends = ['Joey', 'Ross', 'Rachel', 'Chandler', 'Monica', 'Phoebe'] %}
{% for friend in friends %}
{# Check for specific names to assign pronouns dynamically #}
{% if friend in ['Rachel', 'Monica', 'Phoebe'] %}
{% set pronoun = 'She' %}
{% else %}
{% set pronoun = 'He' %}
{% endif %}
-- {{ friend }} is in the group. {{ pronoun }} is ready to code.
{% endfor %}
Compiled Output
When dbt parses this Jinja block, it evaluates the loops and variables and compiles them down into plain text (and it works the same way to compile your SQL functions):
-- Joey is in the group. He is ready to code.
-- Ross is in the group. He is ready to code.
-- Rachel is in the group. She is ready to code.
-- Chandler is in the group. He is ready to code.
-- Monica is in the group. She is ready to code.
-- Phoebe is in the group. She is ready to code.
2. Using Jinja Control Flow in SQL
Instead of manually typing out repetitive SQL statements for multiple values, you can use loops with jinja to write them dynamically.
For example, if you wanted to pivot payment methods into separate columns without using the pivot function (to illustrate this functionality), you don't need to copy and paste ten SUM(CASE WHEN ...) blocks. You can define a list and loop through it:
{% set payment_methods = ['bank_transfer', 'credit_card', 'coupon', 'gift_card'] %}
select
order_id,
{% for method in payment_methods %}
sum(case when payment_method = '{{ method }}' then amount else 0 end) as {{ method }}_amount
{%- if not loop.last %},{% endif %}
{% endfor %}
from {{ ref('stg_payments') }}
group by 1
Tip: Notice the hyphen in{%- if not loop.last %}. Placing a hyphen inside the tag ({%-or-%}) strips out unnecessary whitespace and newlines so your compiled SQL stays clean.
Compiled Output
When dbt compiles this model, it evaluates the loop and outputs clean, standardized SQL ready for Snowflake:
select
order_id,
sum(case when payment_method = 'bank_transfer' then amount else 0 end) as bank_transfer_amount,
sum(case when payment_method = 'credit_card' then amount else 0 end) as credit_card_amount,
sum(case when payment_method = 'coupon' then amount else 0 end) as coupon_amount,
sum(case when payment_method = 'gift_card' then amount else 0 end) as gift_card_amount
from analytics.staging.stg_payments
group by 1
3. Creating Reusable Code with Macros
A Macro in dbt is simply a reusable snippet of Jinja code that acts like a function. You store macros inside the macros/ folder of your dbt project.
Step 1: Define the Macro
Suppose your source data stores all monetary amounts as integers in cents (e.g., 1050 for $10.50). Instead of repeating round(amount * 1.0 / 100, 2) everywhere, you can build a macro called convert_to_dollars:
-- Located in macros/convert_to_dollars.sql
{% macro convert_to_dollars(column_name, dp=2) -%}
round(({{ column_name }} * 1.0 / 100), {{ dp }})
{%- endmacro %}
This macro accepts two arguments:
column_name: The column you want to convert.dp: The number of decimal places (defaulting to2).
Step 2: Call the Macro in a Model
Once defined, you can call this macro in any dbt model across your entire project using expression syntax ({{ ... }}):
-- Located in models/marts/fct_orders.sql
select
order_id,
-- Calling the macro with default 2 decimal places
{{ convert_to_dollars('item_price_in_cents') }} as item_price,
-- Overriding the default decimal places to 4
{{ convert_to_dollars('tax_in_cents', dp=4) }} as tax_amount
from {{ ref('stg_stripe_payments') }}
Compiled Output
When dbt runs, it compiles that model into standard, valid SQL before executing it in Snowflake:
select
order_id,
round((item_price_in_cents * 1.0 / 100), 2) as item_price,
round((tax_in_cents * 1.0 / 100), 4) as tax_amount
from analytics.staging.stg_stripe_payments
4. Why This Matters for Data Engineering
Using Jinja and macros can streamline how you maintain a data warehouse:
- Single Source of Truth: If the business changes how currency rounding or tax calculations work, you update the logic once inside the macro. Every model calling that macro updates automatically.
- Readability: Models stay short, clean, and focused on transformation intent rather than verbose SQL.
- Ecosystem Packages: You aren't limited to writing your own macros. Packages like
dbt_utilsorcodegengive you access to hundreds of pre-built community macros for operations such as surrogate key generation, schema generation, and cross-database SQL functions.
Summary
Moving from static SQL files to dbt with Jinja allows you to apply real software developing standards to your data transformation pipelines. By turning repetitive calculations into clean, reusable macros, you make your codebase easier to audit, test, and maintain.