Skip to main content

CI/CD for dbt: Slim CI with GitHub Actions and Snowflake (Part 9)

A dbt project is code, and code changes should be tested before they reach production. The goal of this part: every pull request automatically builds and tests the models it changed, in its own temporary Snowflake schema, and the result shows up as a green tick or a red cross on the pull request. Only then is it merged and picked up by the Airflow DAG from part 8.

Part 9 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow. Previous: Part 8, Orchestrating dbt with Airflow. Next: Part 10, Running the pipeline in production.

Code for this part: .github/workflows/dbt_ci.yml, macros/drop_ci_schemas.sql, ci/profiles.yml. Full project: tpch_analytics on GitHub.

Pull requestopened or updatedCheck out main,dbt parse --target prod→ production manifestdbt build--select state:modified+--defer --state prod-statein schema CI_PR_42_*Drop CIschemasMergeUnchanged upstream modelsread from productionDeploy: Airflow picks up main,dbt deps + dbt parse

The idea: slim CI

Building the whole project on every pull request is slow and costs credits. Slim CI builds only what a change can affect, using two dbt features:

  • --select state:modified+ compares the pull request's project with production's and selects the models whose code changed, plus everything downstream of them (+).
  • --defer --state lets the selected models read unchanged upstream models from production, so CI does not have to rebuild them.

Both need production's manifest.json, dbt's description of the project. The simplest way to get it, with no artifact storage, is to check out the main branch and run dbt parse, which writes the manifest without querying Snowflake.

What state:modified+ actually selects

I tested the selection on the project. Adding a column to the monthly revenue mart selects only that model:

bashdbt ls --select state:modified+ --state prod-state --resource-type model
tpch_analytics.marts.finance.agg_monthly_revenue

Adding a column to stg_tpch__orders instead selects it and its five downstream models, 6 of the 13:

tpch_analytics.marts.finance.agg_monthly_revenue
tpch_analytics.marts.core.dim_customers
tpch_analytics.marts.core.fct_order_items
tpch_analytics.marts.core.fct_orders
tpch_analytics.intermediate.int_order_items__enriched
tpch_analytics.staging.tpch.stg_tpch__orders

Why --defer matters

When I first ran that second selection in a fresh CI schema without --defer, it failed:

Done. PASS=5 WARN=0 ERROR=2 SKIP=14 NO-OP=0 REUSED=0 TOTAL=21

The selected models referenced stg_tpch__customers and stg_tpch__line_items, which were not selected and so did not exist in the empty CI schema. With --defer --state prod-state, dbt points those references at the production tables instead:

Done. PASS=21 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=21

These outputs come from the local DuckDB version of the project used to test the series; on Snowflake the same commands select and build the same models.

Step 1: ci and prod targets

Add two targets to ci/profiles.yml next to dev from part 4. Both use the key pair; the CI schema name comes from an environment variable set to the pull request number, so parallel pull requests never collide.

ci/profiles.yml (continued)    ci:                       # GitHub Actions: one schema per pull request
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: AIRFLOW_SVC
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PASSPHRASE') }}"
      role: TRANSFORMER
      warehouse: TRANSFORM_WH
      database: ANALYTICS
      schema: "{{ env_var('DBT_CI_SCHEMA', 'CI') }}"
      threads: 8

    prod:                     # used by CI to read the production manifest
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: AIRFLOW_SVC
      private_key_path: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_PRIVATE_KEY_PASSPHRASE') }}"
      role: TRANSFORMER
      warehouse: TRANSFORM_WH
      database: ANALYTICS
      schema: PROD
      threads: 8

For simplicity this reuses the AIRFLOW_SVC user. In a larger team, create a separate CI service user with only the TRANSFORMER role, so a leaked CI secret cannot load data.

Step 2: clean up after every run

Each run creates schemas such as CI_PR_42_STAGING and CI_PR_42_MARTS. This macro drops exactly the schemas the project built for the current run. It refuses to run on any target except ci, and only touches schemas starting with the CI schema name:

macros/drop_ci_schemas.sql{# Drop every schema this project built for the current CI run
   (e.g. CI_PR_42_STAGING, CI_PR_42_MARTS, CI_PR_42_SNAPSHOTS). #}
{% macro drop_ci_schemas() %}
    {% if target.name != 'ci' %}
        {{ exceptions.raise_compiler_error("drop_ci_schemas only runs on the ci target") }}
    {% endif %}
    {% set schemas = [] %}
    {% for node in graph.nodes.values() %}
        {% if node.resource_type in ['model', 'snapshot', 'seed']
              and node.schema | upper is not in schemas
              and (node.schema | upper).startswith(target.schema | upper) %}
            {% do schemas.append(node.schema | upper) %}
        {% endif %}
    {% endfor %}
    {% for schema in schemas %}
        {% do adapter.drop_schema(api.Relation.create(database=target.database, schema=schema)) %}
        {{ log("Dropped schema " ~ schema, info=True) }}
    {% endfor %}
{% endmacro %}
Dropped schema CI_PR_7_INTERMEDIATE
Dropped schema CI_PR_7_MARTS
Dropped schema CI_PR_7_STAGING
Dropped schema CI_PR_7_SNAPSHOTS

The snapshot schema appears because part 7 configured the snapshot with schema='snapshots'. With the older target_schema setting, CI runs would have written test snapshots straight into the production snapshot table.

Step 3: the GitHub Actions workflow

In the companion repository the dbt project lives in a tpch_analytics/ subfolder, so its copy of this workflow sets defaults: run: working-directory: tpch_analytics and prefixes the paths with tpch_analytics/. The version below assumes the dbt project is at the root of its repository.

.github/workflows/dbt_ci.ymlname: dbt CI

on:
  pull_request:
    branches: [main]
    paths:
      - "models/**"
      - "macros/**"
      - "snapshots/**"
      - "tests/**"
      - "dbt_project.yml"
      - "packages.yml"

concurrency:
  group: dbt-ci-${{ github.event.pull_request.number }}
  cancel-in-progress: true

jobs:
  dbt-build-changed:
    runs-on: ubuntu-latest
    env:
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_PRIVATE_KEY_PASSPHRASE: ${{ secrets.SNOWFLAKE_PRIVATE_KEY_PASSPHRASE }}
      SNOWFLAKE_PRIVATE_KEY_PATH: ${{ github.workspace }}/rsa_key.p8
      DBT_CI_SCHEMA: CI_PR_${{ github.event.pull_request.number }}
      DBT_PROFILES_DIR: ${{ github.workspace }}/ci

    steps:
      - name: Check out the pull request
        uses: actions/checkout@v4

      - name: Check out main (the production version)
        uses: actions/checkout@v4
        with:
          ref: main
          path: main-branch

      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"

      - name: Install dbt
        run: pip install -r requirements.txt

      - name: Write the Snowflake private key
        run: echo "${{ secrets.SNOWFLAKE_PRIVATE_KEY }}" > rsa_key.p8

      - name: Build the production manifest from main
        working-directory: main-branch
        run: |
          dbt deps
          dbt parse --target prod
          cp -r target ../prod-state

      - name: Build and test only what changed
        run: |
          dbt deps
          dbt build --target ci --select state:modified+ --defer --state prod-state

      - name: Drop the CI schemas
        if: always()
        run: dbt run-operation drop_ci_schemas --target ci

Points worth noticing

  • paths: the job runs only when dbt files change, not for README edits.
  • concurrency: a new push to the same pull request cancels the previous run instead of queuing behind it.
  • working-directory: main-branch: dbt is run from the main checkout, so its manifest describes production, while DBT_PROFILES_DIR still points at the pull request's ci/ folder.
  • if: always(): the clean-up runs even when the build fails.

Step 4: secrets and branch protection

In the repository settings, add three secrets: SNOWFLAKE_ACCOUNT, SNOWFLAKE_PRIVATE_KEY (the full contents of rsa_key.p8) and SNOWFLAKE_PRIVATE_KEY_PASSPHRASE. Then add a branch protection rule on main requiring the dbt-build-changed check to pass before merging. Without that rule, CI is only advice.

Step 5: deploying to Airflow

After a merge, Airflow needs the new code and a fresh manifest (part 8 reads target/manifest.json). How you ship code depends on your Airflow setup (a git-sync sidecar, a new container image, or a file sync to a managed service), but the deployment step always ends with:

bashcd /opt/airflow/dbt/tpch_analytics
/opt/airflow/dbt_venv/bin/dbt deps
/opt/airflow/dbt_venv/bin/dbt parse --target prod

The next scheduled run then builds production with the merged changes. For changes to incremental models, remember part 7: if existing rows must be recalculated, trigger a one-off --full-refresh for that model rather than waiting for the daily run.

Next

Code is now tested before it ships and runs daily without anyone touching it. The final part covers living with it: what the pipeline costs in Snowflake, how to find slow models, how to spot failures from dbt's own run results, and a runbook for the mornings when something breaks.

The series

  1. The stack and what we will build
  2. Setting up Snowflake for dbt
  3. Loading raw data into Snowflake
  4. Your first dbt project on Snowflake
  5. Modeling with dbt: staging to marts
  6. Testing and documenting with dbt
  7. Incremental models and snapshots
  8. Orchestrating dbt with Airflow
  9. CI/CD for dbt with GitHub Actions (this post)
  10. Running the pipeline in production

All parts: Data Pipeline Series. Every file shown in this series is in the companion project tpch_analytics on GitHub; clone it to follow along, or run it locally on DuckDB without a Snowflake account.

Comments

Post a Comment