dbt models for data analytics should make business transformations readable, testable and deployable while respecting warehouse behavior. A good project does more than turn select statements into tables: it establishes source boundaries, stable dependencies, model contracts, ownership and a release path. The result is an analytical graph that teams can change without guessing which dashboard will break.
Use this guide with Edilec's warehouse modeling guide, product guide to dbt models, data quality guide and data lineage guide. Begin from a defined analytical entity or decision, not from a folder convention.
Define what each model represents
The dbt model documentation describes a model as a SQL select statement in a project. Give each model one clear grain, purpose and owner. Document primary or business keys, time semantics, update behavior, sensitive columns and expected consumers. A model named orders should state whether it contains current orders, order versions, order lines or daily order snapshots.
Organize by business domain with recognizable layers. Sources describe loaded data; staging models rename, cast and lightly standardize one source; intermediate models implement reusable joins or logic; marts publish governed entities and metrics. Avoid a deep chain of single-use wrappers and a single enormous query. A reader should understand where source-specific cleanup ends and business meaning begins.
| Layer | Responsibility | Avoid |
|---|---|---|
| Source | Declare loaded relation and freshness | Business transformation |
| Staging | Rename, cast, basic cleanup | Cross-domain joins |
| Intermediate | Reusable business steps | Public dashboard contract |
| Mart | Stable entity, fact or aggregate | Raw source quirks |
| Exposure | Named downstream use and owner | Undocumented dependency |
Build dependencies with ref and source
Use the source feature for raw loaded tables so lineage, freshness and documentation begin at the boundary. Use the ref function for model dependencies. ref resolves relations and creates dependency edges, allowing dbt to order execution. Avoid hard-coded production database names and direct downstream references to raw tables.
Name models for meaning rather than implementation. Use consistent prefixes only when they help readers distinguish layers. Keep aliases deliberate because database relation names become interfaces. Version models or publish a new relation for breaking changes to columns, grain or semantics. Search downstream exposures and lineage before removal, and give consumers a migration window.
Choose materializations from behavior
The dbt materializations guide covers table, view, incremental, ephemeral and materialized-view patterns. Use views for light transformations where query cost and latency are acceptable; tables for reused or expensive results; ephemeral models for limited inlining; and warehouse-supported materialized views when their refresh semantics fit. Select per model from volume, update pattern, query demand, recovery and cost.
A materialization is an operating choice. Record build duration, storage, downstream query cost, freshness and failure behavior. A view can shift cost and instability to every consumer query. A table can become stale or expensive to rebuild. Prefer simple defaults and promote a model to a heavier strategy after measurement, not because its folder name sounds important.
Design incremental models for correction and replay
The dbt incremental-model documentation explains filtering new or updated rows and configuring how they are inserted or updated. Choose a reliable watermark and unique key. Include a lookback window for late updates when appropriate. Define behavior for deletes, corrected historical records, schema changes and full refresh. Incremental logic must produce the same trusted result as a rebuild for a controlled test range.
Do not use maximum event time blindly when events can arrive late or future-dated. Track ingestion or source sequence where available and reconcile business dates. Keep predicates early enough to reduce scans, while ensuring joins do not omit changed dimensions. Test an interrupted run and rerun. For large facts, build a backfill procedure that isolates dates or keys and validates totals before replacement.
| Change pattern | Strategy | Validation |
|---|---|---|
| Append-only events | Watermark with stable event ID | No duplicate keys and count reconciliation |
| Mutable records | Merge on unique key | Latest source version represented |
| Late facts | Lookback or partition rebuild | Late-arrival test |
| Hard deletes | Tombstone or scoped reconciliation | Deleted source absent as designed |
| Logic correction | Controlled full refresh or backfill | Old and new aggregate comparison |
Use tests as data-product controls
The dbt data-test documentation defines assertions that return failing rows. Apply not-null, unique, relationship and accepted-value tests where they express a real contract. Add singular or generic domain tests for balances, status transitions and temporal rules. A test should have an owner, severity, reason and response; hundreds of ignored warnings do not improve trust.
Place tests where failures become attributable. Test source freshness and basic shape near sources, key and type cleanup in staging, join and domain rules in intermediate models, and reconciliation in marts. Use representative fixtures in pull requests and production data tests after deployment. Protect sensitive values in test output and logs. Store failing rows only with approved access and retention.
Write transformations for review and change
Use explicit columns at published boundaries, clear common-table-expression names and comments for non-obvious business reasons. Centralize repeated definitions with tested macros only when abstraction improves consistency. Avoid macros that generate opaque SQL across many domains. Review compiled SQL and query plans for expensive models. Filter early, prevent accidental many-to-many joins and make time-zone conversion explicit.
Keep business rules close to domain ownership. A currency conversion model should identify rate source, rate date, fallback and precision; a customer-status model should expose mutually exclusive definitions and effective time. Store rule changes in version control and include issue context in review. Never hide manual spreadsheet adjustments outside the graph; ingest controlled adjustments with owner, reason and effective period.
Deploy and operate the dbt project
In continuous integration, parse and compile the project, run changed models and relevant parents or children in an isolated schema, execute tests and compare material outputs. Protect production credentials and use environment-specific targets without conditional business logic. Deploy code before scheduling it, record invocation and manifest artifacts, and use state-aware selection carefully so deferred references point to an approved environment.
Monitor run success, duration, queue time, model freshness, test failures, source freshness, warehouse consumption and downstream availability. Alert according to consumer deadlines. Runbooks should cover source delay, permissions, warehouse contention, schema changes, incremental repair and full refresh. Retain manifests and run results so operators can connect a failed relation to code and dependencies at that deployment.
Worked example: building an orders mart
A dbt project receives application orders, order items, payments and refunds. Source declarations identify loaded relations and freshness expectations. Staging models cast identifiers, normalize timestamps and retain source status without joining domains. An intermediate model builds payment allocation by order item. The published order-item fact states one row per item, current financial state, reporting currency, order date and last source update, with an analytics owner and two named dashboard exposures.
The fact is incremental on source update time with a three-day lookback and merge key of order item. Tests cover key uniqueness, order relationship, accepted state, nonnegative quantity and the domain rule that allocated payments minus refunds reconcile to the order balance within currency precision. A fixture includes a late refund, duplicate payment event, canceled item and currency conversion boundary. A weekly comparison rebuilds a recent partition from scratch and checks equality with incremental output.
In CI, changed models build in an isolated schema using production-shaped fixtures and selected upstream parents. The candidate fact is compared with the current model on row count, balance totals and changed keys. A semantic change from booked to fulfilled revenue is released as a new field with documentation and a consumer migration window, not by redefining the old column. Production artifacts preserve the manifest and run results; the on-call runbook can identify which source delay blocks the daily finance exposure.
Model readiness evidence
- Documented grain, business keys, time semantics, owner and downstream exposures.
- Source declarations and ref-based dependencies without hard-coded production relations.
- Materialization decision supported by measured build, storage and query behavior.
- Incremental tests for late updates, duplicates, deletes, interruption and full rebuild comparison.
- Key, relationship, accepted-value and domain reconciliation tests with response owners.
- Compiled SQL and query-plan review for expensive or high-impact models.
- Isolated CI build with representative fixtures and candidate-to-current output comparison.
- Manifest, run results, deployment version, freshness monitoring and recovery runbook retained.
Key takeaways
- Give every model a grain, purpose, owner and update contract.
- Use source and ref to create explicit, environment-safe lineage.
- Select materialization from observed cost, freshness and query behavior.
- Prove incremental models under late data, correction, rerun and backfill.
- Treat tests, manifests and run results as production control evidence.
Frequently asked questions
How much logic should one dbt model contain?
Enough to express one coherent transformation at a clear grain. Split when a step is reused, independently testable or conceptually distinct. Keep together when splitting only creates pass-through files and makes the graph harder to read.
Should large models always be incremental?
No. Incremental state adds correctness and recovery complexity. Use it when full rebuild time or cost exceeds the added operational burden, and only after defining unique keys, late data, deletes, schema changes and a tested rebuild path.
Should every column have a test?
Test properties that protect a meaningful contract or detect a credible failure. Critical keys and domain rules deserve strong coverage; decorative tests create noise. Document important columns even when a generic assertion adds little value.
Conclusion
Production dbt practice makes analytical meaning visible in code, dependencies, tests and run evidence. Clear layers and simple defaults help, but reliability comes from explicit grain, replayable incremental design, governed change and ownership. Build the project as a data product graph, not a collection of convenient SQL files.