Models guide

Prerequisites

Before adding a model, ensure that you have already created your project and that you are working in a dev environment.

Adding a model

To add a model:

Within your models folder, create a new file. For example, we might add new_model.sql to the quickstart project.

Within the file, define a model. For example:

MODEL (
    name sqlmesh_example.new_model,
    kind incremental_by_time_range (
        time_column (ds, '%Y-%m-%d'),
    ),
);

SELECT *
FROM sqlmesh_example.incremental_model
WHERE ds BETWEEN @start_ds and @end_ds

Note: The last line in this file is required if your model is incremental. Refer to model kinds for more information about the kinds of models you can create.

Editing an existing model

To edit an existing model:

Open the model file you wish to edit in your preferred editor and make a change.
To preview an example of what your change looks like without actually creating a table, use the sqlmesh evaluate command. Refer to evaluating a model below.
To materialize this change, use the sqlmesh plan command. Refer to previewing changes using the plan command below.

Evaluating a model

The evaluate command will run a query against your database or engine and return a dataframe. It is used to test or iterate on models without database side effects and at minimal cost because SQLMesh isn't materializing any data.

To evaluate a model:

Run the evaluate command using either the CLI or Notebook. For example, running the evaluate command on incremental_model from the quickstart project:

$ sqlmesh evaluate sqlmesh_example.incremental_model --start=2020-01-07 --end=2020-01-07

id  item_id          ds
0   7        1  2020-01-07

When you run the evaluate command, SQLMesh detects the changes made to the model, executes the model as a query using the options passed to evaluate, and shows the output returned by the model query.

Previewing changes using the `plan` command

When SQLMesh runs the plan command on your environment, it will show you whether any downstream models are impacted by your changes. If so, SQLMesh will prompt you to classify the changes as Breaking or Non-Breaking before applying the changes.

To preview changes using plan:

Enter the sqlmesh plan <environment name> command.
Enter 1 to classify the changes as Breaking, or enter 2 to classify the changes as Non-Breaking. In this example, the changes are classified as Non-Breaking:

$ sqlmesh plan dev
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
Summary of differences against `dev`:
├── Directly Modified:
│   └── sqlmesh_example.incremental_model
└── Indirectly Modified:
    └── sqlmesh_example.full_model
---
+++
@@ -1,6 +1,7 @@
SELECT
id,
item_id,
+  1 AS new_column,
ds
FROM (VALUES
(1, 1, '2020-01-01'),
Directly Modified: sqlmesh_example.incremental_model
└── Indirectly Modified Children:
    └── sqlmesh_example.full_model
[1] [Breaking] Backfill sqlmesh_example.incremental_model and indirectly modified children
[2] [Non-breaking] Backfill sqlmesh_example.incremental_model but not indirectly modified children: 2
Models needing backfill (missing dates):
└── sqlmesh_example.incremental_model: (2020-01-01, 2023-02-17)
Enter the backfill start date (eg. '1 year', '2020-01-01') or blank for the beginning of history:
Enter the backfill end date (eg. '1 month ago', '2020-01-01') or blank to backfill up until now:
Apply - Backfill Tables [y/n]: y

sqlmesh_example__dev.incremental_model ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:00

All model batches have been executed successfully

Virtually Updating 'dev' ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully

For more information, refer to plans.

Reverting a change to a model

Before trying to revert a change, ensure that you have already made a change and that you have run the sqlmesh plan command.

To revert your change:

Open the model file you wish to edit in your preferred editor, and undo a change you made earlier. For this example, we'll remove the column we added in the quickstart example.
Run sqlmesh plan and apply your changes. Enter y to run a Virtual Update.

$ sqlmesh plan dev
======================================================================
Successfully Ran 1 tests against duckdb
----------------------------------------------------------------------
Summary of differences against `dev`:
├── Directly Modified:
│   └── sqlmesh_example.incremental_model
└── Indirectly Modified:
    └── sqlmesh_example.full_model
---
+++
@@ -1,7 +1,6 @@

SELECT
id,
item_id,
-  1 AS new_column,
ds
FROM (VALUES
    (1, 1, '2020-01-01'),
Directly Modified: sqlmesh_example.incremental_model (Non-breaking)
└── Indirectly Modified Children:
    └── sqlmesh_example.full_model
Apply - Virtual Update [y/n]: y

Virtual Update executed successfully

Virtual Update

Reverting to a previous model version is a quick operation since no additional work is being done. For more information, refer to plan application and Virtual Update.

Note: The SQLMesh janitor runs periodically and automatically to clean up SQLMesh artifacts no longer being used, and determines the time-to-live (TTL) for tables (how much time can pass before reverting is no longer possible).

Validating changes to a model

Automatic model validation

SQLMesh automatically validates your models in order to ensure the quality and accuracy of your data. This is done via the following:

Running unit tests by default when you execute the plan command. This ensures all changes to applied to any environment are logically validated. Refer to testing for more information.
Running audits whenever data is loaded to a table (either for backfill or loading on a cadence). This way you know all data present in any table has passed all defined audits. Refer to auditing for more information.

SQLMesh also provides automatic validation via CI/CD by automatically creating a preview environment.

Manual model validation

To manually validate your models, you can perform one or more of the following tasks:

Deleting a model

Before deleting a model, ensure that you have already run sqlmesh plan.

To delete a model:

Within your models directory, delete the file containing the model and any associated tests in the tests directory. For this example, we'll delete the models/full_model.sql and tests/test_full_model.yaml files from our quickstart project.

Run the sqlmesh plan <environment> command, specifying the environment to which you want to apply the change. In this example, we apply the change to our development environment dev:

```bash linenums="1"
$ sqlmesh plan dev
======================================================================
Successfully Ran 0 tests against duckdb
----------------------------------------------------------------------
Summary of differences against `dev`:
└── Removed Models:
    └── sqlmesh_example.full_model
Apply - Virtual Update [y/n]: y
Virtually Updating 'dev' ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully


Virtual Update executed successfully
```

Note: If you have other files that reference the model you wish to delete (such as tests), an error message will note the file(s) containing the reference. You must also delete these files to apply the change.

Plan and apply your changes to production, and enter y for the Virtual Update. By default, the sqlmesh plan command targets your production environment:

```
$ sqlmesh plan
======================================================================
Successfully Ran 0 tests against duckdb
----------------------------------------------------------------------
Summary of differences against `prod`:
└── Removed Models:
    └── sqlmesh_example.full_model
Apply - Virtual Update [y/n]: y
Virtually Updating 'prod' ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 0:00:00

The target environment has been updated successfully


Virtual Update executed successfully
```

Verify that the full_model.sql model was removed from the output.

Viewing the DAG of a project's models

Before generating a DAG, ensure that you have already installed the graphviz package.

To install the package with pip, enter the following command:

pip install graphviz

Alternatively, enter the following command to install graphviz with apt-get:

sudo apt-get install graphviz

To view the DAG, enter the following command:

sqlmesh dag

The generated files (.gv and .jpeg formats) will be placed at the root of your project folder.