Testing
Testing allows you to protect your project from regression by continuously verifying the output of each model matches your expectations. Unlike audits, tests are executed either on demand (for example, as part of a CI/CD job) or every time a new plan is created.
Similar to unit testing in software development, SQLMesh evaluates the model's logic against predefined inputs and then compares the output to expected outcomes provided as part of each test.
A comprehensive suite of tests can empower data practitioners to work with confidence, as it allows them to ensure models behave as expected after changes have been applied to them.
Creating tests
Test suites are defined using YAML format within .yaml
files in the tests/
folder of your SQLMesh project. Each test within a suite file contains the following attributes:
- The unique name of a test
- The name of the model targeted by this test
- Test inputs, which are defined per external table or upstream model referenced by the target model. Each test input consists of the following:
- The name of an upstream model or external table
- The list of rows defined as a mapping from a column name to a value associated with it
- Expected outputs, which are defined as follows:
- The list of rows that are expected to be returned by the model's query defined as a mapping from a column name to a value associated with it
- [Optional] The list of expected rows per each individual Common Table Expression (CTE) defined in the model's query
- [Optional] The dictionary of values for macro variables that will be set during model testing
The YAML format is defined as follows:
Example
In this example, we'll use the sqlmesh_example.example_full_model
model, which is provided as part of the sqlmesh init
command and defined as follows:
Notice how the query of the model definition above references one upstream model: sqlmesh_example.example_incremental_model
.
The test definition for this model may look like following:
Testing CTEs
Individual CTEs within the model's query can also be tested. Let's slightly modify the query of the model used in the previous example:
Below is the example of a test that verifies individual rows returned by the filtered_orders_cte
CTE before aggregation takes place:
Running tests
Automatic testing with plan
Tests run automatically every time a new plan is created.
Manual testing with the CLI
You can execute tests on demand using the sqlmesh test
command as follows:
$ sqlmesh test
.
----------------------------------------------------------------------
Ran 1 test in 0.005s
OK
The command returns a non-zero exit code if there are any failures, and reports them in the standard error stream:
$ sqlmesh test
F
======================================================================
FAIL: test_example_full_model (/Users/izeigerman/github/tmp/tests/test_suite.yaml:1)
----------------------------------------------------------------------
AssertionError: Data differs
- {'item_id': 1, 'num_orders': 3}
? ^
+ {'item_id': 1, 'num_orders': 2}
? ^
----------------------------------------------------------------------
Ran 1 test in 0.008s
FAILED (failures=1)
Testing for specific models
To run a specific model test, pass in the suite file name followed by ::
and the name of the test:
You can also run tests that match a pattern or substring using a glob pathname expansion syntax: