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

Answer from memory before opening each one.

1. A PR adds a unique test on a column that has duplicates. Trace the chain from that failing test to a disabled Merge button. Which link in the chain is not provided by dbt-ci.yml, and what happens if it is missing?

   Show answer

   The test fails, so dbt build exits non-zero; the step fails, the job fails, and the PR's check turns red. The last link — red check means no merge — comes from a branch protection rule requiring status checks to pass, which is a repo setting, not part of the workflow. Without it CI still runs and still goes red, but anyone can merge the red PR anyway, and "main is always green" is only a hope. See Continuous Integration.

2. A PR renames a column in stg_rentals. A downstream model still selects the old name, and the author only ran the staging model locally. With the hands-on workflow in place, where and when does this surface — and where would it have surfaced without CI?

   Show answer

   On the pull request: dbt build runs every model and its tests in dependency order, so the downstream model errors on the runner and the check goes red before anyone merges. Without CI it is found tomorrow morning when the production run fails — or later, as a dashboard quietly showing wrong numbers. CI turns a production problem into a pull-request problem owned by the person who caused it. See CI for data.

3. A colleague suggests pointing the CI job at the real warehouse "so the tests see real data". What is the risk, and what three properties make a DuckDB file the better fit for this job?

   Show answer

   CI runs code that has not been reviewed or merged, so a buggy PR could rebuild or corrupt production tables — CI must run against a throwaway environment. DuckDB is just a file, needs no server, and needs no secrets; it is created fresh on the runner and destroyed with it, so you get a real engine to build against for the cost of a pip install. See CI for data.

4. Someone creates deploy.yml by copying dbt-ci.yml, changes the command to dbt build --target prod, and leaves on: pull_request in place. What does this workflow now do, and what should the trigger be?

   Show answer

   It builds against the real warehouse every time anyone opens or updates a PR — deploying unreviewed, unmerged code to production. CD fires after merge: on: push with branches: [ main ], because a merge is a push to main. CI answers "is this safe to merge?"; CD ships only what already passed. See Continuous Deployment.

5. The deploy job needs a Snowflake password and the repo is public. Describe the path the password takes from where it is stored to where dbt reads it, naming every file it does not appear in. A teammate once pasted it into the YAML and removed it in the next commit — is that the end of it?

   Show answer

   It is stored as an encrypted GitHub secret, injected into the runner as an environment variable via ${{ secrets.SNOWFLAKE_PASSWORD }} (and masked in logs), and read by profiles.yml through env_var('SNOWFLAKE_PASSWORD') — so the value is in neither the workflow nor the profile nor any other tracked file. The pasted password is not dealt with: it lives in git history and in every clone, so it must be rotated at the source. See Secrets in CI.

6. Your team's CD currently deploys to prod on every merge. Someone proposes adding a staging environment between CI and prod. What would staging catch that the CI sandbox cannot, what does it cost, and when is merge-straight-to-prod defensible?

   Show answer

   CI builds against a small throwaway DuckDB; staging is a prod-like copy, so it can show problems that only appear on a realistic warehouse and data before customers see them. The cost is another target and warehouse/schema to maintain and a slower path to prod — though the dbt code is identical, only --target changes. What decides it is who is hurt by a bad deploy: for a solo project like mini-griddp, CI then prod is reasonable; once customers read the output, a change should earn its way through staging. See environments.

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

Takeaway

• A test protects you only if it runs before a change merges, so CI runs your tests on every push and pull request.
• A branch protection rule that requires passing status checks turns the green check into a hard gate, which keeps main always working.
• For data, CI runs dbt build, and compile and lint steps, against a throwaway environment such as a DuckDB file, never the production warehouse.
• dbt compile catches a broken ref() or bad Jinja without touching the warehouse, and a missing column only shows up at dbt build.
• CD deploys after merge, and a change is promoted through environments with the same code and only the --target changing.
• Credentials live in GitHub secrets and reach profiles.yml through env_var(); a password pasted into any committed file is compromised and must be rotated.

Code is now tested and deployed automatically, but the infrastructure under it is still built by hand, so the next chapter makes infrastructure code too. → Infrastructure as Code

Further reading

• GitHub Actions: Understanding GitHub Actions — GitHub docs, accessed 2026. Workflows, jobs, steps and runners.
• GitHub Actions: Using secrets — GitHub docs, accessed 2026. How secrets are stored, masked and injected.
• GitHub: About protected branches — GitHub docs, accessed 2026. Required status checks as a merge gate.
• Continuous integration in dbt — dbt Labs docs, accessed 2026. Building and testing only what a pull request changed.
• dbt: env_var — dbt Labs docs, accessed 2026. Reading credentials from the environment in profiles.yml.