dbt for transformations: real-world lessons and examples

After months of building data pipelines with Spark, you realize your transformations are scattered across notebooks, SQL files, and brittle scripts. dbt for transformations: real-world lessons and examples shows how to bring software engineering discipline to your data layer—using SQL as your modeling language while gaining testing, documentation, and lineage tracking for free. We've learned this the hard way across e-commerce analytics, trading platforms, and SaaS data warehouses.

Why dbt matters in a Spark world

dbt (data build tool) has changed how we think about data transformation. Instead of writing one-off Spark jobs or sprawling SQL scripts, dbt lets you define transformations as models—reusable SQL SELECT statements that become tables or views in your warehouse. The magic is that dbt handles dependency resolution, incremental builds, and testing, leaving you to focus on business logic.

In production systems, we've seen teams reduce transformation complexity by 40% after migrating from hand-rolled Spark scripts to dbt. The biggest win isn't performance; it's maintainability. When your analytics team can version control, review, and test SQL like software engineers do code, data quality improves dramatically.

Models — SELECT statements become tables or views

At its core, a dbt model is a SQL file containing a SELECT statement. dbt materializes it as a table, view, or incremental table depending on your config. Let's walk through a real example from an e-commerce platform.

Suppose you're building a customer lifetime value (CLV) model. You start with raw events in Spark:

-- models/marts/customer_ltv.sql
{{ config(
    materialized='table',
    indexes=[
        {'columns': ['customer_id'], 'type': 'btree'},
    ]
) }}

with customer_orders as (
    select
        customer_id,
        order_id,
        order_total,
        order_date,
        row_number() over (partition by customer_id order by order_date) as order_num
    from {{ ref('stg_orders') }}
),

order_cohorts as (
    select
        customer_id,
        min(order_date) as first_order_date,
        count(distinct order_id) as lifetime_orders,
        sum(order_total) as lifetime_value,
        max(order_date) as last_order_date,
        datediff(max(order_date), min(order_date)) as customer_lifetime_days
    from customer_orders
    group by customer_id
)

select
    customer_id,
    first_order_date,
    lifetime_orders,
    lifetime_value,
    last_order_date,
    customer_lifetime_days,
    case
        when datediff(current_date(), last_order_date) <= 30 then 'active'
        when datediff(current_date(), last_order_date) <= 90 then 'at_risk'
        else 'dormant'
    end as status
from order_cohorts

Three things stand out here:

• CTEs for readability: We break the logic into named steps. Anyone reading this six months later understands the flow.
• The ref() macro: {{ ref('stg_orders') }} creates a dependency. dbt knows to build stg_orders first, and if that changes, this model knows it needs a rebuild.
• Materialization config: materialized='table' means this runs once and stores the result. For a model queried frequently but updated nightly, a table beats a view.

In a Spark environment using dbt-spark, you can push incremental logic too:

-- models/incremental_events.sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='event_id'
) }}

select
    event_id,
    customer_id,
    event_type,
    event_timestamp,
    event_data
from raw.events
{% if execute %}
    {% if this.identifier in database.list_tables() %}
        where event_timestamp > (select max(event_timestamp) from {{ this }})
    {% endif %}
{% endif %}

Here, dbt runs a MERGE to upsert only new events since the last build. On a table with billions of rows, this saves hours of compute.

Tests — Not null, unique and relationships

In production, bad data breaks dashboards and reports silently. We've seen a single NULL in a customer_id column cascade through analytics for weeks. dbt tests catch these issues automatically.

dbt ships with four generic tests: not_null, unique, accepted_values, and relationships. You define them in YAML:

-- models/schema.yml
version: 2

models:
  - name: customer_ltv
    description: "Customer lifetime value and status."
    columns:
      - name: customer_id
        description: "Unique customer identifier."
        tests:
          - not_null
          - unique
      - name: lifetime_value
        description: "Total spend in USD."
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 10000000
      - name: status
        description: "Customer lifecycle status."
        tests:
          - accepted_values:
              values: ['active', 'at_risk', 'dormant']
  - name: stg_orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id

When you run dbt test, it executes these checks. If a test fails, the build halts—preventing bad data from reaching your users. In a trading platform we worked on, a relationships test caught a bug where order records were being linked to deleted customers. Caught it before production impact.

You can also write custom SQL tests:

-- tests/assert_order_dates_reasonable.sql
select count(*) as issue_count
from {{ ref('stg_orders') }}
where order_date > current_date()
  or order_date < '2000-01-01'
having count(*) > 0

If this query returns rows, the test fails. This catches data anomalies—like accidentally loading orders from the future—that generic tests miss.

Docs — Generated lineage and documentation

One of dbt's underrated superpowers is documentation. You write it once in YAML, and dbt generates an interactive website showing:

• Column-level descriptions and data types
• Model dependencies and lineage DAG
• Test results and coverage
• SQL parsing to show columns used and produced

At an e-commerce company, the analytics team had 50+ models. New hires spent days reverse-engineering which transformations fed which dashboards. After documenting in dbt YAML, onboarding time dropped to hours.

Here's a realistic example:

-- models/schema.yml
version: 2

models:
  - name: customer_ltv
    description: |
      Customer lifetime metrics computed daily.
      Used by retention dashboards and ML models for churn prediction.
    meta:
      owner: analytics
      refresh: daily
      pii: true
    columns:
      - name: customer_id
        description: "UUID, primary key from production customers table."
      - name: lifetime_value
        description: "USD revenue, excludes refunds."
      - name: status
        description: "Computed from days since last order."

  - name: stg_orders
    description: "Staging layer for raw orders. Cleaned but not aggregated."
    depends_on:
      - name: raw_orders
    meta:
      owner: data-eng

Run dbt docs generate && dbt docs serve, and you get a searchable site. Click a model, see the lineage, drill into a column, read the definition. For teams, this is invaluable.

Works with Spark — dbt-spark and dbt-databricks adapters

dbt doesn't come with Spark support built in. Instead, adapters handle dialect differences. The two main adapters are:

• dbt-spark: Works with open-source Spark and Delta Lake. Best if you're running Spark on-prem or EC2.
• dbt-databricks: Optimized for Databricks. Supports SQL warehouses, clusters, and Unity Catalog.

Installing and configuring dbt-spark looks like this:

pip install dbt-spark[pyodbc]

# profiles.yml
my_spark_project:
  target: dev
  outputs:
    dev:
      type: spark
      method: session  # Use an existing Spark session
      schema: analytics_dev
      threads: 4
      connect_retries: 2
      connect_timeout: 10
      user: ''
      password: ''

Or, if you're using Databricks SQL Warehouse:

pip install dbt-databricks

# profiles.yml
my_project:
  target: prod
  outputs:
    prod:
      type: databricks
      host: adb-12345.cloud.databricks.com
      http_path: /sql/1.0/warehouses/abc123
      token: dapi1234567890abcdef
      schema: analytics
      threads: 4
      timeout_seconds: 300

From there, the rest is vanilla dbt. You write SQL, define models and tests, and run dbt run. The adapter translates to Spark dialect. If you use specific Spark features like OPTIMIZE or VACUUM for Delta tables, you can include them in post-hooks:

{{ config(
    materialized='table',
    post_hook=[
        "optimize {{ this }} zorder by (customer_id)",
        "vacuum {{ this }}"
    ]
) }}

select * from {{ ref('customer_ltv') }}

This tells Spark to optimize the table layout and remove old file versions after building—keeping your data lake lean.

Common mistakes

• Treating dbt as just SQL: The real value is version control, testing, and docs. If you're not committing models to Git and running tests in CI/CD, you're missing 80% of the benefit.
• Overusing views: Views are convenient, but they recompute on every query. For data queried often, use tables or incremental models. Profile before you materialize.
• Weak test coverage: A model with no tests is worse than a model that doesn't exist—you'll trust it. At minimum, test primary keys and foreign keys.
• Ignoring incremental builds: Rebuilding a 10-billion-row fact table every night is expensive. Incremental builds save compute; use incremental_strategy='merge' for slowly-changing dimensions.
• Poor naming and documentation: tmp_a and staging_2 tell you nothing. Use prefixes like stg_ for staging, fct_ for facts, dim_ for dimensions, and document every model.

Key takeaways

• dbt models are SELECT statements versioned in Git, tested automatically, and materialized as tables or views with dependency tracking.
• Tests (not_null, unique, relationships, and custom SQL) catch data issues before they hit dashboards and reports.
• Documentation in YAML generates lineage DAGs and searchable reference sites, cutting onboarding time and supporting knowledge transfer.
• dbt-spark and dbt-databricks adapters integrate seamlessly with Spark clusters and Databricks, translating dbt SQL to Spark dialect automatically.
• Treat dbt as a software engineering discipline for data: version control, CI/CD testing, and peer review of SQL reduce bugs and improve data quality across the organization.