1880851 - Airflow task bqetl_firefox_ios.checks__fail_firefox_ios_derived__funnel_retention_clients_week_4__v1 failed for exec_date 2024-02-19

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Leli [:Leli]]

Airflow task bqetl_firefox_ios.checks__fail_firefox_ios_derived__funnel_retention_clients_week_4__v1 failed for exec_date 2024-02-19

Task link:
https://workflow.telemetry.mozilla.org/dags/bqetl_firefox_ios/grid?dag_run_id=scheduled__2024-02-18T04%3A00%3A00%2B00%3A00&task_id=checks__fail_firefox_ios_derived__funnel_retention_clients_week_4__v1

Log extract:

Error in query string: Error processing job 'moz-fx-data-shared- prod:bqjob_rc8e9d44ce279c87_0000018dbf8a2ddb_1': Duplicates detected (Expected combined set of values for columns ['client_id'] to be unique.)

Comment 1

[Krzysztof Ignasiak [:kik]]

Opened a PR to address this failure:
https://github.com/mozilla/bigquery-etl/pull/5067

Comment 2

[Krzysztof Ignasiak [:kik]]

After investigation this appears to be related to Shredder and the delay between the table being created and added to the Shredder configuration. This has been addressed by using the deletion requests to identify which clients should be removed from the following tables:

• firefox_ios_derived.firefox_ios_clients_v1
• firefox_ios_derived.funnel_retention_clients_week_2_v1
• firefox_ios_derived.funnel_retention_clients_week_4_v1

Query used to identify those clients:

WITH deletion_requests AS (
  SELECT
    DATE(submission_timestamp) AS request_date,
    client_info.client_id,
  FROM `moz-fx-data-shared-prod.firefox_ios.deletion_request`
  WHERE DATE(submission_timestamp) < CURRENT_DATE
),
clients_to_delete AS (
  SELECT target_table.client_id
  FROM `moz-fx-data-shared-prod.firefox_ios_derived.[TABLE_NAME]` AS target_table
  INNER JOIN deletion_requests
    ON deletion_requests.client_id = target_table.client_id
    AND deletion_requests.request_date <= target_table.first_seen_date
)

SELECT * 
FROM `moz-fx-data-shared-prod.firefox_ios_derived.[TABLE_NAME]`
WHERE client_id NOT IN (
  SELECT * FROM clients_to_delete
)

Comment 3

[Krzysztof Ignasiak [:kik]]

Comparing the tables before and after:

clients_week_2:
https://sql.telemetry.mozilla.org/queries/97897/source#241724

clients_week_4:
https://sql.telemetry.mozilla.org/queries/97898/source#241726

firefox_ios_clients:
https://sql.telemetry.mozilla.org/queries/97899/source#241728

The differences looks reasonable to me and nothing that would suggest this process caused any issues.

Comment 4

[Krzysztof Ignasiak [:kik]]

Verifying that those tables no longer contain clients with a deletion request by running the following query for each:

WITH deletion_requests AS (
  SELECT
    DATE(submission_timestamp) AS request_date,
    client_info.client_id,
  FROM `moz-fx-data-shared-prod.firefox_ios.deletion_request`
  WHERE DATE(submission_timestamp) < CURRENT_DATE
)
SELECT target_table.client_id
FROM `moz-fx-data-shared-prod.firefox_ios_derived.[TABLE_NAME]` AS target_table
INNER JOIN deletion_requests
  ON deletion_requests.client_id = target_table.client_id
  AND deletion_requests.request_date <= target_table.first_seen_date

We no longer get any results back which is what we expected / desired. Hopefully, we should not be seeing this check failing anymore after today.

Comment 5

[Krzysztof Ignasiak [:kik]]

:Alekhya could you confirm how this specific bug is blocking the linked bug?

Comment 6

[Krzysztof Ignasiak [:kik]]

The issue described in this bug appears to now be resolved.