1721878 - Data missing from clients_daily from 2017-01-01 through 2018-10-29

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect, P1)

Description

[Jeff Klukas [:klukas] (UTC-4)]

As part of https://bugzilla.mozilla.org/show_bug.cgi?id=1716074 I ran a backfill of clients_daily_v6 from main_v4 for all dates starting at 2017-01-01. This ran during the Wellness week (last week of June).

I had forgotten, however, that main_v4 only reaches back to 2018-10-30, so all the earlier dates ended up empty. I did not do sufficient validation to notice this, and :benwu just alerted me to the problem.

We are BigQuery's built-in 7-day retention period, so we cannot simply roll back those partitions to their previous state. I see two options for recovery:

1. Rerun the empty partitions, using a previous version of the clients_daily_v6 query which targeted main_summary, or
2. Backfill by querying clients_last_seen; that table contains a superset of the clients_daily fields, so we should be able to craft a SELECT statement that recreates clients_daily.

Option (2) is likely cheaper in terms of compute time and data scanned, and also is likely easier to implement. I will pursue that route.

Comment 1

[Jeff Klukas [:klukas] (UTC-4)]

Attachment: Full query for backfill from clients_last_seen to clients_daily

Specifically, the query will look like:

```
SELECT
  submission_date,
  client_id,
  aborts_content_sum,
  ...
FROM
  `moz-fx-data-shared-prod.telemetry_derived.clients_last_seen_v1`
WHERE
  submission_date = @submission_date
  -- Only include clients seen today.
  AND mozfun.bits28.days_since_seen(days_seen_bits) = 0
```

Comment 2

[Jeff Klukas [:klukas] (UTC-4)]

Attachment: full query

Accidental double-post.

Comment 3

[Jeff Klukas [:klukas] (UTC-4)]

I've started this backfill from 2018-10-29 backwards, using the moz-fx-data-backfill-slots project.

Comment 4

[Jeff Klukas [:klukas] (UTC-4)]

I've completed a backfill for 2017-01-01 to 2018-10-29. clients_daily now looks continuous over those boundaries.