I have 3 tables and I need to check if they have a positive number in the "rate" column. Tables have different schema but they all have a column named "rate". I am working with airflow and I created a DAG which has a task to check if the rate in not a positive number then fail. So in a simple word, I want my SQL fails if there is an incorrect rate in any of those tables. I have done this:
WITH t1 AS (
SELECT deliverydate, rate
FROM table1
WHERE rate <= 0
),
t2 AS (
SELECT deliverydate, rate
FROM table2
WHERE rate <= 0
),
t3 AS (
SELECT deliverydate, rate
FROM table3
WHERE rate <= 0
)
SELECT 1 FROM (
SELECT * FROM t1
UNION ALL
SELECT * FROM t2
UNION ALL
SELECT * FROM t3
)
In this case, t1, t2 and t3 will report rows with wrong rate(0 or negative rates). And Select 1 fails if there is no wrong rate.
But my goal is: pass if all the rates are correct and fail if at least one record has wrong rate and show me that record to fix it.
SQL queries run on bigquery standard