Conditionally trigger files/flow in Dataform based on a login

[knight_m]

Hello Team,

Is there a way for us to implement a if / else kind of mechanism in Dataform.

For Example : If an assertion fails, trigger another file in Dataform. As far as I have observed we can reference the table defined in previous sqlx files but can we conditionally trigger the code in a sqlx file based on a logic. ( select * from table where condition is satisfied or not met.)

Inputs/ direction for implementing the same will be greatly appreciated.

Thanks.

[nikacalupas]

Hi @knight_m,

Welcome to the Google Cloud Community!

Currently, Dataform does not support an if/else mechanism to conditionally trigger SQLX files based on conditions such as assertion failures. However, there are potential workarounds that might help implement an if/else-like mechanism in Dataform:

• Using when() function (or similar CASE statements) allows you to implement if-else-like logic directly inside SQL queries. 

• Using Cloud Function combined with Cloud Scheduler Approach: This setup can help you achieve conditional logic and trigger different Dataform SQLX files based on specific conditions, such as assertion results. Here's how you can do it:

  • Set-up Cloud Functions: Write a Cloud Function to check the assertion results and trigger another action if the condition fails.

  • Check Assertion: A Cloud Function checks Dataform assertion results. Based on the outcome (e.g., success or failure), it triggers subsequent actions, such as executing a different Dataform workflow or sending notifications.

  • Use Cloud Scheduler: Schedule the initial Cloud Function to run at your desired times using Cloud Scheduler, which can trigger HTTP(S) endpoints suitable for invoking Cloud Functions.

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.