MediaWiki Content History alerts too much for minor reconcile issues
Closed, InvalidPublic
Actions

Assigned To

Authored By

	xcollazo
	May 23 2025, 4:27 PM

Project Tags

Referenced Files

None

Subscribers

Description

On T384962: Implement alerting for wmf_content.mediawiki_content_history_v1, we setup alerting on a few data quality measures for wmf_content.mediawiki_content_history_v1.

Now that the system has been in production for a week, we have noticed many alerts regarding inconsistent rows:

presto> SELECT * FROM wmf_data_ops.data_quality_alerts WHERE partition_ts=CAST('2025-05-22 00:00:00' AS TIMESTAMP) AND map_key_exists(tags, 'project') AND tags['project']='mediawiki_content_history' AND status= 'Failure' ORDER BY partition_ts ASC;
                                          source_table                                          |                      partition_id                       |      partition>
------------------------------------------------------------------------------------------------+---------------------------------------------------------+--------------->
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=arwikisource/year=2025/month=5/day=22/hour=0    | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=dawiktionary/year=2025/month=5/day=22/hour=0    | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=bbcwiki/year=2025/month=5/day=22/hour=0         | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=cswikibooks/year=2025/month=5/day=22/hour=0     | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=arywiki/year=2025/month=5/day=22/hour=0         | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=bnwikisource/year=2025/month=5/day=22/hour=0    | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=cawikimedia/year=2025/month=5/day=22/hour=0     | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=dewikisource/year=2025/month=5/day=22/hour=0    | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=betawikiversity/year=2025/month=5/day=22/hour=0 | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=bnwiki/year=2025/month=5/day=22/hour=0          | 2025-05-22 00:>
 `wmf_content`.`inconsistent_rows_of_mediawiki_content_history_v1_mediawiki_content_history_v1` | wiki_id=azwikibooks/year=2025/month=5/day=22/hour=0     | 2025-05-22 00:>
(11 rows)

A cursory check suggests that these are all very small wikis alerting, so when it says ~10% of revisions need reconcile, it is ~10% out of ~1000 total revisions, etc. However. let's have a deeper look at it.

Details

Other Assignee: Antoine_Quhen

Related Changes in GitLab:

	Title	Reference	Author	Source Branch	Dest Branch
	analytics: pick up fix for page moves for mw content pipelines	repos/data-engineering/airflow-dags!1574	xcollazo	pickup-fix-moves-ingest	main
	Fix a bug where move events were not being consumed properly	repos/data-engineering/mediawiki-content-pipelines!74	xcollazo	fix-moves-ingest	main

Customize query in GitLab

Related Objects
Search...

Status	Assigned	Task
Open	None	T366752 Dumps 2.0 Phase III: Production level dumps (SDS 1.2)
In Progress	xcollazo	T384382 Production-level file export (aka dump) of MW Content in XML
Invalid	xcollazo	T395139 MediaWiki Content History alerts too much for minor reconcile issues

Event Timeline

xcollazo created this task.May 23 2025, 4:27 PM

Restricted Application added subscribers: Nemoralis, Aklapper. · View Herald TranscriptMay 23 2025, 4:27 PM

xcollazo added a parent task: T366752: Dumps 2.0 Phase III: Production level dumps (SDS 1.2).May 23 2025, 4:27 PM

Nemoralis unsubscribed.May 25 2025, 5:48 AM

xcollazo claimed this task.May 27 2025, 2:23 PM

xcollazo moved this task from Next Up to In progress on the Data-Engineering (Q4 2025 April 1st - June 30th) board.May 29 2025, 1:57 PM

@mforns and I looked at this yesterday.

We found a lead in the way of late arriving events, but the total amount of late events do not match the total amount of reconcile issues:

Reconcile issues per day:

spark.sql("""
SELECT count(1) as count,
       reasons
FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
WHERE computation_dt = CAST('2025-05-31' AS TIMESTAMP)
  AND computation_class = 'last-24h'
GROUP BY reasons
ORDER BY count DESC
""").show(truncate=False)


+-----+----------------------------------------------------------+
|count|reasons                                                   |
+-----+----------------------------------------------------------+
|12662|[missing_from_target]                                     |
|2393 |[missing_from_source]                                     |
|130  |[mismatch_content_visibility]                             |
|49   |[missing_from_target, page_was_restored]                  |
|10   |[mismatch_user_visibility, mismatch_comment_visibility]   |
|10   |[mismatch_page]                                           |
|10   |[mismatch_user_visibility]                                |
|7    |[mismatch_user_visibility, mismatch_content_visibility]   |
|6    |[mismatch_comment_visibility]                             |
|4    |[mismatch_content_visibility, mismatch_comment_visibility]|
+-----+----------------------------------------------------------+

Detected events that would trigger issues becase of lateness:

spark.sql("""
SELECT count(1) FROM (
SELECT meta.dt, revision.rev_dt
FROM event.mediawiki_page_change_v1
WHERE year = 2025
  AND month = 5
  AND day = 31
  -- AND hour = 0
  AND CAST(revision.rev_dt AS TIMESTAMP) >= CAST('2025-05-30' AS TIMESTAMP)
  AND CAST(revision.rev_dt AS TIMESTAMP) <  CAST('2025-05-31' AS TIMESTAMP)
ORDER BY revision.rev_dt ASC
)
""").show(1000, truncate=False)

+--------+
|count(1)|
+--------+
|927     |
+--------+

Investigations to be continued.

amastilovic subscribed.Jun 6 2025, 6:45 PM

xcollazo moved this task from In progress to Blocked/Paused on the Data-Engineering (Q4 2025 April 1st - June 30th) board.Jun 13 2025, 2:13 PM

xcollazo mentioned this in T397525: Duplicate rows on wmf_content.mediawiki_content_history_v1 after big reconcile.Jun 26 2025, 1:49 PM

xcollazo moved this task from Blocked/Paused to In progress on the Data-Engineering (Q4 2025 April 1st - June 30th) board.Jun 26 2025, 4:16 PM

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/74

Fix a bug where move events were not being consumed properly

In T395139#10969696, @CodeReviewBot wrote:

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/74

Fix a bug where move events were not being consumed properly

Here is the investigation that led to the proposed changes:

Recent daily inconsistencies hover around ~9K per day:

spark.sql("""
SELECT count(1) as count,
       computation_dt
FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
WHERE computation_dt > CAST('2025-06-25' AS TIMESTAMP)
  AND computation_class = 'last-24h'
GROUP BY computation_dt
ORDER BY computation_dt ASC
""").show(60, truncate=False)
[Stage 173:====================================================>(176 + 1) / 177]
+-----+-------------------+
|count|computation_dt     |
+-----+-------------------+
|7945 |2025-06-26 00:00:00|
|8276 |2025-06-27 00:00:00|
|7731 |2025-06-28 00:00:00|
|7363 |2025-06-29 00:00:00|
|9088 |2025-06-30 00:00:00|
|9483 |2025-07-01 00:00:00|
+-----+-------------------+

The vast majority of them being missing_from_target (that is, revisions that we see in the source MariaDB but are not reflected on the datalake table):

spark.sql("""
SELECT count(1) as count,
       reasons
FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
WHERE computation_dt = CAST('2025-07-01' AS TIMESTAMP)
  AND computation_class = 'last-24h'
GROUP BY reasons
ORDER BY count DESC
""").show(truncate=False)
[Stage 53:=====================================================>  (60 + 3) / 63]
+-----+------------------------------------------------------------------------------------+
|count|reasons                                                                             |
+-----+------------------------------------------------------------------------------------+
|8149 |[missing_from_target]                                                               |
|1246 |[missing_from_source]                                                               |
|51   |[missing_from_target, page_was_restored]                                            |
|8    |[mismatch_user_visibility]                                                          |
|8    |[mismatch_user_visibility, mismatch_comment_visibility]                             |
|7    |[mismatch_content_visibility, mismatch_comment_visibility]                          |
|6    |[mismatch_comment_visibility]                                                       |
|3    |[mismatch_content_visibility]                                                       |
|2    |[missing_from_source, page_was_deleted]                                             |
|2    |[mismatch_page]                                                                     |
|1    |[mismatch_user_visibility, mismatch_content_visibility, mismatch_comment_visibility]|
+-----+------------------------------------------------------------------------------------+

If we now join the inconsistencies table with event.mediawiki_page_change_v1, which is the source of most of our events, we see that we can match at least 6634 / 9483 = 60% of the inconsistencies:

df = spark.sql("""
WITH inconsistencies AS (
  SELECT *
  FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE computation_dt = CAST('2025-07-01' AS TIMESTAMP)
    AND computation_class = 'last-24h'
)

SELECT s.*
FROM event.mediawiki_page_change_v1 s
JOIN inconsistencies i ON (i.wiki_id = s.wiki_id AND i.revision_id = s.revision.rev_id)
  AND s.year = 2025
  -- one day behind computation_dt above
  AND s.month = 6
  AND s.day = 30 
""").cache()

df.count()                                                                       
6634

This means that 70% of the inconsistencies are indeed making it to the datalake, but we are not consuming them for some reason.

A further drill down shows that most of those ~70% inconsistencies are move events:

df.createOrReplaceTempView("source_match_w_inconsistencies")

spark.sql("""
SELECT count(1) as count,
       page_change_kind,
       changelog_kind
FROM source_match_w_inconsistencies
GROUP BY page_change_kind, changelog_kind
ORDER BY count DESC
""").show()
[Stage 78:======================================================> (60 + 2) / 62]
+-----+-----------------+--------------+
|count| page_change_kind|changelog_kind|
+-----+-----------------+--------------+
| 5235|             move|        update|
| 1109|             edit|        update|
|  268|           create|        insert|
|   13|visibility_change|        update|
|    5|           delete|        delete|
|    4|         undelete|        insert|
+-----+-----------------+--------------+

5235 / 9483 explains 55% of the missing events. So I looked at the code and lo and behold we were not consuming revisions related to moves for the latest revision.

From MR 74:

When consuming page change events related to page moves, we were applying the moves to the
page's history, but we were not consuming the page move, which is a revision in itself.

We now make sure to ingest the move event on process_revisions() only for the latest revision.
We want to do this there as that process is optimized for recent data.
Later, on process_page_moves() we ingest the same event again, but this time we apply it to all of the pages' revisions.
We want to do this there as that process is optimized for touching older data efficiently.

As per our analysis, this should solve about ~55% of the daily inconsistencies.

mforns moved this task from In progress to Blocked/Paused on the Data-Engineering (Q4 2025 April 1st - June 30th) board.Jul 7 2025, 4:38 PM

xcollazo moved this task from Blocked/Paused to In progress on the Data-Engineering (Q4 2025 April 1st - June 30th) board.Jul 8 2025, 11:59 PM

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1574

Draft: analytics: bump conda artifact for mw content pipelines

dr0ptp4kt subscribed.Jul 21 2025, 8:16 PM

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/74

Fix a bug where move events were not being consumed properly

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1574

analytics: pick up fix for page moves for mw content pipelines

Maintenance_bot removed a project: Patch-For-Review.Jul 23 2025, 4:31 PM

xcollazo moved this task from In progress to Blocked/Paused on the Data-Engineering (Q4 2025 April 1st - June 30th) board.Jul 25 2025, 5:46 PM

Ahoelzl edited projects, added: Data-Engineering (Q1 FY25/26 July 1st - September 30th); removed: Data-Engineering (Q4 2025 April 1st - June 30th).Jul 25 2025, 10:56 PM

Ahoelzl moved this task from Next Up to Blocked/Paused on the Data-Engineering (Q1 FY25/26 July 1st - September 30th) board.

Recent runs should show improvements after MR 1574, but they did not:

spark.sql("""
SELECT count(1) as count,
       computation_dt
FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
WHERE computation_dt > CAST('2025-07-15' AS TIMESTAMP)
  AND computation_class = 'last-24h'
GROUP BY computation_dt
ORDER BY computation_dt ASC
""").show(60, truncate=False)
[Stage 3:================================================>        (11 + 2) / 13]
+-----+-------------------+
|count|computation_dt     |
+-----+-------------------+
|11622|2025-07-16 00:00:00|
|7614 |2025-07-17 00:00:00|
|9882 |2025-07-18 00:00:00|
|15425|2025-07-19 00:00:00|
|10845|2025-07-20 00:00:00|
|8431 |2025-07-21 00:00:00|
|9843 |2025-07-22 00:00:00|
|10280|2025-07-23 00:00:00|
|9467 |2025-07-24 00:00:00|
|11443|2025-07-25 00:00:00|
|8689 |2025-07-26 00:00:00|
+-----+-------------------+

After thinking about it for a sec, we realized: we deployed to prod, but forgot to delete the Airflow Variable that holds the DAGProperties, thus we were running with the old artifact. I've just deleted this, ~~and will rerun a couple recent mw_content_reconcile_mw_content_history_daily runs.~~ the fix is upstream of this so we cannot just rerun. We need to wait a couple more days to see the results on new runs of the upstream DAGs.

Killed currently running one via:

$ kerberos-run-command analytics yarn application -kill application_1750705250302_1009708
Killing application application_1750705250302_1009708
25/07/29 18:20:09 INFO impl.YarnClientImpl: Killed application application_1750705250302_1009708

xcollazo edited parent tasks, added: T384382: Production-level file export (aka dump) of MW Content in XML; removed: T366752: Dumps 2.0 Phase III: Production level dumps (SDS 1.2).Aug 1 2025, 6:24 PM

After fixing the issue discussed in T395139#11044435, we can now see a sharp drop in inconsistencies since around 2025-08-01:

+-----+-------------------+
|count|computation_dt     |
+-----+-------------------+
|11622|2025-07-16 00:00:00|
|7614 |2025-07-17 00:00:00|
|9882 |2025-07-18 00:00:00|
|15425|2025-07-19 00:00:00|
|10845|2025-07-20 00:00:00|
|8431 |2025-07-21 00:00:00|
|9843 |2025-07-22 00:00:00|
|10280|2025-07-23 00:00:00|
|9467 |2025-07-24 00:00:00|
|11443|2025-07-25 00:00:00|
|8689 |2025-07-26 00:00:00|
|8228 |2025-07-27 00:00:00|
|10636|2025-07-28 00:00:00|
|8323 |2025-07-29 00:00:00|
|6146 |2025-07-30 00:00:00|
|7715 |2025-07-31 00:00:00|
|3409 |2025-08-01 00:00:00|
|7728 |2025-08-02 00:00:00|
|2371 |2025-08-03 00:00:00|
|3980 |2025-08-04 00:00:00|
|2769 |2025-08-05 00:00:00|
|2357 |2025-08-06 00:00:00|
|3692 |2025-08-07 00:00:00|
|3129 |2025-08-08 00:00:00|
|2456 |2025-08-09 00:00:00|
|3598 |2025-08-10 00:00:00|
|2977 |2025-08-11 00:00:00|
+-----+-------------------+

Let's compare 10 day averages from the 10 days before and after 2025-08-01:

spark.sql("""
SELECT avg(count) as avg_inconsistencies
FROM (
    SELECT count(1) as count,
           computation_dt
    FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
    WHERE computation_dt >= CAST('2025-07-21' AS TIMESTAMP)
      AND computation_dt <= CAST('2025-07-31' AS TIMESTAMP)
      AND computation_class = 'last-24h'
    GROUP BY computation_dt
    ORDER BY computation_dt ASC
)
""").show(60, truncate=False)
[Stage 24:====================================================>   (16 + 1) / 17]
+-------------------+
|avg_inconsistencies|
+-------------------+
|9018.272727272728  |
+-------------------+

spark.sql("""
SELECT avg(count) as avg_inconsistencies
FROM (
    SELECT count(1) as count,
           computation_dt
    FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
    WHERE computation_dt >= CAST('2025-08-01' AS TIMESTAMP)
      AND computation_dt <= CAST('2025-08-11' AS TIMESTAMP)
      AND computation_class = 'last-24h'
    GROUP BY computation_dt
    ORDER BY computation_dt ASC
)
""").show(60, truncate=False)
spark.sql("""
SELECT avg(count) as avg_inconsistencies
FROM (
    SELECT count(1) as count,
           computation_dt
    FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
    WHERE computation_dt >= CAST('2025-08-01' AS TIMESTAMP)
      AND computation_dt <= CAST('2025-08-11' AS TIMESTAMP)
      AND computation_class = 'last-24h'
    GROUP BY computation_dt
    ORDER BY computation_dt ASC
)
""").show(60, truncate=False)
[Stage 20:>                                                         (0 + 1) / 1]
+-------------------+
|avg_inconsistencies|
+-------------------+
|3496.909090909091  |
+-------------------+

Thus it looks like an effective (1 - (3496 / 9018)) * 100 = ~61% reduction of inconsistencies, which matches our estimate of ~55% as per T395139#10969747. 🎉 🎉 🎉