Data Transformations with dbt — Models, Tests & Production Deployments

In this tutorial, you'll learn about Data Transformations with dbt. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

dbt (data build tool) transforms raw data in your warehouse using SQL SELECT statements — turning analysts into data engineers by managing materializations, dependencies, testing, and documentation automatically.

What You'll Learn

By the end of this tutorial, you'll understand dbt's model hierarchy, Jinja templating for dynamic SQL, materialization strategies (view, table, incremental, ephemeral), generic and singular tests, documentation generation, and how to deploy dbt in production with CI/CD.

Why It Matters

Modern ELT workflows load raw data into the warehouse, then transform it there. Writing raw CREATE TABLE and INSERT statements is fragile, hard to test, and impossible to document. dbt treats transformations as code: version-controlled, peer-reviewed, tested, and self-documenting. DodaTech runs 200+ dbt models to transform Doda Browser event data into analytics-ready tables for product, marketing, and engineering teams.

Real-World Use

dbt Labs processes petabytes daily across thousands of customer projects. GitLab uses dbt for its product analytics pipeline. JetBlue transforms operational data with dbt models that power real-time dashboards for flight operations.

dbt Transformation Architecture

flowchart LR
    subgraph "Sources"
        A[Raw Tables] --> B[Staging Layer]
    end
    subgraph "Staging"
        B --> C[Clean + Type Cast]
    end
    subgraph "Intermediate"
        C --> D[Joins + Aggregations]
    end
    subgraph "Marts"
        D --> E[Dimensional Models]
        D --> F[Fact Tables]
    end
    subgraph "Consumption"
        E --> G[BI Tools]
        F --> H[ML Models]
        F --> I[APIs]
    end
    style B fill:#f90,color:#fff
    style E fill:#f90,color:#fff

What Is dbt?

dbt sits between your raw data and your business users. It reads from source tables, applies transformations defined in SQL models, and writes the results back to the warehouse as views or tables.

The key insight: dbt models are just SELECT statements. You never write CREATE TABLE, INSERT, or ALTER. dbt handles data definition language (DDL) and data manipulation language (DML) automatically based on your materialization configuration.

Core dbt Concepts

Models and Materializations

-- models/staging/stg_web_events.sql
-- Stage model: clean raw web events
with source as (
    select * from {{ source('web_tracking', 'page_views') }}
),

cleaned as (
    select
        event_id,
        visitor_id,
        page_url,
        parse_timestamp(event_time) as event_time,
        cast(duration_ms as integer) as duration_ms,
        coalesce(referrer, 'direct') as referrer_source
    from source
    where event_id is not null
)

select * from cleaned

-- models/marts/dim_visitors.sql
-- Dimension: visitor profiles
with stg_events as (
    select * from {{ ref('stg_web_events') }}
),

visitor_agg as (
    select
        visitor_id,
        min(event_time) as first_visit_at,
        max(event_time) as last_visit_at,
        count(distinct page_url) as pages_viewed,
        sum(duration_ms) as total_duration_ms,
        count(*) as session_count
    from stg_events
    group by 1
)

select
    visitor_id,
    first_visit_at,
    last_visit_at,
    pages_viewed,
    total_duration_ms,
    session_count,
    case
        when session_count >= 10 then 'power_user'
        when session_count >= 3 then 'regular'
        else 'casual'
    end as visitor_segment
from visitor_agg

-- models/marts/fct_daily_metrics.sql
-- Fact: daily aggregated metrics
with events as (
    select * from {{ ref('stg_web_events') }}
),

daily as (
    select
        date_trunc('day', event_time) as event_date,
        count(distinct visitor_id) as unique_visitors,
        count(*) as total_page_views,
        avg(duration_ms) as avg_duration_ms,
        count(distinct page_url) as unique_pages
    from events
    group by 1
)

select * from daily

Expected output (dbt run):

17:30:00  Running 3 models (3 already materialized as views)
17:30:01  1 of 3 START view model analytics.stg_web_events ........... [RUN]
17:30:02  1 of 3 OK created view model analytics.stg_web_events ...... [OK]
17:30:02  2 of 3 START table model analytics.dim_visitors ............ [RUN]
17:30:04  2 of 3 OK created table model analytics.dim_visitors ....... [OK]
17:30:04  3 of 3 START incremental model analytics.fct_daily_metrics . [RUN]
17:30:06  3 of 3 OK created incremental model analytics.fct_daily_metrics [OK]

Testing Data Quality

dbt provides built-in generic tests (unique, not_null, accepted_values, relationships) and supports custom singular tests.

# models/schema.yml
version: 2

models:
  - name: stg_web_events
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: visitor_id
        tests:
          - not_null
      - name: duration_ms
        tests:
          - not_null
          - accepted_values:
              values: [0, 1, 2, 3, 4, 5]

  - name: dim_visitors
    columns:
      - name: visitor_id
        tests:
          - unique
          - not_null

  - name: fct_daily_metrics
    columns:
      - name: event_date
        tests:
          - not_null
      - name: unique_visitors
        tests:
          - not_null

-- tests/assert_no_negative_duration.sql
-- Singular test: duration_ms must not be negative
select *
from {{ ref('stg_web_events') }}
where duration_ms < 0

-- tests/assert_visitor_join_valid.sql
-- Singular test: every visitor has at least one event
select v.visitor_id
from {{ ref('dim_visitors') }} v
left join {{ ref('stg_web_events') }} e
    on v.visitor_id = e.visitor_id
where e.visitor_id is null

Jinja Templating and Macros

dbt extends SQL with Jinja for dynamic Code Generation.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

-- macros/pivot_columns.sql
{% macro pivot_metrics(source_table, value_column, category_column, categories) %}
    select
        date_trunc('day', event_date) as day,
        {% for category in categories %}
        sum(case when {{ category_column }} = '{{ category }}' then {{ value_column }} else 0 end) as {{ category }}_metric
        {% if not loop.last %},{% endif %}
        {% endfor %}
    from {{ source_table }}
    group by 1
{% endmacro %}

Production dbt Workflow

dbt_project.yml

name: dodatech_analytics
version: '1.1'
config-version: 2
profile: dodatech_prod

model-paths: ["models"]
test-paths: ["tests"]
macro-paths: ["macros"]
docs-paths: ["docs"]

models:
  dodatech_analytics:
    staging:
      +materialized: view
      +schema: staging
    marts:
      +materialized: table
      +schema: analytics
      dims:
        +materialized: table
      facts:
        +materialized: incremental
        +unique_key: event_date

Commands

# Run all models in dependency order
dbt run

# Run specific model with upstream dependencies
dbt run --model stg_web_events+

# Run tests after models
dbt test

# Generate and serve documentation
dbt docs generate
dbt docs serve --port 8080

# Build, test, and snapshot in one command
dbt build --select tag:daily

# Run with full refresh for incremental models
dbt run --full-refresh --select fct_daily_metrics

Common dbt Mistakes

1. Not Using ref() for Model References

Hard-coded table names like analytics.stg_web_events break when schemas change or environments differ. Always use {{ ref('model_name') }} so dbt resolves dependencies automatically.

2. Overly Complex Single Models

A 300-line SQL model is impossible to debug. Break logic into intermediate models. Each model should represent one transformation step: clean, join, aggregate, enrich.

3. Skipping Tests on Sources

Raw data is unpredictable. Add not_null and unique tests to source tables. Better to fail early at ingestion than discover bad data in a monthly board meeting.

4. No Freshness Checks

Sources that stop loading silently poison downstream models. Configure freshness blocks in sources.yml with warn_after and error_after thresholds tied to alerting.

5. Forgetting Full Refresh for Incremental Models

Schema changes on incremental models only apply to new data. Existing rows keep the old schema. Run dbt run --full-refresh after every schema change to rebuild the table.

Practice Questions

1. What is the difference between a view and a table materialization in dbt? A view stores the query definition and runs on every read — no storage cost, data is always fresh. A table persists results to disk — faster queries but requires rebuilds via dbt run. Views suit staging, tables suit marts.

2. When would you use an incremental model instead of a table model? When working with large datasets that grow over time (event logs, daily metrics). Incremental models only Process new or changed records using a unique_key and a filter like where event_date > (select max(event_date) from target_table), reducing runtime from hours to minutes.

3. How does dbt resolve model dependencies? dbt parses all ref() calls in model SQL to build a dependency graph. When running, dbt executes models in topological order — dependencies first. The lineage graph is visible in dbt docs and critical for debugging.

Frequently Asked Questions

{{< faq question="Can I use dbt with non-SQL databases like MongoDB or S3?">}} dbt works best with SQL-based analytic databases (Snowflake, BigQuery, Redshift, Databricks, DuckDB, Postgres). For non-SQL sources, use a separate ingestion tool (Fivetran, Airbyte) to land raw data in the warehouse, then transform with dbt. dbt's Python models feature allows pandas or PySpark transformations for advanced cases. {{< /faq >}}

{{< faq question="How do I handle secrets and environment-specific configurations in dbt?">}} Use environment variables via {{ env_var('DBT_PASSWORD') }} in profiles.yml. Never hard-code credentials. For different environments, use target profiles (dev, staging, prod) and set target in your CI/CD pipeline. dbt Cloud manages secrets natively. For self-hosted dbt Core, use .env files or vault systems. {{< /faq >}}

{{< faq question="What is the difference between dbt Core and dbt Cloud?">}} dbt Core is the open-source command-line tool — free, self-hosted, extensible via plugins. dbt Cloud is the managed SaaS platform with a web IDE, job scheduler, CI/CD, Observability, and security features. Both run the same SQL models. Start with Core for flexibility; migrate to Cloud when you need team collaboration and monitoring.{{< /faq >}}

Mini Project: dbt Model Lineage Visualizer

# lineage_visualizer.py
# Simulate dbt model dependency resolution
class DbtProject:
    def __init__(self, name):
        self.name = name
        self.models = {}

    def add_model(self, name, materialization, dependencies=None):
        self.models[name] = {
            "name": name,
            "materialization": materialization,
            "dependencies": dependencies or [],
            "status": "pending",
        }

    def resolve_execution_order(self):
        graph = {}
        for name, model in self.models.items():
            graph[name] = model["dependencies"]
        sorted_models = []
        visited = set()
        def visit(node):
            if node in visited:
                return
            visited.add(node)
            for dep in graph.get(node, []):
                if dep in graph:
                    visit(dep)
            sorted_models.append(node)
        for node in graph:
            visit(node)
        return sorted_models

    def run(self):
        order = self.resolve_execution_order()
        print(f"=== dbt Run: {self.name} ===")
        print(f"Execution order: {' -> '.join(order)}")
        print()
        for model_name in order:
            model = self.models[model_name]
            model["status"] = "running"
            print(f"[dbt] RUN model: {model_name} ({model['materialization']})")
            model["status"] = "success"
            print(f"[dbt] SUCCESS model: {model_name}")
        print()
        print("=== Lineage ===")
        for name, model in self.models.items():
            deps = model["dependencies"]
            if deps:
                print(f"  {name} <-- {', '.join(deps)}")
            else:
                print(f"  {name} <-- [source]")

project = DbtProject("dodatech_analytics")
project.add_model("stg_web_events", "view")
project.add_model("dim_visitors", "table", ["stg_web_events"])
project.add_model("fct_daily_metrics", "incremental", ["stg_web_events"])
project.add_model("rpt_visitor_segments", "table", ["dim_visitors", "fct_daily_metrics"])
project.run()

Expected output:

=== dbt Run: dodatech_analytics ===
Execution order: stg_web_events -> dim_visitors -> fct_daily_metrics -> rpt_visitor_segments

[dbt] RUN model: stg_web_events (view)
[dbt] SUCCESS model: stg_web_events
[dbt] RUN model: dim_visitors (table)
[dbt] SUCCESS model: dim_visitors
[dbt] RUN model: fct_daily_metrics (incremental)
[dbt] SUCCESS model: fct_daily_metrics
[dbt] RUN model: rpt_visitor_segments (table)
[dbt] SUCCESS model: rpt_visitor_segments

=== Lineage ===
  stg_web_events <-- [source]
  dim_visitors <-- stg_web_events
  fct_daily_metrics <-- stg_web_events
  rpt_visitor_segments <-- dim_visitors, fct_daily_metrics

Related Concepts

What's Next

You now understand dbt's model hierarchy, testing framework, and production workflow. Next, learn how Apache Spark handles large-scale data transformations and how Python integrates with dbt for custom macros and hooks.

• Practice daily — Convert three existing SQL scripts into dbt models with tests
• Build a project — Run dbt Core with DuckDB locally on a public dataset (NYC taxi data)
• Explore related topics — Check out dbt exposures, metrics layer, and dbt Mesh for multi-project deployments

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.