SQLMesh — Model-Based Data Transformation with Audits, Environments, and CI/CD

Transformations That Know What Changed

Most SQL transformation tools treat your warehouse as a stateless target: you run a build, tables get rebuilt, and nobody quite knows what the change did until it lands. That uncertainty is where data incidents live — a tweaked join silently doubles a fact table, a new filter drops rows downstream, and the first person to notice is the analyst staring at a broken dashboard.

SQLMesh is an open-source data transformation framework that treats change as a first-class object. It parses your SQL, understands the column-level lineage between models, and tells you exactly which tables a change affects before anything touches production. Think of it as a compiler and a plan/apply workflow bolted onto your transformation layer.

If you already run dbt, the mental model is familiar — models, tests, incremental builds — but SQLMesh adds two things that fundamentally change the workflow: Virtual Data Environments and semantic change detection. This guide walks through both, plus model kinds, audits, and CI/CD.

A Project in Sixty Seconds

The fastest way to see the workflow is the built-in DuckDB scaffold. It creates a project directory with example models, seeds, and an audits folder, and it needs no external warehouse to run.

mkdir sqlmesh-example && cd sqlmesh-example
python -m venv .venv && source .venv/bin/activate
pip install sqlmesh

# Scaffold a project backed by a local DuckDB file
sqlmesh init duckdb

# Build everything into the prod environment for the first time
sqlmesh plan

That first sqlmesh plan is the whole philosophy in miniature. It diffs your local files against the target environment, shows you what it intends to create and backfill, and only executes after you confirm. Nothing is applied silently.

Models Are Metadata Plus a Query

A SQL model is a single file: a MODEL(...) DDL block that declares metadata, followed by exactly one SELECT. The metadata is where SQLMesh earns its keep — it declares the model's name, its build strategy (the kind), a cron cadence, a grain, and any audits.

MODEL (
  name sushi.stg_payments,
  cron '@daily',
  grain payment_id,
  audits (
    UNIQUE_VALUES(columns = (payment_id)),
    NOT_NULL(columns = (payment_id))
  )
);

SELECT
  id            AS payment_id,
  order_id,
  payment_method,
  amount / 100  AS amount
FROM sushi.seed_raw_payments

Because SQLMesh parses the SQL with SQLGlot, it understands the query semantically rather than treating it as an opaque string. That parsing powers column-level lineage, dialect transpilation across 10+ SQL dialects, and — crucially — the ability to tell a cosmetic edit from one that changes results.

Model Kinds: How a Table Gets Built

The kind tells SQLMesh how to materialize a model on each evaluation. Picking the right one is the difference between reprocessing a billion-row event table every night and touching only the day that changed.

• VIEW. The default. Creates or replaces a non-materialized view; no data is written. Good for lightweight logic that reads cheaply.
• FULL. Rewrites the entire dataset on every run. Simple and correct for small tables and aggregates with no temporal dimension.
• INCREMENTAL_BY_TIME_RANGE. Processes only missing time intervals, keyed on a time_column. Ideal for immutable facts — events, logs, transactions.
• INCREMENTAL_BY_UNIQUE_KEY. Merges on a unique_key, inserting new keys and updating existing ones. Handles mutable records and supports composite keys and when_matched.
• SCD_TYPE_2_BY_TIME / SCD_TYPE_2_BY_COLUMN. Tracks slowly changing dimensions with valid_from and valid_to columns, either from an updated-at timestamp or by watching specific columns.
• SEED, EMBEDDED, EXTERNAL, MANAGED. Static CSVs, subquery-injected shared logic, metadata for tables SQLMesh does not own, and engine-managed materializations respectively.

The incremental-by-time kind is the workhorse for large fact tables. You filter the input by the interval macros, and SQLMesh appends its own time-range filter on the output as a safety net against overwriting unrelated rows when late data arrives.

MODEL (
  name analytics.events,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column event_date
  ),
  start '2024-01-01',
  cron '@daily'
);

SELECT
  event_date::DATE AS event_date,
  user_id,
  event_type,
  payload
FROM raw.events
WHERE event_date BETWEEN @start_ds AND @end_ds

The @start_ds and @end_ds macros expand to the date bounds of the interval currently being processed. SQLMesh tracks which intervals are already materialized, so a backfill or a gap is filled without you hand-writing date logic — a recurring pain point covered in our guide to dbt incremental models in production.

Virtual Data Environments

This is the feature that changes how teams work. Every distinct version of a model — identified by a fingerprint of its SQL and configuration — gets its own physical table. An environment is just a named collection of references (views) pointing at those physical tables.

The consequence is that spinning up a dev environment costs almost nothing. If your dev branch shares 40 unchanged models with prod, SQLMesh reuses the exact same physical tables and only builds the handful you actually modified. No full clone of the warehouse, no duplicated compute.

# Build your changes into an isolated dev environment
sqlmesh plan dev

# Preview only a recent slice of data while iterating
sqlmesh plan dev --start '1 day ago' --min-intervals 1

# When dev looks right, promote to prod — often a pure reference swap
sqlmesh plan

Promotion to production is frequently a Virtual Update: because the dev environment already built and validated the new physical tables, going live is reduced to swapping which tables prod's views point at. When there are no data gaps to fill, that promotion has no runtime cost and is effectively instant.

Breaking vs Non-Breaking Changes

When you modify a model, SQLMesh asks you to categorize the change — but only for the models you directly edited. It infers the impact on everything downstream automatically from the lineage graph.

• Breaking. The change functionally alters downstream results — say, adding a WHERE that filters rows. SQLMesh backfills the model and all of its dependents. Safe, but more compute.
• Non-breaking. The change cannot affect existing downstream output — like adding a new column nothing consumes yet. Only the modified model is backfilled; dependents are left alone.

When several upstream dependencies disagree on a category, the most conservative one wins: breaking takes precedence. This categorization is what lets you make a large edit and pay only for the backfill the change genuinely requires, instead of rebuilding the world out of caution.

Forward-Only Plans for Expensive Tables

Some tables are simply too large to rebuild. For those, a forward-only plan changes the model logic going forward without backfilling history — it reuses the existing physical table rather than creating a new versioned one.

# Apply a change without rebuilding historical data
sqlmesh plan --forward-only

# Make the new logic effective from a specific date
sqlmesh plan --forward-only --effective-from 2024-01-01

# Explicitly allow a destructive schema change on matching models
sqlmesh plan --forward-only --allow-destructive-model "analytics.*"

The trade-off is that reverting is harder, since you did not preserve a fresh copy of the old version. In development, forward-only output is written to shallow clones (on BigQuery, Databricks, and Snowflake) or temp tables and is preview-only — it is not reused once the change deploys to prod.

Audits: Quality Gates on Every Run

An audit is a SQL query that looks for bad data — it should return zero rows, and any rows it returns mean something is wrong. Audits run automatically after a model evaluates, and SQLMesh ships a library of built-ins you attach directly in the MODEL block.

MODEL (
  name sushi.orders,
  audits (
    not_null(columns := (id, customer_id)),
    unique_values(columns := (id)),
    accepted_values(column := status, is_in := ('open', 'paid', 'void')),
    number_of_rows(threshold := 10),
    forall(criteria := (amount >= 0, LENGTH(status) > 0))
  )
);

Beyond these, built-ins include accepted_range, at_least_one, not_constant, valid_email, valid_uuid, mean_in_range, and z_score. When a built-in does not fit, you write a custom audit as a named query in the audits/ directory.

AUDIT (
  name assert_positive_order_amount,
  dialect duckdb
);

SELECT *
FROM @this_model
WHERE amount < 0;

The @this_model macro resolves to the audited model and respects the interval being processed, so on incremental-by-time models the audit only inspects the rows just built. Generic audits take parameters, so one does_not_exceed_threshold(column := price, threshold := 100) definition serves many models.

When SQL Runs Out: Python Models

Not everything expresses cleanly in SQL. For API enrichment, custom scoring, or anything that needs a real programming language, SQLMesh supports Python models: a decorated execute function that returns a DataFrame.

import typing as t
from datetime import datetime

import pandas as pd
from sqlmesh import ExecutionContext, model


@model(
    "analytics.enriched_users",
    kind="full",
    columns={
        "user_id": "int",
        "risk_score": "double",
    },
)
def execute(
    context: ExecutionContext,
    start: datetime,
    end: datetime,
    execution_time: datetime,
    **kwargs: t.Any,
) -> pd.DataFrame:
    df = context.fetchdf("SELECT user_id FROM analytics.users")
    df["risk_score"] = df["user_id"].apply(score_user)
    return df

Because SQLMesh creates tables before evaluating models, the columns argument declaring the output schema is required. The function receives the interval bounds (start, end, execution_time) and an ExecutionContext it can query through. Note that VIEW, SEED, MANAGED, and EMBEDDED kinds are not available for Python models, and a Python model may not return an empty DataFrame.

Shipping Changes: The GitHub CI/CD Bot

SQLMesh ships a GitHub Actions CI/CD bot that turns the plan/apply workflow into a pull-request review process. On each PR it runs unit tests, builds an isolated PR environment representing the code changes, categorizes and backfills the affected models, and — once approved — deploys to production and merges the PR.

name: SQLMesh Bot
on:
  pull_request:
    types: [synchronize, opened]
concurrency:
  group: ${{ github.workflow }}-${{ github.head_ref || github.ref_name }}
  cancel-in-progress: true
jobs:
  sqlmesh:
    runs-on: ubuntu-latest
    permissions:
      contents: write
      issues: write
      checks: write
      pull-requests: write
    steps:
      - uses: actions/setup-python@v4
      - uses: actions/checkout@v4
      - run: pip install "sqlmesh[github]"
      - name: Run CI/CD Bot
        run: |
          sqlmesh_cicd -p ${{ github.workspace }} \
            github --token ${{ secrets.GITHUB_TOKEN }} run-all

The run-all subcommand chains the individual steps — run-tests, update-pr-environment, check-required-approvers, and deploy-production — and posts a summary back to the PR. In your project config you control the merge_method (merge, squash, or rebase) and can set enable_deploy_command to let an approver trigger deploy with a /deploy comment.

Scheduling with run and Airflow

plan and run do different jobs. A plan synchronizes an environment with your code — applying new or changed models. A runapplies no code changes; it reprocesses intervals that have come due according to each model's cron, filling in the data that time has made available.

In production you schedule sqlmesh run on a cadence. The simplest option is SQLMesh's built-in scheduler on a cron trigger, but for teams already invested in a heavier orchestrator, SQLMesh integrates with Apache Airflow so runs participate in a broader DAG alongside ingestion and reverse-ETL.

# Reprocess any intervals now due in prod (no code changes)
sqlmesh run

# Run a specific environment (e.g. after a prod restatement)
sqlmesh run dev

# Reprocess a fixed window without editing model files
sqlmesh plan --restate-model "analytics.events" \
  --start "2024-01-01" --end "2024-01-10"

Restatement plans are the escape hatch for upstream data fixes: they ignore local file changes, reprocess a chosen window, and cascade the backfill downstream. A prod restatement also clears the affected intervals from other environments, so you re-run those to catch them up.

Coming From dbt

You do not have to rewrite everything to try SQLMesh. It can run an existing dbt project directly, reading your dbt_project.yml, models, and Jinja so you can evaluate the plan/apply workflow and Virtual Environments against real transformations before committing to a migration.

The conceptual mapping is close: dbt models become SQLMesh models, dbt tests map onto audits, and dbt's ref() lineage is what SQLMesh reads to compute breaking-change impact. What you gain is the semantic change detection and near-free environments; what you trade is a smaller ecosystem and a newer tool. Our dbt testing deep dive is a useful comparison point for how the two frameworks think about validation.

Production Checklist

• Pick kinds deliberately. Reserve FULL for small tables; use INCREMENTAL_BY_TIME_RANGE for immutable facts and INCREMENTAL_BY_UNIQUE_KEY for mutable records.
• Review every plan. Read the diff and the categorization before applying — that confirmation step is the point, not a formality.
• Develop in cheap environments. Iterate in plan dev with a narrow --start window, then promote via Virtual Update.
• Categorize honestly. Mark result-changing edits as breaking; a wrong non-breaking call leaves stale downstream data.
• Gate with audits. Attach not-null, uniqueness, and range audits to every model; keep them blocking unless you have a reason not to.
• Automate with the bot. Let the GitHub CI/CD bot build PR environments and deploy on approval, so production changes are always reviewed.
• Separate plan from run. Deploy code with plan; schedule data processing with run on cron or Airflow.
• Reach for forward-only carefully. Use it for tables too big to rebuild, and accept that reverting becomes harder.

SQLMesh's bet is that transformation should feel less like running scripts and more like deploying software: a reviewable plan, isolated environments, quality gates that block on failure, and a CI/CD bot that ties it together. If your team has been burned by a silent rebuild or a runaway backfill, the model-based, change-aware workflow is worth an afternoon on the DuckDB scaffold.