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