1635906 - ETL for tracking ecosystem_user_id changes

Status: RESOLVED FIXED

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

Description

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

When an FxA user has their ecosystem_user_id value change (due a password reset, for example), we will receive some ping with both old and new values. We will need ETL to keep track of a canonical value.

Comment 1

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

I am currently working on how to express this in BigQuery SQL, and it will require a bit of new tooling since the necessary logic requires a loop to catch the full trail if there are multiple password resets for a single user in a given day.

The canonical_ecosystem_user_id table will have columns (ecosystem_user_id, canonical_id) where ecosystem_user_id is unique and canonical_id may appear multiple times.

We can choose to have canonical_id be the user's first seen ecosystem_user_id value, some hash of that first value, or a generated UUID.

Outline of the proposed logic:

• Create a temporary table of (eco_uid, previous_eco_uid) pairs from for the target submission date from firefox_accounts_stable.account_ecosystem_v1
• Deduplicate the temporary table to ensure a single entry per eco_uid
• Loop
  • Rewrite the temp table using a self-join that connects eco_uid to previous_eco_uid; we replace previous_eco_uid with the parent value when available
  • Loop ends when no more entries match the join condition
• Merge the new day's results into the full canonical_ecosystem_user_id table

Comment 2

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/1298

Comment 3

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/1323

Comment 4

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

Real implementation up for review in https://bugzilla.mozilla.org/attachment.cgi?id=9176318

It introduces terminology of canonical_id for the first ecosystem_user_id per user.

This table is being created in dataset account_ecosystem_derived and will be referenced in future user-facing views under account_ecosystem.

Comment 5

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/1335