[Bug 1903788] Update checks for reference_browser_derived.baseline_clients_last_seen_v1

[scholtzan]

This app is sending very little data, some days none at all. I updated some of the tests (like min row count), so they don't go off constantly

Checklist for reviewer:

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

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

• [ ] Follow the change control procedure

┆Issue is synchronized with this Jira Task

[dataops-ci-bot]

Integration report for "[Bug 1903788] Update checks for reference_browser_derived.baseline_clients_last_seen_v1"

sql.diff

Click to expand!

```diff Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived: baseline_clients_last_seen_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 21:06:36.000000000 +0000 @@ -0,0 +1,35 @@ +-- Overrides bigquery_etl.glean_usage due to this app having very limited data, sometimes none + +#warn +{{ is_unique(["client_id"], where="submission_date = @submission_date") }} + +#warn +{{ not_null([ + "submission_date", + "client_id", + "sample_id", + "first_seen_date", + "days_seen_bits", + "days_active_bits", + "days_created_profile_bits", + "days_seen_session_start_bits", + "days_seen_session_end_bits" + ], where="submission_date = @submission_date") }} + +#warn +SELECT + IF( + COUNTIF(normalized_channel NOT IN ("nightly", "aurora", "release", "Other", "beta", "esr")) > 0, + ERROR("Unexpected values for field normalized_channel detected."), + NULL + ) +FROM + `{{ project_id }}.{{ dataset_id }}.{{ table_name }}` +WHERE + submission_date = @submission_date; + +#warn +{{ matches_pattern(column="country", pattern="^[A-Z]{2}$", where="submission_date = @submission_date") }} + +#warn +{{ value_length(column="client_id", expected_length=36, where="submission_date = @submission_date") }} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/reference_browser_derived/baseline_clients_last_seen_v1/metadata.yaml 2024-06-25 21:08:34.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Baseline Clients Last Seen V1 +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + checks.sql: + - .. ```

Link to full diff

[scholtzan]

Ah yes, you're right!

[dataops-ci-bot]

Integration report for "Merge branch 'main' into override-reference-browser-checks"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py --- /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py 2024-06-25 21:34:07.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py 2024-06-25 21:35:01.000000000 +0000 @@ -1224,42 +1224,6 @@ checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( - task_id="checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - source_table="baseline_clients_last_seen_v1", - dataset_id="org_mozilla_reference_browser_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="ascholtz@mozilla.com", - email=["ascholtz@mozilla.com", "telemetry-alerts@mozilla.com"], - depends_on_past=False, - parameters=["submission_date:DATE:{{ds}}"], - retries=0, - task_group=task_group_reference_browser, - ) - - with TaskGroup( - "checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", - parent_group=task_group_reference_browser, - ) as checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: - ExternalTaskMarker( - task_id="bqetl_nondesktop__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_nondesktop", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - ExternalTaskMarker( - task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_gud", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( task_id="checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1", source_table="baseline_clients_last_seen_v1", @@ -4808,6 +4772,28 @@ task_group=task_group_reference_browser, ) + with TaskGroup( + "org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", + parent_group=task_group_reference_browser, + ) as org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: + ExternalTaskMarker( + task_id="bqetl_nondesktop__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_nondesktop", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_gud__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_gud", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + ) + org_mozilla_reference_browser_derived__events_stream__v1 = bigquery_etl_query( task_id="org_mozilla_reference_browser_derived__events_stream__v1", destination_table="events_stream_v1", @@ -5466,18 +5452,6 @@ org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1.set_upstream( checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 ) @@ -5751,14 +5725,6 @@ ) checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) @@ -6999,7 +6965,7 @@ pine_derived__metrics_clients_daily__v1.set_upstream(wait_for_copy_deduplicate_all) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_gud.py /tmp/workspace/generated-sql/dags/bqetl_gud.py --- /tmp/workspace/main-generated-sql/dags/bqetl_gud.py 2024-06-25 21:34:07.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_gud.py 2024-06-25 21:34:56.000000000 +0000 @@ -206,10 +206,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -218,10 +218,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -230,10 +230,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -431,15 +431,15 @@ ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py --- /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py 2024-06-25 21:34:07.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py 2024-06-25 21:34:54.000000000 +0000 @@ -191,10 +191,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -203,10 +203,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -215,10 +215,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -333,15 +333,15 @@ ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( @@ -397,15 +397,15 @@ ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 21:31:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 21:30:16.000000000 +0000 @@ -1,12 +1,9 @@ --- Generated via bigquery_etl.glean_usage +-- Overrides bigquery_etl.glean_usage due to this app having very limited data, sometimes none #warn {{ is_unique(["client_id"], where="submission_date = @submission_date") }} #warn -{{ min_row_count(1, where="submission_date = @submission_date") }} - -#warn {{ not_null([ "submission_date", "client_id", @@ -36,53 +33,3 @@ #warn {{ value_length(column="client_id", expected_length=36, where="submission_date = @submission_date") }} - -#fail -WITH daily AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_daily_v1` - WHERE - submission_date = @submission_date - AND sample_id IS NOT NULL - GROUP BY - submission_date -), -last_seen AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_last_seen_v1` - WHERE - submission_date = @submission_date - AND mozfun.bits28.days_since_seen(days_seen_bits) = 0 - GROUP BY - submission_date -), -check_results AS ( - SELECT - 1 - (last_seen.client_count / daily.client_count) AS difference_perc - FROM - daily - LEFT JOIN - last_seen - USING (submission_date) -) -SELECT - IF( - ABS((SELECT difference_perc FROM check_results)) > 0.001, - ERROR( - CONCAT( - "Results don't match by > 1%, baseline_clients_daily table has ", - STRING(((SELECT submission_date FROM daily))), - ": ", - ABS((SELECT client_count FROM daily)), - ". baseline_clients_last_seen has ", - IFNULL(((SELECT client_count FROM last_seen)), 0) - ) - ), - NULL - ); ```

Link to full diff

[dataops-ci-bot]

Integration report for "[Bug 1903788] Update checks for reference_browser_derived.baseline_clients_last_seen_v1"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py --- /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py 2024-06-25 22:17:31.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py 2024-06-25 22:18:19.000000000 +0000 @@ -1224,42 +1224,6 @@ checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( - task_id="checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - source_table="baseline_clients_last_seen_v1", - dataset_id="org_mozilla_reference_browser_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="ascholtz@mozilla.com", - email=["ascholtz@mozilla.com", "telemetry-alerts@mozilla.com"], - depends_on_past=False, - parameters=["submission_date:DATE:{{ds}}"], - retries=0, - task_group=task_group_reference_browser, - ) - - with TaskGroup( - "checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", - parent_group=task_group_reference_browser, - ) as checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: - ExternalTaskMarker( - task_id="bqetl_nondesktop__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_nondesktop", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - ExternalTaskMarker( - task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_gud", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( task_id="checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1", source_table="baseline_clients_last_seen_v1", @@ -4808,6 +4772,28 @@ task_group=task_group_reference_browser, ) + with TaskGroup( + "org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", + parent_group=task_group_reference_browser, + ) as org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: + ExternalTaskMarker( + task_id="bqetl_nondesktop__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_nondesktop", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_gud__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_gud", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + ) + org_mozilla_reference_browser_derived__events_stream__v1 = bigquery_etl_query( task_id="org_mozilla_reference_browser_derived__events_stream__v1", destination_table="events_stream_v1", @@ -5466,18 +5452,6 @@ org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1.set_upstream( checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 ) @@ -5751,14 +5725,6 @@ ) checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) @@ -6999,7 +6965,7 @@ pine_derived__metrics_clients_daily__v1.set_upstream(wait_for_copy_deduplicate_all) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_gud.py /tmp/workspace/generated-sql/dags/bqetl_gud.py --- /tmp/workspace/main-generated-sql/dags/bqetl_gud.py 2024-06-25 22:17:31.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_gud.py 2024-06-25 22:18:14.000000000 +0000 @@ -206,10 +206,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -218,10 +218,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -230,10 +230,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -431,15 +431,15 @@ ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py --- /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py 2024-06-25 22:17:31.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py 2024-06-25 22:18:13.000000000 +0000 @@ -191,10 +191,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -203,10 +203,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -215,10 +215,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -333,15 +333,15 @@ ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( @@ -397,15 +397,15 @@ ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 22:14:05.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 22:13:27.000000000 +0000 @@ -1,12 +1,9 @@ --- Generated via bigquery_etl.glean_usage +-- Overrides bigquery_etl.glean_usage due to this app having very limited data, sometimes none #warn {{ is_unique(["client_id"], where="submission_date = @submission_date") }} #warn -{{ min_row_count(1, where="submission_date = @submission_date") }} - -#warn {{ not_null([ "submission_date", "client_id", @@ -32,57 +29,4 @@ submission_date = @submission_date; #warn -{{ matches_pattern(column="country", pattern="^[A-Z]{2}$", where="submission_date = @submission_date") }} - -#warn {{ value_length(column="client_id", expected_length=36, where="submission_date = @submission_date") }} - -#fail -WITH daily AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_daily_v1` - WHERE - submission_date = @submission_date - AND sample_id IS NOT NULL - GROUP BY - submission_date -), -last_seen AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_last_seen_v1` - WHERE - submission_date = @submission_date - AND mozfun.bits28.days_since_seen(days_seen_bits) = 0 - GROUP BY - submission_date -), -check_results AS ( - SELECT - 1 - (last_seen.client_count / daily.client_count) AS difference_perc - FROM - daily - LEFT JOIN - last_seen - USING (submission_date) -) -SELECT - IF( - ABS((SELECT difference_perc FROM check_results)) > 0.001, - ERROR( - CONCAT( - "Results don't match by > 1%, baseline_clients_daily table has ", - STRING(((SELECT submission_date FROM daily))), - ": ", - ABS((SELECT client_count FROM daily)), - ". baseline_clients_last_seen has ", - IFNULL(((SELECT client_count FROM last_seen)), 0) - ) - ), - NULL - ); diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/schema.yaml 2024-06-25 22:13:30.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/search_clients_daily_v8/schema.yaml 2024-06-25 22:13:27.000000000 +0000 @@ -19,7 +19,7 @@ type: STRING - mode: NULLABLE name: has_adblocker_addon - type: BOOLEAN + type: STRING - mode: NULLABLE name: app_version type: STRING ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into override-reference-browser-checks"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py --- /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py 2024-06-25 23:11:08.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py 2024-06-25 23:13:18.000000000 +0000 @@ -1224,42 +1224,6 @@ checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( - task_id="checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - source_table="baseline_clients_last_seen_v1", - dataset_id="org_mozilla_reference_browser_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="ascholtz@mozilla.com", - email=["ascholtz@mozilla.com", "telemetry-alerts@mozilla.com"], - depends_on_past=False, - parameters=["submission_date:DATE:{{ds}}"], - retries=0, - task_group=task_group_reference_browser, - ) - - with TaskGroup( - "checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", - parent_group=task_group_reference_browser, - ) as checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: - ExternalTaskMarker( - task_id="bqetl_nondesktop__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_nondesktop", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - ExternalTaskMarker( - task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_gud", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( task_id="checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1", source_table="baseline_clients_last_seen_v1", @@ -4808,6 +4772,28 @@ task_group=task_group_reference_browser, ) + with TaskGroup( + "org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", + parent_group=task_group_reference_browser, + ) as org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: + ExternalTaskMarker( + task_id="bqetl_nondesktop__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_nondesktop", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_gud__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_gud", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + ) + org_mozilla_reference_browser_derived__events_stream__v1 = bigquery_etl_query( task_id="org_mozilla_reference_browser_derived__events_stream__v1", destination_table="events_stream_v1", @@ -5466,18 +5452,6 @@ org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1.set_upstream( checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 ) @@ -5751,14 +5725,6 @@ ) checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) @@ -6999,7 +6965,7 @@ pine_derived__metrics_clients_daily__v1.set_upstream(wait_for_copy_deduplicate_all) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_gud.py /tmp/workspace/generated-sql/dags/bqetl_gud.py --- /tmp/workspace/main-generated-sql/dags/bqetl_gud.py 2024-06-25 23:11:08.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_gud.py 2024-06-25 23:13:14.000000000 +0000 @@ -206,10 +206,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -218,10 +218,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -230,10 +230,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -431,15 +431,15 @@ ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py --- /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py 2024-06-25 23:11:08.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py 2024-06-25 23:13:11.000000000 +0000 @@ -191,10 +191,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -203,10 +203,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -215,10 +215,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -333,15 +333,15 @@ ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( @@ -397,15 +397,15 @@ ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 23:08:12.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-25 23:07:53.000000000 +0000 @@ -1,12 +1,9 @@ --- Generated via bigquery_etl.glean_usage +-- Overrides bigquery_etl.glean_usage due to this app having very limited data, sometimes none #warn {{ is_unique(["client_id"], where="submission_date = @submission_date") }} #warn -{{ min_row_count(1, where="submission_date = @submission_date") }} - -#warn {{ not_null([ "submission_date", "client_id", @@ -32,57 +29,4 @@ submission_date = @submission_date; #warn -{{ matches_pattern(column="country", pattern="^[A-Z]{2}$", where="submission_date = @submission_date") }} - -#warn {{ value_length(column="client_id", expected_length=36, where="submission_date = @submission_date") }} - -#fail -WITH daily AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_daily_v1` - WHERE - submission_date = @submission_date - AND sample_id IS NOT NULL - GROUP BY - submission_date -), -last_seen AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_last_seen_v1` - WHERE - submission_date = @submission_date - AND mozfun.bits28.days_since_seen(days_seen_bits) = 0 - GROUP BY - submission_date -), -check_results AS ( - SELECT - 1 - (last_seen.client_count / daily.client_count) AS difference_perc - FROM - daily - LEFT JOIN - last_seen - USING (submission_date) -) -SELECT - IF( - ABS((SELECT difference_perc FROM check_results)) > 0.001, - ERROR( - CONCAT( - "Results don't match by > 1%, baseline_clients_daily table has ", - STRING(((SELECT submission_date FROM daily))), - ": ", - ABS((SELECT client_count FROM daily)), - ". baseline_clients_last_seen has ", - IFNULL(((SELECT client_count FROM last_seen)), 0) - ) - ), - NULL - ); ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into override-reference-browser-checks"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py --- /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py 2024-06-26 15:47:47.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py 2024-06-26 15:50:19.000000000 +0000 @@ -1224,42 +1224,6 @@ checks__fail_org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( - task_id="checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - source_table="baseline_clients_last_seen_v1", - dataset_id="org_mozilla_reference_browser_derived", - project_id="moz-fx-data-shared-prod", - is_dq_check_fail=True, - owner="ascholtz@mozilla.com", - email=["ascholtz@mozilla.com", "telemetry-alerts@mozilla.com"], - depends_on_past=False, - parameters=["submission_date:DATE:{{ds}}"], - retries=0, - task_group=task_group_reference_browser, - ) - - with TaskGroup( - "checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", - parent_group=task_group_reference_browser, - ) as checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: - ExternalTaskMarker( - task_id="bqetl_nondesktop__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_nondesktop", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - ExternalTaskMarker( - task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_gud", - external_task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( task_id="checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1", source_table="baseline_clients_last_seen_v1", @@ -4808,6 +4772,28 @@ task_group=task_group_reference_browser, ) + with TaskGroup( + "org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external", + parent_group=task_group_reference_browser, + ) as org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external: + ExternalTaskMarker( + task_id="bqetl_nondesktop__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_nondesktop", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + ExternalTaskMarker( + task_id="bqetl_gud__wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_dag_id="bqetl_gud", + external_task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=82800)).isoformat() }}", + ) + + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1_external.set_upstream( + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + ) + org_mozilla_reference_browser_derived__events_stream__v1 = bigquery_etl_query( task_id="org_mozilla_reference_browser_derived__events_stream__v1", destination_table="events_stream_v1", @@ -5466,18 +5452,6 @@ org_mozilla_klar_derived__baseline_clients_last_seen__v1 ) - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_social_nightly_derived__baseline_clients_last_seen__v1.set_upstream( checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 ) @@ -5751,14 +5725,6 @@ ) checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( - org_mozilla_ios_firefox_derived__baseline_clients_daily__v1 - ) - - checks__warn_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1.set_upstream( org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) @@ -6999,7 +6965,7 @@ pine_derived__metrics_clients_daily__v1.set_upstream(wait_for_copy_deduplicate_all) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( - checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) reference_browser_derived__clients_last_seen_joined__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_gud.py /tmp/workspace/generated-sql/dags/bqetl_gud.py --- /tmp/workspace/main-generated-sql/dags/bqetl_gud.py 2024-06-26 15:47:47.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_gud.py 2024-06-26 15:50:13.000000000 +0000 @@ -206,10 +206,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -218,10 +218,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -230,10 +230,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -431,15 +431,15 @@ ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__smoot_usage_nondesktop__v2.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py --- /tmp/workspace/main-generated-sql/dags/bqetl_nondesktop.py 2024-06-26 15:47:47.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_nondesktop.py 2024-06-26 15:50:10.000000000 +0000 @@ -191,10 +191,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="reference_browser.checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", + external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -203,10 +203,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="firefox_reality.checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1", + external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -215,10 +215,10 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1", + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( + task_id="wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_glean_usage", - external_task_id="lockwise_android.mozilla_lockbox_derived__baseline_clients_last_seen__v1", + external_task_id="reference_browser.org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1", execution_delta=datetime.timedelta(seconds=3600), check_existence=True, mode="reschedule", @@ -333,15 +333,15 @@ ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_day_2_7_activation__v1.set_upstream( @@ -397,15 +397,15 @@ ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 + wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_checks__fail_org_mozilla_vrbrowser_derived__baseline_clients_last_seen__v1 + wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( - wait_for_mozilla_lockbox_derived__baseline_clients_last_seen__v1 + wait_for_org_mozilla_reference_browser_derived__baseline_clients_last_seen__v1 ) telemetry_derived__firefox_nondesktop_exact_mau28__v1.set_upstream( diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-26 15:44:38.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/org_mozilla_reference_browser_derived/baseline_clients_last_seen_v1/checks.sql 2024-06-26 15:44:09.000000000 +0000 @@ -1,12 +1,9 @@ --- Generated via bigquery_etl.glean_usage +-- Overrides bigquery_etl.glean_usage due to this app having very limited data, sometimes none #warn {{ is_unique(["client_id"], where="submission_date = @submission_date") }} #warn -{{ min_row_count(1, where="submission_date = @submission_date") }} - -#warn {{ not_null([ "submission_date", "client_id", @@ -32,57 +29,4 @@ submission_date = @submission_date; #warn -{{ matches_pattern(column="country", pattern="^[A-Z]{2}$", where="submission_date = @submission_date") }} - -#warn {{ value_length(column="client_id", expected_length=36, where="submission_date = @submission_date") }} - -#fail -WITH daily AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_daily_v1` - WHERE - submission_date = @submission_date - AND sample_id IS NOT NULL - GROUP BY - submission_date -), -last_seen AS ( - SELECT - submission_date, - COUNT(DISTINCT client_id) AS client_count - FROM - `moz-fx-data-shared-prod.org_mozilla_ios_firefox_derived.baseline_clients_last_seen_v1` - WHERE - submission_date = @submission_date - AND mozfun.bits28.days_since_seen(days_seen_bits) = 0 - GROUP BY - submission_date -), -check_results AS ( - SELECT - 1 - (last_seen.client_count / daily.client_count) AS difference_perc - FROM - daily - LEFT JOIN - last_seen - USING (submission_date) -) -SELECT - IF( - ABS((SELECT difference_perc FROM check_results)) > 0.001, - ERROR( - CONCAT( - "Results don't match by > 1%, baseline_clients_daily table has ", - STRING(((SELECT submission_date FROM daily))), - ": ", - ABS((SELECT client_count FROM daily)), - ". baseline_clients_last_seen has ", - IFNULL(((SELECT client_count FROM last_seen)), 0) - ) - ), - NULL - ); ```

Link to full diff