Full monthly load stats for all wikis, 2026-03 snapshot.

Time taken: ~6 min and ~11min respectively. Total ~17 minutes.

dbt run --select +mrt_editor_counts_per_page --full-refresh  --vars '{"mwh_snapshot": "2026-03", "load_to_cassandra": false}'

05:43:31  Finished running 2 incremental models, 1 view model in 0 hours 17 minutes and 4.46 seconds (1024.46s).

1006 uniuqe wiki_ids.

spark-sql (default)> select count(DISTINCT wiki_id) from int_editors_per_page;
count(DISTINCT wiki_id)
1006

Row counts

spark-sql (default)> select count(1) from int_editors_per_page;
count(1)
4_016_870_453

spark-sql (default)> select count(1) from mrt_editor_counts_per_page;
count(1)
686_981_624

DB sizes

akhatun@stat1008:~$ hdfs dfs -du -h /user/hive/warehouse/akhatun_wmf.db
37.1 G   /user/hive/warehouse/akhatun_wmf.db/int_editors_per_page
3.8 G  /user/hive/warehouse/akhatun_wmf.db/mrt_editor_counts_per_page

akhatun opened https://gitlab.wikimedia.org/repos/sre/data-gateway/-/merge_requests/11

Add unique editor counts per page endpoint

Update:

• dbt-job MR: https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs/-/merge_requests/70 (for some reason did not connect to the ticket)
• Pushed code to create data gateway API: https://gitlab.wikimedia.org/repos/sre/data-gateway/-/merge_requests/11
• Created ticket for MW Interfaces team to help with data verification: T427548: Check Editor Counts
• Created design review ticket so Data Persistence and/or SRE can create Cassandra table: T427442: [Data Persistence Design Review] Editor count per page (Attribution API)
  • Halley Coplin started work on filling out some TBD areas, such as SLOs etc

In-progress:

• Analyzing how revision imports work for these counts. We have event_user_is_cross_wiki to identify these edits. Need to make decisions on whether to include them in anon/temp/bot etc counts. These don't have event_user_central_id, so the same user editing on multiple wikis are not considered the same user. But I think that is a valid assumption.... TBD

Some analysis on user status (anonymous, temporary, permanent, cross-wiki)

• In this metric we focus on event_user_central_id to get unique editor counts. Ideally all permanent and temporary users should have this id. For Anonymous (aka IP) users, we use the event_user_text field
• Edge case: Some older revisions have permanent/temporary users who don't have a event_user_central_id.
  • We should use user_id for these
• If both ids are NULL, we can fall back to user_text. Usually that is the case for anonymous users.
• Cross-wiki users are marked as anonymous. This may be misleading as the users are not really anonymous. These do not have is_permanent or is_temporary set. So we don't know what kind of account the original user had directly from these fields.
  • We could include a cross_wiki_edit_count to count them separately as required.

I am leaning towards adding the cross-wiki users as a separate count as such

{
  "wiki_id": "enwiki",
  "page_id": 12345,
  "editor_bot_count": 4,
  "editor_logged_out_count": 21,
  "editor_permanent_count": 93,
  "editor_temporary_count": 9,
  "editor_cross_wiki_count": 9,
  "page_is_deleted": false,
  "updated_at": "2026-05-26 00:00:00.000Z"
}

Downstream applications cas use it as they see fit.

Also wondering if we should retain the editor_total_count field. Right now it is simply the sum of the existing fields. If in future we change the definition such that the various counts are not mutually exclusive, the total count will be useful.

Details/Caveats of this dataset:

• We are using event_user_text_historical, meaning user name changes are not being considered. Because event_user_text (the latest username) field is not available in Inc. MWH. We use user_ids, so this should not effect permanent/temp users. Only affects anonymous users. Anon users can't change names, so our metric should be just fine.
• We are using event_user_is_bot_by_historical, as current bot status event_user_is_bot_by is not available in Inc. MWH. This means if someone was deemed bot later on (or vice versa), we won't know that until that user makes a new change in that page. We receive an update for that user, and change the bot field. We are able to take on the latest value of bot-ness (if available) because we do MAX_BY(editor_is_bot, event_timestamp).
• page_is_deleted is NULL: There are cases where page_is_deleted can be NULL as set in MWH.
  • This field is absent from Incremental MWH. We may have to drop this field entirely.

Cross-wiki users are marked as anonymous. This may be misleading as the users are not really anonymous. These do not have is_permanent or is_temporary set. So we don't know what kind of account the original user had directly from these fields.

From this month onward MWH will have a event_user_is_cross_wiki field, based on extracting cross-wiki usernames with a regex (there might false positive, hopefully not much).

This field is absent from Incremental MWH. We may have to drop this field entirely.

We are working on making this field available in inc-MWH.

This means if someone was deemed bot later on (or vice versa), we won't know that until that user makes a new change in that page. We receive an update for that user, and change the bot field.

Hm! I wonder if this is necessary. If we made mutable fields that we group by (like botness), as part of the group by / unique key, then we wouldn't have to reclassify old edits as bot edits if the user was not a bot when they made the edit. I think botness is the only field like this right now? And, perhaps...we are wrong to but bot into user_type. Perhaps user_type should be an immutable value per user, but bot is an extra property?

So I guess editors per page becomes something like:

wiki_id
page_id
user_id
user_central_id 
user_is_bot                  # true if this user made an edit to this page while it was a bot, 
user_type                    # (permanent, temp, etc.)
editor_is_visible       # whether or not this user’s contributions on this page is publicly visible by MediaWiki users.  This will be false if the page has been deleted, or if the user’s revisions on this page have all been hidden (or their username has been hidden) via MW RevisionDelete.
record_incremental_update_dt # time this record was last updated by an incremental source.
record_lambda_update_dt      # time this record was last lambda loaded by a snapshot source

(apologies if I am using an outdated schema, but you get the idea)

This would mean that it is possible to have 2 records for the same page_id and user, e.g.

indicating that this user made edits to this page while it was a bot and also while it wasn't a bot.

I'm not sure about this though. It seems correct under the hood: An edit made by a user that is not a bot is technically not an edit made by a bot, even if later on they are marked as a bot? OTOH, that means if a page has 2 edits by the same user, where for one they were a bot and the other they were not, the total unique editor count for that page will be 2, even though it will look like only one user edited the page.

What do you think?

OTOH, that means if a page has 2 edits by the same user, where for one they were a bot and the other they were not, the total unique editor count for that page will be 2, even though it will look like only one user edited the page.

Discussed this with @AKhatun_WMF. We think this is weirder than re-classifying old edits by an editor's botness. Let's stick with @AKhatun_WMF's original approach (latest user botness value per page is the one that applies).

TL;DR

• Unless strictly required for editor metrics, I propose to leave out cross-wiki users for now. When we add user-status details in MWH (permanent/temp/anonymous + bot) for cross-wiki users, the editor metrics will be more accurate. T428018: Add user status details for cross-wiki users in MWH
• Continue to consider the latest revision of a editor in a page to get the most recent bot-ness. This means the bot-ness may not be updated in some pages until another edit is made. But the bot-ness is consistent within that page.
• Consider bot-by-group and bot-by-name as bot count.

@HCoplin-WMF, @Ottomata, @JAllemandou : Thoughts? Issues? Sounds good?

Details

Terminology

• The terms user and editor is being used interchangeably here.
• Bot definition = Bot classification = Bot-ness = Bot by either group or name only. Not any fancy Bot detection algorithm.
• Cross-wiki user: https://www.mediawiki.org/wiki/Help:Import, T425735: interwiki imports and its effects on revision data

Some updates after disccusion with @Ottomata and @JAllemandou.

Cross-wiki users

Cross-wiki users (en>USERNAME, imported>USERNAME) are identified by a > and being marked as anonymous. BUT they really could be permanent/temporary/anonymous users from another wiki. MWH does not have information of whether they were permanent/temp/anonymous originally.

• Proposal 1: was to do a simple regex to obtain the username and identify permanent, temp, and anonymous usernames. It is best to make this change in MWH itself so this information is populated for everyone. Issues:
  • There may be some false positives in identifying cross wiki users
  • Possibly requires some research on how cross-wiki users are named etc.

Unlike with transwiki import, which always preserves content from the source wiki unchanged, nothing stops you from editing the XML file before importing it, allowing backdating of edits, fake usernames, or other ways of rewriting history.

(source)

• • Not sure if regex parsing will be reliable if user-names can be changed like this. Although the percentage could be quite small.
  • May be slightly involved to add this in MWH.
• Proposal 2: was to get the username and the wiki from where it was imported from. Get the user information from user-tables based on that username. Issues:
  • Since this uses event time username, the username could have changed
  • The prefix of cross-wiki usernames are not consistent, it may not be easy to simply find a project where this user made the original edit. E.g. we have en, w:en, en:w, meta, MetaWiki, template, imported, w, jewiki, 1, *, wikipedia:hi. Some are possibly false positives. But maybe not. Here is an example of an actual import with a weird prefix: wiki page 1. More examples wiki page 2, wiki page 3.
  • In short: this method won't be easy and/or reliable.
• Proposal 3: Don't perform special parsing/derivation. Add a editor_cross_wiki_count. Let downstream users use this information as they see fit. Not ideal but simple and clear enough. When we figure out the user status of cross wiki users, we can deprecate this field.
• Proposal 4 [recommended]: Leave out cross-wiki users altogether. If we expose such a field, downstream users may start using it, and later deprecation would be problematic. Better to not include it at all. It will be clear from a page history that only direct editors are being counted for that page. Once we have user status available in MWH, that will be reflected in the counts automatically. Thus making it more accurate without schema or any other major changes. The number of revisions from cross-wiki user is small: 0.1%, some wikis use it more than others, dewiki specifically .

• We also don't have bot information about a cross-wiki user, We only have bot by name, which is essentially identifying if the username contains the term Bot.

Is Bot

We consider event time bots classification at the moment (user_is_bot_by_historical). Bot definitions can change over time.

• From the original proposal, we wanted to keep the latest definition of bots. Since we won't have user_is_bot_by field (current bot status) in Incremental MWH, always getting the freshest bot classification won't be possible for now.
• Proposal 1 [recommended] The next best thing is to get the most recent bot definition using new edit events that come in. For every (page_id, user), we grab the latest revision based on event-time and get the bot definition from that row.
  • Pros:
    • We get the latest definition IF can edit was made by that user in that page.
    • The bot-ness of a user per page remains same in incremental MWH vs monthly loads from MWH. So it is consistent.
  • Cons:
    • If bot definition of a user changed after an edit. Unless another edit was made by that user we won't know that their bot definition was changed.
    • The bot-ness is not consistent across time for a user. A user will be considered bot or not based on recent edits and the counts will differ based on that.
    • Bot-ness is not consistent across pages. If comparing the edit history of a user in different pages, we may see the user as bot in one place, not a bot in another.
• Proposal 2: T426316#11976366. If a users bot-ness changed, consider them as edits from 2 different users. We don't change counts of previous edits. Only add counts to users based on whether or not they are bot at the time of the edit.
  • Pros:
    • The metric is stable across pages and across time
    • Still stable in Incremental vs Monthly loads.
  • Cons:
    • The bot definition is not the most updated definition. More true to event time bot-ness.
    • Looking at the history in a page, a user can be counted as both bot and not bot. Which edits are what will not be known. But just that someone was once a bot, or is now a bot.
• I would prefer #1 because
  1. Thats the most recent bot-ness
  2. Less confusing in the edit history within a page
  3. If we eventually get a user_is_bot_by (current bot-ness) field in Incremental MWH we can easily swap the fields and make this metric more accurate and consistent across pages.
• @JAllemandou proposed we only consider bot by group and ignore bot my name since it is a construct made by MWH. group comes from MW itself so that makes more sense. I prefer to keep both since with bot by name is a simple regex on finding the term "Bot" in the user-name. This is clear enough that this user should be a bot, and this would keep the definition of bot consistent with other metrics that use MWH. Although someone could also use name_bot, group_bot separately (as derived in MWH reduced). LMK if someone thinks otherwise.

bot...

group comes from MW itself so that makes more sense.

Just FYI:
user.is_bot in events is determined by group as well as permissions, Which I think we don't have in MWH?

I prefer option 2 from above, particularly for the reason that a metric changing in time is not a good idea. I'm not the decision maker though :)

user.is_bot in events is determined by group as well as permissions, Which I think we don't have in MWH?

Correct, we don't have permissions in MWH.

eevans merged https://gitlab.wikimedia.org/repos/sre/data-gateway/-/merge_requests/11

Add unique editor counts per page endpoint

Visibility Changes:

What Incremental MWH does: Currently we perform a monthly load of MWH. Then as visibility change events come for old edits, we go back to the old edit and update its visibility status. User/Text/Comment visibility can be updated in this way.

Inc MWH deletes all data until the end of the month and lands new more correct data till that date. But any visibility change events that arrived while Inc. MWH was processing get lost as monthly load overwrites it. We don't get back this visibility change until next monthly reload fixes this.

In short, if monthly load gets snapshot data till 31st May, and lands on 3rd June. Any visibility change events applied to old revisions 1 or 2 June gets lost. This is corrected in the next monthly load.

Checking with last 90 days of data, we have ~1300 visibility change events on average occurring per day across all wikis. Daily average across wikis (Query):

So we *may* lose ~1500 events on average, or *worse case* maybe hit one of those days with a lot of visibility event changes and hit ~4-13k events for large wikis. This may or may not affect the editor count depending on whether the editor has other visible edits.

Connecting this back to bot-ness discussion above, this metric is not quite stable over time. It will get fixed after every monthly load, but during the month it is at best a good approximation.

Page is Deleted

The same logic applies to the page_is_deleted field. This has a different kind of impact. It does not change counts but whether downstream APIs want to show this data or not depends on this field. So if some pages are deleted in first few days of the month, this won't be reflected. I think whats worse is, if some page is undeleted in the first few days of the month, it will still be marked as "deleted" for the rest of the month until the monthly reconcile fixes it. If we rely on the page_is_deleted field, we might prevent showing data for legit pages.

Currently there are ~98k delete and ~155 undelete events on average per day across all wikis. So we will have the incorrect page deletion status of ~98k pages for the month, until the next monthly load. Daily average across wikis (Query):

cc: @HCoplin-WMF wondering if these limitations are acceptable and whether you would want to rely on other easier data sources for page_deletion status?
cc: @xcollazo to make sure the page deletion understanding is correct.

Other details

Page delete/undelete changes page_id?

Your metric keys on (wiki_id, page_id), and the daily writer's MERGE 6 back-patches page_is_deleted onto revision rows by joining on page_id (:1475-1477). Two consequences:

• Common case (page_id preserved across delete→undelete): the delete event flips page_is_deleted = TRUE on the page's revision rows, and the undelete (restore) flips them back to FALSE on the same rows. Counts stay consistent.
• Edge case (undelete assigns a new page_id): the archived revisions in MWH still carry the old page_id, but the restore event carries the new one. MERGE 6's page_id join won't match the old revision rows, so they can be left stranded as page_is_deleted = TRUE, and the page's editor history effectively splits across two page_ids. Daily incremental won't reconcile this — only the monthly full refresh (which rebuilds from wmf.mediawiki_history's consistent page_id assignment) will.

Looks like in the past 90 days, prior_state.page.page_id is always NULL, meaning page_ids did not change for undeletions. And the code says

		// Usually page_id will be the same, but there are some historical
		// edge cases where a new page_id is created as part of an undelete.

So this should be rare case.

Even if page_id was changed, *eventually*, all revisions will be now attached to the new page_id. So essentially we will have the editor counts correctly, just on a different page_id. And old page_id should ideally be set to page_is_deleted=True.

, if monthly load gets snapshot data till 31st May, and lands on 3rd June. Any visibility change events applied to old revisions 1 or 2 June gets lost. This is corrected in the next monthly load.

For MWHInc Phase 2: I hope we could just incorporate revision visibility changes as its own event row. We wouldn't have to mutate the historical state at all. Instead, we could provide a lookup table to join the current/latest revision visibility state.

there are some historical edge cases where a new page_id is created as part of an undelete

IIRC, this should never happen anymore. This is very old MediaWiki behavior. So there may be old records in MWH where an undelete causes a changed page_id, but there shouldn't be any new occurrences of this.

In short, if monthly load gets snapshot data till 31st May, and lands on 3rd June. Any visibility change events applied to old revisions 1 or 2 June gets lost. This is corrected in the next monthly load.

Noting here that we decided on a solution for this: We are now sidestepping this issue by blocking the first daily incremental run on the monthly reconcile. This wins us correctness, as we do not loose the visibility or page deletes by forcing ordering of events. The cost is that the first daily of the month and the 2nd will likely be late. We estimate those two will be at least 24 hours late. The 3rd may be late as well. By the 4th day we should have recovered. The rest of the month will run dailies with no delays associated to this.

cc: @xcollazo to make sure the page deletion understanding is correct.

Understanding is correct, with the caveat that we agreed on blocking dailies to avoid these situations as discussed above.

For MWHInc Phase 2: I hope we could just incorporate revision visibility changes as its own event row.

+1!

Date Interval Consideration:

Incremental MWH data interval is (T428503#12003010):

• Daily runs = data_interval_end (Data for the day of 1 June is stamped as 2 June 00:00:00)
• Monthly reconcile = data_interval_end - 1 second (Data for the day of 31st May is stamped as 31st May 23:59:59)

How to grab both daily and monthly data as they land?

1. (data_interval_start, data_interval_end] of data interval: editor counts dag waits for Inc MWH daily dag, gets data stamped data_interval_end and it is picked up.
   • Problem: Monthly (e.g. 1 June) data drops on data_interval_end-1 second. And it drops ~3 days later (e.g. 3 June). By that time, the dag that started on 1 June (for data of (31 May, 1 June]) is over and misses the monthly reconcile data that lands later but is stamped 31st May 23:59:59.
   • Fix: Branching: Only on 2nd June (data_interval_start = first of month), we pick up data [data_interval_start - 1 second, data_interval_end]. This picks up the monthly data. But, this means during later re-runs, the run of 1st June will pick up the monthly data, and the run of 2nd June will ALSO pick up the monthly data.
2. Always do >= (data_interval_start - 1 sec). Monthly and daily data always caught properly as they land. BUT, the missing end interval makes the read set depend on execution time → not rerun-stable; retries/backfills re-scan everything since start; unbounded work.

I am thinking of #1 for now. Let me know if there's a better solution here. @JAllemandou @Ottomata @xcollazo

Cassandra loads

• Currently I am setting updated_at = data_interval_end and loading cassandra tables for that run by filtering for rows where updated_at = data_interval_end. This works fine per dag. But if we re-run an older dag (e.g. due to failure) this will set the updated_at to an older date time. It will still push to cassandra correctly. But the field will have an older date even though it does use newer data (if available).
• I am thinking of adding a separate loaded_at that is always set as data_interval_end so cassandra loads correctly. And updated_at = MAX(row_update_dt). So updated_at now means the latest timestamp of source table. So updated_at is monotonically increasing. loaded_at can go back in time.
• I am also wondering if we even need a updated_at and if only keeping a loaded_at works? Is there any need for a field to show "how latest is this data?" I guess the dags should tell us how far we have computed the data?

I am also wondering if we even need a updated_at and if only keeping a loaded_at works?

Maybe not, but if it is easy to keep updated_at it may turn out to be useful. I can imagine someone filing a bug about incorrect data, and having both of these fields in cassandra to be able to correlate records with what is going on in the Data Lake sounds useful?

But I'm not sure, so up to you!

akhatun updated https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2290

Editor counts dbt job

@Ottomata: so I ended up discarding updated_at, unfortunately.

• One way I was thinking was to do max(updated_at, current_load_timestamp). In this case, if dags are run out-of-order, the update_at field will have the latest value, but the data is not necessarily latest up to that updated_at timestamp. Since we re-count from all revisions of a page if any revision of that page was touched, we would essentially get "what is the most current editor count for a page that was touched on this date". So updated_at field doesn't really give us any new information here.
• Another way would be to do max(updated_at, row_update_at), where row_update_at is the Inc MWH field. This is to sync updated at field with what was the latest data we used from Inc MWH. But current_load_timestamp is actually the same as row_update_at, so it remains the same thing. Currently Inc MWH does not support out-of-order runs, so this field is not useful to track Inc MWH latest data timestamp either.

To solve the issue of "how latest was the data?", we can look at airflow dag run times based on the loaded_at timestamp. E.g. if loaded_at was 2026-06-02 00:00:00, the logs of dag run of 2026-06-02 00:00:00 data_end_interval needs to be investigated. The time when that dag was run should tell us how recent the data was exactly.

The pipeline is ready, tested in staging. Data pipeline and Cassandra loads are working great.

What it is

Distinct visible editor counts per (wiki_id, page_id) — total, permanent, bot, logged-out (anonymous), and temporary — built from the incremental MediaWiki History table (wmf_mediawiki.mediawiki_history_incremental_v1). Model chain: stg_mwh__revision_editor → int_editors_per_page → editor_counts_per_page, with an optional Cassandra load post-hook.

Summary of decisions made

• A daily dag waits for mw_incremental_history_daily, captures any revision added/edited in the last day, and re-computes unique editor counts for pages that were touched. mw_incremental_history_daily has a field called row_update_at that we use to find which rows were touched.
• mw_incremental_history_daily blocks on mw_incremental_history_monthly. Usual days are fine. 1st, 2nd, and 3rd daily run of the month waits until the monthly job finishes. This means editor counts metric for 1st, 2nd, and 3rd of the month will also be delayed until the 3rd or 4th day. This trade-off was accepted to ensure we do not carry incorrect visibility or other back-changes for the whole month until the next monthly refresh fixes it.
• Cross-wiki editors excluded from all counts (kept in int_editors_per_page) until MWH exposes reliable user-status for them. Currently they are always marked logged-out, which is incorrect.
  • user_is_cross_wiki *may* have some false positives in MWH, which will trickle down to this dataset. Essentially if a logged-our user-text contains >, which I don't think should happen.
• New-wiki completeness gap: The daily writer inner-joins the previous-month namespace snapshot, so a wiki created mid-month misses events until the next snapshot.
• loaded_at ≠ row_update_dt. loaded_at is the load timestamp (data_interval_end), not the MWH watermark and does not mean "latest as-of". It determines when a row was last loaded, hence last updated.
• editor_total_count = permanent + logged_out + temporary + bots.
  • permanent, logged_out, temporary are strictly human-like editors
  • bots can be any type, but usually they are always permanent editors.
• We are using event_user_is_bot_by_historical, as current bot status event_user_is_bot_by is not available in Inc. MWH. This means if someone was deemed bot later on (or vice versa), we won't know that until that user makes a new change in that page. If we receive an update for that user, we update the bot field. We are able to take on the latest value of bot-ness (if available) because we do MAX_BY(editor_is_bot, event_timestamp). More details here: T426316#11979835
• page_is_deleted can be NULL: This happens when MWH could not determine the page deletion status. Consumers cannot fully rely on this field to determine if they want to show results to the users or not. A fallback method would be required.

akhatun opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2328

dbt: add cassandra loading feature

akhatun updated https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2328

dbt: add cassandra loading feature

akhatun closed https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2290

Editor counts dbt job