Skip to content

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

pip install -U recce

Start the Recce server with the follow command:

recce server --sqlmesh --sqlmesh-envs prod:dev

Start with specific config name 

recce server --sqlmesh --sqlmesh-envs prod:dev --sqlmesh-config local_config

Tutorial: The Sushi Example Project

Here, the official example project from SQLMesh was used to demonstrate how to use recce.

  1. Clone the SQLMesh repo 

    git clone git@github.com:TobikoData/sqlmesh.git
cd sqlmesh/examples/sushi

  2. Prepare the python venv and install the SQLMesh.

    python -m venv venv
source ./venv/bin/activate
pip install sqlmesh

  3. Plan the prod environment

    sqlmesh --config local_config plan

  4. Modify the model models/customers.sql model

      ...
  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
    and apply this change to the dev environment 
    sqlmesh --config local_config plan dev
    output 
    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]:

  5. Use sqlmesh table-diff to check the change. 

    sqlmesh --config local_config table_diff prod:dev sushi.customers
    output 
    Schema Diff Between 'PROD' and 'DEV' environments for model 'sushi.customers':
└── Schemas match


Row Counts:
├──  COMMON: 55 rows
├──  PROD ONLY: 18 rows
└──  DEV ONLY: 0 rows

COMMON ROWS column comparison stats:
        pct_match
status      100.0
zip         100.0

  6. Install Recce 

    pip install recce

  7. Launch the recce server 

    recce server --sqlmesh --sqlmesh-envs prod:dev --sqlmesh-config local_config

    You can see the lineage DAG diff

    Lineage Diff

  8. In the Query page, you can diff with ad-hoc query. Enter the following SQL script and click the Run Diff button.

    select 
  status,
  count(*) as c
from sushi.customers
group by status
order by status

    Set the primary key as status and click the Run Diff button

    Query Diff Result

    You will see that there is only one record where status=NULL that differs. In the original version, there were two records with status=NULL, but in the new version, there is no record with status=NULL.

Supported Recce Features