The Rule of Three for dbt Macros

The rule of three is a heuristic for deciding when to extract a dbt macro: wait for the third occurrence of a pattern before abstracting it. Early abstraction tends to create macros that obscure SQL without providing genuine reuse benefit.

The Rule

First occurrence: write it inline. No macro. Just SQL.

Second occurrence: note the pattern. Maybe add a comment: “Similar logic in base__shopify__orders.” Still no macro.

Third occurrence: now you have enough information to extract something useful. You’ve seen how the pattern actually gets used in real models. You know which parts truly vary and which stay constant. Now you can build an abstraction that fits reality instead of guessing at what you might need.

This rule comes from software engineering practice, and dbt’s own documentation endorses the underlying principle: “Favor readability when mixing Jinja with SQL, even if it means repeating some lines.”

What the Progression Looks Like

-- First time: inline
SELECT
    order_id,
    ROUND(amount_cents / 100.0, 2) AS order__amount_dollars
FROM {{ ref('base__shopify__orders') }}

-- Second time: still inline, but you notice the pattern
SELECT
    payment_id,
    ROUND(amount_cents / 100.0, 2) AS payment__amount_dollars
FROM {{ ref('base__stripe__payments') }}

-- Third time: now extract the macro
{% macro cents_to_dollars(column_name, scale=2) %}
    ROUND({{ column_name }} / 100.0, {{ scale }})
{% endmacro %}

Notice that waiting until the third use revealed something important: the scale parameter. The first time you see this pattern, you might hardcode 2. Only after seeing it used in different contexts do you realize some callers might want 4 decimal places for precision-sensitive calculations. The rule of three surfaces the parameters you actually need versus the ones you imagine you might need.

The Temptation to Skip Ahead

The temptation is strong. You see the pattern on day one and think, “I’ll definitely use this again.” Sometimes you’re right. More often, the second and third uses have subtle differences that break your original abstraction.

The classic failure: you extract a macro after seeing two uses, design it around those two cases, then the third use requires a slightly different variant. Now you’re adding an optional parameter to handle the exception. The fourth use needs another. By the sixth use you have a macro with five parameters and conditional logic branches, and it’s harder to understand than inline SQL would have been.

Waiting lets you build the right abstraction instead of the imagined one.

The Cost of Premature Macros

Premature macros create three compounding problems.

Reduced readability. Every macro reference is a mental context switch. The reader has to open another file, understand the macro’s logic, then return to the model. With inline code, everything is visible in one place. A model that requires reading four macro files just to understand one query is unreadable.

Increased complexity. To handle all the cases you imagined, you add parameters. Each parameter is a decision point. A macro with seven parameters is harder to use correctly than inline code would have been — and harder to test, and harder to document.

Fragile code. When a macro serves five models, changes get scary. You want to fix the behavior for one model but can’t risk breaking the others. So you add another parameter, making the complexity problem worse. The macro becomes load-bearing infrastructure that nobody wants to touch.

These three problems are not independent. Complexity makes fragility worse; fragility makes readability worse; poor readability hides the complexity that’s making things fragile. Premature abstraction is a trap that closes on itself.

When to Break the Rule

Three occurrences is a heuristic, not a law. There are legitimate reasons to extract a macro earlier:

Security-sensitive patterns. If the pattern contains something that must never vary — a hard-coded schema name, an encryption approach — extract it immediately so there’s one authoritative version.

Boilerplate with no variation. Some patterns are genuinely invariant. add_audit_columns adds the same metadata fields to every table. There’s no “second use with subtle differences” to wait for.

Team conventions. When onboarding a team to a new project pattern — say, a consistent approach to handling deleted records from a SaaS source — you might define the macro upfront as a signal of intent: “this is how we do this here.”

Even in these cases, keep the macro simple. The justification for early extraction is that the pattern is well-understood and invariant — not that you’re confident about all the cases it might need to handle.

The Underlying Principle

The rule of three is about gathering information before abstracting. By the third use, the pattern’s actual variation is visible: what changes between call sites, what stays constant, and what edge cases real models produce. That information produces an abstraction grounded in actual usage rather than anticipated need.

See dbt Single Responsibility Macros for what to do once extraction is warranted, and dbt Macro Naming Conventions for naming patterns that make macros discoverable.