From qa-db-migrations
Authors and runs SQLMesh - data-transformation framework with version control, virtual data environments, automatic breaking-vs-non-breaking change classification, and downstream impact analysis; supports `sqlmesh init` / `plan` / `apply` / `run` / `audit` / `test` lifecycle; covers DuckDB, Postgres, Snowflake, BigQuery, Redshift, Databricks. Use when the user works with SQL data pipelines (warehouse + dbt-adjacent ELT), needs safer model evolution than dbt's deploy-and-pray, or wants the strongest impact-analysis story in the OSS data tooling space.
How this skill is triggered — by the user, by Claude, or both
Slash command
/qa-db-migrations:sqlmesh-migrationsThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Per [sqlmesh.readthedocs.io/en/stable/quickstart/cli/][sm-cli]:
Per sqlmesh.readthedocs.io/en/stable/quickstart/cli/:
SQLMesh is a data transformation tool that enables version control and testing for SQL pipelines. Its distinguishing features vs schema-only tools (Flyway / Liquibase / Atlas):
dev env without
touching prod data.WHERE clause).
Non-breaking changes preserve validity of existing data (e.g.,
adding a new column)."Per sm-cli:
pip install sqlmesh
Optionally with extras for specific engines (e.g.,
pip install sqlmesh[bigquery]).
Per sm-cli:
sqlmesh init <dialect>
# example:
sqlmesh init duckdb
Generates project skeleton: config.yaml, models/, macros/,
tests/, audits/, seeds/.
A SQLMesh model is a SQL (or Python) file in models/ with a
MODEL directive header:
-- models/sales/orders_summary.sql
MODEL (
name sales.orders_summary,
kind FULL,
cron '@daily',
owner '[email protected]',
description 'Daily summary of orders by customer'
);
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM sales.orders
GROUP BY customer_id;
Model kinds (per SQLMesh docs): FULL (rebuild every run),
INCREMENTAL_BY_TIME_RANGE (process new time partitions),
INCREMENTAL_BY_UNIQUE_KEY (upsert by key), VIEW (no persisted
table), SEED (static data).
Per sm-cli:
sqlmesh plan dev
plan shows:
The user reviews the plan, then confirms - plan is integrated
with apply: confirming the plan applies it.
sqlmesh plan prod # promote dev to prod
The promotion is virtual until apply: prod continues serving
existing data until the new env is built.
sqlmesh run
Runs models per their cron schedule. Typically scheduled in CI/CD
(daily / hourly), sqlmesh run checks each model and executes if
its cron is due.
Audits are SQL-based data-quality checks attached to models:
-- audits/no_null_amounts.sql
AUDIT (
name no_null_amounts,
);
SELECT * FROM @this_model WHERE amount IS NULL;
sqlmesh audit
Returns failures if the audit query returns any rows.
Compare with great-expectations
and soda-checks:
SQLMesh audits are tightly coupled to SQLMesh models; GE/Soda are
standalone data-quality frameworks. Choose audits when you're
already in SQLMesh; GE/Soda for cross-framework data quality.
Unlike audits (which run on real data), tests run on synthetic input → synthetic output:
# tests/test_orders_summary.yaml
test_orders_summary:
model: sales.orders_summary
inputs:
sales.orders:
- { customer_id: 1, amount: 100.00 }
- { customer_id: 1, amount: 200.00 }
- { customer_id: 2, amount: 50.00 }
outputs:
query:
- { customer_id: 1, order_count: 2, total_amount: 300.00 }
- { customer_id: 2, order_count: 1, total_amount: 50.00 }
sqlmesh test
- run: pip install sqlmesh
- run: sqlmesh test # unit tests
- run: sqlmesh plan ci-${{ github.run_id }} --no-prompts # build a per-PR env
- run: sqlmesh audit # data-quality checks
# Promotion to prod is a separate workflow with manual approval gate
The per-PR env approach gives full data-pipeline isolation: each PR materializes its own copy of the models, audits run against real PR data, and merge-then-promote is the production path.
Pair with migration-blast-radius-reviewer
for adversarial review of breaking changes - sqlmesh plan already
classifies, but the reviewer adds estimation of downstream consumer
impact (BI dashboards, downstream services) that SQLMesh's model
graph alone doesn't capture.
For underlying schema migrations (DDL on the warehouse, separate
from SQLMesh model changes), use flyway-migrations
or atlas-migrations.
| Anti-pattern | Why it fails | Fix |
|---|---|---|
sqlmesh apply directly to prod without dev plan | No review of breaking-vs-non-breaking; broken pipelines | Always plan dev first (Step 4) |
Use kind FULL for everything | Full rebuilds expensive on large tables | Pick INCREMENTAL_BY_TIME_RANGE / BY_UNIQUE_KEY per model semantics |
| Skip audits on critical models | Data drift goes undetected | Audit every transformation (Step 6) |
| Write tests against production data instead of synthetic | Tests pass-by-accident; brittle | Use the inputs/outputs test format (Step 7) |
| Treat SQLMesh as schema-migration tool | SQLMesh manages data models, not raw DDL | Pair with Flyway/Atlas for raw schema (Step 9) |
flyway-migrations,
liquibase-migrations,
atlas-migrations - sister tools
(DDL-focused; SQLMesh complements them at the data-model layer)dbt-testing,
great-expectations,
soda-checks - sister data-quality frameworksmigration-blast-radius-reviewer - adversarial reviewerSearches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
Guides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Implements vector databases with Pinecone, Weaviate, Qdrant, Milvus, pgvector for semantic search, RAG, recommendations, and similarity systems. Optimizes embeddings, indexing, and hybrid search.
npx claudepluginhub testland/qa --plugin qa-db-migrations