adding search with ads organic to mobile_search_aggregates and search_revenue_levers_daily table

pissac17 commented 1 month ago

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

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_analytics_aggregations.py /tmp/workspace/generated-sql/dags/bqetl_analytics_aggregations.py --- /tmp/workspace/main-generated-sql/dags/bqetl_analytics_aggregations.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_aggregations.py 2024-06-03 16:17:17.000000000 +0000 @@ -190,6 +190,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(seconds=8100), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_firefox_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( task_id="wait_for_firefox_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", @@ -848,6 +860,10 @@ wait_for_fenix_derived__clients_last_seen_joined__v1 ) + fenix_active_users_aggregates.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + firefox_desktop_active_users_aggregates.set_upstream( wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 ) @@ -868,11 +884,19 @@ wait_for_firefox_ios_derived__clients_last_seen_joined__v1 ) + firefox_ios_active_users_aggregates.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + focus_android_active_users_aggregates.set_upstream( wait_for_focus_android_derived__clients_last_seen_joined__v1 ) focus_android_active_users_aggregates.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + + focus_android_active_users_aggregates.set_upstream( wait_for_telemetry_derived__core_clients_last_seen__v1 ) @@ -892,6 +916,10 @@ wait_for_focus_ios_derived__clients_last_seen_joined__v1 ) + focus_ios_active_users_aggregates.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + klar_ios_active_users_aggregates.set_upstream( wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) @@ -908,6 +936,10 @@ wait_for_klar_ios_derived__clients_last_seen_joined__v1 ) + klar_ios_active_users_aggregates.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + telemetry_derived__cohort_daily_statistics__v1.set_upstream( wait_for_checks__fail_telemetry_derived__unified_metrics__v1 ) 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-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_braze.py 2024-06-03 16:17:18.000000000 +0000 @@ -55,6 +55,28 @@ tags=tags, ) as dag: + wait_for_subscription_platform_derived__logical_subscriptions_history__v1 = ExternalTaskSensor( + task_id="wait_for_subscription_platform_derived__logical_subscriptions_history__v1", + external_dag_id="bqetl_subplat", + external_task_id="subscription_platform_derived__logical_subscriptions_history__v1", + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + + wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1", + external_dag_id="bqetl_marketing_suppression_list", + external_task_id="checks__fail_marketing_suppression_list_derived__main_suppression_list__v1", + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + braze_derived__newsletters__v1 = bigquery_etl_query( task_id="braze_derived__newsletters__v1", destination_table="newsletters_v1", @@ -175,6 +197,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 +372,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 +424,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", @@ -431,6 +491,10 @@ braze_derived__newsletters__v1.set_upstream(checks__fail_braze_derived__users__v1) + braze_derived__products__v1.set_upstream( + wait_for_subscription_platform_derived__logical_subscriptions_history__v1 + ) + braze_derived__subscriptions__v1.set_upstream( checks__fail_braze_derived__subscriptions_map__v1 ) @@ -453,10 +517,14 @@ checks__fail_braze_derived__waitlists__v1 ) + braze_derived__users__v1.set_upstream( + wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1 + ) + 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 +535,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 +581,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 +601,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 ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_desktop_mobile_search_monthly.py /tmp/workspace/generated-sql/dags/bqetl_desktop_mobile_search_monthly.py --- /tmp/workspace/main-generated-sql/dags/bqetl_desktop_mobile_search_monthly.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_desktop_mobile_search_monthly.py 2024-06-03 16:17:16.000000000 +0000 @@ -50,6 +50,18 @@ tags=tags, ) as dag: + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(days=2, seconds=10800), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_search_derived__search_clients_daily__v8 = ExternalTaskSensor( task_id="wait_for_search_derived__search_clients_daily__v8", external_dag_id="bqetl_search", @@ -75,5 +87,9 @@ ) search_derived__desktop_mobile_search_clients_monthly__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + + search_derived__desktop_mobile_search_clients_monthly__v1.set_upstream( wait_for_search_derived__search_clients_daily__v8 ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_firefox_ios.py /tmp/workspace/generated-sql/dags/bqetl_firefox_ios.py --- /tmp/workspace/main-generated-sql/dags/bqetl_firefox_ios.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_firefox_ios.py 2024-06-03 16:17:16.000000000 +0000 @@ -86,6 +86,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(seconds=7200), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_org_mozilla_ios_fennec_derived__baseline_clients_daily__v1 = ExternalTaskSensor( task_id="wait_for_org_mozilla_ios_fennec_derived__baseline_clients_daily__v1", external_dag_id="bqetl_glean_usage", @@ -693,6 +705,10 @@ wait_for_copy_deduplicate_all ) + firefox_ios_derived__attributable_clients__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + firefox_ios_derived__baseline_clients_yearly__v1.set_upstream( wait_for_org_mozilla_ios_fennec_derived__baseline_clients_daily__v1 ) @@ -725,6 +741,10 @@ wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 ) + firefox_ios_derived__clients_activation__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + firefox_ios_derived__firefox_ios_clients__v1.set_upstream( wait_for_copy_deduplicate_all ) @@ -796,3 +816,7 @@ firefox_ios_derived__new_profile_activation__v2.set_upstream( wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 ) + + firefox_ios_derived__new_profile_activation__v2.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_fxa_events.py /tmp/workspace/generated-sql/dags/bqetl_fxa_events.py --- /tmp/workspace/main-generated-sql/dags/bqetl_fxa_events.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_fxa_events.py 2024-06-03 16:17:14.000000000 +0000 @@ -142,13 +142,6 @@ ) ExternalTaskMarker( - task_id="bqetl_generated_funnels__wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", - external_dag_id="bqetl_generated_funnels", - external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=73800)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_event_rollup__wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", external_dag_id="bqetl_event_rollup", external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", @@ -183,13 +176,6 @@ ) ExternalTaskMarker( - task_id="bqetl_generated_funnels__wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", - external_dag_id="bqetl_generated_funnels", - external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=73800)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_event_rollup__wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", external_dag_id="bqetl_event_rollup", external_task_id="wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", @@ -235,13 +221,6 @@ ) ExternalTaskMarker( - task_id="bqetl_generated_funnels__wait_for_firefox_accounts_derived__fxa_stdout_events__v1", - external_dag_id="bqetl_generated_funnels", - external_task_id="wait_for_firefox_accounts_derived__fxa_stdout_events__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=73800)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_event_rollup__wait_for_firefox_accounts_derived__fxa_stdout_events__v1", external_dag_id="bqetl_event_rollup", external_task_id="wait_for_firefox_accounts_derived__fxa_stdout_events__v1", diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py --- /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-06-03 16:17:17.000000000 +0000 @@ -89,42 +89,6 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1 = ExternalTaskSensor( - task_id="wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1", - external_dag_id="bqetl_fxa_events", - external_task_id="firefox_accounts_derived__fxa_gcp_stderr_events__v1", - execution_delta=datetime.timedelta(seconds=12600), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1 = ExternalTaskSensor( - task_id="wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1", - external_dag_id="bqetl_fxa_events", - external_task_id="firefox_accounts_derived__fxa_gcp_stdout_events__v1", - execution_delta=datetime.timedelta(seconds=12600), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_firefox_accounts_derived__fxa_stdout_events__v1 = ExternalTaskSensor( - task_id="wait_for_firefox_accounts_derived__fxa_stdout_events__v1", - external_dag_id="bqetl_fxa_events", - external_task_id="firefox_accounts_derived__fxa_stdout_events__v1", - execution_delta=datetime.timedelta(seconds=12600), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - accounts_frontend_derived__email_first_reg_login_funnels__v1 = bigquery_etl_query( task_id="accounts_frontend_derived__email_first_reg_login_funnels__v1", destination_table="email_first_reg_login_funnels_v1", @@ -232,23 +196,6 @@ depends_on_past=False, ) - firefox_accounts_derived__registration_funnels_legacy_events__v1 = ( - bigquery_etl_query( - task_id="firefox_accounts_derived__registration_funnels_legacy_events__v1", - destination_table="registration_funnels_legacy_events_v1", - dataset_id="firefox_accounts_derived", - project_id="moz-fx-data-shared-prod", - owner="ksiegler@mozilla.org", - email=[ - "ascholtz@mozilla.com", - "ksiegler@mozilla.org", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - ) - monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1 = bigquery_etl_query( task_id="monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1", destination_table="monitor_dashboard_user_journey_funnels_v1", @@ -298,18 +245,6 @@ wait_for_fenix_derived__funnel_retention_clients_week_4__v1 ) - firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( - wait_for_firefox_accounts_derived__fxa_gcp_stderr_events__v1 - ) - - firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( - wait_for_firefox_accounts_derived__fxa_gcp_stdout_events__v1 - ) - - firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( - wait_for_firefox_accounts_derived__fxa_stdout_events__v1 - ) - monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1.set_upstream( wait_for_copy_deduplicate_all ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_marketing_suppression_list.py /tmp/workspace/generated-sql/dags/bqetl_marketing_suppression_list.py --- /tmp/workspace/main-generated-sql/dags/bqetl_marketing_suppression_list.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_marketing_suppression_list.py 2024-06-03 16:17:19.000000000 +0000 @@ -118,6 +118,19 @@ retries=0, ) + with TaskGroup( + "checks__fail_marketing_suppression_list_derived__main_suppression_list__v1_external", + ) as checks__fail_marketing_suppression_list_derived__main_suppression_list__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1", + ) + + checks__fail_marketing_suppression_list_derived__main_suppression_list__v1_external.set_upstream( + checks__fail_marketing_suppression_list_derived__main_suppression_list__v1 + ) + checks__warn_marketing_suppression_list_derived__main_suppression_list__v1 = bigquery_dq_check( task_id="checks__warn_marketing_suppression_list_derived__main_suppression_list__v1", source_table="main_suppression_list_v1", diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_mobile_activation.py /tmp/workspace/generated-sql/dags/bqetl_mobile_activation.py --- /tmp/workspace/main-generated-sql/dags/bqetl_mobile_activation.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_mobile_activation.py 2024-06-03 16:17:17.000000000 +0000 @@ -182,6 +182,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(days=-1, seconds=79200), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( task_id="wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", @@ -341,6 +353,10 @@ wait_for_org_mozilla_firefox_derived__baseline_clients_daily__v1 ) + fenix_derived__new_profile_activation__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + firefox_ios_derived__new_profile_activation__v1.set_upstream( wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 ) @@ -364,3 +380,7 @@ firefox_ios_derived__new_profile_activation__v1.set_upstream( wait_for_org_mozilla_ios_firefoxbeta_derived__baseline_clients_daily__v1 ) + + firefox_ios_derived__new_profile_activation__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_mobile_search.py /tmp/workspace/generated-sql/dags/bqetl_mobile_search.py --- /tmp/workspace/main-generated-sql/dags/bqetl_mobile_search.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_mobile_search.py 2024-06-03 16:17:14.000000000 +0000 @@ -52,6 +52,18 @@ tags=tags, ) as dag: + wait_for_copy_deduplicate_all = ExternalTaskSensor( + task_id="wait_for_copy_deduplicate_all", + external_dag_id="copy_deduplicate", + external_task_id="copy_deduplicate_all", + execution_delta=datetime.timedelta(seconds=3600), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + search_derived__mobile_search_aggregates__v1 = bigquery_etl_query( task_id="search_derived__mobile_search_aggregates__v1", destination_table="mobile_search_aggregates_v1", @@ -68,6 +80,90 @@ depends_on_past=False, ) + search_derived__mobile_search_clients_daily__v1 = bigquery_etl_query( + task_id="search_derived__mobile_search_clients_daily__v1", + destination_table="mobile_search_clients_daily_v1", + dataset_id="search_derived", + project_id="moz-fx-data-shared-prod", + owner="akomar@mozilla.com", + email=[ + "akomar@mozilla.com", + "anicholson@mozilla.com", + "cmorales@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + + with TaskGroup( + "search_derived__mobile_search_clients_daily__v1_external", + ) as search_derived__mobile_search_clients_daily__v1_external: + ExternalTaskMarker( + task_id="bqetl_analytics_aggregations__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_analytics_aggregations", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=78300)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_kpis_shredder__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_kpis_shredder", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + ) + + ExternalTaskMarker( + task_id="bqetl_org_mozilla_firefox_derived__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_org_mozilla_firefox_derived", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + ) + + ExternalTaskMarker( + task_id="bqetl_mobile_activation__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_activation", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(seconds=7200)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_firefox_ios__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_firefox_ios", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=79200)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_review_checker__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_review_checker", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(seconds=7200)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_desktop_mobile_search_monthly__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_desktop_mobile_search_monthly", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-3, seconds=75600)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_search_dashboard__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_search_dashboard", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=77400)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_unified__wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_unified", + external_task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + search_derived__mobile_search_clients_daily__v1_external.set_upstream( + search_derived__mobile_search_clients_daily__v1 + ) + search_derived__mobile_search_clients_last_seen__v1 = bigquery_etl_query( task_id="search_derived__mobile_search_clients_last_seen__v1", destination_table="mobile_search_clients_last_seen_v1", @@ -83,3 +179,15 @@ date_partition_parameter="submission_date", depends_on_past=True, ) + + search_derived__mobile_search_aggregates__v1.set_upstream( + search_derived__mobile_search_clients_daily__v1 + ) + + search_derived__mobile_search_clients_daily__v1.set_upstream( + wait_for_copy_deduplicate_all + ) + + search_derived__mobile_search_clients_last_seen__v1.set_upstream( + search_derived__mobile_search_clients_daily__v1 + ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_org_mozilla_firefox_derived.py /tmp/workspace/generated-sql/dags/bqetl_org_mozilla_firefox_derived.py --- /tmp/workspace/main-generated-sql/dags/bqetl_org_mozilla_firefox_derived.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_org_mozilla_firefox_derived.py 2024-06-03 16:17:15.000000000 +0000 @@ -139,6 +139,17 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_checks__fail_firefox_ios_derived__baseline_clients_yearly__v1 = ExternalTaskSensor( task_id="wait_for_checks__fail_firefox_ios_derived__baseline_clients_yearly__v1", external_dag_id="bqetl_firefox_ios", @@ -520,12 +531,20 @@ wait_for_org_mozilla_firefox_derived__baseline_clients_daily__v1 ) + fenix_derived__attributable_clients__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + fenix_derived__attributable_clients__v2.set_upstream(wait_for_copy_deduplicate_all) fenix_derived__attributable_clients__v2.set_upstream( wait_for_fenix_derived__new_profile_activation__v1 ) + fenix_derived__attributable_clients__v2.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + fenix_derived__client_adclicks_history__v1.set_upstream( fenix_derived__attributable_clients__v2 ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_review_checker.py /tmp/workspace/generated-sql/dags/bqetl_review_checker.py --- /tmp/workspace/main-generated-sql/dags/bqetl_review_checker.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_review_checker.py 2024-06-03 16:17:17.000000000 +0000 @@ -112,6 +112,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(days=-1, seconds=79200), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + firefox_desktop_review_checker_clients__v1 = bigquery_etl_query( task_id="firefox_desktop_review_checker_clients__v1", destination_table="review_checker_clients_v1", @@ -249,6 +261,10 @@ wait_for_copy_deduplicate_all ) + org_mozilla_fenix_review_checker_clients__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + org_mozilla_fenix_review_checker_events__v1.set_upstream( wait_for_copy_deduplicate_all ) @@ -257,6 +273,10 @@ wait_for_copy_deduplicate_all ) + org_mozilla_ios_firefox_review_checker_clients__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + org_mozilla_ios_firefox_review_checker_events__v1.set_upstream( wait_for_copy_deduplicate_all ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_search_dashboard.py /tmp/workspace/generated-sql/dags/bqetl_search_dashboard.py --- /tmp/workspace/main-generated-sql/dags/bqetl_search_dashboard.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_search_dashboard.py 2024-06-03 16:17:15.000000000 +0000 @@ -71,6 +71,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(seconds=9000), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_checks__fail_fenix_derived__active_users_aggregates__v3 = ( ExternalTaskSensor( task_id="wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", @@ -229,6 +241,10 @@ wait_for_search_derived__search_aggregates__v8 ) + search_derived__mobile_search_aggregates_for_searchreport__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + search_derived__search_revenue_levers_daily__v1.set_upstream( wait_for_checks__fail_fenix_derived__active_users_aggregates__v3 ) @@ -254,5 +270,9 @@ ) search_derived__search_revenue_levers_daily__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + + search_derived__search_revenue_levers_daily__v1.set_upstream( wait_for_search_derived__search_clients_daily__v8 ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_subplat.py /tmp/workspace/generated-sql/dags/bqetl_subplat.py --- /tmp/workspace/main-generated-sql/dags/bqetl_subplat.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_subplat.py 2024-06-03 16:17:15.000000000 +0000 @@ -1036,6 +1036,19 @@ ) ) + with TaskGroup( + "subscription_platform_derived__logical_subscriptions_history__v1_external", + ) as subscription_platform_derived__logical_subscriptions_history__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_subscription_platform_derived__logical_subscriptions_history__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_subscription_platform_derived__logical_subscriptions_history__v1", + ) + + subscription_platform_derived__logical_subscriptions_history__v1_external.set_upstream( + subscription_platform_derived__logical_subscriptions_history__v1 + ) + subscription_platform_derived__monthly_active_logical_subscriptions__v1 = bigquery_etl_query( task_id="subscription_platform_derived__monthly_active_logical_subscriptions__v1", destination_table="monthly_active_logical_subscriptions_v1", diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_unified.py /tmp/workspace/generated-sql/dags/bqetl_unified.py --- /tmp/workspace/main-generated-sql/dags/bqetl_unified.py 2024-06-03 16:04:45.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_unified.py 2024-06-03 16:17:16.000000000 +0000 @@ -128,6 +128,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_search_derived__mobile_search_clients_daily__v1 = ExternalTaskSensor( + task_id="wait_for_search_derived__mobile_search_clients_daily__v1", + external_dag_id="bqetl_mobile_search", + external_task_id="search_derived__mobile_search_clients_daily__v1", + execution_delta=datetime.timedelta(seconds=3600), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_telemetry_derived__core_clients_last_seen__v1 = ExternalTaskSensor( task_id="wait_for_telemetry_derived__core_clients_last_seen__v1", external_dag_id="bqetl_core", @@ -329,5 +341,9 @@ ) telemetry_derived__unified_metrics__v1.set_upstream( + wait_for_search_derived__mobile_search_clients_daily__v1 + ) + + telemetry_derived__unified_metrics__v1.set_upstream( wait_for_telemetry_derived__core_clients_last_seen__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/fenix_derived/retention_v1: backfill.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_accounts_derived: registration_funnels_legacy_events_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search/mobile_search_clients_engines_sources_daily: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/desktop_engagement_clients_v1: backfill.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/desktop_engagement_v1: backfill.yaml 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-06-03 16:01:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-06-03 16:03:54.000000000 +0000 @@ -50,7 +50,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.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.interaction_v1` + `moz-fx-data-shared-prod.bedrock_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.bedrock_live.events_v1` + `moz-fx-data-shared-prod.bedrock_live.non_interaction_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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/checks.sql 2024-06-03 16:02:07.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/newsletters_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/checks.sql 2024-06-03 16:02:07.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/products_v1/metadata.yaml 2024-06-03 16:14:18.000000000 +0000 @@ -12,7 +12,10 @@ scheduling: dag_name: bqetl_braze date_partition_parameter: null - referenced_tables: [] + referenced_tables: + - - moz-fx-data-shared-prod + - subscription_platform_derived + - logical_subscriptions_history_v1 bigquery: time_partitioning: null range_partitioning: null @@ -27,6 +30,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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/checks.sql 2024-06-03 16:02:07.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/subscriptions_map_v1/script.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/script.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/script.sql 2024-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/script.sql 2024-06-03 16:02:07.000000000 +0000 @@ -131,14 +131,6 @@ 'mozilla-builder' ), ( - 'mozilla-builders-application-2024', - 'Mozilla Builder Application', - 'd48a2578-4963-4ac9-9d4e-3005106a3606', - 'd985ba67-a7ad-47a3-8358-04d223a16079', - '6fdbce2c-0110-4bd5-94ce-ff6f4e2ca905', - 'mozilla-builders-application-2024' - ), - ( 'mozilla-innovation', 'Innovation Newsletter', '39fcaf11-2926-4366-b756-2c02e65cbce7', 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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/checks.sql 2024-06-03 16:02:07.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/users_v1/metadata.yaml 2024-06-03 16:14:18.000000000 +0000 @@ -13,7 +13,10 @@ scheduling: dag_name: bqetl_braze date_partition_parameter: null - referenced_tables: [] + referenced_tables: + - - moz-fx-data-shared-prod + - marketing_suppression_list_derived + - main_suppression_list_v1 bigquery: time_partitioning: null range_partitioning: null @@ -28,6 +31,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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/checks.sql 2024-06-03 16:02:07.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_derived/waitlists_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_firefox_subscriptions_sync_v1/query.sql 2024-06-03 16:02:07.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_newsletters_sync_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_products_sync_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:02:07.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-06-03 16:14:18.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-06-03 16:02:07.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-06-03 16:02:07.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_users_sync_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/changed_waitlists_sync_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:02:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/braze_external/delete_users_sync_v1/metadata.yaml 2024-06-03 16:14:18.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-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-03 16:09:14.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: source + mode: NULLABLE +- name: source type: STRING -- mode: NULLABLE - name: event_type + mode: NULLABLE +- name: event_type type: STRING -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: subdivision1 + mode: NULLABLE +- name: subdivision1 type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: event_count + mode: NULLABLE +- name: event_count type: INTEGER -- mode: NULLABLE - name: user_count + mode: NULLABLE +- name: user_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-03 16:01:18.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-03 16:09:13.000000000 +00 ```

⚠️ Only part of the diff is displayed.

Link to full diff

dataops-ci-bot commented 1 month ago

Integration report for "Fix CI issue"

`sql.diff`

Click to expand!