Deng 3763 increment changed tables

[chelseybeck]

What changed:

• changed sync tables to incremental + partitioned
• removed raw sql checks - they're no longer required since we're using incremental
• added update timestamp to subscriptions sync payload + changed to use this filter in sync
• removed changed subscriptions table as no longer needed

Checklist for reviewer:

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

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

• [ ] Follow the change control procedure

┆Issue is synchronized with this Jira Task

[dataops-ci-bot]

Integration report for "updating comments"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_braze.py /tmp/workspace/generated-sql/dags/bqetl_braze.py --- /tmp/workspace/main-generated-sql/dags/bqetl_braze.py 2024-05-31 20:06:24.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_braze.py 2024-05-31 20:18:48.000000000 +0000 @@ -175,18 +175,6 @@ task_concurrency=1, ) - braze_external__changed_subscriptions__v1 = bigquery_etl_query( - task_id="braze_external__changed_subscriptions__v1", - destination_table="changed_subscriptions_v1", - dataset_id="braze_external", - project_id="moz-fx-data-shared-prod", - owner="cbeck@mozilla.com", - email=["cbeck@mozilla.com", "leli@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - braze_external__changed_users__v1 = bigquery_etl_query( task_id="braze_external__changed_users__v1", destination_table="changed_users_v1", @@ -350,19 +338,6 @@ retries=0, ) - checks__fail_braze_external__changed_subscriptions__v1 = bigquery_dq_check( - task_id="checks__fail_braze_external__changed_subscriptions__v1", - source_table="changed_subscriptions_v1", - dataset_id="braze_external", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="cbeck@mozilla.com", - email=["cbeck@mozilla.com", "leli@mozilla.com"], - depends_on_past=False, - task_concurrency=1, - retries=0, - ) - checks__warn_braze_external__changed_firefox_subscriptions_sync__v1 = bigquery_dq_check( task_id="checks__warn_braze_external__changed_firefox_subscriptions_sync__v1", source_table="changed_firefox_subscriptions_sync_v1", @@ -402,19 +377,6 @@ retries=0, ) - checks__warn_braze_external__changed_subscriptions__v1 = bigquery_dq_check( - task_id="checks__warn_braze_external__changed_subscriptions__v1", - source_table="changed_subscriptions_v1", - dataset_id="braze_external", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=False, - owner="cbeck@mozilla.com", - email=["cbeck@mozilla.com", "leli@mozilla.com"], - depends_on_past=False, - task_concurrency=1, - retries=0, - ) - checks__warn_braze_external__changed_users__v1 = bigquery_dq_check( task_id="checks__warn_braze_external__changed_users__v1", source_table="changed_users_v1", @@ -494,7 +456,7 @@ braze_derived__waitlists__v1.set_upstream(checks__fail_braze_derived__users__v1) braze_external__changed_firefox_subscriptions_sync__v1.set_upstream( - checks__fail_braze_external__changed_subscriptions__v1 + checks__fail_braze_derived__subscriptions__v1 ) braze_external__changed_newsletters_sync__v1.set_upstream( @@ -505,10 +467,6 @@ checks__fail_braze_derived__products__v1 ) - braze_external__changed_subscriptions__v1.set_upstream( - checks__fail_braze_derived__subscriptions__v1 - ) - braze_external__changed_users__v1.set_upstream( braze_external__users_previous_day_snapshot__v2 ) @@ -551,14 +509,6 @@ checks__fail_braze_derived__waitlists__v1.set_upstream(braze_derived__waitlists__v1) - checks__fail_braze_external__changed_subscriptions__v1.set_upstream( - braze_external__changed_subscriptions__v1 - ) - - checks__fail_braze_external__changed_subscriptions__v1.set_upstream( - checks__fail_braze_derived__subscriptions__v1 - ) - checks__warn_braze_external__changed_firefox_subscriptions_sync__v1.set_upstream( braze_external__changed_firefox_subscriptions_sync__v1 ) @@ -571,14 +521,6 @@ braze_external__changed_products_sync__v1 ) - checks__warn_braze_external__changed_subscriptions__v1.set_upstream( - braze_external__changed_subscriptions__v1 - ) - - checks__warn_braze_external__changed_subscriptions__v1.set_upstream( - checks__fail_braze_derived__subscriptions__v1 - ) - checks__warn_braze_external__changed_users__v1.set_upstream( braze_external__changed_users__v1 ) Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external: changed_subscriptions_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 20:05:46.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.interaction_v1` + `moz-fx-data-shared-prod.bedrock_live.non_interaction_v1` UNION ALL SELECT submission_timestamp, @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.events_v1` + `moz-fx-data-shared-prod.bedrock_live.interaction_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.bedrock_live.non_interaction_v1` + `moz-fx-data-shared-prod.bedrock_live.events_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/checks.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/checks.sql 2024-05-31 20:03:52.000000000 +0000 @@ -1,20 +1,3 @@ --- raw SQL checks --- checking to see if there is new data since the last run --- if not, fail or we will have blank sync tables - -#fail -ASSERT( - SELECT - COUNT(1) - FROM - `moz-fx-data-shared-prod.braze_derived.newsletters_v1`, - UNNEST(newsletters) AS newsletters - WHERE - newsletters.update_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 HOUR) -) > 0 -AS - "No new records in the braze_derived.newsletters_v1 table in the last 7 hours"; - -- macro checks #fail diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/metadata.yaml 2024-05-31 20:15:34.000000000 +0000 @@ -27,7 +27,6 @@ references: checks.sql: - .. - - moz-fx-data-shared-prod.braze_derived.newsletters_v1 query.sql: - moz-fx-data-shared-prod.braze_derived.users_v1 - moz-fx-data-shared-prod.ctms_braze.ctms_newsletters diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/checks.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/checks.sql 2024-05-31 20:03:52.000000000 +0000 @@ -1,22 +1,3 @@ --- raw SQL checks --- checking to see if there is new data since the last run --- if not, fail or we will have blank sync tables - -#fail -ASSERT( --- Retrieves the maximum subscription updated timestamp from the last run to only --- select recently changed records - SELECT - COUNT(1) - FROM - `moz-fx-data-shared-prod.braze_derived.products_v1`, - UNNEST(products) AS products - WHERE - products.subscription_updated_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 HOUR) -) > 0 -AS - "No new records in the braze_derived.products_v1 table in the last 7 hours"; - -- macro checks #fail diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/metadata.yaml 2024-05-31 20:15:34.000000000 +0000 @@ -27,7 +27,6 @@ references: checks.sql: - .. - - moz-fx-data-shared-prod.braze_derived.products_v1 query.sql: - moz-fx-data-shared-prod.braze_derived.users_v1 - moz-fx-data-shared-prod.ctms_braze.ctms_fxa diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/checks.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/checks.sql 2024-05-31 20:03:52.000000000 +0000 @@ -1,3 +1,5 @@ +--macro checks + #fail {{ not_null(["braze_subscription_name", "description", "mozilla_subscription_id", "firefox_subscription_id", "mozilla_dev_subscription_id", "basket_slug"]) }} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/checks.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/checks.sql 2024-05-31 20:03:52.000000000 +0000 @@ -1,19 +1,3 @@ --- raw SQL checks --- checking to see if there is new data since the last run --- if not, fail or we will have blank sync tables - -#fail -ASSERT( - SELECT - COUNT(1) - FROM - `moz-fx-data-shared-prod.braze_derived.users_v1` AS users - WHERE - users.update_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 HOUR) -) > 0 -AS - "No new records in the braze_derived.users_v1 table in the last 7 hours"; - -- macro checks #fail diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/metadata.yaml 2024-05-31 20:15:34.000000000 +0000 @@ -28,7 +28,6 @@ references: checks.sql: - .. - - moz-fx-data-shared-prod.braze_derived.users_v1 query.sql: - moz-fx-data-shared-prod.braze_derived.subscriptions_map_v1 - moz-fx-data-shared-prod.ctms_braze.ctms_emails diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/checks.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/checks.sql 2024-05-31 20:03:52.000000000 +0000 @@ -1,19 +1,4 @@ --- raw SQL checks --- checking to see if there is new data since the last run --- if not, fail or we will have blank sync tables - -#fail -ASSERT( - SELECT - COUNT(1) - FROM - `moz-fx-data-shared-prod.braze_derived.waitlists_v1`, - UNNEST(waitlists) AS waitlists - WHERE - waitlists.update_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 HOUR) -) > 0 -AS - "No new records in the braze_derived.waitlists_v1 table in the last 7 hours"; +-- macro checks #fail {{ not_null(["external_id"]) }} -- to do: add array values diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/metadata.yaml 2024-05-31 20:15:34.000000000 +0000 @@ -27,7 +27,6 @@ references: checks.sql: - .. - - moz-fx-data-shared-prod.braze_derived.waitlists_v1 query.sql: - moz-fx-data-shared-prod.braze_derived.users_v1 - moz-fx-data-shared-prod.ctms_braze.ctms_waitlists diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/metadata.yaml 2024-05-31 20:15:34.000000000 +0000 @@ -33,4 +33,5 @@ checks.sql: - .. query.sql: - - moz-fx-data-shared-prod.braze_external.changed_subscriptions_v1 + - moz-fx-data-shared-prod.braze_derived.subscriptions_v1 + - moz-fx-data-shared-prod.braze_external.changed_firefox_subscriptions_sync_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/query.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/query.sql 2024-05-31 20:03:52.000000000 +0000 @@ -1,4 +1,26 @@ --- Construct the JSON payload in Braze required format +-- get the latest update timestamp from the last sync +WITH max_update AS ( + SELECT + MAX( + CAST(JSON_EXTRACT_SCALAR(payload, '$.update_timestamp') AS TIMESTAMP) + ) AS latest_subscription_updated_at + FROM + `moz-fx-data-shared-prod.braze_external.changed_firefox_subscriptions_sync_v1` +), +-- get the max update timestamp for each external_id in subscriptions_v1 +max_subscriptions AS ( + SELECT + external_id, + MAX(subscriptions.update_timestamp) AS update_timestamp + FROM + `moz-fx-data-shared-prod.braze_derived.subscriptions_v1` + CROSS JOIN + UNNEST(subscriptions) AS subscriptions + GROUP BY + external_id +) +-- select all records from subscriptions_v1 that have been updated since the last sync +-- and construct JSON payload for Braze sync SELECT CURRENT_TIMESTAMP() AS UPDATED_AT, subscriptions.external_id AS EXTERNAL_ID, @@ -11,12 +33,19 @@ ) ORDER BY subscriptions_array.update_timestamp DESC - ) AS subscription_groups + ) AS subscription_groups, + max_subscriptions.update_timestamp ) ) AS PAYLOAD FROM - `moz-fx-data-shared-prod.braze_external.changed_subscriptions_v1` AS subscriptions + `moz-fx-data-shared-prod.braze_derived.subscriptions_v1` AS subscriptions CROSS JOIN UNNEST(subscriptions.subscriptions) AS subscriptions_array +JOIN + max_subscriptions + ON subscriptions.external_id = max_subscriptions.external_id +WHERE + subscriptions_array.update_timestamp > (SELECT latest_subscription_updated_at FROM max_update) GROUP BY - subscriptions.external_id; + subscriptions.external_id, + max_subscriptions.update_timestamp diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/checks.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/checks.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,27 +0,0 @@ --- raw SQL checks --- checking to see if there is new data since the last run --- if not, fail or we will have blank sync tables - -#fail -ASSERT( - SELECT - COUNT(1) - FROM - `moz-fx-data-shared-prod.braze_derived.subscriptions_v1`, - UNNEST(subscriptions) AS subscriptions - WHERE - subscriptions.update_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 HOUR) -) > 0 -AS - "No new records in the braze_external.changed_subscriptions_v1 table in the last 15 hours"; - --- macro checks - -#warn -{{ not_null(["external_id"]) }} - -#warn -{{ min_row_count(1) }} - -#warn -{{ is_unique(["external_id"]) }} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,37 +0,0 @@ -friendly_name: Braze Changed Subscriptions -description: |- - This table represents changes to user subscriptions since the - previous DAG run. It enables us to build the changed subscriptions - sync(s) - - See https://mozilla-hub.atlassian.net/browse/DENG-3182 -owners: -- cbeck@mozilla.com -labels: - schedule: daily - owner: cbeck - dag: bqetl_braze - owner1: cbeck -scheduling: - dag_name: bqetl_braze - date_partition_parameter: null -bigquery: - time_partitioning: null - range_partitioning: null - clustering: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:braze - - workgroup:braze/ingestion-mozilla-dev - - workgroup:braze/ingestion-firefox -- role: roles/bigquery.dataEditor - members: - - workgroup:braze/writers -references: - checks.sql: - - .. - - moz-fx-data-shared-prod.braze_derived.subscriptions_v1 - query.sql: - - moz-fx-data-shared-prod.braze_derived.subscriptions_v1 - - moz-fx-data-shared-prod.braze_external.changed_subscriptions_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/query.sql 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,42 +0,0 @@ - -- CTE to determine the maximum update timestamp from changed_subscriptions_v1 -WITH max_update AS ( - SELECT - MAX(subscriptions.update_timestamp) AS latest_subscription_updated_at - FROM - `moz-fx-data-shared-prod.braze_external.changed_subscriptions_v1` AS changed, - UNNEST(changed.subscriptions) AS subscriptions -) - -- Main query to select all records from subscriptions_v1 that have been updated since the last sync -SELECT - subscriptions.external_id, - -- Reconstruct the subscriptions array to include only entries with non-null timestamps greater than max_timestamp - ARRAY( - SELECT AS STRUCT - subscriptions_array.subscription_name AS subscription_name, - subscriptions_array.firefox_subscription_id AS firefox_subscription_id, - subscriptions_array.mozilla_subscription_id AS mozilla_subscription_id, - subscriptions_array.mozilla_dev_subscription_id AS mozilla_dev_subscription_id, - subscriptions_array.subscription_state AS subscription_state, - subscriptions_array.update_timestamp AS update_timestamp - FROM - UNNEST(subscriptions.subscriptions) AS subscriptions_array - WHERE - subscriptions_array.update_timestamp > max_update.latest_subscription_updated_at - AND subscriptions_array.update_timestamp IS NOT NULL - ) AS subscriptions -FROM - `moz-fx-data-shared-prod.braze_derived.subscriptions_v1` AS subscriptions, - max_update - -- Filter to include only those rows where the new subscriptions array is not empty -WHERE - ARRAY_LENGTH( - ARRAY( - SELECT - 1 - FROM - UNNEST(subscriptions.subscriptions) AS subscriptions_array - WHERE - subscriptions_array.update_timestamp > max_update.latest_subscription_updated_at - AND subscriptions_array.update_timestamp IS NOT NULL - ) - ) > 0; diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/schema.yaml 2024-05-31 20:02:57.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,26 +0,0 @@ -fields: -- mode: NULLABLE - name: external_id - type: STRING -- fields: - - mode: NULLABLE - name: subscription_name - type: STRING - - mode: NULLABLE - name: firefox_subscription_id - type: STRING - - mode: NULLABLE - name: mozilla_subscription_id - type: STRING - - mode: NULLABLE - name: mozilla_dev_subscription_id - type: STRING - - mode: NULLABLE - name: subscription_state - type: STRING - - mode: NULLABLE - name: update_timestamp - type: TIMESTAMP - mode: REPEATED - name: subscriptions - type: RECORD diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_users_sync_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_users_sync_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_users_sync_v1/metadata.yaml 2024-05-31 20:04:32.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_users_sync_v1/metadata.yaml 2024-05-31 20:15:34.000000000 +0000 @@ -8,7 +8,7 @@ owners: - cbeck@mozilla.com labels: - schedule: daily + incremental: true owner: cbeck dag: bqetl_braze owner1: cbeck diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-31 20:02:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-31 20:10:59.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: source + mode: NULLABLE +- name: source type: STRING -- mode: NULLABLE - name: event_type + mode: NULLABLE +- name: event_type type: STRING -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: subdivision1 + mode: NULLABLE +- name: subdivision1 type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: event_count + mode: NULLABLE +- name: event_count type: INTEGER -- mode: NULLABLE - name: user_count + mode: NULLABLE +- name: user_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-31 20:02:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-31 20:10:59.000000000 +0000 @@ -1,40 +1,40 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: impression_count + mode: NULLABLE +- name: impression_count type: INTEGER -- mode: NULLABLE - name: click_count + mode: NULLABLE +- name: click_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-31 20:02:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-31 20:12:19.000000000 +0000 @@ -26,6 +26,9 @@ - name: adjust_network type: STRING mode: NULLABLE +- name: install_source + type: STRING + mode: NULLABLE - name: retained_week_2 type: BOOLEAN mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-31 20:02:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-31 20:12:17.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-05-31 20:08:40.000000000 +0000 @@ -1642,6 +1642,26 @@ - name: value type: INTEGER mode: NULLABLE + - name: network_tls_early_data_bytes_written + type: RECORD + mode: NULLABLE + fields: + - name: count + type: INTEGER + mode: NULLABLE + - name: sum + type: INTEGER + mode: NULLABLE + - name: values + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE - name: labeled_counter type: RECORD mode: NULLABLE @@ -3000,6 +3020,46 @@ type: RECORD mode: REPEATED fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: network_tls_early_data_accepted + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: network_tls_early_data_negotiated + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: networking_http_redirect_to_scheme_subresource + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + - name: networking_http_redirect_to_scheme_top_level + type: RECORD + mode: REPEATED + fields: - name: key type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql 2024-05-31 20:08:39.000000000 +0000 @@ -175,7 +175,11 @@ metrics.labeled_counter.webrtcdtls_srtp_cipher, metrics.labeled_counter.media_playback_not_supported_video_per_mime_type, metrics.labeled_counter.power_energy_per_process_type, - metrics.labeled_counter.bounce_tracking_protection_purge_count + metrics.labeled_counter.bounce_tracking_protection_purge_count, + metrics.labeled_counter.network_tls_early_data_accepted, + metrics.labeled_counter.network_tls_early_data_negotiated, + metrics.labeled_counter.networking_http_redirect_to_scheme_subresource, + metrics.labeled_counter.networking_http_redirect_to_scheme_top_level ) AS `labeled_counter`, metrics.quantity, metrics.string, @@ -439,7 +443,8 @@ metrics.custom_distribution.networking_http_3_upload_throughput_100, metrics.custom_distribution.networking_http_3_upload_throughput_10_50, metrics.custom_distribution.networking_http_3_upload_throughput_50_100, - metrics.custom_distribution.pdfjs_editing_highlight_thickness + metrics.custom_distribution.pdfjs_editing_highlight_thickness, + metrics.custom_distribution.network_tls_early_data_bytes_written ) AS `custom_distribution`, STRUCT( metrics.labeled_counter.crash_metrics_crash_count, @@ -577,7 +582,11 @@ metrics.labeled_counter.webrtcdtls_srtp_cipher, metrics.labeled_counter.media_playback_not_supported_video_per_mime_type, metrics.labeled_counter.power_energy_per_process_type, - metrics.labeled_counter.bounce_tracking_protection_purge_count + metrics.labeled_counter.bounce_tracking_protection_purge_count, + metrics.labeled_counter.network_tls_early_data_accepted, + metrics.labeled_counter.network_tls_early_data_negotiated, + metrics.labeled_counter.networking_http_redirect_to_scheme_subresource, + metrics.labeled_counter.networking_http_redirect_to_scheme_top_level ) AS `labeled_counter`, metrics.quantity, STRUCT( @@ -1067,7 +1076,8 @@ metrics.custom_distribution.networking_http_3_upload_throughput_100, metrics.custom_distribution.networking_http_3_upload_throughput_10_50, metrics.custom_distribution.networking_http_3_upload_throughput_50_100, - metrics.custom_distribution.pdfjs_editing_highlight_thickness + metrics.custom_distribution.pdfjs_editing_highlight_thickness, + metrics.custom_distribution.network_tls_early_data_bytes_written ) AS `custom_distribution`, STRUCT( metrics.labeled_counter.crash_metrics_crash_count, @@ -1205,7 +1215,11 @@ metrics.labeled_counter.webrtcdtls_srtp_cipher, metrics.labeled_counter.media_playback_not_supported_video_per_mime_type, metrics.labeled_counter.power_energy_per_process_type, - metrics.labeled_counter.bounce_tracking_protection_purge_count + metrics.labeled_counter.bounce_tracking_protection_purge_count, + metrics.labeled_counter.network_tls_early_data_accepted, + metrics.labeled_counter.network_tls_early_data_negotiated, + metrics.labeled_counter.networking_http_redirect_to_scheme_subresource, + metrics.labeled_counter.networking_http_redirect_to_scheme_top_level ) AS `labeled_counter`, metrics.quantity, STRUCT( @@ -1609,7 +1623,11 @@ metrics.labeled_counter.webrtcdtls_srtp_cipher, metrics.labeled_counter.media_playback_not_supported_video_per_mime_type, metrics.labeled_counter.power_energy_per_process_type, - metrics.labeled_counter.bounce_tracking_protection_purge_count + metrics.labeled_counter.bounce_tracking_protection_purge_count, + metrics.labeled_counter.network_tls_early_data_accepted, + metrics.labeled_counter.network_tls_early_data_negotiated, + metrics.labeled_counter.networking_http_redirect_to_scheme_subresource, + metrics.labeled_counter.networking_http_redirect_to_scheme_top_level ) AS `labeled_counter`, metrics.quantity, metrics.string, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-05-31 20:07:25.000000000 +0000 @@ -7230,6 +7230,48 @@ - name: use_counter_page_js_subclassing_typedarray_type_3 type: INTEGER mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domattr_modified + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domcharacter_data_modified + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domnode_inserted + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domnode_inserted_into_document + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domnode_removed + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domnode_removed_from_document + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_doc_domsubtree_modified + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domattr_modified + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domcharacter_data_modified + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domnode_inserted + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domnode_inserted_into_document + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domnode_removed + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domnode_removed_from_document + type: INTEGER + mode: NULLABLE + - name: use_counter_deprecated_ops_page_domsubtree_modified + type: INTEGER + mode: NULLABLE - name: labeled_counter type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/crash/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/crash/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/crash/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/crash/schema.yaml 2024-05-31 20:04:58.000000000 +0000 @@ -310,6 +310,40 @@ description: 'If true, the crash occurred during process startup. ' + - name: crash_is_garbage_collecting + type: BOOLEAN + mode: NULLABLE + description: 'Indicates that the crash occurred while the garbage collector + was running. + + ' + - name: crash_windows_error_reporting + type: BOOLEAN + mode: NULLABLE + description: 'Indicates if the crash was intercepted by the Windows Error Reporting + runtime exception module. + + ' + - name: dll_blocklist_init_failed + type: BOOLEAN + mode: NULLABLE + description: 'Indicates whether initialization of the DLL blocklist failed. + + ' + - name: dll_blocklist_user32_loaded_before + type: BOOLEAN + mode: NULLABLE + description: 'Indicates whether `user32.dll` was loaded before the DLL blocklist + was initialized. + + ' + - name: environment_headless_mode + type: BOOLEAN + mode: NULLABLE + description: 'Whether the app was invoked in headless mode via `--headless` + or `--backgroundtask`. + + ' - name: datetime type: RECORD mode: NULLABLE @@ -342,6 +376,101 @@ for the purpose of experimentation enrollment. ' + - name: crash_app_build + type: STRING + mode: NULLABLE + description: 'The build id of the application. This may differ from `client_info` + because a main process crash may be reported later by a different (e.g. updated) + client. + + ' + - name: crash_app_channel + type: STRING + mode: NULLABLE + description: 'The release channel of the application. This may differ from `client_info` + because a main process crash may be reported later by a different (e.g. updated) + client. + + ' + - name: crash_app_display_version + type: STRING + mode: NULLABLE + description: 'The version of the application. This may differ from `client_info` + because a main process crash may be reported later by a different (e.g. updated) + client. + + ' + - name: crash_background_task_name + type: STRING + mode: NULLABLE + description: 'The name of the background task if launched as one. + + ' + - name: crash_font_name + type: STRING + mode: NULLABLE + description: 'The font family name that is being loaded when the crash occurred. + + ' + - name: crash_ipc_channel_error + type: STRING + mode: NULLABLE + description: 'The error reason for an ipc-based content crash. + + ' + - name: crash_main_thread_runnable_name + type: STRING + mode: NULLABLE + description: 'Name of the currently executing `nsIRunnable` on the main thread. + Nightly-only. + + ' + - name: crash_minidump_sha256_hash + type: STRING + mode: NULLABLE + description: 'The SHA256 hash of the associated minidump file, if any. + + ' + - name: crash_moz_crash_reason + type: STRING + mode: NULLABLE + description: 'Contains the string passed to `MOZ_CRASH()`. + + ' + - name: crash_profiler_child_shutdown_phase + type: STRING + mode: NULLABLE + description: 'The shutdown phase of the profiler. + + ' + - name: crash_remote_type + type: STRING + mode: NULLABLE + description: 'The type of the content process. See the full list of options + [here](https://firefox-source-docs.mozilla.org/toolkit/components/telemetry/data/crash-ping.html#remote-process-types). + + ' + - name: crash_shutdown_progress + type: STRING + mode: NULLABLE + description: 'The shutdown phase in which the crash occurred. + + ' + - name: crash_windows_file_dialog_error_code + type: STRING + mode: NULLABLE + description: 'The HRESULT returned from a Win32 system call leading to termination + of the file-dialog utility process. MozCrashReason is expected to provide + context for the value. + + ' + - name: windows_package_family_name + type: STRING + mode: NULLABLE + description: 'The Package Family Name of Firefox, if installed through an MSIX + package. + + ' - name: timespan type: RECORD mode: NULLABLE @@ -360,6 +489,143 @@ ping''s `UptimeTS` field. ' + - name: environment_uptime + type: RECORD + mode: NULLABLE + fields: + - name: time_unit + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'The start time of the application. + + ' + - name: object + type: RECORD + mode: NULLABLE + fields: + - name: crash_async_shutdown_timeout + type: JSON + mode: NULLABLE + description: 'Present when a shutdown blocker failed to respond within a reasonable + amount of time. + + ' + - name: crash_quota_manager_shutdown_timeout + type: JSON + mode: NULLABLE + description: 'Contains a list of shutdown steps and status of the quota manager + clients. + + ' + - name: crash_stack_traces + type: JSON + mode: NULLABLE + description: 'Stack trace and module information of the crashing process. + + ' + - name: quantity + type: RECORD + mode: NULLABLE + fields: + - name: crash_event_loop_nesting_level + type: INTEGER + mode: NULLABLE + description: 'Indicates the nesting level of the event loop. + + ' + - name: crash_gpu_process_launch + type: INTEGER + mode: NULLABLE + description: 'The number of times that the GPU process was launched. + + ' + - name: memory_available_commit + type: INTEGER + mode: NULLABLE + description: 'Available commit size. + + ' + - name: memory_available_physical + type: INTEGER + mode: NULLABLE + description: 'Available physical memory. + + ' + - name: memory_available_swap + type: INTEGER + mode: NULLABLE + description: 'Available swap memory. + + ' + - name: memory_available_virtual + type: INTEGER + mode: NULLABLE + description: 'Available virtual memory. + + ' + - name: memory_low_physical + type: INTEGER + mode: NULLABLE + description: 'The number of times the available memory tracker has detected + that free physical memory is running low. + + ' + - name: memory_oom_allocation_size + type: INTEGER + mode: NULLABLE + description: 'The size of the allocation that caused on OOM crash. + + ' + - name: memory_purgeable_physical + type: INTEGER + mode: NULLABLE + description: 'The amount of memory that can be deallocated by the OS in case + of memory pressure. + + ' + - name: memory_system_use_percentage + type: INTEGER + mode: NULLABLE + description: 'The percentage of memory in use. + + ' + - name: memory_texture + type: INTEGER + mode: NULLABLE + description: 'The amount of memory used in textures. + + ' + - name: memory_total_page_file + type: INTEGER + mode: NULLABLE + description: 'The total page file size. + + ' + - name: memory_total_physical + type: INTEGER + mode: NULLABLE + description: 'The total physical memory. + + ' + - name: memory_total_virtual + type: INTEGER + mode: NULLABLE + description: 'The total virtual memory. + + ' + - name: string_list + type: RECORD + mode: NULLABLE + fields: + - name: dll_blocklist_list + type: STRING + mode: REPEATED + - name: environment_experimental_features + type: STRING + mode: REPEATED - name: normalized_app_name type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml 2024-05-31 20:04:54.000000000 +0000 @@ -2542,6 +2542,62 @@ would have happened under the dry label. ' + - name: network_tls_early_data_accepted + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'TLS early data was used and it was accepted or rejected by the + remote host. + + ' + - name: network_tls_early_data_negotiated + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'Sending TLS early data was not possible, possible and used, or + possible but not used. + + ' + - name: networking_http_redirect_to_scheme_subresource + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'Count of the HTTP redirection that triggered by subresource, keyed + by the URL scheme redirected to + + ' + - name: networking_http_redirect_to_scheme_top_level + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'Count of the HTTP redirection that triggered by top-level document, + keyed by the URL scheme redirected to + + ' - name: memory_distribution type: RECORD mode: NULLABLE @@ -9887,6 +9943,32 @@ description: 'The thickness used to draw a free highlight. ' + - name: network_tls_early_data_bytes_written + type: RECORD + mode: NULLABLE + fields: + - name: count + type: INTEGER + mode: NULLABLE + description: This was accidentally sent in the past and is now deprecated. + See https://bugzilla.mozilla.org/show_bug.cgi?id=1799509#c5 + - name: sum + type: INTEGER + mode: NULLABLE + - name: values + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'Amount of bytes sent using TLS early data at the start of a TLS + connection for a given channel. + + ' - name: url2 type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml 2024-05-31 20:04:46.000000000 +0000 @@ -17789,6 +17789,104 @@ `use.counter.top_level_content_documents_destroyed` to calculate the rate. ' + - name: use_counter_deprecated_ops_doc_domattr_modified + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMAttrModified. Compare against `use.counter.content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_doc_domcharacter_data_modified + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMCharacterDataModified. Compare against + `use.counter.content_documents_destroyed` to calculate the rate. + + ' + - name: use_counter_deprecated_ops_doc_domnode_inserted + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMNodeInserted. Compare against `use.counter.content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_doc_domnode_inserted_into_document + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMNodeInsertedIntoDocument. Compare against + `use.counter.content_documents_destroyed` to calculate the rate. + + ' + - name: use_counter_deprecated_ops_doc_domnode_removed + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMNodeRemoved. Compare against `use.counter.content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_doc_domnode_removed_from_document + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMNodeRemovedFromDocument. Compare against + `use.counter.content_documents_destroyed` to calculate the rate. + + ' + - name: use_counter_deprecated_ops_doc_domsubtree_modified + type: INTEGER + mode: NULLABLE + description: 'Whether a document used DOMSubtreeModified. Compare against `use.counter.content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domattr_modified + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMAttrModified. Compare against `use.counter.top_level_content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domcharacter_data_modified + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMCharacterDataModified. Compare against + `use.counter.top_level_content_documents_destroyed` to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domnode_inserted + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMNodeInserted. Compare against `use.counter.top_level_content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domnode_inserted_into_document + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMNodeInsertedIntoDocument. Compare against + `use.counter.top_level_content_documents_destroyed` to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domnode_removed + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMNodeRemoved. Compare against `use.counter.top_level_content_documents_destroyed` + to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domnode_removed_from_document + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMNodeRemovedFromDocument. Compare against + `use.counter.top_level_content_documents_destroyed` to calculate the rate. + + ' + - name: use_counter_deprecated_ops_page_domsubtree_modified + type: INTEGER + mode: NULLABLE + description: 'Whether a page used DOMSubtreeModified. Compare against `use.counter.top_level_content_documents_destroyed` + to calculate the rate. + + ' - name: labeled_counter type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml 2024-05-31 20:04:48.000000000 +0000 @@ -515,6 +515,12 @@ description: 'Screen pixel depth property ' + - name: characteristics_screen_orientation + type: INTEGER + mode: NULLABLE + description: 'Orientation of the primary screen. + + ' - name: string type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks/crash/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks/crash/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks/crash/schema.yaml 2024-05-31 20:03:34.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks/crash/schema.yaml 2024-05-31 20:04:50.000000000 +0000 @@ -246,6 +246,40 @@ description: 'If true, the crash occurred during process startup. ' + - name: crash_is_garbage_collecting + type: BOOLEAN + mode: NULLABLE + description: 'Indicates that the crash occurred while the garbage collector + was running. + + ' + - name: crash_windows_error_reporting + type: BOOLEAN + mode: NULLABLE + description: 'Indicates if the crash was intercepted by the Windows Error Reporting + runtime exception module. + + ' + - name: dll_blocklist_init_failed + type: BOOLEAN + mode: NULLABLE + description: 'Indicates whether initialization of the DLL blocklist failed. + + ' + - name: dll_blocklist_user32_loaded_before + type: BOOLEAN + mode: NULLABLE + description: 'Indicates whether `user32.dll` was loaded before the DLL blocklist + was initialized. + + ' + - name: environment_headless_mode + type: BOOLEAN + mode: NULLABLE + description: 'Whether the app was invoked in headless mode via `--headless` + or `--backgroundtask`. + + ' - name: datetime type: RECORD mode: NULLABLE @@ -342,6 +376,101 @@ for the purpose of experimentation enrollment. ' + - name: crash_app_build + type: STRING + mode: NULLABLE + description: 'The build id of the application. This may differ from `client_info` + because a main process crash may be reported later by a different (e.g. updated) + client. + + ' + - name: crash_app_channel + type: STRING + mode: NULLABLE + description: 'The release channel of the application. This may differ from `client_info` + because a main process crash may be reported later by a different (e.g. updated) + client. + + ' + - name: crash_app_display_version + type: STRING + mode: NULLABLE + description: 'The version of the application. This may differ from `client_info` + because a main process crash may be reported later by a different (e.g. updated) + client. + + ' + - name: crash_background_task_name + type: STRING + mode: NULLABLE + description: 'The name of the background task if launched as one. + + ' + - name: crash_font_name + type: STRING + mode: NULLABLE + description: 'The font family name that is being loaded when the crash occurred. + + ' + - name: crash_ipc_channel_error + type: STRING + mode: NULLABLE + description: 'The error reason for an ipc-based content crash. + + ' + - name: crash_main_thread_runnable_name + type: STRING + mode: NULLABLE + description: 'Name of the currently executing `nsIRunnable` on the main thread. + Nightly-only. + + ' + - name: crash_minidump_sha256_hash + type: STRING + mode: NULLABLE + description: 'The SHA256 hash of the associated minidump file, if any. + + ' + - name: crash_moz_crash_reason + type: STRING + mode: NULLABLE + description: 'Contains the string passed to `MOZ_CRASH()`. + + ' + - name: crash_profiler_child_shutdown_phase + type: STRING + mode: NULLABLE + description: 'The shutdown phase of the profiler. + + ' + - name: crash_remote_type + type: STRING + mode: NULLABLE + description: 'The type of the content process. See the full list of options + [here](https://firefox-source-docs.mozilla.org/toolkit/components/telemetry/data/crash-ping.html#remote-process-types). + + ' + - name: crash_shutdown_progress + type: STRING + mode: NULLABLE + description: 'The shutdown phase in which the crash occurred. + + ' + - name: crash_windows_file_dialog_error_code + type: STRING + mode: NULLABLE + description: 'The HRESULT returned from a Win32 system call leading to termination + of the file-dialog utility process. MozCrashReason is expected to provide + context for the value. + + ' + - name: windows_package_family_name + type: STRING + mode: NULLABLE + description: 'The Package Family Name of Firefox, if installed through an MSIX + package. + + ' - name: timespan type: RECORD mode: NULLABLE @@ -360,6 +489,143 @@ ping''s `UptimeTS` field. ' + - name: environment_uptime + type: RECORD + mode: NULLABLE + fields: + - name: time_unit + type: STRING + mode: NULLABLE + - name: value + type: INTEGER + mode: NULLABLE + description: 'The start time of the application. + + ' + - name: object + type: RECORD + mode: NULLABLE + fields: + - name: crash_async_shutdown_timeout + type: JSON + mode: NULLABLE + description: 'Present when a shutdown blocker failed to respond within a reasonable + amount of time. + + ' + - name: crash_quota_manager_shutdown_timeout + type: JSON + mode: NULLABLE + description: 'Contains a list of shutdown steps and status of the quota manager + clients. + + ' + - name: crash_stack_traces + type: JSON + mode: NULLABLE + description: 'Stack trace and module information of the crashing process. + + ' + - name: quantity + type: RECORD + mode: NULLABLE + fields: + - name: crash_event_loop_nesting_level + type: INTEGER + mode: NULLABLE + description: 'Indicates the nesting level of the event loop. + + ' + - name: crash_gpu_process_launch + type: INTEGER + mode: NULLABLE + description: 'The number of times that the GPU process was launched. + + ' + - name: memory_available_commit + type: INTEGER + mode: NULLABLE + description: 'Available commit size. + + ' + - name: memory_available_physical + type: INTEGER + mode: NULLABLE + description: 'Available physical memory. + + ' + - name: memory_available_swap + type: INTEGER + mode: NULLABLE + description: 'Available swap memory. + + ' + - name: memory_available_virtual + type: INTEGER + mode: NULLABLE + description: 'Available virtual memory. + + ' + - name: memory_low_physical + type: INTEGER + mode: NULLABLE + description: 'The number of times the available memory tracker has detected + that free physical memory is running low. + + ' + - name: memory_oom_allocation_size + type: INTEGER + mode: NULLABLE + description: 'The size of the allocation that caused on OOM crash. + + ' + - name: memory_purgeable_physical + type: INTEGER + mode: NULLABLE + description: 'The amount of memory that can be deallocated by the OS in case + of memory pressure. + + ' + - name: memory_system_use_percentage + type: INTEGER + mode: NULLABLE + description: 'The percentage of memory in use. + + ' + - name: memory_texture + type: INTEGER + mode: NULLABLE + description: 'The amount of memory used in textures. + + ' + - name: memory_total_page_file + type: INTEGER + mode: NULLABLE + description: 'The total page file size. + + ' + - name: memory_total_physical + type: INTEGER + mode: NULLABLE + description: 'The total physical memory. + + ' + - name: memory_total_virtual + type: INTEGER + mode: NULLABLE + description: 'The total virtual memory. + + ' + - name: string_list + type: RECORD + mode: NULLABLE + fields: + - name: dll_blocklist_list + type: STRING + mode: REPEATED + - name: environment_experimental_features + type: STRING + mode: REPEATED - name: normalized_app_name type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 20:03:33.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 20:05:47.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.background_tasks_v1` ```

⚠️ Only part of the diff is displayed.

Link to full diff