Executing SQL on Dataform Assertion Failure

[himkush]

I want to execute SQL when a dataform assertion fails and insert audit record into a master audit table. How can I accomplish this?

[ms4446]

To execute SQL when a Dataform assertion fails and insert an audit record into a master audit table in Google Cloud Dataform, follow these steps:

1. Create an Audit Table:

First, create a table to store the audit records. This table should include columns for the assertion name, the timestamp of the assertion failure, and any other relevant information.

CREATE TABLE audit_records (
  assertion_name STRING,
  assertion_failure_timestamp TIMESTAMP,
  additional_information STRING
);

2. Create Assertions with Failure Handling Logic:

In Dataform, assertions are defined in SQLX files. You can include the logic for handling assertion failures within these files. However, Dataform doesn't support a separate "error handler" file as a standard feature. Instead, you handle the assertion failures within the assertion definition or use JavaScript for more complex logic.

Here's an example of an assertion definition:

-- definitions/my_assertion.sqlx
config {
  type: "assertion"
}

SELECT *
FROM your_table
WHERE your_condition_is_not_met;

In this SQLX file, you define what conditions must be met for the data to be considered valid. If the assertion fails, it means these conditions were not met.

3. Optional: Use JavaScript for Advanced Error Handling:

For more complex error handling, such as inserting a record into the audit table when an assertion fails, you can use Dataform's JavaScript API.

// definitions/handle_assertions.js
const dataform = require("@dataform/core");

function handleAssertionFailure(assertionName, additionalInfo) {
  const auditSQL = `
    INSERT INTO audit_records (
      assertion_name,
      assertion_failure_timestamp,
      additional_information
    )
    VALUES (
      '${assertionName}',
      CURRENT_TIMESTAMP,
      '${additionalInfo}'
    )
  `;

  dataform.execute(auditSQL);
}

// You can call this function based on the results of your data pipeline runs.

In this JavaScript file, you define a function that inserts a record into the audit table. This function can be triggered based on the results of your Dataform runs, allowing you to log details about assertion failures.

With this setup, you have a robust system for monitoring data quality. When an assertion fails, you can trigger the JavaScript function to insert a detailed record into your audit table, helping you keep track of data issues and maintain high data quality standards.

[himkush]

@ms4446, Thanks for the solution.

How can I know that a assertion has failed and call the JavaScript function?

[DataEngineer]

I have something similar set up. Create an incremental table that uses the same logic as the assertion. Then reference that in the list of dependencies on the sqlx file your assertion is running on. Or if you need custom SQL for the insert then use a type: "operations" in your sqlx file.

[himkush]

@ms4446 @DataEngineer, 
How can I determine if a dataform assertion has failed? If I set the assertion as a dependency for the subsequent action/SQL, it only runs when the assertion passes and blocks execution on failure. Is there a way to identify assertion failure and execute an SQL query/ failure operation accordingly?