1822095 - Review buildid SQL queries

Status: RESOLVED WONTFIX

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

Description

[Jan-Erik Rediger [:janerik]]

These:

• https://sql.telemetry.mozilla.org/queries/86461/source
• https://sql.telemetry.mozilla.org/queries/86462/source
• https://sql.telemetry.mozilla.org/queries/88816/source

via alissy:

what is want is knowing how much buildid mismatch and false-positives we hit per release
and try to compare that with the population size of those releases
and the 88816 is about debugging why we have the spikes on the first ones
so there I was looking at just how much false-positives we have per release and later try to check system.os.version to get some hints

Initial request: https://mozilla.slack.com/archives/C4D5ZA91B/p1669136150751299

Comment 1

[Jan-Erik Rediger [:janerik]]

Couple of things I noticed:

• ordering by app version makes the graph a bit harder to parse, due to "100" < "99". I fixed it the ugly way by prefixing versions < 100 with a 0.
• you might want to filter by channel: normalized_channel = "release" (I haven't run the full query to see if that makes a big difference. Are non-release builds even shipped by those distributors? If not, then the filter on distribution_id already is a release filter I guess)

I also rewrote the query using CTEs and a join, but the graph is the same. You'll find it here: https://sql.telemetry.mozilla.org/queries/91021/source#225332
(It also isn't faster, note that I'm running it on main_1pct, a 1% sample of the full main table and only since January 2023)

I don't fully understand the probes buildID_mismatch and buildID_mismatch_false_positive.
From their names I first thought they might be related, that is for every buildID_mismatch_false_positive there will be a buildID_mismatch (but not the other way around).
But from the data that doesn't seem to be the case.

The spikes in mismatches at least correlate to spikes in client numbers. More clients = more potential for problems of course.

Comment 2

[Travis Long [:travis_]]

Why not convert the version to a numerical value and ORDER BY that instead of adding a "0" to versions < 100?

Comment 3

[Jan-Erik Rediger [:janerik]]

(In reply to Travis Long [:travis_] from comment #2)

Why not convert the version to a numerical value and ORDER BY that instead of adding a "0" to versions < 100?

Mostly because "nice frontend displaying" (and right now the frontend takes care of sorting, and that does a string comparison).

Comment 4

[Jan-Erik Rediger [:janerik]]

I noticed that I actually forgot to notify you, Alexandre.
Does comment #6 above help you? Any more you would need from us?

Comment 5

[:gerard-majax]

No, that helps a lot if you say my queries are sound it's enough for me :)

Comment 6

[:gerard-majax]

Jan-Erik, I've updated my dashboard by adapting your queries: https://sql.telemetry.mozilla.org/dashboard/buildid-mismatches

However, I'm a bit troubles by the values (false_positive being way high IMHO), can I ask your help to verify what I did?

Comment 7

[Jan-Erik Rediger [:janerik]]

I won't have time really to dive into this and help you. Let's take this to #data-help once more to get some assistance.