1693428 - Hook into schema deployment system to evolve derived table schema from glean schema

Status: RESOLVED WONTFIX

(Data Platform and Tools :: General, task)

Description

[Anthony Miyaguchi [:amiyaguchi]]

I am building a legacy shim for https://jira.mozilla.com/browse/LOOK-1 where I am creating a table that looks like org_mozilla_ios_firefox.metrics_v1 using telemetry.mobile_event_v4. This derived table is probably going to end up as a table in org_mozilla_ios_firefox_derived.legacy_metrics_v1.

This derived table is fully schema compatible with the glean schema and will not be updated since the mobile_event telemetry will be decommissioned in the near future. However, it would be useful to maintain schema compatibility of the table (and have null columns in places).

My understanding of the schema pipeline is that there is a terraform call that effectively runs bq update with the schema artifacts into the live and stable tables. It would be useful to hook into this cadence such that this derived table is never out of date. This would be the equivalent of running bq update moz-fx-data-shared-prod:org_mozilla_ios_firefox_derived.legacy_metrics_v1 schemas/org_mozilla_ios_firefox_derived/metrics/metrics.1.schema.json.

Some alternatives to this are to create a new derived table for the metrics table so glean table and legacy table never go out of sync. This would be possible with something like the clients daily tables in glam which contain a row per metric/key per day which are tall but skinny tables (and a consistent table schema). These clients daily tables are not optimized for general purpose use though. This could be a more performant solution for looking at glean metrics in looker, since it would take care of aggregating clients per day (given that the table is clustered appropriately). It may be more work overall, since the looker dashboards seem to only query columns relevant to the dashboard at hand.

Another alternative is to generate a view on the stable table view that is deployed on a cadence controlled by bigquery-etl (i.e. Airflow). The view would be auto-generated from the probe-dictionary. The schema of this deployed view could then be dumped via bq show, the table for legacy metrics updated with the schema, and then the view and table unioned together in yet another view. I dislike like this solution because it's effectively a roundabout way of accomplishing a direct schema update.

Comment 1

[Wesley Dawson [:whd]]

The approach of moving these schema updates into ops logic is the opposite direction of the approach I'd like to take in bug #1693353. I think it would be better for e.g. generated-sql to reflect these kinds of derived tables changes based on generated-schemas and make it the responsibility of data engineering to define and maintain these topologies.

Perhaps desirable is to redesign generated-schemas to include explicitly stable and live tables (and maybe the tables this bug is about too). :klukas had a draft proposal for a generated-schemas branch structure like this from late last year that included datasets, but it was before the ops logic around non-ingestion namespace datasets and workgroup ACLs were fully codified. I think we're in a reasonable position to begin tackling the redesign, but it's going to take concerted effort.

The 0.14 bigquery module is probably the most complicated piece of terraform code we employ. It includes a bunch of conditional variables to support the various ways in which we deploy bigquery resources, and it would be fairly easy to extend this module to support the use case here. However, I haven't begun work on bug #1692117, and indeed only today closed its primary blocker in bug #1682749. Any change to BQ provisioning before that bug is finished will require double implementation in 0.11 and 0.14+.

Comment 2

[Jeff Klukas [:klukas] (UTC-4)]

It may be possible to work around this problem without adding any new concepts.

We could define org_mozilla_ios_firefox_derived.legacy_metrics_v1 in a query.py file that handles the logic of first pulling down the org_mozilla_ios_firefox.metrics_v1 schema and doing an update operation to ensure the legacy_metrics_v1 schema is in sync, then runs the incremental query. We have support in bigquery-etl for being able to run a query.py rather than a SQL file directly in the generated DAG, which seems appropriate for this rather custom need.

Comment 3

[Anthony Miyaguchi [:amiyaguchi]]

We run the ETL once, and the only subsequent operation needed is to apply the latest schema to the table on a schedule.

I'm accustomed to running python scripts directly within the generated dags but believe the latency between schema updates and when the ETL run will cause a short period where queries against the view can fail. This period would probably be under an hour, assuming the dag is scheduled to schema deploys (re: deploy history https://protosaur.dev/mps-deploys/), but it could be even longer with schema deploys outside the regular cadence.

Example:

• A view (1) unions org_mozilla_ios_firefox.metrics (2) and org_mozilla_ios_firefox.legacy_metrics (3).
• UTC+03 - A new probe is added to (2) during schema deploys causing queries against (1) to fail with incompatible columns.
• UTC+04 - ETL runs to update (3) using the schema from (2) causing queries against (1) to succeed.

Comment 4

[Anthony Miyaguchi [:amiyaguchi]]

I'm going to close this with with wont fix. The column ordering is an issue, even with up to date schemas. My solution is to go with a generated view that's deployed via query.py which does a few things:

• fetches the last schema deploy commit via the mps-deploys dashboard (https://protosaur.dev/mps-deploys/data/mozilla_pipeline_schemas/deploys.json)
• fetches the latest schema for org_mozilla_ios_firefox.events_v1
• applies the schema to the legacy metrics table
• generates a union view using the latest schema as the source of truth, so there is never an issue with incompatible schemas and column ordering

This ends up being similar to the last alternative in my original post . The initial implementation is here: https://github.com/mozilla/bigquery-etl/blob/bc771a04e7b3a3bdd7a60edfd2b325e17041f902/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox/unified_metrics/query.py