1863535 - Potential bug with the same client_id being added to different Fenix app (channel) tables

Status: RESOLVED FIXED

(Data Platform and Tools :: Glean: SDK, defect)

Description

[Krzysztof Ignasiak [:kik]]

Potential bug with the same client_id being added to different Fenix app (channel) tables

We've noticed the following view: fenix.baseline_clients_first_seen results in multiple entries for a single client to be returned. This is unexpected and our current assumption is that the table grain should be 1 row per 1 client_id:

SELECT
  client_id,
  ARRAY_AGG(submission_date),
  ARRAY_AGG(normalized_app_id),
  COUNT(*) AS _cnt,
FROM fenix.baseline_clients_first_seen
WHERE submission_date < CURRENT_DATE
GROUP BY client_id
HAVING _cnt > 1

Yields:

client_id	f0_	f1_	_cnt
0a33cc2d-ae4b-403d-a203-334bfa4dfd2f	"[2023-09-19,2023-09-20]"	"[org_mozilla_fenix,org_mozilla_firefox]"	2
a93c3672-935c-47ee-8ce3-4df1671f41df	"[2020-08-31,2020-08-12]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2
f37b2546-005e-4b4d-8c8a-9badaca46603	"[2020-08-15,2020-09-05]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2
2e4ef5e0-a786-4491-8bdc-6b202ae2422c	"[2021-03-15,2021-03-21]"	"[org_mozilla_fenix,org_mozilla_firefox]"	2
fe7d00d0-a6c8-4052-9166-cf4c92955241	"[2022-02-12,2022-02-13]"	"[org_mozilla_fenix,org_mozilla_firefox]"	2
b2f953f9-f496-486a-b422-691e7617f256	"[2020-04-18,2020-04-27]"	"[org_mozilla_fenix_nightly,org_mozilla_fenix]"	2
00000000-0000-4000-8000-000000000000	"[2023-06-11,2023-06-26]"	"[org_mozilla_fenix,org_mozilla_firefox]"	2
c7ebf099-e034-437c-96c1-fddaf5cb988a	"[2023-09-21,2023-09-14]"	"[org_mozilla_fenix,org_mozilla_firefox_beta]"	2
5ded5753-e002-43da-b8c9-9b5c1395668c	"[2020-07-06,2020-12-29]"	"[org_mozilla_fenix,org_mozilla_firefox]"	2
89a9d4e9-f9e7-4a24-bf32-6afbc6290226	"[2020-09-16,2021-01-14]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2
4a0c3c24-d6c6-437a-9e64-2a5b06933397	"[2020-09-26,2020-12-11]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2
3f174162-c0c3-4105-b555-5254bf2075c6	"[2023-06-17,2023-06-17]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2
94bf24c7-5b5b-4fd0-a5ee-11aa04394dd1	"[2020-04-06,2020-07-03]"	"[org_mozilla_fenix_nightly,org_mozilla_fenix]"	2
02254013-aa07-409f-9ff7-5b71643440d3	"[2022-02-21,2022-02-20,2022-02-15]"	"[org_mozilla_fenix,org_mozilla_firefox_beta,org_mozilla_firefox]"	3
5fb1823e-1b84-49d1-9609-6fd4037aa459	"[2020-07-11,2020-07-10]"	"[org_mozilla_fennec_aurora,org_mozilla_fenix]"	2
950bbb76-8698-4103-ac9a-3dac48b4d702	"[2020-04-02,2020-07-03]"	"[org_mozilla_fenix,org_mozilla_firefox_beta]"	2
113771b7-654b-49cc-8241-c8f698b939f9	"[2022-01-28,2022-01-29]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2
4cf0e877-edbb-4b6a-8ba3-67585364490b	"[2020-09-30,2020-10-06]"	"[org_mozilla_firefox_beta,org_mozilla_firefox]"	2

The different normalized_app_ids correspond to different channel tables. Here's a link to the view logic where the referenced tables can be found:
https://github.com/mozilla/bigquery-etl/blob/generated-sql/sql/moz-fx-data-shared-prod/fenix/baseline_clients_first_seen/view.sql

It appears for the Fenix app specifically it is possible for a single client_id to be present inside more than 1 table at a time.

Using client 0a33cc2d-ae4b-403d-a203-334bfa4dfd2f as an example:

SELECT
  "org_mozilla_fenix" AS app_id,
  COUNT(*),
FROM `moz-fx-data-shared-prod.org_mozilla_fenix.baseline_clients_first_seen`
WHERE client_id = "0a33cc2d-ae4b-403d-a203-334bfa4dfd2f"
AND submission_date BETWEEN "2023-09-18" AND "2023-09-22"
UNION ALL
SELECT
  "org_mozilla_firefox" AS app_id,
  COUNT(*),
FROM `moz-fx-data-shared-prod.org_mozilla_firefox.baseline_clients_first_seen`
WHERE client_id = "0a33cc2d-ae4b-403d-a203-334bfa4dfd2f"
AND submission_date BETWEEN "2023-09-18" AND "2023-09-22"

This query returns a result showing that this client has an entry in both tables resulting in what we consider duplication downstream of this view. This specifically appears to be a problem with Fenix:

app_id	f0_
org_mozilla_fenix	1
org_mozilla_firefox	1

Dear GLEAN team, could you please help us understand why this is happening?

Thanks!

Comment 1

[Jan-Erik Rediger [:janerik]]

Potentially just some very weird clients?
As seen from your query but also this one that particular client ID sent pings on exactly 3 days only.

Comment 2

[Krzysztof Ignasiak [:kik]]

Ok, but our understanding that we should not be seeing this is would be correct?

We're trying what is the best way and where to handle this in the pipeline. Ideally, we'd avoid having such polluted entries in the underlying tables rather than adding additional complexity downstream for this.

Just to clarify, is this something we have to accept and assume this is something that may occur from time to time?

---

Also, it appears the same client sent 1 more ping almost a month later (according to the graph you shared)?

Comment 3

[Jan-Erik Rediger [:janerik]]

From a code-point of view it's highly unlikely that we generate the same client IDs on different devices/apps. Not impossible, but highly unlikely.
User-initiaed profile migration is not a thing on Android afaik.
On Desktop this could of course happen: profiles can be cloned/re-used by different channels (but there we don't have the table split per channel).

There will always be weird (valid) clients as well as bogus data being sent to us. We will continue to run into those edgecases regardless.

Is this actually breaking things on a larger scale somewhere down the line?
I don't know enougb about the affected table, maybe it should explicitly aggregate by channel or app id?

(In reply to Krzysztof Ignasiak [:kik] from comment #2)

Also, it appears the same client sent 1 more ping almost a month later (according to the graph you shared)?

True. Even more of an indicator of what a weird client this is.

Comment 4

[Alessio Placitelli [:Dexter]]

(In reply to Jan-Erik Rediger [:janerik] from comment #3)

There will always be weird (valid) clients as well as bogus data being sent to us. We will continue to run into those edgecases regardless.

To further support what Jan-Erik said, consider the case of some entity curl'ing us with reasonably different payloads with the same client id.

Comment 5

[Frank Bertsch [:frank]]

The core question here: Is there a valid reason for a single client-id to appear in multiple channels?

Since the answer seems to be no, we can treat these as a single client in terms of historical record.

Comment 6

[Jan-Erik Rediger [:janerik]]

(In reply to Frank Bertsch [:frank] from comment #5)

The core question here: Is there a valid reason for a single client-id to appear in multiple channels?

Since the answer seems to be no, we can treat these as a single client in terms of historical record.

Correct.

Comment 7

[Krzysztof Ignasiak [:kik]]

ok, thanks a lot for your help. So just to clarify.

• We should not be seeing the same client_id across multiple channels and this is not a bug bur rather the specific client being "naughty".
• We can treat a single client id records as a single historical record.

I will add these as notes to the bug we're currently working on and address this at the analytics stage.

Thanks again!

Comment 8

[Jan-Erik Rediger [:janerik]]

(In reply to Krzysztof Ignasiak [:kik] from comment #7)

ok, thanks a lot for your help. So just to clarify.

• We should not be seeing the same client_id across multiple channels and this is not a bug bur rather the specific client being "naughty".

We should not see the same valid client_id across multiple Fenix channels.
We do see it in rare circumstances due to misbehaving/buggy/"naughty" clients.

• We can treat a single client id records as a single historical record.

Correct.