SQLmesh manages model state, so incremental jobs can process only the time ranges that have not been completed.
2
SQLmesh virtual environments let teams build and test tables in development, then update production with a pointer swap instead of rerunning the same queries.
3
MLOps engineers need to understand data modeling, lineage, backfills, and the handoff from raw data to clean tables.
Summary
Ben and Demetrios Brinkmann use DuckDB, Runescape marketplace data, and Prefect as a practical setting for exploring SQLmesh. They first discuss DuckDB's limits as a shared production database. DuckDB can read remote Iceberg and Delta tables, but the open-source version cannot write to remote DuckDB files, which makes collaboration difficult. The main walkthrough covers SQLmesh projects, DuckDB-backed models, full and incremental model kinds, audits, tests, column-level lineage, and virtual environments. SQLmesh tracks which time ranges have already been processed. It can fill missing daily or hourly ranges, split work into smaller windows, and run those windows in parallel. Its development environment can be planned and tested locally, then promoted to production through a pointer swap when the underlying data is already current. The live coding session ends before the Runescape API data is fully joined into a Python model, with several unresolved SQL and API-shape errors.
DuckDB is excellent for reading remote data but awkward as a shared production database
Ben distinguishes between using DuckDB to query data and using it as the production database. DuckDB can read remote DuckDB files, Iceberg tables, and Delta tables, but the open-source version does not provide a good way to write to remote locations other than producing Parquet files. He tried mounting S3 as a local file system, which created the lock but was very slow. He also exposed DuckDB through a Modal endpoint, but that arbitrary REST interface could not connect cleanly to SQLmesh, Prefect, or SQL Alchemy. MotherDuck is presented as the practical option when shared writes are needed.
Local DuckDB can reduce the cost of read-heavy analytics on a lakehouse
The discussion proposes using a local DuckDB instance for read-only work against an Iceberg or Delta lakehouse. A data scientist or analytics team could connect to a development environment and run queries locally rather than paying Snowflake for compute on every read. DuckDB has native Iceberg and Delta support, and its Delta extension is built in. The idea could even extend to browser-based applications using DuckDB WASM or Pyodide, although the speakers acknowledge that a browser would quickly run out of memory and storage.
MLOps engineers need to understand the data work that precedes model training
The longer-term project is intended to build an MLOps platform with GitHub CI/CD, training, retraining, monitoring, and orchestration. The speakers deliberately start with data engineering because many MLOps tutorials skip from a dataset to training and serving. They describe the MLOps role as supporting raw-to-modeled data, feature creation, column-level lineage, breakages, backfills, and scaling those backfills. SQLmesh or dbt can move data from raw through bronze and silver into clean gold tables that become the handoff to machine learning work.
SQLmesh extends the versioned SQL workflow that made dbt popular
The speakers credit dbt with bringing analytics SQL into Git and giving teams a common way to reference models, build a dependency graph, and collaborate. They criticize Jinja-heavy SQL as limited and point out that dbt has no inherent concept of processing state. SQLmesh keeps the model graph while adding features the speakers value, especially virtual environments and state-aware backfills. They also mention SQLmesh's plugins, its support for different backends, and a GitHub CI/CD workflow that they describe as especially useful.
Incremental models let SQLmesh process only completed time windows
Ben explains the difference between full and incremental models. A full model reruns its query, while an incremental-by-time model defines a time column and an optional start date. SQLmesh stores the state of each model and supplies start and end values so the query processes only the relevant time range. With a daily cron, a run after several missed days fills each completed daily window and does not process the current unfinished day. An hourly model behaves the same way, stopping at the most recently completed hour.
SQLmesh can split backfills into smaller parallel batches
Incremental-by-time models can be divided into smaller units when a complete day is too large for one machine. Ben describes configuring a daily cron while splitting each day into hourly or smaller windows, with a chosen level of parallelism. SQLmesh then fills the resulting ranges back into the database. The speakers compare this with Airflow backfills, which they found difficult to manage, and say that SQLmesh makes the operation part of the model's normal state handling. The same approach can support work done with Polars when a full day's data will not fit locally.
Virtual environments avoid paying twice for development and production queries
After planning and backfilling models in a development environment, SQLmesh can update production with a pointer swap when the development tables already contain the required results. Ben says the production plan can recognize that the work was completed in a virtual environment and avoid rerunning the queries. In a CI/CD setup, a pull request can run the plan and tests in development. Once merged, production can point to the already validated tables instead of paying again for identical computation.
The live example shows both SQLmesh's metadata and the limits of unprepared API data
The example uses Runescape's Grand Exchange API. One endpoint provides item mappings, while another provides aggregated high and low prices over a time period. DuckDB can query the JSON endpoint directly with read_json_auto. SQLmesh displays the model graph and column-level lineage, and audits can fail a job when a condition such as a positive ID is violated. The session runs into trouble because the price response is a JSON object rather than the row-shaped data the model expects. The speakers stop while debugging timestamp formatting, JSON extraction, and a Python model error.
"SQLMesh does. So, like there's two things minimum that I can say that SQLMesh is already doing better than DBT. So, one is it got rid of Jinja. It actually added some plugins that are really nice. So, software engineers tend to like it. And secondly, it manages state."Eric29:24
Who should watch
You are evaluating SQLmesh against dbt and want to understand its handling of state, incremental models, and backfills.
Your ML platform has weak data-management practices and you need a concrete view of lineage, modeled tables, and the handoff to feature work.
You are deciding whether local DuckDB can support development or read-heavy analytics against Iceberg or Delta data.
