CI/CD for Data

Why CI/CD — the safety net for shipping

In Course 4, Chapter 11 you learned to write tests: not_null, unique, accepted-values, and bespoke assertions about your data. Those tests are only worth something if they run at the right moment — before a change reaches anyone else. A test you have to remember to run by hand is a test you'll skip the day you're in a hurry, which is exactly the day a bug slips through.

CI/CD automates that moment. It splits into two halves:

Continuous Integration — main is always green

Continuous Integration is a simple rule with big consequences: on every push and every pull request, automatically run your tests; if they fail, the change is blocked. Nothing broken gets into the shared main branch.

Here's the flow of a single change through CI/CD, from your laptop to production:

The payoff is a team norm called "main is always green." Because every change must pass tests before it can merge, anyone who pulls main at any moment gets working code. No "don't pull right now, it's broken" messages in chat. New work starts from a known-good base, every time.

GitHub Actions basics

GitHub Actions is GitHub's built-in CI/CD system. You describe what should happen in YAML files, and GitHub runs them on its servers whenever a trigger fires. The vocabulary is small:

A minimal workflow that just says hello on every pull request:

name: Hello CI

on:
  pull_request:          # trigger: run this on every PR

jobs:
  greet:                 # one job, named "greet"
    runs-on: ubuntu-latest   # the runner — a fresh Ubuntu VM
    steps:
      - name: Say hello
        run: echo "CI is running on commit $GITHUB_SHA"

Commit that file, push it, and open a pull request. GitHub reads the on: trigger, spins up an Ubuntu runner, and executes the one step. You'll see it appear under the Actions tab and as a check on the PR.

CI for data — the killer use case

For application code, CI runs unit tests. For a data project, CI runs your dbt build and dbt test — and this is genuinely the feature that changes how data teams work.

The problem it solves: someone opens a PR that changes a model. Maybe they renamed a column another model depends on, or wrote SQL that doesn't compile, or introduced a join that creates duplicate rows. Without CI, that breakage is discovered tomorrow morning when the production pipeline fails — or worse, when a dashboard quietly shows wrong numbers. With data CI, it's caught on the pull request, before anyone merges it.

Crucially, CI must run against a throwaway environment, not your real warehouse — a CI database or a small sample, so a buggy PR can never touch production data. For our lab the perfect fit is DuckDB: it's just a file, needs no server, and is created fresh on the runner and thrown away when the job ends. You get a real warehouse to build against for the cost of pip install.

Continuous Deployment — shipping after merge

CI proves a change is safe. Continuous Deployment is the second half: once a change merges to main, automatically deploy it — run the production dbt build, refresh the pipeline, update what users see. No human SSHing into a server and running commands from memory.

The trigger flips from "on pull request" to "on push to main" (which is what a merge is):

on:
  push:
    branches: [ main ]   # fire only when something lands on main

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: dbt build --target prod   # build against the REAL warehouse

Real teams rarely go straight to production. They promote a change through environments, each a separate target with its own warehouse/schema:

A change earns its way rightward: it builds in CI (dev), gets verified in staging, and only then deploys to prod. The same dbt code runs in each — only the --target (and thus the connection) changes. That's the discipline that lets you deploy confidently instead of crossing your fingers.

Hands-on: a data CI workflow for the mini-platform

Time to build the real thing. We'll write a workflow that, on every pull request, spins up a fresh Ubuntu runner, installs Python and dbt, and runs dbt build (which runs your models and their tests) against a throwaway DuckDB database. If anything fails, the PR is blocked.

Create the file in your repo at the exact path below — GitHub only looks in .github/workflows/:

name: dbt CI

on:
  pull_request:           # run on every PR targeting any branch
  push:
    branches: [ main ]    # and on merges, as a safety re-check

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    steps:
      # 1. Get the PR's code onto the runner
      - name: Check out the repo
        uses: actions/checkout@v4

      # 2. Install Python
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.11"

      # 3. Install dbt with the DuckDB adapter
      - name: Install dbt
        run: pip install dbt-duckdb

      # 4. Build models AND run their tests against a throwaway DuckDB file.
      #    The runner is destroyed after the job, so the database is disposable.
      - name: dbt build (models + tests)
        run: dbt build --target ci
        env:
          DBT_PROFILES_DIR: ./ci   # use a CI-only profiles.yml (below)

That --target ci needs a connection. Add a tiny CI-only profile that points dbt at a local DuckDB file — no server, no secrets, no real warehouse:

mini_griddp:            # must match the profile name in dbt_project.yml
  target: ci
  outputs:
    ci:
      type: duckdb
      path: ci.duckdb   # a throwaway file, created fresh on each run

Commit both files on a branch, push, and open a pull request:

git checkout -b add-ci
git add .github/workflows/dbt-ci.yml ci/profiles.yml
git commit -m "Add dbt CI on every pull request"
git push -u origin add-ci
gh pr create --fill          # open the PR from the terminal

Secrets in CI — never hard-code credentials

Our DuckDB CI needs no secrets — that's part of why it's a great sandbox. But the moment a workflow talks to a real warehouse (Snowflake, BigQuery, Postgres) it needs credentials, and those must never be written into the YAML. Workflow files are in git and often public; a password committed there is a password leaked forever.

Instead, store credentials as GitHub secrets (Settings → Secrets and variables → Actions, or per-environment secrets). GitHub encrypts them and injects them into the runner as environment variables at run time, masking them in logs. You reference them with the ${{ secrets.NAME }} syntax:

      - name: dbt build against prod
        run: dbt build --target prod
        env:
          # Pulled from encrypted GitHub secrets — NOT written in this file
          SNOWFLAKE_ACCOUNT:  ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_USER:     ${{ secrets.SNOWFLAKE_USER }}
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}

Your profiles.yml for prod then reads those env vars with dbt's env_var() function, e.g. password: "{{ env_var('SNOWFLAKE_PASSWORD') }}" — so no credential ever appears in any tracked file.

✓ Check yourself

• Can you explain, in one sentence each, what CI and CD do — and the question each answers?
• What does "main is always green" mean, and what enforces it beyond just running CI?
• Why run dbt CI against a throwaway DuckDB instead of your real warehouse?
• Where do credentials go, and why never in the workflow YAML?

models:
  - name: stg_meter_reads
    columns:
      - name: reading_id
        tests:
          - unique        # will FAIL if duplicates exist
          - not_null

git checkout -b break-it
git add models/schema.yml
git commit -m "Add a test that should fail in CI"
git push -u origin break-it
gh pr create --fill

Next

Your code is now tested and deployed automatically — but the runner, the warehouse, and the cloud resources underneath it are still set up by hand. Next we make the infrastructure itself code. → Infrastructure as Code