dbt: Transform Data in Your Warehouse

By Kokil Thapa | Last reviewed: September 2026

Your application writes rows to Postgres or MySQL every day. Marketing exports CSVs. Finance pulls spreadsheets. Nobody trusts the dashboard numbers. That gap is exactly why teams adopt dbt: Transform Data in Your Warehouse — a workflow where you version SQL transformations, test them, and document them beside the data they produce. Unlike legacy ETL that copies data out, processes it elsewhere, and loads it back, dbt runs inside the warehouse. If you already manage data engineering alongside DevOps, dbt fits naturally into the modern ELT stack.

What is dbt and how does it transform data in your warehouse?

dbt (data build tool) is an open-source CLI and framework for analytics engineering. You write SELECT statements in .sql files called models. dbt compiles them, resolves dependencies, and materialises results as views or tables in Snowflake, BigQuery, Redshift, Postgres, Databricks, or other supported platforms.

The mental model is simple. Raw data lands in staging schemas through ingestion tools like Fivetran, Airbyte, or custom pipelines. dbt sits on top and turns that raw layer into clean, tested, documented datasets that BI tools consume.

On production Laravel applications I maintain, the operational database is not the analytics database. Application DBs optimise for transactions. Warehouses optimise for aggregations. dbt gives you a disciplined way to bridge that split without writing brittle one-off scripts.

dbt Core is free and runs locally or in CI. dbt Cloud adds a hosted IDE, scheduling, and observability. Most teams start with Core on a laptop, then graduate to Cloud or self-hosted runners once model count grows past a dozen.

Core concepts you will use daily

• Models — SQL files that SELECT from other models or sources.
• Sources — YAML declarations pointing at raw tables loaded by ingestion.
• Tests — assertions like unique, not_null, and custom SQL checks.
• Macros — reusable Jinja-templated SQL snippets.
• Snapshots — slowly changing dimension tracking for historical rows.
• Seeds — CSV reference data versioned in Git.

Official reference lives at dbt's documentation. Treat that site as the source of truth for adapter-specific syntax.

How do you set up dbt Core to transform warehouse data?

Start with Python 3.9 or newer and a warehouse you can reach from your machine. The setup below uses Postgres, but the project structure is identical for Snowflake or BigQuery once you swap the adapter.

Step 1: Install dbt and initialise a project

python3 -m venv .venv
source .venv/bin/activate
pip install dbt-postgres

dbt init analytics_project
cd analytics_project

dbt prompts for adapter credentials. Store secrets in ~/.dbt/profiles.yml, never in Git. Your project repo holds models and tests only.

Step 2: Define sources and staging models

Create models/staging/stg_orders.sql:

with source as (
    select * from {{ source('app', 'orders') }}
),

renamed as (
    select
        id as order_id,
        user_id,
        total_amount,
        created_at::timestamp as ordered_at
    from source
    where deleted_at is null
)

select * from renamed

Declare the source in models/staging/_sources.yml:

version: 2

sources:
  - name: app
    database: analytics
    schema: raw
    tables:
      - name: orders
        loaded_at_field: _fivetran_synced

Step 3: Build mart models downstream

Mart models answer business questions. A daily revenue summary might live in models/marts/fct_daily_revenue.sql:

select
    date_trunc('day', ordered_at) as order_date,
    count(*) as order_count,
    sum(total_amount) as gross_revenue
from {{ ref('stg_orders') }}
group by 1

The {{ ref('stg_orders') }} macro tells dbt about dependency order. Run dbt run and dbt builds staging first, then the mart.

Step 4: Run, test, and document

1. dbt debug — verify warehouse connection.
2. dbt run — materialise all models.
3. dbt test — execute schema and custom tests.
4. dbt docs generate && dbt docs serve — browse lineage in a local site.

For JSON-heavy staging columns, validate payloads before modelling with a JSON formatter during exploratory queries. That small step prevents silent type coercion bugs in downstream marts.

How does dbt compare to ETL tools and ad-hoc SQL scripts?

Teams often ask whether they need dbt at all. The honest answer depends on team size, warehouse maturity, and how often definitions change.

dbt is not a replacement for Airflow. Airflow schedules when jobs run. dbt defines what SQL transformations do. In practice you trigger dbt run from an Airflow BashOperator or a GitLab CI pipeline after ingestion completes.

ELT beats traditional ETL on cost for cloud warehouses. Snowflake and BigQuery charge for compute inside the platform. Moving gigabytes to an external server, transforming, and loading back wastes time and money. Google's BigQuery documentation describes this pushdown pattern as the recommended approach for large datasets.

For Nepal-based companies handling personal data, keeping transformations inside a controlled warehouse region supports data residency and compliance goals. You audit one system instead of chasing scripts on three servers.

What dbt testing and documentation patterns actually work in production?

Untested marts are how dashboards lie. dbt makes testing cheap enough that you should test every primary key and every foreign key relationship that matters.

Schema tests in YAML

version: 2

models:
  - name: fct_daily_revenue
    columns:
      - name: order_date
        tests:
          - not_null
          - unique
      - name: gross_revenue
        tests:
          - not_null

Custom data tests

Save tests/assert_revenue_non_negative.sql:

select order_date, gross_revenue
from {{ ref('fct_daily_revenue') }}
where gross_revenue < 0

Any returned row fails the test. This pattern catches sign errors after currency conversion logic changes.

Documentation that stays current

Add descriptions in the same YAML file. dbt docs pull them into the lineage site automatically. When a new analyst joins, they read model docs instead of Slack history.

Pair dbt tests with test data management for pipelines in lower environments. Seed anonymised subsets, run the full test suite in CI, and only then promote to production.

On a booking platform like Adventure Third Pole Trek, operational tables track reservations, payments, and supplier payouts. Staging models normalise those entities once. Mart models power finance and marketing without duplicating business rules in PHP controllers or BI calculated fields.

How do you deploy dbt transformations to production with CI/CD?

Local dbt run on a laptop does not scale. Production needs locked dependencies, isolated targets, and rollback discipline — the same principles I apply to Linux deployment workflows on application servers.

Project layout for teams

• models/staging/ — one model per source table, minimal joins.
• models/intermediate/ — reusable logic blocks between staging and marts.
• models/marts/ — business-facing tables grouped by domain (finance, marketing).
• macros/ — shared Jinja for date spines, currency conversion, surrogate keys.
• packages.yml — pin community packages like dbt_utils.

GitLab CI example

stages:
  - test
  - deploy

dbt_ci:
  stage: test
  image: ghcr.io/dbt-labs/dbt-postgres:1.9.latest
  script:
    - dbt deps
    - dbt debug --target ci
    - dbt build --target ci --select state:modified+
  only:
    - merge_requests

dbt_prod:
  stage: deploy
  script:
    - dbt deps
    - dbt build --target prod
  only:
    - main

Use dbt build instead of separate run and test commands. It runs tests immediately after each model builds, so failures surface early.

Materialisation strategy

Choose materialisations deliberately in model config or dbt_project.yml:

models:
  analytics_project:
    staging:
      +materialized: view
    marts:
      +materialized: table

Views are cheap for staging layers that change often. Tables speed up BI queries on large marts. Incremental models suit event streams where only recent partitions change daily.

Schedule production runs after ingestion finishes. If you replicate application data with tools described in cross-cloud replication guides, add a sensor or timestamp check so dbt never builds marts on stale raw tables.

Cache frequently queried marts in Redis only when BI latency still misses SLA after warehouse tuning. Most teams find proper indexing and table materialisation enough before adding another layer. See Redis data structures for when that extra cache earns its keep.

What are common dbt mistakes and how do you avoid them?

I've seen the same failures on client analytics projects regardless of warehouse vendor.

Skipping staging layers. Analysts join raw tables directly in marts. Column names drift. Deleted-row logic duplicates. Always normalise in staging first.

God models. One 400-line SQL file with twelve joins. Split into intermediate models. Each file should answer one logical question.

No primary key tests. Duplicates silently double revenue. Add unique and not_null on every mart grain column.

Running full refresh in production casually. It drops and rebuilds tables. Use incremental models or --select flags for surgical fixes.

Ignoring cost. BigQuery and Snowflake bill per query. Tag models by domain, use dbt run --select tag:finance, and set warehouse auto-suspend policies.

When analytics supports a product built with Laravel or custom PHP, keep operational reporting in the app and strategic reporting in the warehouse. Mixing both in one database creates lock contention and messy permissions. A enterprise application architecture review helps draw that boundary early.

For larger programmes, pair dbt with a lake ingestion path. Raw files land in S3 first, then sync to the warehouse. The pattern in building a data lake on S3 complements dbt when you need cheap archival storage plus fast SQL marts.

Key Takeaways

• dbt transforms data in your warehouse using version-controlled SQL models — data stays put, compute scales with your platform.
• Structure projects as staging → intermediate → marts, with sources declared in YAML and dependencies via ref().
• Run dbt build in CI on every pull request; promote to production only after tests pass on an isolated schema.
• Test primary keys, foreign keys, and business rules — untested marts produce untrusted dashboards.
• dbt orchestrates SQL transformations; pair it with Airflow or GitLab CI for scheduling, not as a replacement.
• Document models in YAML so lineage stays accurate without a separate wiki that rots in six months.

People Also Ask

Is dbt free to use?

dbt Core is open source and free. dbt Cloud offers a free tier for one developer seat, with paid tiers for scheduling, IDE hosting, and enterprise SSO. Most small teams run Core in GitLab CI at zero licensing cost.

Which warehouses does dbt support?

dbt supports Snowflake, BigQuery, Redshift, Postgres, Databricks, Spark, Trino, and other adapters maintained by dbt Labs and community contributors. Install the adapter package matching your warehouse, for example dbt-snowflake or dbt-bigquery.

Can dbt replace my ETL tool entirely?

dbt replaces the transformation step, not ingestion. You still need a loader to copy application data into the warehouse. After raw tables exist, dbt handles all downstream SQL logic, testing, and documentation.

Do I need to know Python to use dbt?

No. dbt projects are primarily SQL and YAML. Python installs the CLI, but daily work is writing SELECT statements and Jinja macros. Analysts with strong SQL skills become productive within a week.

Ship warehouse transformations you can trust

Analytics debt accumulates quietly. One-off SQL scripts become folklore. Dashboards disagree. Adopting dbt: Transform Data in Your Warehouse gives your team a repeatable, testable, documented path from raw ingestion to business-ready marts. Start with one domain — orders, bookings, or leads — prove the workflow in CI, then expand.

If you need help designing warehouse architecture, ingestion pipelines, or analytics layers alongside your web application, review the custom software development services or browse the full project portfolio. For a scoped discussion on your stack, contact us with your current warehouse, ingestion tools, and reporting goals.