SQLMesh: The Modern Data Transformation Framework That Made Me Reconsider dbt
Hey everyone! This time I want to introduce you to a tool that genuinely made me rethink how I manage my data warehouse. It is called SQLMesh. For those of you who have been around the data engineering world for a while, you are surely familiar with dbt (data build tool). SQLMesh arrives as a modern alternative that brings some fresh ideas that I find pretty revolutionary, especially around virtual data environments, column-level lineage, and incremental models that run automatically.
In this article I will explain everything from scratch: from installation, creating your first project, writing SQL and Python models, the various model kinds, the sqlmesh plan concept that is the heart of SQLMesh, audits and tests, all the way to an honest comparison between SQLMesh and dbt. I will give you plenty of code examples you can try right away. Let us get started!
Introduction
Before diving into the technical parts, I want to tell you why SQLMesh is interesting. If you have ever worked with dbt, you know how the flow goes. You write SQL models, then run dbt run, and dbt materializes those models into tables or views in your warehouse. Simple and powerful. But there are a few problems that often gave me a headache.
The first problem is about testing changes. In dbt, if I want to try a change to a model, I usually have to create a separate development schema, run all the models from scratch, and that takes time plus warehouse compute cost that is not trivial. If your data is large, this can become a nightmare. Every time I want to test one small change, I have to rebuild a bunch of tables.
The second problem is about incremental models. In dbt, incremental models exist but you have to write the isincremental() logic yourself, manage date filters manually, and it is very easy to get wrong in ways that cause duplicate or missing data. I personally got hit by an incremental bug once that lost a week of data because the date filter was wrong.
Now, SQLMesh comes with solutions to both of these problems plus many more. The core idea is that SQLMesh is "column-aware" and "state-aware". That means SQLMesh understands the structure of your SQL down to the column level (using a library called SQLGlot for parsing SQL), and it stores state about what has already been run. With this understanding, SQLMesh can automatically determine which models need to be rebuilt when you change your code, and which models can be reused without extra computation.
The feature I love the most is virtual data environments. The concept is like this: SQLMesh separates "physical data" (the actual tables in your warehouse) from "virtual views" (which serve as the query interface). When you create a new environment for development, SQLMesh does not always rebuild all the tables from scratch. If your model code is identical to what is in production, SQLMesh just creates views that point to the existing physical tables. So creating a dev environment is cheap and fast, sometimes only taking a few seconds.
Some of the SQLMesh advantages we will cover:
- Virtual data environments that make development cheap and safe
- Automatic column-level lineage, so you know exactly which column affects which
- Incremental models that are truly automatic without writing manual date filter logic
- Built-in audits and unit tests to protect data quality
- Compatibility with existing dbt projects (you can migrate gradually)
- Preview data before applying, so you know the impact of changes before they happen
For those of you who feel dbt is good enough, that is fair and not wrong. But if you often run into problems with expensive rebuilds, complicated incrementals, or you want to know the impact of changes before applying, SQLMesh is well worth a try. Alright, let us install it.
Installation
SQLMesh is a Python-based tool, so installing it is very easy with pip. I always recommend creating a virtual environment first so the dependencies do not clash with other projects. This is important especially if you have many Python projects.
First, we create the project folder and a virtual environment:
mkdir sqlmesh-demo
cd sqlmesh-demo
python3 -m venv .venv
source .venv/bin/activate
For those of you on Windows, activating the virtual environment is slightly different:
python -m venv .venv
.venv\Scripts\activate
After the virtual environment is active, we install SQLMesh:
pip install sqlmesh
If you want to use a specific warehouse, SQLMesh provides extras. For example for DuckDB (which is nice for learning because it runs locally without complicated setup), Postgres, Snowflake, or BigQuery:
pip install "sqlmesh[duckdb]"
pip install "sqlmesh[postgres]"
pip install "sqlmesh[snowflake]"
pip install "sqlmesh[bigquery]"
For this tutorial I use DuckDB because it is the easiest for experimenting. DuckDB is an analytical database that runs in-process, so there is no need to install a separate server. It is perfect for learning and prototyping.
After it is done, we check the version to confirm the installation succeeded:
sqlmesh --version
If a version number shows up, then you are good. Now we can start creating a project.
Oh right, if you want to use the UI (SQLMesh has a web UI that is pretty nice for viewing lineage), you can install the web extra:
pip install "sqlmesh[web]"
Then run it with sqlmesh ui later after the project is set up. But we will cover that later, for now let us focus on the CLI first.
Basic Usage
Initializing a Project
The fastest way to start is using the sqlmesh init command. This command generates a standard folder structure plus a few example models so you have a picture to work with. We init with the DuckDB engine:
sqlmesh init duckdb
After running it, you will see a folder structure like this:
.
├── config.yaml # SQLMesh main configuration
├── models/ # where all SQL and Python models live
│ ├── fullmodel.sql
│ ├── incrementalmodel.sql
│ └── seedmodel.sql
├── seeds/ # static CSV data
│ └── seeddata.csv
├── audits/ # custom audit definitions
├── tests/ # unit tests
└── macros/ # custom Python macros
This structure is very similar to dbt, so if you are already used to dbt you will pick it up quickly. The config.yaml file is the heart of your configuration. Its contents look roughly like this:
gateways:
duckdb:
connection:
type: duckdb
database: db.db
defaultgateway: duckdb
modeldefaults:
dialect: duckdb
start: 2024-01-01
Here gateways is the connection definition to your warehouse. modeldefaults is the default settings for all models, including dialect (the SQL dialect used) and start (the start date for incremental models). You can have many gateways, for example one for dev using DuckDB and one for prod using Snowflake.
Writing Your First SQL Model
Now let us look at a SQL model. What makes SQLMesh different from dbt is how you declare model metadata. In SQLMesh, you use a MODEL(...) block above your SQL query, not a separate YAML file or a Jinja config block. Here is an example full model:
MODEL (
name demo.fullmodel,
kind FULL,
cron '@daily',
grain itemid,
audits (assertpositiveorderids)
);
SELECT
itemid,
COUNT(DISTINCT id) AS numorders
FROM
demo.incrementalmodel
GROUP BY
itemid;
Notice the MODEL(...) block at the top. There we declare:
name: the model name that becomes the table/view name in the warehousekind: the materialization type of the model (here FULL, meaning fully rebuilt each run)cron: the schedule for how often this model is refreshedgrain: the column that serves as the logical unique key of this modelaudits: the list of audits that run to validate the data
The cool thing is that SQLMesh reads your SQL query and understands the column structure automatically. So you do not need to redeclare the columns in another file like in dbt. This is what I mean by SQLMesh being column-aware.
Incremental Model
Now this is the part I find the most powerful. Incremental models in SQLMesh are much simpler than in dbt. Here is an example:
MODEL (
name demo.incrementalmodel,
kind INCREMENTALBYTIMERANGE (
timecolumn eventdate
),
start '2024-01-01',
cron '@daily',
grain (id, eventdate)
);
SELECT
id,
itemid,
eventdate
FROM
demo.seedmodel
WHERE
eventdate BETWEEN @startdate AND @enddate;
Look at WHERE eventdate BETWEEN @startdate AND @enddate. Now @startdate and @enddate are built-in SQLMesh macros. You do not need to write isincremental() logic like in dbt. SQLMesh automatically fills in the correct date range based on the data that has not been processed yet. So if there are 3 days of new data, SQLMesh runs this query 3 times (or according to batching) with the correct date ranges.
This is what I mean by truly automatic incremental. I no longer need to think about "if incremental run then what is the date filter, if full refresh then what". SQLMesh handles it all. And more importantly, SQLMesh stores state about which intervals have been processed, so there is never any doubled or missing data.
Running sqlmesh plan
This is the most important command in SQLMesh, sqlmesh plan. Whereas in dbt you go straight to dbt run, in SQLMesh the flow is different. You create a "plan" first. A plan is like a preview of what will change before it is actually executed. Similar to the terraform plan concept for those of you used to infrastructure as code.
sqlmesh plan
When you first run this in the prod environment (the default), SQLMesh detects that all models are new and need to be created. It shows a summary of which models will be built, then asks for confirmation. If you agree, type y and SQLMesh executes.
The output looks roughly like this:
prod environment will be initialized
Models:
└── Added:
├── demo.fullmodel
├── demo.incrementalmodel
└── demo.seedmodel
Apply - Backfill Tables [y/n]: y
After applying, all your models are now tables/views in the warehouse and ready to query. Congrats, you just ran your first pipeline with SQLMesh!
Advanced Usage
Now we move into the deeper parts. Here I will cover Python models, the various model kinds, virtual environments which are the killer feature of SQLMesh, plus audits and tests.
Python Model
Not all transformations are pleasant to write in SQL. Sometimes you need to call an API, use a machine learning library, or do complicated logic. For cases like this, SQLMesh provides Python models. You create a .py file in the models/ folder and use the @model decorator:
from datetime import datetime
import typing as t
import pandas as pd
from sqlmesh import ExecutionContext, model
@model(
"demo.pythonmodel",
columns={
"id": "int",
"name": "text",
"createdat": "timestamp",
},
kind="FULL",
cron="@daily",
)
def execute(
context: ExecutionContext,
start: datetime,
end: datetime,
executiontime: datetime,
*kwargs: t.Any,
) -> pd.DataFrame:
# fetch data from another model via context
table = context.resolvetable("demo.incrementalmodel")
df = context.fetchdf(f"SELECT id, itemid FROM {table}")
# transform using pandas
df["name"] = "item-" + df["itemid"].astype(str)
df["createdat"] = pd.Timestamp.now()
return df[["id", "name", "createdat"]]
The important things to note in a Python model:
- The
@modeldecorator holds the same metadata as theMODEL(...)block in a SQL model columnsmust be declared because SQLMesh cannot infer the schema from Python code- The
executefunction must return a DataFrame (pandas) or a batch generator context.resolvetable()is used to resolve a model name to the correct physical name for the environment- You can access
start,end, andexecutiontimefor incremental logic
I often use Python models for cases where I need to enrich data from an external API or apply an ML model. The flexibility is great, and it still integrates with the SQLMesh lineage and scheduling system.
The Various Model Kinds
SQLMesh has several model kinds that determine how a model is materialized. This is important to understand because choosing the right kind greatly affects performance and cost. Let us cover the most commonly used ones.
FULL is the simplest. Every time the model is refreshed, the table is dropped and rebuilt entirely. Good for small models or aggregates that are easy to recompute:
MODEL (
name demo.summary,
kind FULL,
cron '@daily'
);
SELECT category, COUNT() AS total
FROM demo.orders
GROUP BY category;
INCREMENTALBYTIMERANGE is the kind we discussed earlier. The model is processed per time range, good for event data or logs that keep growing. You must specify the timecolumn:
MODEL (
name demo.events,
kind INCREMENTALBYTIMERANGE (
timecolumn ts
),
start '2024-01-01',
cron '@hourly'
);
SELECT userid, action, ts
FROM demo.rawevents
WHERE ts BETWEEN @startts AND @endts;
INCREMENTALBYUNIQUEKEY is for upsert cases. If a row with the same key exists, the old row is updated. Good for changing dimension data, for example user profiles:
MODEL (
name demo.users,
kind INCREMENTALBYUNIQUEKEY (
uniquekey userid
),
cron '@daily'
);
SELECT userid, email, updatedat
FROM demo.rawusers;
VIEW just creates a view, it does not store physical data. Lightweight but the query is recomputed each time it is accessed:
MODEL (
name demo.activeusers,
kind VIEW
);
SELECT FROM demo.users WHERE status = 'active';
Besides those there is SCDTYPE2 for slowly changing dimensions that automatically track change history, and EMBEDDED for models that are inlined into other models. But to get started, the four kinds above already cover the majority of needs.
Virtual Data Environments
This is the feature that made me fall in love with SQLMesh. The virtual environment concept. Imagine you want to try a model change without disturbing production. In dbt you have to create a new schema and rebuild everything. In SQLMesh, you just create a new environment:
sqlmesh plan dev
This command creates an environment named dev. The cool thing is, if your models have not changed from prod, SQLMesh does not rebuild anything. It just creates virtual views pointing to the prod physical tables. So the dev environment becomes instant and computationally free.
Now suppose you change one model, then run plan dev again. SQLMesh detects which model changed and only rebuilds that one plus its downstream. You can preview the data in dev, validate it, and only after you are confident promote it to prod:
sqlmesh plan
When you apply to prod, SQLMesh simply swaps the virtual views to point to the new tables you built in dev. There is no rebuild in prod. This is why it is called "virtual" because promoting to prod is just a pointer operation, not a recomputation. Deployment becomes fast and safe.
To see the resulting data in a specific environment, you can query it with the environment suffix:
sqlmesh fetchdf "SELECT FROM demo_dev.fullmodel LIMIT 10"
Notice the schema becomes demo_dev, that is the marker for the dev environment. This makes isolation between environments very clean.
Audits: Protecting Data Quality
Audits are the way SQLMesh validates the data produced by a model. If an audit fails, SQLMesh can stop the pipeline so bad data does not reach production. SQLMesh has built-in audits like notnull, uniquevalues, and acceptedvalues. You attach them directly in the MODEL block:
MODEL (
name demo.orders,
kind FULL,
audits (
notnull(columns := (orderid, customerid)),
uniquevalues(columns := (orderid)),
acceptedvalues(column := status, isin := ('pending', 'shipped', 'delivered'))
)
);
SELECT orderid, customerid, status
FROM demo.raworders;
You can also create custom audits in the audits/ folder. A custom audit is a SQL query that returns rows that VIOLATE the rule. If the query returns rows, that means the audit fails:
AUDIT (
name assertpositiveorderids
);
SELECT *
FROM @thismodel
WHERE orderid < 0;
Here @thismodel is a macro that points to the model being audited. This query looks for negative orderids. If any exist, the audit fails and the pipeline stops. Simple but powerful for protecting data integrity.
Unit Tests
Besides audits (which run on real data), SQLMesh has unit tests that run on sample data. The difference is that unit tests are deterministic: you give fixed inputs, then check that the output matches expectations. This is great for making sure the transformation logic is correct before it hits real data. Tests are written in the tests/ folder using YAML format:
testfullmodel:
model: demo.full
model
inputs:
demo.incrementalmodel:
- id: 1
itemid: 1
- id: 2
itemid: 1
- id: 3
itemid: 2
outputs:
query:
- itemid: 1
numorders: 2
- itemid: 2
numorders: 1
You run the tests with:
sqlmesh test
SQLMesh runs the model query using the inputs you provide, then compares against the expected output. If they do not match, the test fails and you are told exactly where the difference is. I always recommend writing unit tests for models with complex logic, because it is far cheaper to catch bugs here than in production.
Column-Level Lineage
One of the big advantages of SQLMesh is automatic column-level lineage. Because SQLMesh parses your SQL using SQLGlot, it knows exactly which output column originates from which input column. To see the lineage visually, run the web UI:
sqlmesh ui
Then open your browser to http://localhost:8000. You will see a graph of your models and you can click any column to see where it comes from and where it is used downstream. This is very helpful during debugging or impact analysis. For example if you want to change one column, you can immediately see which models are affected before making the change.
Best Practices
After using SQLMesh for a while, there are a few practices that make life easier. I have summarized them here so you do not have to learn from mistakes like I did.
First, always use a virtual environment for development. Never apply directly to prod. Create a dev environment first, run sqlmesh plan dev, preview the data, and only when you are confident promote it to prod. This uses the main strength of SQLMesh that makes dev cheap. There is no reason to mess with prod directly.
Second, leverage sqlmesh plan to see the impact of changes. One thing I love, when you change a model, SQLMesh tells you whether the change is "breaking" or "non-breaking". A breaking change (for example changing business logic) triggers a downstream rebuild. A non-breaking change (for example adding a comment or reformatting) does not. SQLMesh is smart about distinguishing these, but you still need to carefully review the summary it gives before typing y.
Third, define the grain in every model. The grain is the declaration of the columns that serve as the logical unique identifier of the model. Besides being documentation, the grain helps SQLMesh and your team understand the data structure. It also makes some validation features work better.
Fourth, choose the model kind deliberately. Do not just make everything FULL. If your data is large and keeps growing, INCREMENTALBYTIMERANGE saves a lot of compute cost. If it is dimension data that gets updated, use INCREMENTALBYUNIQUEKEY. Choosing the right kind is an important architectural decision that directly affects your warehouse bill.
Fifth, write audits and tests from the start. I know the temptation to skip testing is big, especially when you are in a rush. But audits and tests in SQLMesh are lightweight to write and save you from data quality issues that can damage stakeholder trust. At minimum, attach notnull and uniquevalues on key columns.
Sixth, use sqlmesh audit and sqlmesh test in your CI/CD. Integrating SQLMesh into a CI/CD pipeline is easy. Before merging a PR, run tests and audits automatically. If they fail, block the merge. This protects data quality systematically and prevents bugs from reaching production.
Seventh, leverage dbt compatibility if you are migrating. SQLMesh can read your existing dbt project through compatibility mode. So you do not have to rewrite everything from scratch. You can run your dbt project using the SQLMesh engine and enjoy virtual environments plus other features gradually. This makes migration far less scary.
Finally, document your models using description and column comments. SQLMesh generates documentation from this metadata. A well-documented data warehouse is a big asset for the team, especially when new members need to understand the data structure.
Comparison with dbt
Since many people will ask "so what is the difference from dbt?", I will address it specifically here to make it clear. I will be honest about the strengths and weaknesses of each.
From the model definition side, dbt uses heavy Jinja templating plus separate YAML files for configuration. SQLMesh uses a MODEL(...) block that is merged with the SQL and minimal Jinja. In my opinion the SQLMesh approach is cleaner and easier to read, but dbt has a mature Jinja macro ecosystem.
From the environment management side, this is the biggest difference. dbt requires a full rebuild for each new environment, which is expensive and slow. SQLMesh has virtual environments that make dev instant and cheap. This is a big SQLMesh advantage that is hard to beat.
From the incremental model side, dbt requires you to write manual isincremental() logic that is easy to get wrong. SQLMesh handles incrementals automatically through the @startdate/@enddate macros plus state tracking. I find incrementals in SQLMesh far safer and less bug-prone.
From the state and idempotency side, dbt is generally stateless, it does not remember what has been run. SQLMesh stores state, so it knows which intervals have been processed. This makes reruns safe and efficient, with no risk of double-processing.
From the ecosystem and community side, this is where dbt still wins. dbt has been around much longer, its community is large, there are many tutorials, many packages on dbt Hub, and many companies already use it. SQLMesh is newer, so its ecosystem is still developing even though it is growing fast. If you need broad community support, dbt is still the safer bet.
The conclusion is, dbt is mature and has a large ecosystem, good if you need stability and community support. SQLMesh is more technically innovative, especially for teams that have rebuild cost problems, complicated incrementals, or need to preview changes before applying. The good news is, you do not have to choose black-and-white because SQLMesh is compatible with dbt, so you can try it gradually.
Conclusion
Alright everyone, that was a fairly complete introduction to SQLMesh. We covered everything from installation with pip install sqlmesh, initializing a project with sqlmesh init, writing SQL and Python models, the various model kinds like FULL and INCREMENTALBYTIMERANGE, the sqlmesh plan concept that is the heart of the workflow, virtual data environments that make development cheap and safe, audits and tests to protect data quality, all the way to an honest comparison with dbt.
In my opinion SQLMesh brings fresh ideas that genuinely solve real problems I often experience in the data engineering world. Cheap virtual environments, automatic incrementals that are less bug-prone, and automatic column-level lineage are features that make day-to-day work far more pleasant. I am not saying you have to immediately abandon dbt, but SQLMesh is very worth putting on your radar, especially if you often run into the cost and complexity problems I mentioned earlier.
My advice is, try it first with DuckDB locally so you can experiment freely at no cost. Build a small project, play around with plans and virtual environments, and feel for yourself how nice it is. Once you are comfortable, then consider it for more serious projects. If you are currently using dbt and are curious, leverage the compatibility mode to try it without having to rewrite.
I hope this tutorial helps everyone looking for a modern alternative for data transformation. If you have questions or want to discuss further, do not hesitate to reach out to me. Happy hacking with your data, and see you in the next tutorial!