dataops-ci-bot
commented
1 month ago Integration report for "Add registration funnels using legacy events"
sql.diff
Click to expand!
```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_fxa_events.py /tmp/workspace/generated-sql/dags/bqetl_fxa_events.py --- /tmp/workspace/main-generated-sql/dags/bqetl_fxa_events.py 2024-05-30 22:50:42.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_fxa_events.py 2024-05-30 23:01:49.000000000 +0000 @@ -142,6 +142,13 @@ ) ExternalTaskMarker( + task_id="bqetl_generated_funnels__wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", + external_dag_id="bqetl_generated_funnels", + external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=73800)).isoformat() }}", + ) + + ExternalTaskMarker( task_id="bqetl_event_rollup__wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", external_dag_id="bqetl_event_rollup", external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", @@ -176,6 +183,13 @@ ) ExternalTaskMarker( + task_id="bqetl_generated_funnels__wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", + external_dag_id="bqetl_generated_funnels", + external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=73800)).isoformat() }}", + ) + + ExternalTaskMarker( task_id="bqetl_event_rollup__wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", external_dag_id="bqetl_event_rollup", external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", @@ -221,6 +235,13 @@ ) ExternalTaskMarker( + task_id="bqetl_generated_funnels__wait_for_firefox_accounts_derived__fxa_stdout_events__v1", + external_dag_id="bqetl_generated_funnels", + external_task_id="wait_for_firefox_accounts_derived__fxa_stdout_events__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=73800)).isoformat() }}", + ) + + ExternalTaskMarker( task_id="bqetl_event_rollup__wait_for_firefox_accounts_derived__fxa_stdout_events__v1", external_dag_id="bqetl_event_rollup", external_task_id="wait_for_firefox_accounts_derived__fxa_stdout_events__v1", diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py --- /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py 2024-05-30 22:50:42.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-05-30 23:01:54.000000000 +0000 @@ -89,6 +89,42 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1 = ExternalTaskSensor( + task_id="wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", + external_dag_id="bqetl_fxa_events", + external_task_id="firefox_accounts_derived__fxa_gcp_stderr_events__v1", + execution_delta=datetime.timedelta(seconds=12600), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + + wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1 = ExternalTaskSensor( + task_id="wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", + external_dag_id="bqetl_fxa_events", + external_task_id="firefox_accounts_derived__fxa_gcp_stdout_events__v1", + execution_delta=datetime.timedelta(seconds=12600), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + + wait_for_firefox_accounts_derived__fxa_stdout_events__v1 = ExternalTaskSensor( + task_id="wait_for_firefox_accounts_derived__fxa_stdout_events__v1", + external_dag_id="bqetl_fxa_events", + external_task_id="firefox_accounts_derived__fxa_stdout_events__v1", + execution_delta=datetime.timedelta(seconds=12600), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + accounts_frontend_derived__email_first_reg_login_funnels__v1 = bigquery_etl_query( task_id="accounts_frontend_derived__email_first_reg_login_funnels__v1", destination_table="email_first_reg_login_funnels_v1", @@ -196,6 +232,23 @@ depends_on_past=False, ) + firefox_accounts_derived__registration_funnels_legacy_events__v1 = ( + bigquery_etl_query( + task_id="firefox_accounts_derived__registration_funnels_legacy_events__v1", + destination_table="registration_funnels_legacy_events_v1", + dataset_id="firefox_accounts_derived", + project_id="moz-fx-data-shared-prod", + owner="ksiegler@mozilla.org", + email=[ + "ascholtz@mozilla.com", + "ksiegler@mozilla.org", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + ) + monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1 = bigquery_etl_query( task_id="monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1", destination_table="monitor_dashboard_user_journey_funnels_v1", @@ -245,6 +298,18 @@ wait_for_fenix_derived__funnel_retention_clients_week_4__v1 ) + firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( + wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1 + ) + + firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( + wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1 + ) + + firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( + wait_for_firefox_accounts_derived__fxa_stdout_events__v1 + ) + monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1.set_upstream( wait_for_copy_deduplicate_all ) Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived: registration_funnels_legacy_events_v1 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-30 22:47:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:49:35.000000000 +0000 @@ -50,7 +50,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, @@ -60,7 +60,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` UNION ALL SELECT submission_timestamp, 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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-30 22:54:22.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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-30 22:54:22.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/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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-30 22:54:48.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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-30 22:54:49.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/firefox_accounts_derived/registration_funnels_legacy_events_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/metadata.yaml 2024-05-30 22:58:45.000000000 +0000 @@ -0,0 +1,26 @@ +friendly_name: Registration Funnels Legacy Events +description: |- + Please provide a description for the query +owners: +- ksiegler@mozilla.org +labels: + incremental: true + dag: bqetl_generated_funnels + owner1: ksiegler +scheduling: + dag_name: bqetl_generated_funnels +bigquery: + time_partitioning: + type: day + field: submission_date + require_partition_filter: false + expiration_days: null + range_partitioning: null + clustering: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + query.sql: + - mozdata.firefox_accounts.fxa_all_events diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived/registration_funnels_legacy_events_v1/query.sql 2024-05-30 22:54:10.000000000 +0000 @@ -0,0 +1,571 @@ +-- extract the relevant fields for each funnel step and segment if necessary +WITH registration_overall_success_by_service_reg_view AS ( + SELECT + flow_id AS join_key, + service AS service, + country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - view' +), +registration_overall_success_by_service_reg_complete AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + registration_overall_success_by_service_reg_view AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - complete' +), +registration_email_confirmation_overall_success_by_service_reg_view AS ( + SELECT + flow_id AS join_key, + service AS service, + country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - view' +), +registration_email_confirmation_overall_success_by_service_reg_email_code_view AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + registration_email_confirmation_overall_success_by_service_reg_view AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - signup_code_view' +), +registration_email_confirmation_overall_success_by_service_reg_complete AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + registration_email_confirmation_overall_success_by_service_reg_email_code_view AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - complete' +), +google_reg_third_party_auth_completions_google_signin_complete AS ( + SELECT + flow_id AS join_key, + service AS service, + country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_third_party_auth - google_signin_complete' +), +google_reg_third_party_auth_completions_reg_complete AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + google_reg_third_party_auth_completions_google_signin_complete AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - complete' +), +google_login_third_party_auth_completions_google_signin_complete AS ( + SELECT + flow_id AS join_key, + service AS service, + country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_third_party_auth - google_signin_complete' +), +google_login_third_party_auth_completions_login_complete AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + google_login_third_party_auth_completions_google_signin_complete AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_login - complete' +), +apple_reg_third_party_auth_completions_apple_signin_complete AS ( + SELECT + flow_id AS join_key, + service AS service, + country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_third_party_auth - apple_signin_complete' +), +apple_reg_third_party_auth_completions_reg_complete AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + apple_reg_third_party_auth_completions_apple_signin_complete AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_reg - complete' +), +apple_login_third_party_auth_completions_apple_signin_complete AS ( + SELECT + flow_id AS join_key, + service AS service, + country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_third_party_auth - apple_signin_complete' +), +apple_login_third_party_auth_completions_login_complete AS ( + SELECT + flow_id AS join_key, + prev.service AS service, + prev.country AS country, + DATE(timestamp) AS submission_date, + user_id AS client_id, + flow_id AS column + FROM + mozdata.firefox_accounts.fxa_all_events + INNER JOIN + apple_login_third_party_auth_completions_apple_signin_complete AS prev + ON prev.submission_date = DATE(timestamp) + AND prev.join_key = flow_id + WHERE + {% if is_init() %} + DATE(timestamp) >= DATE("2023-01-01") + {% else %} + DATE(timestamp) = @submission_date + {% endif %} + AND event_type = 'fxa_login - complete' +), +-- aggregate each funnel step value +registration_overall_success_by_service_reg_view_aggregated AS ( + SELECT + submission_date, + "registration_overall_success_by_service" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + registration_overall_success_by_service_reg_view + GROUP BY + service, + country, + submission_date, + funnel +), +registration_overall_success_by_service_reg_complete_aggregated AS ( + SELECT + submission_date, + "registration_overall_success_by_service" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + registration_overall_success_by_service_reg_complete + GROUP BY + service, + country, + submission_date, + funnel +), +registration_email_confirmation_overall_success_by_service_reg_view_aggregated AS ( + SELECT + submission_date, + "registration_email_confirmation_overall_success_by_service" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + registration_email_confirmation_overall_success_by_service_reg_view + GROUP BY + service, + country, + submission_date, + funnel +), +registration_email_confirmation_overall_success_by_service_reg_email_code_view_aggregated AS ( + SELECT + submission_date, + "registration_email_confirmation_overall_success_by_service" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + registration_email_confirmation_overall_success_by_service_reg_email_code_view + GROUP BY + service, + country, + submission_date, + funnel +), +registration_email_confirmation_overall_success_by_service_reg_complete_aggregated AS ( + SELECT + submission_date, + "registration_email_confirmation_overall_success_by_service" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + registration_email_confirmation_overall_success_by_service_reg_complete + GROUP BY + service, + country, + submission_date, + funnel +), +google_reg_third_party_auth_completions_google_signin_complete_aggregated AS ( + SELECT + submission_date, + "google_reg_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + google_reg_third_party_auth_completions_google_signin_complete + GROUP BY + service, + country, + submission_date, + funnel +), +google_reg_third_party_auth_completions_reg_complete_aggregated AS ( + SELECT + submission_date, + "google_reg_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + google_reg_third_party_auth_completions_reg_complete + GROUP BY + service, + country, + submission_date, + funnel +), +google_login_third_party_auth_completions_google_signin_complete_aggregated AS ( + SELECT + submission_date, + "google_login_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + google_login_third_party_auth_completions_google_signin_complete + GROUP BY + service, + country, + submission_date, + funnel +), +google_login_third_party_auth_completions_login_complete_aggregated AS ( + SELECT + submission_date, + "google_login_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + google_login_third_party_auth_completions_login_complete + GROUP BY + service, + country, + submission_date, + funnel +), +apple_reg_third_party_auth_completions_apple_signin_complete_aggregated AS ( + SELECT + submission_date, + "apple_reg_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + apple_reg_third_party_auth_completions_apple_signin_complete + GROUP BY + service, + country, + submission_date, + funnel +), +apple_reg_third_party_auth_completions_reg_complete_aggregated AS ( + SELECT + submission_date, + "apple_reg_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + apple_reg_third_party_auth_completions_reg_complete + GROUP BY + service, + country, + submission_date, + funnel +), +apple_login_third_party_auth_completions_apple_signin_complete_aggregated AS ( + SELECT + submission_date, + "apple_login_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + apple_login_third_party_auth_completions_apple_signin_complete + GROUP BY + service, + country, + submission_date, + funnel +), +apple_login_third_party_auth_completions_login_complete_aggregated AS ( + SELECT + submission_date, + "apple_login_third_party_auth_completions" AS funnel, + service, + country, + COUNT(DISTINCT column) AS aggregated + FROM + apple_login_third_party_auth_completions_login_complete + GROUP BY + service, + country, + submission_date, + funnel +), +-- merge all funnels so results can be written into one table +merged_funnels AS ( + SELECT + COALESCE( + registration_overall_success_by_service_reg_view_aggregated.service, + registration_email_confirmation_overall_success_by_service_reg_view_aggregated.service, + google_reg_third_party_auth_completions_google_signin_complete_aggregated.service, + google_login_third_party_auth_completions_google_signin_complete_aggregated.service, + apple_reg_third_party_auth_completions_apple_signin_complete_aggregated.service, + apple_login_third_party_auth_completions_apple_signin_complete_aggregated.service + ) AS service, + COALESCE( + registration_overall_success_by_service_reg_view_aggregated.country, + registration_email_confirmation_overall_success_by_service_reg_view_aggregated.country, + google_reg_third_party_auth_completions_google_signin_complete_aggregated.country, + google_login_third_party_auth_completions_google_signin_complete_aggregated.country, + apple_reg_third_party_auth_completions_apple_signin_complete_aggregated.country, + apple_login_third_party_auth_completions_apple_signin_complete_aggregated.country + ) AS country, + submission_date, + funnel, + COALESCE( + registration_overall_success_by_service_reg_view_aggregated.aggregated, + registration_email_confirmation_overall_success_by_service_reg_view_aggregated.aggregated, + NULL, + NULL, + NULL, + NULL + ) AS reg_view, + COALESCE( + NULL, + registration_email_confirmation_overall_success_by_service_reg_email_code_view_aggregated.aggregated, + NULL, + NULL, + NULL, + NULL + ) AS reg_email_code_view, + COALESCE( + registration_overall_success_by_service_reg_complete_aggregated.aggregated, + registration_email_confirmation_overall_success_by_service_reg_complete_aggregated.aggregated, + google_reg_third_party_auth_completions_reg_complete_aggregated.aggregated, + NULL, + apple_reg_third_party_auth_completions_reg_complete_aggregated.aggregated, + NULL + ) AS reg_complete, + COALESCE( + NULL, + NULL, + NULL, + google_login_third_party_auth_completions_login_complete_aggregated.aggregated, + NULL, + apple_login_third_party_auth_completions_login_complete_aggregated.aggregated + ) AS login_complete, + COALESCE( + NULL, + NULL, + google_reg_third_party_auth_completions_google_signin_complete_aggregated.aggregated, + google_login_third_party_auth_completions_google_signin_complete_aggregated.aggregated, + NULL, + NULL + ) AS google_signin_complete, + COALESCE( + NULL, + NULL, + NULL, + NULL, + apple_reg_third_party_auth_completions_apple_signin_complete_aggregated.aggregated, + apple_login_third_party_auth_completions_apple_signin_complete_aggregated.aggregated + ) AS apple_signin_complete, + FROM + registration_overall_success_by_service_reg_view_aggregated + FULL OUTER JOIN + registration_overall_success_by_service_reg_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + registration_email_confirmation_overall_success_by_service_reg_view_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + registration_email_confirmation_overall_success_by_service_reg_email_code_view_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + registration_email_confirmation_overall_success_by_service_reg_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + google_reg_third_party_auth_completions_google_signin_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + google_reg_third_party_auth_completions_reg_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + google_login_third_party_auth_completions_google_signin_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + google_login_third_party_auth_completions_login_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + apple_reg_third_party_auth_completions_apple_signin_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + apple_reg_third_party_auth_completions_reg_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + apple_login_third_party_auth_completions_apple_signin_complete_aggregated + USING (submission_date, service, country, funnel) + FULL OUTER JOIN + apple_login_third_party_auth_completions_login_complete_aggregated + USING (submission_date, service, country, funnel) +) +SELECT + * +FROM + merged_funnels 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-30 22:47: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-30 22:49:36.000000000 +0000 @@ -60,7 +60,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.prototype_no_code_events_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.firefox_desktop_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` UNION ALL SELECT submission_timestamp, @@ -80,7 +80,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.prototype_no_code_events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` ) CROSS JOIN UNNEST(events) AS event, 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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml 2024-05-30 22:56:05.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/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-30 22:47:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-05-30 22:51:14.000000000 +0000 @@ -55,7 +55,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.prototype_no_code_events_v1` UNION ALL SELECT submission_timestamp, @@ -65,7 +65,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, @@ -75,7 +75,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.prototype_no_code_events_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -572,7 +572,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` 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, @@ -663,7 +663,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` UNION ALL SELECT submission_timestamp, @@ -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, @@ -754,7 +754,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` UNION ALL SELECT submission_timestamp, @@ -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, @@ -1555,7 +1555,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_stable.daemonsession_v1` + `moz-fx-data-shared-prod.mozillavpn_stable.vpnsession_v1` UNION ALL SELECT submission_timestamp, @@ -1565,7 +1565,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_stable.main_v1` + `moz-fx-data-shared-prod.mozillavpn_stable.daemonsession_v1` UNION ALL SELECT submission_timestamp, @@ -1575,7 +1575,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_stable.vpnsession_v1` + `moz-fx-data-shared-prod.mozillavpn_stable.main_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1646,7 +1646,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.daemonsession_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.vpnsession_v1` UNION ALL SELECT submission_timestamp, @@ -1656,7 +1656,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.main_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.daemonsession_v1` UNION ALL SELECT submission_timestamp, @@ -1666,7 +1666,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.main_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1737,7 +1737,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.daemonsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.vpnsession_v1` UNION ALL SELECT submission_timestamp, @@ -1747,7 +1747,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.daemonsession_v1` UNION ALL SELECT submission_timestamp, @@ -1757,7 +1757,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.main_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1828,7 +1828,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.daemonsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.vpnsession_v1` UNION ALL SELECT submission_timestamp, @@ -1838,7 +1838,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.daemonsession_v1` UNION ALL SELECT submission_timestamp, @@ -1848,7 +1848,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.main_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1990,7 +1990,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, @@ -2000,7 +2000,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` UNION ALL SELECT submission_timestamp, @@ -2081,7 +2081,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.viu_politica_stable.video_index_v1` + `moz-fx-data-shared-prod.viu_politica_stable.main_events_v1` UNION ALL SELECT submission_timestamp, @@ -2091,7 +2091,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.viu_politica_stable.main_events_v1` + `moz-fx-data-shared-prod.viu_politica_stable.video_index_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:47:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:49:35.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_live.daemonsession_v1` + `moz-fx-data-shared-prod.mozillavpn_live.vpnsession_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.mozillavpn_live.main_v1` + `moz-fx-data-shared-prod.mozillavpn_live.daemonsession_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.mozillavpn_live.vpnsession_v1` + `moz-fx-data-shared-prod.mozillavpn_live.main_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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-05-30 22:54:33.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_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:47:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:49:37.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.daemonsession_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.vpnsession_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.org_mozilla_firefox_vpn_live.main_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.daemonsession_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_firefox_vpn_live.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.main_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_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-30 22:47:35.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-30 22:49:38.000000000 +0000 @@ -50,7 +50,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` UNION ALL SELECT submission_timestamp, @@ -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, 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-30 22:47:35.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-30 22:49:38.000000000 +0000 @@ -50,7 +50,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` UNION ALL SELECT submission_timestamp, @@ -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, 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-30 22:47:35.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-30 22:49:38.000000000 +0000 @@ -50,7 +50,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` UNION ALL SELECT submission_timestamp, @@ -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, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:47:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:49:39.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.daemonsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.vpnsession_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.org_mozilla_ios_firefoxvpn_live.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.daemonsession_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_firefoxvpn_live.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.main_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_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:47:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-30 22:49:39.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.daemonsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.vpnsession_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.org_mozilla_ios_firefoxvpn_network_extension_live.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.daemonsession_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_firefoxvpn_network_extension_live.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.main_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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-30 22:49:34.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 @@ -722,12 +720,6 @@ SUBSTR(search.key, STRPOS(search.key, '.') + 1), search.search_type ) - WHEN search.search_type = 'search-with-ads' - THEN IF( - REGEXP_CONTAINS(search.key, '\\.'), - SUBSTR(search.key, STRPOS(search.key, '.') + 1), - search.search_type - ) ELSE search.search_type END AS source, search.value AS search_count, @@ -780,8 +772,6 @@ CASE WHEN search_type = 'ad-click' THEN IF(STARTS_WITH(source, 'in-content.organic'), 'ad-click-organic', search_type) - WHEN search_type = 'search-with-ads' - THEN IF(STARTS_WITH(source, 'in-content.organic'), 'search-with-ads-organic', search_type) WHEN STARTS_WITH(source, 'in-content.sap.') THEN 'tagged-sap' WHEN REGEXP_CONTAINS(source, '^in-content.*-follow-on') @@ -864,15 +854,6 @@ ) ) AS search_with_ads, SUM( - IF( - search_type != 'search-with-ads-organic' - OR engine IS NULL - OR search_count > 10000, - 0, - search_count - ) - ) AS search_with_ads_organic, - SUM( IF(search_type != 'unknown' OR engine IS NULL OR search_count > 10000, 0, search_count) ) AS unknown, udf.mode_last(ARRAY_AGG(country)) AS country, @@ -891,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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml 2024-05-30 22:54:42.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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml 2024-05-30 22:54:43.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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml 2024-05-30 22:54:43.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-30 22:47:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml 2024-05-30 22:54:42.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_tl ```⚠️ 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