CI/CD for Data Pipelines: A Practical Guide for SQL, dbt and Power BI Teams

What CI/CD means for a data team

CI/CD came from software engineering, but the idea maps cleanly onto data work. Everything that defines your data platform is treated as code: table DDL, views, stored procedures, dbt models, pipeline definitions, data tests, access grants, and increasingly the Power BI semantic model and reports themselves. That code lives in a Git repository, and three rules apply:

• Nobody changes production by hand. Every change starts on a branch and arrives through a reviewed pull request (PR).
• Continuous Integration (CI): every PR automatically triggers a pipeline that proves the change works (it compiles, it passes data tests, it doesn't break anything downstream) before anyone can merge it.
• Continuous Delivery / Deployment (CD): once merged, the change is promoted through Dev, Test and Prod by the same automated steps every time. With continuous delivery a human approves the final production release; with continuous deployment it goes out automatically.

The payoff is not "DevOps for its own sake". It's fewer broken dashboards on Monday morning, a full audit trail of who changed what and why, and the confidence to ship small changes often instead of big risky ones rarely.

Why data CI/CD is harder than app CI/CD

An application deploy mostly replaces code: throw the old version away, start the new one. Data doesn't work like that.

The pipeline, stage by stage

1. Branch: a developer creates a feature branch (feature/add-discount-column) and edits SQL locally against a personal Dev schema.
2. Pull request: the change is pushed and a PR is opened. The description explains why, links the ticket, and notes any data impact.
3. CI pipeline: triggered automatically by the PR. Lint → build changed objects in a throwaway CI schema → run tests → post results back to the PR. Red means no merge.
4. Code review: a teammate reviews the SQL and the CI results. Branch protection rules enforce "at least one approval + green CI".
5. Merge & deploy to Test/UAT: merging to main triggers CD, which applies the change to the Test environment, where business users validate numbers.
6. Release to Prod: an approval gate (or a release tag) promotes the exact same artifact to Production, with the rollback plan ready.

What to run in CI

A good data CI pipeline answers four questions on every PR:

• Is the code clean? SQL linting and formatting (for example SQLFluff) catches style drift, ambiguous joins and risky patterns before review.
• Does it build? Compile and build the changed models plus everything downstream of them in an isolated schema such as CI_PR_142, so a renamed column that breaks a downstream view fails here, not in Prod.
• Is the data right? Run data tests. Every test is just a query that returns the failing rows, and zero rows means pass:

-- Each test returns the rows that VIOLATE a rule. CI fails if any test returns rows.

-- Uniqueness: one row per order
SELECT order_id, COUNT(*) AS dupes
FROM ci_pr_142.fct_orders
GROUP BY order_id HAVING COUNT(*) > 1;

-- Referential integrity: every order points at a real customer
SELECT o.order_id
FROM ci_pr_142.fct_orders o
LEFT JOIN ci_pr_142.dim_customer c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- Reconciliation: the new model must not change the revenue total by accident
SELECT 'revenue drift' AS issue
WHERE ABS((SELECT SUM(amount) FROM ci_pr_142.fct_orders)
        - (SELECT SUM(amount) FROM prod.fct_orders)) > 0.01;

• What changed in the data? The most valuable and most often skipped step: compare the CI build with production (row counts, totals, a column-level diff) and post a summary on the PR so the reviewer sees the data impact, not just the code diff. Our Dev vs Prod data comparison guide has ready-made queries for this.

Database changes: migrations vs state-based

There are two ways to deploy changes to stateful objects like tables, and you should know both.

1. Migration-based (versioned). You commit an ordered series of change scripts. The deploy tool keeps a history table in each database recording which scripts have already run, and applies only the new ones, in order. Flyway, Liquibase and schemachange (popular with Snowflake) all work this way.

-- V1__create_orders.sql
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, amount NUMBER(12,2), order_date DATE);

-- V2__add_discount_column.sql   (data-preserving: ADD, never DROP + CREATE)
ALTER TABLE orders ADD COLUMN discount NUMBER(12,2) DEFAULT 0;

-- V3__backfill_discount.sql
UPDATE orders SET discount = amount * 0.05 WHERE order_date >= '2026-09-01';

-- The tool's history table, per environment, decides what still needs to run:
-- Dev has V1-V3, Test has V1-V2, Prod has V1  =>  Prod deploy runs V2 then V3.

2. State-based (declarative). You commit the desired end state of each object (the full CREATE TABLE), and a tool compares it to the live database and generates the change script. Convenient, but review generated scripts carefully: a column rename can be "diffed" as drop-and-add, which silently deletes that column's data.

Environments and promotion

Most teams run three environments, each with its own database (or schema set), credentials and data:

Cloud warehouses make realistic CI environments cheap. In Snowflake, a zero-copy clone creates a full copy of a database in seconds without duplicating storage, so each PR can be tested against production-shaped data:

-- Start of CI run: a disposable, production-shaped database for PR #142
CREATE DATABASE analytics_ci_pr_142 CLONE analytics_prod;

-- ... apply pending migrations, build changed models, run tests ...

-- End of CI run
DROP DATABASE IF EXISTS analytics_ci_pr_142;

Two promotion principles matter more than any tool: build once, promote the same artifact (never re-edit SQL between Test and Prod), and keep environment differences in configuration (connection names, warehouse sizes) rather than in the SQL itself.

Worked example: GitHub Actions + dbt + Snowflake

Here is a realistic CI workflow that runs on every pull request. It lints the SQL, then uses dbt's state comparison to build and test only the models changed in this PR plus everything downstream, reading unchanged upstream models from production instead of rebuilding them (often called "slim CI"):

name: dbt-ci
on:
  pull_request:
    branches: [main]

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    env:
      SNOWFLAKE_ACCOUNT:  ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_USER:     ${{ secrets.SNOWFLAKE_CI_USER }}
      SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_CI_PASSWORD }}
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: pip install dbt-snowflake sqlfluff

      # 1. Lint: fail fast on style and risky SQL
      - run: sqlfluff lint models/ --dialect snowflake

      # 2. Fetch production's manifest.json (from your last prod run) into ./prod-state
      - run: ./scripts/download_prod_manifest.sh prod-state

      # 3. Build + test only modified models and their downstream dependents,
      #    in an isolated CI schema, deferring unchanged upstream refs to prod
      - run: dbt deps
      - run: dbt build --select state:modified+ --defer --state prod-state --target ci

The CD side is a second workflow triggered on merge to main: it runs migrations and dbt build against Test, waits for an approval (GitHub Environments with required reviewers, or an Azure DevOps approval gate), then runs the identical steps against Prod. Credentials live in the CI tool's secret store and belong to a dedicated service account, so humans don't need write access to Prod at all.

Where Power BI fits

The BI layer is part of the release too. Otherwise a perfect warehouse deploy still breaks a report that referenced a renamed column. The common building blocks:

• Version-controlled reports and models: saving Power BI work in the project format (PBIP, with the semantic model as readable text files) lets reports and DAX live in Git and go through pull requests like SQL.
• Deployment pipelines: Power BI / Fabric deployment pipelines promote content across Dev, Test and Prod workspaces, with deployment rules that swap the data source per stage (Dev report → Dev Snowflake, Prod report → Prod Snowflake).
• Automated checks: best-practice rules for the semantic model and a refresh-and-validate step after deployment catch broken measures and failed refreshes before users do.
• Order matters: deploy warehouse changes first, then the semantic model, then reports, so each layer is released on top of the layer it depends on.

Rollback strategies

Every release needs an answer to "what if this goes wrong at 9 AM?". The options, from simplest to most robust:

• Revert and redeploy: revert the merge commit in Git and let the pipeline redeploy the previous version. Ideal for views and models that hold no data of their own.
• Fix forward: for table changes, ship a new migration that corrects the problem rather than trying to "undo" a script that already ran.
• Blue/green with a view swap: build the new version beside the old one (sales_v2 next to sales_v1) and point a stable view at it. Going live and rolling back are each a single statement.
• Platform recovery: features like Snowflake Time Travel can restore a table to how it looked before the deploy, for example CREATE TABLE orders_restored CLONE orders AT(OFFSET => -3600);.

Common mistakes

• Letting people (and BI tools) keep write access to Prod, so changes keep bypassing the pipeline "just this once".
• Testing code but not data: a green build that never compared row counts or totals against production.
• Rebuilding the whole warehouse on every PR, making CI so slow and expensive that the team starts skipping it.
• Editing a migration script that already ran in Test or Prod, so environments silently drift apart.
• Hard-coding environment names (PROD_DB.SALES.ORDERS) inside SQL instead of resolving them from configuration.
• Forgetting the BI layer, deploying the warehouse change while the Power BI model still references the old column.