Skip to content

sqlmesh

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

sqlmesh

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
examples
examples
 
 
tools
tools
 
 
README.md
README.md
 
 

README.md

Cheat Sheet - SQLMesh

Table of Content (ToC)

Created by gh-md-toc

Overview

This cheat sheet explains how to install and to use SQLMesh, e.g., on a laptop or on a virtual machine (VM).

SQLMesh is a next-generation data transformation framework designed to ship data quickly, efficiently, and without error. Data teams can efficiently run and deploy data transformations written in SQL or Python with visibility and control at any size. It is more than just a dbt alternative.

SQLMesh requires some database to store its state. DuckDB is the database by default, as it is small and efficient enough to be available virtually everywhere:

DuckDB is an embedded database, similar to SQLite, but designed for OLAP-style analytics. It is crazy fast and allows you to read and write data stored in CSV, JSON, and Parquet files directly, without requiring you to load them into the database first.

For production-ready deployments, other database backends, like PostgreSQL, may be advised.

References

Data Engineering helpers

Slowly Changing Dimension (SCD)

How to create an SCD2 Table using MERGE INTO with Spark & Iceberg

Wikipedia article

SQLMesh

Documentation reference for SQLMesh

Concepts

Development

Models

Macros

Metrics

Architecture

Integrations

Integration with tools
Integration with execution engines

Git repository with SQLMesh examples

Sushi project

Ibis project

DuckDB

Unity Catalog (UC)

Articles and Git knowledge sharing projects

Reddit thread

SQLMesh - Migrate

In short, if you use dbt-core and run it yourself in Airflow or in a container on cron etc, there is no reason not to switch to SQLMesh, and there are many reasons to do so.

The fact that it’s backwards compatible with dbt means it can’t be ignored. I know that learning a new framework is a big deal for most data folks. You are under pressure to deliver instead of trying new tools, but you can even run an existing dbt project using sqlmesh and continue to keep building dbt models if you don’t want to learn the SQLMesh way. You get the benefits of virtual data environments and the sqlmesh plan/apply workflow, which are substantial for very little lift.

Then, when you have time, you can try the SQLMesh model kinds and see that they are not so difficult or different to use.

SQLMesh as alternative to dbt

Arcane insight

Multi-engine Stacks

The rise of the analytics pretendgineer

Unlocking data insights with Ibis and SQLMesh

SQL + DataOps = SQLMesh

Time To Move From dbt to SQLMesh

Introduction to the examples in this Git repository

  • Examples in this Git repository:

  • In most of the example directories, there is a Makefile with the most used commands as targets, for instance:

    • Clean the project from potential previous experiments: make clean
    • Create the prod environment: make plan-prod
    • List the tables in prod: make list-tables-prod
    • Browse the content of the main table in prod: make check-data-prod
    • Suggest what to do next in order to introduce a change: make hint-change
    • Create a dev environment: make plan-dev
    • List the tables in dev: make list-tables-dev
    • Browse the content of the main table in dev: make check-data-dev
    • List the differences for the main table between dev and prod: make diff

  • A typical sequence is:

make clean
make plan-prod
make list-tables-prod
make check-data-prod
make hint-change
# Make a change on the model suggested above => vi models/some_model.{sql,py}
make plan-dev
make list-tables-dev
make check-data-dev
make diff

Quickstart

  • This sesction is a reproduction, step by step and with the full source code, of the quickstart guide on the SQLMesh documentation

  • It features a simple example with DuckDB, both as the execution engine and to store the SQLMesh state, in a local data file (namely db.db) ignored by Git (so that the example may be reproduced without interfering with this Git repository)

  • Change to the examples/001-simple directory within the SQLMesh dedicated directory:

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/001-simple
  • Clean/remove results of potential earlier tries (those files are ignored by Git):
make clean # equivalent of:
rm -rf .cache logs db.db

Some information about the project

  • The info command gives a high level overview of the project:
sqlmesh info
Models: 3
Macros: 0
Data warehouse connection succeeded

Initial models

  • The datasets are materialized as tables in a (to be created) prod environment

  • The datasets are also called models. In the remainder of the documentation, datasets, tables and models may be interchanged

Create a prod environment

sqlmesh plan
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
`prod` environment will be initialized

Models:
└── Added:
    ├── sqlmesh_example.full_model
    ├── sqlmesh_example.incremental_model
    └── sqlmesh_example.seed_model
Models needing backfill (missing dates):
├── sqlmesh_example.full_model: 2024-12-24 - 2024-12-24
├── sqlmesh_example.incremental_model: 2020-01-01 - 2024-12-24
└── sqlmesh_example.seed_model: 2024-12-24 - 2024-12-24
Apply - Backfill Tables [y/n]:
  • Answer yes (y) to the prompted question ("backfill tables?"):
Creating physical tables ━━━━ ... ━━━━━ 100.0% • 3/3 • 0:00:00

All model versions have been created successfully

[1/1] sqlmesh_example.seed_model evaluated in 0.03s
[1/1] sqlmesh_example.incremental_model evaluated in 0.01s
[1/1] sqlmesh_example.full_model evaluated in 0.01s
Evaluating models ━━━━━━ ... ━━━━━━━ 100.0% • 3/3 • 0:00:00


All model batches have been executed successfully

Virtually Updating 'prod' ━━━━━━━ ... ━━━━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully

  • It will update the DuckDB database (db.db), which is ignored by Git

  • Logs are available in the logs/ sub-directory (also ignored by Git) and cache files are to be found in the .cache/ sub-directory (also ignored by Git)

  • If the SQLMesh plan is run again, this time, there will be no change (the SQLMesh commands are idempotent):

sqlmesh plan
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
No changes to plan: project files match the `prod` environment

Check the new state brought by the plan

  • The sqlmesh fetchdf command is a proxy to the backend database, DuckDB in this example. The content of the backend database may therefore be queries either through the sqlmesh fetchdf command or directly with the backend database.

  • In the remaining of this sub-section, DuckDB is used directly. All the SQL queries could also be executed thanks to the sqlmesh fetchdf command, for instance:

sqlmesh fetchdf "select * from sqlmesh_example.incremental_model"
   id  item_id event_date
0   1        2 2020-01-01
 ...
6   7        1 2020-01-07
  • Check the models with DuckDB
    • Launch DuckDB on the just created/updated database (namely db.db) (as a reminder, to quit the Duck shell, either type Control-D or the .quit command):
duckdb db.db
  • List all the tables:
D show all tables;
  • List the items of the seed_model table:
D select * from sqlmesh_example.seed_model;
┌───────┬─────────┬────────────┐
│  id   │ item_id │ event_date │
│ int32 │  int32  │    date    │
├───────┼─────────┼────────────┤
│     1 │       2 │ 2020-01-01 │
 ...
│     7 │       1 │ 2020-01-07 │
└───────┴─────────┴────────────┘
  • List the items of the incremental_model table:
D select * from sqlmesh_example.incremental_model;
┌───────┬─────────┬────────────┐
│  id   │ item_id │ event_date │
│ int32 │  int32  │    date    │
├───────┼─────────┼────────────┤
│     1 │       2 │ 2020-01-01 │
 ...
│     7 │       1 │ 2020-01-07 │
└───────┴─────────┴────────────┘
  • List the items of the full_model table:
D select * from sqlmesh_example.full_model;
┌─────────┬────────────┐
│ item_id │ num_orders │
│  int32  │   int64    │
├─────────┼────────────┤
│       2 │          1 │
│       1 │          5 │
│       3 │          1 │
└─────────┴────────────┘
  • Quit DuckDB:
D .quit

Launch the tests

  • Launch the tests:
sqlmesh test
.
----------------------------------------------------------------------
Ran 1 test in 0.021s

OK

Introduce a change in the incremental model

Create a dev environment

sqlmesh plan dev
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
New environment `dev` will be created from `prod`

Differences from the `prod` environment:

Models:
├── Directly Modified:
│   └── sqlmesh_example__dev.incremental_model
└── Indirectly Modified:
    └── sqlmesh_example__dev.full_model
...
Directly Modified: sqlmesh_example__dev.incremental_model (Non-breaking)
└── Indirectly Modified Children:
    └── sqlmesh_example__dev.full_model (Indirect Non-breaking)
Models needing backfill (missing dates):
└── sqlmesh_example__dev.incremental_model: 2020-01-01 - 2024-12-24
Enter the backfill start date (eg. '1 year', '2020-01-01') or blank to backfill from the beginning of history:
Enter the backfill end date (eg. '1 month ago', '2020-01-01') or blank to backfill up until '2024-12-25 00:00:00':
Apply - Backfill Tables [y/n]:
  • Answer yes when prompted about applying the change:
Apply - Backfill Tables [y/n]: y
Creating physical tables ━━━━━ ... ━━━━━ 100.0% • 3/3 • 0:00:00

All model versions have been created successfully

[1/1] sqlmesh_example__dev.incremental_model evaluated in 0.04s
Evaluating models ━━━━━ ... ━━━━━━ 100.0% • 1/1 • 0:00:00


All model batches have been executed successfully

Virtually Updating 'dev' ━━━━━ ... ━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully

Check the new state brought by the plan on dev

  • Check the content of the updated table in the dev environment:
sqlmesh fetchdf "select * from sqlmesh_example__dev.incremental_model"
   id  item_id new_column event_date
0   1        2          z 2020-01-01
 ...
6   7        1          z 2020-01-07
  • Even though there is a new full_model table in the dev environment, its content is the same as the same table on the prod environment (as this table does not use the new column yet):
sqlmesh fetchdf "select * from sqlmesh_example__dev.full_model"
   item_id  num_orders
0        2           1
1        1           5
2        3           1
  • There is a command, namely table_diff, to display the differences between two environment for a given table:
sqlmesh table_diff prod:dev sqlmesh_example.incremental_model

Schema Diff Between 'PROD' and 'DEV' environments for model 'sqlmesh_example.incremental_model':
└── Added Columns:
    └── new_column (TEXT)

Row Counts:
└──  FULL MATCH: 7 rows (100.0%)

COMMON ROWS column comparison stats:
         pct_match
item_id      100.0

Update the prod environment

sqlmesh plan # prod
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
Differences from the `prod` environment:

Models:
├── Directly Modified:
│   └── sqlmesh_example.incremental_model
└── Indirectly Modified:
    └── sqlmesh_example.full_model
...
Directly Modified: sqlmesh_example.incremental_model (Non-breaking)
└── Indirectly Modified Children:
    └── sqlmesh_example.full_model (Indirect Non-breaking)
Apply - Virtual Update [y/n]:
  • Answer yes when prompted about applying the change:
Apply - Backfill Tables [y/n]: y
Creating physical tables ━━━━ ... ━━━━━ 100.0% • 3/3 • 0:00:00

All model versions have been created successfully

Virtually Updating 'prod' ━━━━ ... ━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully


Virtual Update executed successfully

Check the upddates in the prod environment

sqlmesh fetchdf "select * from sqlmesh_example.incremental_model"
   id  item_id new_column event_date
0   1        2          z 2020-01-01
 ...
6   7        1          z 2020-01-07
  • The table_diff command now reports that the tables are the same in both the dev and prod environments:
sqlmesh table_diff prod:dev sqlmesh_example.incremental_model

Schema Diff Between 'PROD' and 'DEV' environments for model 'sqlmesh_example.incremental_model':
└── Schemas match


Row Counts:
└──  FULL MATCH: 7 rows (100.0%)

COMMON ROWS column comparison stats:
            pct_match
item_id         100.0
new_column      100.0

Cleanup

  • For convenience, all the clean commands are featured in a single Makefile target:
make clean
  • For reference, the following cleaning operations are performed
  • As DuckDB stores both the state and the datasets, cleaning up is as straightforward as deleting the DuckDB data file, namely db.db:
rm -f db.db
  • Delete also the log and the cache directories:
make clean # equivalent of:
rm -rf .cache logs
  • Comment the clause for the z column in the incremental_model model:
make hint-change
grep "z" models/incremental_model.sql
    --'z' AS new_column, -- Added column
  • The project is now ready to start afresh, with no memory nor any change when compared to the Git repository

More advanced examples

Local PostgreSQL to store the state

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/002-postgresql-state
  • Clean/remove results of potential earlier tries (those files are ignored by Git):
make clean # equivalent of:
rm -rf .cache logs db.db db.db.wal
  • The other steps (i.e., SQLMesh plan, introduce a change, SQLMesh dev environment, check the updates, SQLMesh plan to merge the updates on prod, cleanup the project) are the same as in the quickstart example. The Makefile has been adapted to take PostgreSQL into account

Simple Python example

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/003-python-simple

  • To create a project skeleton with Python models, simply use the sqlmesh init command, that is, using the default dialect (being DuckDB), like for SQL models. So, there are no difference, at that stage, between a project for SQL models and a project for Python models

  • Note that the sqlmesh init command has already been performed and the resulting project skeleton is part of this Git repository

  • Note that the sqlmesh init command accepts python as a dialect.

    • But if a project skeleton is created that way (i.e., with the sqlmesh init python command), the resulting project skeleton looks similar to a regular SQL-model project, with the important difference that the dialect in the config.yaml configuration file will be python rather than duckdb.
    • And then, when launching the SQLMesh plan (with the sqlmesh plan command), the underlying SQLGlot engine will fail with some cryptic error:
make plan-prod # equivalent of:
sqlmesh plan
Error: Required keyword: 'this' missing for <class 'sqlglot.expressions.Between'>. Line 1, Col: 239.
  odel WHERE BETWEEN(scope[None][event_date], DATESTRTODATE('1970-01-01'), DATESTRTODATE('1970-01-01'))

SQLMesh plan with Python models

  • Launch the SQLMesh plan:
sqlmesh plan
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
`prod` environment will be initialized

Requirements:
+ pandas==2.2.3
Models:
└── Added:
    ├── sqlmesh_example.full_model
    ├── sqlmesh_example.incremental_model
    ├── sqlmesh_example.seed_model
    └── sqlmesh_example.full_model_python
Models needing backfill (missing dates):
├── sqlmesh_example.full_model: 2024-12-26 - 2024-12-26
├── sqlmesh_example.incremental_model: 2020-01-01 - 2024-12-26
├── sqlmesh_example.seed_model: 2024-12-26 - 2024-12-26
└── sqlmesh_example.full_model_python: 2024-12-26 - 2024-12-26
  • Accept the suggestions at the prompt:
Apply - Backfill Tables [y/n]: y
Creating physical tables ━━━━ ... ━━━━ 100.0% • 4/4 • 0:00:00

All model versions have been created successfully

[1/1] sqlmesh_example.seed_model evaluated in 0.03s
[1/1] sqlmesh_example.full_model_python evaluated in 0.01s
[1/1] sqlmesh_example.incremental_model evaluated in 0.01s
[1/1] sqlmesh_example.full_model evaluated in 0.01s
Evaluating models ━━━━ ... ━━━━ 100.0% • 4/4 • 0:00:00


All model batches have been executed successfully

Virtually Updating 'prod' ━━━━━ ... ━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully

Check the content of the tables with Python models

  • Use the fetchdf command:
    • To list the tables:
make list-tables-prod # equivalent of
sqlmesh fetchdf "use sqlmesh_example; show tables"
                name
0         full_model
1  full_model_python
2  incremental_model
3         seed_model
  • To browse the content of the Python model table (that is, the sqlmesh_example.full_model_python table):
make check-data-prod # equivalent of:
sqlmesh fetchdf "use sqlmesh_example; select * from full_model_python"
   id   name
0   1  Laura
1   2   John
2   3  Lucie

Audit with Python models

  • Launch the audit command:
sqlmesh audit # equivalent of
Found 2 audit(s).
assert_positive_order_ids on model sqlmesh_example.full_model ✅ PASS.
not_null on model sqlmesh_example.full_model_python ✅ PASS.

Finished with 0 audit errors and 0 audits skipped.
Done.

Test with Python models

  • Launch the test command:
sqlmesh test # equivalent of
sqlmesh test
.
----------------------------------------------------------------------
Ran 1 test in 0.016s

OK

Cleanup

  • Clean/remove results of potential earlier tries (those files are ignored by Git):
make clean # equivalent of:
rm -rf .cache logs db.db
  • Comment the clause for the z column in the incremental_model model:
make hint-change # equivalent of:
grep "z" models/incremental_model.sql
    --'z' AS new_column, -- Added column

Full example with Python models

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/004-python-ibis

  • This example has not been generated with the sqlmesh init command, but it has rather been fully imported from the SQLMesh example Git repository (with git clone git@github.com:TobikoData/sqlmesh-examples.git into a temporary directory, and then rsync -av from that temporary directory unto this current python-ibis directory)

  • It features the Ibis framework, a Python library to translate dataframes from one dialect to another (it is similar to SQLGlot, but for Python instead of for SQL)

SQLMesh plan with Python models

  • Launch the SQLMesh plan:
make plan-prod # equivalent of
sqlmesh plan
New environment `prod` will be created from `prod`
Summary of differences against `prod`:
Models:
└── Added:
    ├── ibis.full_model
    ├── ibis.ibis_full_model_python
    ├── ibis.ibis_full_model_sql
    ├── ibis.incremental_model
    └── ibis.seed_model
Models needing backfill (missing dates):
├── ibis.full_model: 2020-01-01 - 2024-12-26
├── ibis.ibis_full_model_python: 2020-01-01 - 2024-12-26
├── ibis.ibis_full_model_sql: 2020-01-01 - 2024-12-26
├── ibis.incremental_model: 2020-01-01 - 2024-12-26
└── ibis.seed_model: 2024-12-26 - 2024-12-26
  • Answer yes to the prompt asking whether to apply and backfill the models:
Apply - Backfill Tables [y/n]: y
Creating physical table ━━━━ ... ━━━━ 100.0% • 5/5 • 0:00:00

All model versions have been created successfully

[1/1] ibis.seed_model evaluated in 0.00s
[1/1] ibis.incremental_model evaluated in 0.01s
[1/1] ibis.full_model evaluated in 0.01s
[1/1] ibis.ibis_full_model_python evaluated in 0.06s
[1/1] ibis.ibis_full_model_sql evaluated in 0.03s
Evaluating models ━━━ ... ━━━━ 100.0% • 5/5 • 0:00:00


All model batches have been executed successfully

Virtually Updating 'prod' ━━━ ... ━━━━ 100.0% • 0:00:00

The target environment has been updated successfully

Check the created tables

  • Note that the result of the sqlmesh fetchdf "show all tables" command may be truncated (i.e., the names of the tables do not appear). It is therefore advised to use a specific schema (e.g., ibis here):
make list-tables-prod # equivalent of
sqlmesh fetchdf "use ibis; show tables"
                     name
0              full_model
1  ibis_full_model_python
2     ibis_full_model_sql
3       incremental_model
4              seed_model
  • Browse the content of the incremental model/table:
make check-data-prod # equivalent of
sqlmesh fetchdf "select * from ibis.incremental_model"
   id  item_id event_date
0   1        2 2020-01-01
1   2        1 2020-01-01
2   3        3 2020-01-03
3   4        1 2020-01-04
4   5        1 2020-01-05
5   6        1 2020-01-06
6   7        1 2020-01-07

  • Anyway, DuckDB may also be used to explore the tables and the content. In the remainder of this sub-section, DuckDB will be used to explore the data

  • Launch the DuckDB shell

    • Note that, as specified within the config.yaml configuration file, the DuckDB data file is data/local.duckdb
    • As may be seen in the various models, the schema is ibis
    • In order to quit the DuckDB shell, type Control-D or the .quit command
duckdb data/local.duckdb
D use ibis;
D show tables;
┌────────────────────────┐
│          name          │
│        varchar         │
├────────────────────────┤
│ full_model             │
│ ibis_full_model_python │
│ ibis_full_model_sql    │
│ incremental_model      │
│ seed_model             │
└────────────────────────┘
  • Leave the DuckDB shell:
D .quit

Execution, tests and audits

  • Run the project (as there have been no change yet, running the project does not do anything):
sqlmesh run
Run finished for environment 'prod'
  • Launch the tests:
sqlmesh test

----------------------------------------------------------------------
Ran 0 tests in 0.000s

OK
  • Launch the audit:
sqlmesh audit
Found 3 audit(s).
assert_positive_order_ids on model ibis.full_model ✅ PASS.
assert_positive_order_ids on model ibis.ibis_full_model_python ✅ PASS.
assert_positive_order_ids on model ibis.ibis_full_model_sql ✅ PASS.

Finished with 0 audit errors and 0 audits skipped.
Done.

Simple PySpark example

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/005-pyspark-simple
  • The project has been initialized with the sqlmesh init spark command

SQLMesh plan

  • Launch the SQLMesh plan:
make plan-prod # equivalent of:
sqlmesh plan
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
`prod` environment will be initialized

Requirements:
+ pyspark==3.5.4
Models:
└── Added:
    ├── docs_example.pyspark
    ├── sqlmesh_example.full_model
    ├── sqlmesh_example.incremental_model
    └── sqlmesh_example.seed_model
Models needing backfill (missing dates):
└── docs_example.pyspark: 2024-12-26 - 2024-12-26
  • Answer yes to the prompt:
Apply - Backfill Tables [y/n]: y
Creating physical tables ━━━━ ... ━━━━━ 100.0% • 4/4 • 0:00:00

All model versions have been created successfully

Full end-to-end example

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/006-e2e

Simple DataBricks example

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/007-databricks-simple
  • The project has been initialized with the sqlmesh init spark command

Instantiate files depending on env vars

  • First, specify the following environment variables (for instance, in the ~/.bashrc or ~/.zshrc Shell configuration file):

    • DBS_SVR_HST - DataBricks host, e.g., <some-workspace>.cloud.databricks.com
    • DBS_HTTP_PATH - DataBricks HTTP path, e.g., sql/protocolv1/o/<wksp-id>/<cluster-id>
    • DBS_PAT - DataBricks Personal Access Token (PAT)
    • DBS_SCH - DataBricks schema/database, on which the DataBricks cluster should have the right to write. That schema is the one used by the SQLMesh models

  • Create the .env environment file, by substituting the environment variables in the .env.sample file:

envsubst < .env.sample > .env

  • That .env file is handled in a different way from all the .in files, as it is imported by the Makefile. That Makefile can therefore not alter the .env file itself, otherwise there will be a catch 22 situation

  • Execute the init-files target in order to substitute the environment variables into the model files, the test files and the configuration file:

make init-files

SQLMesh plan

  • Launch the SQLMesh plan:
make plan-prod # equivalent of:
sqlmesh plan
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
`prod` environment will be initialized

Requirements:
+ pyspark==3.5.4
Models:
└── Added:
    ├── docs_example.pyspark
    ├── sqlmesh_example.full_model
    ├── sqlmesh_example.incremental_model
    └── sqlmesh_example.seed_model
Models needing backfill (missing dates):
└── docs_example.pyspark: 2024-12-26 - 2024-12-26
  • Answer yes to the prompt:
Apply - Backfill Tables [y/n]: y
Creating physical tables ━━━━ ... ━━━━━ 100.0% • 4/4 • 0:00:00

All model versions have been created successfully

Simple Unity Catalog (UC) example

cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh/examples/008-unitycatalog-simple
  • The project has been initialized with the sqlmesh init spark command

Spark Connect server

sparkconnectstart
  • (At the end of the work session,) Shutdown the Spark Connect server:
sparkconnectstop

SQLMesh plan

  • Launch the SQLMesh plan:
make plan-prod # equivalent of:
sqlmesh plan
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
`prod` environment will be initialized

Requirements:
+ pyspark==3.5.4
Models:
└── Added:
    ├── docs_example.pyspark
    ├── sqlmesh_example.full_model
    ├── sqlmesh_example.incremental_model
    └── sqlmesh_example.seed_model
Models needing backfill (missing dates):
└── docs_example.pyspark: 2024-12-26 - 2024-12-26
  • Answer yes to the prompt:
Apply - Backfill Tables [y/n]: y
Creating physical tables ━━━━ ... ━━━━━ 100.0% • 4/4 • 0:00:00

All model versions have been created successfully

Installation

Clone this repository

mkdir -p ~/dev/knowledge-sharing
git clone https://github.com/data-engineering-helpers/ks-cheat-sheets ~/dev/knowledge-sharing/ks-cheat-sheets
cd ~/dev/knowledge-sharing/ks-cheat-sheets/data-processing/sqlmesh

DuckDB

  • See also Data Engineering Helpers - Knowledge Sharing - DuckDB for more details on how to install DuckDB

  • DuckDB may be installed through the native packaging utility, when available (for instance, on MacOS, brew install duckdb), through binary artifacts (on Linux) or through one of the programming stack utilities (for instance, for the Python stack, pip install -U duckdb)

Public data sets on DuckDB

$ duckdb
  • (In DuckDB,) attach to a public catalog, for instance the BlueSky catalog:
D attach 'https://hive.buz.dev/bluesky/catalog' as bluesky;
  • Check that the BlueSky data is available:
D select count(*)/1e6 as nb_rows from bluesky.jetstream;
┌─────────┐
│ nb_rows │
│ double  │
├─────────┤
│     1.0 │
└─────────┘
D select * from bluesky.jetstream limit 10;
  • To leave the DuckDB shell, either type Control-D or the .quit command:
D .quit

Unity Catalog (UC)

  • See Data Engineering Helpers - Knowledge Sharing - Unity Catalog (UC) (in this same Git repository) for details on how to install and use Unity Catalog (UC)

  • For just some trial of Unity Catalog, it is generally easier to use Docker compose (docker compose up)

  • For the seasoned data engineer, it makes however more sense to know what is under the hood and to maintain Unity Catalog (UC) natively with a Java Virtual Machine (JVM) and with a local PostgreSQL database to store the catalog (it can be the same PostgreSQL service storing some SQLMesh states, but with different databse, schema and user).

    • Building the UC JARs and publishing them goes something like:
cd ~/dev/infra/unitycatalog
git pull
sbt package publishLocal
  • In a dedicated tab of the terminal window, launch the Unity Catalog (Control-C to terminate the service)
    • With the default port (8080):
./bin/start-uc-server
  • With an alternative port (e.g., 9090):
./bin/start-uc-server -p 9090
  • (Optionally,) To start the UC UI, in another dedicated tab of the terminal window:
  • Start the UI through Yarn (Control-C to terminate the service):
cd ui
yarn install
yarn start
  • To interact with the UC
    • When the UC server has been started on the default port (entities: schema, volume, model_version, metastore, auth, catalog, function, permission, registered_model, user, table):
bin/uc <entity> <operation>
  • When the UC server has been started on an alternative port (say 9090), specify the --server parameter before the entity:
bin/uc --server http://localhost:9090 <entity> <operation>
  • List the catalogs (the default one is usually called unity):
bin/uc catalog list
  • List the schemas (the default one is usually called default):
bin/uc schema list --catalog unity
  • List the tables:
bin/uc table list --catalog unity --schema default
  • Browse the records of a given table (numbers is a sample usually provided with UC at the installation):
bin/uc table read --full_name unity.default.numbers

Spark Connect

ls -lFh ~/.ivy2/jars/io.unitycatalog*
  • (If not already done so,) Launch the Spark Connect server:
sparkconnectstart
  • (At the end of the work session,) Shutdown the Spark Connect server:
sparkconnectstop

SQLMesh

  • SQLMesh comes as a Python package, and may therefore installed simply with the Python packager. For instance:
python -mpip install -U "sqlmesh[web,databricks]"

  • The SQLMesh package installs two executable scripts, namely sqlmesh and sqlmesh_cicd, which are usually stored along side the other Python packages. For instance, with PyEnv, it will end up as wrappers in ~/.pyenv/shims/.

  • Usually, for the Shell (e.g., Bash or Zsh) to become aware of those newly installed executables scripts, it has to be refreshed (with the exec command)

    • For the Bash Shell:
exec bash
  • For the Zsh Shell:
exec zsh
  • Check the version of the just installed SQLMesh package:
sqlmesh --version
0.141.1

  • Note that most of the projetcs, to be found in Git repositories, have already been initialized; they no longer need initializing.

  • Clean/remove results of potential earlier tries (those files are ignored by Git):

make clean # equivalent of:
rm -rf .cache logs db.db

SQLMesh UI

  • In a separate terminal tab, as the default local port (8000) may already be taken by other processes (e.g., LakeFS is running on the 8000 port by default), launch the SQLMesh UI by specifying a port not already in use:
sqlmesh ui --port 9090
INFO:     Started server process [7586]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:9090 (Press CTRL+C to quit)

Local PostgreSQL server

Setup of the configuration for the local PostgreSQL to store the state

  • See https://sqlmesh.readthedocs.io/en/stable/guides/configuration/#overrides on the relevant SQLMesh configuration options

    • In particular, the password of the PostgreSQL database user may be specified within an environment variable, for instance SQLMESH__GATEWAYS__LOCAL__STATE_CONNECTION__PASSWORD
    • However, within that documentation, another method (not to expose the password in the Git repository) has been chosen; see the next bullet point for that alternative method

  • As the config.yaml configuration files contain credentials for the PostgreSQL database (even though those credentials are just some samples for local services, some security scanners may bump onto them in GitHub repositories and report them as false positives)

    • Git stores only a sample version of the config.yaml configuration file, namely config.yaml.sample
    • The config.yaml configuration file, that SQLMesh is expecting, has to be copied from the config.yaml.sample file and:
      • The password has to be adjusted in it
    • That config.yaml configuration file is ignored by Git (so that the credentials do not appear in clear in the Git repository)
    • The sequence is therefore as the following:
cp config.yaml.sample config.yaml
sed -i.bak -e 's/<sqlmesh-pass>/<REPLACE-HERE-BY-THE-POSTGRESQL-USER-PASSWORD>/' config.yaml && rm -f config.yaml.bak
  • Check that the content of the config.yaml configuration file seems correct
cat config.yaml | yq -r '.gateways.local.state_connection'
type: postgres
host: localhost
port: 5432
database: sqlmesh
user: sqlmesh
password: <REPLACE-HERE-BY-THE-POSTGRESQL-USER-PASSWORD>
  • SQLMesh is now ready to run with:
    • DuckDB as the execution engine
    • Local PostgreSQL database server to store the state
    • See Local PostgreSQL to store the state for the walkthrough
    • The next sub-sections detail a few specifities of having PostgreSQL to store the SQLMesh state

SQLMesh with PostgreSQL to store the state

  • Follow the same steps as in the pure DuckDB example

    • The behaviour of SQLMesh should be exactly the same, except that the state is now stored in the local PostgreSQL database, as can be checked with the state-related tables in the PostgreSQL database. See the remainder of this sub-section for the details

  • After sqlmesh plan

    • The datasets are in the DuckDB database, namely the dbwost.db file. The content may still be queried with the sqlmesh fetchdf command, e.g.:
sqlmesh fetchdf "select * from sqlmesh_example.full_model"
  • The state is stored in the PostgreSQL database. For instance:
    • List the state-related tables (the -t option is to display tuples only, that is, turn off the header, footer and comments):
# psql -h localhost -U sqlmesh -d sqlmesh -t -c "\dt"
psql -h localhost -U sqlmesh -d sqlmesh -t -c "select table_name from information_schema.tables where table_schema = 'sqlmesh'"
 _snapshots
 _environments
 _auto_restatements
 _intervals
 _plan_dags
 _versions
* List the (virtual data) environments:

psql -h localhost -U sqlmesh -d sqlmesh -t -c "select * from _environments;"
* List the intervals:

psql -h localhost -U sqlmesh -d sqlmesh -t -c "select * from _intervals;"
 36c6a51271164c7687215c2d6927c252 | 1735489690863 | "db"."sqlmesh_example"."seed_model"        | 372700188  | 2185867172 | 1734998400000 | 1735430400000 | f      | f          | f            | f
 1aae0371a4664f51925bd0101984be12 | 1735489690871 | "db"."sqlmesh_example"."incremental_model" | 1463271556 | 1880815781 | 1577836800000 | 1735430400000 | f      | f          | f            | f
 15be8abdaf62493c8ebfab91f72a9099 | 1735489690881 | "db"."sqlmesh_example"."full_model"        | 3906121019 | 2278521865 | 1734998400000 | 1735430400000 | f      | f          | f            | f

Cleanup when a local PostgreSQL database stores the state

  • Delete the datasets in DuckDB, the logs and the cache directories:
make clean # equivalent of:
rm -rf db.db .cache logs

  • In order to clean the SQLMesh state in the local PostgreSQL database, there is a Bash script, namely tools/clean-pg-state.sh in this Git repository

    • That Bash script features a few sanity checks (for instance, that the psql command exists and works)
    • If the PostgreSQL parameters are different from the default ones, they can be altered in that tools/clean-pg-state.sh Bash script at its top
    • The remainded of this sub-section still details on how to clean the SQLMesh state from the local PostgreSQL database manually

  • Specify the list of the state-related tables in a Shell array variable:

table_list=($(psql -h localhost -U sqlmesh -d sqlmesh -t -c "select table_name from information_schema.tables where table_schema = 'sqlmesh'"))
  • Clean/drop the state-related tables in PostgreSQL:
for table in "${table_list[@]}"; do echo "Dropping ${table} table..."; psql -h localhost -U sqlmesh -d sqlmesh -c "drop table if exists ${table};"; echo "... ${table} table dropped"; done

Cleanup the changes when a local PostgreSQL database stores the state

grep "z" models/incremental_model.sql
    --'z' AS new_column, -- Added column
  • The project is now ready to start afresh, with no memory nor any change when compared to the Git repository

Local Airflow service