What is the --explain Flag?

The --explain flag is a game-changer for data engineers who want more visibility and control over their SQLMesh deployments. When you add this flag to your plan command, SQLMesh provides a comprehensive breakdown of:

  • Which models will be modified (both directly and indirectly)
  • What physical tables and views will be created or updated
  • Which models need backfilling, and their date ranges
  • The exact sequence of operations that will occur
  • How the virtual layer will be updated

Think of it as a "dry run" for your plan—you get all the insights without any of the consequences.

How to Use --explain

Using the --explain flag is easy. Simply add it to your existing sqlmesh plan command along with any other flags you normally use:

sqlmesh plan my_env --explain

You can combine it with other plan options as needed:

sqlmesh plan my_env --explain --skip-backfill

sqlmesh plan production --explain --start 2025-01-01

Understanding the Output

Let's walk through an example to see what information --explain provides:

$ sqlmesh plan my_env --explain

=================================================================

Successfully Ran 3 tests against duckdb

-----------------------------------------------------------------

‍

New environment `my_env` will be created from `prod`

‍

Differences from the `prod` environment:

‍

Models:

├── Directly Modified:

│   └── sushi__my_env.waiter_revenue_by_day

└── Indirectly Modified:

    └── sushi__my_env.top_waiters

‍

Directly Modified: sushi__my_env.waiter_revenue_by_day (Breaking)

└── Indirectly Modified Children:

    └── sushi__my_env.top_waiters (Indirect Breaking)

Models needing backfill:

├── sushi__my_env.top_waiters: [recreate view]

└── sushi__my_env.waiter_revenue_by_day: [2025-05-28 - 2025-05-29]

Explained plan

├── Validate SQL and create physical layer tables and views if they do not exist

│   ├── sushi__my_env.waiter_revenue_by_day -> duckdb.sqlmesh__sushi.sushi__waiter_revenue_by_day__190497413

│   │   ├── Dry run model query without inserting results

│   │   └── Create table if it doesn't exist

│   └── sushi__my_env.top_waiters -> duckdb.sqlmesh__sushi.sushi__top_waiters__3767248144

│       ├── Dry run model query without inserting results

│       └── Create view if it doesn't exist

├── Backfill models by running their queries and run standalone audits

│   ├── sushi__my_env.waiter_revenue_by_day -> duckdb.sqlmesh__sushi.sushi__waiter_revenue_by_day__190497413

│   │   ├── Fully refresh table

│   │   └── Run 'number_of_rows' audit

│   └── sushi__my_env.top_waiters -> duckdb.sqlmesh__sushi.sushi__top_waiters__3767248144

│       ├── Recreate view

│       └── Run 'unique_values' audit

└── Update the virtual layer for environment 'my_env'

    └── Create or update views in the virtual layer to point at new physical tables and views

        ├── sushi__my_env.top_waiters -> duckdb.sqlmesh__sushi.sushi__top_waiters__3767248144

        └── sushi__my_env.waiter_revenue_by_day -> duckdb.sqlmesh__sushi.sushi__waiter_revenue_by_day__190497413

The output is organized into several sections:

1. Environment Information

Shows which environment will be created or modified and its parent environment.

2. Model Changes

Clearly distinguishes between:

  • Directly Modified Models: Models you explicitly changed
  • Indirectly Modified Models: Downstream models affected by your changes
  • Breaking Changes: Marked clearly to alert you to potential impacts

3. Backfill Requirements

Lists which models need backfilling and specifies:

  • Date ranges for incremental models
  • View recreation for views
  • Full refresh requirements for tables

4. Execution Plan

The most detailed section, showing the exact sequence of operations:

  • Physical Layer Operations: Table and view creation with their fingerprinted names
  • Backfill Operations: Including any signals and audits that will run
  • Schema Alterations: That will be applied to tables in production for forward-only changes
  • Virtual Layer Updates: How the environment's views will be updated to point to the new physical objects

When to Use --explain

The --explain flag is particularly valuable in these scenarios:

1. Production Deployments

Before applying changes to production, use --explain to verify exactly what will happen. This helps prevent surprises and allows you to communicate changes to stakeholders.

2. Debugging Plan Issues

If a plan is taking longer than expected or behaving unexpectedly, --explain helps you understand the scope of work without waiting for execution.

3. Learning and Documentation

New team members can use --explain to understand how SQLMesh operates without making actual changes. It's also great for creating documentation about planned changes.

Saving --explain Output

Here are ways that you can save your explained output to the most out of --explain:

1. Maintain a Plan History

# Append instead of overwrite (using >>)

sqlmesh plan --explain >> plan_history.txt

This creates an audit trail of all planned changes over time, which can be helpful for debugging and compliance if additional documentation is needed.

2. Use Timestamped Filenames

# Save with timestamp in filename

sqlmesh plan --explain > plan_$(date +%Y%m%d_%H%M%S).txt

‍

# For PowerShell users:

sqlmesh plan --explain > plan_$(Get-Date -Format "yyyyMMdd_HHmmss").txt

This creates files like plan_20250124_143052.txt, making it easy to track when each plan was generated.

3. Validate Successful Saves

Confirm the plan explanation was saved successfully:

# Save only if successful (bash/Linux/macOS)

sqlmesh plan --explain > plan_explanation.txt && echo "Plan saved successfully"

‍

# For PowerShell users:

sqlmesh plan --explain > plan_explanation.txt; if ($?) { echo "Plan saved successfully" }

‍

Conclusion

The --explain flag brings additional transparency to your SQLMesh operations. By providing a detailed preview of plan execution, it empowers data teams to work with greater confidence and control. 

We're committed to making this feature even better, so please try it out and share your feedback with the Tobiko team. Your input will help shape future improvements to make SQLMesh even more powerful and user-friendly.

{{cta-talk}}

{{banner-slack}}