Problem
With the volume of data flowing into product_metrics_web_base Hive table from Test Kitchen-based instruments and experiments, it has become impossible to build Presto-powered dashboards in Superset on top of this huge table.
Any existing dashboards that were developed when there was less data flowing into it now frequently time out (often leaving behind ghost queries, cf. T418542).
Even though dbt has made it easier to create pipelines to aggregate/calculate metrics and store their values, we still need a way of quickly standing up a Superset dashboard, especially if we don't have all the requirements (yet) for the shape of the aggregated data, including but not limited to:
- the exact metric definition (easy to modify in presto query, harder to redefine and backfill)
- the dimensions by which the metric should be sliceable (easy to modify in presto query, harder to expand table)
Proposed solution
Essentially, a generalized version of T429051: Personal Dashboard health metrics events Iceberg table where instead of siphoning data from product_metrics_web_base for just one instrument, we siphon all instrument-produced data into an Iceberg table that uses days(timestamp) and instrument_name partitioning scheme.
We can then decommission the pipeline created in T429051 and update the queries in the Superset dashboard to use wmf_product.web_instrument_event_v1 (and specify instrument_name = 'personal-dashboard-health-metrics') instead of wmf_product.personal_dashboard_event_v1.
Once the data is pre-processed, it might also be easier/faster to derive aggregated datasets from it. That is, querying the derived Iceberg table is less computationally intensive than querying the raw product_metrics_web_base table.
Maintenance
Maintenance should have a configuration like:
maintenance: schedule: "@monthly" expire_snapshots: older_than_days: 90 data_delete: enabled: True where: "timestamp <= TIMESTAMP '{{ data_interval_end | subtract_days(90) | to_dt() }}'"
Acceptance criteria
- An hourly-updated Iceberg table wmf_product.web_instrument_event_v1 with proposed partitioning scheme and 90 day data retention policy