The Ultimate Guide to dbt

June 2026

A practical guide to dbt: how source definitions, models, tests, macros, project structure, and warehouse-native transforms fit together in a maintainable analytics engineering workflow.

Start Here: What dbt Actually Is

dbt, short for data build tool, is a framework for transforming data inside your warehouse using SQL plus YAML-based tests and documentation. This guide focuses on dbt's SQL-first core, because that is still where most teams spend most of their production modeling time. You write models as select statements, dbt builds a dependency graph, compiles warehouse-specific SQL, and materializes those models in the destination.

This is intentionally a guide to dbt's warehouse-first core, not a full tour of every adjacent surface such as semantic layers, Mesh patterns, or Python-first extensions. The goal here is to make the practical transformation model legible.

The important idea is not just that dbt runs SQL. Plenty of tools can run SQL. dbt turns warehouse transformations into a versioned, testable project rather than a scattered mix of BI queries, notebooks, stored procedures, and manual scripts.

A concrete example looks like this: raw orders data lands in your warehouse, a dbt staging model cleans timestamps and status values, and a gold mart computes daily revenue or customer retention on top. All of that lives in files, with dependency ordering and tests handled explicitly.

• dbt is strongest after load: it assumes the data already exists in the destination and helps you transform it there.
• dbt is SQL-first: most model logic is ordinary SQL rather than a proprietary GUI abstraction.
• dbt is workflow-aware: models, tests, docs, and DAG structure can live in Git and CI like the rest of your engineering stack.
• dbt is not the warehouse itself: the warehouse provides compute and storage; dbt provides the transformation framework on top.

What dbt Is Not

A lot of confusion disappears once you name what dbt does not do. dbt is not an ingestion tool, not a warehouse, and not a magic business-logic generator that removes the need for careful modeling.

Needdbt roleWhat still has to existExtract from sourcesNoAn ingestion path that lands raw data in the destinationStore and execute dataNoA warehouse or lakehouse such as Snowflake, BigQuery, Redshift, or DatabricksTransform with dependency managementYesClear project structure and model ownershipDefine business meaningPartlyHumans still own semantic correctness, naming, and metric design

That matters because teams sometimes say "we use dbt" when what they really mean is "we have a transformation repo." That is not the same as having a good ingestion layer, a good warehouse model, or a trustworthy analytics contract.

dbt Core, dbt Cloud, and the Runtime Split

The simplest split is this: dbt Core is the open-source CLI framework, while managed offerings such as dbt Cloud add scheduling, hosted development workflows, and operational features on top. The modeling concepts stay the same either way.

• dbt Core gives you compilation, DAG execution, tests, docs generation, and full control over where jobs run.
• Managed dbt runtimes can add scheduling, job management, hosted IDEs, and operational observability.
• The project itself is still the durable asset: SQL models, YAML config, tests, and macros that define transformation behavior.

That is useful because your team can learn the dbt mental model without tying that learning to one vendor-managed screen. The project structure is the part that lasts.

The dbt Mental Model: Sources, Models, refs, DAGs, and Materializations

The fastest way to understand dbt is to think in five pieces:

• Sources: named references to raw landed tables in the destination.
• Models: SQL select statements that define tables or views in the warehouse.
• refs: dependency references between models, which let dbt build the DAG.
• DAG: the directed acyclic graph that determines execution order and lineage.
• Materializations: how a model becomes a warehouse object, usually as a view, table, incremental model, or sometimes an ephemeral intermediate.

A model is usually one file, one relation, one clear purpose. A staging model might clean one source table. An intermediate model might encapsulate reusable joins. A mart might expose one stable business-facing output. The source() function connects the model graph to raw landed tables, and ref() connects models to each other so dbt knows build order and lineage automatically.

Materialization choice changes behavior significantly. Views are cheap to define but push work into query time. Tables make downstream queries faster but cost more to rebuild. Incremental models reduce rebuild cost on large facts, but they also introduce correctness questions around late-arriving data, updates, and reruns.

What Lives in a Real dbt Project

Strong dbt projects are easy to scan because the important repository-scoped assets are predictable. Even when conventions vary slightly, the same building blocks show up again and again.

File or directoryWhat it doesWhy it mattersdbt_project.ymlProject-level config and model defaultsSets the operating rules for the whole projectmodels/SQL models grouped by layer or domainHolds the actual transformation logicmodels/schema.yml or layer YAML filesSource definitions, tests, descriptionsKeeps metadata and validation close to the modelsmacros/Reusable SQL or Jinja logicPrevents duplication when patterns repeatpackages.ymlExternal dbt package dependenciesLets you reuse maintained community patternsseeds/Small static reference tables checked into the repoUseful for lookup data and controlled enumerationssnapshots/History capture for changing rows when that pattern is justifiedUseful for preserving attribute history without losing current state

Runtime configuration sits slightly differently. In dbt Core, profiles.yml is often local or environment-managed rather than committed to the repo, and managed runtimes usually keep connection configuration outside the project entirely. That is why it helps to separate repository assets from runtime config in your mental model.

In Skippr's documented integration, the generated dbt project includes files such as dbt_project.yml, profiles.yml, models/schema.yml, models/staging/stg_*.sql, and packages.yml. That matters because the output is ordinary dbt, not a special format you have to reverse engineer later.

Sources, Source Freshness, Macros, and Packages

Production dbt projects get much stronger when the raw dependency boundary is explicit. Source definitions name the raw tables once, keep physical warehouse paths out of downstream SQL, and give the team a place to express freshness expectations.

If the landed raw tables include a load timestamp such as _loaded_at, the YAML can express both the dependency and the staleness contract:

`version: 2 sources:

• name: raw schema: RAW tables:
  • name: orders loaded_at_field: _loaded_at freshness: warn_after: {count: 2, period: hour} error_after: {count: 6, period: hour}`

Macros solve a different problem: repeated SQL patterns. If five models all need the same normalization rule or surrogate-key logic, that logic belongs in one well-named macro rather than in five copied expressions.

{% macro cents_to_currency(column_name) %} {{ column_name }} / 100.0 {% endmacro %}

Packages extend the same idea at project scale. Teams often pull in packages for recurring utilities, audit helpers, or cross-warehouse patterns. The goal is not to build a clever abstraction maze. The goal is to keep the repeated parts consistent while leaving the compiled SQL readable enough that reviewers can still follow the actual warehouse logic.

A Worked Example: Raw Orders to a Revenue Mart

Use one simple path to make the mental model concrete. Imagine raw orders data already landed in a bronze schema called RAW. The dbt job is to turn that raw table into clean staging logic and then a business-facing revenue mart.

A raw row might look like this in the warehouse:

{ order_id: 8472, customer_id: 219, status: "PAID", total_cents: 129900, created_at: "2026-05-14 12:11:03-04" }

A staging model keeps one clear job: clean names, types, and light semantics without turning into a full mart.

select order_id, customer_id, lower(status) as order_status, total_cents / 100.0 as order_total, cast(created_at as timestamp) as created_at_ts from {{ source("raw", "orders") }}

An intermediate model can then add reusable business logic that several marts may need:

select o.order_id, o.customer_id, c.plan_id, o.order_status, o.order_total, o.created_at_ts from {{ ref("stg_orders") }} o left join {{ ref("stg_customers") }} c on o.customer_id = c.customer_id where o.order_status != 'cancelled'

A mart can then express the business question more clearly:

select date_trunc('day', created_at_ts) as order_date, sum(order_total) as paid_revenue from {{ ref("int_orders_enriched") }} where order_status = 'paid' group by 1

And the YAML alongside it can declare the contract that downstream users rely on:

`version: 2 models:

• name: stg_orders columns:
  • name: order_id tests:
    • not_null
    • unique
  • name: order_status tests:
    • accepted_values: values: [paid, refunded, cancelled, pending]`

A project like this often also adds one singular business test in tests/, for example asserting that no row with order_status = "paid" has a negative order_total. That is the point where dbt structure saves teams from drift: reusable transformations sit in one place, business logic is explicit, and validation lives close to the code rather than in somebody's BI workbook.

How to Structure Staging, Intermediate, and Mart Layers

A lot of maintainability comes from keeping layers disciplined. The most common healthy structure is staging for source cleanup, intermediate for reusable transformation steps, and marts for business-facing outputs.

• Staging models usually stay close to one source table: rename columns, cast types, standardize status values, preserve keys.
• Intermediate models hold reusable joins or domain logic you do not want copied into multiple marts.
• Marts expose business questions directly, such as daily revenue, active subscriptions, or customer cohorts.

The anti-pattern is letting staging models grow into giant semantic layers because it feels convenient in the moment. Once that happens, source cleanup, domain logic, and consumption logic blur together, and every downstream change becomes harder to reason about.

Incremental Models, Snapshots, and Correctness Over Time

The hardest dbt problems usually show up after the first successful run. Large fact tables need incremental logic. Late-arriving data appears. Historical corrections require rebuilds. Teams discover that "it worked once" is not the same as "it stays correct over time."

• Use incremental models deliberately: they are valuable for large facts, but they also create rules about what counts as new or changed data.
• Plan for late-arriving data: some models need a lookback window or reconciliation logic rather than a strict append-only assumption.
• Know when to full refresh: some semantic changes, key fixes, or history corrections justify a rebuild rather than trying to patch incrementally forever.
• Use historical tracking where it matters: if the business needs change history, snapshots or equivalent history-capture patterns must be designed explicitly.
• Protect reruns: retries should not duplicate facts or silently corrupt marts because the incremental boundary was naive. PatternBest fitWatch out forAppend-only incrementalEvent or log-style facts where old rows rarely changeLate-arriving events and accidental duplicate ingestionMerge or upsert incrementalFacts or dimensions where existing rows can changeUnique-key correctness, deletes, and stale updatesSnapshot historyChanging attributes where "what was true when" mattersUsing snapshots as a substitute for raw or CDC history when the underlying mutation story is more complex

A compact incremental model might look like this:

`{{ config(materialized="incremental", unique_key="order_id") }}

select order_id, customer_id, order_total, created_at_ts from {{ ref("stg_orders") }}

{% if is_incremental() %} where created_at_ts >= ( select dateadd(day, -2, max(created_at_ts)) from {{ this }} ) {% endif %}`

The exact date arithmetic varies by warehouse, but the operating idea stays the same: define a unique key, decide how much lookback to keep for late-arriving data, and know when a full refresh is safer than another incremental patch. dbt can help manage transformation-time incrementality, but it does not provide ingestion or CDC correctness by itself.

A concrete failure mode is easy to imagine: an order is corrected two days late, your incremental filter only looks at yesterday, and the mart quietly stays wrong. That is why incremental design is not just about speed. It is about what kinds of corrections your model promises to absorb.

A useful question for every important model is: what happens if yesterday's source correction arrives tomorrow? If the team cannot answer that cleanly, the model is not operationally ready yet.

Testing, Documentation, Environments, and CI

dbt becomes much more powerful when you stop treating tests and docs as nice extras. The same project that defines the SQL can also define whether the result is trusted enough for downstream users and whether the DAG is healthy enough to merge.

• Generic schema tests such as not_null, unique, relationships, and accepted_values catch common structural failures quickly.

• Custom SQL tests let the team assert business rules that generic tests cannot express cleanly, such as "paid revenue is never negative" or "every invoice maps to an account."

• Source freshness checks tell you whether the raw landing layer is stale before users blame the model layer for missing rows.

• Docs and descriptions make lineage easier to understand when the project grows beyond a handful of models.

• Environment strategy matters: dev, staging, and prod should map cleanly enough that analysts and engineers can validate changes before promotion.

• CI selection strategy matters as the DAG grows: smaller projects may build everything on each PR, while larger ones often validate changed models and their dependents in a non-production target first.

• State-aware builds matter at scale: manifests, deferral, and slim CI patterns can keep validation useful without rebuilding the universe on every pull request.

• Orchestration boundaries matter too: dbt defines transformation logic and execution order inside the DAG, while external schedulers usually own broader pipeline timing and cross-system dependencies.

• Open a PR with the model changes, YAML changes, and any new sources or macros in the same diff.

• Run dbt build or the team's targeted CI selection in a non-production target so broken SQL and failing tests are caught before merge.

• Treat test failures, source freshness problems on critical inputs, and dependency surprises as merge blockers rather than downstream cleanup work.

• After merge, let scheduled production jobs run the broader DAG and watch for cost, runtime, and data-quality drift over time.

That is how dbt turns analytics work into something closer to software engineering: not because SQL became magical, but because build behavior, validation, and ownership became explicit.

Published Interfaces, Contracts, and Ownership

The maturity jump in dbt comes when teams stop thinking only about "models that run" and start thinking about published interfaces. A mart that other teams, dashboards, or data products depend on is not just another SQL file. It is a contract.

• Published marts need ownership: somebody should be able to answer whether a column is stable, deprecated, or safe to use.
• Contracts need scope: not every staging model is a public interface, and treating all layers as equally public creates noise.
• Deprecation needs a path: renaming or removing a field should be handled deliberately rather than hidden in a casual refactor.
• Versioning is sometimes worth it: high-value marts with many consumers may need explicit version transitions rather than silent breakage.
• Descriptions and tests are part of the contract: they tell downstream users what a model promises, not just what it happened to contain yesterday.

A useful test is simple: if this mart changed tomorrow, who would notice and how would they find out? Teams that can answer that question usually have a healthier dbt operating model than teams that only know whether the SQL compiled.

Common dbt Failure Patterns

dbt projects fail in recognizable ways, and most of them come from skipping structure rather than from SQL itself being impossible.

• Monolithic models: one giant SQL file becomes the place where every business rule goes to hide.
• Logic duplicated across marts: the same calculation is copied into multiple places until definitions drift.
• Blind incrementalism: incremental models are used everywhere without a clear late-data or rebuild strategy.
• Hidden business logic in BI tools: the dbt repo looks clean, but the real definitions still live in dashboard SQL.
• Weak ownership: nobody knows who should fix a broken source definition, model, or test.
• Generated-but-unreviewed code: scaffolding helps, but unowned generated SQL becomes technical debt if the team never refines it.

The pattern underneath all of these is simple: dbt was adopted as a tool, but not as a modeling discipline.

How Skippr Fits a Practical dbt Workflow

Skippr fits best when the hard problem is not "write SQL from scratch forever," but "get from source metadata and raw landing to a solid first dbt project quickly without hiding the output or giving up control." The documented path is explicit: discover the source shape, sync raw data into bronze, model a dbt project, and validate the result against the destination.

• Generated project and validation path: the documented flow drafts a standard dbt project with files such as dbt_project.yml, profiles.yml, models/schema.yml, staging models, and packages.yml, then validates that project against the destination.
• Deterministic responsibilities: schema discovery and destination mapping, type reconciliation and evolution handling, incremental checkpoints and replay behavior, and CDC reconciliation logic stay outside model guesswork.
• AI-assisted responsibilities: dbt model and test scaffolding, naming and staging-model structure, and descriptive metadata and model documentation are accelerated where that helps.
• Data boundary: row-level data only ever exists on the machine running skippr and in the destination, metadata is the default model input, and the cloud path handles authentication and control-plane services rather than row-level source data.

That is useful for teams adopting dbt alongside a broader ELT or migration program, because the first project can exist as a standard starting point instead of a hand-built blank repo. What Skippr does not remove is the need to decide which marts matter, how business logic should be modeled, and which tests are required before consumers rely on the output.

For related reading, pair this guide with The Ultimate Guide to ELT, The Ultimate Guide to Data Migration, The Ultimate Guide to Data Modeling, What Is dbt?, and the Core Concepts docs.

Your Practical dbt Checklist

If you want one section to keep open while standardizing a dbt workflow, use this one.

• Decide what dbt owns in your stack and what still belongs to ingestion, orchestration, or BI tools.
• Keep raw landing separate from staging cleanup, intermediate logic, and marts.
• Structure models so one relation has one clear job wherever possible.
• Make tests and descriptions part of the model contract, not an afterthought.
• Use incremental models only when the volume and change pattern justify them.
• Have a real answer for late-arriving data, rebuilds, and historical corrections.
• Run dbt validation in CI and review SQL diffs like any other production code.
• Treat generated scaffolding as a starting point for ownership, not as finished modeling work.

That sequence reflects how strong dbt projects actually get built. First define boundaries. Then build one useful path. Then harden it with tests, ownership, and operational discipline before the project sprawls.