sqlmesh
SQLMesh数据转换模式,具有列级谱系和虚拟环境。在需要高级功能(如自动DAG推断和高效增量处理)构建数据管道时使用。
📖 中文导读:SQLMesh数据转换模式,具有列级谱系和虚拟环境。在需要高级功能(如自动DAG推断和高效增量处理)构建数据管道时使用。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。
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.
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