search

mozilla / lookml-generator

LookML Generator for Glean and Mozilla Data
https://mozilla.github.io/lookml-generator/generator.html
Mozilla Public License 2.0
17 stars 17 forks source link

always_filter on date's in dimension groups prevent aggregate tables at other date levels. #388

Open ANich opened 2 years ago

ANich commented 2 years ago

For Explores that point to large tables like search_clients_daily, it's usually helpful to have aggregate tables smaller than the daily grain (weekly, monthly, etc.)

The always_filter on submission_date (https://github.com/mozilla/lookml-generator/blob/main/generator/explores/explore.py#L239-L240) for example means we can't aggregate to submission_month or submission_year so the fastest aggregate-aware queries we have will point to daily aggregates. We can get a lot faster for month/month year/year queries if we change that.

Opening this issue to gather thoughts on how to fix that.

One option is to remove the auto-generated always_filter for submission_date: 28 days in Explores eg. https://github.com/mozilla/looker-hub/blob/main/search/explores/desktop_search_counts.explore.lkml#L13-L17 and find another way to prevent huge scans. Any other ideas?

cc @Marlene-M-Hirose @scholtzan

ANich commented 2 years ago

Here's an example: image

Ideally we'd have "Submission Month" in the past 3 months which would hit a monthly aggregated table.