Revert "adding script to land files in GCS for merino migration"

[scholtzan]

See https://mozilla.slack.com/archives/C01E8GDG80N/p1723478782891329?thread_ts=1723474424.129419&cid=C01E8GDG80N

Reverting to unblock artifact deploys

Reverts mozilla/bigquery-etl#6032

┆Issue is synchronized with this Jira Task

[dataops-ci-bot]

Integration report for "Revert "adding script to land files in GCS for merino migration (#6032)""

sql.diff

Click to expand!

```diff Only in /tmp/workspace/main-generated-sql/dags/: bqetl_merino_newtab_aggregates_to_gcs.py 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-08-12 16:12:30.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-08-12 16:26:54.000000000 +0000 @@ -288,6 +288,21 @@ ) ) + 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", + dataset_id="monitor_frontend_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, + ) + accounts_frontend_derived__email_first_reg_login_funnels_by_service__v1.set_upstream( wait_for_copy_deduplicate_all ) @@ -349,3 +364,7 @@ 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 + ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_merino_newtab_aggregates_to_gcs.py /tmp/workspace/generated-sql/dags/bqetl_merino_newtab_aggregates_to_gcs.py --- /tmp/workspace/main-generated-sql/dags/bqetl_merino_newtab_aggregates_to_gcs.py 2024-08-12 16:12:30.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_merino_newtab_aggregates_to_gcs.py 1970-01-01 00:00:00.000000000 +0000 @@ -1,81 +0,0 @@ -# Generated via https://github.com/mozilla/bigquery-etl/blob/main/bigquery_etl/query_scheduling/generate_airflow_dags.py - -from airflow import DAG -from airflow.sensors.external_task import ExternalTaskMarker -from airflow.sensors.external_task import ExternalTaskSensor -from airflow.utils.task_group import TaskGroup -import datetime -from operators.gcp_container_operator import GKEPodOperator -from utils.constants import ALLOWED_STATES, FAILED_STATES -from utils.gcp import bigquery_etl_query, bigquery_dq_check - -docs = """ -### bqetl_merino_newtab_aggregates_to_gcs - -Built from bigquery-etl repo, [`dags/bqetl_merino_newtab_aggregates_to_gcs.py`](https://github.com/mozilla/bigquery-etl/blob/generated-sql/dags/bqetl_merino_newtab_aggregates_to_gcs.py) - -#### Description - -Aggregates Newtab engagement data that lands in a GCS bucket for Merino recommendations. - -#### Owner - -cbeck@mozilla.com - -#### Tags - -* impact/tier_1 -* repo/bigquery-etl -""" - - -default_args = { - "owner": "cbeck@mozilla.com", - "start_date": datetime.datetime(2024, 8, 12, 0, 0), - "end_date": None, - "email": ["cbeck@mozilla.com", "gkatre@mozilla.com"], - "depends_on_past": False, - "retry_delay": datetime.timedelta(seconds=300), - "email_on_failure": True, - "email_on_retry": False, - "retries": 2, -} - -tags = ["impact/tier_1", "repo/bigquery-etl"] - -with DAG( - "bqetl_merino_newtab_aggregates_to_gcs", - default_args=default_args, - schedule_interval="*/20 * * * *", - doc_md=docs, - tags=tags, -) as dag: - - checks__fail_merino_external__merino_newtab_aggregates__v1 = bigquery_dq_check( - task_id="checks__fail_merino_external__merino_newtab_aggregates__v1", - source_table="merino_newtab_aggregates_v1", - dataset_id="merino_external", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="cbeck@mozilla.com", - email=["cbeck@mozilla.com", "gkatre@mozilla.com"], - depends_on_past=False, - task_concurrency=1, - retries=0, - ) - - merino_external__merino_newtab_aggregates__v1 = bigquery_etl_query( - task_id="merino_external__merino_newtab_aggregates__v1", - destination_table="merino_newtab_aggregates_v1", - dataset_id="merino_external", - project_id="moz-fx-data-shared-prod", - owner="cbeck@mozilla.com", - email=["cbeck@mozilla.com", "gkatre@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - - checks__fail_merino_external__merino_newtab_aggregates__v1.set_upstream( - merino_external__merino_newtab_aggregates__v1 - ) Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod: merino_external diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:12.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:13.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:13.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, 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-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:13.000000000 +0000 @@ -80,7 +80,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/burnham_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:13.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, 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-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-08-12 16:16:28.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-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-08-12 16:16:43.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/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:14.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, 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-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-08-12 16:16:50.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-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-08-12 16:16:58.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_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_defaultagent_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:14.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:14.000000000 +0000 @@ -70,7 +70,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:14.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, 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-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:14.000000000 +0000 @@ -90,7 +90,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_translations_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:15.000000000 +0000 @@ -70,7 +70,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-08-12 16:19:32.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - type: STRING mode: NULLABLE + type: STRING description: App ID of the channel data was received from - name: normalized_channel - type: STRING mode: NULLABLE + type: STRING description: Normalized channel name - name: additional_properties type: STRING diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/glean_dictionary_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:15.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/gleanjs_docs_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:15.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_page/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_page/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_page/schema.yaml 2024-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_page/schema.yaml 2024-08-12 16:17:05.000000000 +0000 @@ -6,9 +6,10 @@ - name: site_url type: STRING mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. + description: 'For domain properties, this will be `sc-domain:` followed by the domain + name. + + For URL-prefix properties, it will be the full URL of the property definition.' - name: site_domain_name type: STRING mode: NULLABLE @@ -28,19 +29,23 @@ - name: localized_site_code type: STRING mode: NULLABLE - description: Localized site code such as `en-US` or `de` found in the first segment of the page URL path (if any). + description: Localized site code such as `en-US` or `de` found in the first segment + of the page URL path (if any). - name: localized_site type: STRING mode: NULLABLE - description: Description of the localized site language and/or country based on `localized_site_code` (if any). + description: Description of the localized site language and/or country based on + `localized_site_code` (if any). - name: localized_site_language_code type: STRING mode: NULLABLE - description: Localized site language code in ISO-639-alpha-2 format found in the first segment of the page URL path (if any). + description: Localized site language code in ISO-639-alpha-2 format found in the + first segment of the page URL path (if any). - name: localized_site_language type: STRING mode: NULLABLE - description: Localized site language based on `localized_site_language_code` (if any). + description: Localized site language based on `localized_site_language_code` (if + any). - name: query type: STRING mode: NULLABLE @@ -48,20 +53,15 @@ - name: query_type type: STRING mode: NULLABLE - description: |- - Type of search query: - * Brand: Query contained one or more Mozilla brand keywords. - * Non-Brand: Query didn't contain any Mozilla brand keywords. - * Unknown: Query couldn't be classified. + description: "Type of search query:\n * Brand: Query contained one or more Mozilla\ + \ brand keywords.\n * Non-Brand: Query didn't contain any Mozilla brand keywords.\n\ + \ * Unknown: Query couldn't be classified." - name: search_type type: STRING mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. + description: "Where the link was seen by the user:\n * Web: In Google Search's\ + \ default \"All\" tab.\n * Image: In Google Search's \"Images\" tab.\n * Video:\ + \ In Google Search's \"Videos\" tab.\n * News: In Google Search's \"News\" tab." - name: user_country_code type: STRING mode: NULLABLE @@ -81,12 +81,13 @@ - name: device_type type: STRING mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. + description: 'The type of device on which the user was searching: Desktop, Mobile, + or Tablet.' - name: impressions type: INTEGER mode: NULLABLE - description: The number of times that search results with a link to the page were shown to a user. + description: The number of times that search results with a link to the page were + shown to a user. - name: clicks type: INTEGER mode: NULLABLE @@ -94,4 +95,5 @@ - name: average_position type: FLOAT mode: NULLABLE - description: The average position of the page in the search results, where `1` is the topmost position. + description: The average position of the page in the search results, where `1` is + the topmost position. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_site/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_site/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_site/schema.yaml 2024-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/limited_historical_search_impressions_by_site/schema.yaml 2024-08-12 16:17:06.000000000 +0000 @@ -6,9 +6,10 @@ - name: site_url type: STRING mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. + description: 'For domain properties, this will be `sc-domain:` followed by the domain + name. + + For URL-prefix properties, it will be the full URL of the property definition.' - name: site_domain_name type: STRING mode: NULLABLE @@ -20,20 +21,15 @@ - name: query_type type: STRING mode: NULLABLE - description: |- - Type of search query: - * Brand: Query contained one or more Mozilla brand keywords. - * Non-Brand: Query didn't contain any Mozilla brand keywords. - * Unknown: Query couldn't be classified. + description: "Type of search query:\n * Brand: Query contained one or more Mozilla\ + \ brand keywords.\n * Non-Brand: Query didn't contain any Mozilla brand keywords.\n\ + \ * Unknown: Query couldn't be classified." - name: search_type type: STRING mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. + description: "Where the link was seen by the user:\n * Web: In Google Search's\ + \ default \"All\" tab.\n * Image: In Google Search's \"Images\" tab.\n * Video:\ + \ In Google Search's \"Videos\" tab.\n * News: In Google Search's \"News\" tab." - name: user_country_code type: STRING mode: NULLABLE @@ -53,17 +49,20 @@ - name: device_type type: STRING mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. + description: 'The type of device on which the user was searching: Desktop, Mobile, + or Tablet.' - name: impressions type: INTEGER mode: NULLABLE - description: The number of times that search results with at least one link to the site were shown to a user. + description: The number of times that search results with at least one link to the + site were shown to a user. - name: clicks type: INTEGER mode: NULLABLE - description: The number of times a user clicked at least one search result link to the site. + description: The number of times a user clicked at least one search result link + to the site. - name: average_top_position type: FLOAT mode: NULLABLE - description: The average top position of the site in the search results, where `1` is the topmost position. + description: The average top position of the site in the search results, where `1` + is the topmost position. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml 2024-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml 2024-08-12 16:17:07.000000000 +0000 @@ -6,9 +6,10 @@ - name: site_url type: STRING mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. + description: 'For domain properties, this will be `sc-domain:` followed by the domain + name. + + For URL-prefix properties, it will be the full URL of the property definition.' - name: site_domain_name type: STRING mode: NULLABLE @@ -16,99 +17,104 @@ - name: page_url type: STRING mode: NULLABLE - description: |- - The final page URL linked by a search result after any skip redirects. - This will be null for anonymized Discover impressions. + description: 'The final page URL linked by a search result after any skip redirects. + + This will be null for anonymized Discover impressions.' - name: page_domain_name type: STRING mode: NULLABLE - description: |- - Domain name of the page URL. - This will be null for anonymized Discover impressions. + description: 'Domain name of the page URL. + + This will be null for anonymized Discover impressions.' - name: page_path type: STRING mode: NULLABLE - description: |- - The path part of the page URL. - This will be null for anonymized Discover impressions. + description: 'The path part of the page URL. + + This will be null for anonymized Discover impressions.' - name: localized_site_code type: STRING mode: NULLABLE - description: |- - Localized site code such as `en-US` or `de` found in the first segment of the page URL path (if any). - This will be null for anonymized Discover impressions. + description: 'Localized site code such as `en-US` or `de` found in the first segment + of the page URL path (if any). + + This will be null for anonymized Discover impressions.' - name: localized_site type: STRING mode: NULLABLE - description: |- - Description of the localized site language and/or country based on `localized_site_code` (if any). - This will be null for anonymized Discover impressions. + description: 'Description of the localized site language and/or country based on + `localized_site_code` (if any). + + This will be null for anonymized Discover impressions.' - name: localized_site_language_code type: STRING mode: NULLABLE - description: |- - Localized site language code in ISO-639-alpha-2 format found in the first segment of the page URL path (if any). - This will be null for anonymized Discover impressions. + description: 'Localized site language code in ISO-639-alpha-2 format found in the + first segment of the page URL path (if any). + + This will be null for anonymized Discover impressions.' - name: localized_site_language type: STRING mode: NULLABLE - description: |- - Localized site language based on `localized_site_language_code` (if any). - This will be null for anonymized Discover impressions. + description: 'Localized site language based on `localized_site_language_code` (if + any). + + This will be null for anonymized Discover impressions.' - name: query type: STRING mode: NULLABLE - description: |- - The search query. - This will be null for anonymized search impressions, and all Discover and Google News search impressions. + description: 'The search query. + + This will be null for anonymized search impressions, and all Discover and Google + News search impressions.' - name: query_type type: STRING mode: NULLABLE - description: |- - Type of search query: - * Anonymized: Query was redacted by Google to protect the users' privacy. - * Brand: Query contained one or more Mozilla brand keywords. - * Non-Brand: Query didn't contain any Mozilla brand keywords. - * Unknown: Query couldn't be classified. - This will be null for all Discover and Google News search impressions. + description: "Type of search query:\n * Anonymized: Query was redacted by Google\ + \ to protect the users' privacy.\n * Brand: Query contained one or more Mozilla\ + \ brand keywords.\n * Non-Brand: Query didn't contain any Mozilla brand keywords.\n\ + \ * Unknown: Query couldn't be classified.\nThis will be null for all Discover\ + \ and Google News search impressions." - name: is_anonymized type: BOOLEAN mode: NULLABLE - description: |- - Whether Google has anonymized the search impression to protect the users' privacy. + description: 'Whether Google has anonymized the search impression to protect the + users'' privacy. + The `query` field will be null for anonymized search impressions. - The `country_code`, `page_url`, and related fields will be null for anonymized Discover impressions. + + The `country_code`, `page_url`, and related fields will be null for anonymized + Discover impressions.' - name: has_good_page_experience type: BOOLEAN mode: NULLABLE - description: Whether Google Search considers the page to be providing a good page experience. + description: Whether Google Search considers the page to be providing a good page + experience. - name: search_type type: STRING mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. - * Discover: In Google's Discover feed. - * Google News: On news.google.com or in the Google News app on Android and iOS. + description: "Where the link was seen by the user:\n * Web: In Google Search's\ + \ default \"All\" tab.\n * Image: In Google Search's \"Images\" tab.\n * Video:\ + \ In Google Search's \"Videos\" tab.\n * News: In Google Search's \"News\" tab.\n\ + \ * Discover: In Google's Discover feed.\n * Google News: On news.google.com\ + \ or in the Google News app on Android and iOS." - name: search_appearance type: STRING mode: NULLABLE - description: How the search result appeared (e.g. normal result, translated result, video). + description: How the search result appeared (e.g. normal result, translated result, + video). - name: user_country_code type: STRING mode: NULLABLE - description: |- - Country from which the user was searching, in ISO-3166-1-alpha-3 format. - This will be null for anonymized Discover impressions. + description: 'Country from which the user was searching, in ISO-3166-1-alpha-3 format. + + This will be null for anonymized Discover impressions.' - name: user_country type: STRING mode: NULLABLE - description: |- - Country from which the user was searching. - This will be null for anonymized Discover impressions. + description: 'Country from which the user was searching. + + This will be null for anonymized Discover impressions.' - name: user_region type: STRING mode: NULLABLE @@ -120,13 +126,15 @@ - name: device_type type: STRING mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. - This will be null for Discover impressions. + description: 'The type of device on which the user was searching: Desktop, Mobile, + or Tablet. + + This will be null for Discover impressions.' - name: impressions type: INTEGER mode: NULLABLE - description: The number of times that search results with a link to the page were shown to a user. + description: The number of times that search results with a link to the page were + shown to a user. - name: clicks type: INTEGER mode: NULLABLE @@ -134,6 +142,7 @@ - name: average_position type: FLOAT mode: NULLABLE - description: |- - The average position of the page in the search results, where `1` is the topmost position. - This will be null for Discover and Google News search impressions. + description: 'The average position of the page in the search results, where `1` + is the topmost position. + + This will be null for Discover and Google News search impressions.' diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml 2024-08-12 16:07:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml 2024-08-12 16:17:09.000000000 +0000 @@ -6,9 +6,10 @@ - name: site_url type: STRING mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. + description: 'For domain properties, this will be `sc-domain:` followed by the domain + name. + + For URL-prefix properties, it will be the full URL of the property definition.' - name: site_domain_name type: STRING mode: NULLABLE @@ -20,27 +21,23 @@ - name: query_type type: STRING mode: NULLABLE - description: |- - Type of search query: - * Anonymized: Query was redacted by Google to protect the users' privacy. - * Brand: Query contained one or more Mozilla brand keywords. - * Non-Brand: Query didn't contain any Mozilla brand keywords. - * Unknown: Query couldn't be classified. + description: "Type of search query:\n * Anonymized: Query was redacted by Google\ + \ to protect the users' privacy.\n * Brand: Query contained one or more Mozilla\ + \ brand keywords.\n * Non-Brand: Query didn't contain any Mozilla brand keywords.\n\ + \ * Unknown: Query couldn't be classified." - name: is_anonymized type: BOOLEAN mode: NULLABLE - description: |- - Whether Google has anonymized the search impression to protect the users' privacy. - The `query` field will be null for anonymized search impressions. + description: 'Whether Google has anonymized the search impression to protect the + users'' privacy. + + The `query` field will be null for anonymized search impressions.' - name: search_type type: STRING mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. + description: "Where the link was seen by the user:\n * Web: In Google Search's\ + \ default \"All\" tab.\n * Image: In Google Search's \"Images\" tab.\n * Video:\ + \ In Google Search's \"Videos\" tab.\n * News: In Google Search's \"News\" tab." - name: user_country_code type: STRING mode: NULLABLE @@ -60,17 +57,20 @@ - name: device_type type: STRING mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. + description: 'The type of device on which the user was searching: Desktop, Mobile, + or Tablet.' - name: impressions type: INTEGER mode: NULLABLE - description: The number of times that search results with at least one link to the site were shown to a user. + description: The number of times that search results with at least one link to the + site were shown to a user. - name: clicks type: INTEGER mode: NULLABLE - description: The number of times a user clicked at least one search result link to the site. + description: The number of times a user clicked at least one search result link + to the site. - name: average_top_position type: FLOAT mode: NULLABLE - description: The average top position of the site in the search results, where `1` is the topmost position. + description: The average top position of the site in the search results, where `1` + is the topmost position. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml 2024-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml 2024-08-12 16:19:42.000000000 +0000 @@ -1,11 +1,11 @@ fields: - name: normalized_app_id - mode: NULLABLE type: STRING + mode: NULLABLE description: App ID of the channel data was received from - name: normalized_channel - mode: NULLABLE type: STRING + mode: NULLABLE description: Normalized channel name - name: additional_properties type: STRING diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mdn_yari_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:15.000000000 +0000 @@ -70,7 +70,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/dataset_metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/dataset_metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/dataset_metadata.yaml 2024-08-12 16:07:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/dataset_metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,10 +0,0 @@ -friendly_name: Merino External -description: |- - Data aggregated from Newtab engagements and exported to GCS for Merino consumption -dataset_base_acl: external -user_facing: false -labels: {} -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/checks.sql 2024-08-12 16:07:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/checks.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,16 +0,0 @@ --- macro checks - -#fail -{{ not_null(["scheduled_corpus_item_id"]) }} - -#fail -{{ is_unique(["scheduled_corpus_item_id"]) }} - -#fail -{{ not_null(["impression_count"]) }} - -#fail -{{ not_null(["click_count"]) }} - -#fail -{{ min_row_count(1) }} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/metadata.yaml 2024-08-12 16:09:25.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,28 +0,0 @@ -friendly_name: Merino Newtab Aggregates -description: |- - Aggregated Newtab engagement data for Merino recommendations. Used to export JSON objects to GCS. - See https://mozilla-hub.atlassian.net/browse/MC-1256 -owners: -- cbeck@mozilla.com -- gkatre@mozilla.com -labels: - owner: cbeck - dag: bqetl_merino_newtab_aggregates_to_gcs - owner1: cbeck - owner2: gkatre -scheduling: - dag_name: bqetl_merino_newtab_aggregates_to_gcs - date_partition_parameter: null -bigquery: - time_partitioning: null - range_partitioning: null - clustering: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential -references: - checks.sql: - - .. - query.sql: - - moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/query.sql 2024-08-12 16:07:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,47 +0,0 @@ -WITH deduplicated_pings AS ( - SELECT - submission_timestamp, - document_id, - events, - FROM - `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` - WHERE - submission_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) - QUALIFY - ROW_NUMBER() OVER ( - PARTITION BY - DATE(submission_timestamp), - document_id - ORDER BY - submission_timestamp DESC - ) = 1 -), -flattened_newtab_events AS ( - SELECT - document_id, - submission_timestamp, - unnested_events.name AS event_name, - mozfun.map.get_key( - unnested_events.extra, - 'scheduled_corpus_item_id' - ) AS scheduled_corpus_item_id, - mozfun.map.get_key(unnested_events.extra, 'position') AS position, - COUNT(1) OVER (PARTITION BY document_id, unnested_events.name) AS user_event_count - FROM - deduplicated_pings, - UNNEST(events) AS unnested_events - --filter to Pocket events - WHERE - unnested_events.category = 'pocket' - AND unnested_events.name IN ('impression', 'click', 'save', 'dismiss') - --keep only data with a non-null scheduled corpus item ID - AND (mozfun.map.get_key(unnested_events.extra, 'scheduled_corpus_item_id') IS NOT NULL) -) -SELECT - scheduled_corpus_item_id, - SUM(CASE WHEN event_name = 'impression' THEN 1 ELSE 0 END) AS impression_count, - SUM(CASE WHEN event_name = 'click' THEN 1 ELSE 0 END) AS click_count -FROM - flattened_newtab_events -GROUP BY - 1; diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/schema.yaml 2024-08-12 16:07:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/merino_external/merino_newtab_aggregates_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,10 +0,0 @@ -fields: -- mode: NULLABLE - name: scheduled_corpus_item_id - type: STRING -- mode: NULLABLE - name: impression_count - type: INTEGER -- mode: NULLABLE - name: click_count - type: INTEGER diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:15.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-08-12 16:11:16.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-08-10" + DATE(submission_timestamp) >= "2024-08-12" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/metadata.yaml 2024-08-12 16:08:46.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/metadata.yaml 2024-08-12 16:22:19.000000000 +0000 @@ -1,10 +1,19 @@ +friendly_name: Event Monitoring Live +description: |- + Materialized view of experimentation related events + coming from monitor_frontend. +owners: +- ascholtz@mozilla.com +- akomar@mozilla.com +labels: + materialized_view: true + owner1: ascholtz + owner2: akomar +bigquery: null workgroup_access: - role: roles/bigquery.dataViewer members: - workgroup:mozilla-confidential - - workgroup:dataops-managed/external-fides - -# Generated by bigquery_etl.dependency references: materialized_view.sql: - moz-fx-data-shared-prod.monitor_frontend_live.events_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/monitor_dashboard_user_journey_funnels_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/monitor_dashboard_user_journey_funnels_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/monitor_dashboard_user_journey_funnels_v1/metadata.yaml 2024-08-12 16:08:46.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/monitor_dashboard_user_journey_funnels_v1/metadata.yaml 2024-08-12 16:22:19.000000000 +0000 @@ -1,10 +1,26 @@ +friendly_name: Monitor Dashboard User Journey Funnels +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 - - workgroup:dataops-managed/external-fides - -# Generated by bigquery_etl.dependency references: query.sql: - mozdata.monitor_frontend.events_unnested diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/airflow_trigger/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/airflow_trigger/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/airflow_trigger/schema.yaml 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/airflow_trigger/schema.yaml 2024-08-12 16:16:22.000000000 +0000 @@ -17,11 +17,3 @@ - name: triggerer_id type: INTEGER mode: NULLABLE -- name: trigger_id - type: INTEGER - mode: NULLABLE - description: null -- name: created_at - type: TIMESTAMP - mode: NULLABLE - description: null diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql 2024-08-12 16:08:14.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql 2024-08-12 16:16:17.000000000 +0000 @@ -1,4 +1,4 @@ -WITH first_partition_org_mozilla_firefox_stable AS ( +WITH first_partition_firefox_launcher_process_stable AS ( SELECT table_catalog, table_schema, @@ -6,25 +6,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_launcher_process_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_firefox_stable AS ( +first_non_empty_partition_firefox_launcher_process_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_launcher_process_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_lockbox_stable AS ( +first_partition_org_mozilla_fenix_stable AS ( SELECT table_catalog, table_schema, @@ -32,25 +32,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_ios_lockbox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_fenix_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_ios_lockbox_stable AS ( +first_non_empty_partition_org_mozilla_fenix_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.org_mozilla_ios_lockbox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_fenix_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_pocket_stable AS ( +first_partition_mozillavpn_stable AS ( SELECT table_catalog, table_schema, @@ -58,25 +58,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.pocket_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozillavpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_pocket_stable AS ( +first_non_empty_partition_mozillavpn_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.pocket_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozillavpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_pine_stable AS ( +first_partition_org_mozilla_bergamot_stable AS ( SELECT table_catalog, table_schema, @@ -84,25 +84,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.pine_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_pine_stable AS ( +first_non_empty_partition_org_mozilla_bergamot_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.pine_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_contextual_services_stable AS ( +first_partition_org_mozilla_connect_firefox_stable AS ( SELECT table_catalog, table_schema, @@ -110,25 +110,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.contextual_services_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_connect_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_contextual_services_stable AS ( +first_non_empty_partition_org_mozilla_connect_firefox_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.contextual_services_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_connect_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_eng_workflow_stable AS ( +first_partition_org_mozilla_ios_firefoxbeta_stable AS ( SELECT table_catalog, table_schema, @@ -136,25 +136,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.eng_workflow_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_eng_workflow_stable AS ( +first_non_empty_partition_org_mozilla_ios_firefoxbeta_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.eng_workflow_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_activity_stream_stable AS ( +first_partition_org_mozilla_ios_firefoxvpn_stable AS ( SELECT table_catalog, table_schema, @@ -162,25 +162,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.activity_stream_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_activity_stream_stable AS ( +first_non_empty_partition_org_mozilla_ios_firefoxvpn_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_partition_current, FROM - `moz-fx-data-shared-prod.activity_stream_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_connect_firefox_stable AS ( +first_partition_ads_backend_stable AS ( SELECT table_catalog, table_schema, @@ -188,25 +188,25 @@ PARSE_DATE("%Y%m%d", partition_id) AS first_partition_current, total_rows AS first_partition_row_count, FROM - `moz-fx-data-shared-prod.org_mozilla_connect_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.ads_backend_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' QUALIFY ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY partition_id) = 1 ), -first_non_empty_partition_org_mozilla_connect_firefox_stable AS ( +first_non_empty_partition_ads_backend_stable AS ( SELECT table_ ```

⚠️ Only part of the diff is displayed.

Link to full diff