SQLMesh tracks model state, dependencies, backfills, and virtual environments so teams can update data models without rerunning unchanged work.
2
Prefect can schedule a SQLMesh project through one task, although the open-source integration does not expose each SQLMesh model as a separate Prefect task.
3
Ben argues that teams should put feature transformations into atomic, visible models when they need shared ownership and clearer review, while Eric prefers starting with simpler monolithic pipelines.
Summary
Ben Eric and Demetrios Brinkmann continue a live build of an MLOps platform around SQLMesh and DuckDB. They fix SQL quoting and timestamp issues, inspect model lineage, run backfills, add a rolling average, and examine how SQLMesh stores state and skips work that has already run. The data comes from a RuneScape marketplace API, which gives them a practical stream for testing time-based models. They then wrap the SQLMesh command in a simple Prefect flow and discuss what is lost when the flow treats the whole project as one task. The second half becomes a design discussion about monolithic versus decoupled feature, training, and inference pipelines. Ben favors atomic models in a shared repository, with reviews, audits, and reusable features. Eric points out that small teams often move faster with one pipeline and can split components later. Both agree that the right structure depends on team size, reuse, ownership, and the cost of debugging.
SQL quoting caused SQLMesh to read identifiers as string values
The first errors came from SQL syntax that looked harmless in the model definitions. A model name written with single quotes was treated as a string literal rather than an identifier, so SQLMesh saw a different nesting level from the other models. The same problem affected the timestamp column: the code cast the literal text TS instead of referring to the TS column. Ben says the errors were difficult to diagnose and that SQLMesh could make them easier to catch with documentation and linting. The example shows that SQL files used as model definitions still have their own interpretation rules.
The example uses SQLMesh to turn an API response into time-based tables
The project pulls RuneScape marketplace data through a DuckDB HTTP function. The API returns a large JSON object whose keys are item IDs, with price and volume fields nested below each key. The SQL model casts the response to JSON, unnests it, extracts the item ID, and produces rows with average high and low prices and volumes. SQLMesh supplies the time window for each incremental run. If several hourly windows are missing, SQLMesh can execute the model for each window and use the start timestamp inside the query.
SQLMesh virtual environments let engineers plan changes without sharing unfinished data
Running SQLMesh plan in Dev creates an environment-specific version of the models. Ben explains that the production model name remains separate from the version with the environment suffix. Engineers can work with their own virtual environments and data, then SQLMesh reconciles the work and avoids running the same query twice when it has already been processed elsewhere. The platform also records model dependencies, backfill ranges, and processed intervals. The team notes that a transactional database such as Postgres is preferable for SQLMesh state in a serious deployment, even though this demo stores it in DuckDB.
Backfills and batch settings determine how missed hourly windows are processed
The team initially expects an hourly cron setting to execute one query per hour, then clarifies that the cron defines the completed time windows SQLMesh needs to cover. Batch size controls how those windows are grouped. Setting the batch size to one hour makes SQLMesh run each missed hourly window separately. SQLMesh also supports lookback windows for data that arrives late. The backfill demonstrates the value of stored state: once a window has been processed, later runs skip it unless the model changed or the data needs to be refreshed.
Prefect can schedule the project with one task, but that limits model-level debugging
The Prefect example is deliberately small. A flow invokes the SQLMesh command as one task, passes the project path, and runs the Dev environment. A Prefect deployment can then be pushed to Prefect Cloud and scheduled through a YAML file. The trade-off is visibility. If the command fails, Prefect reports a failed SQLMesh task rather than identifying the individual model. Ben contrasts this with Airflow integration, where SQLMesh can expose individual models as tasks. They also discuss submitting separate time windows concurrently, but warn that doing so would duplicate orchestration logic that SQLMesh already handles.
The speakers disagree about when feature transformations should leave model pipelines
Ben argues that feature generation should live in SQLMesh or a similar model system whenever possible. He wants each transformation to be an atomic unit with clear ownership, tests, and review. Eric says monolithic feature-training-inference pipelines are often easier for small teams because schema contracts stay together and changes can be deployed as one unit. They use the feature-training-inference pattern from Hopsworks to frame the choice. Decoupling can make features reusable, but it also creates contracts between separate systems and introduces more coordination.
Shared repositories can reduce duplicated feature logic, but they add coordination costs
Ben says a shared SQLMesh project can expose features created by one team to another team, while code owners and model boundaries provide review. He is concerned that last-mile transformations inside separate training DAGs can grow into long, conditional functions that are difficult to test and debug. Eric points out that a monorepo can make engineers aware of unrelated projects and create the risk of stepping on other teams. They agree that separate SQLMesh projects remain possible when teams are genuinely isolated. The appropriate boundary depends on how many people collaborate, how much feature reuse exists, and how much review the organization needs.
"The goal of this live stream is that Eric and I have been building MLOps systems for a really long time and there are more tools than we have opportunities to play with in our jobs."Demetrios Brinkmann12:27
Who should watch
You are choosing between SQLMesh and a scheduler such as Prefect or Airflow and want to see how the integration behaves in a real project.
Your team is deciding whether feature generation belongs in shared data models or inside each training pipeline.
You want practical examples of backfills, time windows, model state, virtual environments, and debugging rather than a polished product demo.
