dataops-ci-bot
commented
1 month ago Integration report for "removed backfill yaml"
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 22:00:59.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_braze.py 2024-05-31 22:12:49.000000000 +0000 @@ -175,6 +175,18 @@ 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", @@ -338,6 +350,19 @@ 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", @@ -377,6 +402,19 @@ 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", @@ -456,7 +494,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_derived__subscriptions__v1 + checks__fail_braze_external__changed_subscriptions__v1 ) braze_external__changed_newsletters_sync__v1.set_upstream( @@ -467,6 +505,10 @@ 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 ) @@ -509,6 +551,14 @@ 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 ) @@ -521,6 +571,14 @@ 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/generated-sql/sql/moz-fx-data-shared-prod/braze_external: changed_subscriptions_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1: backfill.yaml 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 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/checks.sql 2024-05-31 21:58:09.000000000 +0000 @@ -1,3 +1,20 @@ +-- 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 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -27,6 +27,7 @@ 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 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/checks.sql 2024-05-31 21:58:09.000000000 +0000 @@ -1,3 +1,22 @@ +-- 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 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -27,6 +27,7 @@ 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 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/checks.sql 2024-05-31 21:58:09.000000000 +0000 @@ -1,5 +1,3 @@ ---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 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/checks.sql 2024-05-31 21:58:09.000000000 +0000 @@ -1,3 +1,19 @@ +-- 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 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -28,6 +28,7 @@ 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 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/checks.sql 2024-05-31 21:58:09.000000000 +0000 @@ -1,4 +1,19 @@ --- macro checks +-- 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"; #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 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -27,6 +27,7 @@ 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 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -9,7 +9,7 @@ owners: - cbeck@mozilla.com labels: - incremental: true + schedule: daily owner: cbeck dag: bqetl_braze owner1: cbeck @@ -33,5 +33,4 @@ checks.sql: - .. query.sql: - - moz-fx-data-shared-prod.braze_derived.subscriptions_v1 - - moz-fx-data-shared-prod.braze_external.changed_firefox_subscriptions_sync_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_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 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/query.sql 2024-05-31 21:58:09.000000000 +0000 @@ -1,26 +1,4 @@ --- 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 +-- Construct the JSON payload in Braze required format SELECT CURRENT_TIMESTAMP() AS UPDATED_AT, subscriptions.external_id AS EXTERNAL_ID, @@ -33,19 +11,12 @@ ) ORDER BY subscriptions_array.update_timestamp DESC - ) AS subscription_groups, - max_subscriptions.update_timestamp + ) AS subscription_groups ) ) AS PAYLOAD FROM - `moz-fx-data-shared-prod.braze_derived.subscriptions_v1` AS subscriptions + `moz-fx-data-shared-prod.braze_external.changed_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, - max_subscriptions.update_timestamp + subscriptions.external_id; diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_newsletters_sync_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_newsletters_sync_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_newsletters_sync_v1/metadata.yaml 2024-05-31 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_newsletters_sync_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -8,7 +8,7 @@ owners: - cbeck@mozilla.com labels: - incremental: true + schedule: daily owner: cbeck dag: bqetl_braze owner1: cbeck diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_products_sync_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_products_sync_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_products_sync_v1/metadata.yaml 2024-05-31 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_products_sync_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -8,7 +8,7 @@ owners: - cbeck@mozilla.com labels: - incremental: true + schedule: daily owner: cbeck dag: bqetl_braze owner1: cbeck 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 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/checks.sql 2024-05-31 21:58:09.000000000 +0000 @@ -0,0 +1,27 @@ +-- 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 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -0,0 +1,37 @@ +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 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/query.sql 2024-05-31 21:58:09.000000000 +0000 @@ -0,0 +1,42 @@ + -- 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 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_subscriptions_v1/schema.yaml 2024-05-31 21:58:09.000000000 +0000 @@ -0,0 +1,26 @@ +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 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_users_sync_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -8,7 +8,7 @@ owners: - cbeck@mozilla.com labels: - incremental: true + schedule: daily owner: cbeck dag: bqetl_braze owner1: cbeck diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_waitlists_sync_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_waitlists_sync_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_waitlists_sync_v1/metadata.yaml 2024-05-31 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_waitlists_sync_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -8,7 +8,7 @@ owners: - cbeck@mozilla.com labels: - incremental: true + schedule: daily owner: cbeck dag: bqetl_braze owner1: cbeck diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/delete_users_sync_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/delete_users_sync_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_external/delete_users_sync_v1/metadata.yaml 2024-05-31 21:59:04.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/delete_users_sync_v1/metadata.yaml 2024-05-31 22:09:41.000000000 +0000 @@ -17,7 +17,6 @@ owners: - cbeck@mozilla.com labels: - incremental: true schedule: daily owner: cbeck dag: bqetl_braze 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 21:57:17.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-31 22:05:53.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 21:57:17.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-31 22:05:53.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 21:57:17.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-31 22:04:58.000000000 +0000 @@ -26,6 +26,9 @@ - name: adjust_network type: STRING mode: NULLABLE +- name: install_source + type: STRING + mode: NULLABLE - name: retained_week_2 type: BOOLEAN mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-31 21:57:17.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-31 22:04:58.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_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 21:57:54.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 21:59:55.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.background_tasks_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.events_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.firefox_desktop_background_tasks_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.background_tasks_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:55.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.prototype_no_code_events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.prototype_no_code_events_v1` UNION ALL SELECT submission_timestamp, @@ -80,7 +80,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.newtab_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/backfill.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived/retention_v1/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,7 +0,0 @@ -2024-05-31: - start_date: 2021-01-01 - end_date: 2024-05-31 - reason: The table is created, this is to populate it with data. - watchers: - - kik@mozilla.com - status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml 2024-05-31 21:57:17.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/internet_outages/global_outages_v1/schema.yaml 2024-05-31 22:06:01.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: country +- name: country type: STRING -- mode: NULLABLE - name: city + mode: NULLABLE +- name: city type: STRING -- mode: NULLABLE - name: datetime + mode: NULLABLE +- name: datetime type: TIMESTAMP -- mode: NULLABLE - name: proportion_undefined + mode: NULLABLE +- name: proportion_undefined type: FLOAT -- mode: NULLABLE - name: proportion_timeout + mode: NULLABLE +- name: proportion_timeout type: FLOAT -- mode: NULLABLE - name: proportion_abort + mode: NULLABLE +- name: proportion_abort type: FLOAT -- mode: NULLABLE - name: proportion_unreachable + mode: NULLABLE +- name: proportion_unreachable type: FLOAT -- mode: NULLABLE - name: proportion_terminated + mode: NULLABLE +- name: proportion_terminated type: FLOAT -- mode: NULLABLE - name: proportion_channel_open + mode: NULLABLE +- name: proportion_channel_open type: FLOAT -- mode: NULLABLE - name: avg_dns_success_time + mode: NULLABLE +- name: avg_dns_success_time type: FLOAT -- mode: NULLABLE - name: missing_dns_success + mode: NULLABLE +- name: missing_dns_success type: FLOAT -- mode: NULLABLE - name: avg_dns_failure_time + mode: NULLABLE +- name: avg_dns_failure_time type: FLOAT -- mode: NULLABLE - name: missing_dns_failure + mode: NULLABLE +- name: missing_dns_failure type: FLOAT -- mode: NULLABLE - name: count_dns_failure + mode: NULLABLE +- name: count_dns_failure type: FLOAT -- mode: NULLABLE - name: ssl_error_prop + mode: NULLABLE +- name: ssl_error_prop type: FLOAT -- mode: NULLABLE - name: avg_tls_handshake_time + mode: NULLABLE +- name: avg_tls_handshake_time type: FLOAT + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/monitoring_derived/event_monitoring_aggregates_v1/query.sql 2024-05-31 22:01:28.000000000 +0000 @@ -45,7 +45,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.prototype_no_code_events_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` UNION ALL SELECT submission_timestamp, @@ -65,7 +65,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.prototype_no_code_events_v1` UNION ALL SELECT submission_timestamp, @@ -75,7 +75,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_stable.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -572,7 +572,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -592,7 +592,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_stable.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -663,7 +663,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -683,7 +683,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_stable.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -754,7 +754,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -774,7 +774,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_stable.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1565,7 +1565,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_stable.vpnsession_v1` + `moz-fx-data-shared-prod.mozillavpn_stable.main_v1` UNION ALL SELECT submission_timestamp, @@ -1575,7 +1575,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_stable.main_v1` + `moz-fx-data-shared-prod.mozillavpn_stable.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1656,7 +1656,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.main_v1` UNION ALL SELECT submission_timestamp, @@ -1666,7 +1666,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.main_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_stable.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1747,7 +1747,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.main_v1` UNION ALL SELECT submission_timestamp, @@ -1757,7 +1757,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_stable.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -1838,7 +1838,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.main_v1` UNION ALL SELECT submission_timestamp, @@ -1848,7 +1848,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_stable.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -2162,7 +2162,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.background_tasks_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.events_v1` UNION ALL SELECT submission_timestamp, @@ -2172,7 +2172,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_stable.background_tasks_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:54.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozillavpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:54.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_live.vpnsession_v1` + `moz-fx-data-shared-prod.mozillavpn_live.main_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.mozillavpn_live.main_v1` + `moz-fx-data-shared-prod.mozillavpn_live.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_fenix/geckoview_version/schema.yaml 2024-05-31 22:06:31.000000000 +0000 @@ -1,7 +1,10 @@ fields: -- type: DATETIME - name: build_hour -- type: INTEGER - name: geckoview_major_version -- type: INTEGER - name: n_pings +- name: build_hour + type: DATETIME + mode: NULLABLE +- name: geckoview_major_version + type: INTEGER + mode: NULLABLE +- name: n_pings + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_firefox_vpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:56.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.main_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.main_v1` + `moz-fx-data-shared-prod.org_mozilla_firefox_vpn_live.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_fennec_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:57.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_fennec_live.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxbeta_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:57.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxbeta_live.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefox_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:57.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.first_session_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.metrics_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.metrics_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefox_live.first_session_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:57.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.main_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_live.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:57:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_ios_firefoxvpn_network_extension_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-31 21:59:57.000000000 +0000 @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.vpnsession_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.main_v1` UNION ALL SELECT submission_timestamp, @@ -70,7 +70,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.main_v1` + `moz-fx-data-shared-prod.org_mozilla_ios_firefoxvpn_network_extension_live.vpnsession_v1` ) CROSS JOIN UNNEST(events) AS event, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-31 21:59:54.000000000 +0000 @@ -1,6 +1,4 @@ --- Query generated by ./bqetl generate search --- This file doesn't get overwritten by the generator. The generator output needs --- to be written to this file manually. +-- Query generated by bigquery-etl/search/mobile_search_clients_daily.py -- -- Older versions separate source and engine with an underscore instead of period -- Return array of form [source, engine] if key is valid, empty array otherwise @@ -498,7 +496,7 @@ metrics.counter.browser_total_uri_count, client_info.locale, FROM - org_mozilla_ios_klar.metrics AS org_mozilla_klar_metrics + org_mozilla_ios_klar.metrics AS org_mozilla_ios_klar_metrics ), fenix_baseline AS ( SELECT @@ -874,7 +872,6 @@ ANY_VALUE(sample_id) AS sample_id, udf.map_mode_last(ARRAY_CONCAT_AGG(experiments)) AS experiments, SUM(total_uri_count) AS total_uri_count, - CAST(NULL AS STRING) AS normalized_engine FROM combined_search_clients WHERE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/ca_postal_districts_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,9 +1,7 @@ fields: - name: postal_district_code type: STRING - mode: REQUIRED - description: One-character Canadian postal district code. + mode: NULLABLE - name: province_code type: STRING mode: NULLABLE - description: Two-character Canadian province/territory code (if any). diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_codes_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,47 +1,28 @@ fields: - name: name - description: Official country name per ISO 3166 type: STRING - mode: REQUIRED + mode: NULLABLE - name: code - description: ISO 3166 alpha-2 country code type: STRING - mode: REQUIRED + mode: NULLABLE - name: code_3 - description: ISO 3166 alpha-3 country code type: STRING - mode: REQUIRED + mode: NULLABLE - name: region_name - description: Region name. These are based on the UN Statistics Division standard - country or area codes for statistical use (M49), but with the "Americas" region - split into "North America" and "South America". type: STRING - mode: REQUIRED + mode: NULLABLE - name: subregion_name - description: Sub-region name. These are based on UN Statistics Division standard - country or area codes for statistical use (M49), but with the "Latin America and the - Caribbean" and "Sub-Saharan Africa" sub-regions split into more specific - sub-regions. type: STRING - mode: REQUIRED + mode: NULLABLE - name: pocket_available_on_newtab - description: Whether Pocket is available on the newtab page in this country. Note - that Pocket might only be available in certain locales/languages within a country. - type: BOOL - mode: REQUIRED + type: BOOLEAN + mode: NULLABLE - name: mozilla_vpn_available - description: Whether Mozilla VPN is available in this country. - type: BOOL - mode: REQUIRED + type: BOOLEAN + mode: NULLABLE - name: sponsored_tiles_available_on_newtab - description: Whether sponsored tiles are available on the newtab page in this country. - Note that Pocket might only be available in certain locales/languages within a - country. - type: BOOL - mode: REQUIRED + type: BOOLEAN + mode: NULLABLE - name: ads_value_tier - description: Lowercase label detailing the monetary value tier that Mozilla Ads - assign to that region based on market size and our existing products, e.g., tier - 1, tier 2, etc. type: STRING - mode: REQUIRED + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/country_names_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,10 +1,7 @@ fields: - name: name - description: An alias for a country's name (including misspellings and alternate - encodings). type: STRING - mode: REQUIRED + mode: NULLABLE - name: code - description: ISO 3166 alpha-2 country code type: STRING - mode: REQUIRED + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/data_incidents_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,22 +1,22 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: incident + mode: NULLABLE +- name: incident type: STRING -- mode: NULLABLE - name: description + mode: NULLABLE +- name: description type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING -- mode: NULLABLE - name: product + mode: NULLABLE +- name: product type: STRING -- mode: NULLABLE - name: version + mode: NULLABLE +- name: version type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/iana_tls_cipher_suites/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,27 +1,16 @@ fields: -- mode: NULLABLE - description: Hex value assigned to the TLS cipher, in format like "0x00,0x84"; note - some values are ranges or contain wildcards - name: value +- name: value type: STRING -- mode: NULLABLE - description: Human-readable name of the TLS cipher - name: description + mode: NULLABLE +- name: description type: STRING -- mode: NULLABLE - description: Any TLS cipher suite that is specified for use with DTLS MUST define - limits on the use of the associated AEAD function that preserves margins for both - confidentiality and integrity, as specified in [RFC-ietf-tls-dtls13-43] - name: dtls_ok + mode: NULLABLE +- name: dtls_ok type: BOOLEAN -- mode: NULLABLE - description: Whether the TLS cipher is recommended by the IETF. If an item is not - marked as "recommended", it does not necessarily mean that it is flawed; rather, - it indicates that the item either has not been through the IETF consensus process, - has limited applicability, or is intended only for specific use cases - name: recommended + mode: NULLABLE +- name: recommended type: BOOLEAN -- mode: NULLABLE - description: RFCs or associated reference material for the TLS cipher - name: reference + mode: NULLABLE +- name: reference type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/language_codes_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,17 +1,13 @@ fields: - name: code_3 - description: ISO 639 alpha-3 language code. type: STRING - mode: REQUIRED + mode: NULLABLE - name: code_2 - description: ISO 639 alpha-2 language code (if any). type: STRING mode: NULLABLE - name: name - description: Language name. type: STRING - mode: REQUIRED + mode: NULLABLE - name: other_names - description: Other names for the language (if any). type: STRING mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_distinct_docids_notes_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,19 +1,19 @@ fields: -- mode: NULLABLE - name: start_date +- name: start_date type: DATE -- mode: NULLABLE - name: end_date + mode: NULLABLE +- name: end_date type: DATE -- mode: NULLABLE - name: document_namespace + mode: NULLABLE +- name: document_namespace type: STRING -- mode: NULLABLE - name: document_type + mode: NULLABLE +- name: document_type type: STRING -- mode: NULLABLE - name: notes + mode: NULLABLE +- name: notes type: STRING -- mode: NULLABLE - name: bug + mode: NULLABLE +- name: bug type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml 2024-05-31 21:57:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/static/monitoring_missing_columns_notes_v1/schema.yaml 2024-05-31 22:07:11.000000000 +0000 @@ -1,25 +1,25 @@ fields: -- mode: NULLABLE - name: start_date +- nam ```⚠️ Only part of the diff is displayed.
Backfill yaml files with same table name and entry dates are causing issues since the resulting backfill staging destination table name will be the same. Removing one of them added from PR #5699
Checklist for reviewer:
<username>:<branch>of the fork as parameter. The parameter will also show up in the logs of themanual-trigger-required-for-forkCI task together with more detailed instructions.For modifications to schemas in restricted namespaces (see
CODEOWNERS):┆Issue is synchronized with this Jira Task