using SqlSensor in Airflow, create a query that checks if all days from previous month are in a table

user_14444816 0
Viewed 162

Would this be a correct/fast way to check if all days from the previous month are in my table? My Snowflake table has a 'date' column that is a 'date' type:

I'm doing this but I feel there must be a better way?

SELECT *
FROM dfp.revenue_allocation
WHERE YEAR("date") = '{{ execution_date.year }}'
AND MONTH("date") = '{{ execution_date.month }}'-1
airflow snowflake-cloud-data-platform
1 Answers

To get best performance, expression on columns should be avoided:

SELECT *
FROM dfp.revenue_allocation
WHERE "date" >= TRUNC( CAST(? AS DATE) - INTERVAL '1 MONTH', 'MONTH')
  AND "date" < TRUNC( CAST(? AS DATE), 'MONTH');

TRUNC

Truncates a date, time, or timestamp to the specified part

user_50708790
Related