Use project tests
Project tests are custom data tests that check for a passing condition, such as checking that the given query doesn’t return any unexpected rows. They are based on dbt singular tests, but, as a data user, you don’t need to know dbt to use project tests in Prophecy. Prophecy makes it easy set up, configure, and schedule project tests.
Project tests perform a SQL query that checks the executed result of your project against a passing condition to see if it's successful. This is done by checking whether a particular table, generated by combining one or more database objects from your project, meets a certain condition.
There could be many tests within a project. Each test is checking a table created by your choice of input models, data sources, and transformation Gems.
By default, and most often, the condition for a project test is simply a check that the given query does not return any failing rows.
Set up a test
Since project tests share the same canvas as models, you can set them up the same way you would develop and run a model.
Develop a test
To develop a project test, start by opening a project:
Under the Project Browser, click Add Test.
Enter your Test Name. Project tests are saved to a tests SQL file in the Git directory by default.
Click OK to create your new test.
Create your test model by dragging and dropping Gems to your visual canvas. Connect them to your Data Test. You can also write your test on the Code view. You can use as many models and other database objects as you’d like in your test.
Run a test
After you’ve developed your project test and executed all of your models, you can run the test.
Click the Play button on either the canvas or an individual Gem to execute your test. The table input to the data test Gem is what’s tested.
Run a whole test. Clicking the canvas Play button executes the complete test and shows the interim data after the Data Test Gem. Additionally, it displays the final test status, succeeded, warning, or failed, in a summary.
Run a partial test. Clicking the Gem Play button executes the test SQL up to a particular Gem, and displays that Gem’s data on the output. This option doesn’t execute the data test.
Click See Run Details to view the test Summary. Depending on the outcome of the test, the icon displays a different color.
You can click to expand the test logs in the Summary to view the dbt logs.
In addition to the previous relational integrity test, you can create tests to check that the total payment amount from customers is positive or simply check that all of your tables are still accessible. You can test any series of transformation Gems, because a project test simply asserts that the resulting table meets a certain criteria.
Fix a failed test
If your project test fails, check your test model for any changes.
Click the test output to see your failed rows.
Some examples of test failure causes include:
- Happy path: There is now a row in the table → you have detected the thing you were trying to detect!
- The table no longer exists since one or more input data sources were deleted or inaccessible
- The Failure Calculation function is invalid
- The Error If and Warn If conditions are invalid
Make sure you have write permission to create a new table in your data warehouse, otherwise you may run into errors while trying to run your own tests.
By default, project tests are configured to fail if the table has one or more rows. You can also modify the passing condition of your test through advanced options.
Configure a test
You can configure your own passing condition of your project test to help decide which cases to focus on.
You might need to pay more attention as the number of rows in a table increases. For example, you might have a customer churn use case where many customers have a few comments (represented by rows in a table), but you need to change course when the number of customer comments exceeds a threshold of five comments. Configuring your test can help you identify and take action, such as assigning more resources to assist that customer.
To configure a test, follow these steps:
Click on the test to open the test details.
Enter conditional values for the following options:
- (A) Failure Calculation - Sets the failure condition used to run against the test result. You can use the
count()function on a column or multiple columns. - (B) Limit- Sets the maximum number of failures returned by a test query. You can set the limit to save resources and time by having the test stop its query as soon as it encounters a certain number of failed rows.
- (C) Severity - Determines whether the failure of the test returns an error or warning. The severity operates from the highest priority selection, error, to the lowest, warning. So if you select error, then the test first checks for errors. If it doesn’t find any, then it then checks for warnings. If you select warning, then the test only checks for warnings. If you don’t select a severity, then error is chosen by default.
- (D) Error If and (E) Warning If - Sets the number of failed rows to determine a failed test. Depending on the selected severity, your test only returns a failed test for error checks. Warning won’t return a failed test.
- (A) Failure Calculation - Sets the failure condition used to run against the test result. You can use the
Click Save.
Schedule a test
When scheduling your project, you can opt in to run a test along with the project or model. Scheduling a test allows you to ensure on a daily basis that your data is correct. You can only schedule a test with a project or model. You cannot schedule one individually.
To Schedule your project to run with tests, follow these steps:
Under the Project Browser, click Add Job.
Drag a Model Gem to your visual canvas.
Click the Model to open the Model Properties.
Select the database object you want to run the test on. You can schedule the entire project or just a single Model:
- Run entire Project
- Run a SQL Model
Select Run tests.
Check that your Project, model, and Fabric are correct.
Click Save. The Job runs automatically. You can see the Job status by clicking Detail.