sqlmesh

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