search

ccao-data / data-architecture

Codebase for CCAO data infrastructure construction and management
https://ccao-data.github.io/data-architecture/
6 stars 4 forks source link

Update mode calculation for `res_report_summary` view #604

Closed dfsnow closed 2 months ago

dfsnow commented 2 months ago

Our build-daily-dbt-models workflow started failing this morning due to an obscure error resulting from the reporting.res_report_summary view:

Query exhausted resources at this scale factor.

I traced the error back to the calculation of the modal class code within the view. The mode calculation seems pretty inefficient since it has to ORDER BY many small groups multiple times. Swapping the count -> sort method for the one from this SO post fixes the error and has the same query time/data amount.

wrridgeway commented 2 months ago

Truly, I had no idea histogram was even a presto function. Neat stuff.