DRY Revenue Logic: Extracting a Shared dbt Macro for Partner-Specific Calculations

Revenue numbers that don’t add up are the kind of bug that erodes trust fast. Last month I tracked down a case where a quote adjustment of over two thousand euros was producing zero revenue in our reporting layer. The root cause? Duplicated business logic across two dbt models — logic that had drifted apart over time.

The Setup: Two Models, Same Logic, Different Results

Our data warehouse calculates revenue differently depending on the partner. Some partners use a tiered commission model — different percentages for labour, paint, parts, and other cost categories. Others use a flat commission rate on the total invoice amount. This logic lived in two places:

• A bids model in layer 08 (analytics business logic) — this calculates expected revenue when a bid is accepted.
• A quote adjustments model in layer 07 (normalized) — this adjusts revenue when a quote changes after the initial bid.

Both models contained their own version of the partner-specific commission calculation. When the tiered pricing for one partner was updated in the bids model — say, from 20% to 25% on labour — nobody remembered to update the quote adjustments model. The result: any quote adjustment for that partner calculated revenue as zero, because the model didn’t recognise the partner’s pricing tier at all and fell through to the default ELSE 0 branch.

Spotting the Leak

The symptom was subtle. A downstream report showed a quote adjustment record with an invoice amount of roughly €2,466 but a revenue column of €0.00. For flat-commission partners this would have been caught immediately — but tiered partners have enough complexity that a zero here looked like it might be intentional (maybe no margin on a particular cost category). It took a manual audit to surface the discrepancy.

Looking at the two SQL files side by side made the problem obvious. The bids model had this partner in its CASE statement with the updated rates. The quote adjustments model didn’t have the partner at all.

The Fix: A Shared dbt Macro

The solution was to extract the commission calculation into a single dbt macro that both models call. This way, partner-specific logic lives in exactly one place. Here’s the pattern I used.

The Macro: calc_commission

I created a macro file at macros/calc_commission.sql:

{% macro calc_commission(
    partner_id_column,
    labour_amount_column,
    paint_amount_column,
    parts_amount_column,
    other_amount_column,
    total_amount_column
) %}

case
    {# --- Partner A: tiered commission --- #}
    when {{ partner_id_column }} = 'partner_a'
        then (
            coalesce({{ labour_amount_column }}, 0) * 0.25
            + coalesce({{ paint_amount_column }}, 0) * 0.25
            + coalesce({{ parts_amount_column }}, 0) * 0.15
            + coalesce({{ other_amount_column }}, 0) * 0.25
        )

    {# --- Partner B: tiered commission (different rates) --- #}
    when {{ partner_id_column }} = 'partner_b'
        then (
            coalesce({{ labour_amount_column }}, 0) * 0.30
            + coalesce({{ paint_amount_column }}, 0) * 0.20
            + coalesce({{ parts_amount_column }}, 0) * 0.10
            + coalesce({{ other_amount_column }}, 0) * 0.20
        )

    {# --- Partner C: flat commission on total --- #}
    when {{ partner_id_column }} = 'partner_c'
        then coalesce({{ total_amount_column }}, 0) * 0.18

    {# --- Default: standard flat rate --- #}
    else coalesce({{ total_amount_column }}, 0) * 0.20
end

{% endmacro %}

Key design decisions:

• Column names are parameters — the two calling models have different column names for the same concept (e.g., bid_labour_amount vs adjustment_labour_amount). Passing column names as macro arguments keeps it flexible.
• COALESCE everything — some cost categories can be NULL when they don’t apply. Wrapping every column in coalesce(..., 0) inside the macro means callers don’t need to worry about NULLs.
• Explicit default — the ELSE branch applies a standard flat rate instead of returning zero. This is a deliberate choice: if a new partner isn’t added to the macro yet, they get the default rate rather than zero revenue. This makes missing configurations visible as slightly wrong numbers rather than silently zero numbers.

Calling the Macro from the Bids Model (Layer 08)

-- models/08_analytics_bizlogic/int_bids__commission.sql

select
    bid_id,
    partner_id,
    labour_amount,
    paint_amount,
    parts_amount,
    other_amount,
    total_invoice_amount,

    {{ calc_commission(
        partner_id_column='partner_id',
        labour_amount_column='labour_amount',
        paint_amount_column='paint_amount',
        parts_amount_column='parts_amount',
        other_amount_column='other_amount',
        total_amount_column='total_invoice_amount'
    ) }} as commission_revenue

from {{ ref('int_bids__enriched') }}

Calling the Macro from the Quote Adjustments Model (Layer 07)

-- models/07_normalized/quote_adjustments.sql

select
    adjustment_id,
    bid_id,
    partner_id,
    adj_labour_amount,
    adj_paint_amount,
    adj_parts_amount,
    adj_other_amount,
    adj_total_amount,

    {{ calc_commission(
        partner_id_column='partner_id',
        labour_amount_column='adj_labour_amount',
        paint_amount_column='adj_paint_amount',
        parts_amount_column='adj_parts_amount',
        other_amount_column='adj_other_amount',
        total_amount_column='adj_total_amount'
    ) }} as adjustment_commission_revenue

from {{ ref('stg_quote_adjustments') }}

Testing the Macro

I added a schema test to verify the macro produces consistent results. In schema.yml for both models, I added a not_null test on the commission column, plus a custom test that verifies commission is always between 0 and the total amount (no negative commissions, no commission exceeding 100%):

# tests/assert_commission_bounds.sql

{% test commission_within_bounds(model, column_name, total_column) %}

select *
from {{ model }}
where {{ column_name }} < 0
   or {{ column_name }} > {{ total_column }}

{% endtest %}

Applied in the YAML:

models:
  - name: int_bids__commission
    columns:
      - name: commission_revenue
        tests:
          - not_null
          - commission_within_bounds:
              total_column: total_invoice_amount

Why Not a dbt Variable or Seed?

I considered a few alternatives before settling on a macro:

• A seed CSV with partner rates — this would work for flat-commission partners but doesn’t handle the tiered logic cleanly. You’d need a seed with rows per partner per cost category, plus a join and pivot, which is more complex than a CASE statement.
• A dbt variable dict — same issue: variables are great for simple key-value lookups, but tiered vs. flat logic needs conditional branching that’s more natural in a macro.
• A SQL function (UDF) — possible in BigQuery, but UDFs add runtime overhead and are harder to test within dbt. The macro compiles to plain SQL, so there’s zero runtime cost.

The macro approach keeps everything in dbt’s compile step. When you run dbt compile, you get the full expanded SQL for each model — easy to review and debug.

Lessons Learned

• Duplicate business logic is a ticking time bomb. It’s not a matter of if the copies diverge — it’s when. The fix took an hour. The investigation, audit, and trust repair took days.
• An ELSE 0 branch hides problems. Having the default produce zero revenue meant missing partner configurations were invisible. A non-zero default (or even raising an error) would have surfaced this immediately.
• dbt macros are underused for business logic consolidation. Most teams use macros for utility functions (date spines, surrogate keys), but they’re equally valuable for encapsulating business rules that appear in multiple models.
• Test the boundaries, not just existence. A simple not_null test wouldn’t have caught the €0 revenue — the column was populated, just wrong. The bounds test (commission between 0 and total) catches a broader class of errors.

Since deploying the macro, we’ve added two more partners to the tiered pricing logic. Each addition was a single edit to one file, compiled and tested in minutes. That’s the payoff of DRY in a data warehouse — not just less code, but fewer places where things can quietly go wrong.