Production dbt — CI/CD, Environments, Scheduling

Three environments

Env	Purpose	Target
dev	Each dev's sandbox	Personal schema (dbt_anuj)
ci	PR validation	Ephemeral schema (dbt_pr_<id>)
prod	Source of truth	analytics.dbt

In profiles.yml:

my_profile:
  outputs:
    dev:
      schema: dbt_{{ env_var('USER') }}
    ci:
      schema: dbt_pr_{{ env_var('GITHUB_PR_NUMBER') }}
    prod:
      schema: dbt

CI on every PR

# .github/workflows/dbt_ci.yml
name: dbt CI
on:
  pull_request:
    branches: [main]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - run: pip install dbt-snowflake
      - run: dbt deps --target ci
      - run: dbt build --target ci --select state:modified+ --defer --state ./prod-manifest
        env:
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}

Key bits:

• --select state:modified+ — only changed models (and descendants).
• --defer --state ./prod-manifest — unchanged upstream resolves to prod tables.

Slim CI

state:modified+ + --defer = "slim CI". Most teams need it within their first year because full CI rebuilds are too expensive.

Scheduled production runs

dbt doesn't schedule itself. Options:

1. dbt Cloud — built-in scheduler.
2. GitHub Actions cron — simple, free.
3. Airflow / Dagster / Prefect — if you have other pipelines.
4. AWS Step Functions + EventBridge — cloud-native.

on:
  schedule:
    - cron: '0 */1 * * *'
jobs:
  run:
    steps:
      - uses: actions/checkout@v3
      - run: pip install dbt-snowflake
      - run: dbt build --target prod

Tag-based scheduling

{{ config(tags=['hourly']) }}

dbt build --select tag:hourly        # every hour
dbt build --select tag:daily         # every day

Most models can be daily. A few critical dashboards need hourly. Almost nothing needs every-15-min.

Alerts on failure

The minimum: failed dbt build posts to a #data-alerts Slack channel with the failed model and error. Without alerts, failures go unnoticed.

Options: Slack via GitHub Actions, PagerDuty for severe, email digest for warnings.

Observability

Three things to track:

1. dbt run results (target/run_results.json).
2. Test pass/fail rates over time.
3. Model timing (which models got slower?). Use dbt_artifacts package.

Common mistakes

1. No CI — bad PRs land on main.
2. No scheduling — depends on manual triggering.
3. No alerts — failures unnoticed.
4. No defer in CI — every PR rebuilds everything, warehouse-credit explosion.
5. Same target for everyone — devs trample each other.

dbt Cloud vs DIY

Cloud bundles IDE + scheduler + CI + alerts + docs at $50-200/dev-seat. Worth it for teams >5 engineers or strict governance. Not worth it for small teams comfortable with GitHub Actions.

Takeaway

Three environments. CI on every PR with --defer. Scheduled prod runs. Alerts on failure. Tag-based cadence. Slim CI. Get these right and dbt feels boring (highest compliment for prod infrastructure).

Model Contracts & Column Constraints: Enforcing Schemas Across Teams

In large analytics organizations and data mesh architectures, models built by one data team often serve as critical dependencies for downstream analytics, executive dashboards, or operational reverse-ETL syncs. Without explicit contracts, an upstream engineer renaming customer_id to user_id or changing a data type from INT to STRING silently breaks production consumers.

Introduced in dbt 1.5+ and refined in later versions, Model Contracts allow model authors to guarantee schema shape, column names, data types, and integrity constraints before building.

Declaring a Model Contract

To enforce a contract on a model, set contract: {enforced: true} in your YAML specification. When enforced, every column returned by the model must be explicitly defined with its exact data_type:

version: 2

models:
  - name: dim_customers
    config:
      contract:
        enforced: true
      materialized: table
    columns:
      - name: customer_id
        data_type: integer
        description: "Surrogate primary key"
        constraints:
          - type: not_null
          - type: primary_key
      - name: email
        data_type: varchar
        constraints:
          - type: not_null
      - name: lifetime_spend_usd
        data_type: numeric
      - name: created_at
        data_type: timestamp_ntz

How Contract Enforcement Works

1. Pre-build Schema Validation: During compilation and execution, dbt verifies that the model's SQL query outputs exactly the contracted columns and types. If an unlisted column is selected, an expected column is missing, or a data type mismatches, dbt build aborts immediately with a clear contract violation error before committing changes.
2. Database Constraints: On supported platforms (Snowflake, BigQuery, Postgres, Databricks), dbt can push constraints (like not_null and primary_key) down to the warehouse DDL, enabling warehouse-level enforcement and query optimizer hints.
3. Public vs Private Model Access: Contracts pair with model access levels (access: public, access: protected, access: private). Only contracted, public models can be referenced across separate projects in a dbt Mesh architecture.

When to Contract Models

• Marts and Public Dimensions: Any model consumed by BI tools, machine learning pipelines, or external teams.
• Reverse-ETL Source Tables: Models feeding Salesforce, HubSpot, or operational apps where unexpected column shifts cause downtime.
• SLA-Bound Enterprise Pipelines: Upstream changes must follow breaking-change deprecation cycles rather than silent drift.