07.08.2025: Blog series 3

Apply Business Logic Reusably with dbt and Jinja Templates

Giorgio Frisenda on August 7, 2025

This is part 3 of our blog series on using dbt in the modern marketing data stack, aimed especially at BI managers, data analysts, analytics engineers, and similar roles.

In the first article of our dbt blog series, we explained how dbt simplifies complex SQL workflows and makes it easier to manage marketing data pipelines. In the second blogpost, we explored how dbt empowers teams to enforce data quality using automated testing, including schema-level checks (like unique, not_null, accepted_values, and relationships) and source freshness tests to detect stale data before it affects dashboards and reporting.

The Challenge: Repeating Logic Across Many Marketing Sources

In fast-moving marketing environments, the same business logic often needs to be applied across multiple data sources like extracting UTM parameters from URLs, standardizing campaign names, or normalizing platforms (e.g. Instagram, Facebook, TikTok).

In traditional SQL workflows (e.g. within Snowflake), this means copying and pasting logic across models. This leads to duplication, inconsistent results, and hard-to-maintain code.

The Solution: Reusable Logic with dbt and Jinja

dbt solves this with Jinja templating. Jinja is a lightweight templating language that lets you embed control logic (like loops and if/else conditions) directly into your SQL models via reusable macros.

Key Benefits of Using Jinja Templates in dbt

Reusable logic: Define once, use everywhere. Macros let you encapsulate complex SQL logic and call it across models.
Clean, focused models: Business logic stays front and center, while repetitive code is abstracted away.
Consistent output: Shared macros prevent mismatches in calculations across teams or data sources.
Easy maintenance: Update a macro in one place and instantly propagate changes project-wide.

Practical example: Standardizing UTM Parameter Extraction

Imagine you’re collecting campaign data from Facebook, Google Ads, LinkedIn, and TikTok. Each source logs click URLs differently, but you want to extract consistent UTM parameters (utm_campaign, utm_source, etc.) from those URLs.

With dbt, you can build reusable macros to handle this once and apply it everywhere.

-- macros/clean_url.sql
{% macro clean_url(column_name) %}
    LOWER(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    DECODE_URL(
                                        SPLIT_PART(SPLIT_PART({{ column_name }}, '?', 1), '#', 1)
                                    ),
                                    'https://', ''
                                ),
                                'http://', ''
                            ),
                            'ö', 'oe'
                        ),
                        'ü', 'ue'
                    ),
                    'ä', 'ae'
                ),
                'ß', 'ss'
            )
        )
    )
{% endmacro %}

-- macros/extract_utm_params.sql
{% macro extract_utm_params(url_col) %}
  regexp_extract({{ url_col }}, '(?:[?&])utm_source=([^&]+)', 1) as utm_source,
  regexp_extract({{ url_col }}, '(?:[?&])utm_medium=([^&]+)', 1) as utm_medium,
  regexp_extract({{ url_col }}, '(?:[?&])utm_campaign=([^&]+)', 1) as utm_campaign,
  regexp_extract({{ url_col }}, '(?:[?&])utm_content=([^&]+)', 1) as utm_content,
  regexp_extract({{ url_col }}, '(?:[?&])utm_term=([^&]+)', 1) as utm_term
{% endmacro %}

And use the same code across different files and models:

-- models/intermediate/facebook_campaigns.sql
SELECT
  account_id,
	....
  {{ clean_url(url') }} AS cleaned_click_url,
  {{ extract_utm_params('cleaned_click_url') }}
	....
FROM {{ source('facebook_ads', 'campaigns') }}

-- models/intermediate/linkedin_campaigns.sql
SELECT
  account_id,
	....
  {{ clean_url(url') }} AS cleaned_click_url,
  {{ extract_utm_params('cleaned_click_url') }}
	....
FROM {{ source('linkedin_ads', 'campaigns') }}

-- models/intermediate/outbrain_campaigns.sql
SELECT
  account_id,
	....
  {{ clean_url(url') }} AS cleaned_click_url,
  {{ extract_utm_params('cleaned_click_url') }}
	....
FROM {{ source('outbrain_ads', 'campaigns') }}

Conclusion: Why dbt Matters in Marketing Analytics

The modern marketing data stack demands flexibility, speed, and trust at scale. When used alongside a powerful warehouse like Snowflake, dbt brings a strategic layer of transformation that simplifies workflows, enforces data quality, and promotes reusability.

Let’s recap the core advantages of dbt for marketing teams:

Simplified transformations: Declarative SQL models eliminate complex procedural logic and make pipelines easier to read and maintain
Embedded testing and monitoring: Ensures your KPIs are trustworthy and your dashboards don’t break, even across multiple platforms
Reusable, scalable business logic: Jinja templates promote consistency and reduce repetitive code across marketing sources like Facebook, LinkedIn, or TikTok

In short, dbt helps your team spend less time debugging pipelines and more time driving insights. If you’re serious about building a modern, reliable marketing analytics stack, should consider dbt as one option for a central role.

We are happy to help you if you’d like to know more about dbt, want to get started or are stuck in a current project.

dbt Training

dbt Consulting