About assertions

An assertion is a data quality test query that finds rows that violate one ormore conditions specified in the query. If the query returns any rows,the assertionfails. Dataform runs assertions every time it updates your workflowand it alerts you if any assertions fail.

Dataform automatically creates views in BigQuery that containthe results of compiled assertion queries. Asconfigured in your workflow settings file,Dataform creates these views in an assertions schema where you caninspect assertion results.

For example, for the defaultdataform_assertions schema, Dataformcreates a view in BigQuery in the following format:dataform_assertions.assertion_name.

You can create assertions for all Dataform table types: tables,incremental tables, views, and materialized views.

You can create assertions in the following ways:

• Add built-in assertions to the config block of a table.

  You can add built-in assertions to theconfig block of a table andspecify their conditions.

• Add manual assertions in a separate SQLX file.

  You manually write custom assertions in a separate SQLX file for advanceduse cases or for datasets not created by Dataform.

Before you begin

1. In the Google Cloud console, go to theDataform page.

   Go to the Dataform page

2. Select orcreate a repository.

3. Select orcreate a development workspace.

4. Create a table.

Required roles

To get the permissions that you need to create assertions, ask your administrator to grant you theDataform Editor (roles/dataform.editor) IAM role on workspaces. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Create built-in assertions

You can add built-in Dataform assertions to theconfig block of atable. Dataform runs these assertions after table creation. AfterDataform creates the table, you can see if the assertion passed in theWorkflow execution logs tab of your workspace.

You can create the following assertions in theconfig block of a table:

• nonNull

  This condition asserts that the specified columns are not null across alltable rows. This condition is used for columns that can never be null.

  The following code sample shows anonNull assertion in theconfig blockof a table:

config {  type: "table",  assertions: {    nonNull: ["user_id", "customer_id", "email"]  }}SELECT ...

• rowConditions

  This condition asserts that all table rows follow the custom logic youdefine. Each row condition is a custom SQL expression, and each table row isevaluated against each row condition. The assertion fails if any table rowresults infalse.

  The following code sample shows a customrowConditions assertion in theconfig block of an incremental table:

config {  type: "incremental",  assertions: {    rowConditions: [      'signup_date is null or signup_date > "2022-08-01"',      'email like "%@%.%"'    ]  }}SELECT ...

• uniqueKey

  This condition asserts that, in a specified column, no table rows have thesame value.

  The following code sample shows auniqueKey assertion in theconfigblock of a view:

config {  type: "view",  assertions: {    uniqueKey: ["user_id"]  }}SELECT ...

• uniqueKeys

  This condition asserts that, in the specified columns, no table rows havethe same value. The assertion fails if there is more than one row in thetable with the same values for all the specified columns.

  The following code sample shows auniqueKeys assertion in theconfigblock of a table:

config {  type: "table",  assertions: {    uniqueKeys: [["user_id"], ["signup_date", "customer_id"]]  }}SELECT ...

Add assertions to theconfig block

To add assertions to the config block of a table, follow these steps:

1. In your development workspace, in theFiles pane, select a tabledefinition SQLX file.
2. In theconfig block of the table file, enterassertions: {}.
3. Insideassertions: {}, add your assertions.
4. Optional: ClickFormat.

The following code sample shows the conditions added in theconfig block:

config {  type: "table",  assertions: {    uniqueKey: ["user_id"],    nonNull: ["user_id", "customer_id"],    rowConditions: [      'signup_date is null or signup_date > "2019-01-01"',      'email like "%@%.%"'    ]  }}SELECT ...

Create manual assertions with SQLX

Manual assertions are SQL queries that you write in a dedicated SQLX file. Amanual assertion SQL query must return zero rows. If the query returns rowswhen it's run, the assertion fails.

To add manual assertions in a new SQLX file, follow these steps:

1. In theFiles pane, next todefinitions/, click theMore menu.
2. ClickCreate file.

3. In theAdd a file path field, enter the name of the file followed by.sqlx. For example,definitions/custom_assertion.sqlx.

   Filenames can only include numbers, letters, hyphens, and underscores.

4. ClickCreate file.

5. In theFiles pane, click the new file.

6. In the file, enter:

   config {  type: "assertion"}

7. Below theconfig block, write your SQL query or multiple queries.

8. Optional: ClickFormat.

The following code sample shows a manual assertion in a SQLX file that assertsthat fieldsA,B, andc are neverNULL insometable:

config { type: "assertion" }SELECT  *FROM  ${ref("sometable")}WHERE  a IS NULL  OR b IS NULL  OR c IS NULL

What's next

• To learn more about assertion types, seeDataform API.
• To learn how to define assertions with JavaScript, seeCreate workflows exclusively with JavaScript.
• To learn how to manually run workflows, seeManually trigger runs.