SQLMesh Lineage and Envs

Column-Level Lineage and Virtual Environments in SQLMesh

Because SQLMesh parses the SQL, its lineage reaches individual columns, not just models. A small script calls its column_dependencies() API hop by hop:

Tracing genre_month.gross back to its source columnsShell

python lineage.py booknest.genre_month gross

Output

booknest.genre_month.gross
  <- booknest_sm.booknest.stg_sales.gross_amount
    <- booknest_sm.main.order_items.qty
    <- booknest_sm.main.order_items.unit_price

Virtual data environments separate physical tables from the names people query. Each model version is built once into a table in sqlmesh__booknest; an environment is just a schema of views pointing at versions. Adding a copies column (sum(qty) AS copies) and planning a dev environment builds only the changed model:

A change planned in dev, then promoted to prod

sqlmesh plan dev --auto-apply
sqlmesh plan --auto-apply

Output

* `booknest__dev.genre_month` (Non-breaking)
[1/1] booknest__dev.genre_month   [full refresh]
...
* `booknest.genre_month` (Non-breaking)
SKIP: No physical layer updates to perform
SKIP: No model batches to execute
Virtual layer updated

Before promotion, information_schema showed views booknest.genre_month and booknest__dev.genre_month over two physical tables, booknest__genre_month__1272746190 and __2868695462, while stg_sales was shared, not copied. Promoting to prod repointed a view and recomputed nothing. On a large warehouse this makes development environments nearly free and a deployment instantaneous; dbt 37,942 reaches part of this with --defer and clones.

MENU