Start with SQLMesh (Experimental)
Note
The integration of SQLMesh is still in the experimental stage, and some features are not yet fully supported.
SQLMesh is an alternative to dbt. Unlike dbt, it records the state of each environment within the data warehouse. This feature simplifies the use of Recce.
Usage
Start the Recce
server with the follow command:
Start with specific config name
Tutorial: The Sushi Example Project
Here, the official example project from SQLMesh was used to demonstrate how to use recce.
-
Clone the SQLMesh repo
-
Prepare the python venv and install the SQLMesh.
-
Plan the prod environment
-
Modify the model
models/customers.sql
modeland apply this change to the dev environment output... SELECT DISTINCT o.customer_id::INT AS customer_id, -- customer_id uniquely identifies customers m.status, d.zip FROM sushi.orders AS o LEFT JOIN current_marketing AS m ON o.customer_id = m.customer_id LEFT JOIN raw.demographics AS d ON o.customer_id = d.customer_id + WHERE status is not NULL
New environment `dev` will be created from `prod` Summary of differences against `dev`: Models: ├── Directly Modified: │ └── sushi__dev.customers └── Indirectly Modified: └── sushi__dev.waiter_as_customer_by_day --- +++ @@ -31,3 +31,5 @@ ON o.customer_id = m.customer_id LEFT JOIN raw.demographics AS d ON o.customer_id = d.customer_id +WHERE + NOT status IS NULL Directly Modified: sushi__dev.customers (Breaking) └── Indirectly Modified Children: └── sushi__dev.waiter_as_customer_by_day (Indirect Breaking) Apply - Virtual Update [y/n]:
-
Use
outputsqlmesh table-diff
to check the change. -
Install Recce
-
Launch the recce server
You can see the lineage DAG diff
-
In the Query page, you can diff with ad-hoc query. Enter the following SQL script and click the Run Diff button.
Set the primary key as
status
and click the Run Diff buttonYou will see that there is only one record where
status=NULL
that differs. In the original version, there were two records withstatus=NULL
, but in the new version, there is no record withstatus=NULL
.
Supported Recce Features
-
Web UI: For SQLMesh integration, Recce currently supports lineage diff, schema diff, row count diff, and query diff
-
Command line interface: Not supported yet