dbt Models Tests Documentation and Semantic Layers

Published by Programming Line in Data Engineering in Depth. Last updated .

Purpose and outcome

dbt turns warehouse SQL into a governed transformation project. A model is a select statement that dbt materializes as a relation or view. Tests are assertions about model output. Documentation describes those models in the same project that builds them. The semantic layer adds a governed metric interface on top of modeled data so dashboards, notebooks, and applications ask the same question in the same way.

In a data engineering workflow, this matters because transformation code is often where business meaning becomes physical data. dbt does not move data from source systems; it assumes raw or staged data already exists in a warehouse. Its job is to compile project files, resolve dependencies, run SQL in the right order, check assumptions, and publish metadata that other people can trust.

How dbt works internally

A dbt project is a directed acyclic graph. Each node is usually a model, seed, snapshot, test, exposure, source, or semantic object. When dbt parses the project, it reads SQL files, YAML files, macros, and configuration, then builds a manifest describing every node and dependency. The important mechanism is the ref() function. A model that calls ref('stg_orders') does two things: it asks dbt for the fully qualified relation name of that model, and it creates an edge in the graph so dbt knows the upstream model must exist first.

Compilation happens before execution. Jinja expressions and macros are rendered into warehouse-specific SQL. Adapter plugins translate behavior for systems such as Snowflake, BigQuery, Postgres, Databricks, or Redshift. Materialization then decides what dbt asks the warehouse to create. A view materialization emits a create-or-replace view statement. A table materialization stores query output as a physical table. Incremental materialization runs a smaller query and merges or inserts changes according to project configuration.

Tests are graph nodes too. A generic test such as not_null or unique compiles into a SQL query that returns failing rows. The test passes when the query returns zero rows. A singular test is a hand-written SQL file using the same convention: return rows only when the assertion is violated. This failure-as-result-set design makes tests explainable because the diagnostic rows are usually the records that need attention.

Documentation is generated from the manifest, catalog metadata, descriptions, tests, column definitions, lineage, and optionally exposures. The generated site is not separate from the transformation code; it is a compiled view of the project. The semantic layer extends that metadata with entities, dimensions, measures, and metrics. It lets a consumer ask for a metric such as revenue by order month without rewriting joins and aggregation logic in each dashboard.

Project anatomy

The usual dbt layout separates source declarations, staging models, intermediate models, marts, tests, macros, and semantic definitions. The exact folders are conventions, but the dependency graph is determined by ref(), source(), and YAML metadata rather than by directory names.

name: retail_analytics
profile: retail_analytics
model-paths: ["models"]
test-paths: ["tests"]
models:
  retail_analytics:
    staging:
      +materialized: view
    marts:
      +materialized: table

This fragment says staging models should default to views and mart models should default to tables. That is a design choice: staging views keep raw normalization cheap and transparent, while mart tables make high-use analytical queries faster and more stable.

Example 1: a staging model

A staging model usually performs light cleanup: renaming columns, casting types, and standardizing source values. It should avoid heavy business rules so downstream models can reuse a clean but still recognizable representation of source data.

select
  cast(order_id as integer) as order_id,
  cast(customer_id as integer) as customer_id,
  cast(order_total as numeric) as order_total,
  cast(ordered_at as timestamp) as ordered_at,
  lower(status) as order_status
from raw.orders

If raw.orders contains ORDER_TOTAL as text and mixed-case status values, the expected model output has numeric totals and normalized statuses such as paid, refunded, and cancelled. The model is deterministic because it has no aggregation; one valid source row produces one staged row.

Example 2: tests and documentation

The companion YAML file documents the model and attaches tests. The model description explains the relation, column descriptions explain fields, and tests define executable assumptions.

version: 2
models:
  - name: stg_orders
    description: Cleaned order records from the raw order feed.
    columns:
      - name: order_id
        description: Stable identifier for one order.
        tests:
          - not_null
          - unique
      - name: customer_id
        description: Customer that placed the order.
        tests:
          - not_null
      - name: order_total
        description: Final order amount in the warehouse currency.
        tests:
          - not_null
      - name: order_status
        description: Lowercase lifecycle state for the order.

When dbt runs these tests, not_null on order_id compiles to a query that selects rows where order_id is null. unique groups by order_id and returns identifiers with a count greater than one. Expected passing behavior is an empty result set for every test. A duplicate order_id does not cause an ambiguous warning; it produces a failed test with the duplicated key visible in the test query result.

Example 3: a mart model

A mart model expresses business logic for a consumption pattern. The following model creates one row per customer with order counts, revenue, and the most recent order timestamp.

select
  customer_id,
  count(*) as order_count,
  sum(order_total) as lifetime_revenue,
  max(ordered_at) as last_ordered_at
from {{ ref('stg_orders') }}
where order_status = 'paid'
group by customer_id

The ref() call means dbt compiles the dependency to the warehouse relation name for stg_orders. If customer 42 has three paid orders for 10, 15, and 25, the expected row has order_count = 3 and lifetime_revenue = 50. Refunded and cancelled orders are excluded by the mart rule, not by staging cleanup.

Example 4: semantic layer metric

The semantic layer describes how metrics relate to modeled data. The following fragment defines a semantic model over an orders mart and a revenue metric that aggregates the order_total measure.

semantic_models:
  - name: orders
    model: ref('stg_orders')
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    dimensions:
      - name: ordered_at
        type: time
        type_params:
          time_granularity: day
      - name: order_status
        type: categorical
    measures:
      - name: paid_revenue
        agg: sum
        expr: order_total
        agg_time_dimension: ordered_at
        filter: order_status = 'paid'
metrics:
  - name: revenue
    type: simple
    type_params:
      measure: paid_revenue

A query for revenue by month can be generated from this definition rather than copied across every BI tool. The deterministic behavior is that the metric sums only paid orders and uses ordered_at as its time spine. If a dashboard and a notebook both request revenue, they share the same filter and aggregation semantics.

Design choices and trade-offs

Materialization is the first major trade-off. Views are cheap to build and always reflect upstream data, but repeated dashboard queries may become expensive. Tables cost storage and must be rebuilt, but they provide predictable read performance. Incremental models reduce build time for large facts, but they introduce edge cases around late-arriving records, changed business logic, and backfills.

Test placement is another choice. Staging tests should verify source-shaped assumptions such as primary keys, non-null foreign keys, accepted values, and freshness. Mart tests should verify business outputs such as one row per customer, non-negative revenue, and valid dimensional relationships. Too few tests allow silent drift. Too many low-value tests slow development and create alert fatigue.

Documentation works best when it is close to the model owner. Descriptions should explain business meaning, not restate the column name. A column named lifetime_revenue does not need a description saying it is lifetime revenue; it needs to say whether refunds, tax, discounts, and cancelled orders are included.

Failure modes and troubleshooting

Symptom: dbt says a model depends on a node that was not found. Cause: the ref() name does not match any model file or enabled model. Diagnostic steps: run a parse or list command, inspect model names, and check whether package or folder configuration disabled the target. Correction: fix the ref name, enable the upstream model, or qualify the package if two projects define similar names.

Symptom: a uniqueness test fails after a source load. Cause: duplicate business keys arrived, or staging picked the wrong natural key. Diagnostic steps: open the compiled test SQL, run it directly, and inspect counts by key and load timestamp. Correction: deduplicate in staging with a documented ordering rule, correct the upstream load, or change the test to the actual grain if the model was misunderstood.

Symptom: an incremental model misses corrected historical rows. Cause: the incremental filter only processes new timestamps and ignores updates to older records. Diagnostic steps: compare raw updated timestamps with the model’s filter condition and run a full-refresh in a development schema. Correction: use a reliable updated-at watermark, widen the lookback window, or schedule controlled full refreshes after logic changes.

Symptom: documentation is generated but columns appear blank. Cause: descriptions are missing from YAML, YAML indentation is wrong, or model names do not match. Diagnostic steps: parse the project, inspect the manifest, and verify the model’s YAML entry exactly matches the model node name. Correction: fix indentation and names, then regenerate docs.

Security, performance, and reliability

dbt executes with the permissions of its warehouse credentials, so production jobs should use roles scoped to the schemas they build and read. Development targets should write to separate schemas to avoid overwriting shared production relations. Secrets belong in profiles, environment variables, or platform secret stores, not in model SQL or project files.

Performance depends heavily on warehouse execution plans. dbt gives structure, but the warehouse still decides joins, scans, clustering, partition pruning, and result caching. Review compiled SQL for expensive cross joins, repeated transformations, and filters that prevent partition pruning. Reliability improves when the project separates source validation, deterministic transformation, and consumption marts because a failure can be located in one layer instead of in a monolithic SQL script.

Hands-on lab

Prerequisites: a dbt project connected to a development warehouse or local adapter, permission to create relations in a development schema, and a raw orders relation with order_id, customer_id, order_total, ordered_at, and status.

  1. Create models/staging/stg_orders.sql using the staging SQL from Example 1.
  2. Add models/staging/stg_orders.yml using the tests and descriptions from Example 2.
  3. Create models/marts/customer_revenue.sql using the mart SQL from Example 3.
  4. Add the semantic model fragment to an appropriate semantic YAML file if your dbt environment supports semantic definitions.
  5. Run dbt parse to validate project structure, then run dbt build --select stg_orders customer_revenue.
  6. Generate documentation with dbt docs generate and inspect the lineage between staging and mart models.
dbt parse
dbt build --select stg_orders customer_revenue
dbt docs generate

Verification: the build should create or replace the staging and mart relations, all generic tests should pass with zero failing rows, and the docs site should show descriptions and a lineage edge from stg_orders to customer_revenue. Query customer_revenue for a known customer and compare the count and sum against paid rows in raw.orders.

Cleanup or rollback: drop the development relations if they are temporary, or run the project in a disposable schema. If a test fails because of known dirty source data, keep the failing result as evidence and document whether the fix belongs upstream or in staging logic.

Assessment exercises

  1. A dashboard is slow because it reads a view that joins five large staging models. Which materialization would you try for the mart model, and what cost would that introduce?
  2. A unique test fails on order_id, but the business says split shipments create multiple rows per order. How should the model grain or tests change?
  3. Write a singular test that returns rows where paid revenue is negative. Explain why returning rows means failure.
  4. A metric named revenue excludes refunds in one dashboard but includes them in another. How would a semantic layer definition reduce that inconsistency?
  5. An incremental model uses created_at as its watermark and misses late corrections. What diagnostic query would you run, and what watermark would be safer?

Summary

dbt models compile SQL into an ordered transformation graph. Tests turn data assumptions into queries that fail by returning bad rows. Documentation publishes lineage, descriptions, and column meaning from the same project. The semantic layer raises the abstraction again by defining shared metrics over governed models. Used together, these pieces make transformation logic inspectable, testable, and reusable across the data engineering lifecycle.