While working with event_sanitized.serversideaccouncreation data, I found many "fantom users" reported (users that are in SSAC, but not in mediawiki_user). Here is an example:
Spark query:
SELECT * FROM event_sanitized.serversideaccountcreation WHERE year = 2023 AND month = 3 AND day = 28 AND wiki = 'enwiki' AND event.isSelfMade == true AND event.userId = 45645424
Output (WMF-NDA paste):
{P94154}
Specifically, it reports the user's name to be PeterPanlikesmen, created at en.wiki.
However, all of the following SQL queries against enwiki database return zero rows:
- SELECT user_id, user_name FROM user WHERE user_id = 45645424
- SELECT user_id, user_name FROM user WHERE user_name = "PeterPanlikesmen"
- SELECT * FROM logging WHERE log_namespace = 2 AND log_title = "PeterPanlikesmen"
SELECT gu_id, gu_name FROM globaluser WHERE gu_name='PeterPanlikesmen' against centralauth also yields zero results.
I tried to see how widespread this is, and I run the following query:
SELECT ssac.dt, ssac.event.userId, ssac.event.userName, ssac.event.isSelfMade, rmu.user_id AS mw_uid FROM event_sanitized.serversideaccountcreation ssac LEFT JOIN wmf_raw.mediawiki_user rmu ON ( (rmu.snapshot = '2026-05') AND (rmu.wiki_db = ssac.wiki) AND (rmu.user_id = ssac.event.userId) ) WHERE ( -- between January 2021 and February 2025 (year >= 2021 AND year < 2025) OR (year = 2025 AND month < 3) ) AND ssac.wiki = 'enwiki' AND ssac.event.isSelfMade == true
On this, dfUsers[['is_joinable', 'dt']].groupby('is_joinable').count() returns:
- False: 6914
- True: 4975864
Meaning we have ~7k of fantom users in this data period.