Add view for unnested serp category data

[dzeber]

Flattens serp_categorization data into 1 row per event and expands the extras fields into columns. Also maps the category IDs to names.

Checklist for reviewer:

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

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

• [ ] Follow the change control procedure

┆Issue is synchronized with this Jira Task

[pissac17]

@dzeber Is there a way to add a sample ID to this table using any kind of sampling technique? As the table grows over time, having a sample ID could be used for quick data sampling. Alternatively, would it be more practical to randomly sample the data each time we query from this table?

[dzeber]

@dzeber Is there a way to add a sample ID to this table using any kind of sampling technique?

As discussed, we can't sample on client ID the way we do in other datasets since there is no client identifier. The main unit of analysis for this table is at the level of serp impressions/events, and the table has 1 row per event. The easiest way to sample is probably to do it in SQL at query time.

[dataops-ci-bot]

Integration report for "Map category names"

sql.diff

Click to expand!

```diff Only in /tmp/workspace/main-generated-sql/dags/: bqetl_census_feed.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_browser_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_device_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_os_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_desktop_conv_evnt_categorization.py Only in /tmp/workspace/generated-sql/dags/: bqetl_mobile_engagement_model.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_mobile_kpi_metrics.py diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_contact_export.py /tmp/workspace/generated-sql/dags/bqetl_acoustic_contact_export.py --- /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_contact_export.py 2024-06-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_acoustic_contact_export.py 2024-06-18 22:55:09.000000000 +0000 @@ -99,6 +99,20 @@ depends_on_past=False, ) + with TaskGroup( + "acoustic_external__contact_raw__v1_external", + ) as acoustic_external__contact_raw__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_acoustic_external__contact_raw__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_acoustic_external__contact_raw__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + acoustic_external__contact_raw__v1_external.set_upstream( + acoustic_external__contact_raw__v1 + ) + acoustic_derived__contact__v1.set_upstream(acoustic_external__contact_raw__v1) acoustic_derived__contact_current_snapshot__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_raw_recipient_export.py /tmp/workspace/generated-sql/dags/bqetl_acoustic_raw_recipient_export.py --- /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_raw_recipient_export.py 2024-06-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_acoustic_raw_recipient_export.py 2024-06-18 22:55:08.000000000 +0000 @@ -87,6 +87,20 @@ depends_on_past=False, ) + with TaskGroup( + "acoustic_external__raw_recipient_raw__v1_external", + ) as acoustic_external__raw_recipient_raw__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_acoustic_external__raw_recipient_raw__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_acoustic_external__raw_recipient_raw__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + acoustic_external__raw_recipient_raw__v1_external.set_upstream( + acoustic_external__raw_recipient_raw__v1 + ) + acoustic_derived__raw_recipient__v1.set_upstream( acoustic_external__raw_recipient_raw__v1 ) 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-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_aggregations.py 2024-06-18 22:55:09.000000000 +0000 @@ -54,6 +54,20 @@ tags=tags, ) as dag: + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 = ( + ExternalTaskSensor( + task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + external_dag_id="bqetl_analytics_tables", + external_task_id="checks__fail_fenix_derived__firefox_android_clients__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_checks__fail_telemetry_derived__unified_metrics__v1 = ExternalTaskSensor( task_id="wait_for_checks__fail_telemetry_derived__unified_metrics__v1", external_dag_id="bqetl_unified", @@ -126,36 +140,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 = ExternalTaskSensor( - task_id="wait_for_checks__fail_telemetry_derived__clients_last_seen__v2", + wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_telemetry_derived__clients_last_seen__v1", external_dag_id="bqetl_main_summary", - external_task_id="checks__fail_telemetry_derived__clients_last_seen__v2", - 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_checks__fail_fenix_derived__firefox_android_clients__v1 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_analytics_tables", - external_task_id="checks__fail_fenix_derived__firefox_android_clients__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_fenix_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_fenix_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.fenix_derived__metrics_clients_last_seen__v1", + external_task_id="telemetry_derived__clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -190,46 +178,10 @@ 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", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__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__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_firefox_ios_derived__metrics_clients_last_seen__v1", + wait_for_fenix_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_fenix_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.firefox_ios_derived__metrics_clients_last_seen__v1", + external_task_id="fenix.fenix_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -238,10 +190,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", + 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", @@ -250,10 +202,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", + 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", - external_task_id="focus_android.checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_ios.firefox_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -262,22 +214,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", + wait_for_focus_android_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_focus_android_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__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_focus_android_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_focus_android_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.focus_android_derived__metrics_clients_last_seen__v1", + external_task_id="focus_android.focus_android_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -298,58 +238,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__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_focus_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_focus_ios_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.focus_ios_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_android.checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__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_klar_android_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_klar_android_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_android.klar_android_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1", + wait_for_focus_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_focus_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="klar_ios.checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1", + external_task_id="focus_ios.focus_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -358,10 +250,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_klar_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_klar_ios_derived__metrics_clients_last_seen__v1", + wait_for_klar_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_klar_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="klar_ios.klar_ios_derived__metrics_clients_last_seen__v1", + external_task_id="klar_ios.klar_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -396,16 +288,19 @@ ) ) - wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_telemetry_derived__clients_last_seen__v1", - external_dag_id="bqetl_main_summary", - external_task_id="telemetry_derived__clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=8100), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", + active_users_aggregates_attribution_v1 = bigquery_etl_query( + task_id="active_users_aggregates_attribution_v1", + destination_table="active_users_aggregates_attribution_v1", + dataset_id="telemetry_derived", + project_id="moz-fx-data-shared-prod", + owner="lvargas@mozilla.com", + email=[ + "gkaberere@mozilla.com", + "lvargas@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, ) active_users_aggregates_device_v1 = bigquery_etl_query( @@ -423,6 +318,20 @@ depends_on_past=False, ) + with TaskGroup( + "active_users_aggregates_device_v1_external", + ) as active_users_aggregates_device_v1_external: + ExternalTaskMarker( + task_id="bqetl_search_dashboard__wait_for_active_users_aggregates_device_v1", + external_dag_id="bqetl_search_dashboard", + external_task_id="wait_for_active_users_aggregates_device_v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", + ) + + active_users_aggregates_device_v1_external.set_upstream( + active_users_aggregates_device_v1 + ) + active_users_aggregates_v1 = bigquery_etl_query( task_id="active_users_aggregates_v1", destination_table="active_users_aggregates_v1", @@ -440,7 +349,7 @@ checks__fail_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_fenix_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -451,27 +360,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_fenix_derived__active_users_aggregates__v3_external", - ) as checks__fail_fenix_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_fenix_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_fenix_derived__active_users_aggregates__v3 - ) - checks__fail_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_firefox_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -482,27 +377,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_firefox_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_firefox_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_firefox_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_firefox_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_firefox_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_firefox_ios_derived__active_users_aggregates__v3 - ) - checks__fail_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_focus_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -513,27 +394,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_focus_android_derived__active_users_aggregates__v3_external", - ) as checks__fail_focus_android_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_focus_android_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_focus_android_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_focus_android_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_focus_android_derived__active_users_aggregates__v3 - ) - checks__fail_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_focus_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -544,58 +411,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_focus_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_focus_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_focus_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_focus_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_focus_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_focus_ios_derived__active_users_aggregates__v3 - ) - - checks__fail_klar_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_klar_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, - ) - - with TaskGroup( - "checks__fail_klar_android_derived__active_users_aggregates__v3_external", - ) as checks__fail_klar_android_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_klar_android_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_klar_android_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_klar_android_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_klar_android_derived__active_users_aggregates__v3 - ) - checks__fail_klar_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_klar_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -606,27 +428,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_klar_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_klar_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_klar_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_klar_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_klar_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_klar_ios_derived__active_users_aggregates__v3 - ) - checks__warn_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_fenix_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -637,13 +445,14 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - checks__warn_firefox_desktop_derived__active_users_aggregates__v3 = bigquery_dq_check( + checks__warn_firefox_desktop_derived__active_users_aggregates__v3 = ( + bigquery_dq_check( task_id="checks__warn_firefox_desktop_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="firefox_desktop_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -654,13 +463,14 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) + ) checks__warn_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_firefox_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -671,13 +481,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_focus_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -688,13 +498,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_focus_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -705,30 +515,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, - ) - - checks__warn_klar_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__warn_klar_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=False, - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_klar_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_klar_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -739,13 +532,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) fenix_active_users_aggregates = bigquery_etl_query( task_id="fenix_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -754,14 +547,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) firefox_desktop_active_users_aggregates = bigquery_etl_query( task_id="firefox_desktop_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="firefox_desktop_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -770,28 +562,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - ) - - with TaskGroup( - "firefox_desktop_active_users_aggregates_external", - ) as firefox_desktop_active_users_aggregates_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_firefox_desktop_active_users_aggregates", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_firefox_desktop_active_users_aggregates", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - firefox_desktop_active_users_aggregates_external.set_upstream( - firefox_desktop_active_users_aggregates ) firefox_ios_active_users_aggregates = bigquery_etl_query( task_id="firefox_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -800,14 +577,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) focus_android_active_users_aggregates = bigquery_etl_query( task_id="focus_android_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -816,14 +592,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) focus_ios_active_users_aggregates = bigquery_etl_query( task_id="focus_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -832,30 +607,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - ) - - klar_android_active_users_aggregates = bigquery_etl_query( - task_id="klar_android_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) klar_ios_active_users_aggregates = bigquery_etl_query( task_id="klar_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -864,9 +622,8 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) telemetry_derived__cohort_daily_statistics__v1 = bigquery_etl_query( @@ -901,6 +658,14 @@ depends_on_past=False, ) + active_users_aggregates_attribution_v1.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 + ) + + active_users_aggregates_attribution_v1.set_upstream( + wait_for_checks__fail_telemetry_derived__unified_metrics__v1 + ) + active_users_aggregates_device_v1.set_upstream( wait_for_checks__fail_telemetry_derived__unified_metrics__v1 ) @@ -945,10 +710,6 @@ focus_ios_active_users_aggregates ) - checks__fail_klar_android_derived__active_users_aggregates__v3.set_upstream( - klar_android_active_users_aggregates - ) - checks__fail_klar_ios_derived__active_users_aggregates__v3.set_upstream( klar_ios_active_users_aggregates ) @@ -978,11 +739,11 @@ ) checks__warn_firefox_desktop_derived__active_users_aggregates__v3.set_upstream( - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 + firefox_desktop_active_users_aggregates ) checks__warn_firefox_desktop_derived__active_users_aggregates__v3.set_upstream( - firefox_desktop_active_users_aggregates + wait_for_telemetry_derived__clients_last_seen__v1 ) checks__warn_firefox_ios_derived__active_users_aggregates__v3.set_upstream( @@ -997,10 +758,6 @@ focus_ios_active_users_aggregates ) - checks__warn_klar_android_derived__active_users_aggregates__v3.set_upstream( - klar_android_active_users_aggregates - ) - checks__warn_klar_ios_derived__active_users_aggregates__v3.set_upstream( klar_ios_active_users_aggregates ) @@ -1010,99 +767,95 @@ ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 - ) - - fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1 - ) - - fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1 + wait_for_fenix_derived__clients_last_seen_joined__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_fenix_derived__metrics_clients_last_seen__v1 + 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 + wait_for_telemetry_derived__clients_last_seen__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 + wait_for_firefox_ios_derived__clients_last_seen_joined__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) - firefox_ios_active_users_aggregates.set_upstream( - wait_for_firefox_ios_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) focus_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 + wait_for_telemetry_derived__core_clients_last_seen__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_focus_android_derived__metrics_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_telemetry_derived__core_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) focus_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 + wait_for_focus_ios_derived__clients_last_seen_joined__v1 ) focus_ios_active_users_aggregates.set_upstream( - wait_for_focus_ios_derived__metrics_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) - klar_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) - klar_android_active_users_aggregates.set_upstream( - wait_for_klar_android_derived__metrics_clients_last_seen__v1 + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 + ) + + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) klar_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1 + wait_for_klar_ios_derived__clients_last_seen_joined__v1 ) klar_ios_active_users_aggregates.set_upstream( - wait_for_klar_ios_derived__metrics_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) telemetry_derived__cohort_daily_statistics__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py --- /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py 2024-06-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py 2024-06-18 22:55:10.000000000 +0000 @@ -211,10 +211,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 = ExternalTaskSensor( - task_id="wait_for_checks__fail_telemetry_derived__clients_last_seen__v2", + wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_telemetry_derived__clients_last_seen__v1", external_dag_id="bqetl_main_summary", - external_task_id="checks__fail_telemetry_derived__clients_last_seen__v2", + external_task_id="telemetry_derived__clients_last_seen__v1", check_existence=True, mode="reschedule", allowed_states=ALLOWED_STATES, @@ -265,13 +265,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_kpi_metrics__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_mobile_kpi_metrics", - external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_mobile_feature_usage__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", external_dag_id="bqetl_mobile_feature_usage", external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", @@ -291,6 +284,13 @@ external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", ) + ExternalTaskMarker( + task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + external_dag_id="bqetl_mobile_engagement_model", + external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", + ) + checks__fail_fenix_derived__firefox_android_clients__v1_external.set_upstream( checks__fail_fenix_derived__firefox_android_clients__v1 ) @@ -324,13 +324,6 @@ ) ExternalTaskMarker( - task_id="bqetl_desktop_conv_evnt_categorization__wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", - external_dag_id="bqetl_desktop_conv_evnt_categorization", - external_task_id="wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_mozilla_org_derived__wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", external_dag_id="bqetl_mozilla_org_derived", external_task_id="wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", @@ -693,5 +686,5 @@ ) telemetry_derived__clients_first_seen_28_days_later__v1.set_upstream( - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 + wait_for_telemetry_derived__clients_last_seen__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-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_braze.py 2024-06-18 22:55:10.000000000 +0000 @@ -50,11 +50,59 @@ with DAG( "bqetl_braze", default_args=default_args, - schedule_interval="0 5,13,21 * * *", + schedule_interval="0 10 * * *", doc_md=docs, 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", + execution_delta=datetime.timedelta(seconds=29700), + 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", + execution_delta=datetime.timedelta(seconds=25200), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + + wait_for_acoustic_external__contact_raw__v1 = ExternalTaskSensor( + task_id="wait_for_acoustic_external__contact_raw__v1", + external_dag_id="bqetl_acoustic_contact_export", + external_task_id="acoustic_external__contact_raw__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_acoustic_external__raw_recipient_raw__v1 = ExternalTaskSensor( + task_id="wait_for_acoustic_external__raw_recipient_raw__v1", + external_dag_id="bqetl_acoustic_raw_recipient_export", + external_task_id="acoustic_external__raw_recipient_raw__v1", + execution_delta=datetime.timedelta(seconds=3600), + 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", @@ -103,6 +151,18 @@ sql_file_path="sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/script.sql", ) + braze_derived__suppressions__v1 = bigquery_etl_query( + task_id="braze_derived__suppressions__v1", + destination_table="suppressions_v1", + dataset_id="braze_derived", + 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_derived__user_profiles__v1 = bigquery_etl_query( task_id="braze_derived__user_profiles__v1", destination_table="user_profiles_v1", @@ -149,7 +209,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_newsletters_sync__v1 = bigquery_etl_query( @@ -162,7 +221,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_products_sync__v1 = bigquery_etl_query( @@ -175,7 +233,18 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], + ) + + 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( @@ -200,7 +269,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_waitlists_sync__v1 = bigquery_etl_query( @@ -213,7 +281,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__delete_users_sync__v1 = bigquery_etl_query( @@ -226,7 +293,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__users_previous_day_snapshot__v1 = bigquery_etl_query( @@ -241,6 +307,18 @@ sql_file_path="sql/moz-fx-data-shared-prod/braze_external/users_previous_day_snapshot_v1/script.sql", ) + braze_external__users_previous_day_snapshot__v2 = bigquery_etl_query( + task_id="braze_external__users_previous_day_snapshot__v2", + destination_table=None, + 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, + sql_file_path="sql/moz-fx-data-shared-prod/braze_external/users_previous_day_snapshot_v2/script.sql", + ) + checks__fail_braze_derived__newsletters__v1 = bigquery_dq_check( task_id="checks__fail_braze_derived__newsletters__v1", source_table="newsletters_v1", @@ -332,9 +410,9 @@ retries=0, ) - checks__fail_braze_external__users_previous_day_snapshot__v1 = bigquery_dq_check( - task_id="checks__fail_braze_external__users_previous_day_snapshot__v1", - source_table="users_previous_day_snapshot_v1", + 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, @@ -355,7 +433,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -369,7 +446,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -383,7 +459,19 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], + 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, ) @@ -410,7 +498,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -424,7 +511,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -438,12 +524,17 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) braze_derived__newsletters__v1.set_upstream(checks__fail_braze_derived__users__v1) + braze_derived__products__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 ) @@ -452,6 +543,10 @@ checks__fail_braze_derived__user_profiles__v1 ) + braze_derived__suppressions__v1.set_upstream( + wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1 + ) + braze_derived__user_profiles__v1.set_upstream( checks__fail_braze_derived__newsletters__v1 ) @@ -466,10 +561,24 @@ checks__fail_braze_derived__waitlists__v1 ) + braze_derived__users__v1.set_upstream(wait_for_acoustic_external__contact_raw__v1) + + braze_derived__users__v1.set_upstream( + wait_for_acoustic_external__raw_recipient_raw__v1 + ) + + braze_derived__users__v1.set_upstream( + checks__fail_braze_derived__subscriptions_map__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( @@ -480,12 +589,16 @@ 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( - checks__fail_braze_derived__users__v1 + braze_external__users_previous_day_snapshot__v2 ) braze_external__changed_users__v1.set_upstream( - checks__fail_braze_external__users_previous_day_snapshot__v1 + checks__fail_braze_derived__users__v1 ) braze_external__changed_users_sync__v1.set_upstream( @@ -522,8 +635,12 @@ checks__fail_braze_derived__waitlists__v1.set_upstream(braze_derived__waitlists__v1) - checks__fail_braze_external__users_previous_day_snapshot__v1.set_upstream( - braze_external__users_previous_day_snapshot__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( @@ -538,6 +655,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_census_feed.py /tmp/workspace/generated-sql/dags/bqetl_census_feed.py --- /tmp/workspace/main-generated-sql/dags/bqetl_census_feed.py 2024-06-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_census_feed.py 1970-01-01 00:00:00.000000000 +0000 @@ -1,82 +0,0 @@ -# Generated via https://github.com/mozilla/bigquery-etl/blob/main/bigquery_etl/query_scheduling/generate_airflow_dags.py - -from airflow import DAG -from airflow.sensors.external_task import ExternalTaskMarker -from airflow.sensors.external_task import ExternalTaskSensor -from airflow.utils.task_group import TaskGroup -import datetime -from operators.gcp_container_operator import GKEPodOperator -from utils.constants import ALLOWED_STATES, FAILED_STATES -from utils.gcp import bigquery_etl_query, bigquery_dq_check - -docs = """ -### bqetl_census_feed - -Built from bigquery-etl repo, [`dags/bqetl_census_feed.py`](https://github.com/mozilla/bigquery-etl/blob/generated-sql/dags/bqetl_census_feed.py) - -#### Description - -Loads the desktop conversion event tables -#### Owner - -kwindau@mozilla.com - -#### Tags - -* impact/tier_2 -* repo/bigquery-etl -""" - - -default_args = { - "owner": "kwindau@mozilla.com", - "start_date": datetime.datetime(2024, 6, 10, 0, 0), - "end_date": None, - "email": ["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - "depends_on_past": False, - "retry_delay": datetime.timedelta(seconds=1800), - "email_on_failure": True, - "email_on_retry": False, - "retries": 2, -} - -tags = ["impact/tier_2", "repo/bigquery-etl"] - -with DAG( - "bqetl_census_feed", - default_args=default_args, - schedule_interval="0 17 * * *", - doc_md=docs, - tags=tags, -) as dag: - - wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2", - external_dag_id="bqetl_google_analytics_derived_ga4", - external_task_id="checks__fail_mozilla_org_derived__gclid_conversions__v2", - execution_delta=datetime.timedelta(seconds=18000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - ) - - mozilla_org_derived__ga_desktop_conversions__v1 = bigquery_etl_query( - task_id="mozilla_org_derived__ga_desktop_conversions__v1", - destination_table='ga_desktop_conversions_v1${{ macros.ds_format(macros.ds_add(ds, -2), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - parameters=["activity_date:DATE:{{macros.ds_add(ds, -2)}}"] - + ["submission_date:DATE:{{ds}}"], - ) - - mozilla_org_derived__ga_desktop_conversions__v1.set_upstream( - wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2 - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_cloudflare_browser_market_share.py /tmp/workspace/generated-sql/dags/bqetl_cloudflare_browser_market_share.py --- /tmp/workspace/main-generated-sql/dags/bqetl_cloudflare_browser_market_share.py 2024-06-18 22:58:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_cloudflare_browser_market_share.py 1970-01-01 00:00:00.000000000 +0000 @@ -1,69 +0,0 @@ -# Generated via https://github.com/mozilla/bigquery-etl/blob/main/bigquery_etl/q ```

⚠️ Only part of the diff is displayed.

Link to full diff

[dataops-ci-bot]

Integration report for "Make uncategorized sessions have null category"

sql.diff

Click to expand!

```diff Only in /tmp/workspace/main-generated-sql/dags/: bqetl_census_feed.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_browser_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_device_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_os_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_desktop_conv_evnt_categorization.py Only in /tmp/workspace/generated-sql/dags/: bqetl_mobile_engagement_model.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_mobile_kpi_metrics.py diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_contact_export.py /tmp/workspace/generated-sql/dags/bqetl_acoustic_contact_export.py --- /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_contact_export.py 2024-06-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_acoustic_contact_export.py 2024-06-21 18:07:41.000000000 +0000 @@ -99,6 +99,20 @@ depends_on_past=False, ) + with TaskGroup( + "acoustic_external__contact_raw__v1_external", + ) as acoustic_external__contact_raw__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_acoustic_external__contact_raw__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_acoustic_external__contact_raw__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + acoustic_external__contact_raw__v1_external.set_upstream( + acoustic_external__contact_raw__v1 + ) + acoustic_derived__contact__v1.set_upstream(acoustic_external__contact_raw__v1) acoustic_derived__contact_current_snapshot__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_raw_recipient_export.py /tmp/workspace/generated-sql/dags/bqetl_acoustic_raw_recipient_export.py --- /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_raw_recipient_export.py 2024-06-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_acoustic_raw_recipient_export.py 2024-06-21 18:07:40.000000000 +0000 @@ -87,6 +87,20 @@ depends_on_past=False, ) + with TaskGroup( + "acoustic_external__raw_recipient_raw__v1_external", + ) as acoustic_external__raw_recipient_raw__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_acoustic_external__raw_recipient_raw__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_acoustic_external__raw_recipient_raw__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + acoustic_external__raw_recipient_raw__v1_external.set_upstream( + acoustic_external__raw_recipient_raw__v1 + ) + acoustic_derived__raw_recipient__v1.set_upstream( acoustic_external__raw_recipient_raw__v1 ) 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-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_aggregations.py 2024-06-21 18:07:41.000000000 +0000 @@ -54,6 +54,20 @@ tags=tags, ) as dag: + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 = ( + ExternalTaskSensor( + task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + external_dag_id="bqetl_analytics_tables", + external_task_id="checks__fail_fenix_derived__firefox_android_clients__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_checks__fail_telemetry_derived__unified_metrics__v1 = ExternalTaskSensor( task_id="wait_for_checks__fail_telemetry_derived__unified_metrics__v1", external_dag_id="bqetl_unified", @@ -126,36 +140,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 = ExternalTaskSensor( - task_id="wait_for_checks__fail_telemetry_derived__clients_last_seen__v2", + wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_telemetry_derived__clients_last_seen__v1", external_dag_id="bqetl_main_summary", - external_task_id="checks__fail_telemetry_derived__clients_last_seen__v2", - 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_checks__fail_fenix_derived__firefox_android_clients__v1 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_analytics_tables", - external_task_id="checks__fail_fenix_derived__firefox_android_clients__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_fenix_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_fenix_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.fenix_derived__metrics_clients_last_seen__v1", + external_task_id="telemetry_derived__clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -190,22 +178,10 @@ 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", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", + wait_for_fenix_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_fenix_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", + external_task_id="fenix.fenix_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -214,10 +190,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", + 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", @@ -226,10 +202,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_firefox_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_firefox_ios_derived__metrics_clients_last_seen__v1", + 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", - external_task_id="firefox_ios.firefox_ios_derived__metrics_clients_last_seen__v1", + external_task_id="firefox_ios.firefox_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -238,46 +214,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", + wait_for_focus_android_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_focus_android_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__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_focus_android_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_focus_android_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.focus_android_derived__metrics_clients_last_seen__v1", + external_task_id="focus_android.focus_android_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -298,10 +238,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", + wait_for_focus_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_focus_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", + external_task_id="focus_ios.focus_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -310,58 +250,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_focus_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_focus_ios_derived__metrics_clients_last_seen__v1", + wait_for_klar_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_klar_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.focus_ios_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_android.checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__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_klar_android_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_klar_android_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_android.klar_android_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_ios.checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__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_klar_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_klar_ios_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_ios.klar_ios_derived__metrics_clients_last_seen__v1", + external_task_id="klar_ios.klar_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -396,21 +288,9 @@ ) ) - wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_telemetry_derived__clients_last_seen__v1", - external_dag_id="bqetl_main_summary", - external_task_id="telemetry_derived__clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=8100), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - active_users_aggregates_device_v1 = bigquery_etl_query( - task_id="active_users_aggregates_device_v1", - destination_table="active_users_aggregates_device_v1", + active_users_aggregates_attribution_v1 = bigquery_etl_query( + task_id="active_users_aggregates_attribution_v1", + destination_table="active_users_aggregates_attribution_v1", dataset_id="telemetry_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -423,72 +303,54 @@ depends_on_past=False, ) - checks__fail_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_fenix_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="fenix_derived", + active_users_aggregates_device_v1 = bigquery_etl_query( + task_id="active_users_aggregates_device_v1", + destination_table="active_users_aggregates_device_v1", + dataset_id="telemetry_derived", project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, owner="lvargas@mozilla.com", email=[ "gkaberere@mozilla.com", "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, ) with TaskGroup( - "checks__fail_fenix_derived__active_users_aggregates__v3_external", - ) as checks__fail_fenix_derived__active_users_aggregates__v3_external: + "active_users_aggregates_device_v1_external", + ) as active_users_aggregates_device_v1_external: ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", + task_id="bqetl_search_dashboard__wait_for_active_users_aggregates_device_v1", external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", + external_task_id="wait_for_active_users_aggregates_device_v1", execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", ) - checks__fail_fenix_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_fenix_derived__active_users_aggregates__v3 + active_users_aggregates_device_v1_external.set_upstream( + active_users_aggregates_device_v1 ) - checks__fail_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_firefox_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="firefox_ios_derived", + active_users_aggregates_v1 = bigquery_etl_query( + task_id="active_users_aggregates_v1", + destination_table="active_users_aggregates_v1", + dataset_id="telemetry_derived", project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, owner="lvargas@mozilla.com", email=[ "gkaberere@mozilla.com", "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, - ) - - with TaskGroup( - "checks__fail_firefox_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_firefox_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_firefox_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_firefox_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_firefox_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_firefox_ios_derived__active_users_aggregates__v3 ) - checks__fail_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_focus_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="focus_android_derived", + checks__fail_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_fenix_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, owner="lvargas@mozilla.com", @@ -498,28 +360,14 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_focus_android_derived__active_users_aggregates__v3_external", - ) as checks__fail_focus_android_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_focus_android_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_focus_android_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_focus_android_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_focus_android_derived__active_users_aggregates__v3 - ) - - checks__fail_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_focus_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="focus_ios_derived", + checks__fail_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_firefox_ios_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, owner="lvargas@mozilla.com", @@ -529,28 +377,14 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_focus_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_focus_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_focus_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_focus_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_focus_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_focus_ios_derived__active_users_aggregates__v3 - ) - - checks__fail_klar_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_klar_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", + checks__fail_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_focus_android_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, owner="lvargas@mozilla.com", @@ -560,27 +394,30 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_klar_android_derived__active_users_aggregates__v3_external", - ) as checks__fail_klar_android_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_klar_android_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_klar_android_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_klar_android_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_klar_android_derived__active_users_aggregates__v3 + checks__fail_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_focus_ios_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="focus_ios_derived", + project_id="moz-fx-data-shared-prod", + is_dq_check_fail=True, + owner="lvargas@mozilla.com", + email=[ + "gkaberere@mozilla.com", + "lvargas@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + depends_on_past=False, + parameters=["submission_date:DATE:{{ds}}"], + retries=0, ) checks__fail_klar_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_klar_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -591,27 +428,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_klar_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_klar_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_klar_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_klar_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_klar_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_klar_ios_derived__active_users_aggregates__v3 - ) - checks__warn_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_fenix_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -622,7 +445,7 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) @@ -647,7 +470,7 @@ checks__warn_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_firefox_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -658,13 +481,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_focus_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -675,13 +498,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_focus_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -692,30 +515,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, - ) - - checks__warn_klar_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__warn_klar_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=False, - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_klar_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_klar_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -726,13 +532,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) fenix_active_users_aggregates = bigquery_etl_query( task_id="fenix_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -741,9 +547,8 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) firefox_desktop_active_users_aggregates = bigquery_etl_query( @@ -761,23 +566,9 @@ depends_on_past=False, ) - with TaskGroup( - "firefox_desktop_active_users_aggregates_external", - ) as firefox_desktop_active_users_aggregates_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_firefox_desktop_active_users_aggregates", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_firefox_desktop_active_users_aggregates", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - firefox_desktop_active_users_aggregates_external.set_upstream( - firefox_desktop_active_users_aggregates - ) - firefox_ios_active_users_aggregates = bigquery_etl_query( task_id="firefox_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -786,14 +577,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) focus_android_active_users_aggregates = bigquery_etl_query( task_id="focus_android_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -802,14 +592,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) focus_ios_active_users_aggregates = bigquery_etl_query( task_id="focus_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -818,30 +607,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - ) - - klar_android_active_users_aggregates = bigquery_etl_query( - task_id="klar_android_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) klar_ios_active_users_aggregates = bigquery_etl_query( task_id="klar_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -850,9 +622,8 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) telemetry_derived__cohort_daily_statistics__v1 = bigquery_etl_query( @@ -887,10 +658,22 @@ depends_on_past=False, ) + active_users_aggregates_attribution_v1.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 + ) + + active_users_aggregates_attribution_v1.set_upstream( + wait_for_checks__fail_telemetry_derived__unified_metrics__v1 + ) + active_users_aggregates_device_v1.set_upstream( wait_for_checks__fail_telemetry_derived__unified_metrics__v1 ) + active_users_aggregates_v1.set_upstream( + wait_for_checks__fail_telemetry_derived__unified_metrics__v1 + ) + checks__fail_fenix_derived__active_users_aggregates__v3.set_upstream( wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 ) @@ -927,10 +710,6 @@ focus_ios_active_users_aggregates ) - checks__fail_klar_android_derived__active_users_aggregates__v3.set_upstream( - klar_android_active_users_aggregates - ) - checks__fail_klar_ios_derived__active_users_aggregates__v3.set_upstream( klar_ios_active_users_aggregates ) @@ -960,11 +739,11 @@ ) checks__warn_firefox_desktop_derived__active_users_aggregates__v3.set_upstream( - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 + firefox_desktop_active_users_aggregates ) checks__warn_firefox_desktop_derived__active_users_aggregates__v3.set_upstream( - firefox_desktop_active_users_aggregates + wait_for_telemetry_derived__clients_last_seen__v1 ) checks__warn_firefox_ios_derived__active_users_aggregates__v3.set_upstream( @@ -979,10 +758,6 @@ focus_ios_active_users_aggregates ) - checks__warn_klar_android_derived__active_users_aggregates__v3.set_upstream( - klar_android_active_users_aggregates - ) - checks__warn_klar_ios_derived__active_users_aggregates__v3.set_upstream( klar_ios_active_users_aggregates ) @@ -992,99 +767,95 @@ ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 - ) - - fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1 - ) - - fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1 + wait_for_fenix_derived__clients_last_seen_joined__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_fenix_derived__metrics_clients_last_seen__v1 + 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 + wait_for_telemetry_derived__clients_last_seen__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 + wait_for_firefox_ios_derived__clients_last_seen_joined__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) - firefox_ios_active_users_aggregates.set_upstream( - wait_for_firefox_ios_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) focus_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 + wait_for_telemetry_derived__core_clients_last_seen__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_focus_android_derived__metrics_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_telemetry_derived__core_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) focus_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 + wait_for_focus_ios_derived__clients_last_seen_joined__v1 ) focus_ios_active_users_aggregates.set_upstream( - wait_for_focus_ios_derived__metrics_clients_last_seen__v1 + 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 ) - klar_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) - klar_android_active_users_aggregates.set_upstream( - wait_for_klar_android_derived__metrics_clients_last_seen__v1 + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) klar_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1 + wait_for_klar_ios_derived__clients_last_seen_joined__v1 ) klar_ios_active_users_aggregates.set_upstream( - wait_for_klar_ios_derived__metrics_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) telemetry_derived__cohort_daily_statistics__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py --- /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py 2024-06-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py 2024-06-21 18:07:42.000000000 +0000 @@ -211,10 +211,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 = ExternalTaskSensor( - task_id="wait_for_checks__fail_telemetry_derived__clients_last_seen__v2", + wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_telemetry_derived__clients_last_seen__v1", external_dag_id="bqetl_main_summary", - external_task_id="checks__fail_telemetry_derived__clients_last_seen__v2", + external_task_id="telemetry_derived__clients_last_seen__v1", check_existence=True, mode="reschedule", allowed_states=ALLOWED_STATES, @@ -265,13 +265,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_kpi_metrics__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_mobile_kpi_metrics", - external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_mobile_feature_usage__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", external_dag_id="bqetl_mobile_feature_usage", external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", @@ -291,6 +284,13 @@ external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", ) + ExternalTaskMarker( + task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + external_dag_id="bqetl_mobile_engagement_model", + external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", + ) + checks__fail_fenix_derived__firefox_android_clients__v1_external.set_upstream( checks__fail_fenix_derived__firefox_android_clients__v1 ) @@ -324,13 +324,6 @@ ) ExternalTaskMarker( - task_id="bqetl_desktop_conv_evnt_categorization__wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", - external_dag_id="bqetl_desktop_conv_evnt_categorization", - external_task_id="wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_mozilla_org_derived__wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", external_dag_id="bqetl_mozilla_org_derived", external_task_id="wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", @@ -693,5 +686,5 @@ ) telemetry_derived__clients_first_seen_28_days_later__v1.set_upstream( - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 + wait_for_telemetry_derived__clients_last_seen__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-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_braze.py 2024-06-21 18:07:42.000000000 +0000 @@ -50,11 +50,59 @@ with DAG( "bqetl_braze", default_args=default_args, - schedule_interval="0 5,13,21 * * *", + schedule_interval="0 10 * * *", doc_md=docs, 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", + execution_delta=datetime.timedelta(seconds=29700), + 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", + execution_delta=datetime.timedelta(seconds=25200), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + + wait_for_acoustic_external__contact_raw__v1 = ExternalTaskSensor( + task_id="wait_for_acoustic_external__contact_raw__v1", + external_dag_id="bqetl_acoustic_contact_export", + external_task_id="acoustic_external__contact_raw__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_acoustic_external__raw_recipient_raw__v1 = ExternalTaskSensor( + task_id="wait_for_acoustic_external__raw_recipient_raw__v1", + external_dag_id="bqetl_acoustic_raw_recipient_export", + external_task_id="acoustic_external__raw_recipient_raw__v1", + execution_delta=datetime.timedelta(seconds=3600), + 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", @@ -103,6 +151,18 @@ sql_file_path="sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/script.sql", ) + braze_derived__suppressions__v1 = bigquery_etl_query( + task_id="braze_derived__suppressions__v1", + destination_table="suppressions_v1", + dataset_id="braze_derived", + 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_derived__user_profiles__v1 = bigquery_etl_query( task_id="braze_derived__user_profiles__v1", destination_table="user_profiles_v1", @@ -149,7 +209,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_newsletters_sync__v1 = bigquery_etl_query( @@ -162,7 +221,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_products_sync__v1 = bigquery_etl_query( @@ -175,7 +233,18 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], + ) + + 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( @@ -200,7 +269,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_waitlists_sync__v1 = bigquery_etl_query( @@ -213,7 +281,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__delete_users_sync__v1 = bigquery_etl_query( @@ -226,7 +293,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__users_previous_day_snapshot__v1 = bigquery_etl_query( @@ -241,6 +307,18 @@ sql_file_path="sql/moz-fx-data-shared-prod/braze_external/users_previous_day_snapshot_v1/script.sql", ) + braze_external__users_previous_day_snapshot__v2 = bigquery_etl_query( + task_id="braze_external__users_previous_day_snapshot__v2", + destination_table=None, + 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, + sql_file_path="sql/moz-fx-data-shared-prod/braze_external/users_previous_day_snapshot_v2/script.sql", + ) + checks__fail_braze_derived__newsletters__v1 = bigquery_dq_check( task_id="checks__fail_braze_derived__newsletters__v1", source_table="newsletters_v1", @@ -332,9 +410,9 @@ retries=0, ) - checks__fail_braze_external__users_previous_day_snapshot__v1 = bigquery_dq_check( - task_id="checks__fail_braze_external__users_previous_day_snapshot__v1", - source_table="users_previous_day_snapshot_v1", + 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, @@ -355,7 +433,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -369,7 +446,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -383,7 +459,19 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], + 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, ) @@ -410,7 +498,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -424,7 +511,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -438,12 +524,17 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) braze_derived__newsletters__v1.set_upstream(checks__fail_braze_derived__users__v1) + braze_derived__products__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 ) @@ -452,6 +543,10 @@ checks__fail_braze_derived__user_profiles__v1 ) + braze_derived__suppressions__v1.set_upstream( + wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1 + ) + braze_derived__user_profiles__v1.set_upstream( checks__fail_braze_derived__newsletters__v1 ) @@ -466,10 +561,24 @@ checks__fail_braze_derived__waitlists__v1 ) + braze_derived__users__v1.set_upstream(wait_for_acoustic_external__contact_raw__v1) + + braze_derived__users__v1.set_upstream( + wait_for_acoustic_external__raw_recipient_raw__v1 + ) + + braze_derived__users__v1.set_upstream( + checks__fail_braze_derived__subscriptions_map__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( @@ -480,12 +589,16 @@ 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( - checks__fail_braze_derived__users__v1 + braze_external__users_previous_day_snapshot__v2 ) braze_external__changed_users__v1.set_upstream( - checks__fail_braze_external__users_previous_day_snapshot__v1 + checks__fail_braze_derived__users__v1 ) braze_external__changed_users_sync__v1.set_upstream( @@ -522,8 +635,12 @@ checks__fail_braze_derived__waitlists__v1.set_upstream(braze_derived__waitlists__v1) - checks__fail_braze_external__users_previous_day_snapshot__v1.set_upstream( - braze_external__users_previous_day_snapshot__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( @@ -538,6 +655,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_census_feed.py /tmp/workspace/generated-sql/dags/bqetl_census_feed.py --- /tmp/workspace/main-generated-sql/dags/bqetl_census_feed.py 2024-06-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_census_feed.py 1970-01-01 00:00:00.000000000 +0000 @@ -1,82 +0,0 @@ -# Generated via https://github.com/mozilla/bigquery-etl/blob/main/bigquery_etl/query_scheduling/generate_airflow_dags.py - -from airflow import DAG -from airflow.sensors.external_task import ExternalTaskMarker -from airflow.sensors.external_task import ExternalTaskSensor -from airflow.utils.task_group import TaskGroup -import datetime -from operators.gcp_container_operator import GKEPodOperator -from utils.constants import ALLOWED_STATES, FAILED_STATES -from utils.gcp import bigquery_etl_query, bigquery_dq_check - -docs = """ -### bqetl_census_feed - -Built from bigquery-etl repo, [`dags/bqetl_census_feed.py`](https://github.com/mozilla/bigquery-etl/blob/generated-sql/dags/bqetl_census_feed.py) - -#### Description - -Loads the desktop conversion event tables -#### Owner - -kwindau@mozilla.com - -#### Tags - -* impact/tier_2 -* repo/bigquery-etl -""" - - -default_args = { - "owner": "kwindau@mozilla.com", - "start_date": datetime.datetime(2024, 6, 10, 0, 0), - "end_date": None, - "email": ["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - "depends_on_past": False, - "retry_delay": datetime.timedelta(seconds=1800), - "email_on_failure": True, - "email_on_retry": False, - "retries": 2, -} - -tags = ["impact/tier_2", "repo/bigquery-etl"] - -with DAG( - "bqetl_census_feed", - default_args=default_args, - schedule_interval="0 17 * * *", - doc_md=docs, - tags=tags, -) as dag: - - wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2", - external_dag_id="bqetl_google_analytics_derived_ga4", - external_task_id="checks__fail_mozilla_org_derived__gclid_conversions__v2", - execution_delta=datetime.timedelta(seconds=18000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - ) - - mozilla_org_derived__ga_desktop_conversions__v1 = bigquery_etl_query( - task_id="mozilla_org_derived__ga_desktop_conversions__v1", - destination_table='ga_desktop_conversions_v1${{ macros.ds_format(macros.ds_add(ds, -2), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - parameters=["activity_date:DATE:{{macros.ds_add(ds, -2)}}"] - + ["submission_date:DATE:{{ds}}"], - ) - - mozilla_org_derived__ga_desktop_conversions__v1.set_upstream( - wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2 - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_cloudflare_browser_market_share.py /tmp/workspace/generated-sql/dags/bqetl_cloudflare_browser_market_share.py --- /tmp/workspace/main-generated-sql/dags/bqetl_cloudflare_browser_market_share.py 2024-06-21 17:55:34.000000000 +0000 +++ /tmp/workspace/generated ```

⚠️ Only part of the diff is displayed.

Link to full diff

[dzeber]

@curtismorales looks like the CI failed with

400 Table *****************************.serp_category_name does not have a schema

I'm not sure what to make of this. This is the table from https://github.com/mozilla/private-bigquery-etl/pull/411

[dataops-ci-bot]

Integration report for "Remove event timestamp"

sql.diff

Click to expand!

```diff Only in /tmp/workspace/main-generated-sql/dags/: bqetl_census_feed.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_browser_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_device_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_cloudflare_os_market_share.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_desktop_conv_evnt_categorization.py Only in /tmp/workspace/generated-sql/dags/: bqetl_mobile_engagement_model.py Only in /tmp/workspace/main-generated-sql/dags/: bqetl_mobile_kpi_metrics.py diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_contact_export.py /tmp/workspace/generated-sql/dags/bqetl_acoustic_contact_export.py --- /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_contact_export.py 2024-06-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_acoustic_contact_export.py 2024-06-21 18:48:45.000000000 +0000 @@ -99,6 +99,20 @@ depends_on_past=False, ) + with TaskGroup( + "acoustic_external__contact_raw__v1_external", + ) as acoustic_external__contact_raw__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_acoustic_external__contact_raw__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_acoustic_external__contact_raw__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + acoustic_external__contact_raw__v1_external.set_upstream( + acoustic_external__contact_raw__v1 + ) + acoustic_derived__contact__v1.set_upstream(acoustic_external__contact_raw__v1) acoustic_derived__contact_current_snapshot__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_raw_recipient_export.py /tmp/workspace/generated-sql/dags/bqetl_acoustic_raw_recipient_export.py --- /tmp/workspace/main-generated-sql/dags/bqetl_acoustic_raw_recipient_export.py 2024-06-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_acoustic_raw_recipient_export.py 2024-06-21 18:48:44.000000000 +0000 @@ -87,6 +87,20 @@ depends_on_past=False, ) + with TaskGroup( + "acoustic_external__raw_recipient_raw__v1_external", + ) as acoustic_external__raw_recipient_raw__v1_external: + ExternalTaskMarker( + task_id="bqetl_braze__wait_for_acoustic_external__raw_recipient_raw__v1", + external_dag_id="bqetl_braze", + external_task_id="wait_for_acoustic_external__raw_recipient_raw__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + acoustic_external__raw_recipient_raw__v1_external.set_upstream( + acoustic_external__raw_recipient_raw__v1 + ) + acoustic_derived__raw_recipient__v1.set_upstream( acoustic_external__raw_recipient_raw__v1 ) 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-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_aggregations.py 2024-06-21 18:48:45.000000000 +0000 @@ -54,6 +54,20 @@ tags=tags, ) as dag: + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 = ( + ExternalTaskSensor( + task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + external_dag_id="bqetl_analytics_tables", + external_task_id="checks__fail_fenix_derived__firefox_android_clients__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_checks__fail_telemetry_derived__unified_metrics__v1 = ExternalTaskSensor( task_id="wait_for_checks__fail_telemetry_derived__unified_metrics__v1", external_dag_id="bqetl_unified", @@ -126,36 +140,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 = ExternalTaskSensor( - task_id="wait_for_checks__fail_telemetry_derived__clients_last_seen__v2", + wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_telemetry_derived__clients_last_seen__v1", external_dag_id="bqetl_main_summary", - external_task_id="checks__fail_telemetry_derived__clients_last_seen__v2", - 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_checks__fail_fenix_derived__firefox_android_clients__v1 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_analytics_tables", - external_task_id="checks__fail_fenix_derived__firefox_android_clients__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_fenix_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_fenix_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.fenix_derived__metrics_clients_last_seen__v1", + external_task_id="telemetry_derived__clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -190,22 +178,10 @@ 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", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", + wait_for_fenix_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_fenix_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", + external_task_id="fenix.fenix_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -214,10 +190,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", + 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", @@ -226,10 +202,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_firefox_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_firefox_ios_derived__metrics_clients_last_seen__v1", + 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", - external_task_id="firefox_ios.firefox_ios_derived__metrics_clients_last_seen__v1", + external_task_id="firefox_ios.firefox_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -238,46 +214,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", + wait_for_focus_android_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_focus_android_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__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_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__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_focus_android_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_focus_android_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.focus_android_derived__metrics_clients_last_seen__v1", + external_task_id="focus_android.focus_android_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -298,10 +238,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", + wait_for_focus_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_focus_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", + external_task_id="focus_ios.focus_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -310,58 +250,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_focus_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_focus_ios_derived__metrics_clients_last_seen__v1", + wait_for_klar_ios_derived__clients_last_seen_joined__v1 = ExternalTaskSensor( + task_id="wait_for_klar_ios_derived__clients_last_seen_joined__v1", external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.focus_ios_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_android.checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__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_klar_android_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_klar_android_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_android.klar_android_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_ios.checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__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_klar_ios_derived__metrics_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_klar_ios_derived__metrics_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="klar_ios.klar_ios_derived__metrics_clients_last_seen__v1", + external_task_id="klar_ios.klar_ios_derived__clients_last_seen_joined__v1", execution_delta=datetime.timedelta(seconds=8100), check_existence=True, mode="reschedule", @@ -396,21 +288,9 @@ ) ) - wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_telemetry_derived__clients_last_seen__v1", - external_dag_id="bqetl_main_summary", - external_task_id="telemetry_derived__clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=8100), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - active_users_aggregates_device_v1 = bigquery_etl_query( - task_id="active_users_aggregates_device_v1", - destination_table="active_users_aggregates_device_v1", + active_users_aggregates_attribution_v1 = bigquery_etl_query( + task_id="active_users_aggregates_attribution_v1", + destination_table="active_users_aggregates_attribution_v1", dataset_id="telemetry_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -423,72 +303,54 @@ depends_on_past=False, ) - checks__fail_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_fenix_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="fenix_derived", + active_users_aggregates_device_v1 = bigquery_etl_query( + task_id="active_users_aggregates_device_v1", + destination_table="active_users_aggregates_device_v1", + dataset_id="telemetry_derived", project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, owner="lvargas@mozilla.com", email=[ "gkaberere@mozilla.com", "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, ) with TaskGroup( - "checks__fail_fenix_derived__active_users_aggregates__v3_external", - ) as checks__fail_fenix_derived__active_users_aggregates__v3_external: + "active_users_aggregates_device_v1_external", + ) as active_users_aggregates_device_v1_external: ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", + task_id="bqetl_search_dashboard__wait_for_active_users_aggregates_device_v1", external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_fenix_derived__active_users_aggregates__v3", + external_task_id="wait_for_active_users_aggregates_device_v1", execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", ) - checks__fail_fenix_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_fenix_derived__active_users_aggregates__v3 + active_users_aggregates_device_v1_external.set_upstream( + active_users_aggregates_device_v1 ) - checks__fail_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_firefox_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="firefox_ios_derived", + active_users_aggregates_v1 = bigquery_etl_query( + task_id="active_users_aggregates_v1", + destination_table="active_users_aggregates_v1", + dataset_id="telemetry_derived", project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, owner="lvargas@mozilla.com", email=[ "gkaberere@mozilla.com", "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, - ) - - with TaskGroup( - "checks__fail_firefox_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_firefox_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_firefox_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_firefox_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_firefox_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_firefox_ios_derived__active_users_aggregates__v3 ) - checks__fail_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_focus_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="focus_android_derived", + checks__fail_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_fenix_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, owner="lvargas@mozilla.com", @@ -498,28 +360,14 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_focus_android_derived__active_users_aggregates__v3_external", - ) as checks__fail_focus_android_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_focus_android_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_focus_android_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_focus_android_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_focus_android_derived__active_users_aggregates__v3 - ) - - checks__fail_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_focus_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="focus_ios_derived", + checks__fail_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_firefox_ios_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, owner="lvargas@mozilla.com", @@ -529,28 +377,14 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_focus_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_focus_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_focus_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_focus_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_focus_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_focus_ios_derived__active_users_aggregates__v3 - ) - - checks__fail_klar_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__fail_klar_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", + checks__fail_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_focus_android_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, owner="lvargas@mozilla.com", @@ -560,27 +394,30 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_klar_android_derived__active_users_aggregates__v3_external", - ) as checks__fail_klar_android_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_klar_android_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_klar_android_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_klar_android_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_klar_android_derived__active_users_aggregates__v3 + checks__fail_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( + task_id="checks__fail_focus_ios_derived__active_users_aggregates__v3", + source_table="active_users_aggregates_v3", + dataset_id="focus_ios_derived", + project_id="moz-fx-data-shared-prod", + is_dq_check_fail=True, + owner="lvargas@mozilla.com", + email=[ + "gkaberere@mozilla.com", + "lvargas@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + depends_on_past=False, + parameters=["submission_date:DATE:{{ds}}"], + retries=0, ) checks__fail_klar_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__fail_klar_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=True, @@ -591,27 +428,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) - with TaskGroup( - "checks__fail_klar_ios_derived__active_users_aggregates__v3_external", - ) as checks__fail_klar_ios_derived__active_users_aggregates__v3_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_checks__fail_klar_ios_derived__active_users_aggregates__v3", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_checks__fail_klar_ios_derived__active_users_aggregates__v3", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - checks__fail_klar_ios_derived__active_users_aggregates__v3_external.set_upstream( - checks__fail_klar_ios_derived__active_users_aggregates__v3 - ) - checks__warn_fenix_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_fenix_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -622,7 +445,7 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) @@ -647,7 +470,7 @@ checks__warn_firefox_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_firefox_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -658,13 +481,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_focus_android_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_focus_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -675,13 +498,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_focus_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_focus_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -692,30 +515,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - retries=0, - ) - - checks__warn_klar_android_derived__active_users_aggregates__v3 = bigquery_dq_check( - task_id="checks__warn_klar_android_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=False, - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) checks__warn_klar_ios_derived__active_users_aggregates__v3 = bigquery_dq_check( task_id="checks__warn_klar_ios_derived__active_users_aggregates__v3", - source_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + source_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", is_dq_check_fail=False, @@ -726,13 +532,13 @@ "telemetry-alerts@mozilla.com", ], depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], + parameters=["submission_date:DATE:{{ds}}"], retries=0, ) fenix_active_users_aggregates = bigquery_etl_query( task_id="fenix_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="fenix_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -741,9 +547,8 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) firefox_desktop_active_users_aggregates = bigquery_etl_query( @@ -761,23 +566,9 @@ depends_on_past=False, ) - with TaskGroup( - "firefox_desktop_active_users_aggregates_external", - ) as firefox_desktop_active_users_aggregates_external: - ExternalTaskMarker( - task_id="bqetl_search_dashboard__wait_for_firefox_desktop_active_users_aggregates", - external_dag_id="bqetl_search_dashboard", - external_task_id="wait_for_firefox_desktop_active_users_aggregates", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=85500)).isoformat() }}", - ) - - firefox_desktop_active_users_aggregates_external.set_upstream( - firefox_desktop_active_users_aggregates - ) - firefox_ios_active_users_aggregates = bigquery_etl_query( task_id="firefox_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="firefox_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -786,14 +577,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) focus_android_active_users_aggregates = bigquery_etl_query( task_id="focus_android_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="focus_android_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -802,14 +592,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) focus_ios_active_users_aggregates = bigquery_etl_query( task_id="focus_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="focus_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -818,30 +607,13 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, - depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], - ) - - klar_android_active_users_aggregates = bigquery_etl_query( - task_id="klar_android_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="klar_android_derived", - project_id="moz-fx-data-shared-prod", - owner="lvargas@mozilla.com", - email=[ - "gkaberere@mozilla.com", - "lvargas@mozilla.com", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) klar_ios_active_users_aggregates = bigquery_etl_query( task_id="klar_ios_active_users_aggregates", - destination_table='active_users_aggregates_v3${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', + destination_table="active_users_aggregates_v3", dataset_id="klar_ios_derived", project_id="moz-fx-data-shared-prod", owner="lvargas@mozilla.com", @@ -850,9 +622,8 @@ "lvargas@mozilla.com", "telemetry-alerts@mozilla.com", ], - date_partition_parameter=None, + date_partition_parameter="submission_date", depends_on_past=False, - parameters=["submission_date:DATE:{{macros.ds_add(ds, -1)}}"], ) telemetry_derived__cohort_daily_statistics__v1 = bigquery_etl_query( @@ -887,10 +658,22 @@ depends_on_past=False, ) + active_users_aggregates_attribution_v1.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 + ) + + active_users_aggregates_attribution_v1.set_upstream( + wait_for_checks__fail_telemetry_derived__unified_metrics__v1 + ) + active_users_aggregates_device_v1.set_upstream( wait_for_checks__fail_telemetry_derived__unified_metrics__v1 ) + active_users_aggregates_v1.set_upstream( + wait_for_checks__fail_telemetry_derived__unified_metrics__v1 + ) + checks__fail_fenix_derived__active_users_aggregates__v3.set_upstream( wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 ) @@ -927,10 +710,6 @@ focus_ios_active_users_aggregates ) - checks__fail_klar_android_derived__active_users_aggregates__v3.set_upstream( - klar_android_active_users_aggregates - ) - checks__fail_klar_ios_derived__active_users_aggregates__v3.set_upstream( klar_ios_active_users_aggregates ) @@ -960,11 +739,11 @@ ) checks__warn_firefox_desktop_derived__active_users_aggregates__v3.set_upstream( - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 + firefox_desktop_active_users_aggregates ) checks__warn_firefox_desktop_derived__active_users_aggregates__v3.set_upstream( - firefox_desktop_active_users_aggregates + wait_for_telemetry_derived__clients_last_seen__v1 ) checks__warn_firefox_ios_derived__active_users_aggregates__v3.set_upstream( @@ -979,10 +758,6 @@ focus_ios_active_users_aggregates ) - checks__warn_klar_android_derived__active_users_aggregates__v3.set_upstream( - klar_android_active_users_aggregates - ) - checks__warn_klar_ios_derived__active_users_aggregates__v3.set_upstream( klar_ios_active_users_aggregates ) @@ -992,99 +767,95 @@ ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 - ) - - fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1 - ) - - fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1 + wait_for_fenix_derived__clients_last_seen_joined__v1 ) fenix_active_users_aggregates.set_upstream( - wait_for_fenix_derived__metrics_clients_last_seen__v1 + 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 + wait_for_telemetry_derived__clients_last_seen__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 + wait_for_firefox_ios_derived__clients_last_seen_joined__v1 ) firefox_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) - firefox_ios_active_users_aggregates.set_upstream( - wait_for_firefox_ios_derived__metrics_clients_last_seen__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_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) focus_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 + wait_for_telemetry_derived__core_clients_last_seen__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_focus_android_derived__metrics_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) - focus_android_active_users_aggregates.set_upstream( - wait_for_telemetry_derived__core_clients_last_seen__v1 + focus_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) focus_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 + wait_for_focus_ios_derived__clients_last_seen_joined__v1 ) focus_ios_active_users_aggregates.set_upstream( - wait_for_focus_ios_derived__metrics_clients_last_seen__v1 + 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 ) - klar_android_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 ) - klar_android_active_users_aggregates.set_upstream( - wait_for_klar_android_derived__metrics_clients_last_seen__v1 + klar_ios_active_users_aggregates.set_upstream( + wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) klar_ios_active_users_aggregates.set_upstream( - wait_for_checks__fail_org_mozilla_ios_klar_derived__baseline_clients_last_seen__v1 + wait_for_klar_ios_derived__clients_last_seen_joined__v1 ) klar_ios_active_users_aggregates.set_upstream( - wait_for_klar_ios_derived__metrics_clients_last_seen__v1 + wait_for_search_derived__mobile_search_clients_daily__v1 ) telemetry_derived__cohort_daily_statistics__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py --- /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py 2024-06-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py 2024-06-21 18:48:46.000000000 +0000 @@ -211,10 +211,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 = ExternalTaskSensor( - task_id="wait_for_checks__fail_telemetry_derived__clients_last_seen__v2", + wait_for_telemetry_derived__clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_telemetry_derived__clients_last_seen__v1", external_dag_id="bqetl_main_summary", - external_task_id="checks__fail_telemetry_derived__clients_last_seen__v2", + external_task_id="telemetry_derived__clients_last_seen__v1", check_existence=True, mode="reschedule", allowed_states=ALLOWED_STATES, @@ -265,13 +265,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_kpi_metrics__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_mobile_kpi_metrics", - external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_mobile_feature_usage__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", external_dag_id="bqetl_mobile_feature_usage", external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", @@ -291,6 +284,13 @@ external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", ) + ExternalTaskMarker( + task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + external_dag_id="bqetl_mobile_engagement_model", + external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", + ) + checks__fail_fenix_derived__firefox_android_clients__v1_external.set_upstream( checks__fail_fenix_derived__firefox_android_clients__v1 ) @@ -324,13 +324,6 @@ ) ExternalTaskMarker( - task_id="bqetl_desktop_conv_evnt_categorization__wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", - external_dag_id="bqetl_desktop_conv_evnt_categorization", - external_task_id="wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_mozilla_org_derived__wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", external_dag_id="bqetl_mozilla_org_derived", external_task_id="wait_for_checks__fail_telemetry_derived__clients_first_seen__v2", @@ -693,5 +686,5 @@ ) telemetry_derived__clients_first_seen_28_days_later__v1.set_upstream( - wait_for_checks__fail_telemetry_derived__clients_last_seen__v2 + wait_for_telemetry_derived__clients_last_seen__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-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_braze.py 2024-06-21 18:48:47.000000000 +0000 @@ -50,11 +50,59 @@ with DAG( "bqetl_braze", default_args=default_args, - schedule_interval="0 5,13,21 * * *", + schedule_interval="0 10 * * *", doc_md=docs, 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", + execution_delta=datetime.timedelta(seconds=29700), + 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", + execution_delta=datetime.timedelta(seconds=25200), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + + wait_for_acoustic_external__contact_raw__v1 = ExternalTaskSensor( + task_id="wait_for_acoustic_external__contact_raw__v1", + external_dag_id="bqetl_acoustic_contact_export", + external_task_id="acoustic_external__contact_raw__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_acoustic_external__raw_recipient_raw__v1 = ExternalTaskSensor( + task_id="wait_for_acoustic_external__raw_recipient_raw__v1", + external_dag_id="bqetl_acoustic_raw_recipient_export", + external_task_id="acoustic_external__raw_recipient_raw__v1", + execution_delta=datetime.timedelta(seconds=3600), + 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", @@ -103,6 +151,18 @@ sql_file_path="sql/moz-fx-data-shared-prod/braze_derived/subscriptions_map_v1/script.sql", ) + braze_derived__suppressions__v1 = bigquery_etl_query( + task_id="braze_derived__suppressions__v1", + destination_table="suppressions_v1", + dataset_id="braze_derived", + 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_derived__user_profiles__v1 = bigquery_etl_query( task_id="braze_derived__user_profiles__v1", destination_table="user_profiles_v1", @@ -149,7 +209,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_newsletters_sync__v1 = bigquery_etl_query( @@ -162,7 +221,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_products_sync__v1 = bigquery_etl_query( @@ -175,7 +233,18 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], + ) + + 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( @@ -200,7 +269,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__changed_waitlists_sync__v1 = bigquery_etl_query( @@ -213,7 +281,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__delete_users_sync__v1 = bigquery_etl_query( @@ -226,7 +293,6 @@ date_partition_parameter=None, depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], ) braze_external__users_previous_day_snapshot__v1 = bigquery_etl_query( @@ -241,6 +307,18 @@ sql_file_path="sql/moz-fx-data-shared-prod/braze_external/users_previous_day_snapshot_v1/script.sql", ) + braze_external__users_previous_day_snapshot__v2 = bigquery_etl_query( + task_id="braze_external__users_previous_day_snapshot__v2", + destination_table=None, + 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, + sql_file_path="sql/moz-fx-data-shared-prod/braze_external/users_previous_day_snapshot_v2/script.sql", + ) + checks__fail_braze_derived__newsletters__v1 = bigquery_dq_check( task_id="checks__fail_braze_derived__newsletters__v1", source_table="newsletters_v1", @@ -332,9 +410,9 @@ retries=0, ) - checks__fail_braze_external__users_previous_day_snapshot__v1 = bigquery_dq_check( - task_id="checks__fail_braze_external__users_previous_day_snapshot__v1", - source_table="users_previous_day_snapshot_v1", + 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, @@ -355,7 +433,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -369,7 +446,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -383,7 +459,19 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], + 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, ) @@ -410,7 +498,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -424,7 +511,6 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) @@ -438,12 +524,17 @@ email=["cbeck@mozilla.com", "leli@mozilla.com"], depends_on_past=False, task_concurrency=1, - arguments=["--append_table", "--noreplace"], retries=0, ) braze_derived__newsletters__v1.set_upstream(checks__fail_braze_derived__users__v1) + braze_derived__products__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 ) @@ -452,6 +543,10 @@ checks__fail_braze_derived__user_profiles__v1 ) + braze_derived__suppressions__v1.set_upstream( + wait_for_checks__fail_marketing_suppression_list_derived__main_suppression_list__v1 + ) + braze_derived__user_profiles__v1.set_upstream( checks__fail_braze_derived__newsletters__v1 ) @@ -466,10 +561,24 @@ checks__fail_braze_derived__waitlists__v1 ) + braze_derived__users__v1.set_upstream(wait_for_acoustic_external__contact_raw__v1) + + braze_derived__users__v1.set_upstream( + wait_for_acoustic_external__raw_recipient_raw__v1 + ) + + braze_derived__users__v1.set_upstream( + checks__fail_braze_derived__subscriptions_map__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( @@ -480,12 +589,16 @@ 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( - checks__fail_braze_derived__users__v1 + braze_external__users_previous_day_snapshot__v2 ) braze_external__changed_users__v1.set_upstream( - checks__fail_braze_external__users_previous_day_snapshot__v1 + checks__fail_braze_derived__users__v1 ) braze_external__changed_users_sync__v1.set_upstream( @@ -522,8 +635,12 @@ checks__fail_braze_derived__waitlists__v1.set_upstream(braze_derived__waitlists__v1) - checks__fail_braze_external__users_previous_day_snapshot__v1.set_upstream( - braze_external__users_previous_day_snapshot__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( @@ -538,6 +655,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_census_feed.py /tmp/workspace/generated-sql/dags/bqetl_census_feed.py --- /tmp/workspace/main-generated-sql/dags/bqetl_census_feed.py 2024-06-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_census_feed.py 1970-01-01 00:00:00.000000000 +0000 @@ -1,82 +0,0 @@ -# Generated via https://github.com/mozilla/bigquery-etl/blob/main/bigquery_etl/query_scheduling/generate_airflow_dags.py - -from airflow import DAG -from airflow.sensors.external_task import ExternalTaskMarker -from airflow.sensors.external_task import ExternalTaskSensor -from airflow.utils.task_group import TaskGroup -import datetime -from operators.gcp_container_operator import GKEPodOperator -from utils.constants import ALLOWED_STATES, FAILED_STATES -from utils.gcp import bigquery_etl_query, bigquery_dq_check - -docs = """ -### bqetl_census_feed - -Built from bigquery-etl repo, [`dags/bqetl_census_feed.py`](https://github.com/mozilla/bigquery-etl/blob/generated-sql/dags/bqetl_census_feed.py) - -#### Description - -Loads the desktop conversion event tables -#### Owner - -kwindau@mozilla.com - -#### Tags - -* impact/tier_2 -* repo/bigquery-etl -""" - - -default_args = { - "owner": "kwindau@mozilla.com", - "start_date": datetime.datetime(2024, 6, 10, 0, 0), - "end_date": None, - "email": ["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - "depends_on_past": False, - "retry_delay": datetime.timedelta(seconds=1800), - "email_on_failure": True, - "email_on_retry": False, - "retries": 2, -} - -tags = ["impact/tier_2", "repo/bigquery-etl"] - -with DAG( - "bqetl_census_feed", - default_args=default_args, - schedule_interval="0 17 * * *", - doc_md=docs, - tags=tags, -) as dag: - - wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2", - external_dag_id="bqetl_google_analytics_derived_ga4", - external_task_id="checks__fail_mozilla_org_derived__gclid_conversions__v2", - execution_delta=datetime.timedelta(seconds=18000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - ) - - mozilla_org_derived__ga_desktop_conversions__v1 = bigquery_etl_query( - task_id="mozilla_org_derived__ga_desktop_conversions__v1", - destination_table='ga_desktop_conversions_v1${{ macros.ds_format(macros.ds_add(ds, -2), "%Y-%m-%d", "%Y%m%d") }}', - dataset_id="mozilla_org_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - parameters=["activity_date:DATE:{{macros.ds_add(ds, -2)}}"] - + ["submission_date:DATE:{{ds}}"], - ) - - mozilla_org_derived__ga_desktop_conversions__v1.set_upstream( - wait_for_checks__fail_mozilla_org_derived__gclid_conversions__v2 - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_cloudflare_browser_market_share.py /tmp/workspace/generated-sql/dags/bqetl_cloudflare_browser_market_share.py --- /tmp/workspace/main-generated-sql/dags/bqetl_cloudflare_browser_market_share.py 2024-06-21 18:35:52.000000000 +0000 +++ /tmp/workspace/generated ```

⚠️ Only part of the diff is displayed.

Link to full diff

[dzeber]

Moved to https://github.com/mozilla/private-bigquery-etl/pull/426 to address CI failure