elementary-data
Allow for elementary to utilize partitions in Bigquery
Is your feature request related to a problem? Please describe.
My team has noticed high query costs for elementary on recurring queries to the table elementary test_result_rows. This is because:
- The table isn't created partitioned
- Even if we partition the table on
detected_at, the where-clause in the query isn't designed to use the partitions. (It will look like this:where timestamp_diff(current_timestamp, cast(detected_at as TIMESTAMP), day) < 7. The casting of the column prevents bigquery from using the partitioned column.)
Issue is also described in this slack-thread
Describe the solution you'd like I'd like to see elementary output a query that utilizes a partitioned table. (A bonus would also be if the table was automatically created partitioned.)
Describe alternatives you've considered
I've considered adjusting the where-clause in the macro elementary/monitor/dbt_project/macros/get_result_rows_agate.sql
to something that doesn't transform detected_at, for example by using edr_timeadd, but I'm a bit unsure how it would work on the different cloud solutions.
Haven't checked out how to create the table partitioned.
Additional context
Would you be willing to contribute this feature? Sure, but would need some guidance to not break the other clouds.
