I have a bunch of queries that I have to run in BigQuery from my DAG and they return either 0 or 1 depending on the check that they do, sth like this:
SELECT IF(last_week_count/7>average_daily_threshold, 0, 1) FROM (SELECT ... ;
SELECT IF(last_week_count/7>average_daily_threshold, 0, 1) FROM (SELECT ... ;
What I would like to do is to raise an error (to fail the task in DAG) if there is ever 1 or ‘False’ returned from any of those queries. I have my operator configured like so:
run_bigquery_check = BigQueryInsertJobOperator(
task_id="run_bigquery_check",
configuration={
"query": {
"query": "{% include 'sql_check.sql' %}",
"useLegacySql": False,
}
},
location='europe-west3'
)
How can I obtain a result of a failed DAG from within an sql script?
The answer to my own question is to fail the sql with the ERROR() function. I don’t know why I couldn’t find it mentioned anywhere earlier.
Here is an example:
SELECT IF(last_week_count/7>average_daily_threshold, 0, ERROR("The average count of the ingested rows dropped below given threshold")) FROM (SELECT ... ;