1786526 - Count of new profiles in August 15, 2022 returns zero in telemetry_derived datasets.

Status: RESOLVED WORKSFORME

(Data Platform and Tools :: General, defect)

Description

[Lucia Vargas [:lucia-vargas-a]]

Attachment: new_profiles.png

User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:105.0) Gecko/20100101 Firefox/105.0

Steps to reproduce:

Check the New profiles for Desktop on Looker https://mozilla.cloud.looker.com/looks/540.

Actual results:

On Aug 15, 2022 a backfill for clients_first_seen was run and for its downstream dependencies on August 16th. These datasets are missing data for that date, Aug 15, 2022.

Expected results:

Data was backfilled for August 15, 2022 and for its downstream dependencies on the next day, via Airflow.

Comment 1

[Lucia Vargas [:lucia-vargas-a]]

Commands to run:

./bqetl query backfill telemetry_derived.clients_first_seen_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.clients_last_seen_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query telemetry_derived.clients_daily_joined_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query telemetry_derived.clients_last_seen_joined_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.unified_metrics_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_attribution_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_device_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_device_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.rolling_cohorts_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0
./bqetl query backfill telemetry_derived.cohort_daily_statistics_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-15 -n 0

Comment 2

[Anna Scholtz [:ascholtz]]

We'll need to adjust the timeframes to -s 2020-08-15 -e 2022-08-23. Queries like clients_first_seen_v1 depend on the results of the previous day to determine the most recent first_seen dates, so with 2020-08-15 missing all the days after might have incorrect data.

Comment 3

[BugBot [:suhaib / :marco/ :calixte]]

The Bugbug bot thinks this bug should belong to the 'Core::Graphics: WebRender' component, and is moving the bug to that component. Please correct in case you think the bot is wrong.

Comment 4

[Lucia Vargas [:lucia-vargas-a]]

Updated commands to run, one by one and in the listed order:

./bqetl query backfill telemetry_derived.clients_first_seen_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.clients_last_seen_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query telemetry_derived.clients_daily_joined_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query telemetry_derived.clients_last_seen_joined_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.unified_metrics_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_attribution_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_device_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_device_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.rolling_cohorts_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.cohort_daily_statistics_v1 --project_id=moz-fx-data-shared-prod -s 2020-08-15 -e 2022-08-23 -n 0

Comment 5

[Anna Scholtz [:ascholtz]]

lgtm

Comment 6

[Anna Scholtz [:ascholtz]]

ah wait 2022-08-15 should be the date not 2020

Comment 7

[Lucia Vargas [:lucia-vargas-a]]

Ah, thanks for the good spotting,

./bqetl query backfill telemetry_derived.clients_first_seen_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.clients_last_seen_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query telemetry_derived.clients_daily_joined_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query telemetry_derived.clients_last_seen_joined_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.unified_metrics_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_attribution_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_device_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.active_users_aggregates_device_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.rolling_cohorts_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0
./bqetl query backfill telemetry_derived.cohort_daily_statistics_v1 --project_id=moz-fx-data-shared-prod -s 2022-08-15 -e 2022-08-23 -n 0

Comment 8

[Lucia Vargas [:lucia-vargas-a]]

clients_first_seen_v1 could not be updated directly using a backfill command, with Error in query string: Query parameter 'submission_date' not found at [9:23]. The DAG has a comment on the requirement to provide the submission_date as a generic parameter.
For this reason, the tables (clients_first_seen_v1, clients_last_seen_v1, clients_daily_joined_v1, clients_last_seen_joined_v1) were updated via Airflow by clearing the DAG for all days since 2022-08-15 until 2022-08-24.

Comment 9

[Lucia Vargas [:lucia-vargas-a]]

Completed, for all the other tables used the commands above.
Note. The command for telemetry_derived.active_users_aggregates_device_v1 is repeated but it was only triggered once.