[DS-3326] Add state to subplat subscriptions v2

BenWu commented 6 days ago

This adds state for US and Canada to derived stripe customer and downstream subscription tables

I tested this by changing the project qualifiers and deploying/running in a sandbox project. The results look fine on a cursory look

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 commented 6 days ago

Integration report for "DS-3326 Update stripe subs to match location hierarchy for states"

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1: backfill.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8: backfill.yaml 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-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-28 21:48:07.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-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-28 21:48:07.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-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-06-28 21:49:28.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-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-06-28 21:49:29.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/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-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml 2024-06-28 21:48:10.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,103 +17,110 @@ - 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. - This will be null when the source data wasn't exported directly to BigQuery by Google. + description: 'Whether Google Search considers the page to be providing a good page + experience. + + This will be null when the source data wasn''t exported directly to BigQuery by + Google.' - 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). - This will be null when the source data wasn't exported directly to BigQuery by Google. + description: 'How the search result appeared (e.g. normal result, translated result, + video). + + This will be null when the source data wasn''t exported directly to BigQuery by + Google.' - 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 @@ -124,13 +132,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 @@ -138,6 +148,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-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml 2024-06-28 21:48:10.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/monitoring/event_monitoring_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live/schema.yaml 2024-06-28 21:49:06.000000000 +0000 @@ -0,0 +1,37 @@ +fields: +- name: window_start + type: TIMESTAMP + mode: NULLABLE +- name: window_end + type: TIMESTAMP + mode: NULLABLE +- name: event_category + type: STRING + mode: NULLABLE +- name: event_name + type: STRING + mode: NULLABLE +- name: event_extra_key + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: channel + type: STRING + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: experiment + type: STRING + mode: NULLABLE +- name: experiment_branch + type: STRING + mode: NULLABLE +- name: total_events + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-06-28 21:42:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-06-28 21:48:19.000000000 +0000 @@ -1,7 +1,13 @@ fields: -- type: DATETIME - name: build_hour -- type: INTEGER - name: geckoview_major_version -- type: INTEGER - name: n_pings +- name: build_hour + type: DATETIME + mode: NULLABLE + description: null +- name: geckoview_major_version + type: INTEGER + mode: NULLABLE + description: null +- name: n_pings + type: INTEGER + mode: NULLABLE + description: null diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,9 +0,0 @@ -2024-06-27: - start_date: 2024-04-01 - end_date: 2024-05-31 - reason: https://mozilla-hub.atlassian.net/browse/RS-1247 - watchers: - - akommasani@mozilla.com - - skahmann@mozilla.com - - pissac@mozilla.com - status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml 2024-06-28 21:42:45.000000000 +0000 @@ -5,4 +5,4 @@ watchers: - akommasani@mozilla.com - skahmannz@mozilla.com - status: Complete + status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,9 +0,0 @@ -2024-06-27: - start_date: 2024-04-01 - end_date: 2024-05-31 - reason: https://mozilla-hub.atlassian.net/browse/RS-1247 - watchers: - - akommasani@mozilla.com - - skahmann@mozilla.com - - pissac@mozilla.com - status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml 2024-06-28 21:42:45.000000000 +0000 @@ -5,4 +5,4 @@ watchers: - akommasani@mozilla.com - skahmann@mozilla.com - status: Complete + status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -51,6 +51,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -57,6 +57,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED @@ -241,6 +245,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql 2024-06-28 21:42:46.000000000 +0000 @@ -146,6 +146,7 @@ ) AS customer_subscription_number, history.subscription.country_code, COALESCE(countries.name, history.subscription.country_code, 'Unknown') AS country_name, + history.subscription.state_code, history.subscription.services, history.subscription.provider_product_id, history.subscription.product_name, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -54,6 +54,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -54,6 +54,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml 2024-06-28 21:43:50.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml 2024-06-28 21:54:51.000000000 +0000 @@ -8,7 +8,8 @@ This table's schema closely mirrors Stripe's customers API (https://stripe.com/docs/api/customers/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -38,6 +38,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -200,6 +204,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml 2024-06-28 21:43:50.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml 2024-06-28 21:54:51.000000000 +0000 @@ -7,7 +7,8 @@ This table's schema closely mirrors Stripe's customers API (https://stripe.com/docs/api/customers/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql 2024-06-28 21:42:46.000000000 +0000 @@ -40,18 +40,69 @@ JSON_VALUE(customer.metadata.userid) AS userid, TO_HEX(SHA256(JSON_VALUE(customer.metadata.userid))) AS userid_sha256 ) AS metadata, - -- Limit address data to just country since the metadata includes FxA user IDs - -- and this is in a Mozilla-confidential dataset. - STRUCT(customer.address.country) AS address, + -- Limit address data to country and state since the metadata includes FxA user IDs + -- and this is in a Mozilla-confidential dataset. State is only for US and Canada. + STRUCT( + NULLIF(customer.address.country, ""), + IF( + customer.address.country IN ("US", "CA"), + COALESCE( + -- Only use two-letter codes for consistency since billing addresses sometimes have full names + IF(LENGTH(customer.address.state) = 2, customer.address.state, NULL), + us_zip_code_prefixes.state_code, + ca_postal_districts.province_code + ), + NULL + ) AS state + ) AS address, ( SELECT AS STRUCT - customer.shipping.* REPLACE (STRUCT(customer.shipping.address.country) AS address) + customer.shipping.* REPLACE ( + STRUCT( + NULLIF(customer.shipping.address.country, ""), + IF( + customer.shipping.address.country IN ("US", "CA"), + COALESCE( + IF( + LENGTH(customer.shipping.address.state) = 2, + customer.shipping.address.state, + NULL + ), + us_shipping_zip_code_prefixes.state_code, + ca_shipping_postal_districts.province_code + ), + NULL + ) AS state + ) AS address + ) ) AS shipping ) ) AS customer, ROW_NUMBER() OVER customer_changes_asc AS customer_change_number FROM `moz-fx-data-shared-prod`.stripe_external.customers_changelog_v1 + -- try to get state using postal code if state field is unavailable + LEFT JOIN + `moz-fx-data-shared-prod.static.us_zip_code_prefixes_v1` AS us_shipping_zip_code_prefixes + ON customer.shipping.address.country = "US" + AND LEFT( + customer.shipping.address.postal_code, + 3 + ) = us_shipping_zip_code_prefixes.zip_code_prefix + LEFT JOIN + `moz-fx-data-shared-prod.static.us_zip_code_prefixes_v1` AS us_zip_code_prefixes + ON customer.address.country = "US" + AND LEFT(customer.address.postal_code, 3) = us_zip_code_prefixes.zip_code_prefix + LEFT JOIN + `moz-fx-data-shared-prod.static.ca_postal_districts_v1` AS ca_shipping_postal_districts + ON customer.shipping.address.country = "CA" + AND UPPER( + LEFT(customer.shipping.address.postal_code, 1) + ) = ca_shipping_postal_districts.postal_district_code + LEFT JOIN + `moz-fx-data-shared-prod.static.ca_postal_districts_v1` AS ca_postal_districts + ON customer.address.country = "CA" + AND UPPER(LEFT(customer.address.postal_code, 1)) = ca_postal_districts.postal_district_code WINDOW customer_changes_asc AS ( PARTITION BY @@ -68,7 +119,7 @@ TIMESTAMP '2022-03-25 00:02:29' AS `timestamp`, STRUCT( id, - STRUCT(address.country) AS address, + STRUCT(address.country, address.state) AS address, created, CAST(NULL AS STRING) AS default_source_id, CAST( @@ -103,7 +154,7 @@ CAST(NULL AS STRING) AS userid, JSON_VALUE(metadata.userid_sha256) AS userid_sha256 ) AS metadata, - CAST(NULL AS STRUCT

>) AS shipping, + CAST(NULL AS STRUCT

>) AS shipping, CAST(NULL AS STRING) AS tax_exempt ) AS customer, 1 AS customer_change_number diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -48,6 +48,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -210,6 +214,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql 2024-06-28 21:42:46.000000000 +0000 @@ -62,14 +62,19 @@ SELECT history.id AS subscriptions_history_id, ARRAY_AGG( - cards.country IGNORE NULLS + IF( + cards.country IS NOT NULL, + STRUCT( + cards.country AS latest_card_country, + COALESCE(charge_us_zip.state_code, charge_ca_post.province_code) AS latest_card_state + ), + NULL + ) IGNORE NULLS ORDER BY - -- Prefer charges that succeeded. + -- Prefer charges that succeeded IF(charges.status = 'succeeded', 1, 2), charges.created DESC - LIMIT - 1 - )[SAFE_ORDINAL(1)] AS latest_card_country, + )[SAFE_ORDINAL(1)].*, LOGICAL_OR(refunds.status = 'succeeded') AS has_refunds, LOGICAL_OR( charges.fraud_details_user_report = 'fraudulent' @@ -94,6 +99,18 @@ LEFT JOIN `moz-fx-data-shared-prod.stripe_external.refund_v1` AS refunds ON charges.id = refunds.charge_id + -- charges usually have postal code and are sometimes associated with + -- a card that does not have a state or postal code + LEFT JOIN + `moz-fx-data-shared-prod.static.us_zip_code_prefixes_v1` AS charge_us_zip + ON cards.country = "US" + AND LEFT(charges.billing_detail_address_postal_code, 3) = charge_us_zip.zip_code_prefix + LEFT JOIN + `moz-fx-data-shared-prod.static.ca_postal_districts_v1` AS charge_ca_post + ON cards.country = "CA" + AND UPPER( + LEFT(charges.billing_detail_address_postal_code, 1) + ) = charge_ca_post.postal_district_code GROUP BY subscriptions_history_id ) @@ -109,7 +126,8 @@ history.valid_from, history.valid_to, history.id AS provider_subscriptions_history_id, - STRUCT( + ( + SELECT AS STRUCT CONCAT( 'Stripe-', history.subscription.id, @@ -124,24 +142,44 @@ history.subscription.customer.id AS provider_customer_id, history.subscription.customer.metadata.userid AS mozilla_account_id, history.subscription.customer.metadata.userid_sha256 AS mozilla_account_id_sha256, + ( CASE - -- Use the same address hierarchy as Stripe Tax after we enabled Stripe Tax (FXA-5457). + -- Use the same address hierarchy as Stripe Tax after we enabled Stripe Tax on 2022-12-01 (FXA-5457). -- https://stripe.com/docs/tax/customer-locations#address-hierarchy WHEN DATE(history.valid_to) >= '2022-12-01' AND ( DATE(history.subscription.ended_at) >= '2022-12-01' OR history.subscription.ended_at IS NULL ) - THEN COALESCE( - NULLIF(history.subscription.customer.shipping.address.country, ''), - NULLIF(history.subscription.customer.address.country, ''), - charge_summaries.latest_card_country + THEN + CASE + WHEN history.subscription.customer.shipping.address.country IS NOT NULL + THEN STRUCT( + history.subscription.customer.shipping.address.country AS country_code, + history.subscription.customer.shipping.address.state AS state_code + ) + WHEN history.subscription.customer.address.country IS NOT NULL + THEN STRUCT( + history.subscription.customer.address.country AS country_code, + history.subscription.customer.address.state AS state_code ) - -- SubPlat copies the PayPal billing agreement country to the customer's address. + ELSE STRUCT( + charge_summaries.latest_card_country AS country_code, + charge_summaries.latest_card_state AS state_code + ) + END + -- SubPlat copied the PayPal billing agreement country to the customer's address before we enabled Stripe Tax (FXA-5457). WHEN paypal_subscriptions.subscription_id IS NOT NULL - THEN NULLIF(history.subscription.customer.address.country, '') - ELSE charge_summaries.latest_card_country - END AS country_code, + THEN STRUCT( + history.subscription.customer.address.country AS country_code, + history.subscription.customer.address.state AS state_code + ) + ELSE STRUCT( + charge_summaries.latest_card_country AS country_code, + charge_summaries.latest_card_state AS state_code + ) + END + ).*, plan_services.services, subscription_item.plan.product.id AS provider_product_id, subscription_item.plan.product.name AS product_name, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -45,6 +45,10 @@ - name: country_code type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml 2024-06-28 21:43:50.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml 2024-06-28 21:54:51.000000000 +0000 @@ -8,7 +8,8 @@ This table's schema closely mirrors Stripe's subscriptions API (https://stripe.com/docs/api/subscriptions/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -53,6 +53,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -218,6 +222,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml 2024-06-28 21:43:50.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml 2024-06-28 21:54:51.000000000 +0000 @@ -4,7 +4,8 @@ This table's schema closely mirrors Stripe's subscriptions API (https://stripe.com/docs/api/subscriptions/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml 2024-06-28 21:42:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml 2024-06-28 21:42:46.000000000 +0000 @@ -21,6 +21,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -183,6 +187,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/clients_last_seen/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/clients_last_seen/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/clients_last_seen/schema.yaml 2024-06-28 21:42:38.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/clients_last_seen/schema.yaml 2024-06-28 21:49:10.000000000 +0000 @@ -77,44 +77,33 @@ - name: submission_date type: DATE mode: NULLABLE - description: Submission Date - name: first_seen_date type: DATE mode: NULLABLE - description: First Seen Date - name: second_seen_date type: DATE mode: NULLABLE - description: Second Seen Date - name: days_seen_bits type: INTEGER mode: NULLABLE - description: Bit pattern to identify when the client_id has sent a main ping in - the previous 28 days. - name: days_visited_1_uri_bits type: INTEGER mode: NULLABLE - description: Days Visited 1 URI Bits - name: days_visited_5_uri_bits type: INTEGER mode: NULLABLE - description: Days Visited 5 URI Bits - name: days_visited_10_uri_bits type: INTEGER mode: NULLABLE - description: Days Visited 10 URI Bits - name: days_had_8_active_ticks_bits type: INTEGER mode: NULLABLE - description: Days Had 8 Active Ticks Bits - name: days_opened_dev_tools_bits type: INTEGER mode: NULLABLE - description: Days Opened Dev Tools Bits - name: days_interacted_bits type: INTEGER mode: NULLABLE - description: Days Interacted Bits - name: days_visited_1_uri_normal_mode_bits type: INTEGER mode: NULLABLE @@ -143,23 +132,18 @@ - name: client_id type: STRING mode: NULLABLE - description: Client ID - name: aborts_content_sum type: INTEGER mode: NULLABLE - description: Aborts Content Sum - name: aborts_gmplugin_sum type: INTEGER mode: NULLABLE - description: null - name: aborts_plugin_sum type: INTEGER mode: NULLABLE - description: Aborts Plugin Sum - name: active_addons_count_mean type: FLOAT mode: NULLABLE - description: Active Addons Count Mean - name: active_addons type: RECORD mode: REPEATED @@ -167,92 +151,69 @@ - name: addon_id type: STRING mode: NULLABLE - description: Add-On Identifier - name: blocklisted type: BOOLEAN mode: NULLABLE - description: Blocklisted - name: name type: STRING mode: NULLABLE - description: Name - name: user_disabled type: BOOLEAN mode: NULLABLE - description: User Disabled - name: app_disabled type: BOOLEAN mode: NULLABLE - description: App Disabled - name: version type: STRING mode: NULLABLE - description: Version - name: scope type: INTEGER mode: NULLABLE - description: Scope - name: type type: STRING mode: NULLABLE - description: Type - name: foreign_install type: BOOLEAN mode: NULLABLE - description: Foreign Install - name: has_binary_components type: BOOLEAN mode: NULLABLE - description: Has Binary Components - name: install_day type: INTEGER mode: NULLABLE - description: Install Day - name: update_day type: INTEGER mode: NULLABLE - description: Update Day - name: signed_state type: INTEGER mode: NULLABLE - description: Signed State - name: is_system type: BOOLEAN mode: NULLABLE - description: Is System - name: is_web_extension type: BOOLEAN mode: NULLABLE - description: Is Web Extension - name: multiprocess_compatible type: BOOLEAN mode: NULLABLE - description: Multiprocess Compatible - description: Active Addons - name: active_hours_sum type: FLOAT mode: NULLABLE - description: Active Hours Sum - name: addon_compatibility_check_enabled type: BOOLEAN mode: NULLABLE - description: Addon Compatibility Check Enabled - name: app_build_id type: STRING mode: NULLABLE - description: App Build ID - name: app_display_version type: STRING mode: NULLABLE - description: App Display Version - name: app_name type: STRING mode: NULLABLE - description: App Name - name: app_version type: STRING mode: NULLABLE - description: App Version - name: attribution type: RECORD mode: NULLABLE @@ -260,196 +221,147 @@ - name: source type: STRING mode: NULLABLE - description: Source - name: medium type: STRING mode: NULLABLE - description: Medium - name: campaign type: STRING mode: NULLABLE - description: Campaign - name: content type: STRING mode: NULLABLE - description: Content - name: experiment type: STRING mode: NULLABLE - description: Experiment - name: variation type: STRING mode: NULLABLE - description: Variation - name: dltoken type: STRING mode: NULLABLE - description: Download Token - name: dlsource type: STRING mode: NULLABLE - description: Download Source - name: ua type: STRING mode: NULLABLE - description: null - description: Attribution - name: blocklist_enabled type: BOOLEAN mode: NULLABLE - description: Blocklist Enabled - name: channel type: STRING mode: NULLABLE - description: Channel - name: client_clock_skew_mean type: FLOAT mode: NULLABLE - description: Client Clock Skew Mean - name: client_submission_latency_mean type: FLOAT mode: NULLABLE - description: Client Submission Latency Mean - name: cpu_cores type: INTEGER mode: NULLABLE - description: CPU Cores - name: cpu_count type: INTEGER mode: NULLABLE - description: CPU Count - name: cpu_family type: INTEGER mode: NULLABLE - description: CPU Family - name: cpu_l2_cache_kb type: INTEGER mode: NULLABLE - description: CPU L2 Cache KB - name: cpu_l3_cache_kb type: INTEGER mode: NULLABLE - description: CPU L3 Cache KB - name: cpu_model type: INTEGER mode: NULLABLE - description: CPU Model - name: cpu_speed_mhz type: INTEGER mode: NULLABLE - description: CPU Speed MHz - name: cpu_stepping type: INTEGER mode: NULLABLE - description: CPU Stepping - name: cpu_vendor type: STRING mode: NULLABLE - description: CPU Vendor - name: crashes_detected_content_sum type: INTEGER mode: NULLABLE - description: Crashes Detected Content Sum - name: crashes_detected_gmplugin_sum type: INTEGER mode: NULLABLE - description: null - name: crashes_detected_plugin_sum type: INTEGER mode: NULLABLE - description: Crashes Detected Plugin Sum - name: crash_submit_attempt_content_sum type: INTEGER mode: NULLABLE - description: Crash Submit Attempt Content Sum - name: crash_submit_attempt_main_sum type: INTEGER mode: NULLABLE - description: Crash Submit Attempt Main Sum - name: crash_submit_attempt_plugin_sum type: INTEGER mode: NULLABLE - description: Crash Submit Attempt Plugin Sum - name: crash_submit_success_content_sum type: INTEGER mode: NULLABLE - description: Crash Submit Success Content Sum - name: crash_submit_success_main_sum type: INTEGER mode: NULLABLE - description: Crash Submit Success Main Sum - name: crash_submit_success_plugin_sum type: INTEGER mode: NULLABLE - description: Crash Submit Success Plugin Sum - name: default_search_engine type: STRING mode: NULLABLE - description: Default Search Engine - name: default_search_engine_data_load_path type: STRING mode: NULLABLE - description: Default Search Engine Data Load Path - name: default_search_engine_data_name type: STRING mode: NULLABLE - description: Default Search Engine Data Name - name: default_search_engine_data_origin type: STRING mode: NULLABLE - description: Default Search Engine Data origin - name: default_search_engine_data_submission_url type: STRING mode: NULLABLE - description: Default Search Engine Data Submission URL - name: devtools_toolbox_opened_count_sum type: INTEGER mode: NULLABLE - description: Dev Tools Toolbox Opened Count Sum - name: distribution_id type: STRING mode: NULLABLE - description: Distribution ID - name: e10s_enabled type: BOOLEAN mode: NULLABLE - description: E10s Enabled - name: env_build_arch type: STRING mode: NULLABLE - description: Environment Build Arch - name: env_build_id type: STRING mode: NULLABLE - description: Environment Build ID - name: env_build_version type: STRING mode: NULLABLE - description: Environment Build Version - name: environment_settings_intl_accept_languages type: STRING mode: REPEATED - description: Environment Settings Intl Accept Languages - name: environment_settings_intl_app_locales type: STRING mode: REPEATED - description: Environment Settings Intl App Locales - name: environment_settings_intl_available_locales type: STRING mode: REPEATED - description: Environment Settings Intl Available Locales - name: environment_settings_intl_requested_locales type: STRING mode: REPEATED - description: Environment Settings Intl Requested Localed - name: environment_settings_intl_system_locales type: STRING mode: REPEATED - description: Environment Settings Intl System Locales - name: environment_settings_intl_regional_prefs_locales type: STRING mode: REPEATED - description: Environment Settings Intl Regional Prefs Locales - name: experiments type: RECORD mode: REPEATED @@ -457,351 +369,264 @@ - name: key type: STRING mode: NULLABLE - description: Key - name: value type: STRING mode: NULLABLE - description: Value - description: Experiments - name: first_paint_mean type: FLOAT mode: NULLABLE - description: First Paint Mean - name: flash_version type: STRING mode: NULLABLE - description: Flash Version - name: country type: STRING mode: NULLABLE - description: Country - name: city type: STRING mode: NULLABLE - description: City - name: geo_subdivision1 type: STRING mode: NULLABLE - description: Geo Subdivision 1 - name: geo_subdivision2 type: STRING mode: NULLABLE - description: Geo Subdivision 2 - name: isp_name type: STRING mode: NULLABLE - description: ISP Name - name: isp_organization type: STRING mode: NULLABLE - description: ISP Organization - name: gfx_features_advanced_layers_status type: STRING mode: NULLABLE - description: GFX Features Advanced Layers Status - name: gfx_features_d2d_status type: STRING mode: NULLABLE - description: GFX Features D2D Status - name: gfx_features_d3d11_status type: STRING mode: NULLABLE - description: GFX Features D3D11 Status - name: gfx_features_gpu_process_status type: STRING mode: NULLABLE - description: GFX Features GPU Process Status - name: histogram_parent_devtools_aboutdebugging_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools About Debugging Opened Count Sum - name: histogram_parent_devtools_animationinspector_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Animation Inspector Opened Count Sum - name: histogram_parent_devtools_browserconsole_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Browser Console Opened Count Sum - name: histogram_parent_devtools_canvasdebugger_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Canvas Debugger Opened Count Sum - name: histogram_parent_devtools_computedview_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Computed View Opened Count Sum - name: histogram_parent_devtools_custom_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Custom Opened Count Sum - name: histogram_parent_devtools_dom_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools DOM Opened Count Sum - name: histogram_parent_devtools_eyedropper_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Eyedropper Opened Count Sum - name: histogram_parent_devtools_fontinspector_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Font Inspector Opened Count Sum - name: histogram_parent_devtools_inspector_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Inspector Opened Count Sum - name: histogram_parent_devtools_jsbrowserdebugger_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools JS Browser Debugger Opened Count Sum - name: histogram_parent_devtools_jsdebugger_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Devtools JS Debugger Opened Count Sum - name: histogram_parent_devtools_jsprofiler_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools JS Profiler Opened Count Sum - name: histogram_parent_devtools_layoutview_opened_count_sum type: INTEGER mode: NULLABLE - description: null - name: histogram_parent_devtools_memory_opened_count_sum type: INTEGER mode: NULLABLE - name: histogram_parent_devtools_menu_eyedropper_opened_count_sum type: INTEGER mode: NULLABLE - description: null - name: histogram_parent_devtools_netmonitor_opened_count_sum type: INTEGER mode: NULLABLE - description: null - name: histogram_parent_devtools_options_opened_count_sum type: INTEGER mode: NULLABLE - description: null - name: histogram_parent_devtools_paintflashing_opened_count_sum type: INTEGER mode: NULLABLE - description: null - name: histogram_parent_devtools_picker_eyedropper_opened_count_sum type: INTEGER mode: NULLABLE - description: null - name: histogram_parent_devtools_responsive_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Responsive Opened Count Sum - name: histogram_parent_devtools_ruleview_opened_count_sum type: INTEGER mode: NULLABLE - description: Histogram Parent Dev Tools Rule View Opened Count Sum - name: histogram_parent_devtools_scratchpad_opened_count_sum t ```

⚠️ Only part of the diff is displayed.

Link to full diff

dataops-ci-bot commented 2 days ago

Integration report for "🧹"

`sql.diff`

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1: backfill.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8: backfill.yaml 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-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-28 22:23:13.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-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-28 22:23:13.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-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-06-28 22:23:16.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-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-06-28 22:23:16.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/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-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_page/schema.yaml 2024-06-28 22:22:17.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,103 +17,110 @@ - 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. - This will be null when the source data wasn't exported directly to BigQuery by Google. + description: 'Whether Google Search considers the page to be providing a good page + experience. + + This will be null when the source data wasn''t exported directly to BigQuery by + Google.' - 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). - This will be null when the source data wasn't exported directly to BigQuery by Google. + description: 'How the search result appeared (e.g. normal result, translated result, + video). + + This will be null when the source data wasn''t exported directly to BigQuery by + Google.' - 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 @@ -124,13 +132,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 @@ -138,6 +148,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-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/google_search_console/search_impressions_by_site/schema.yaml 2024-06-28 22:22:17.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/monitoring/event_monitoring_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring/event_monitoring_live/schema.yaml 2024-06-28 22:24:33.000000000 +0000 @@ -0,0 +1,37 @@ +fields: +- name: window_start + type: TIMESTAMP + mode: NULLABLE +- name: window_end + type: TIMESTAMP + mode: NULLABLE +- name: event_category + type: STRING + mode: NULLABLE +- name: event_name + type: STRING + mode: NULLABLE +- name: event_extra_key + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: channel + type: STRING + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: experiment + type: STRING + mode: NULLABLE +- name: experiment_branch + type: STRING + mode: NULLABLE +- name: total_events + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-06-28 22:15:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-06-28 22:24:33.000000000 +0000 @@ -1,7 +1,13 @@ fields: -- type: DATETIME - name: build_hour -- type: INTEGER - name: geckoview_major_version -- type: INTEGER - name: n_pings +- name: build_hour + type: DATETIME + mode: NULLABLE + description: null +- name: geckoview_major_version + type: INTEGER + mode: NULLABLE + description: null +- name: n_pings + type: INTEGER + mode: NULLABLE + description: null diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_aggregates_v1/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,9 +0,0 @@ -2024-06-27: - start_date: 2024-04-01 - end_date: 2024-05-31 - reason: https://mozilla-hub.atlassian.net/browse/RS-1247 - watchers: - - akommasani@mozilla.com - - skahmann@mozilla.com - - pissac@mozilla.com - status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/backfill.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -5,4 +5,4 @@ watchers: - akommasani@mozilla.com - skahmannz@mozilla.com - status: Complete + status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_aggregates_v8/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,9 +0,0 @@ -2024-06-27: - start_date: 2024-04-01 - end_date: 2024-05-31 - reason: https://mozilla-hub.atlassian.net/browse/RS-1247 - watchers: - - akommasani@mozilla.com - - skahmann@mozilla.com - - pissac@mozilla.com - status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/backfill.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -5,4 +5,4 @@ watchers: - akommasani@mozilla.com - skahmann@mozilla.com - status: Complete + status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/daily_active_logical_subscriptions_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -51,6 +51,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscription_events_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -57,6 +57,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED @@ -241,6 +245,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/query.sql 2024-06-28 22:16:42.000000000 +0000 @@ -146,6 +146,7 @@ ) AS customer_subscription_number, history.subscription.country_code, COALESCE(countries.name, history.subscription.country_code, 'Unknown') AS country_name, + history.subscription.state_code, history.subscription.services, history.subscription.provider_product_id, history.subscription.product_name, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/logical_subscriptions_history_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -54,6 +54,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/monthly_active_logical_subscriptions_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -54,6 +54,10 @@ - name: country_name type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml 2024-06-28 22:17:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/metadata.yaml 2024-06-28 22:28:50.000000000 +0000 @@ -8,7 +8,8 @@ This table's schema closely mirrors Stripe's customers API (https://stripe.com/docs/api/customers/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_history_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -38,6 +38,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -200,6 +204,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml 2024-06-28 22:17:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/metadata.yaml 2024-06-28 22:28:50.000000000 +0000 @@ -7,7 +7,8 @@ This table's schema closely mirrors Stripe's customers API (https://stripe.com/docs/api/customers/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/query.sql 2024-06-28 22:16:42.000000000 +0000 @@ -40,18 +40,69 @@ JSON_VALUE(customer.metadata.userid) AS userid, TO_HEX(SHA256(JSON_VALUE(customer.metadata.userid))) AS userid_sha256 ) AS metadata, - -- Limit address data to just country since the metadata includes FxA user IDs - -- and this is in a Mozilla-confidential dataset. - STRUCT(customer.address.country) AS address, + -- Limit address data to country and state since the metadata includes FxA user IDs + -- and this is in a Mozilla-confidential dataset. State is only for US and Canada. + STRUCT( + NULLIF(customer.address.country, "") AS country, + IF( + customer.address.country IN ("US", "CA"), + COALESCE( + -- Only use two-letter codes for consistency since billing addresses sometimes have full names + IF(LENGTH(customer.address.state) = 2, customer.address.state, NULL), + us_zip_code_prefixes.state_code, + ca_postal_districts.province_code + ), + NULL + ) AS state + ) AS address, ( SELECT AS STRUCT - customer.shipping.* REPLACE (STRUCT(customer.shipping.address.country) AS address) + customer.shipping.* REPLACE ( + STRUCT( + NULLIF(customer.shipping.address.country, "") AS country, + IF( + customer.shipping.address.country IN ("US", "CA"), + COALESCE( + IF( + LENGTH(customer.shipping.address.state) = 2, + customer.shipping.address.state, + NULL + ), + us_shipping_zip_code_prefixes.state_code, + ca_shipping_postal_districts.province_code + ), + NULL + ) AS state + ) AS address + ) ) AS shipping ) ) AS customer, ROW_NUMBER() OVER customer_changes_asc AS customer_change_number FROM `moz-fx-data-shared-prod`.stripe_external.customers_changelog_v1 + -- try to get state using postal code if state field is unavailable + LEFT JOIN + `moz-fx-data-shared-prod.static.us_zip_code_prefixes_v1` AS us_shipping_zip_code_prefixes + ON customer.shipping.address.country = "US" + AND LEFT( + customer.shipping.address.postal_code, + 3 + ) = us_shipping_zip_code_prefixes.zip_code_prefix + LEFT JOIN + `moz-fx-data-shared-prod.static.us_zip_code_prefixes_v1` AS us_zip_code_prefixes + ON customer.address.country = "US" + AND LEFT(customer.address.postal_code, 3) = us_zip_code_prefixes.zip_code_prefix + LEFT JOIN + `moz-fx-data-shared-prod.static.ca_postal_districts_v1` AS ca_shipping_postal_districts + ON customer.shipping.address.country = "CA" + AND UPPER( + LEFT(customer.shipping.address.postal_code, 1) + ) = ca_shipping_postal_districts.postal_district_code + LEFT JOIN + `moz-fx-data-shared-prod.static.ca_postal_districts_v1` AS ca_postal_districts + ON customer.address.country = "CA" + AND UPPER(LEFT(customer.address.postal_code, 1)) = ca_postal_districts.postal_district_code WINDOW customer_changes_asc AS ( PARTITION BY @@ -68,7 +119,7 @@ TIMESTAMP '2022-03-25 00:02:29' AS `timestamp`, STRUCT( id, - STRUCT(address.country) AS address, + STRUCT(address.country, address.state) AS address, created, CAST(NULL AS STRING) AS default_source_id, CAST( @@ -103,7 +154,7 @@ CAST(NULL AS STRING) AS userid, JSON_VALUE(metadata.userid_sha256) AS userid_sha256 ) AS metadata, - CAST(NULL AS STRUCT

>) AS shipping, + CAST(NULL AS STRUCT

>) AS shipping, CAST(NULL AS STRING) AS tax_exempt ) AS customer, 1 AS customer_change_number diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_customers_revised_changelog_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -48,6 +48,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -210,6 +214,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/query.sql 2024-06-28 22:16:42.000000000 +0000 @@ -62,14 +62,21 @@ SELECT history.id AS subscriptions_history_id, ARRAY_AGG( - cards.country IGNORE NULLS + IF( + cards.country IS NOT NULL, + STRUCT( + cards.country AS latest_card_country, + COALESCE(charge_us_zip.state_code, charge_ca_post.province_code) AS latest_card_state + ), + NULL + ) IGNORE NULLS ORDER BY - -- Prefer charges that succeeded. + -- Prefer charges that succeeded IF(charges.status = 'succeeded', 1, 2), charges.created DESC LIMIT 1 - )[SAFE_ORDINAL(1)] AS latest_card_country, + )[SAFE_ORDINAL(1)].*, LOGICAL_OR(refunds.status = 'succeeded') AS has_refunds, LOGICAL_OR( charges.fraud_details_user_report = 'fraudulent' @@ -94,6 +101,18 @@ LEFT JOIN `moz-fx-data-shared-prod.stripe_external.refund_v1` AS refunds ON charges.id = refunds.charge_id + -- charges usually have postal code and are sometimes associated with + -- a card that does not have a state or postal code + LEFT JOIN + `moz-fx-data-shared-prod.static.us_zip_code_prefixes_v1` AS charge_us_zip + ON cards.country = "US" + AND LEFT(charges.billing_detail_address_postal_code, 3) = charge_us_zip.zip_code_prefix + LEFT JOIN + `moz-fx-data-shared-prod.static.ca_postal_districts_v1` AS charge_ca_post + ON cards.country = "CA" + AND UPPER( + LEFT(charges.billing_detail_address_postal_code, 1) + ) = charge_ca_post.postal_district_code GROUP BY subscriptions_history_id ) @@ -109,7 +128,8 @@ history.valid_from, history.valid_to, history.id AS provider_subscriptions_history_id, - STRUCT( + ( + SELECT AS STRUCT CONCAT( 'Stripe-', history.subscription.id, @@ -124,24 +144,44 @@ history.subscription.customer.id AS provider_customer_id, history.subscription.customer.metadata.userid AS mozilla_account_id, history.subscription.customer.metadata.userid_sha256 AS mozilla_account_id_sha256, + ( CASE - -- Use the same address hierarchy as Stripe Tax after we enabled Stripe Tax (FXA-5457). + -- Use the same address hierarchy as Stripe Tax after we enabled Stripe Tax on 2022-12-01 (FXA-5457). -- https://stripe.com/docs/tax/customer-locations#address-hierarchy WHEN DATE(history.valid_to) >= '2022-12-01' AND ( DATE(history.subscription.ended_at) >= '2022-12-01' OR history.subscription.ended_at IS NULL ) - THEN COALESCE( - NULLIF(history.subscription.customer.shipping.address.country, ''), - NULLIF(history.subscription.customer.address.country, ''), - charge_summaries.latest_card_country + THEN + CASE + WHEN history.subscription.customer.shipping.address.country IS NOT NULL + THEN STRUCT( + history.subscription.customer.shipping.address.country AS country_code, + history.subscription.customer.shipping.address.state AS state_code + ) + WHEN history.subscription.customer.address.country IS NOT NULL + THEN STRUCT( + history.subscription.customer.address.country AS country_code, + history.subscription.customer.address.state AS state_code ) - -- SubPlat copies the PayPal billing agreement country to the customer's address. + ELSE STRUCT( + charge_summaries.latest_card_country AS country_code, + charge_summaries.latest_card_state AS state_code + ) + END + -- SubPlat copied the PayPal billing agreement country to the customer's address before we enabled Stripe Tax (FXA-5457). WHEN paypal_subscriptions.subscription_id IS NOT NULL - THEN NULLIF(history.subscription.customer.address.country, '') - ELSE charge_summaries.latest_card_country - END AS country_code, + THEN STRUCT( + history.subscription.customer.address.country AS country_code, + history.subscription.customer.address.state AS state_code + ) + ELSE STRUCT( + charge_summaries.latest_card_country AS country_code, + charge_summaries.latest_card_state AS state_code + ) + END + ).*, plan_services.services, subscription_item.plan.product.id AS provider_product_id, subscription_item.plan.product.name AS product_name, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_logical_subscriptions_history_v1/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -45,6 +45,10 @@ - name: country_code type: STRING mode: NULLABLE + - name: state_code + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: services type: RECORD mode: REPEATED diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml 2024-06-28 22:17:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/metadata.yaml 2024-06-28 22:28:50.000000000 +0000 @@ -8,7 +8,8 @@ This table's schema closely mirrors Stripe's subscriptions API (https://stripe.com/docs/api/subscriptions/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_history_v2/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -53,6 +53,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -218,6 +222,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml 2024-06-28 22:17:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/metadata.yaml 2024-06-28 22:28:50.000000000 +0000 @@ -4,7 +4,8 @@ This table's schema closely mirrors Stripe's subscriptions API (https://stripe.com/docs/api/subscriptions/object). Fields which Fivetran doesn't sync and fields we've specifically chosen not to include (e.g. PII) have been omitted. - In particular, all address fields except country have been omitted because Firefox Account user IDs are present and this is in a Mozilla-confidential dataset. + In particular, all address fields except country and state have been omitted because Firefox Account user IDs + are present and this is in a Mozilla-confidential dataset. owners: - srose@mozilla.com labels: diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml 2024-06-28 22:16:42.000000000 +0000 @@ -21,6 +21,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: created type: TIMESTAMP mode: NULLABLE @@ -183,6 +187,10 @@ type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). + - name: state + type: STRING + mode: NULLABLE + description: Two-letter state/province code (only for US and Canada). - name: tax_exempt type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/eng_workflow_build_parquet/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/eng_workflow_build_parquet/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/eng_workflow_build_parquet/schema.yaml 2024-06-28 22:16:31.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry/eng_workflow_build_parquet/schema.yaml 2024-06-28 22:22:52.000000000 +0000 @@ -28,48 +28,36 @@ - name: artifact type: BOOLEAN mode: NULLABLE - description: true if --enable-artifact-builds - name: ccache type: BOOLEAN mode: NULLABLE - description: true if ccache is in use (--with-ccache) - name: compiler type: STRING mode: NULLABLE - description: The compiler type in use (CC_TYPE) - name: debug type: BOOLEAN mode: NULLABLE - description: true if build is debug (--enable-debug) - name: icecream type: BOOLEAN mode: NULLABLE - description: true if icecream in use - name: opt type: BOOLEAN mode: NULLABLE - description: true if build is optimized (--enable-optimize) - name: sccache type: BOOLEAN mode: NULLABLE - description: true if ccache in use is sccache - description: Selected build options - name: client_id type: STRING mode: NULLABLE - description: A UUID to uniquely identify a client - name: command type: STRING mode: NULLABLE - description: The mach command that was invoked - name: duration_ms type: FLOAT mode: NULLABLE - description: Command duration in milliseconds - name: success type: BOOLEAN mode: NULLABLE - description: true if the command succeeded - name: system type: RECORD mode: NULLABLE @@ -77,40 +65,30 @@ - name: os type: STRING mode: NULLABLE - description: Operating system - name: cpu_brand type: STRING mode: NULLABLE - description: CPU brand string from CPUID - name: drive_is_ssd type: BOOLEAN mode: NULLABLE - description: true if the source directory is on a solid-state disk - name: logical_cores type: INTEGER mode: NULLABLE - description: Number of logical CPU cores present - name: memory_gb type: INTEGER mode: NULLABLE - description: System memory in GB - name: physical_cores type: INTEGER mode: NULLABLE - description: Number of physical CPU cores present - name: virtual_machine type: BOOLEAN mode: NULLABLE - description: true if the OS appears to be running in a virtual machine - name: time type: STRING mode: NULLABLE - description: Time at which this event happened - name: exception type: STRING mode: NULLABLE - description: If a Python exception was encountered during the execution of the command, - this value contains the result of calling `repr` on the exception object. - name: file_types_changed type: RECORD mode: REPEATED @@ -118,11 +96,9 @@ - name: count type: INTEGER mode: NULLABLE - description: Count of changed files with this extension - name: ext type: STRING mode: NULLABLE - description: File extension - name: build_attrs type: RECORD mode: NULLABLE @@ -130,9 +106,6 @@ - name: clobber type: BOOLEAN mode: NULLABLE - description: true if the build was a clobber/full build - name: cpu_percent type: FLOAT mode: NULLABLE - description: cpu utilization observed during a build - description: Attributes characterizing a build diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 22:17:37.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 22:28:46.000000000 +0000 @@ -1,8 +1,6 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields. - Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, - and now rely on Glean's newtab ping as of Fx120. There's an effective cutover version of Fx123. + daily client-level aggregates of Sponsored Tiles-related fields owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 22:15:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 22:16:42.000000000 +0000 @@ -43,8 +43,7 @@ 1, 2 ), ------- DESKTOP Dismissals and Disables ---- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) + ------ DESKTOP Dismissals and Disables desktop_activity_stream_events AS ( SELECT client_id, @@ -63,69 +62,9 @@ `moz-fx-data-shared-prod.activity_stream.events` WHERE DATE(submission_timestamp) = @submission_date - AND CAST(metadata.user_agent.version AS INT64) < 123 GROUP BY - client_id, - submission_date -), ---- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) -desktop_newtab_events AS ( - SELECT - n.client_info.client_id AS client_id, - n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, - DATE(submission_timestamp) AS submission_date, - COUNTIF( - e.category = 'topsites' - AND e.name = 'dismiss' - AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' - ) AS sponsored_tiles_dismissal_count, - COUNTIF( - e.name = 'pref_changed' - AND `mozfun.map.get_key`( - e.extra, - 'pref_name' - ) = 'browser.newtabpage.activity-stream.showSponsoredTopSites' - AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' - ) AS sponsored_tiles_disable_count - FROM - `moz-fx-data-shared-prod.firefox_desktop.newtab` n, - UNNEST(events) e - WHERE - DATE(submission_timestamp) = @submission_date - AND `mozfun`.norm.browser_version_info(client_info.app_display_version).major_version >= 123 - GROUP BY - client_id, - legacy_telemetry_client_id, - submission_date -), -desktop_joint_events AS ( - SELECT - n.submission_date, - n.legacy_telemetry_client_id AS client_id, - n.sponsored_tiles_dismissal_count, - n.sponsored_tiles_disable_count - FROM - desktop_newtab_events n - UNION ALL - SELECT - a.submission_date, - a.client_id, - a.sponsored_tiles_dismissal_count, - a.sponsored_tiles_disable_count - FROM - desktop_activity_stream_events a -), -desktop_agg_events AS ( - SELECT - submission_date, - client_id, - SUM(sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, - SUM(sponsored_tiles_disable_count) AS sponsored_tiles_disable_count - FROM - desktop_joint_events - GROUP BY - submission_date, - client_id + 1, + 2 ), ------ iOS SPONSORED TILES ios_data AS ( @@ -253,7 +192,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_agg_events + desktop_activity_stream_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff

mozilla / bigquery-etl