dbt Project Audit: What a Useful One Actually Covers

Your dbt repo grew fast, people are nervous about run times and warehouse cost, and nobody can say exactly what breaks if a model slips an hour. Here’s the rubric we use when a team asks for a dbt project audit. You’ll see exactly what to inspect, the queries and code to run, and the artifacts you should receive. If your team has the time, use this as a checklist and fix it yourselves. If not, this is the scope to hold us—or any partner—to. The deliverable is a severity-ranked plan with diffs and PRs, not a slide deck.

Map the DAG and the critical path

Start with lineage, but don’t stop at a pretty graph. You need timings, fan-in/out, and where parallelism is blocked. Export manifest.json and run a quick analysis to find the longest path and high-degree nodes that throttle throughput. Compare that against scheduler limits (dbt Cloud, Airflow) and warehouse concurrency.

Example: compute a crude critical path from manifest.json using Python. Feed it model timings from your last prod job (dbt artifacts + scheduler logs) to get realistic durations.

import json
from collections import defaultdict, deque

with open('target/manifest.json') as f:
    m = json.load(f)

# Build DAG
parents = defaultdict(set)
children = defaultdict(set)
for node_id, n in m['nodes'].items():
    if n['resource_type'] == 'model':
        for p in n.get('depends_on', {}).get('nodes', []):
            if p.startswith('model.'):  # narrow to model deps
                parents[node_id].add(p)
                children[p].add(node_id)

# Longest path length (topo DP)
indeg = {n: len(parents[n]) for n in parents}
queue = deque([n for n, d in indeg.items() if d == 0])
length = {n: 1 for n in indeg}
while queue:
    u = queue.popleft()
    for v in children[u]:
        length[v] = max(length.get(v, 1), length[u] + 1)
        indeg[v] -= 1
        if indeg[v] == 0:
            queue.append(v)

print(sorted(length.items(), key=lambda x: x[1], reverse=True)[:10])

Flag models that appear on most downstream paths and those with huge fan-in. That’s your first remediation queue: break up blocking transforms, add ephemeral layers to reduce materialization, and revisit scheduler parallelism caps. If this section resonates, keep this rundown of slow dbt runs handy.

Materializations and incremental strategy that won’t bite in week two

Materialization misfits cause most production pain: overusing views on heavy joins, incremental models that can’t backfill, or tables rebuilt daily without need. Catalog each model’s materialization and partitioning; check that it matches data volume and SLA.

Good defaults look like this:

-- models/fct_orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='sync_all_columns',
    incremental_strategy='merge',
    tags=['fact']
) }}

with src as (
    select * from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
    {% endif %}
)
select * from src

Then validate that full_refresh actually works on production volumes. Teams skip this and discover a 6-hour surprise when they need to fix a bad merge. Also standardize ephemeral models for small, reusable logic to cut I/O. For trade-offs between view, table, incremental, and ephemeral, see our reference on dbt materializations and the deeper strategies in incremental models that hold up in prod.

Finally, ensure warehouse-specific features (cluster keys in Snowflake, sort keys/partitioning elsewhere) are applied via configs, not ad-hoc SQL. Keep using dbt for DDL where possible so changes are tracked and repeatable.

Expensive models and warehouse cost: find, fix, and prevent

Cost control begins with identifying the models that drive the most compute. In Snowflake, tag dbt sessions so you can roll costs up by model. In your profiles.yml, set a query tag that includes the node name:

# profiles.yml (Snowflake)
query_tag: "dbt|{{ target.name }}|{{ node.name }}"

Now aggregate execution time and bytes scanned by model. This query surfaces your top offenders:

-- Top models by total elapsed time (Snowflake)
select
  split_part(query_tag, '|', 3) as model,
  count(*) as runs,
  sum(total_elapsed_time)/1000 as seconds
from snowflake.account_usage.query_history
where query_tag like 'dbt|%'
  and start_time > dateadd(day, -7, current_timestamp())
  and query_type in ('CREATE_TABLE_AS_SELECT','INSERT','MERGE')
group by 1
order by seconds desc
limit 20;

Then drill into why: poor join predicates, unnecessary selects, over-broad time windows. Use our SQL tuning guide for columnar warehouses alongside Snowflake cost optimization practices to fix the real causes. A quick comparison to keep teams honest:

Symptom	Likely cause	First fix
Hours-long fact rebuild	Table materialization on huge daily range	Switch to incremental with proper predicates
Warehouse auto-scales midday	Fan-in model throttles parallelism	Split model; use ephemeral sub-steps
Duplicate rows in aggregates	Missing unique_key in incremental merges	Set unique_key and dedupe upstream

Don’t guess dollar figures—measure on your own account. If you run on Snowflake Inc., warehouse credit usage and query history give you enough to prioritize.

Tests, data validation, and observability that catch real issues

Audit test coverage by counting models, columns, and actual test types in use. You want not-null and unique on keys, relationship tests between staging and facts, and value-range checks on critical column values. Add freshness to data sources where available.

version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: status
        tests:
          - accepted_values:
              values: ['pending','shipped','cancelled']

For row-count drift and late-arriving data, create an audit model that writes lightweight metrics to an audit table each run:

-- models/_audit/fct_orders_rowcount.sql
{{ config(materialized='incremental', unique_key='audit_ts') }}
select current_timestamp as audit_ts, count(*) as rowcount from {{ ref('fct_orders') }}

Insert an audit record per run and alert on unexpected deltas. If you’re using audit_helper in dbt (sometimes called the dbt audit helper or simply the audit helper package), scope it to critical models and ensure the output tables are small and partition-pruned. Keep custom tests maintainable; one-line tests beat a thousand-line macro that nobody can debug. This is data validation tied to run outcomes, not generic monitoring.

Ownership matters: who triages failing tests during business hours? Capture that in YAML and your on-call rota. That’s how data quality becomes a team sport, not a backlog.

Documentation, ownership, and approachable code (Jinja and macros)

Docs aren’t fluff. They’re how a new dbt developer decides whether to change a model at 5pm. Require descriptions, owners, and SLA hints in schema files:

version: 2
models:
  - name: dim_customer
    description: "One row per customer; joins usage metrics"
    meta:
      owner: analytics@company.com
      sla: "ready by 7am PT"
    columns:
      - name: customer_id
        description: "Surrogate key from stg_customers"

Format code for Jinja readability first, compiled SQL second. Long expressions belong in CTEs; repeat logic belongs in macros. Keep complex dbt macros small and composable:

{% macro safe_divide(numer, denom) -%}
    case when {{ denom }} = 0 then null else {{ numer }} / {{ denom }} end
{%- endmacro %}

Use package patterns that humans can trace. If a macro needs a README to understand inputs and outputs, write it. For patterns worth copying, see our notes on dbt macros and Jinja. Also, document governance touchpoints: mandatory PR reviews, data steward approvals for semantic changes, and who updates BI descriptions when logic moves.

CI/CD, environments, and run reliability (Cloud vs Core)

Every change should build affected models and run tests before merge. Whether you use dbt Cloud or dbt Core, the workflow is the same: on PR, build the subgraph changed by the diff. Enforce mandatory PR reviews.

# .github/workflows/dbt-ci.yml
on: [pull_request]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
      - run: pip install dbt-snowflake
      - name: Build changed graph
        run: |
          dbt deps
          dbt parse
          dbt build --select state:modified+ --defer --state .

If you’re on dbt Cloud, mirror this with a PR job and environment-specific credentials; its audit log is useful for tracking who changed what and when. If you’re on Core, store environment vars in your secret manager and wire jobs in your orchestrator. Either way, fail fast on schema changes, permissions, and broken references. For depth on catching issues pre-merge, we maintain a hands-on guide to dbt CI/CD that stops bad code before prod.

Small note: keep a weekly full_refresh job in non-prod, so you actually test disaster-recovery paths and avoid rot.

Unused, duplicate, and stale assets: cut noise, reclaim cost

Most repos accumulate dead models and orphaned tables. Identify:

• Models not referenced by exposures, downstream models, or BI.
• Tables never queried in 30+ days.
• Duplicate logic (same SQL across two models).

In Snowflake, join table usage against dbt-tagged relations to find tables with no reads:

-- Possible orphans (Snowflake)
with tables as (
  select table_schema, table_name
  from information_schema.tables
  where table_schema ilike 'TRANSFORM%'
), usage as (
  select distinct
         direct_object_name as table_name,
         direct_object_schema as table_schema
  from snowflake.account_usage.access_history
  where event_time > dateadd(day, -30, current_timestamp())
)
select t.*
from tables t
left join usage u using (table_schema, table_name)
where u.table_name is null
order by 1,2;

Before dropping, search your repo for references and check BI semantic layers. Deprecate in YAML first so changes show up in docs:

models:
  - name: dim_customer_legacy
    config: {enabled: false}
    description: "DEPRECATED: replaced by dim_customer"

Keep a running CHANGELOG. Your audit data should include a list of candidates with proposed actions and owners.

Deliverables and a severity framework that drives action

A useful engagement ends with code and a sequenced plan. You should expect:

• Annotated lineage and critical-path map with timings.
• Per-model sheet: materialization, size, duration, tests, owner, and findings.
• Cost and performance report: top N models by runtime and by bytes scanned.
• CI/CD review with concrete PRs or job/export configs.
• Refactor diffs for 3–5 high-impact models (not pseudocode).

We score findings on Impact, Effort, and Confidence to sequence work. Example slice:

Finding	Impact	Effort	Owner	Next step
Switch fct_orders to incremental	High	Medium	AE	PR #123: tested on staging
Split kpi_daily into two models	Medium	Low	AE	PR #124; add ephemeral CTE
Add relationship tests to dim_date joins	Low	Low	AE	PR #125; owner on-call rotates

We turn that into a 2–4 week plan with owners and review gates. If you want outside help, our dbt repo performance service runs this playbook end-to-end, including team pairing and handoff.

Short FAQ for leaders

What does “dbt project” mean?

The repo (models, seeds, snapshots, macros), plus profiles and job configs that define how transformations are built and run.

What is dbt vs Snowflake?

dbt is the transformation framework; Snowflake is the warehouse that executes the SQL. dbt compiles and orchestrates; Snowflake runs the queries.

Is dbt ETL or ELT?

ELT. Load first with an ingestion tool, then transform in-warehouse with dbt.

Is dbt the same as SQL?

No. dbt uses SQL plus Jinja to manage dependencies, environments, and deployment.

Already have dbt tests and packages?

Great. The audit checks depth and critical coverage, not just counts. We’ll trim noisy tests.

Are you optimizing your SQL?

Yes—per-model reviews and warehouse-aware patterns are core to the audit.

Comparing dbt Core and dbt Cloud?

Same fundamentals. Cloud adds UI, scheduler, and enterprise controls; Core fits when you own orchestration.

Data quality ownership extends beyond engineering?

Yes. Assign model owners and escalation paths; include business stewards on semantic changes.

Jinja readability vs compiled SQL?

Optimize for Jinja clarity. Small macros, named CTEs, and consistent style.

Approachable macros?

Keep them pure and documented; avoid hidden side effects.

Full refresh of production data?

Test it regularly in non-prod; schedule occasional prod full_refresh windows.

Mandatory PR reviews?

Yes. Block merges without at least one reviewer and a passing build.

---

If you need a partner to move fast, we’ve shipped platforms at scale. Start with the audit; you’ll leave with code and a plan. When you’re ready, see our notes on incremental durability and wire CI from our CI guide. Then decide if you want us alongside for the push.