sqlmesh

<!-- claude-primary-sync:managed --> <!-- Generated from .claude/skills/sqlmesh/SKILL.md. Edit the Claude source instead. -->

SQLMesh

Open-source data-transformation framework. Apache 2.0 license; governed by the Linux Foundation since 2026-03-25 (see LF announcement). Active development continues. Verify the installed version with pip show sqlmesh and check GitHub releases for the changelog.

This skill is OSS-only. Anything labeled "Tobiko Cloud" is out of scope. Examples assume DuckDB as the warehouse engine; other engines work the same way at the model level — the engine integration index covers the small differences.

When to use this skill

Activate as soon as any of these come up:

• A MODEL (...) block, @model decorator, @macro, audit, or external_models.yaml is being read or edited.
• The user runs (or asks about) sqlmesh plan, apply, run, audit, render, evaluate, test, table_diff, lineage, or migrate.
• Anything about virtual environments, snapshots, fingerprints, state, or promoting dev → prod.
• Choosing or switching a model kind.
• Configuring config.py, gateways, before_all/after_all, vars, signals.
• Migrating a dbt project.
• Doubt about whether the project is still alive.

Always start here

SQLMesh ships fast. The model's training cutoff is usually behind a stable release or two, and behavior around plans, state, and incremental kinds has changed several times. Before answering anything more than trivia:

1. WebFetch the relevant page from https://sqlmesh.readthedocs.io/en/stable/. The full URL map is in references/links.md.
2. If the user reports an error, search GitHub issues before guessing.
3. Trust the live docs over your prior knowledge when they conflict.

Skip the fetch only for stable basics already covered in this file.

Mental model — this is not dbt

Five concepts carry most of SQLMesh's behavior. Internalize them before answering design questions.

Snapshots and fingerprints. Every model version is a snapshot identified by a fingerprint over its rendered SQL plus its kind and properties. Two models with the same fingerprint share the same physical table. Renaming a column, changing a WHERE clause, or editing a macro a model uses changes the fingerprint. See snapshots.

Virtual data environments. dev, prod, and any feature-branch environment are views over the snapshot store. Promoting dev to prod swaps view targets — it does not rebuild data. See environments.

Plans. A plan is the diff between code and what is already materialized, classified as breaking (downstream models must rebuild), non-breaking (only the changed model rebuilds), or forward-only (no backfill; new data only). The plan output lists each affected snapshot and asks for confirmation before mutating state. See plans.

State. SQLMesh stores snapshot and environment metadata in a separate state backend (default: a file alongside the warehouse, or a Postgres database you point it at). State is the source of truth — losing it loses all snapshot history. See state. For production with DuckDB warehouses, put state in Postgres rather than in the warehouse file (DuckDB is single-writer).

Macros run at parse time. Python @macro functions and built-ins like @EACH, @IF, @VAR rewrite the SQL AST before plan time. They have SQLGlot semantics — they manipulate columns and tables, not text. Jinja macros also exist but are pure string substitution and have several known footguns (see references/gotchas.md).

Canonical workflow

sqlmesh info                       # sanity-check config and connections
sqlmesh plan dev --auto-apply      # diff, build, materialize into dev env
sqlmesh audit dev                  # run audits against the dev env
sqlmesh test                       # run YAML unit tests
sqlmesh plan                       # promote dev → prod (no env arg = prod)

plan dev shows a categorized summary of changes (added / modified / removed; breaking / non-breaking / forward-only) and a backfill window. With --auto-apply the user accepts the plan inline; without it they review interactively.

plan (no environment) targets prod. By default it is virtual-only — it re-points prod views at snapshots already built in dev, so promotion is fast and reversible. New physical builds happen only if prod sees a snapshot it has never materialized.

To re-materialize data without a code change, use sqlmesh plan dev --restate-model <name>. See plans.

Model authoring

A model is a MODEL (...) block followed by exactly one query. Minimal DuckDB example:

MODEL (
  name analytics.daily_orders,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date
  ),
  cron '@daily',
  grain (order_date, order_id),
  audits (
    not_null(columns := (order_id, order_date)),
    unique_values(columns := (order_id))
  )
);

SELECT
  order_id,
  order_date,
  customer_id,
  amount
FROM raw.orders
WHERE order_date BETWEEN @start_date AND @end_date;

@start_date and @end_date are macro variables SQLMesh injects per interval for incremental kinds. For TIMESTAMP time columns, use the @start_ts / @end_ts pair instead. See macro variables.

Properties worth knowing: name, kind, cron, owner, grain, audits, columns, tags, depends_on, description. Full reference: model_configuration.

Picking a kind. Quick guide:

• FULL — small lookup tables; rebuilt every run.
• INCREMENTAL_BY_TIME_RANGE — append-mostly fact tables with a clear time column.
• INCREMENTAL_BY_UNIQUE_KEY — upserts keyed by a unique column (CDC-shaped).
• VIEW — pure SQL view, no materialization.
• SEED — static CSV under seeds/.
• SCD_TYPE_2 — slowly-changing dimensions with valid-from / valid-to.
• EXTERNAL — data SQLMesh reads but does not own (declared in external_models.yaml).
• EMBEDDED — inlined into downstream models, not materialized.
• MANAGED — engine-managed (e.g. dynamic tables).
• CUSTOM — user-defined materialization strategy.

Full matrix with required props and DuckDB examples: references/model-kinds.md. Canonical doc: model kinds.

Python models. Use the @model decorator and return a pandas, pyarrow, or ibis table from execute(context, ...). The ExecutionContext exposes fetchdf(), resolve_table(), var(), and engine_adapter. See python_models.

from sqlmesh import ExecutionContext, model
import pandas as pd

@model(
    "analytics.summary",
    columns={"day": "DATE", "n": "BIGINT"},
    kind="FULL",
)
def execute(context: ExecutionContext, **kwargs) -> pd.DataFrame:
    upstream = context.resolve_table("analytics.daily_orders")
    return context.fetchdf(
        f"SELECT order_date AS day, COUNT(*) AS n FROM {upstream} GROUP BY 1"
    )

Macros

Prefer Python @macro for anything beyond trivial substitution. They are parsed against SQLGlot, so they manipulate real SQL nodes and the plan can reason about lineage.

# macros/_my_macros.py
from sqlmesh import macro

@macro()
def add_audit_columns(evaluator, table_alias):
    return [f"{table_alias}.created_at", f"{table_alias}.updated_at"]

Used as @add_audit_columns('o') inside a model query.

Built-ins worth knowing:

• @EACH(items, x -> expr) — generates a list of expressions.
• @IF(cond, then, else) — conditional SQL.
• @VAR('name', default) — read a config var.
• @SQL(template) — paste raw SQL safely.
• Predicate macros (@AND, @OR) and blueprinting for templated model families.

Reference: SQLMesh macros.

Avoid jinja macros. They predate the Python macro system and exist mostly for dbt-import compatibility. They are pure string substitution: no SQL awareness, brittle whitespace, no lineage. The only good reason to write one is if you're inside a dbt-imported project and need to keep parity.

Audits and tests

Audits are queries that must return zero rows. Attach inline:

MODEL (
  name analytics.daily_orders,
  audits (
    not_null(columns := (order_id, order_date)),
    unique_values(columns := (order_id)),
    accepted_range(column := amount, min_v := 0)
  )
);

40+ built-ins ship with SQLMesh: not_null, unique_values, accepted_values, accepted_range, valid_email, valid_url, forall, mutually_exclusive_ranges, etc. Full list: audits.

Custom audits live in audits/<name>.sql:

AUDIT (
  name only_recent_orders,
  defaults (max_age_days = 30)
);
SELECT * FROM @this_model
WHERE order_date < CURRENT_DATE - INTERVAL @max_age_days DAY;

Audits are blocking by default — a failed audit aborts the run. Pass blocking := false to demote to a warning.

Unit tests live in tests/<model>.yaml. They run a model against fixed input rows and assert on output rows. Run with sqlmesh test. See [t

---

Content truncated.