Brainforge Knowledge

dbt-dev-database-loop

3 min read

dbt Modeling: Dev Database Loop

Purpose: Use a dev database (and optionally dev schema) for all dbt runs while iterating on models. Run → test → show impact → change → repeat. Never rely on production for validating model changes.

Audience: dbt developers (and AI agents doing dbt work). Apply to any project that runs dbt against Snowflake (or similar).

Date: February 2026

Principle

  • Prod = production database/schema where scheduled jobs or releases write. Do not use it for ad‑hoc runs while developing.
  • Dev = a dedicated database (e.g. DEV_MARTS, DEV_STAGING) or schema where you run dbt run / dbt build during development. Same warehouse and sources as prod (or a copy); only the output location differs.

Leverage the dev database in a tight loop so you can test and show the impact of every change before opening a PR or merging.

The Loop

  1. Change — Edit one or more dbt models (.sql) or schema (.yml).
  2. Run in dev — From the dbt project directory, run against the dev target: 
    dbt run --select <model_or_selector>
    Example: dbt run --select fct_hedra__invoices_attributes+ to build a mart and its downstream.
  3. Validate in dev — Query the tables that were just built in the dev database (e.g. DEV_MARTS.REVENUE.<table>):
    • Row counts, key metrics, or a sample.
    • Use Snowflake CLI (snow sql -c <conn> -q "SELECT ... FROM DEV_MARTS.REVENUE....") or a BI tool (e.g. Hex) pointed at the dev connection.
  4. Assess impact — Compare to what you expect (prior run, prod snapshot, or business rule). If something is wrong, go back to step 1.
  5. Iterate — Repeat until the dev output is correct and you can articulate the impact of the change.
  6. PR — Open a PR; CI can run the same (or stricter) target. Optionally run a data diff after CI to compare prod vs staging.

Configuring Dev

  • In ~/.dbt/profiles.yml (or project profiles.yml), define a dev target that uses a dev database and schema, for example:
    • database: DEV_MARTS (or DEV_<project>)
    • schema: REVENUE / DEFAULT / or a personal schema
  • Use the same account, warehouse, and (if applicable) source data as prod so that model logic is comparable; only the output database/schema should differ.
  • See dbt Fusion + Hedra local dev for Hedra-specific profile and Fusion usage.

How to Validate in Dev

  • SQL — Run queries against the dev tables (e.g. SELECT COUNT(*), SUM(...) FROM DEV_MARTS.REVENUE.<model>). Use snow sql or a worksheet.
  • Samples / CSVs — Export a sample (e.g. snow sql ... --format csv > sample.csv) and spot-check or share for review.
  • BI / Hex — Point a BI connection at the dev database so stakeholders can see dev tables (e.g. using a dev schema such as DEV_MARTS.REVENUE).
  • Tests — Run dbt test --select <model>+ against the dev target so tests run on dev data.

Why This Matters

  • Safety — You never overwrite or pollute production while experimenting.
  • Speed — You get fast feedback (run → query dev) without waiting for prod jobs or risking prod state.
  • Clarity — You can show “before vs after” or “impact of this change” using dev outputs (counts, samples, or BI) in PRs or handoffs.
  • Consistency — Future dbt developers (and agents) can follow the same loop: change → run in dev → validate in dev → then PR.

Graph View