dataops-ci-bot
commented
2 months ago Integration report for "add is_desktop to engagement and retention tables"
sql.diff
Click to expand!
```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry: active_users_mobile Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry: desktop_retention_clients Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry: mobile_active_users Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry: retention_clients_mobile Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry: retention_mobile diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:54:53.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.non_interaction_v1` + `moz-fx-data-shared-prod.bedrock_live.interaction_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.interaction_v1` + `moz-fx-data-shared-prod.bedrock_live.non_interaction_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-24 16:00:11.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: source + mode: NULLABLE +- name: source type: STRING -- mode: NULLABLE - name: event_type + mode: NULLABLE +- name: event_type type: STRING -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: subdivision1 + mode: NULLABLE +- name: subdivision1 type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: event_count + mode: NULLABLE +- name: event_count type: INTEGER -- mode: NULLABLE - name: user_count + mode: NULLABLE +- name: user_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-24 16:00:11.000000000 +0000 @@ -1,40 +1,40 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: impression_count + mode: NULLABLE +- name: impression_count type: INTEGER -- mode: NULLABLE - name: click_count + mode: NULLABLE +- name: click_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/active_users/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -40,6 +40,7 @@ LOWER(IFNULL(isp, "")) <> "browserstack" AND LOWER(IFNULL(distribution_id, "")) <> "mozillaonline" ) AS is_mobile, + FALSE AS is_desktop, -- Adding isp at the end because it's in different column index in baseline table for some products. -- This is to make sure downstream union works as intended. isp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-24 15:59:25.000000000 +0000 @@ -26,6 +26,9 @@ - name: adjust_network type: STRING mode: NULLABLE +- name: install_source + type: STRING + mode: NULLABLE - name: retained_week_2 type: BOOLEAN mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-24 15:59:24.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/retention/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/retention/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/retention/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/retention/schema.yaml 2024-05-24 15:59:25.000000000 +0000 @@ -30,8 +30,6 @@ - name: is_mobile type: BOOLEAN mode: NULLABLE - description: Indicates if this specific entry is used towards calculating mobile - DAU. - name: adjust_ad_group type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/retention_clients/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/retention_clients/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/retention_clients/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/retention_clients/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -2,7 +2,7 @@ CREATE OR REPLACE VIEW `moz-fx-data-shared-prod.fenix.retention_clients` AS -WITH active_users AS ( +WITH clients_last_seen AS ( SELECT submission_date, client_id, @@ -13,7 +13,6 @@ mozfun.bits28.retention(days_active_bits & days_seen_bits, submission_date) AS retention_active, days_seen_bits, days_active_bits, - is_mobile, FROM `moz-fx-data-shared-prod.fenix.active_users` ), @@ -39,52 +38,51 @@ `moz-fx-data-shared-prod.fenix_derived.firefox_android_clients_v1` ) SELECT - active_users.submission_date AS submission_date, + clients_last_seen.submission_date AS submission_date, clients_daily.submission_date AS metric_date, clients_daily.first_seen_date, clients_daily.client_id, clients_daily.sample_id, - active_users.app_name, + clients_last_seen.app_name, clients_daily.normalized_channel, clients_daily.country, clients_daily.app_display_version AS app_version, clients_daily.locale, clients_daily.isp, - active_users.is_mobile, attribution.play_store_attribution_campaign, attribution.play_store_attribution_medium, attribution.play_store_attribution_source, attribution.meta_attribution_app, attribution.install_source, -- ping sent retention - active_users.retention_seen.day_27.active_on_metric_date AS ping_sent_metric_date, + clients_last_seen.retention_seen.day_27.active_on_metric_date AS ping_sent_metric_date, ( - active_users.retention_seen.day_27.active_on_metric_date - AND active_users.retention_seen.day_27.active_in_week_3 + clients_last_seen.retention_seen.day_27.active_on_metric_date + AND clients_last_seen.retention_seen.day_27.active_in_week_3 ) AS ping_sent_week_4, -- activity retention - active_users.retention_active.day_27.active_on_metric_date AS active_metric_date, + clients_last_seen.retention_active.day_27.active_on_metric_date AS active_metric_date, ( - active_users.retention_active.day_27.active_on_metric_date - AND active_users.retention_active.day_27.active_in_week_3 + clients_last_seen.retention_active.day_27.active_on_metric_date + AND clients_last_seen.retention_active.day_27.active_in_week_3 ) AS retained_week_4, -- new profile retention clients_daily.is_new_profile AS new_profile_metric_date, ( clients_daily.is_new_profile - AND active_users.retention_active.day_27.active_in_week_3 + AND clients_last_seen.retention_active.day_27.active_in_week_3 ) AS retained_week_4_new_profile, ( clients_daily.is_new_profile -- Looking back at 27 days to support the official definition of repeat_profile (someone active between days 2 and 28): - AND BIT_COUNT(mozfun.bits28.range(active_users.days_active_bits, -26, 27)) > 0 + AND BIT_COUNT(mozfun.bits28.range(clients_last_seen.days_active_bits, -26, 27)) > 0 ) AS repeat_profile, attribution.adjust_ad_group, attribution.adjust_campaign, attribution.adjust_creative, attribution.adjust_network, - active_users.days_seen_bits, - active_users.days_active_bits, + clients_last_seen.days_seen_bits, + clients_last_seen.days_active_bits, CASE WHEN clients_daily.submission_date = first_seen_date THEN 'new_profile' @@ -95,17 +93,17 @@ WHEN DATE_DIFF(clients_daily.submission_date, first_seen_date, DAY) >= 28 THEN 'existing_user' ELSE 'Unknown' - END AS lifecycle_stage, + END AS lifecycle_stage FROM `moz-fx-data-shared-prod.fenix.baseline_clients_daily` AS clients_daily INNER JOIN - active_users - ON clients_daily.submission_date = active_users.retention_seen.day_27.metric_date - AND clients_daily.client_id = active_users.client_id - AND clients_daily.normalized_channel = active_users.normalized_channel + clients_last_seen + ON clients_daily.submission_date = clients_last_seen.retention_seen.day_27.metric_date + AND clients_daily.client_id = clients_last_seen.client_id + AND clients_daily.normalized_channel = clients_last_seen.normalized_channel LEFT JOIN attribution ON clients_daily.client_id = attribution.client_id AND clients_daily.normalized_channel = attribution.normalized_channel WHERE - active_users.retention_seen.day_27.active_on_metric_date + clients_last_seen.retention_seen.day_27.active_on_metric_date diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/query.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/query.sql 2024-05-24 15:54:52.000000000 +0000 @@ -7,7 +7,6 @@ country, app_version, locale, - is_mobile, adjust_ad_group, adjust_campaign, adjust_creative, @@ -37,7 +36,6 @@ country, app_version, locale, - is_mobile, adjust_ad_group, adjust_campaign, adjust_creative, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/retention_v1/schema.yaml 2024-05-24 15:54:52.000000000 +0000 @@ -35,11 +35,6 @@ mode: NULLABLE description: Client's locale on the metric date. -- name: is_mobile - type: BOOLEAN - mode: NULLABLE - description: Indicates if this specific entry is used towards calculating mobile DAU. - - name: adjust_ad_group type: STRING mode: NULLABLE @@ -85,6 +80,7 @@ mode: NULLABLE description: The source of a profile installation. + - name: ping_sent_metric_date type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:54:53.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` UNION ALL SELECT submission_timestamp, @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` UNION ALL SELECT submission_timestamp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/active_users/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -40,6 +40,7 @@ LOWER(IFNULL(isp, "")) <> "browserstack" AND LOWER(IFNULL(distribution_id, "")) <> "mozillaonline" ) AS is_mobile, + FALSE AS is_desktop, -- Adding isp at the end because it's in different column index in baseline table for some products. -- This is to make sure downstream union works as intended. isp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention/schema.yaml 2024-05-24 15:59:52.000000000 +0000 @@ -30,8 +30,6 @@ - name: is_mobile type: BOOLEAN mode: NULLABLE - description: Indicates if this specific entry is used towards calculating mobile - DAU. - name: adjust_ad_group type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention_clients/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention_clients/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention_clients/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios/retention_clients/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -2,7 +2,7 @@ CREATE OR REPLACE VIEW `moz-fx-data-shared-prod.firefox_ios.retention_clients` AS -WITH active_users AS ( +WITH clients_last_seen AS ( SELECT submission_date, client_id, @@ -13,7 +13,6 @@ mozfun.bits28.retention(days_active_bits & days_seen_bits, submission_date) AS retention_active, days_seen_bits, days_active_bits, - is_mobile, FROM `moz-fx-data-shared-prod.firefox_ios.active_users` ), @@ -31,48 +30,47 @@ `moz-fx-data-shared-prod.firefox_ios_derived.firefox_ios_clients_v1` ) SELECT - active_users.submission_date AS submission_date, + clients_last_seen.submission_date AS submission_date, clients_daily.submission_date AS metric_date, clients_daily.first_seen_date, clients_daily.client_id, clients_daily.sample_id, - active_users.app_name, + clients_last_seen.app_name, clients_daily.normalized_channel, clients_daily.country, clients_daily.app_display_version AS app_version, clients_daily.locale, clients_daily.isp, - active_users.is_mobile, attribution.is_suspicious_device_client, -- ping sent retention - active_users.retention_seen.day_27.active_on_metric_date AS ping_sent_metric_date, + clients_last_seen.retention_seen.day_27.active_on_metric_date AS ping_sent_metric_date, ( - active_users.retention_seen.day_27.active_on_metric_date - AND active_users.retention_seen.day_27.active_in_week_3 + clients_last_seen.retention_seen.day_27.active_on_metric_date + AND clients_last_seen.retention_seen.day_27.active_in_week_3 ) AS ping_sent_week_4, -- activity retention - active_users.retention_active.day_27.active_on_metric_date AS active_metric_date, + clients_last_seen.retention_active.day_27.active_on_metric_date AS active_metric_date, ( - active_users.retention_active.day_27.active_on_metric_date - AND active_users.retention_active.day_27.active_in_week_3 + clients_last_seen.retention_active.day_27.active_on_metric_date + AND clients_last_seen.retention_active.day_27.active_in_week_3 ) AS retained_week_4, -- new profile retention clients_daily.is_new_profile AS new_profile_metric_date, ( clients_daily.is_new_profile - AND active_users.retention_active.day_27.active_in_week_3 + AND clients_last_seen.retention_active.day_27.active_in_week_3 ) AS retained_week_4_new_profile, ( clients_daily.is_new_profile -- Looking back at 27 days to support the official definition of repeat_profile (someone active between days 2 and 28): - AND BIT_COUNT(mozfun.bits28.range(active_users.days_active_bits, -26, 27)) > 0 + AND BIT_COUNT(mozfun.bits28.range(clients_last_seen.days_active_bits, -26, 27)) > 0 ) AS repeat_profile, attribution.adjust_ad_group, attribution.adjust_campaign, attribution.adjust_creative, attribution.adjust_network, - active_users.days_seen_bits, - active_users.days_active_bits, + clients_last_seen.days_seen_bits, + clients_last_seen.days_active_bits, CASE WHEN clients_daily.submission_date = first_seen_date THEN 'new_profile' @@ -83,17 +81,17 @@ WHEN DATE_DIFF(clients_daily.submission_date, first_seen_date, DAY) >= 28 THEN 'existing_user' ELSE 'Unknown' - END AS lifecycle_stage, + END AS lifecycle_stage FROM `moz-fx-data-shared-prod.firefox_ios.baseline_clients_daily` AS clients_daily INNER JOIN - active_users - ON clients_daily.submission_date = active_users.retention_seen.day_27.metric_date - AND clients_daily.client_id = active_users.client_id - AND clients_daily.normalized_channel = active_users.normalized_channel + clients_last_seen + ON clients_daily.submission_date = clients_last_seen.retention_seen.day_27.metric_date + AND clients_daily.client_id = clients_last_seen.client_id + AND clients_daily.normalized_channel = clients_last_seen.normalized_channel LEFT JOIN attribution ON clients_daily.client_id = attribution.client_id AND clients_daily.normalized_channel = attribution.normalized_channel WHERE - active_users.retention_seen.day_27.active_on_metric_date + clients_last_seen.retention_seen.day_27.active_on_metric_date diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/query.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/query.sql 2024-05-24 15:54:52.000000000 +0000 @@ -7,7 +7,6 @@ country, app_version, locale, - is_mobile, adjust_ad_group, adjust_campaign, adjust_creative, @@ -33,7 +32,6 @@ country, app_version, locale, - is_mobile, adjust_ad_group, adjust_campaign, adjust_creative, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/schema.yaml 2024-05-24 15:54:52.000000000 +0000 @@ -35,11 +35,6 @@ mode: NULLABLE description: Client's locale on the metric date. -- name: is_mobile - type: BOOLEAN - mode: NULLABLE - description: Indicates if this specific entry is used towards calculating mobile DAU. - - name: adjust_ad_group type: STRING mode: NULLABLE @@ -65,6 +60,7 @@ mode: NULLABLE description: Flag to identify suspicious device users, see bug-1846554 for more info. + - name: ping_sent_metric_date type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/active_users/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -40,6 +40,7 @@ LOWER(IFNULL(isp, "")) <> "browserstack" AND LOWER(IFNULL(distribution_id, "")) <> "mozillaonline" ) AS is_mobile, + FALSE AS is_desktop, -- Adding isp at the end because it's in different column index in baseline table for some products. -- This is to make sure downstream union works as intended. isp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_ios/active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_ios/active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_ios/active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_ios/active_users/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -40,6 +40,7 @@ LOWER(IFNULL(isp, "")) <> "browserstack" AND LOWER(IFNULL(distribution_id, "")) <> "mozillaonline" ) AS is_mobile, + FALSE AS is_desktop, -- Adding isp at the end because it's in different column index in baseline table for some products. -- This is to make sure downstream union works as intended. isp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml 2024-05-24 15:59:56.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: country +- name: country type: STRING -- mode: NULLABLE - name: city + mode: NULLABLE +- name: city type: STRING -- mode: NULLABLE - name: datetime + mode: NULLABLE +- name: datetime type: TIMESTAMP -- mode: NULLABLE - name: proportion_undefined + mode: NULLABLE +- name: proportion_undefined type: FLOAT -- mode: NULLABLE - name: proportion_timeout + mode: NULLABLE +- name: proportion_timeout type: FLOAT -- mode: NULLABLE - name: proportion_abort + mode: NULLABLE +- name: proportion_abort type: FLOAT -- mode: NULLABLE - name: proportion_unreachable + mode: NULLABLE +- name: proportion_unreachable type: FLOAT -- mode: NULLABLE - name: proportion_terminated + mode: NULLABLE +- name: proportion_terminated type: FLOAT -- mode: NULLABLE - name: proportion_channel_open + mode: NULLABLE +- name: proportion_channel_open type: FLOAT -- mode: NULLABLE - name: avg_dns_success_time + mode: NULLABLE +- name: avg_dns_success_time type: FLOAT -- mode: NULLABLE - name: missing_dns_success + mode: NULLABLE +- name: missing_dns_success type: FLOAT -- mode: NULLABLE - name: avg_dns_failure_time + mode: NULLABLE +- name: avg_dns_failure_time type: FLOAT -- mode: NULLABLE - name: missing_dns_failure + mode: NULLABLE +- name: missing_dns_failure type: FLOAT -- mode: NULLABLE - name: count_dns_failure + mode: NULLABLE +- name: count_dns_failure type: FLOAT -- mode: NULLABLE - name: ssl_error_prop + mode: NULLABLE +- name: ssl_error_prop type: FLOAT -- mode: NULLABLE - name: avg_tls_handshake_time + mode: NULLABLE +- name: avg_tls_handshake_time type: FLOAT + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_android/active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_android/active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/active_users/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -37,6 +37,7 @@ IFNULL(mozfun.bits28.days_since_seen(days_seen_bits) < 7, FALSE) AS is_weekly_user, IFNULL(mozfun.bits28.days_since_seen(days_seen_bits) < 28, FALSE) AS is_monthly_user, FALSE AS is_mobile, + FALSE AS is_desktop, -- Adding isp at the end because it's in different column index in baseline table for some products. -- This is to make sure downstream union works as intended. isp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_ios/active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_ios/active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_ios/active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_ios/active_users/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -37,6 +37,7 @@ IFNULL(mozfun.bits28.days_since_seen(days_seen_bits) < 7, FALSE) AS is_weekly_user, IFNULL(mozfun.bits28.days_since_seen(days_seen_bits) < 28, FALSE) AS is_monthly_user, FALSE AS is_mobile, + FALSE AS is_desktop, -- Adding isp at the end because it's in different column index in baseline table for some products. -- This is to make sure downstream union works as intended. isp, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-05-24 15:56:26.000000000 +0000 @@ -45,7 +45,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` UNION ALL SELECT submission_timestamp, @@ -55,7 +55,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1` UNION ALL SELECT submission_timestamp, @@ -582,7 +582,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -592,7 +592,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -673,7 +673,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -683,7 +683,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -764,7 +764,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -774,7 +774,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -2000,7 +2000,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_stable.non_interaction_v1` + `moz-fx-data-shared-prod.bedrock_stable.interaction_v1` UNION ALL SELECT submission_timestamp, @@ -2010,7 +2010,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_stable.interaction_v1` + `moz-fx-data-shared-prod.bedrock_stable.non_interaction_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-05-24 16:01:39.000000000 +0000 @@ -1,7 +1,10 @@ fields: -- type: DATETIME - name: build_hour -- type: INTEGER - name: geckoview_major_version -- type: INTEGER - name: n_pings +- name: build_hour + type: DATETIME + mode: NULLABLE +- name: geckoview_major_version + type: INTEGER + mode: NULLABLE +- name: n_pings + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:54:55.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:54:55.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-24 15:54:55.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-24 15:54:51.000000000 +0000 @@ -1,6 +1,4 @@ --- Query generated by ./bqetl generate search --- This file doesn't get overwritten by the generator. The generator output needs --- to be written to this file manually. +-- Query generated by bigquery-etl/search/mobile_search_clients_daily.py -- -- Older versions separate source and engine with an underscore instead of period -- Return array of form [source, engine] if key is valid, empty array otherwise @@ -498,7 +496,7 @@ metrics.counter.browser_total_uri_count, client_info.locale, FROM - org_mozilla_ios_klar.metrics AS org_mozilla_klar_metrics + org_mozilla_ios_klar.metrics AS org_mozilla_ios_klar_metrics ), fenix_baseline AS ( SELECT @@ -874,7 +872,6 @@ ANY_VALUE(sample_id) AS sample_id, udf.map_mode_last(ARRAY_CONCAT_AGG(experiments)) AS experiments, SUM(total_uri_count) AS total_uri_count, - CAST(NULL AS STRING) AS normalized_engine FROM combined_search_clients WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,9 +1,7 @@ fields: - name: postal_district_code type: STRING - mode: REQUIRED - description: One-character Canadian postal district code. + mode: NULLABLE - name: province_code type: STRING mode: NULLABLE - description: Two-character Canadian province/territory code (if any). diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,47 +1,28 @@ fields: - name: name - description: Official country name per ISO 3166 type: STRING - mode: REQUIRED + mode: NULLABLE - name: code - description: ISO 3166 alpha-2 country code type: STRING - mode: REQUIRED + mode: NULLABLE - name: code_3 - description: ISO 3166 alpha-3 country code type: STRING - mode: REQUIRED + mode: NULLABLE - name: region_name - description: Region name. These are based on the UN Statistics Division standard - country or area codes for statistical use (M49), but with the "Americas" region - split into "North America" and "South America". type: STRING - mode: REQUIRED + mode: NULLABLE - name: subregion_name - description: Sub-region name. These are based on UN Statistics Division standard - country or area codes for statistical use (M49), but with the "Latin America and the - Caribbean" and "Sub-Saharan Africa" sub-regions split into more specific - sub-regions. type: STRING - mode: REQUIRED + mode: NULLABLE - name: pocket_available_on_newtab - description: Whether Pocket is available on the newtab page in this country. Note - that Pocket might only be available in certain locales/languages within a country. - type: BOOL - mode: REQUIRED + type: BOOLEAN + mode: NULLABLE - name: mozilla_vpn_available - description: Whether Mozilla VPN is available in this country. - type: BOOL - mode: REQUIRED + type: BOOLEAN + mode: NULLABLE - name: sponsored_tiles_available_on_newtab - description: Whether sponsored tiles are available on the newtab page in this country. - Note that Pocket might only be available in certain locales/languages within a - country. - type: BOOL - mode: REQUIRED + type: BOOLEAN + mode: NULLABLE - name: ads_value_tier - description: Lowercase label detailing the monetary value tier that Mozilla Ads - assign to that region based on market size and our existing products, e.g., tier - 1, tier 2, etc. type: STRING - mode: REQUIRED + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,10 +1,7 @@ fields: - name: name - description: An alias for a country's name (including misspellings and alternate - encodings). type: STRING - mode: REQUIRED + mode: NULLABLE - name: code - description: ISO 3166 alpha-2 country code type: STRING - mode: REQUIRED + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,22 +1,22 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: incident + mode: NULLABLE +- name: incident type: STRING -- mode: NULLABLE - name: description + mode: NULLABLE +- name: description type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING -- mode: NULLABLE - name: product + mode: NULLABLE +- name: product type: STRING -- mode: NULLABLE - name: version + mode: NULLABLE +- name: version type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,27 +1,16 @@ fields: -- mode: NULLABLE - description: Hex value assigned to the TLS cipher, in format like "0x00,0x84"; note - some values are ranges or contain wildcards - name: value +- name: value type: STRING -- mode: NULLABLE - description: Human-readable name of the TLS cipher - name: description + mode: NULLABLE +- name: description type: STRING -- mode: NULLABLE - description: Any TLS cipher suite that is specified for use with DTLS MUST define - limits on the use of the associated AEAD function that preserves margins for both - confidentiality and integrity, as specified in [RFC-ietf-tls-dtls13-43] - name: dtls_ok + mode: NULLABLE +- name: dtls_ok type: BOOLEAN -- mode: NULLABLE - description: Whether the TLS cipher is recommended by the IETF. If an item is not - marked as "recommended", it does not necessarily mean that it is flawed; rather, - it indicates that the item either has not been through the IETF consensus process, - has limited applicability, or is intended only for specific use cases - name: recommended + mode: NULLABLE +- name: recommended type: BOOLEAN -- mode: NULLABLE - description: RFCs or associated reference material for the TLS cipher - name: reference + mode: NULLABLE +- name: reference type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,17 +1,13 @@ fields: - name: code_3 - description: ISO 639 alpha-3 language code. type: STRING - mode: REQUIRED + mode: NULLABLE - name: code_2 - description: ISO 639 alpha-2 language code (if any). type: STRING mode: NULLABLE - name: name - description: Language name. type: STRING - mode: REQUIRED + mode: NULLABLE - name: other_names - description: Other names for the language (if any). type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,19 +1,19 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: document_namespace + mode: NULLABLE +- name: document_namespace type: STRING -- mode: NULLABLE - name: document_type + mode: NULLABLE +- name: document_type type: STRING -- mode: NULLABLE - name: notes + mode: NULLABLE +- name: notes type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,25 +1,25 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: document_namespace + mode: NULLABLE +- name: document_namespace type: STRING -- mode: NULLABLE - name: document_type + mode: NULLABLE +- name: document_type type: STRING -- mode: NULLABLE - name: document_version + mode: NULLABLE +- name: document_version type: STRING -- mode: NULLABLE - name: path + mode: NULLABLE +- name: path type: STRING -- mode: NULLABLE - name: notes + mode: NULLABLE +- name: notes type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_document_namespaces_notes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_document_namespaces_notes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_document_namespaces_notes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_document_namespaces_notes_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,22 +1,22 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: document_namespace + mode: NULLABLE +- name: document_namespace type: STRING -- mode: NULLABLE - name: document_type + mode: NULLABLE +- name: document_type type: STRING -- mode: NULLABLE - name: document_version + mode: NULLABLE +- name: document_version type: STRING -- mode: NULLABLE - name: notes + mode: NULLABLE +- name: notes type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_schema_errors_notes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_schema_errors_notes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_schema_errors_notes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_schema_errors_notes_v1/schema.yaml 2024-05-24 16:00:56.000000000 +0000 @@ -1,22 +1,22 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: document_namespace + mode: NULLABLE +- name: document_namespace type: STRING -- mode: NULLABLE - name: document_type + mode: NULLABLE +- name: document_type type: STRING -- mode: NULLABLE - name: path + mode: NULLABLE +- name: path type: STRING -- mode: NULLABLE - name: notes + mode: NULLABLE +- name: notes type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/normal_distribution/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/normal_distribution/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/normal_distribution/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/normal_distribution/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,7 +1,7 @@ fields: - name: score type: NUMERIC - mode: REQUIRED + mode: NULLABLE - name: value type: NUMERIC - mode: REQUIRED + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/us_zip_code_prefixes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/us_zip_code_prefixes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/us_zip_code_prefixes_v1/schema.yaml 2024-05-24 15:51:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/us_zip_code_prefixes_v1/schema.yaml 2024-05-24 16:00:55.000000000 +0000 @@ -1,9 +1,7 @@ fields: - name: zip_code_prefix type: STRING - mode: REQUIRED - description: Three-digit US ZIP code prefix. + mode: NULLABLE - name: state_code type: STRING mode: NULLABLE - description: Two-character US state/territory code (if any). diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/metadata.yaml 2024-05-24 16:04:15.000000000 +0000 @@ -0,0 +1,19 @@ +friendly_name: Active Users Mobile +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:dataops-managed/taar + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.fenix.active_users + - moz-fx-data-shared-prod.firefox_ios.active_users + - moz-fx-data-shared-prod.focus_android.active_users + - moz-fx-data-shared-prod.focus_ios.active_users + - moz-fx-data-shared-prod.klar_android.active_users + - moz-fx-data-shared-prod.klar_ios.active_users diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/active_users_mobile/view.sql 2024-05-24 15:54:52.000000000 +0000 @@ -0,0 +1,33 @@ +-- Query generated via `kpi_support_metrics` SQL generator. +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.telemetry.active_users_mobile` +AS +SELECT + *, +FROM + `moz-fx-data-shared-prod.fenix.active_users` +UNION ALL +SELECT + *, +FROM + `moz-fx-data-shared-prod.focus_android.active_users` +UNION ALL +SELECT + *, +FROM + `moz-fx-data-shared-prod.firefox_ios.active_users` +UNION ALL +SELECT + *, +FROM + `moz-fx-data-shared-prod.focus_ios.active_users` +UNION ALL +SELECT + *, +FROM + `moz-fx-data-shared-prod.klar_ios.active_users` +UNION ALL +SELECT + *, +FROM + `moz-fx-data-shared-prod.klar_android.active_users` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement/schema.yaml 2024-05-24 16:01:03.000000000 +0000 @@ -74,15 +74,12 @@ - name: dau type: INTEGER mode: NULLABLE - description: DAU - Daily Active User - name: wau type: INTEGER mode: NULLABLE - description: WAU - Weekly Active User - name: mau type: INTEGER mode: NULLABLE - description: MAU - Monthly Active User - name: lifecycle_stage type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement_clients/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement_clients/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement_clients/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_engagement_clients/schema.yaml 2024-05-24 16:01:07.000000000 +0000 @@ -85,15 +85,12 @@ - name: is_dau type: BOOLEAN mode: NULLABLE - description: DAU - Daily Active User - name: is_wau type: BOOLEAN mode: NULLABLE - description: WAU - Weekly Active User - name: is_mau type: BOOLEAN mode: NULLABLE - description: MAU - Monthly Active User - name: lifecycle_stage type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention/schema.yaml 2024-05-24 16:01:00.000000000 +0000 @@ -54,31 +54,24 @@ - name: ping_sent_metric_date type: INTEGER mode: NULLABLE - description: Count of Pings Sent on Metric Date - name: ping_sent_week_4 type: INTEGER mode: NULLABLE - description: Count of Pings Sent on Week 4 - name: active_metric_date type: INTEGER mode: NULLABLE - description: Count of Clients Active on Metric Date - name: retained_week_4 type: INTEGER mode: NULLABLE - description: Count of Clients Retained on Week 4 - name: retained_week_4_new_profiles type: INTEGER mode: NULLABLE - description: Count of New Profiles Retained on Week 4 - name: new_profiles_metric_date type: INTEGER mode: NULLABLE - description: Count of New Profiles on Metric Date - name: repeat_profiles type: INTEGER mode: NULLABLE - description: Count of Repeat Profiles - name: attribution_experiment type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/metadata.yaml 2024-05-24 16:04:15.000000000 +0000 @@ -0,0 +1,14 @@ +friendly_name: Desktop Retention Clients +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:dataops-managed/taar + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.telemetry_derived.desktop_retention_clients_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/desktop_retention_clients/view.sql 2024-05-24 15:53:05.000000000 +0000 @@ -0,0 +1,18 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.telemetry.desktop_retention_clients` +AS +SELECT + *, + CASE + WHEN first_seen_date = metric_date + THEN 'new_profile' + WHEN DATE_DIFF(metric_date, first_seen_date, DAY) + BETWEEN 1 + AND 27 + THEN 'repeat_user' + WHEN DATE_DIFF(metric_date, first_seen_date, DAY) >= 28 + THEN 'existing_user' + ELSE 'Unknown' + END AS lifecycle_stage, +FROM + `moz-fx-data-shared-prod.telemetry_derived.desktop_retention_clients_v1` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/metadata.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,19 +0,0 @@ -friendly_name: Mobile Active Users -description: |- - Please provide a description for the query -owners: [] -labels: {} -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:dataops-managed/taar - - workgroup:mozilla-confidential -references: - view.sql: - - moz-fx-data-shared-prod.fenix.active_users - - moz-fx-data-shared-prod.firefox_ios.active_users - - moz-fx-data-shared-prod.focus_android.active_users - - moz-fx-data-shared-prod.focus_ios.active_users - - moz-fx-data-shared-prod.klar_android.active_users - - moz-fx-data-shared-prod.klar_ios.active_users diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/view.sql 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_active_users/view.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,33 +0,0 @@ --- Query generated via `kpi_support_metrics` SQL generator. -CREATE OR REPLACE VIEW - `moz-fx-data-shared-prod.telemetry.mobile_active_users` -AS -SELECT - *, -FROM - `moz-fx-data-shared-prod.fenix.active_users` -UNION ALL -SELECT - *, -FROM - `moz-fx-data-shared-prod.focus_android.active_users` -UNION ALL -SELECT - *, -FROM - `moz-fx-data-shared-prod.firefox_ios.active_users` -UNION ALL -SELECT - *, -FROM - `moz-fx-data-shared-prod.focus_ios.active_users` -UNION ALL -SELECT - *, -FROM - `moz-fx-data-shared-prod.klar_ios.active_users` -UNION ALL -SELECT - *, -FROM - `moz-fx-data-shared-prod.klar_android.active_users` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/releases_latest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/releases_latest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/releases_latest/schema.yaml 2024-05-24 15:52:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/releases_latest/schema.yaml 2024-05-24 16:01:05.000000000 +0000 @@ ```⚠️ Only part of the diff is displayed.
Checklist for reviewer:
<username>:<branch>of the fork as parameter. The parameter will also show up in the logs of themanual-trigger-required-for-forkCI task together with more detailed instructions.For modifications to schemas in restricted namespaces (see
CODEOWNERS):┆Issue is synchronized with this Jira Task