SQLMesh: Next-generation data transformation framework

Warnings

• breaking Frequent updates to the underlying `sqlglot` library (as seen in chore commits like 'bump sqlglot to v30.4.2') can introduce subtle breaking changes or altered SQL parsing/transpilation behavior. While SQLMesh aims to abstract this, it's crucial to review release notes for `sqlglot` when upgrading SQLMesh.
  Fix: Always check the SQLMesh and SQLGlot changelogs before upgrading. Test your SQLMesh project thoroughly after an upgrade, especially if it involves `sqlglot` bumps, to catch any parsing or transpilation regressions.
• gotcha SQLMesh previously had compatibility issues with `pandas` version 3, requiring users to explicitly exclude it. While this might be resolved in future versions, `pandas` compatibility can be a recurring issue with rapidly evolving libraries.
  Fix: Ensure your `pandas` version is compatible with your SQLMesh installation. Check the official SQLMesh documentation or GitHub issues for known `pandas` compatibility concerns and required version ranges.
• gotcha SQLMesh is inherently stateful. While it can use local databases like DuckDB for development state, production deployments should use robust OLTP databases (e.g., PostgreSQL, MySQL) to store state for ACID transactions, performance, and reliability. Using a local file-based database for shared production state can lead to data integrity issues.
  Fix: Configure a persistent and reliable OLTP database (e.g., PostgreSQL) for SQLMesh state in production environments by specifying `state_connection` in `config.yaml`.
• gotcha The `sqlmesh plan` command defaults to targeting the `prod` environment. Accidentally running `sqlmesh plan` without specifying a development environment can inadvertently target or interfere with your production data if not careful.
  Fix: Always explicitly specify your development environment when running `sqlmesh plan` (e.g., `sqlmesh plan dev_myuser`). Consider changing the default environment in your project's `config.yaml` if all users work in isolated dev environments.
• gotcha SQLMesh infers schema information from your models. When selecting from external data sources (tables not defined within your SQLMesh project), it may issue warnings about missing schema knowledge, impacting lineage and optimization capabilities.
  Fix: Use `sqlmesh create_external_models` or manually define external models (e.g., in `schema.yml`) to provide SQLMesh with schema information for external data sources. This enables full column-level lineage and better optimization.
• gotcha While SQLMesh automatically infers column names and types, explicit type casting and column definition in SQL models (e.g., `SELECT o.customer_id::TEXT`) is a best practice. Relying solely on `SELECT *` can lead to unexpected schema changes or hinder SQLMesh's ability to precisely detect breaking changes, potentially causing unnecessary recomputations or data issues.
  Fix: Explicitly define column names and types in your SQL models, particularly in the final `SELECT` statement. Avoid `SELECT *` in production models where schema stability is critical. Manually specify `columns` in the model definition if schema inference is problematic.

Install

• pip install sqlmesh Base Installation
• pip install "sqlmesh[web]" With UI (Web)
• pip install "sqlmesh[duckdb]" With DuckDB connector

Imports

• model

  from sqlmesh import model

• ExecutionContext

  from sqlmesh import ExecutionContext

• Config

  from sqlmesh.core.config import Config

• exp

  from sqlglot import exp

Quickstart

The most common way to get started with SQLMesh is via its CLI. This quickstart demonstrates how to initialize a new project using DuckDB as the local engine and then run your first plan. The `sqlmesh init duckdb` command scaffolds a project, and `sqlmesh plan` shows the proposed changes. For a fully runnable example demonstrating project setup and execution, refer to the official documentation.

import os
# Ensure SQLMesh is installed along with the DuckDB extra
# pip install "sqlmesh[duckdb]"

# Initialize a new SQLMesh project with DuckDB as the engine
# This creates a 'sqlmesh_example' directory with project files
# and sets up a DuckDB connection.
# os.system("sqlmesh init duckdb --path sqlmesh_example")

# Navigate into the project directory (conceptually, in a real script you'd use os.chdir or similar)
# In a terminal, you would run:
# cd sqlmesh_example
# sqlmesh plan

# Simulate a SQL model file: models/full_model.sql
# -- models/full_model.sql
# -- MODEL (
# --   name sqlmesh_example.full_model,
# --   kind FULL
# -- );
# -- SELECT
# --   1 AS id,
# --   'A' AS value;

# To run the plan and apply changes (requires a project initialized by 'sqlmesh init'):
# print("\n--- Running sqlmesh plan ---")
# if os.path.exists('sqlmesh_example'):
#     os.chdir('sqlmesh_example')
#     os.system('sqlmesh plan --no-gaps --auto-apply')
#     print("\n--- Querying the created model (requires DuckDB CLI or connector) ---")
#     # You can query the model using duckdb CLI if installed:
#     # os.system('duckdb sqlmesh.db "SELECT * FROM sqlmesh_example.full_model;"')
# else:
#     print("Please run 'sqlmesh init duckdb' in your terminal first to create the example project.")

print("To get started, run 'sqlmesh init duckdb' in your terminal, then 'cd sqlmesh_example' and 'sqlmesh plan'.")
print("This will create an example project and show you the execution plan.")