Testings dataform incremental tables

Google Cloud Data Analytics
1

Hi,

is it possible to implement test for incremental tables? I can write them for common table types, however what about incremental ones?

Best regards.

2

Hi MarekK,

Welcome to Google Developer Program Forums!

Yes, it is possible to implement tests for incremental tables in Google Cloud Dataform. These tests, known as assertions in Dataform, are a crucial part of maintaining data quality within your data pipelines.

You can apply data quality tests to all types of tables in Dataform, including the incremental ones you’re working with. These assertions are essentially queries that check for conditions you define. If a query returns any rows, the assertion fails, alerting you to a potential issue.

There are two main ways to create these tests for your incremental tables:

  1. Built-in Assertions:

For common data quality checks, you can add built-in assertions directly into the config block of your incremental table’s SQLX file. Dataform runs these tests after the table has been created or updated.

Supported built-in assertions include:

  • nonNull: Ensures that specified columns do not contain any null values.
  • rowConditions: Allows you to define custom SQL expressions to validate each row in your table. The assertion fails if any row evaluates to false.
  1. Manual Assertions:

For more complex or customized testing scenarios, you can write your own assertion queries in a separate SQLX file. This approach is also useful for testing datasets that are not generated by Dataform.

A manual assertion is a SQL query that should return zero rows if the data is valid.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.