Skip to main content
Table tests are reusable SQL queries that validate your data quality. You write a test definition once, then apply it to any table or model in your project to check for data problems. Use test definitions to catch data issues before they affect your analysis or reports. For example, you might create a test to verify that customer IDs are unique, that required fields aren’t missing, or that two related tables have matching values. When you run these tests, you’ll be able to identify any problems in your data.
Table tests are based on dbt generic data tests behind the scenes. When you add a test definition to a table, Prophecy adds the test as a property of the corresponding dbt model.
SQL fabrics configured with BigQuery and a CMEK are not compatible with data tests.

​
Default test definitions

Prophecy includes a set of default test definitions that you can use to get started.
Test definitionDescription
UniqueValidates that each value within a column is unique.
Not nullValidates that a column contains no null values.
Accepted valuesValidates that column values only include values from a defined set.
RelationshipsValidates referential integrity by ensuring that each value in a column exists as a corresponding value in another column or model.

​
How are test definitions defined?

Test definitions use SQL queries to check your data. The query looks for problems in your data. If the query finds any rows (problems), the test fails. If the query returns no rows (no problems found), the test passes.

​
Understanding test queries

Think of a test query as a question you ask about your data: “Are there any rows that violate this rule?” If the answer is “yes” (rows are returned), the test fails. If the answer is “no” (no rows returned), the test passes. For example:
  • The unique test asks: “Are there any duplicate values in this column?” If duplicates exist, the query returns those duplicate rows and the test fails.
  • The relationships test asks: “Are there any values in this column that don’t exist in the related table?” If mismatched values exist, the query returns those rows and the test fails.
To create your own test definitions, you need to know how to write SQL queries.

​
Example: Comparing row counts

This example equal_rowcount test checks if two models have the same number of rows. It has the following parameters and definition:
ParameterType
modeltable
compare_modeltable
with a as (
    select
      count(*) as count_a
    from {{ model }}
),
b as (
    select
      count(*) as count_b
    from {{ compare_model }}
),
final as (
    select
        count_a,
        count_b,
        abs(count_a - count_b) as diff_count
    from a
    cross join b
)
select * from final
where diff_count > 0
How it works:
  1. The first (a) counts rows in your model.
  2. The second CTE (b) counts rows in the comparison model.
  3. The final CTE calculates the difference between the two counts.
  4. The final SELECT returns rows only when the difference is greater than 0.
If the row counts match, the query returns no rows and the test passes. If they differ, the query returns a row showing the difference and the test fails.

​
Build and run custom tests

In the following sections, we’ll build a test called not_constant to validate that a column does not have the same value in all rows. Follow the example to learn how to build a test definition and run the test.

​
1. Add a test definition

To add a test definition, follow these steps:
  1. In the left sidebar, click + Add Entity.
  2. Hover the Tests option and select Test definitions.
  3. Assign a name to your test definition, such as not_constant.
  4. Keep the default path tests/generic. This is the directory in the project Git repository where Prophecy will store the test definition.
  5. Click Create. The test definition page opens.
You can also create a new data test directly from the Data Tests tab of a table or model gem.

​
2. Define the test query

On the test definition page, configure the following.
  1. Under Description, add a summary of the test.
  2. Under Parameters, add the parameters for the test.
    • All tests require the default model parameter of type table.
    • The not_constant test requires a column_name parameter of type column (the column to check).
  3. Under Definition, add the SQL query for the test. 
    select
   count(distinct {{ column_name }}) as filler_column
from {{ model }}
having count(distinct {{ column_name }}) = 1
    Create a new model test definition

​
3. Assign the test definition

After you’ve created a test definition, you can assign it to a table or model.
  1. Open the table or model that you want to run the test on.
  2. Click the Data Tests tab.
  3. Click + New Test.
  4. Under Data Test Type, select the test definition you want to add to the gem.
  5. Fill in the parameters for the test. Prophecy automatically sets the value of the model parameter to the current table.
  6. Click Create Test.
If necessary, you can add additional tests to the same table or model.
If changes are made to the columns or schemas used in your data test, then Prophecy will delete the data test. For example, if you run into a data mismatch error on the Schema tab of your target model or update the schema, then your data test will be affected.
If you want to change the failure condition of a test, you can do so by changing the Advanced settings for that test.
SettingDescription
Filter ConditionWhen enabled, lets you define a filter expression for what you want your test to run on. Use the dropdown to select an expression.
SeverityDetermines whether the failure of the test returns an error or warning. Select from the dropdown to set the severity level.
Failure CalculationExpression that calculates a value from the test results. The Error If and Warning If conditions evaluate this calculated value.
Error IfCondition that triggers an error. Evaluates the Failure Calculation result. If true and severity is error, the test fails with an error status.
Warn IfCondition that triggers a warning. Evaluates the Failure Calculation result. If true, the test returns a warning status (regardless of severity setting).
Store FailuresWhen enabled, stores all records that failed the test. The records are saved in a new table with schema dbt_test__audit in your database. The table is named after the name of the model and data test. Make sure you have write permission to create a new table in your data warehouse.
Set max no of failuresWhen enabled, 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.
The Advanced settings are generally equivalent to the Data Test gem parameters.

​
4. Run data tests

Now that you’ve configured the tests, you can run them.
  1. Click Run all to execute all the defined tests. Alternatively, run individual tests by clicking the play button on the test’s row.
  2. Once the tests finish running, you’ll see the relevant test result next to each test definition.
Assign a test definition to a table or model
Click View Log to view the test logs. You’ll only be able to view the log of the most recent test run. You can copy or download the logs if needed.

​
5. Schedule test runs

When you schedule a pipeline, you can enable tests associated with Tables gems in the pipeline to run during that schedule.
  1. Open a pipeline containing tests to schedule.
  2. In the project header, click … > Schedule.
  3. Edit the existing schedule or configure a new schedule.
  4. Enable the Run data quality tests toggle.
  5. Click Confirm to save the changes.
Pipeline schedules differ from model schedules. Use the following steps if you are using scheduling models only.
  1. In the left sidebar of the project, click + Add Entity.
  2. Click Job. This opens the Create Job dialog.
  3. Enter a name for your job and click Create New.
  4. Drag a Model gem to your visual canvas.
  5. Click the model to open the model properties.
  6. Select the database object you want to run the test on.
  7. Select the Run tests checkbox in the left sidebar of the model gem.
  8. Ensure that your project, model, and fabric are correct.
  9. Click Save.

​
Share test definitions

If you publish your project as a package, you can share your test definitions with other teams. Once they import the package, they will be able to use your test definitions in their own projects.