sqlmesh-multi-engine-demo

let's have some fun with iceberg

This is a simple, loveable, and complete SQLMesh multi engine demo project with the goal of running multiple, realistic scenarios very fast.

This repo is paired with a blog post. TODO: add link

This is intentionally similar to what people may have experienced when I created this github repo at dbt Labs: here

Basic Setup

What you'll be using:

• SQLMesh: transformation framework
• BigQuery: data warehouse to run transforms against
• DuckDB: local development testing
• Postgres: Store SQLMesh metadata
• SQLGlot: Python macros that compile to SQL
• pytest: test Python macros
• TODO: add snowflake and iceberg catalogs

Setup your virtual environment for SQLMesh:

git clone https://github.com/TobikoData/sqlmesh-demos.git # clone the repo
cd sqlmesh-demos # go to the root directory
python -m venv venv # create virtual environment
source venv/bin/activate # activate it
pip install -r requirements.txt # install dependencies
source venv/bin/activate # reactivate virtual environment

Setup your BigQuery Service Account:

1. Create a service account following these instructions: here
2. Add permissions: BigQuery Data Editor, BigQuery User
3. Download the service account json file
4. Copy the contents of the service account file to your clipboard
5. Export the credentials as an environment variable in your terminal:

export GOOGLE_CREDENTIALS=<your-service-account-key-contents>

Setup your Postgres Database:

1. Sign up for a free Neon account: here
2. Create a new database named: sqlmesh_state_demo
3. Export your environment variables based on the newly created database connection string

# examples based on the image above
export SQLMESH_STATE_HOST=ep-purple-heart-a6mqlep8.us-west-2.aws.neon.tech
export SQLMESH_STATE_USERNAME=<your-neon-username>
export SQLMESH_STATE_PASSWORD=<your-password-blurred-in-the-image>

Verify SQLMesh can connect to BigQuery and Postgres:

sqlmesh info # print info about a SQLMesh project

# expected output
Models: 15
Macros: 1
Data warehouse connection succeeded
State backend connection succeeded
Test connection succeeded

Credits

Portions of this project are modifications based on work created and shared by dbt Labs and used according to terms described in the Apache License, Version 2.0. For the original work and its license, see: here