DENG-4481 Add profile_group_id to telemetry_derived.clients_first_see…

[kwindau]

…n_v2

Checklist for reviewer:

• [ ] Commits should reference a bug or github issue, if relevant (if a bug is referenced, the pull request should include the bug number in the title).
• [ ] If the PR comes from a fork, trigger integration CI tests by running the Push to upstream workflow and provide the <username>:<branch> of the fork as parameter. The parameter will also show up in the logs of the manual-trigger-required-for-fork CI task together with more detailed instructions.
• [ ] If adding a new field to a query, ensure that the schema and dependent downstream schemas have been updated.
• [ ] When adding a new derived dataset, ensure that data is not available already (fully or partially) and recommend extending an existing dataset in favor of creating new ones. Data can be available in the bigquery-etl repository, looker-hub or in looker-spoke-default.

For modifications to schemas in restricted namespaces (see CODEOWNERS):

• [ ] Follow the change control procedure

┆Issue is synchronized with this Jira Task

[dataops-ci-bot]

Integration report for "Merge branch 'main' into DENG-4481-cfs-v2-prfl-grp-id"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 16:36:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 16:37:08.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 16:36:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 16:37:08.000000000 +0000 @@ -184,3 +184,7 @@ name: reported_shutdown_ping type: BOOLEAN mode: NULLABLE +- name: profile_group_id + type: STRING + mode: NULLABLE + description: A UUID identifying the profile's group on a single device and allowing user-oriented correlation of data ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 add profile_group_id to telemetry_derived.clients_first_seen_v2"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 16:43:03.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 16:45:24.000000000 +0000 @@ -397,21 +397,6 @@ depends_on_past=False, ) - mozilla_org_derived__blogs_landing_page_summary__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__blogs_landing_page_summary__v2", - destination_table="blogs_landing_page_summary_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="mhirose@mozilla.com", - email=[ - "kwindau@mozilla.com", - "mhirose@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_org_derived__blogs_sessions__v2 = bigquery_etl_query( task_id="mozilla_org_derived__blogs_sessions__v2", destination_table="blogs_sessions_v2", @@ -592,18 +577,6 @@ mozilla_org_derived__blogs_goals__v2.set_upstream(wait_for_blogs_events_table) - mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( - mozilla_org_derived__blogs_goals__v2 - ) - - mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( - mozilla_org_derived__blogs_sessions__v2 - ) - - mozilla_org_derived__blogs_landing_page_summary__v2.set_upstream( - wait_for_blogs_events_table - ) - mozilla_org_derived__blogs_sessions__v2.set_upstream(wait_for_blogs_events_table) mozilla_org_derived__ga_clients__v2.set_upstream( Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: blogs_landing_page_summary_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 2024-08-09 16:40:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,32 +0,0 @@ -friendly_name: Blogs Landing Page Summary V2 -description: |- - Daily summary Google analytics data for blog.mozilla.org landing page -owners: -- mhirose@mozilla.com -labels: - incremental: true - owner1: mhirose - dag: bqetl_google_analytics_derived_ga4 -scheduling: - dag_name: bqetl_google_analytics_derived_ga4 - depends_on: - - task_id: wait_for_blogs_events_table - dag_name: bqetl_google_analytics_derived_ga4 -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: true - expiration_days: null - range_partitioning: null - clustering: - fields: - - cleaned_landing_page - - browser - - blog - - subblog -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential -references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 2024-08-09 16:38:27.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,108 +0,0 @@ -WITH landing_page_staging AS ( - SELECT - PARSE_DATE('%Y%m%d', event_date) AS `date`, - user_pseudo_id || '-' || CAST( - ( - SELECT - `value` - FROM - UNNEST(event_params) - WHERE - key = 'ga_session_id' - LIMIT - 1 - ).int_value AS STRING - ) AS visit_identifier, - ( - SELECT - `value` - FROM - UNNEST(event_params) - WHERE - key = 'page_location' - LIMIT - 1 - ).string_value AS landing_page, - ( - SELECT - `value` - FROM - UNNEST(event_params) - WHERE - key = 'entrances' - LIMIT - 1 - ).int_value AS is_entrance - FROM - `moz-fx-data-marketing-prod.analytics_314399816.events_*` - WHERE - _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) - QUALIFY - ROW_NUMBER() OVER (PARTITION BY visit_identifier ORDER BY event_timestamp ASC) = 1 -), -landing_page_stg AS ( - SELECT - `date`, - visit_identifier, - landing_page, - SPLIT(landing_page, '?')[OFFSET(0)] AS cleaned_landing_page, - SUM(is_entrance) AS page_sessions - FROM - landing_page_staging - GROUP BY - `date`, - visit_identifier, - landing_page, - cleaned_landing_page - HAVING - page_sessions > 0 -) -SELECT - sessions_table.`date`, - sessions_table.device_category, - sessions_table.operating_system, - sessions_table.browser, - sessions_table.`language`, - sessions_table.country, - standardized_country_list.standardized_country AS standardized_country_name, - sessions_table.source, - sessions_table.medium, - sessions_table.campaign, - sessions_table.content, - sessions_table.blog, - sessions_table.subblog, - lps.landing_page, - lps.cleaned_landing_page, - SUM(sessions_table.sessions) AS sessions, - SUM(goals_table.downloads) AS downloads, - SUM(goals_table.social_share) AS social_share, - SUM(goals_table.newsletter_subscription) AS newsletter_subscription, -FROM - `moz-fx-data-shared-prod.mozilla_org_derived.blogs_sessions_v2` AS sessions_table -LEFT JOIN - `moz-fx-data-shared-prod.mozilla_org_derived.blogs_goals_v2` AS goals_table - USING (`date`, visit_identifier) -LEFT JOIN - landing_page_stg AS lps - USING (`date`, visit_identifier) -LEFT JOIN - `moz-fx-data-shared-prod.static.third_party_standardized_country_names` AS standardized_country_list - ON sessions_table.country = standardized_country_list.raw_country -WHERE - sessions_table.date = @submission_date -GROUP BY - sessions_table.`date`, - sessions_table.device_category, - sessions_table.operating_system, - sessions_table.browser, - sessions_table.`language`, - sessions_table.country, - standardized_country_list.standardized_country, - sessions_table.source, - sessions_table.medium, - sessions_table.campaign, - sessions_table.content, - sessions_table.blog, - sessions_table.subblog, - lps.landing_page, - lps.cleaned_landing_page diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 2024-08-09 16:38:27.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/blogs_landing_page_summary_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,77 +0,0 @@ -fields: -- mode: NULLABLE - name: date - type: DATE - description: Date of the entrance to the visit -- mode: NULLABLE - name: device_category - type: STRING - description: Device Category - The device category the visitor used to visit the site -- mode: NULLABLE - name: operating_system - type: STRING - description: Operating System - The operating system the visitor used to visit the site -- mode: NULLABLE - name: browser - type: STRING - description: Browser - The browser the visitor used to visit the site -- mode: NULLABLE - name: language - type: STRING - description: Language - Language of visitor's device -- mode: NULLABLE - name: country - type: STRING - description: Country - The country from which events were reported, based on IP address -- mode: NULLABLE - name: standardized_country_name - type: STRING - description: Standardized Country Name - The country from which events were reported, based on IP address -- mode: NULLABLE - name: source - type: STRING - description: Source - Referring partner domain -- mode: NULLABLE - name: medium - type: STRING - description: Medium - Category of the source, such as 'organic' for a search engine -- mode: NULLABLE - name: campaign - type: STRING - description: Campaign - Identifier for the marketing campaign -- mode: NULLABLE - name: content - type: STRING - description: Content - Indicates the particular link within a campaign -- mode: NULLABLE - name: blog - type: STRING - description: Blog -- mode: NULLABLE - name: subblog - type: STRING - description: Subblog -- mode: NULLABLE - name: landing_page - type: STRING - description: Landing Page -- mode: NULLABLE - name: cleaned_landing_page - type: STRING - description: Cleaned Landing Page - The URL portion prior to any question mark symbol -- mode: NULLABLE - name: sessions - type: INT64 - description: Sessions - The number of sessions with an entrances value of 1 -- mode: NULLABLE - name: downloads - type: INT64 - description: Downloads - The number of download click events -- mode: NULLABLE - name: social_share - type: INT64 - description: Social Shares - The number of social share events -- mode: NULLABLE - name: newsletter_subscription - type: INT64 - description: Newsletter Subscription - The number of newsletter subscribe events diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 16:38:27.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 16:38:31.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 16:38:27.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 16:38:31.000000000 +0000 @@ -184,3 +184,7 @@ name: reported_shutdown_ping type: BOOLEAN mode: NULLABLE +- name: profile_group_id + type: STRING + mode: NULLABLE + description: A UUID identifying the profile's group on a single device and allowing user-oriented correlation of data ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 update test to include profile_group_id"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 17:39:01.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 17:40:14.000000000 +0000 @@ -483,17 +483,6 @@ depends_on_past=False, ) - mozilla_org_derived__www_site_events_metrics__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__www_site_events_metrics__v2", - destination_table="www_site_events_metrics_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_org_derived__www_site_hits__v2 = bigquery_etl_query( task_id="mozilla_org_derived__www_site_hits__v2", destination_table="www_site_hits_v2", @@ -645,10 +634,6 @@ mozilla_org_derived__www_site_downloads__v2.set_upstream(wait_for_wmo_events_table) - mozilla_org_derived__www_site_events_metrics__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_org_derived__www_site_hits__v2.set_upstream(wait_for_wmo_events_table) mozilla_org_derived__www_site_metrics_summary__v2.set_upstream( Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_events_metrics_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml 2024-08-09 17:35:47.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,29 +0,0 @@ -friendly_name: WWW Site Page Metrics V2 -description: |- - Aggregated metrics per page on www.mozilla.org from Google Analytics 4 -owners: -- kwindau@mozilla.com -labels: - incremental: true - owner1: kwindau - dag: bqetl_google_analytics_derived_ga4 -scheduling: - dag_name: bqetl_google_analytics_derived_ga4 -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: true - expiration_days: null - range_partitioning: null - clustering: - fields: - - page_name - - locale - - country - - medium -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential -references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql 2024-08-09 17:33:47.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,187 +0,0 @@ ---first, calculate the next page view's start time relative to when the session started in seconds using lead -WITH page_view_staging AS ( - SELECT - *, - LEAD(hit_time) OVER ( - PARTITION BY - full_visitor_id, - visit_start_time - ORDER BY - hit_time - ) AS next_pageview, - FROM - `moz-fx-data-shared-prod.mozilla_org_derived.www_site_hits_v2` - WHERE - date = @submission_date - AND hit_type = 'PAGE' -), ---now, subtract current page view start time from next page view start time to get time on current page -page_views_only AS ( - SELECT - a.date, - a.page_path AS page, - a.page_path_level1 AS locale, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content, - SUM(a.next_pageview - a.hit_time) AS total_time_on_page - FROM - page_view_staging AS a - GROUP BY - a.date, - a.page_path, - a.page_path_level1, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content -), -all_events_staging AS ( - SELECT - a.date, - a.page_path AS page, - a.page_path_level1 AS locale, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content, - COUNTIF(a.event_name = 'page_view') AS pageviews, - COUNT( - DISTINCT(CASE WHEN a.event_name = 'page_view' THEN a.visit_identifier ELSE NULL END) - ) AS unique_pageviews, - SUM(a.entrances) AS entrances, - SUM(a.exits) AS exits, - COUNTIF(event_name = 'page_view' AND is_exit IS FALSE) AS non_exit_pageviews, - COUNTIF(hit_type = 'EVENT') AS total_events, - COUNT( - DISTINCT(CASE WHEN hit_type = 'EVENT' THEN visit_identifier ELSE NULL END) - ) AS unique_events, - COUNT( - DISTINCT(CASE WHEN single_page_session IS TRUE THEN visit_identifier ELSE NULL END) - ) AS single_page_sessions, - COUNT( - DISTINCT( - CASE - WHEN bounces = 1 - AND event_name = 'page_view' - THEN visit_identifier - ELSE NULL - END - ) - ) AS bounces - FROM - `moz-fx-data-shared-prod.mozilla_org_derived.www_site_hits_v2` AS a - WHERE - date = @submission_date - GROUP BY - a.date, - a.page_path, - a.page_path_level1, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content -) ---join it all together to get everything plus total time on each page -SELECT - a.date, - a.page, - a.locale, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content, - a.pageviews, - a.unique_pageviews, - a.entrances, - a.exits, - a.non_exit_pageviews, - b.total_time_on_page, - a.total_events, - a.unique_events, - a.single_page_sessions, - a.bounces, -FROM - all_events_staging a -FULL OUTER JOIN - page_views_only b - ON a.date = b.date - AND COALESCE(a.page, '') = COALESCE(b.page, '') - AND COALESCE(a.locale, '') = COALESCE(b.locale, '') - AND COALESCE(a.page_name, '') = COALESCE(b.page_name, '') - AND COALESCE(a.page_level_1, '') = COALESCE(b.page_level_1, '') - AND COALESCE(a.page_level_2, '') = COALESCE(b.page_level_2, '') - AND COALESCE(a.page_level_3, '') = COALESCE(b.page_level_3, '') - AND COALESCE(a.page_level_4, '') = COALESCE(b.page_level_4, '') - AND COALESCE(a.page_level_5, '') = COALESCE(b.page_level_5, '') - AND COALESCE(a.device_category, '') = COALESCE(b.device_category, '') - AND COALESCE(a.operating_system, '') = COALESCE(b.operating_system, '') - AND COALESCE(a.language, '') = COALESCE(b.language, '') - AND COALESCE(a.browser, '') = COALESCE(b.browser, '') - AND COALESCE(a.browser_version, '') = COALESCE(b.browser_version, '') - AND COALESCE(a.country, '') = COALESCE(b.country, '') - AND COALESCE(a.source, '') = COALESCE(b.source, '') - AND COALESCE(a.medium, '') = COALESCE(b.medium, '') - AND COALESCE(a.campaign, '') = COALESCE(b.campaign, '') - AND COALESCE(a.ad_content, '') = COALESCE(b.ad_content, '') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml 2024-08-09 17:33:47.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,117 +0,0 @@ -fields: -- mode: NULLABLE - name: date - type: DATE - description: The date of the visit -- mode: NULLABLE - name: page - type: STRING - description: Page -- mode: NULLABLE - name: locale - type: STRING - description: Locale -- mode: NULLABLE - name: page_name - type: STRING - description: Page Name -- mode: NULLABLE - name: page_level_1 - type: STRING - description: Page Level 1 -- mode: NULLABLE - name: page_level_2 - type: STRING - description: Page Level 2 -- mode: NULLABLE - name: page_level_3 - type: STRING - description: Page Level 3 -- mode: NULLABLE - name: page_level_4 - type: STRING - description: Page Level 4 -- mode: NULLABLE - name: page_level_5 - type: STRING - description: Page Level 5 -- mode: NULLABLE - name: device_category - type: STRING - description: Device Category - The device category the visitor used to visit the site -- mode: NULLABLE - name: operating_system - type: STRING - description: Operating System - The device operating system that the visitor used to visit the site -- mode: NULLABLE - name: language - type: STRING - description: Language -- mode: NULLABLE - name: browser - type: STRING - description: Browser -- mode: NULLABLE - name: browser_version - type: STRING - description: Browser Version -- mode: NULLABLE - name: country - type: STRING - description: Country -- mode: NULLABLE - name: source - type: STRING - description: Source -- mode: NULLABLE - name: medium - type: STRING - description: Medium -- mode: NULLABLE - name: campaign - type: STRING - description: Campaign -- mode: NULLABLE - name: ad_content - type: STRING - description: Ad Content -- mode: NULLABLE - name: pageviews - type: INT64 - description: Page Views -- mode: NULLABLE - name: unique_pageviews - type: INT64 - description: Unique Page Views -- mode: NULLABLE - name: entrances - type: INT64 - description: Entrances -- mode: NULLABLE - name: exits - type: INT64 - description: Exits -- mode: NULLABLE - name: non_exit_pageviews - type: INT64 - description: Non Exit Page Views -- mode: NULLABLE - name: total_time_on_page - type: FLOAT64 - description: Total Time On Page -- mode: NULLABLE - name: total_events - type: INT64 - description: Total Events -- mode: NULLABLE - name: unique_events - type: INT64 - description: Unique Events -- mode: NULLABLE - name: single_page_sessions - type: INT64 - description: Single Page Sessions -- mode: NULLABLE - name: bounces - type: INT64 - description: Bounces diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 17:33:47.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 17:33:52.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 17:33:47.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 17:33:52.000000000 +0000 @@ -184,3 +184,7 @@ name: reported_shutdown_ping type: BOOLEAN mode: NULLABLE +- name: profile_group_id + type: STRING + mode: NULLABLE + description: A UUID identifying the profile's group on a single device and allowing user-oriented correlation of data ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 update test_source_pings for clients_first_seen_v2 to include profile_group_id"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 17:39:41.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 17:41:08.000000000 +0000 @@ -483,17 +483,6 @@ depends_on_past=False, ) - mozilla_org_derived__www_site_events_metrics__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__www_site_events_metrics__v2", - destination_table="www_site_events_metrics_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_org_derived__www_site_hits__v2 = bigquery_etl_query( task_id="mozilla_org_derived__www_site_hits__v2", destination_table="www_site_hits_v2", @@ -645,10 +634,6 @@ mozilla_org_derived__www_site_downloads__v2.set_upstream(wait_for_wmo_events_table) - mozilla_org_derived__www_site_events_metrics__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_org_derived__www_site_hits__v2.set_upstream(wait_for_wmo_events_table) mozilla_org_derived__www_site_metrics_summary__v2.set_upstream( Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_events_metrics_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml 2024-08-09 17:36:42.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,29 +0,0 @@ -friendly_name: WWW Site Page Metrics V2 -description: |- - Aggregated metrics per page on www.mozilla.org from Google Analytics 4 -owners: -- kwindau@mozilla.com -labels: - incremental: true - owner1: kwindau - dag: bqetl_google_analytics_derived_ga4 -scheduling: - dag_name: bqetl_google_analytics_derived_ga4 -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: true - expiration_days: null - range_partitioning: null - clustering: - fields: - - page_name - - locale - - country - - medium -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential -references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql 2024-08-09 17:34:38.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,187 +0,0 @@ ---first, calculate the next page view's start time relative to when the session started in seconds using lead -WITH page_view_staging AS ( - SELECT - *, - LEAD(hit_time) OVER ( - PARTITION BY - full_visitor_id, - visit_start_time - ORDER BY - hit_time - ) AS next_pageview, - FROM - `moz-fx-data-shared-prod.mozilla_org_derived.www_site_hits_v2` - WHERE - date = @submission_date - AND hit_type = 'PAGE' -), ---now, subtract current page view start time from next page view start time to get time on current page -page_views_only AS ( - SELECT - a.date, - a.page_path AS page, - a.page_path_level1 AS locale, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content, - SUM(a.next_pageview - a.hit_time) AS total_time_on_page - FROM - page_view_staging AS a - GROUP BY - a.date, - a.page_path, - a.page_path_level1, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content -), -all_events_staging AS ( - SELECT - a.date, - a.page_path AS page, - a.page_path_level1 AS locale, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content, - COUNTIF(a.event_name = 'page_view') AS pageviews, - COUNT( - DISTINCT(CASE WHEN a.event_name = 'page_view' THEN a.visit_identifier ELSE NULL END) - ) AS unique_pageviews, - SUM(a.entrances) AS entrances, - SUM(a.exits) AS exits, - COUNTIF(event_name = 'page_view' AND is_exit IS FALSE) AS non_exit_pageviews, - COUNTIF(hit_type = 'EVENT') AS total_events, - COUNT( - DISTINCT(CASE WHEN hit_type = 'EVENT' THEN visit_identifier ELSE NULL END) - ) AS unique_events, - COUNT( - DISTINCT(CASE WHEN single_page_session IS TRUE THEN visit_identifier ELSE NULL END) - ) AS single_page_sessions, - COUNT( - DISTINCT( - CASE - WHEN bounces = 1 - AND event_name = 'page_view' - THEN visit_identifier - ELSE NULL - END - ) - ) AS bounces - FROM - `moz-fx-data-shared-prod.mozilla_org_derived.www_site_hits_v2` AS a - WHERE - date = @submission_date - GROUP BY - a.date, - a.page_path, - a.page_path_level1, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content -) ---join it all together to get everything plus total time on each page -SELECT - a.date, - a.page, - a.locale, - a.page_name, - a.page_level_1, - a.page_level_2, - a.page_level_3, - a.page_level_4, - a.page_level_5, - a.device_category, - a.operating_system, - a.language, - a.browser, - a.browser_version, - a.country, - a.source, - a.medium, - a.campaign, - a.ad_content, - a.pageviews, - a.unique_pageviews, - a.entrances, - a.exits, - a.non_exit_pageviews, - b.total_time_on_page, - a.total_events, - a.unique_events, - a.single_page_sessions, - a.bounces, -FROM - all_events_staging a -FULL OUTER JOIN - page_views_only b - ON a.date = b.date - AND COALESCE(a.page, '') = COALESCE(b.page, '') - AND COALESCE(a.locale, '') = COALESCE(b.locale, '') - AND COALESCE(a.page_name, '') = COALESCE(b.page_name, '') - AND COALESCE(a.page_level_1, '') = COALESCE(b.page_level_1, '') - AND COALESCE(a.page_level_2, '') = COALESCE(b.page_level_2, '') - AND COALESCE(a.page_level_3, '') = COALESCE(b.page_level_3, '') - AND COALESCE(a.page_level_4, '') = COALESCE(b.page_level_4, '') - AND COALESCE(a.page_level_5, '') = COALESCE(b.page_level_5, '') - AND COALESCE(a.device_category, '') = COALESCE(b.device_category, '') - AND COALESCE(a.operating_system, '') = COALESCE(b.operating_system, '') - AND COALESCE(a.language, '') = COALESCE(b.language, '') - AND COALESCE(a.browser, '') = COALESCE(b.browser, '') - AND COALESCE(a.browser_version, '') = COALESCE(b.browser_version, '') - AND COALESCE(a.country, '') = COALESCE(b.country, '') - AND COALESCE(a.source, '') = COALESCE(b.source, '') - AND COALESCE(a.medium, '') = COALESCE(b.medium, '') - AND COALESCE(a.campaign, '') = COALESCE(b.campaign, '') - AND COALESCE(a.ad_content, '') = COALESCE(b.ad_content, '') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml 2024-08-09 17:34:38.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_events_metrics_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,117 +0,0 @@ -fields: -- mode: NULLABLE - name: date - type: DATE - description: The date of the visit -- mode: NULLABLE - name: page - type: STRING - description: Page -- mode: NULLABLE - name: locale - type: STRING - description: Locale -- mode: NULLABLE - name: page_name - type: STRING - description: Page Name -- mode: NULLABLE - name: page_level_1 - type: STRING - description: Page Level 1 -- mode: NULLABLE - name: page_level_2 - type: STRING - description: Page Level 2 -- mode: NULLABLE - name: page_level_3 - type: STRING - description: Page Level 3 -- mode: NULLABLE - name: page_level_4 - type: STRING - description: Page Level 4 -- mode: NULLABLE - name: page_level_5 - type: STRING - description: Page Level 5 -- mode: NULLABLE - name: device_category - type: STRING - description: Device Category - The device category the visitor used to visit the site -- mode: NULLABLE - name: operating_system - type: STRING - description: Operating System - The device operating system that the visitor used to visit the site -- mode: NULLABLE - name: language - type: STRING - description: Language -- mode: NULLABLE - name: browser - type: STRING - description: Browser -- mode: NULLABLE - name: browser_version - type: STRING - description: Browser Version -- mode: NULLABLE - name: country - type: STRING - description: Country -- mode: NULLABLE - name: source - type: STRING - description: Source -- mode: NULLABLE - name: medium - type: STRING - description: Medium -- mode: NULLABLE - name: campaign - type: STRING - description: Campaign -- mode: NULLABLE - name: ad_content - type: STRING - description: Ad Content -- mode: NULLABLE - name: pageviews - type: INT64 - description: Page Views -- mode: NULLABLE - name: unique_pageviews - type: INT64 - description: Unique Page Views -- mode: NULLABLE - name: entrances - type: INT64 - description: Entrances -- mode: NULLABLE - name: exits - type: INT64 - description: Exits -- mode: NULLABLE - name: non_exit_pageviews - type: INT64 - description: Non Exit Page Views -- mode: NULLABLE - name: total_time_on_page - type: FLOAT64 - description: Total Time On Page -- mode: NULLABLE - name: total_events - type: INT64 - description: Total Events -- mode: NULLABLE - name: unique_events - type: INT64 - description: Unique Events -- mode: NULLABLE - name: single_page_sessions - type: INT64 - description: Single Page Sessions -- mode: NULLABLE - name: bounces - type: INT64 - description: Bounces diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 17:34:38.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 17:34:13.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + profile_group_id FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 17:34:38.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/schema.yaml 2024-08-09 17:34:13.000000000 +0000 @@ -184,3 +184,7 @@ name: reported_shutdown_ping type: BOOLEAN mode: NULLABLE +- name: profile_group_id + type: STRING + mode: NULLABLE + description: A UUID identifying the profile's group on a single device and allowing user-oriented correlation of data ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 update test schemas"

sql.diff

Click to expand!

```diff 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-09 18:08:11.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-08-09 18:20:44.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_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 18:08:11.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 18:20:45.000000000 +0000 @@ -483,17 +483,6 @@ depends_on_past=False, ) - mozilla_org_derived__www_site_events_metrics__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__www_site_events_metrics__v2", - destination_table="www_site_events_metrics_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_org_derived__www_site_hits__v2 = bigquery_etl_query( task_id="mozilla_org_derived__www_site_hits__v2", destination_table="www_site_hits_v2", @@ -524,17 +513,6 @@ depends_on_past=False, ) - mozilla_org_derived__www_site_page_metrics__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__www_site_page_metrics__v2", - destination_table="www_site_page_metrics_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_vpn_derived__site_metrics_summary__v2 = bigquery_etl_query( task_id="mozilla_vpn_derived__site_metrics_summary__v2", destination_table="site_metrics_summary_v2", @@ -656,20 +634,12 @@ mozilla_org_derived__www_site_downloads__v2.set_upstream(wait_for_wmo_events_table) - mozilla_org_derived__www_site_events_metrics__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_org_derived__www_site_hits__v2.set_upstream(wait_for_wmo_events_table) mozilla_org_derived__www_site_metrics_summary__v2.set_upstream( wait_for_wmo_events_table ) - mozilla_org_derived__www_site_page_metrics__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_vpn_derived__site_metrics_summary__v2.set_upstream( wait_for_wmo_events_table ) Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_events_metrics_v2 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_page_metrics_v2 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-09 18:03:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-08-09 18:11:32.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-09 18:03:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-08-09 18:11:44.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-08-09 18:03:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-08-09 18:11:58.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-09 18:03:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-08-09 18:12:05.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-08-09 18:03:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-08-09 18:12:40.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/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-09 18:03:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-08-09 18:14:27.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/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-09 18:03:20.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-09 18:12: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 @@ -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-09 18:03:20.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-09 18:12: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 @@ -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-09 18:03:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml 2024-08-09 18:12:08.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-09 18:03:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml 2024-08-09 18:12:08.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/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-09 18:04:28.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/metadata.yaml 2024-08-09 18:16:25.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-09 18:04:28.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-09 18:16:25.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_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-09 18:03:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql 2024-08-09 18:11:23.000000000 +0000 @@ -1,4 +1,4 @@ -WITH first_partition_mozphab_stable AS ( +WITH first_partition_pocket_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.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.pocket_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_mozphab_stable AS ( +first_non_empty_partition_pocket_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.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.pocket_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_firefoxvpn_stable AS ( +first_partition_pine_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_firefoxvpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.pine_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_firefoxvpn_stable AS ( +first_non_empty_partition_pine_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_firefoxvpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.pine_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_webpagetest_stable AS ( +first_partition_org_mozilla_bergamot_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.webpagetest_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_webpagetest_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.webpagetest_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_accounts_cirrus_stable AS ( +first_partition_org_mozilla_ios_firefoxbeta_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.accounts_cirrus_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_accounts_cirrus_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.accounts_cirrus_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_org_mozilla_reference_browser_stable AS ( +first_partition_firefox_desktop_background_tasks_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.org_mozilla_reference_browser_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_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_reference_browser_stable AS ( +first_non_empty_partition_firefox_desktop_background_tasks_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_reference_browser_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_social_nightly_stable AS ( +first_partition_glean_dictionary_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.org_mozilla_social_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.glean_dictionary_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_social_nightly_stable AS ( +first_non_empty_partition_glean_dictionary_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_social_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.glean_dictionary_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_viu_politica_stable AS ( +first_partition_firefox_installer_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.viu_politica_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_installer_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_viu_politica_stable AS ( +first_non_empty_partition_firefox_installer_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.viu_politica_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_installer_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_thunderbird_desktop_stable AS ( +first_partition_mdn_yari_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.thunderbird_desktop_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mdn_yari_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_thunderbird_desktop_stable AS ( +first_non_empty_partition_mdn_yari_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.thunderbird_desktop_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mdn_yari_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_firefox_installer_stable AS ( +first_partition_messaging_system_stable AS ( SELECT table_catalog, table_schema, @@ -214,25 +214,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.firefox_installer_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.messaging_system_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_firefox_installer_stable AS ( +first_non_empty_partition_messaging_system_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.firefox_installer_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.messaging_system_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_monitor_frontend_stable AS ( +first_partition_mlhackweek_search_stable AS ( SELECT table_catalog, table_schema, @@ -240,25 +240,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.monitor_frontend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mlhackweek_search_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_monitor_frontend_stable AS ( +first_non_empty_partition_mlhackweek_search_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.monitor_frontend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mlhackweek_search_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_burnham_stable AS ( +first_partition_org_mozilla_fenix_nightly_stable AS ( SELECT table_catalog, table_schema, @@ -266,25 +266,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.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_fenix_nightly_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_burnham_stable AS ( +first_non_empty_partition_org_mozilla_fenix_nightly_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.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_fenix_nightly_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_org_mozilla_focus_stable AS ( SELECT table_catalog, table_schema, @@ -292,25 +292,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.org_mozilla_focus_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_org_mozilla_focus_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_connect_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_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_ios_tiktok_reporter_stable AS ( SELECT table_catalog, table_schema, @@ -318,25 +318,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_ios_tiktok_reporter_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_ios_tiktok_reporter_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_ios_tiktok_reporter_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_mozillavpn_stable AS ( +first_partition_mobile_stable AS ( SELECT table_catalog, table_schema, @@ -344,25 +344,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.mozillavpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mobile_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_mozillavpn_stable AS ( +first_non_empty_partition_mobile_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.mozillavpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mobile_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_bergamot_stable AS ( +first_partition_accounts_cirrus_stable AS ( SELECT table_catalog, table_schema, @@ -370,25 +370,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_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.accounts_cirrus_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_bergamot_stable AS ( +first_non_empty_partition_accounts_cirrus_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_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.accounts_cirrus_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefoxreality_stable AS ( +first_partition_firefox_desktop_background_defaultagent_stable AS ( SELECT table_catalog, table_schema, @@ -396,25 +396,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_firefoxreality_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_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_firefoxreality_stable AS ( +first_non_empty_partition_firefox_desktop_background_defaultagent_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_firefoxreality_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_firefoxvpn_network_extension_stable AS ( +first_partition_firefox_launcher_process_stable AS ( SELECT table_catalog, table_schema, @@ -422,25 +422,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_firefoxvpn_network_extension_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_ios_firefoxvpn_network_extension_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_ios_firefoxvpn_network_extension_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_firefox_desktop_background_defaultagent_stable AS ( +first_partition_org_mozilla_fenix_stable AS ( SELECT table_catalog, table_schema, @@ -448,25 +448,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.firefox_desktop_background_defaultagent_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_firefox_desktop_background_defaultagent_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.firefox_desktop_background_defaultagent_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_org_mozilla_ios_klar_stable AS ( +first_partition_org_mozilla_focus_beta_stable AS ( SELECT table_catalog, table_schema, @@ -474,25 +474,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_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_beta_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_klar_stable AS ( +first_non_empty_partition_org_mozilla_focus_beta_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_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_beta_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_focus_stable AS ( +first_partition_mozillavpn_stable AS ( SELECT table_catalog, table_schema, @@ -500,25 +500,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_focus_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_org_mozilla_ios_focus_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.org_mozilla_ios_focus_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_org_mozilla_focus_stable AS ( +first_partition_org_mozilla_reference_browser_stable AS ( SELECT table_catalog, table_schema, @@ -526,25 +526,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_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_reference_browser_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_focus_stable AS ( +first_non_empty_partition_org_mozilla_reference_browser_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_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_reference_browser_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_mdn_yari_stable AS ( +first_partition_debug_ping_view_stable AS ( SELECT table_catalog, table_schema, @@ -552,25 +552,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.mdn_yari_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.debug_ping_view_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_mdn_yari_stable AS ( +first_non_empty_partition_debug_ping_view_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.mdn_yari_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.debug_ping_view_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_mobile_stable AS ( +first_partition_accounts_backend_stable AS ( SELECT table_catalog, table_schema, @@ -578,25 +578,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.mobile_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.accounts_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_mobile_stable AS ( +first_non_empty_partition_accounts_backend_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.mobile_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.accounts_backend_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefox_vpn_stable AS ( +first_partition_mozillavpn_backend_cirrus_stable AS ( SELECT table_catalog, table_schema, @@ -604,25 +604,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_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozillavpn_backend_cirrus_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_vpn_stable AS ( +first_non_empty_partition_mozillavpn_backend_cirrus_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_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozillavpn_backend_cirrus_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_firefoxreality_stable AS ( SELECT table_catalog, table_schema, @@ -630,25 +630,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_firefoxreality_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_firefoxreality_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_firefoxreality_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_klar_stable AS ( +first_partition_org_mozilla_ios_firefoxvpn_network_extension_stable AS ( SELECT table_catalog, table_schema, @@ -656,25 +656,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_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_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_klar_stable AS ( +first_non_empty_partition_org_mozilla_ios_firefoxvpn_network_extension_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_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_tv_firefox_stable AS ( +first_partition_org_mozilla_ios_lockbox_stable AS ( SELECT table_catalog, table_schema, @@ -682,25 +682,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_tv_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_lockbox_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_tv_firefox_stable AS ( +first_non_empty_partition_org_mozilla_ios_lockbox_stable AS ( SELECT table_name, PARSE_DATE("%Y%m%d", MIN(partition_id)) AS first_non_empty_pa ```

⚠️ Only part of the diff is displayed.

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 update query"

sql.diff

Click to expand!

```diff 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-09 18:31:54.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-08-09 18:43:28.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_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 18:31:54.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-09 18:43:29.000000000 +0000 @@ -427,21 +427,6 @@ depends_on_past=False, ) - mozilla_org_derived__firefox_whatsnew_summary__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__firefox_whatsnew_summary__v2", - destination_table="firefox_whatsnew_summary_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="mhirose@mozilla.com", - email=[ - "kwindau@mozilla.com", - "mhirose@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_org_derived__ga_clients__v2 = bigquery_etl_query( task_id="mozilla_org_derived__ga_clients__v2", destination_table="ga_clients_v2", @@ -498,17 +483,6 @@ depends_on_past=False, ) - mozilla_org_derived__www_site_events_metrics__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__www_site_events_metrics__v2", - destination_table="www_site_events_metrics_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_org_derived__www_site_hits__v2 = bigquery_etl_query( task_id="mozilla_org_derived__www_site_hits__v2", destination_table="www_site_hits_v2", @@ -539,17 +513,6 @@ depends_on_past=False, ) - mozilla_org_derived__www_site_page_metrics__v2 = bigquery_etl_query( - task_id="mozilla_org_derived__www_site_page_metrics__v2", - destination_table="www_site_page_metrics_v2", - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - mozilla_vpn_derived__site_metrics_summary__v2 = bigquery_etl_query( task_id="mozilla_vpn_derived__site_metrics_summary__v2", destination_table="site_metrics_summary_v2", @@ -643,10 +606,6 @@ mozilla_org_derived__blogs_sessions__v2.set_upstream(wait_for_blogs_events_table) - mozilla_org_derived__firefox_whatsnew_summary__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_org_derived__ga_clients__v2.set_upstream( mozilla_org_derived__ga_sessions__v2 ) @@ -675,20 +634,12 @@ mozilla_org_derived__www_site_downloads__v2.set_upstream(wait_for_wmo_events_table) - mozilla_org_derived__www_site_events_metrics__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_org_derived__www_site_hits__v2.set_upstream(wait_for_wmo_events_table) mozilla_org_derived__www_site_metrics_summary__v2.set_upstream( wait_for_wmo_events_table ) - mozilla_org_derived__www_site_page_metrics__v2.set_upstream( - mozilla_org_derived__www_site_hits__v2 - ) - mozilla_vpn_derived__site_metrics_summary__v2.set_upstream( wait_for_wmo_events_table ) Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: firefox_whatsnew_summary_v2 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_events_metrics_v2 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_page_metrics_v2 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-09 18:26:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-08-09 18:34:54.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-09 18:26:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-08-09 18:34:48.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-08-09 18:26:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-08-09 18:35:15.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-09 18:26:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-08-09 18:35:22.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-08-09 18:27:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-08-09 18:37:17.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/fenix/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-08-09 18:27:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-08-09 18:36:25.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/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-09 18:27:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/focus_android/use_counters/schema.yaml 2024-08-09 18:37:47.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/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-09 18:26:35.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-09 18:35:24.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-09 18:26:35.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-09 18:35:22.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-09 18:26:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml 2024-08-09 18:35:22.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-09 18:26:35.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml 2024-08-09 18:35:23.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-09 18:27:19.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/klar_android/use_counters/schema.yaml 2024-08-09 18:37:34.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/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-09 18:27:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitor_frontend_derived/event_monitoring_live_v1/metadata.yaml 2024-08-09 18:39:22.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-09 18:27:55.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-09 18:39:22.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_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-09 18:27:20.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/table_partition_expirations_v1/query.sql 2024-08-09 18:34:41.000000000 +0000 @@ -1,4 +1,4 @@ -WITH first_partition_mozphab_stable AS ( +WITH first_partition_contextual_services_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.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.contextual_services_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_mozphab_stable AS ( +first_non_empty_partition_contextual_services_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.mozphab_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.contextual_services_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_firefoxvpn_stable AS ( +first_partition_firefox_launcher_process_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_firefoxvpn_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_ios_firefoxvpn_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_ios_firefoxvpn_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_webpagetest_stable AS ( +first_partition_default_browser_agent_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.webpagetest_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.default_browser_agent_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_webpagetest_stable AS ( +first_non_empty_partition_default_browser_agent_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.webpagetest_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.default_browser_agent_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_accounts_cirrus_stable AS ( +first_partition_mobile_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.accounts_cirrus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mobile_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_accounts_cirrus_stable AS ( +first_non_empty_partition_mobile_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.accounts_cirrus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mobile_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_reference_browser_stable AS ( +first_partition_org_mozilla_mozregression_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.org_mozilla_reference_browser_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_mozregression_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_reference_browser_stable AS ( +first_non_empty_partition_org_mozilla_mozregression_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_reference_browser_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_mozregression_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_social_nightly_stable AS ( +first_partition_coverage_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.org_mozilla_social_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.coverage_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_social_nightly_stable AS ( +first_non_empty_partition_coverage_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_social_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.coverage_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_viu_politica_stable AS ( +first_partition_firefox_installer_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.viu_politica_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_installer_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_viu_politica_stable AS ( +first_non_empty_partition_firefox_installer_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.viu_politica_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_installer_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_thunderbird_desktop_stable AS ( +first_partition_org_mozilla_firefox_vpn_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.thunderbird_desktop_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_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_thunderbird_desktop_stable AS ( +first_non_empty_partition_org_mozilla_firefox_vpn_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.thunderbird_desktop_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_firefox_installer_stable AS ( +first_partition_pocket_stable AS ( SELECT table_catalog, table_schema, @@ -214,25 +214,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.firefox_installer_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.pocket_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_firefox_installer_stable AS ( +first_non_empty_partition_pocket_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.firefox_installer_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.pocket_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_monitor_frontend_stable AS ( +first_partition_org_mozilla_focus_nightly_stable AS ( SELECT table_catalog, table_schema, @@ -240,25 +240,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.monitor_frontend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_nightly_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_monitor_frontend_stable AS ( +first_non_empty_partition_org_mozilla_focus_nightly_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.monitor_frontend_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_focus_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_burnham_stable AS ( +first_partition_regrets_reporter_ucs_stable AS ( SELECT table_catalog, table_schema, @@ -266,25 +266,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.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.regrets_reporter_ucs_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_burnham_stable AS ( +first_non_empty_partition_regrets_reporter_ucs_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.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.regrets_reporter_ucs_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_burnham_stable AS ( SELECT table_catalog, table_schema, @@ -292,18 +292,18 @@ 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.burnham_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_burnham_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_connect_firefox_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.burnham_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 @@ -336,7 +336,7 @@ GROUP BY table_name ), -first_partition_mozillavpn_stable AS ( +first_partition_mozilla_lockbox_stable AS ( SELECT table_catalog, table_schema, @@ -344,25 +344,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.mozillavpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozilla_lockbox_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_mozillavpn_stable AS ( +first_non_empty_partition_mozilla_lockbox_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.mozillavpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mozilla_lockbox_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_bergamot_stable AS ( +first_partition_org_mozilla_ios_tiktok_reporter_tiktok_reportershare_stable AS ( SELECT table_catalog, table_schema, @@ -370,25 +370,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_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_tiktok_reporter_tiktok_reportershare_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_bergamot_stable AS ( +first_non_empty_partition_org_mozilla_ios_tiktok_reporter_tiktok_reportershare_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_bergamot_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_ios_tiktok_reporter_tiktok_reportershare_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefoxreality_stable AS ( +first_partition_org_mozilla_social_nightly_stable AS ( SELECT table_catalog, table_schema, @@ -396,25 +396,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_firefoxreality_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_social_nightly_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_firefoxreality_stable AS ( +first_non_empty_partition_org_mozilla_social_nightly_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_firefoxreality_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_social_nightly_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_firefoxvpn_network_extension_stable AS ( +first_partition_mozillavpn_stable AS ( SELECT table_catalog, table_schema, @@ -422,25 +422,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_firefoxvpn_network_extension_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_org_mozilla_ios_firefoxvpn_network_extension_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.org_mozilla_ios_firefoxvpn_network_extension_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_firefox_desktop_background_defaultagent_stable AS ( +first_partition_firefox_translations_stable AS ( SELECT table_catalog, table_schema, @@ -448,25 +448,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.firefox_desktop_background_defaultagent_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_translations_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_firefox_desktop_background_defaultagent_stable AS ( +first_non_empty_partition_firefox_translations_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.firefox_desktop_background_defaultagent_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_translations_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_klar_stable AS ( +first_partition_firefox_desktop_background_defaultagent_stable AS ( SELECT table_catalog, table_schema, @@ -474,25 +474,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_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_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_klar_stable AS ( +first_non_empty_partition_firefox_desktop_background_defaultagent_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_klar_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.firefox_desktop_background_defaultagent_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_ios_focus_stable AS ( +first_partition_bedrock_stable AS ( SELECT table_catalog, table_schema, @@ -500,25 +500,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_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.bedrock_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_focus_stable AS ( +first_non_empty_partition_bedrock_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_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.bedrock_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_focus_stable AS ( +first_partition_org_mozilla_firefox_beta_stable AS ( SELECT table_catalog, table_schema, @@ -526,25 +526,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_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_firefox_beta_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_focus_stable AS ( +first_non_empty_partition_org_mozilla_firefox_beta_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_focus_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_firefox_beta_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_mdn_yari_stable AS ( +first_partition_org_mozilla_reference_browser_stable AS ( SELECT table_catalog, table_schema, @@ -552,25 +552,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.mdn_yari_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_reference_browser_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_mdn_yari_stable AS ( +first_non_empty_partition_org_mozilla_reference_browser_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.mdn_yari_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_reference_browser_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_mobile_stable AS ( +first_partition_org_mozilla_tiktokreporter_stable AS ( SELECT table_catalog, table_schema, @@ -578,25 +578,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.mobile_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_tiktokreporter_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_mobile_stable AS ( +first_non_empty_partition_org_mozilla_tiktokreporter_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.mobile_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.org_mozilla_tiktokreporter_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_firefox_vpn_stable AS ( +first_partition_mlhackweek_search_stable AS ( SELECT table_catalog, table_schema, @@ -604,25 +604,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_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mlhackweek_search_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_vpn_stable AS ( +first_non_empty_partition_mlhackweek_search_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_vpn_stable.INFORMATION_SCHEMA.PARTITIONS` + `moz-fx-data-shared-prod.mlhackweek_search_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_pine_stable AS ( +first_partition_eng_workflow_stable AS ( SELECT table_catalog, table_schema, @@ -630,25 +630,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.eng_workflow_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_eng_workflow_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.eng_workflow_stable.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id != '__NULL__' AND total_rows > 0 GROUP BY table_name ), -first_partition_org_mozilla_klar_stable AS ( +first_partition_org_mozilla_firefoxreality_stable AS ( SELECT table_catalog, tabl ```

⚠️ Only part of the diff is displayed.

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into DENG-4481-cfs-v2-prfl-grp-id"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 19:35:01.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 19:35:07.000000000 +0000 @@ -130,6 +130,9 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + mozfun.stats.mode_last( + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp) + ) AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +273,9 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + mozfun.stats.mode_last( + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp) + ) AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +388,10 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + mozfun.stats.mode_last( + ARRAY_AGG(profile_group_id ORDER BY submission_date) + ) AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 switch to last profile group ID"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 20:09:39.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 20:12:33.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_date)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 fix formatting"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 21:07:28.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-09 21:07:33.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_date)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into DENG-4481-cfs-v2-prfl-grp-id"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-10 02:00:47.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-10 02:01:03.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_date)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 fix typo in test"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-10 02:38:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-10 02:41:48.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_date)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE @@ -508,7 +511,47 @@ ), _previous AS ( SELECT - * + client_id, + sample_id, + first_seen_date, + second_seen_date, + architecture, + app_build_id, + app_name, + locale, + platform_version, + vendor, + app_version, + xpcom_abi, + document_id, + distribution_id, + partner_distribution_version, + partner_distributor, + partner_distributor_channel, + partner_id, + attribution_campaign, + attribution_content, + attribution_experiment, + attribution_medium, + attribution_source, + attribution_ua, + engine_data_load_path, + engine_data_name, + engine_data_origin, + engine_data_submission_url, + apple_model_id, + city, + db_version, + subdivision1, + normalized_channel, + country, + normalized_os, + normalized_os_version, + startup_profile_selection_reason, + attribution_dltoken, + attribution_dlsource, + profile_group_id, + metadata FROM `moz-fx-data-shared-prod.telemetry_derived.clients_first_seen_v2` ) ```

Link to full diff

[dataops-ci-bot]

Integration report for "DENG-4481 fix formatting of SQL"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-10 02:48:28.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/clients_first_seen_v2/query.sql 2024-08-10 02:48:23.000000000 +0000 @@ -130,6 +130,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.new_profile` WHERE @@ -270,6 +271,7 @@ ORDER BY submission_timestamp )[SAFE_OFFSET(0)] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry.first_shutdown` WHERE @@ -382,7 +384,8 @@ ] AS attribution_dltoken, ARRAY_AGG(attribution.dlsource RESPECT NULLS ORDER BY submission_date)[ SAFE_OFFSET(0) - ] AS attribution_dlsource + ] AS attribution_dlsource, + ARRAY_AGG(profile_group_id ORDER BY submission_date)[SAFE_OFFSET(0)] AS profile_group_id, FROM `moz-fx-data-shared-prod.telemetry_derived.clients_daily_v6` WHERE @@ -508,7 +511,47 @@ ), _previous AS ( SELECT - * + client_id, + sample_id, + first_seen_date, + second_seen_date, + architecture, + app_build_id, + app_name, + locale, + platform_version, + vendor, + app_version, + xpcom_abi, + document_id, + distribution_id, + partner_distribution_version, + partner_distributor, + partner_distributor_channel, + partner_id, + attribution_campaign, + attribution_content, + attribution_experiment, + attribution_medium, + attribution_source, + attribution_ua, + engine_data_load_path, + engine_data_name, + engine_data_origin, + engine_data_submission_url, + apple_model_id, + city, + db_version, + subdivision1, + normalized_channel, + country, + normalized_os, + normalized_os_version, + startup_profile_selection_reason, + attribution_dltoken, + attribution_dlsource, + profile_group_id, + metadata FROM `moz-fx-data-shared-prod.telemetry_derived.clients_first_seen_v2` ) ```

Link to full diff