CI/CD for Data Pipelines — Ship Data with Confidence

Most data teams deploy like it is 2010. Someone runs dbt run from their laptop. Someone else SSHs into a server to update an Airflow DAG. There is no code review for SQL changes. When something breaks, the rollback plan is “revert the commit and pray.”

This works until it does not. One bad model deploys on Friday evening, corrupts the revenue dashboard, and the on-call engineer spends the weekend manually reloading tables. CI/CD for data pipelines prevents this by automating validation, testing, and deployment — the same way software teams have done it for years.

Why CI/CD matters for data

Data pipeline changes are riskier than application changes in some ways. A broken API endpoint returns errors that users immediately notice. A broken data model returns wrong numbers that look correct. Nobody catches it until a business decision is made based on bad data.

CI/CD provides three guarantees:

1. Every change is validated before it reaches production. SQL is linted, models compile, tests pass, DAGs parse correctly.
2. Deployments are repeatable. The same process runs every time — no human error, no forgotten steps.
3. Rollbacks are possible. If a deployment breaks something, you can revert to the previous state quickly.

Git workflow for data teams

Feature branches (recommended for most teams)

main (production)
  ├── feature/add-customer-ltv-model
  ├── feature/fix-revenue-double-count
  └── feature/refactor-staging-layer

Each change gets its own branch. Pull requests require code review and passing CI checks before merging. This is the safest approach and works well for teams of any size.

# Developer workflow
git checkout -b feature/add-customer-ltv-model
# ... make changes to dbt models ...
git add models/marts/fct_customer_ltv.sql
git commit -m "Add customer LTV fact table"
git push -u origin feature/add-customer-ltv-model
# Open PR → CI runs → review → merge → CD deploys

Trunk-based development (for experienced teams)

Everyone commits directly to main with short-lived branches (less than a day). Requires strong CI and feature flags. Fast-moving teams with mature testing practices use this, but it demands discipline — a bad commit immediately affects production.

What to put in the repository

data-platform/
├── dbt/                     # dbt project
│   ├── models/
│   ├── tests/
│   ├── macros/
│   └── dbt_project.yml
├── airflow/                 # DAG definitions
│   └── dags/
├── infrastructure/          # Terraform/Pulumi
│   └── warehouse.tf
├── .github/
│   └── workflows/
│       ├── dbt-ci.yml       # CI for dbt changes
│       ├── dag-ci.yml       # CI for Airflow changes
│       └── deploy.yml       # CD pipeline
├── .sqlfluff               # SQL linting config
└── pyproject.toml          # Python tooling config

Everything in Git. Everything versioned. No exceptions.

The CI pipeline

CI runs on every pull request. It answers one question: “Is this change safe to deploy?”

Step 1: Lint SQL

Catch style issues and potential bugs before review:

# .sqlfluff
[sqlfluff]
dialect = snowflake
templater = dbt
max_line_length = 120

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = upper

sqlfluff lint models/ --dialect snowflake
sqlfluff fix models/ --dialect snowflake  # Auto-fix

SQLFluff catches inconsistent casing, missing aliases, ambiguous column references, and dozens of other SQL anti-patterns. Linting is cheap — run it first.

Step 2: Compile dbt models

dbt compile --target ci

Compilation catches Jinja syntax errors, missing ref() targets, and invalid configurations. It does not execute SQL — it just renders templates and validates the DAG.

Step 3: Validate Airflow DAGs

# scripts/validate_dags.py
import importlib
import sys
from pathlib import Path

def validate_dags(dag_folder):
    """Import every DAG file and check for parse errors."""
    errors = []
    dag_path = Path(dag_folder)

    for dag_file in dag_path.glob('*.py'):
        try:
            spec = importlib.util.spec_from_file_location(
                dag_file.stem, dag_file
            )
            module = importlib.util.module_from_spec(spec)
            spec.loader.exec_module(module)
            print(f"  OK: {dag_file.name}")
        except Exception as e:
            errors.append(f"  FAIL: {dag_file.name} — {e}")

    if errors:
        print("\nDAG validation failures:")
        for error in errors:
            print(error)
        sys.exit(1)

if __name__ == '__main__':
    validate_dags('airflow/dags/')

Step 4: Run tests on modified models

# Only test what changed — fast CI for large projects
dbt run --target ci --select state:modified+
dbt test --target ci --select state:modified+

The state:modified+ selector compares the current code against the production manifest. It identifies changed models and their downstream dependencies, running only what is necessary.

Complete CI workflow

# .github/workflows/dbt-ci.yml
name: Data Pipeline CI
on:
  pull_request:
    paths:
      - 'dbt/**'
      - 'airflow/**'

jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install sqlfluff dbt-snowflake
      - run: sqlfluff lint dbt/models/ --dialect snowflake

  compile-and-test:
    runs-on: ubuntu-latest
    needs: lint
    env:
      SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_CI_USER }}
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_CI_PASSWORD }}
    steps:
      - uses: actions/checkout@v4
      - run: pip install dbt-snowflake

      - name: Download production manifest
        run: aws s3 cp s3://dbt-artifacts/manifest.json dbt/target/manifest.json

      - name: dbt deps
        run: cd dbt && dbt deps

      - name: dbt compile
        run: cd dbt && dbt compile --target ci

      - name: Build modified models
        run: cd dbt && dbt run --target ci --select state:modified+ --defer --state target/

      - name: Test modified models
        run: cd dbt && dbt test --target ci --select state:modified+ --defer --state target/

  validate-dags:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install apache-airflow
      - run: python scripts/validate_dags.py

The --defer flag is important — it tells dbt to use production tables for any model not being rebuilt in CI. This means CI can test fct_orders without rebuilding all of its upstream dependencies.

The CD pipeline

CD runs after a merge to main. It deploys changes to production.

Deploying dbt models

# .github/workflows/deploy.yml
name: Deploy to Production
on:
  push:
    branches: [main]
    paths: ['dbt/**']

jobs:
  deploy:
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - run: pip install dbt-snowflake

      - name: dbt deps
        run: cd dbt && dbt deps

      - name: dbt run
        run: cd dbt && dbt run --target prod

      - name: dbt test
        run: cd dbt && dbt test --target prod

      - name: Upload manifest for CI comparison
        run: aws s3 cp dbt/target/manifest.json s3://dbt-artifacts/manifest.json

      - name: Notify on failure
        if: failure()
        run: |
          curl -X POST "$SLACK_WEBHOOK" \
            -H 'Content-Type: application/json' \
            -d '{"text": "dbt deployment FAILED. Check: ${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}"}'

Deploying Airflow DAGs

For Airflow on Astronomer or MWAA:

deploy-dags:
  runs-on: ubuntu-latest
  steps:
    - uses: actions/checkout@v4

    # Astronomer deployment
    - name: Deploy to Astronomer
      run: astro deploy --force

    # OR for MWAA (S3 sync)
    - name: Sync DAGs to S3
      run: aws s3 sync airflow/dags/ s3://mwaa-bucket/dags/ --delete

Environment management

Every data team needs at least three environments:

┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│   DEV        │     │   STAGING    │     │  PRODUCTION  │
│              │     │              │     │              │
│ dev schema   │────▶│ staging      │────▶│ production   │
│ subset data  │     │ schema       │     │ schema       │
│ personal     │     │ full data    │     │ full data    │
│ sandboxes    │     │ CI/CD target │     │ business     │
│              │     │              │     │ critical     │
└──────────────┘     └──────────────┘     └──────────────┘

In dbt, environments map to targets in profiles.yml:

# profiles.yml
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      schema: "dev_{{ env_var('USER') }}"  # Personal sandbox
      threads: 4
    ci:
      type: snowflake
      schema: "ci_pr_{{ env_var('GITHUB_PR_NUMBER', 'local') }}"
      threads: 4
    staging:
      type: snowflake
      schema: staging
      threads: 8
    prod:
      type: snowflake
      schema: analytics
      threads: 16

Each developer gets their own schema (dev_alice, dev_bob). CI creates ephemeral schemas per PR (ci_pr_142). Staging mirrors production structure. Production is the real thing.

CI schema cleanup

Ephemeral CI schemas accumulate. Clean them up:

-- Scheduled cleanup job: drop CI schemas older than 7 days
DECLARE schemas ARRAY;
SELECT ARRAY_AGG(schema_name) INTO schemas
FROM information_schema.schemata
WHERE schema_name LIKE 'ci_pr_%'
  AND created < DATEADD(day, -7, CURRENT_TIMESTAMP);

-- Drop each stale schema
-- (Implement as a stored procedure or Python script)

Blue-green deployments for data

Blue-green deployment is a technique borrowed from application deployment. Instead of modifying production tables in place, you build a complete copy and swap.

-- Blue-green for a critical fact table
-- Step 1: Build the "green" version
CREATE SCHEMA IF NOT EXISTS analytics_green;
CREATE TABLE analytics_green.fct_orders AS
SELECT * FROM analytics_staging.fct_orders;

-- Step 2: Validate the green version
-- (run quality checks, compare row counts, sample data)

-- Step 3: Swap
ALTER SCHEMA analytics RENAME TO analytics_blue;    -- Backup
ALTER SCHEMA analytics_green RENAME TO analytics;   -- Promote

-- Step 4: After validation period, drop backup
DROP SCHEMA analytics_blue CASCADE;

In Snowflake, you can use zero-copy clones to make this nearly instant:

-- Clone is instant regardless of data size
CREATE SCHEMA analytics_green CLONE analytics;
-- Run dbt against analytics_green
-- Swap when validated

Rollback strategies

When a deployment breaks data, you need to roll back fast.

Strategy 1: Git revert + redeploy

git revert HEAD          # Create revert commit
git push origin main     # Trigger CD pipeline
# CD pipeline rebuilds previous model versions

Simple and reliable. Works well when the broken change only affected dbt model logic.

Strategy 2: Snapshot restore

If your warehouse supports time travel:

-- Snowflake: restore table to state before deployment
CREATE OR REPLACE TABLE analytics.fct_orders
  CLONE analytics.fct_orders AT (TIMESTAMP => '2024-01-15 10:00:00');

Strategy 3: Blue-green swap back

If you used blue-green deployment, swap back to the blue schema:

ALTER SCHEMA analytics RENAME TO analytics_failed;
ALTER SCHEMA analytics_blue RENAME TO analytics;

Choosing a rollback strategy

Strategy	Speed	Data loss risk	Complexity
Git revert	Minutes	None (full rebuild)	Low
Time travel	Seconds	None	Low (warehouse support needed)
Blue-green	Seconds	None	Medium (requires setup)
Manual fix	Hours	High	High

Tools for data CI/CD

Tool	Best for	Key feature
GitHub Actions	General CI/CD	Broad ecosystem, easy setup
GitLab CI	Self-hosted teams	Built-in registry, tight integration
dbt Cloud CI	dbt-centric teams	Slim CI, automatic PR schemas
Astronomer CI	Airflow deployments	DAG validation, deployment previews
SQLFluff	SQL linting	Configurable rules, auto-fix

Next steps

• Pipeline Testing — the testing strategies your CI pipeline should automate.
• dbt Fundamentals — understand the tool at the heart of most data CI/CD.
• Data Observability — monitor what happens after deployment.
• What Is Apache Airflow? — orchestrate your deployed pipelines.