Some ETLs and views may be silently unioning data incorrectly

[data-sync-user]

While investigating an error relating to unioning ping data [~accountid:6047cd5cd7f56e0071965b2d] noticed that the type and enrollment columns in the ping_info.experiments[].value.extra struct are in different orders in various pings, and when unioning such pings together as-is BigQuery won’t complain because their column types are compatible, which could result in data silently ending up in the wrong column in the union output for some pings.

This has been manually worked around in a couple of cases recently (bigquery-etl#6878, bigquery-etl#6887), but there may be other such cases we don’t yet know about.

It’s possible the Schema.generate_compatible_select_expression() method (code) could be used to help with this situation (it’s currently used for unioning pings in the Glean app ping views).

┆Issue is synchronized with this Jira Bug

[data-sync-user]

➤ Sean Rose commented:

#6916 ( https://github.com/mozilla/bigquery-etl/pull/6916|smart-link ) will allow us to programmatically check for possibly incorrect unions.

Here are the results of a test run of that code:

• Three cases where the unions are definitely incorrect:

  • sql/moz-fx-data-shared-prod/mozilla_vpn_derived/events_daily_v1/query.sql ( https://github.com/mozilla/bigquery-etl/blob/4b2824f2ef4e17949965346f7a2a79c34abf58bc/sql/moz-fx-data-shared-prod/mozilla_vpn_derived/events_daily_v1/query.sql )

    • Schema mismatch in union version queries and add clients_last_seen and mau28_by_dimensions #1: Field Optimize clients_last_seen_v1.init.sql #4 in metadata.header has different names (x_pingsender_version vs x_foxsec_ip_reputation).
    • Schema mismatch in union Wiki changes #2: Field Optimize clients_last_seen_v1.init.sql #4 in metadata.header has different names (x_pingsender_version vs x_foxsec_ip_reputation).
    • Schema mismatch in union Fix exact_mau28_by_dimensions and update exact_mau28 to use it #3: Field Optimize clients_last_seen_v1.init.sql #4 in metadata.header has different names (x_pingsender_version vs x_foxsec_ip_reputation).

  • sql/moz-fx-data-shared-prod/mozilla_vpn/events_unnested/view.sql ( https://github.com/mozilla/bigquery-etl/blob/b45a80f00521935996c39c2820eebccb5e94d93b/sql/moz-fx-data-shared-prod/mozilla_vpn/events_unnested/view.sql )

    • Schema mismatch in union version queries and add clients_last_seen and mau28_by_dimensions #1: Field version queries and add clients_last_seen and mau28_by_dimensions #1 in ping_info.experiments[].value.extra has different names (type vs enrollment_id).
    • Schema mismatch in union Wiki changes #2: Field version queries and add clients_last_seen and mau28_by_dimensions #1 in ping_info.experiments[].value.extra has different names (type vs enrollment_id).

  • sql/moz-fx-data-shared-prod/telemetry_derived/error_aggregates_v1/query.sql ( https://github.com/mozilla/bigquery-etl/blob/b45a80f00521935996c39c2820eebccb5e94d93b/sql/moz-fx-data-shared-prod/telemetry_derived/error_aggregates_v1/query.sql )

    • Schema mismatch in union version queries and add clients_last_seen and mau28_by_dimensions #1: Field Fix exact_mau28_by_dimensions and update exact_mau28 to use it #3 in environment.settings.attribution has different names (medium vs experiment).

• Three cases where it seems like the column names are just arbitrarily different, but the unions are probably OK:

  • sql/moz-fx-data-shared-prod/amo_prod/amo_stats_dau_v2/query.sql ( https://github.com/mozilla/bigquery-etl/blob/b45a80f00521935996c39c2820eebccb5e94d93b/sql/moz-fx-data-shared-prod/amo_prod/amo_stats_dau_v2/query.sql )

    • Schema mismatch in union version queries and add clients_last_seen and mau28_by_dimensions #1: Field version queries and add clients_last_seen and mau28_by_dimensions #1 in addons[] has different names (id vs addon).

  • sql/moz-fx-data-shared-prod/telemetry_derived/error_aggregates_v1/query.sql ( https://github.com/mozilla/bigquery-etl/blob/b45a80f00521935996c39c2820eebccb5e94d93b/sql/moz-fx-data-shared-prod/telemetry_derived/error_aggregates_v1/query.sql )

    • Schema mismatch in union Add first airflow DAG #5: Field counter-PR #7 has different names (os vs normalized_os).

  • sql/moz-fx-data-shared-prod/telemetry/lockwise_mobile_events_v1/view.sql ( https://github.com/mozilla/bigquery-etl/blob/b45a80f00521935996c39c2820eebccb5e94d93b/sql/moz-fx-data-shared-prod/telemetry/lockwise_mobile_events_v1/view.sql )

    • Schema mismatch in union version queries and add clients_last_seen and mau28_by_dimensions #1: Field counter-PR #7 has different names (timestamp vs f0_).
      • The telemetry.telemetry_focus_event_parquet_v1 view's events struct field has subfields with the unfortunate names f0_, f1_, etc.

• 19 cases where there was a minor type mismatch of integer vs numeric, or integer vs float, but the unions are probably OK (other than the output column maybe not having the exact intended type).

[data-sync-user]

➤ Ben Wu commented:

Did you run this on the generated sql? I would expect more of these errors in the generated sql

[data-sync-user]

➤ Sean Rose commented:

Yes, I ran it on everything in the sql directory on the private-generated-sql ( https://github.com/mozilla/private-bigquery-etl/tree/private-generated-sql/sql ) branch. The only blind spot I’m aware of is there were 26 cases where I didn’t have the necessary permissions on the tables/views being selected from and got a 403 Forbidden error from BigQuery.