Hi guys! Happy new year! I need some guidance as I'm facing an issue with the table

test_result_rows

Essentially, it is consuming quite a lot of read bytes from my BigQuery. We run the edr report and alerts every hour and based on the logs I see that it runs quite often the following query:

select
  elementary_test_results_id,
  result_row
from
  `elementary`.`test_result_rows`
where 
  timestamp_diff(current_timestamp, cast(detected_at as TIMESTAMP), day) < 7

As what I could gather, this table stores the test result samples therefore it can get quite big the more tests we run (and we do have plenty!) The problem is that, although this table is indeed partitioned on the

detected_at

field by day, with the above WHERE clause syntax BigQuery doesn't identify the partitions to read and ends up reading the whole table. I faced this issue some weeks ago and truncated the table and added the following variable to my `dbt_project.yaml`:

vars:
  test_sample_row_count: 0

However that didn't seem to work as the table is once again big and consuming quite a lot of bytes read in BigQuery. Is there any way to resolve that other than constantly truncate this table?

Hi @Juliano Garcia, we are sadly aware of the poor performance of elementary on BigQuery, and plan to address that in the future. but currently do not have a solution for that drop sad 🙏

Oh ok! Thanks @Ofek Weiss! Glad to hear that there's a plan to address that in the future! For now, I'll keep truncating it.

Hello! 👋 I'm running into this BigQuery problem as well drop sad have you been able to look at this any further? From what I understand it's the

cast(detected_at as TIMESTAMP)

that prevents BigQuery from using the partitions properly (+ that the table was initially created without partitions) Running

where detected_at >= timestamp_sub(current_timestamp(), interval 1 day)

works like a charm

Hi! Apologies for bringing up an old thread, but I was just wondering if anyone has had any success with this? Thank you!

Hey! No need to apologize, good to resurface this. If anyone wants open a PR for this (depending on how comfortable you are with dbt), the line is here: https://github.com/elementary-data/elementary/blob/69393bdc714ee1d21b585fa9cc0972c[…]elementary/monitor/dbt_project/macros/get_result_rows_agate.sql and we are of course happy to assist. Alternatively, you can maybe truncate the table for anything x days older so you don't store all the historic test results but you are still able to generate the report. You can make this part of your pipeline

Hey! I opened a PR: https://github.com/elementary-data/elementary/pull/1940

@Noya Offer @Itamar Hartstein