Workflow:TobikoData Sqlmesh Plan and apply deployment
| Knowledge Sources | |
|---|---|
| Domains | Data_Engineering, Deployment, SQL_Transformation |
| Last Updated | 2026-02-07 21:00 GMT |
Overview
End-to-end process for safely deploying data transformation changes to a target environment using SQLMesh's plan/apply workflow, analogous to Terraform's infrastructure-as-code deployment model.
Description
This workflow covers the complete deployment lifecycle for SQL model changes in SQLMesh. It begins with loading and validating all project models, proceeds through change detection and impact analysis via the plan system, and concludes with backfilling affected data and updating the virtual layer. The plan/apply pattern ensures that all changes are previewed before execution, providing a safety net against unintended data modifications.
Key outcomes:
- A detailed plan showing exactly which models changed and what data needs reprocessing
- Automatic change categorization (breaking vs non-breaking)
- Controlled backfill execution with progress tracking
- Virtual layer update to expose new data to consumers
Usage
Execute this workflow when you have modified SQL model definitions, added new models, or changed model configurations and need to deploy these changes to any environment (development or production). This is the primary deployment mechanism in SQLMesh and should be used for every change cycle.
Execution Steps
Step 1: Project Loading and Validation
Initialize the SQLMesh context which discovers and loads all model definitions from the project directory. The loader reads SQL files, Python models, seed files, and configuration to build an in-memory representation of the entire project DAG. Models are parsed, validated for syntax correctness, and their dependencies are resolved.
Key considerations:
- All model files in the configured paths are automatically discovered
- Circular dependencies are detected and reported as errors
- External model references are resolved against the state store
Step 2: Unit Test Execution
Run all unit tests defined in the project's test directory to validate model logic before planning. Tests execute model queries against mock data in an isolated environment, comparing actual output to expected results defined in YAML test files.
Key considerations:
- Tests can be skipped with the skip-tests flag for faster iteration
- Failed tests block plan creation by default
- Tests run against a local DuckDB engine regardless of the target engine
Step 3: Plan Creation and Change Detection
Compare the current model definitions against the target environment's state to produce a plan. The planner identifies added, removed, and modified models by computing fingerprints of each model's query logic and configuration. Changes are automatically categorized as breaking (requiring full backfill) or non-breaking (forward-only compatible).
Key considerations:
- Fingerprints capture both the model's direct logic and its upstream dependencies
- Breaking changes propagate downstream through the dependency graph
- Users can override automatic categorization when prompted
Step 4: Backfill Strategy Selection
Determine the time range and scope of data that needs reprocessing. For incremental models, only the affected time intervals are reprocessed. For full-refresh models, the entire table is rebuilt. Users can specify custom date ranges to limit backfill scope.
Key considerations:
- Incremental models only reprocess missing or invalidated intervals
- Full models are always completely rebuilt on any change
- Date ranges can be constrained to reduce compute costs during development
Step 5: Plan Confirmation and Application
Present the plan summary to the user for review and confirmation. The summary shows all affected models, the type of change, and the backfill scope. Upon confirmation, the plan evaluator begins executing the backfill operations in dependency order.
Key considerations:
- Models are processed in topological order respecting the DAG
- Parallelism is applied where independent branches allow
- Progress is reported in real-time via console or web UI
Step 6: Virtual Layer Update
After all backfills complete successfully, update the virtual layer views to point to the newly created physical tables. This atomic operation switches all consumer-facing views simultaneously, ensuring consistency across the environment.
Key considerations:
- The virtual layer swap is atomic per environment
- Previous physical tables are retained for rollback capability
- The janitor process eventually cleans up expired physical tables