dbt (data build tool) lets analysts and analytics engineers write the transform step of an ELT pipeline as version-controlled, testable, modular SQL SELECT statements, rather than as ad-hoc queries run manually or buried in a BI tool's custom field logic. Each dbt model is a .sql file containing a single SELECT statement; dbt compiles the dependency graph between models (via ref() calls), determines execution order automatically, and materializes each model as a table or view in the warehouse. The core shift dbt represents for marketing analytics is treating transformation logic with the same rigor as application code: git version control, pull request review, automated testing, and documentation — rather than a one-off query someone ran once and nobody can reproduce.

models/marts/marketing_attribution.sql

sql

-- dbt model: ref() creates an explicit, trackable dependency on another
-- model rather than a hardcoded table name - dbt uses these references
-- to build the full DAG and run models in the correct order.
WITH sessions AS (
  SELECT * FROM {{ ref('stg_ga4_sessions') }}
),
conversions AS (
  SELECT * FROM {{ ref('stg_crm_conversions') }}
),
last_touch_attribution AS (
  SELECT
    s.session_id,
    s.utm_source,
    s.utm_campaign,
    c.conversion_id,
    c.conversion_value,
    ROW_NUMBER() OVER (
      PARTITION BY c.conversion_id
      ORDER BY s.session_timestamp DESC
    ) AS touch_rank
  FROM sessions s
  JOIN conversions c
    ON s.user_id = c.user_id
    AND s.session_timestamp <= c.conversion_timestamp
)
SELECT
  utm_source,
  utm_campaign,
  COUNT(DISTINCT conversion_id) AS attributed_conversions,
  SUM(conversion_value) AS attributed_revenue
FROM last_touch_attribution
WHERE touch_rank = 1
GROUP BY utm_source, utm_campaign

Built-in tests catch broken marketing data before it reaches a dashboard

dbt lets you declare tests directly against models — unique, not_null, accepted_values, relationship tests between tables — that run as part of the pipeline and fail the build if violated. For marketing data specifically, this catches exactly the kind of silent data quality failures that plague attribution and reporting: a utm_campaign field suddenly containing an unexpected null rate after an ad platform API change, a duplicate conversion row inflating reported revenue, a foreign key between sessions and conversions that stops resolving after a schema change upstream. Without these tests, these failures are usually only discovered when someone notices a dashboard number looks wrong — often weeks after the breakage started.

models/marts/marketing_attribution.yml

sql

version: 2
models:
  - name: marketing_attribution
    columns:
      - name: conversion_id
        tests:
          - unique
          - not_null
      - name: utm_source
        tests:
          - not_null
          - accepted_values:
              values: ['google', 'meta', 'linkedin', 'direct', 'organic', 'email']
      - name: attributed_revenue
        tests:
          - dbt_utils.accepted_range:
              min_value: 0

dbt layers in a typical marketing analytics project

Layer	Naming convention	Purpose
Staging	stg_<source>__<entity>	1:1 with raw source tables, light renaming/typing, no business logic
Intermediate	int_<entity>_<verb>	Joins and reusable logic shared across multiple downstream marts
Marts	mart/fct/dim naming per team convention	Business-ready, final tables consumed directly by BI tools and dashboards

dbt's real value for marketing teams is a single source of truth for business logic

Before adopting dbt, it's common for 'how do we define a qualified lead' or 'which attribution model do we use' to be answered differently in three different BI tool custom fields, each drifting independently over time. Centralizing that logic as a single, tested, documented dbt model that every downstream dashboard references means there's exactly one place the definition lives — changing it propagates everywhere consistently, instead of requiring three separate fixes that inevitably fall out of sync.

dbt models still need orchestration to run on a schedule

dbt itself defines and tests transformations but doesn't natively schedule when they run against fresh data — that's handled by an orchestrator (dbt Cloud's own scheduler, Airflow, or a simpler cron-based trigger) that runs dbt run and dbt test after new raw data has landed from the extract-and-load step. A dbt project with no scheduled run is just well-organized SQL sitting idle against stale data.

What's Next?

dbt handles the transformation logic itself, but the end-to-end pipeline — extracting from sources, loading raw data, then running dbt against it on a reliable schedule — needs an orchestration layer to coordinate all three steps, which is where tools like Airflow come in.

I build these systems professionally.

Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.

Work With Me →

Need production AI or MarTech expertise? I build custom LLM workflows, server-side GTM tagging, and analytics infrastructures professionally.

Need custom AI or MarTech setup? Let's build together.

Let's talk →