sqlmesh

name	sqlmesh
description	SQLMesh patterns for data transformation with column-level lineage and virtual environments. Use when building data pipelines that need advanced features like automatic DAG inference and efficient incremental processing.
user-invocable	false

SQLMesh Skill

This skill provides SQLMesh patterns for data transformation.

Project Structure

sqlmesh_project/
├── config.yaml
├── models/
│   ├── staging/
│   │   └── stg_customers.sql
│   └── marts/
│       └── dim_customers.sql
├── macros/
├── seeds/
├── audits/
└── tests/

Model Definition

-- models/staging/stg_customers.sql
MODEL (
    name staging.stg_customers,
    kind INCREMENTAL_BY_TIME_RANGE (
        time_column created_at
    ),
    cron '@daily'
);

SELECT
    id AS customer_id,
    LOWER(email) AS email,
    created_at
FROM raw.customers
WHERE created_at BETWEEN @start_ds AND @end_ds

Model Kinds

Kind	Use Case
FULL	Complete refresh each run
INCREMENTAL_BY_TIME_RANGE	Time-based incremental
INCREMENTAL_BY_UNIQUE_KEY	Key-based merge
VIEW	Virtual table
SEED	Static CSV data

Virtual Environments

# Create a virtual environment for testing
sqlmesh plan dev

# Apply to production
sqlmesh plan prod

Audits

-- audits/no_nulls.sql
AUDIT (
    name assert_no_null_customer_id,
    model staging.stg_customers
);

SELECT * FROM staging.stg_customers
WHERE customer_id IS NULL

Best Practices

• Use column-level lineage for impact analysis
• Leverage virtual environments for testing
• Define audits for data quality
• Use incremental models for efficiency