SQLMesh — Scalable Data Transformation Framework for SQL

Introduction

SQLMesh is a data transformation framework that brings software engineering best practices to data pipelines. It uses column-level lineage and incremental processing to avoid unnecessary computation, and its virtual data environments let teams preview changes without duplicating tables.

What SQLMesh Does

• Transforms data using SQL or Python models with automatic dependency resolution
• Builds only what changed using column-level lineage and incremental-by-time-range strategies
• Creates virtual data environments that preview pipeline changes without copying data
• Validates data with built-in audits that run automatically after each transformation
• Maintains backwards compatibility with existing dbt projects for easy migration

Architecture Overview

SQLMesh parses SQL models to build a directed acyclic graph of dependencies. Before execution, it compares the current state with the target state and generates a minimal plan of changes. Virtual environments use database views to point at production tables when models have not changed, avoiding data duplication. The scheduler supports serial and parallel execution with checkpointing.

Self-Hosting & Configuration

• Install via pip and initialize a project with sqlmesh init
• Define models as SQL files with a MODEL block specifying name, grain, and incremental strategy
• Configure warehouse connections in config.yaml for Snowflake, BigQuery, Databricks, PostgreSQL, DuckDB, or others
• Use sqlmesh plan to preview changes and sqlmesh run to execute transformations
• Integrate with CI/CD by running sqlmesh plan --auto-apply in pipelines

Key Features

• Virtual data environments that test changes without duplicating tables or data
• Column-level lineage for precise impact analysis and minimal recomputation
• Built-in data audits and tests that run as part of every pipeline execution
• Incremental-by-time-range and incremental-by-unique-key strategies for efficient processing
• dbt compatibility layer for migrating existing dbt projects without rewriting models

Comparison with Similar Tools

• dbt — The standard SQL transformation tool; SQLMesh adds virtual environments and column-level lineage for efficiency
• Dagster — Workflow orchestrator that can run dbt; SQLMesh is a transformation engine with its own scheduler
• Dataform — Google-acquired SQL tool tied to BigQuery; SQLMesh supports multiple warehouses
• Cube — Semantic layer focused on serving metrics; SQLMesh focuses on transformation and materialization
• Great Expectations — Data validation library; SQLMesh has audits built in alongside transformations

FAQ

Q: Can I use SQLMesh with my existing dbt project? A: Yes. SQLMesh can read dbt projects and run them with its own engine, or you can migrate models incrementally.

Q: What databases does SQLMesh support? A: Snowflake, BigQuery, Databricks, Redshift, PostgreSQL, MySQL, DuckDB, Spark, Trino, and ClickHouse.

Q: How do virtual environments avoid data duplication? A: They use database views that point to existing production tables for unchanged models, only materializing tables for models that actually changed.

Q: Is SQLMesh open source? A: Yes. SQLMesh is released under the Apache 2.0 license.

Sources

• https://github.com/SQLMesh/sqlmesh
• https://sqlmesh.readthedocs.io