1776158 - Multiple tasks failing in bqetl_cjms_nonprod Airflow DAG on 2022-06-23

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Arkadiusz Komarzewski [:akomar]]

Multiple tasks failing in bqetl_cjms_nonprod Airflow DAG on 2022-06-23

Comment 1

[Sean Rose [:srose]]

At 2022-06-23 00:10 the cjms_bigquery__subscriptions__v1 task for 2022-06-22 23:00 failed with:

Error in query string: Error processing job 'moz-fx-data-shared-prod:bqjob_r3b871343480fc0e3_000001818de57adf_1': Field name event_type does not exist in STRUCT<err STRING, clientid STRING, messageid STRING, ...>; failed to parse view 'moz-fx-cjms-nonprod-9a36.cjms_bigquery.flows_live' at [34:28]

That corresponds to this subquery:

  SELECT
    PARSE_DATE('%y%m%d', _TABLE_SUFFIX) AS submission_date,
    JSON_VALUE(jsonPayload.fields.user_properties, '$.flow_id') AS flow_id,
    `timestamp`,
    TO_HEX(SHA256(jsonPayload.fields.user_id)) AS fxa_uid,
  FROM
    `moz-fx-fxa-nonprod-375e.fxa_stage_logs.stdout_20*`
  WHERE
    jsonPayload.type = 'amplitudeEvent'
    AND jsonPayload.fields.event_type IS NOT NULL

So for some reason one of the moz-fx-fxa-nonprod-375e.fxa_stage_logs.stdout_20* tables didn't have the jsonPayload.fields.event_type column (presumably the newest one at the time, moz-fx-fxa-nonprod-375e.fxa_stage_logs.stdout_20220623).

Subsequently the cjms_bigquery__flows__v1 tasks for 2022-06-23 00:00 through 2022-06-23 10:00 failed with similar errors stemming from the same subquery on the moz-fx-fxa-nonprod-375e.fxa_stage_logs.stdout_20* tables, but now indicating the jsonPayload.fields.user_id column was missing rather than jsonPayload.fields.event_type. (the cjms_bigquery__subscriptions__v1 tasks for those times didn't run because they're dependent on cjms_bigquery__flows__v1)

Ultimately, at 2022-06-23 12:00 the cjms_bigquery__flows__v1 and cjms_bigquery__subscriptions__v1 tasks for 2022-06-23 11:00 succeeded, and subsequent tasks also succeeded, indicating the underlying problem with the moz-fx-fxa-nonprod-375e.fxa_stage_logs.stdout_20* tables was somehow resolved.

Closing as fixed, though we didn't do anything in particular to fix it.

Comment 2

[Sean Rose [:srose]]

It looks like the exact same problem also happened between 2022-06-18 00:02 and 2022-06-20 13:05. I'm going to follow up with the FxA team to see if they might know why the moz-fx-fxa-nonprod-375e.fxa_stage_logs.stdout_* tables don't always have the expected schema.

Comment 3

[Sean Rose [:srose]]

From Jon Buckley in Slack:

yeah, schemas can change day to day - we probably need to implement https://mozilla-hub.atlassian.net/browse/SVCSE-644 to fix this for good

SVCSE-644 is "Migrate FxA Cloud Logging Big Query sinks from sharded tables to partitioned tables", and references a related bug 1776156 "Multiple tasks failing in bqetl_fxa_events Airflow DAG on 2022-06-23".