1932174 - Airflow task bqetl_unified.checks__warn_telemetry_derived__unified_metrics__v1 failed for exec_date 2024-11-19

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Ben Wu [:benwu]]

Airflow task bqetl_unified.checks__warn_telemetry_derived__unified_metrics__v1 failed for exec_date 2024-11-19

unified_metrics_v1 check is failing. This looks similar to bug 1899800

Task link:
https://workflow.telemetry.mozilla.org/dags/bqetl_unified/grid?task_id=checks__warn_telemetry_derived__unified_metrics__v1&tab=logs&dag_run_id=scheduled__2024-11-18T03%3A00%3A00%2B00%3A00

Log extract:

[2024-11-19, 04:56:24 UTC] {pod_manager.py:472} INFO - [base] Error in query string: Error processing job 'moz-fx-data-shared- prod:bqjob_r758dd2e8043331cc_0000019342c605f0_1': Columns with NULL values: first_seen_date
[2024-11-19, 04:56:26 UTC] {pod_manager.py:490} INFO - [base] Error in query string: Error processing job 'moz-fx-data-shared- prod:bqjob_rdabb31eec1b466e_0000019342c6a640_1': Columns with NULL values: days_since_first_seen, is_new_profile

Comment 1

[Katie Windau]

This is related to the fact that I was working on fixing an issue in the first seen dates table. Will work to correct this today

Comment 2

[Katie Windau]

This issue was caused by the fact that I swapped out clients_first_seen_v2 with a newly backfilled version of clients_first_seen_v2 on Monday 11/18 (this was to address a previously found issue where clients first seen had been run out of order in the past, causing some rows to have second seen dates before first seen dates).

After swapping clients first seen v2 with the corrected data on Monday afternoon, I then had run a merge statement also on Monday to update clients_last_seen_v2 with the first seen and second seen dates from the new table in clients last seen v2. However, the way I wrote the merge statement, if the client ID was now no longer in the newly backfilled clients first seen v2, it set the first seen date and second seen date to null in clients last seen v2. This is what then cascaded nulls into the unified_metrics_v1 desktop table. (Basically, things that had been shredded, now had null dates in clients last seen v2)

To resolve this - I have reset clients last seen v2 to a backup I had taken prior to my change on Monday (which only contained data through 11/17/24). I am now re-running bqetl_main_summary from the "telemetry_derived.clients_last_seen_v2" task forward, to load the latest data for 11/18 to telemetry_derived.clients_last_seen_v2. Once this is done, I will re-run bqetl_unified for submission date = 2024-11-18 to resolve the null issue this had caused that triggered the warning. Will update when done.

Comment 3

[Katie Windau]

I finished running bqetl_main_summary like I proposed in the above step, but then I did a QA check on the new data in clients_last_seen_v2 for 2024-11-18 and I realize it still has null first seen dates for 11/18/24, due to using the new version of clients_first_seen_v2 which now has some clients not in there but that are in clients_last_seen_v2.

So I now restored clients first seen v2 using the backup of it I took on Monday which only has data through 11/17, and am running clients_first_seen_v2 from bqetl_analytics_tables for 2024-11-18. When that is done, will re-run bqetl_main_summary for 2024-11-18 from clients_last_seen_v2 task forward, and then bqetl_unified to resolve the issue in unified metrics.

Comment 4

[Katie Windau]

This is now resolved