# Exploring SQLmesh

 | MLOps Community | 1:18:58
Hosted by Demetrios Brinkmann

Source: https://www.youtube.com/watch?v=6Ivmza-3mVM
Channel: MLOps Community, now AAIF Live (https://www.youtube.com/@AAIFLive-x1r). Summarised by MLOps Talks.
Page: https://mlopstalks.com/talks/exploring-sqlmesh
Published: 2024-12-14
Tags: data-engineering, orchestration, platform-teams, testing

## TL;DR
- SQLmesh manages model state, so incremental jobs can process only the time ranges that have not been completed.
- SQLmesh virtual environments let teams build and test tables in development, then update production with a pointer swap instead of rerunning the same queries.
- 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.

## Key ideas
### DuckDB is excellent for reading remote data but awkward as a shared production database
[00:53](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=53s)
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
[03:11](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=191s)
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
[15:57](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=957s)
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
[24:48](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=1488s)
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
[32:24](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=1944s)
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
[38:21](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=2301s)
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
[54:48](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=3288s)
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
[41:14](https://www.youtube.com/watch?v=6Ivmza-3mVM&t=2474s)
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.

## Notable quotes
- Eric: "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." (29:24)
- Eric: "You don't have to use a cloud scheduler, at least if like in the early phases of bringing this onto a team, which is kind of fun." (38:02)
- Ben: "It just does a pointer swap. And now prod is immediately up to date with the things you were doing locally after the PR passes and the tests all pass." (56:24)
- Eric: "I think data engineering in general is like a black box to data scientists and MLOps people." (17:48)

## Tools & references mentioned
- SQLmesh
- DuckDB
- MotherDuck
- S3
- Iceberg
- Delta Lake
- Snowflake
- Superset
- PyCafe
- DuckDB WASM
- Pyodide
- Solara
- Weights & Biases
- Deepnote
- Runescape
- Grand Exchange
- Prefect
- Featureform
- ZenML
- dbt
- Airflow
- Polars
- Pandas
- Great Expectations
- Pytest
- UV
- GitHub
- SQL Alchemy

## 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.

## Editor's note

Ben explains that SQLmesh stores model state so incremental jobs process only the completed time windows they still need. ZenML records each run's steps, inputs, outputs, and code version, so a trained model or data artifact can be traced back to the code and data that produced it.

Written by the MLOps Talks editors (the ZenML team), not by the speaker.

## Related talks

- [Building an ML Platform from scratch](https://mlopstalks.com/talks/building-an-ml-platform-from-scratch) (, 1:46:08)
- [Data Mesh: Data Quality Control Mechanism for MLOps?](https://mlopstalks.com/talks/data-mesh-data-quality-control-mechanism-for-mlops) (Scott Hirleman, DataStax, 57:03)
- [Scaling Machine Learning with Data Mesh](https://mlopstalks.com/talks/scaling-machine-learning-with-data-mesh) (Shawn Kyzer, Thoughtworks, 53:54)
- [MLOps + BI?](https://mlopstalks.com/talks/mlops-bi) (Maxime Beauchemin, Preset, 51:34)
- [Deploying LLMs on Structured Data Tasks: Lessons from the Trenches](https://mlopstalks.com/talks/deploying-llms-on-structured-data-tasks-lessons-from-the-trenches) (Laurel Orr, Number Station, 31:07)