Data engineering / practical dbt guide
dbt Best Practices 2026: Complete SQL Transformation Guide
A maintainable dbt project makes three things easy to explain: where a number came from, which assumptions protect it, and how to rebuild it safely. Keep the staging/intermediate/marts pattern, but do not mistake a folder convention or an incremental watermark for a correctness guarantee.
1. Establish the engine, distribution and adapter first
The old description of dbt v2 as a June alpha is no longer current. The official version page lists v2.0 released September 14, 2026, while the Python-based v1 line remains separate. Run dbt --version and record the engine, adapter and package versions before upgrading a project.
Free to run is not the same as Apache licensed. dbt v2 is the recommended distribution and includes proprietary capabilities under the dbt Product Licensing Agreement; dbt-oss is the Apache 2.0 distribution using the shared Rust engine. dbt v1 remains Apache 2.0. Consult the licensing reference below instead of treating every v2 feature as an OSS feature.
Keep installation changes on a branch, read the upgrade guide, parse/compile, then build representative models in an isolated schema. Adapter support in a managed service is its own compatibility boundary: the native Fabric dbt guide is the relevant deployment path, not a reason to install the newest local engine into every Fabric job.
2. Keep layers and names useful, not ceremonial
Use staging for source-conformed cleanup, intermediate for reusable transformation steps, and marts for business-facing facts and dimensions. A practical starting point is models/staging/<source>/stg_*.sql, models/intermediate/int_*.sql, and models/marts/fct_*.sql or dim_*.sql.
State the grain before writing SQL: for example, one row per order, not merely orders table. Staging usually renames, casts and lightly cleans one source; joins and aggregations generally belong downstream. These are conventions, not parser-enforced laws.
Folder numbers do not determine execution order. Dependencies expressed through ref() and source() build the DAG. Use {{ ref('stg_orders') }} for a model and {{ source('raw', 'orders') }} for a declared source. A source declaration does not ingest the data. Use explicit output columns and keep business definitions stable when source schemas change.
3. Test the grain, the relationships and the business rule
Start with unique and not_null on a genuine primary key, relationships for foreign keys, and accepted_values for a controlled status. A relationships test does not replace a not-null test. The YAML key data_tests is current; tests remains an alias. Arguments and package syntax must match the installed version.
A singular data test is a SQL query returning failing rows; zero failing rows passes. For an order model whose contract forbids negative gross amounts, a small test is:
select order_id, amount_usd
from {{ ref('fct_orders') }}
where amount_usd < 0Do not apply this rule blindly to refunds or signed accounting entries. Test null amounts separately. Keep unit-test YAML under a model path, not the reserved SQL tests/ directory. Unit tests check transformation logic with fixtures; data tests check built relations. Document both expected outputs and the reason for each rule.
Small acceptance exercise: use three valid unique orders of 120, 80 and 45, then add a duplicate key and a prohibited negative amount. The raw fixture contains five rows; your documented rejection/deduplication logic should produce three valid unique orders totaling 245. The tests detect violations; they do not clean the data automatically.
4. Choose materialization from cost and freshness evidence
| Choice | Useful starting point | What to verify |
|---|---|---|
| View | Light source cleanup or inexpensive transformations | No separately stored result does not mean zero compute cost; freshness is limited by upstream data. |
| Table | Repeatedly queried joins or aggregations | Storage, rebuild cost and time since last successful run. |
| Incremental | Rebuilding the complete result is unnecessarily expensive | Stable key, change detection, adapter strategy, backfill and reconciliation. |
| Ephemeral | Small helpers best compiled as CTEs | Repeated references can enlarge downstream SQL and repeat work. |
Set sensible layer defaults in the project configuration and override deliberately. There is no universal 100-million-row or 30-minute threshold. Measure scanned data, runtime, concurrency and downstream latency. Physical optimization is adapter-specific: do not prescribe Spark OPTIMIZE/ZORDER commands for a Fabric Warehouse dbt model.
5. Make the incremental path correct before making it fast
An incremental model needs valid SQL both on its first/full build and on subsequent builds. is_incremental() is true only when the target exists as a table, the model is incremental and a full refresh was not requested. An empty target can make a max() watermark null; account for this explicitly.
Filter on a reliable change timestamp, not order creation time when older orders can change. Decide how late-arriving rows and corrections are reprocessed. A lookback window can catch bounded lateness, but cannot guarantee recovery of changes older than that window. Arrange periodic reconciliation or an explicit backfill.
unique_key tells supported strategies how to match rows; it does not assert uniqueness. Deduplicate the incoming batch and test the key. Choose an incremental strategy supported by your adapter; merge is not universal. Deletions require an explicit design rather than assuming an upsert removes absent rows.
- Full-build a small fixture and capture row count and totals.
- Rerun unchanged input: counts and totals must be identical.
- Add one order, update an old order and submit a late arrival. Check each outcome.
- Compare the incremental result with a clean full build of the same input.
- Test the backfill/recovery procedure before production scheduling.
6. Use snapshots for observed history, not time travel
Snapshots record changes observed when the snapshot runs. They cannot reconstruct states that changed between runs or history from before the first run. Snapshot the source as directly as practical so a later transformation change does not rewrite the meaning of recorded history.
Prefer the timestamp strategy when a reliable updated_at exists. The check strategy compares selected columns and does not require an updated_at field. Both need a stable unique key. Configure hard-delete handling deliberately; a missing source row is not automatically a closed historical record under every configuration/version.
Schedule snapshots with the required observation frequency, verify effective-date boundaries and test a changed row plus a deletion. Snapshot data needs its own retention and recovery plan; rebuilding a current-state model does not recreate lost snapshot history.
7. Keep documentation, macros and packages close to the model
Put descriptions, grain, ownership and important exclusions beside the model properties. Distinguish gross revenue from net revenue precisely. Generate documentation from supported commands for your installed engine; documentation is not proof that the data passed tests.
Extract a macro when it removes genuinely repeated, well-understood SQL. Do not wrap a package macro merely to hide its name. Prefer readable SQL to speculative abstractions. Review compiled SQL when a macro changes joins, null handling or adapter dispatch.
Choose packages for a specific need, verify the current maintainer and supported engine/adapter range, pin reviewed versions and commit the dependency lockfile. Run dbt deps in CI. A package list with old hardcoded versions is not a permanent recommendation.
Configure freshness against a meaningful load timestamp or supported metadata. Freshness checks measure recency, not row completeness. dbt v2 recommends dbt freshness; the legacy dbt source freshness checks sources only and remains supported. Follow the version-specific configuration reference.
8. Slim CI is a code-change check, not a production refresh schedule
Build PR changes into an isolated CI target, using an immutable manifest from a known successful production deployment. Keep that state directory separate from the current target output:
dbt deps
dbt build --select state:modified+ --defer --state ./prod-state --target ciThe trailing + selects downstream descendants. Defer can resolve unbuilt parents to the production relations described by the manifest. This means a relationship test can compare CI data with production data; assess whether sampled CI inputs make that test meaningful. Never give a PR job a production write target.
Do not point --state at the same directory as the current --target-path: parsing can overwrite the manifest you intended to compare against. State selection detects relevant code/configuration changes, not new source rows; do not use it as the sole selector for a scheduled production refresh.
On dbt v2, dbt lint is available in the recommended distribution; verify distribution availability. It is SQLFluff-compatible, not identical. Retain the appropriate external linter for unsupported environments and review rule differences before replacing a CI gate. Production jobs should combine freshness checks, builds/tests, alerts and retained artifacts; reserve full refreshes for a deliberate rebuild/backfill, not a blanket nightly default.
Common questions
Do numbered folders control dbt execution order?
No. dbt uses dependencies such as ref() to construct the DAG. Numbered folders are only a navigation convention.
Is every dbt v2 distribution Apache 2.0?
No. dbt v2 includes proprietary capabilities under the dbt Product Licensing Agreement. dbt OSS is the Apache 2.0 distribution; dbt v1 also remains Apache 2.0.
Does unique_key guarantee unique incremental rows?
No. It configures row matching for supported incremental strategies. Deduplicate incoming rows and test the key for uniqueness and nulls.
Do snapshots need an updated_at column?
The timestamp strategy needs a reliable timestamp. The check strategy compares configured columns and does not require updated_at. Snapshots only capture changes observed when they run.
Can slim CI replace scheduled production builds?
No. state:modified detects relevant code and configuration changes, not newly arriving source data. Use appropriate selectors for scheduled production refreshes.
Official references and review scope
Checked against the following official vendor documentation on 2026-10-30. This is an independent learning guide, not an official certification or a customer deployment report. These examples have not been executed in your tenant or warehouse. Expected values are teaching acceptance checks; verify current feature availability, licensing, schema, identity and recovery requirements before production use.
