Jump to content

This is a read-only backup copy of Wikitech. The live site can be found at wikitech.wikimedia.org

Data Platform/Data Lake/Edits/Mediawiki history incremental v1

From Wikitech

wmf_mediawiki.mediawiki_history_incremental_v1 is a dataset available in the Data Lake that provides the editing and account history of all Wikimedia wikis — revisions, page events (moves, deletes, restores) and user events (account creation, renames, group changes).

The schema of this table is a narrower version of Mediawiki history ( wmf.mediawiki_history ). However, this table's data comes primarily from event streams from the Event Platform , allowing us to update it on a daily basis, rather than the monthly cadence of wmf.mediawiki_history . The goal is freshness: most analyses that today wait weeks for the monthly rebuild can read this table within roughly a day of an edit.

Each row carries a source column describing its provenance and quality:

  • source = 'events' — recent rows landed daily from event streams. Best-effort: a small number of fields settle over the following days (see FAQ ).
  • source = 'snapshot' — rows projected from wmf.mediawiki_history by a monthly reconcile. Authoritative, with semantics identical to wmf.mediawiki_history .

In practice you query the table once and get the freshest available view: recent activity as 'events' rows, older activity as authoritative 'snapshot' rows once the monthly reconcile has run. If you need strict, fully-reconciled semantics only, filter on source = 'snapshot' .

Consuming this table is different from snapshot-based tables like Mediawiki history . See the FAQ below for details.

Schema

This table may expose data about users or revisions that has since been RevisionDeleted or suppressed . The revision_deleted_parts column lists which components of a revision ( text , comment , user ) were hidden. If the results of a query are to be made public, please honor these visibility flags and do not expose components marked as hidden.

This is an Iceberg dataset, partitioned by months(event_timestamp) . It has no snapshot Hive partition; see the FAQ for how to consume it.

col_name data_type comment
source string Row provenance: events (daily delta) or snapshot (monthly merge).
wiki_id string Wiki identifier, e.g. enwiki, dewiki, eswiktionary.
event_entity string Entity type: revision, user, or page.
event_type string Event sub-type: create, edit, move, delete, etc.
event_timestamp timestamp When this event occurred.
event_user_id bigint Local MediaWiki user ID of the actor; NULL for anonymous users.
event_user_central_id bigint Global CentralAuth user ID of the actor; NULL for anonymous users.
event_user_text_historical string Username or IP at the time of the event.
event_user_is_bot_by_historical array<string> Bot classification methods at event time: name and/or group.
event_user_is_anonymous boolean True if the actor had no local user account at event time.
event_user_is_temporary boolean True if the actor was a temporary (auto-created) account.
event_user_is_permanent boolean True if the actor was a permanent registered account.
event_user_registration_timestamp timestamp When the actor account was registered; NULL for anonymous users.
event_user_revision_count bigint Edit count of the actor at event time.
event_user_groups_historical array<string> Groups held by the actor at event time (before the change for altergroups events). NULL for revision and page events.
event_user_is_cross_wiki boolean True if the actor is a CentralAuth user editing while logged out (username format IP>GlobalUsername). NULL (not FALSE) when the actor username is absent from the event (e.g. system-created user accounts where performer text is not populated).
user_id bigint Local MediaWiki user ID of the user being created/renamed/altered.
user_central_id bigint Global CentralAuth user ID of the user being created/renamed/altered. NULL for non-user events.
user_text_historical string Username of the user after the event: new name for renames, current name otherwise.
user_is_anonymous boolean True if the user had no local account (always false for user events).
user_is_temporary boolean True if the user being acted upon was a temporary account.
user_is_permanent boolean True if the user being acted upon was a permanent registered account.
user_groups_historical array<string> Groups held by the user after the change (for altergroups), or at creation.
user_is_bot_by_historical array<string> Bot classification of the user based on user_groups_historical: name and/or group.
user_is_created_by_self boolean True if the user registered their own account (performer == user, not autocreate). NULL for non-create events.
user_is_created_by_system boolean True if the account was autocreated by the system (SSO/CentralAuth). NULL for non-create events.
user_is_created_by_peer boolean True if a distinct admin created the account (performer != user, not autocreate). NULL for non-create events.
page_id bigint Page ID at event time.
page_title_historical string Page title (without namespace prefix) at event time.
page_namespace_historical int Page namespace ID at event time.
page_namespace_is_content_historical boolean True if page_namespace_historical is a content namespace.
page_is_deleted boolean True if the page was in deleted state at this event. Set at event time for page events; back-patched for revision rows when a page delete/undelete arrives. NULL for user events.
revision_id bigint Revision ID; NULL for non-revision events.
revision_parent_id bigint Parent revision ID; NULL for page-creation revisions.
revision_minor_edit boolean True if the editor flagged this as a minor edit.
revision_text_bytes bigint Uncompressed byte size of the revision text.
revision_text_bytes_diff bigint Byte delta vs. parent revision; NULL for page-creation revisions.
revision_text_sha1 string SHA-1 of the concatenated slot content (all-slots hash).
revision_deleted_parts array<string> Visibility-suppressed components: text, comment, and/or user. NULL when nothing is suppressed. Reflects visibility at event time and is updated when later visibility-change events arrive. NULL for page and user events.
revision_is_identity_reverted boolean True if a later revision restored the page to the state before this one. source=events rows: FALSE for fresh revisions (rev_dt=today, non-null sha1) — a fresh revision cannot already be reverted; upgraded to TRUE when the reverter arrives. NULL for late-arriving or null-sha1 revisions until the monthly snapshot provides the authoritative value.
revision_first_identity_reverting_revision_id bigint ID of the first revision that identity-reverted this one; NULL if not reverted.
revision_seconds_to_identity_revert bigint Seconds between this revision and the first identity revert; NULL if not reverted.
revision_is_identity_revert boolean True if this revision itself is an identity revert (restores a prior page state). source=events rows: FALSE for fresh revisions (rev_dt=today, non-null sha1) where no revert was detected; NULL for late-arriving or null-sha1 revisions.
revision_is_deleted_by_page_deletion boolean True if this revision is in deleted state because its page was deleted (as opposed to an explicit RevisionDelete action). Back-patched when a page delete/undelete event arrives. NULL for page and user events.
revision_tags array<string> Change tags applied to this revision. Populated when the corresponding change-tag events are delivered, which may be after the revision was first written.
event_meta_id string Delivery UUID (meta.id) used as the MERGE key for page and user events. NULL for revision rows and all snapshot rows. Internal; not intended for analytics queries.
control_map map<string,string> Internal, do not use for queries. Per-stream rerun guard tracking the last event/backfill timestamp that updated this row, per stream. NULL for source=snapshot rows.
row_update_dt timestamp Incremental-read watermark of the run that last wrote this row. See the FAQ on reading the table incrementally.

Changes and known problems

Date Phab Task Details
2026-06-12 T424350 Phase I under development. The table is not yet declared production quality and its schema may still change. The exact semantics of the row_update_dt watermark are still being finalized and may change before production. event_user_groups_historical is currently NULL on source='events' revision and page rows; it should carry the actor's groups at event time. To be fixed before production.

FAQ

How do I get only fully-reconciled, authoritative data?

Filter on the source column: 

SELECT *
FROM wmf_mediawiki.mediawiki_history_incremental_v1
WHERE source = 'snapshot'
  AND wiki_id = 'simplewiki'
LIMIT 10

source = 'snapshot' rows are projected from wmf.mediawiki_history by the monthly reconcile and have identical semantics to that table. source = 'events' rows are the fresh daily data and are best-effort (see below).

Which fields are "best-effort" on source='events' rows?

The best-effort aspect is across days : some signals about a revision can arrive after the revision itself, and are patched in on a later daily run.

  • revision_tags are populated when the corresponding change-tag events are delivered, which may be after the revision was first written.
  • revision_deleted_parts reflects visibility as of the last daily run, and is updated when later visibility-change events arrive.
  • The revert fields ( revision_is_identity_reverted , revision_first_identity_reverting_revision_id , revision_seconds_to_identity_revert , revision_is_identity_revert ) are filled in when a reverting revision is processed, which is typically a later day.

So a source='events' revision reflects everything known about it as of the last daily run, but may not yet reflect tags, visibility changes, or reverts that have not happened or not yet been delivered. Every field reaches its authoritative value at the next monthly reconcile, when the row is replaced by a source='snapshot' row.

This table doesn't have a 'snapshot' Hive partition. How do I consume it?

This table does not use a snapshot partition as with other tables such as Mediawiki history . We use a table format called Iceberg . Instead of rewriting all data each month, this technology lets us update the table in place, with the benefit of daily updates.

If you are building a data pipeline and need to define an Airflow sensor to wait on this table's updates, instead of waiting on Hive partitions you should use our datasets.yaml configuration and the appropriate helper functions to construct a sensor.

In your Airflow Python DAG code: 

# here we use platform_eng, but you should use the config
# that applies to your Airflow instance
from platform_eng.config.dag_config import dataset

dataset_id = "iceberg_wmf_mediawiki_mediawiki_history_incremental_v1"

wait_for_sensor = dataset(dataset_id).get_sensor_for(dag)

If your sensor needs to cover more than one day's worth of target DAG executions, use the helper: 

from platform_eng.config.dag_config import dataset
from wmf_airflow_common.sensors.helper import get_daily_execution_dates_fn

dataset_id = "iceberg_wmf_mediawiki_mediawiki_history_incremental_v1"

wait_for_sensor = dataset(dataset_id)
    .get_sensor_for(
        dag,
        execution_date_fn=get_daily_execution_dates_fn(num_days=7),
    )

How do I read only the rows that changed since my last run?

Use the row_update_dt watermark column. Every write stamps the row with the timestamp of the run that last touched it, so an incremental consumer can read only new work: 

SELECT *
FROM wmf_mediawiki.mediawiki_history_incremental_v1
WHERE row_update_dt >= TIMESTAMP '{{ your_last_watermark }}'

You may additionally add an event_timestamp lower bound to get partition pruning, since the table is partitioned by months(event_timestamp) : 

  ... AND event_timestamp >= TIMESTAMP '{{ lower_bound }}'

Caveat: an event_timestamp bound is a performance optimization, not a correctness filter. Daily runs patch rows in place when visibility changes, page deletions, reverts, or change tags arrive for revisions that already exist — including rows with an old event_timestamp . An event_timestamp lower bound will silently miss those updates to historically-old rows. Only add it if your use case can tolerate missing such back-dated updates, or set the bound wide enough to cover them.

The monthly reconcile re-stamps its rows with a watermark at the very end of the snapshot month (one second before the next month boundary). This lets you choose whether your incremental read picks up the monthly re-baseline:

  • A lower bound exactly at a month-boundary midnight returns the daily deltas only and skips the monthly re-baseline.
  • Lowering that same bound by one second ( ... - INTERVAL 1 SECOND ) also pulls the full monthly snapshot (billions of rows).

What does waiting on the sensor guarantee?

After the daily run for a given day succeeds, all of that day's events have been ingested as source='events' rows. Those rows are best-effort (see above), and are made fully authoritative at the next monthly reconcile. If your use case needs strict semantics, consume only source='snapshot' rows.

How do I migrate to MHW incremental?

In the event there is a pipeline that, for any reason, needs to be migrated to wmf_mediawiki.mediawiki_history_incremental_v1 here is a list of changes to apply:

  • switch any reference from wmf.mediawiki_history to wmf_mediawiki.mediawiki_history_incremental_v1 .
  • switch the sensor reference, see above.
  • remove the snapshot filter and replace it with:
    • source = 'snapshot' , if only authoritative data is needed;
    • source = 'events' , if only stream and daily ingested data is needed;
    • no filter at all;
  • replace wiki_db with wiki_id .
  • replace columns such as page_namespace , page_title , user_text , event_user_is_bot_by , etc. with the relative *_historical counterpart. If fresh, up to date data is necessary it is possible to self join the table and get the most recent information from the *_historical column.
  • apply COALESCE to revision_is_identity_reverted .
  • event_timestamp is now a timestamp field, therefore apply the right semantic to the filters. Additionally rework all the time-window filters to the right granularity.

Pipeline

  1. EventBus produces the MediaWiki event streams this table is built from — mediawiki.page_change.v1 , mediawiki.revision_tags_change, mediawiki.revision_visibility_change and mediawiki.user_change — to eventgate and eventually to Kafka.
  2. These events are consumed and processed into Data Lake Hive event tables via Hadoop Event Ingestion .
  3. Every day, a Spark job ( MWHistoryDeltaWriter via Airflow DAG merge_events_to_mw_incremental_history_daily ) reads one calendar day of these events, maps them to the table schema, runs identity-revert detection, applies namespace-content and bot classification, and MERGEs the result as source='events' rows. The same run also patches existing rows in place — including source='snapshot' rows — when that day's visibility changes, page deletions, reverts, or change tags target revisions already in the table.
  4. Every month, after a new wmf.mediawiki_history snapshot is available, a Spark job ( MWHistorySnapshotMerger , via Airflow DAG merge_mw_history_to_mw_incremental_history_monthly ) lays down the authoritative baseline: it deletes the prior source='snapshot' rows (and the source='events' rows they now supersede) and inserts a fresh projection of wmf.mediawiki_history as source='snapshot' rows.
  5. The daily job for a given month is gated on the monthly reconcile of the previous month — it blocks at the start of the month until that authoritative baseline has landed, so no back-dated events are lost. Although this gating helps data correctness, the compromise is that daily runs near the start of the months will likely come in 24h+ late.
  6. Both jobs run on Spark 3.5.8 / Iceberg, write through a per-run Iceberg branch that is fast-forwarded to main on success (write-audit-publish, so readers never see a half-written state), and are serialized through a single-slot mutex pool because they write the same table.
Retrieved from " https://wikitech.wikimedia.org/w/index.php?title=Data_Platform/Data_Lake/Edits/Mediawiki_history_incremental_v1&oldid=2428960 "