Hard dependencies are those that make querying the data impossible when down (Hive, HDFS, ...)
Soft dependencies are those that make updating the data impossible when down (Kafka, Airflow, Kubernetes, ...)

In this task, we should incorporate those updates, ideally with concrete proposals for the X, Y, W and Z based on current performance, for review in our hypothesis sync a couple of weeks from now.

Details

Other Assignee: xcollazo

Related Changes in Gerrit:

	Subject	Author	Repo	Branch	Lines +/-
	pyrra: update the MWHC SLO	Elukey	operations/puppet	production	+11 -4
	Pyrra: add the MWH completeness SLO under Data Platform	Elukey	operations/puppet	production	+25 -0

Customize query in gerrit

Related Objects

Mentioned In: T420787: Visualizing inconsistencies and reconciles via Superset
Mentioned Here: T392499: Post-creation work for rkiwiki
P82117 Freshness of wmf_content.mediawiki_content_history_v1
P82116 Completeness of wmf_content.mediawiki_content_history_v1

Event Timeline

GGoncalves-WMF created this task.Aug 14 2025, 9:24 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 14 2025, 9:24 AM

GGoncalves-WMF moved this task from Incoming (new tickets) to Q1 FY25/26 July 1st - September 30th on the Data-Engineering board.Aug 14 2025, 9:33 AM

GGoncalves-WMF edited projects, added: Data-Engineering (Q1 FY25/26 July 1st - September 30th); removed: Data-Engineering.

Over at https://wikitech.wikimedia.org/wiki/SLO/MediaWiki_Content_Pipelines we talk about the following SLOs:

correctness: ensures that we are alerted about potential data errors in dumps data
freshness: ensures that we are alerted if data is not delivered on time.

But in this ticket we have:

Completeness: At least X% of revisions are present in MWCH, Y% of the time.
Freshness: W% of revision changes propagate to MWCH within Z% of the time.

Should we pursue correctness, completeness, or both?

Here are some calculations to inform discussion.

We can calculate total completeness by doing something like this:

spark.sql("""
WITH content_counts AS (
  SELECT 
         count(1) as total_count,
         'all-wikis' as wiki_id
  FROM wmf_content.mediawiki_content_history_v1
),

inconsistencies_counts AS (
  SELECT 
         count(1) as inconsistencies_count,
         'all-wikis' as wiki_id
  FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE computation_class = 'all-of-wiki-time'
    AND computation_dt = '2025-08-01'
)

SELECT total_count,
       COALESCE(inconsistencies_count, 0) as inconsistencies_count,
       1 - (COALESCE(inconsistencies_count, 0) / total_count) AS revision_completeness,
       cc.wiki_id
FROM content_counts cc
LEFT JOIN inconsistencies_counts ic ON (cc.wiki_id = ic.wiki_id)
ORDER BY revision_completeness ASC
""").show(1500, truncate=False)

+-----------+---------------------+---------------------+---------+
|total_count|inconsistencies_count|revision_completeness|wiki_id  |
+-----------+---------------------+---------------------+---------+
|7381266896 |3072885              |0.9995836913847859   |all-wikis|
+-----------+---------------------+---------------------+---------+

So as of right now we have 99.96% of the data in the datalake table. However, this number is over all wikis. Let's segregate per wiki:

spark.sql("""
WITH content_counts AS (
  SELECT 
         count(1) as total_count,
         wiki_id
  FROM wmf_content.mediawiki_content_history_v1
  GROUP BY wiki_id
),

inconsistencies_counts AS (
  SELECT 
         count(1) as inconsistencies_count,
         wiki_id
  FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE computation_class = 'all-of-wiki-time'
    AND computation_dt = '2025-08-01'
  GROUP BY wiki_id
)

SELECT total_count,
       COALESCE(inconsistencies_count, 0) as inconsistencies_count,
       1 - (COALESCE(inconsistencies_count, 0) / total_count) AS revision_completeness,
       cc.wiki_id
FROM content_counts cc
LEFT JOIN inconsistencies_counts ic ON (cc.wiki_id = ic.wiki_id)
ORDER BY revision_completeness ASC
""").show(1500, truncate=False)

+-----------+---------------------+---------------------+--------------------+
|total_count|inconsistencies_count|revision_completeness|wiki_id             |
+-----------+---------------------+---------------------+--------------------+
|3389       |12485                |-2.6839775745057537  |nupwiki             |
|10325      |1639                 |0.8412590799031476   |knwikiquote         |
|278996     |35404                |0.873102123327933    |igwiki              |
|40709      |2226                 |0.945319216880788    |nywiki              |
|430288     |12825                |0.9701943814375488   |bnwiktionary        |
|36301      |614                  |0.9830858654031569   |viwikiquote         |
|36657      |386                  |0.9894699511689445   |ukwikivoyage        |
|50455      |449                  |0.9911009810722426   |jawikivoyage        |
|2096       |12                   |0.9942748091603053   |apiportalwiki       |
|162559     |818                  |0.9949679808561814   |slwiktionary        |
|86244      |425                  |0.9950721209591392   |uawikimedia         |
|382883     |1798                 |0.9953040484952322   |zh_classicalwiki    |
|75320      |330                  |0.995618693574084    |gpewiki             |
|4753       |19                   |0.9960025247212287   |cywikiquote         |
|144595     |564                  |0.9960994501884575   |kaawiki             |
|135248382  |499418               |0.996307401296675    |ruwiki              |
|201262     |734                  |0.9963530124911807   |trwikiquote         |
|1759       |6                    |0.9965889710062535   |cvwikibooks         |
|287409     |976                  |0.9966041425285916   |mnwwiktionary       |
|80100      |272                  |0.9966042446941323   |fiwikiquote         |
|46632      |155                  |0.9966761022473838   |thwikibooks         |
|106343     |352                  |0.9966899560854969   |viwikivoyage        |
|32452      |102                  |0.9968568963392087   |tawikiquote         |
...

We now see that things are more nuanced. But still, by the 8th rank we are already at 99%. Notice that the wikis with high incompleteness are also very small (i.e. young) wikis. Full output at P82116.

However, completeness over all of time doesn't tell us much about the last 24 hours, which tend to be where most of the inconsistencies lay.

For freshness, we could use a similar mechanism as before, but we can look at the detected inconsistencies over the last consumed 24 hour period:

spark.sql("""
WITH content_counts AS (
  SELECT 
         count(1) as total_count,
         'all-wikis' as wiki_id
  FROM wmf_content.mediawiki_content_history_v1
  WHERE revision_dt >= '2025-08-28'
    AND revision_dt  < '2025-08-29'
),

inconsistencies_counts AS (
  SELECT 
         count(1) as inconsistencies_count,
         'all-wikis' as wiki_id
  FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE computation_class = 'last-24h'
    AND computation_dt = '2025-08-29'
)

SELECT total_count,
       COALESCE(inconsistencies_count, 0) as inconsistencies_count,
       1 - (COALESCE(inconsistencies_count, 0) / total_count) AS revision_completeness,
       cc.wiki_id
FROM content_counts cc
LEFT JOIN inconsistencies_counts ic ON (cc.wiki_id = ic.wiki_id)
ORDER BY revision_completeness ASC
""").show(1500, truncate=False)
                                                                                
+-----------+---------------------+---------------------+---------+
|total_count|inconsistencies_count|revision_completeness|wiki_id  |
+-----------+---------------------+---------------------+---------+
|1404404    |4498                 |0.996797217894566    |all-wikis|
+-----------+---------------------+---------------------+---------+

So in this case we can see we detected 4498 inconsistencies in the last ingest, putting us at "99.6% of changes propagated within 24 hours."

Drilling down:

spark.sql("""
WITH content_counts AS (
  SELECT 
         count(1) as total_count,
         wiki_id
  FROM wmf_content.mediawiki_content_history_v1
  WHERE revision_dt >= '2025-08-28'
    AND revision_dt  < '2025-08-29'
  GROUP BY wiki_id
),

inconsistencies_counts AS (
  SELECT 
         count(1) as inconsistencies_count,
         wiki_id
  FROM wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE computation_class = 'last-24h'
    AND computation_dt = '2025-08-29'
  GROUP BY wiki_id
)

SELECT total_count,
       COALESCE(inconsistencies_count, 0) as inconsistencies_count,
       1 - (COALESCE(inconsistencies_count, 0) / total_count) AS revision_completeness,
       cc.wiki_id
FROM content_counts cc
LEFT JOIN inconsistencies_counts ic ON (cc.wiki_id = ic.wiki_id)
ORDER BY revision_completeness ASC
""").show(1500, truncate=False)

+-----------+---------------------+---------------------+-----------------+
|total_count|inconsistencies_count|revision_completeness|wiki_id          |
+-----------+---------------------+---------------------+-----------------+
|4          |1818                 |-453.5               |rkiwiki          |
|8          |4                    |0.5                  |hewiktionary     |
|15         |1                    |0.9333333333333333   |cswikivoyage     |
|20         |1                    |0.95                 |scowiki          |
|42         |2                    |0.9523809523809523   |lijwiki          |
|22         |1                    |0.9545454545454546   |zhwikinews       |
|404        |14                   |0.9653465346534653   |labswiki         |
|29         |1                    |0.9655172413793104   |siwiki           |
|29         |1                    |0.9655172413793104   |sewikimedia      |
|40         |1                    |0.975                |mznwiki          |
|459        |11                   |0.9760348583877996   |bewiki           |
|237        |5                    |0.9789029535864979   |viwiktionary     |
|48         |1                    |0.9791666666666666   |knwiki           |
|49         |1                    |0.9795918367346939   |igwiki           |
|4418       |70                   |0.98415572657311     |fawiki           |
|531        |8                    |0.9849340866290018   |zh_yuewiki       |
|423        |6                    |0.9858156028368794   |dewikisource     |
|81         |1                    |0.9876543209876543   |dewikibooks      |
|2372       |29                   |0.9877740303541316   |srwiki           |
|333        |4                    |0.987987987987988    |enwikivoyage     |
|187        |2                    |0.9893048128342246   |wuuwiki          |
|103        |1                    |0.9902912621359223   |kswiki           |
|4746       |44                   |0.9907290349768226   |idwiki           |
...

Again, we can see that some wikis are highly impacted if we see it percentage wise, but we quickly move to 99% for all rest of 1000+ wikis. (Note rkiwiki was very recently created so high inconsistency is expected (see T392499)). Full output at P82117.

(Side note: now that I'v been playing with these numbers, I think it would be interesting to repeated our reconcile over data that is between 48 and 24 hours old, that is: a whole day old, and see whether our first reconcile indeed fixed 100% of the issues?)

Excellent, thanks for coming up with this! It's very cool to see the performance of the real pipeline. My thoughts the initial question:

Should we pursue correctness, completeness, or both?

I think completeness is what best models the expectations of the consumers of MWCH: we should be explicit about how much of the upstream data they can expect to find.

But I also think that what the document calls "correctness" is very related to our "completeness". Looking for "correctness" in the current draft:

"correctness: ensures that we are alerted about potential data errors in dumps data"
- I think we can distinguish between the SLO and its alerting. We can alert various pipeline errors, or latencies, or on approximations of the SLIs. All of these help us ultimately uphold our correctness/completeness SLO.
"[The] number of missing events over a period of time (...) informs the correctness of data dumps by tracking events that are present in MariaDB but missing in the target table."
- This is equivalent to our completeness, as I understand it.
"These SLIs are focused on ensuring operational correctness of the MediaWiki Content History system. Additional guarantees on the wmf_content.mediawiki_content_history table correctness are covered by Data Quality Metrics reporting and alerting."
- This is probably the crucial difference: the draft draws a line between "operational" and "table" (semantic) correctness, and proposes an SLO for the former only. I think this can be a totally valid distinction to make, particularly when the data product has a known owner for the semantic properties. For this hypothesis, however, I think it's more beneficial to both DPE and MWCH consumers to offer a pragmatic SLO that is more on the semantic side, hence the "completeness".

Also...

However, completeness over all of time doesn't tell us much about the last 24 hours, which tend to be where most of the inconsistencies lay.

In a way, the freshness SLO is the "completeness for the last 24h", right?

xcollazo moved this task from Next Up to In progress on the Data-Engineering (Q1 FY25/26 July 1st - September 30th) board.Sep 2 2025, 1:58 PM

BTullis subscribed.Sep 9 2025, 9:57 AM

xcollazo moved this task from In progress to Blocked/Paused on the Data-Engineering (Q1 FY25/26 July 1st - September 30th) board.Sep 29 2025, 4:32 PM

Ahoelzl edited projects, added: Data-Engineering (Q2 FY25/26 October 1st - December 31th); removed: Data-Engineering (Q1 FY25/26 July 1st - September 30th).Oct 9 2025, 12:20 AM

Ahoelzl moved this task from Next Up to Blocked/Paused on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.

APizzata-WMF subscribed.Oct 21 2025, 1:41 PM

What about the source data we ingest?
I was thinking that a possible completeness measure should include also the source event.mediawiki_page_content_change_v1 table and union it with the wmf_content.inconsistent_rows_of_mediawiki_content_history_v1. Something like this:

WITH content_history AS (
  -- select all the info for the wiki in the time frame
  SELECT 
    wiki_id, page_id, revision_id
  FROM 
    wmf_content.mediawiki_content_history_v1
  WHERE 
      wiki_id='dewiki'
     AND date(revision_dt) BETWEEN '2025-10-21' AND '2025-10-22'
),
count_content_history AS (
  -- count previous info 
  SELECT 
    count(*) AS count
  FROM 
    content_history
),
source_data AS (
  -- select all the info from the two source tables and tag them for future paritioning
  SELECT 
    distinct wiki_id, page['page_id'] AS page_id, revision['rev_id'] AS revision_id,  'event' AS source
  FROM 
    event.mediawiki_page_content_change_v1
  WHERE 
    wiki_id='dewiki' 
    AND YEAR=2025 
    AND MONTH=10 
    AND DAY =22
  UNION ALL 
  SELECT 
    wiki_id, page_id, revision_id, 'revision' AS source
  FROM 
    wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE 
    wiki_id='dewiki'
    AND date(computation_dt) = '2025-10-22' 
    AND computation_class= 'last-24h'
),
missing AS (
  -- selecting the counts for each source for the missing data
  SELECT 
    count(s.*) AS count, s.source
  FROM 
    source_data s
  LEFT JOIN 
    content_history c 
  ON s.wiki_id=c.wiki_id
  AND s.page_id=c.page_id
  AND s.revision_id=c.revision_id
  WHERE 
    -- where condition to select the missing rows
    c.wiki_id IS NULL
    OR c.page_id IS NULL
    OR c.revision_id IS NULL
  GROUP BY source
),
joined_counts AS (
  -- selecting the total counts by source type and joining with the total content_history count
  SELECT 
    max(
      CASE
        WHEN m.source ='event' THEN m.count
        ELSE 0
      END) AS count_event_missing,
    max(
      CASE
        WHEN m.source ='revision' THEN m.count
        ELSE 0
      END) AS count_revision_missing,
    c.count AS count_content
  FROM 
    missing m
  JOIN 
    count_content_history c
  GROUP BY c.count)
SELECT
  -- create the final output with the ration of missing rows
  coalesce(count_event_missing, 0) AS count_event_missing,
  coalesce(count_revision_missing, 0) AS count_revision_missing,
  count_content,
  1- ((coalesce(count_event_missing, 0)+ coalesce(count_revision_missing, 0))/count_content) AS ratio
FROM 
  joined_counts
UNION ALL
-- this in case everything is present in the content_history selection and therefore the ration is 1
SELECT 
  0 AS count_event_missing,
  0 AS count_revision_missing,
  c.count AS count_content,
  1 AS ratio
FROM 
  count_content_history c
WHERE 
  NOT EXISTS (SELECT 1 FROM joined_counts)

This query returns:

+-------------------+----------------------+-------------+------------------+
|count_event_missing|count_revision_missing|count_content|             ratio|
+-------------------+----------------------+-------------+------------------+
|               1523|                    16|        48628|0.9683515669984372|
+-------------------+----------------------+-------------+------------------+

I feel this could be useful to understand when we don't ingest everything from our source table and gives a better idea of completeness.

Great @APizzata-WMF, let's discuss your query, and perhaps study those 1523 missing events at our next sync up.

APizzata-WMF claimed this task.Oct 29 2025, 5:40 PM

APizzata-WMF updated Other Assignee, added: xcollazo.

APizzata-WMF added a subscriber: xcollazo.

I have updated the qeury:

from datetime import datetime, timezone,timedelta
wiki_id_name= 'dewiki'

utc_now= datetime.now(timezone.utc)
utc_yesterday = utc_now - timedelta(days=1)
year = utc_yesterday.year
month = utc_yesterday.month
day = utc_yesterday.day
year_today=utc_now.year
month_today=utc_now.month
day_today= utc_now.day
#mode = 'freshness'
mode= 'completeness'
mediawiki_filter = "AND( date(revision_dt) = current_date()-1 or date(row_visibility_update_dt) = current_date()-1)" if mode== 'freshness'  else ("" if  mode == 'completeness' else "")
hive_filter= f"AND YEAR={year} AND MONTH={month} AND DAY={day}" if mode == 'freshness' else (f"AND YEAR!={year_today} AND MONTH!={month_today} AND DAY!={day_today}" if  mode == 'completeness' else "")
revision_filter = "AND date(computation_dt) =current_date()-1" if mode=='freshness'  else ("AND date(computation_dt) !=current_date()" if  mode == 'completeness' else "")
_spark.sql(f"""
    WITH content_history AS (
  -- select all the info for the wiki in the time frame
  SELECT 
    wiki_id, page_id, revision_id
  FROM 
    wmf_content.mediawiki_content_history_v1
  WHERE 
      wiki_id='{wiki_id_name}' 
      {mediawiki_filter}
),
count_content_history AS (
  -- already count previous info 
  SELECT 
    count(*) AS count
  FROM 
    content_history
),
source_data AS (
  -- select all the info from the two source tables and tag them for future paritioning
  SELECT 
    wiki_id, page['page_id'] AS page_id, revision['rev_id'] AS revision_id,  '1. event' AS source
  FROM 
    event.mediawiki_page_content_change_v1
  WHERE 
    wiki_id='{wiki_id_name}'
    {hive_filter}
    AND page_change_kind IN ('create', 'edit', 'move')
  UNION ALL 
  SELECT 
    wiki_id, page_id, revision_id, '3. revision' AS source
  FROM 
    wmf_content.inconsistent_rows_of_mediawiki_content_history_v1
  WHERE 
    wiki_id='{wiki_id_name}'
    {revision_filter}
    AND computation_class= 'last-24h'
  UNION ALL 
  SELECT
      database, page_id,rev_id, '2. visibility_change' AS source
  FROM 
    event.mediawiki_revision_visibility_change
  WHERE 
    database='{wiki_id_name}'
    {hive_filter}
),
deduplicated_source_data as(
    select * from(
    select *, row_number() over(partition by wiki_id, page_id, revision_id order by source desc) as rn
    from source_data
    ) where rn =1
),
deleted_pages AS (
  SELECT
    wiki_id, page['page_id'] AS page_id
  FROM
    event.mediawiki_page_content_change_v1
  WHERE 
    wiki_id='{wiki_id_name}'
    {hive_filter}
    AND page_change_kind = 'delete'
),
missing AS (
  -- selecting the counts
  SELECT 
    count(s.*) AS count, s.source
  FROM 
    deduplicated_source_data s
  LEFT JOIN 
    content_history c 
  ON s.wiki_id=c.wiki_id
  AND s.page_id=c.page_id
  AND s.revision_id=c.revision_id
  LEFT JOIN
      deleted_pages dp
  ON s.wiki_id=dp.wiki_id
  AND s.page_id=dp.page_id
  WHERE 
    (c.wiki_id IS NULL
    OR c.page_id IS NULL
    OR c.revision_id IS NULL)
    AND dp.page_id IS NULL
  GROUP BY source
),
joined_counts AS (
  SELECT 
    max(
      CASE
        WHEN m.source ='1. event' THEN m.count
        ELSE 0
      END) AS count_event_missing,
    max(
      CASE
        WHEN m.source ='3. revision' THEN m.count
        ELSE 0
      END) AS count_revision_missing,
    max(
      CASE
        WHEN m.source ='2. visibility_change' THEN m.count
        ELSE 0
      END) AS count_visibility_change_missing,
    c.count AS count_content
  FROM 
    missing m
  JOIN 
    count_content_history c
  GROUP BY c.count)
SELECT 
  '{wiki_id_name}' as wiki_id,
  '{mode}' as check_type,
  coalesce(count_event_missing, 0) AS count_event_missing,
  coalesce(count_revision_missing, 0) AS count_revision_missing,
  coalesce(count_visibility_change_missing, 0) AS count_visibility_change_missing,
  count_content,
  cast(1- ((coalesce(count_event_missing, 0)+ coalesce(count_revision_missing, 0)+ coalesce(count_visibility_change_missing, 0))/count_content) as decimal(10,8)) AS ratio,
  current_timestamp() as execution_dt
FROM 
  joined_counts
UNION ALL
SELECT 
  '{wiki_id_name}' as wiki_id,
  '{mode}' as check_type,
  0 AS count_event_missing,
  0 AS count_revision_missing,
  0 AS count_visibility_change_missing,
  c.count AS count_content,
  1 AS ratio,
  current_timestamp() as execution_dt
FROM 
  count_content_history c
WHERE 
  NOT EXISTS (SELECT 1 FROM joined_counts)
""").show()

It runs on wiki_id level and applies different filters depending if it is in completeness mode or freshness mode.
Here are some results and comparison with what @xcollazo proposed:

+-------+------------+-------------------+----------------------+-------------------------------+-------------+-----------+---------------------+
|wiki_id|  check_type|count_event_missing|count_revision_missing|count_visibility_change_missing|count_content|      ratio|         execution_dt|
+-------+------------+-------------------+----------------------+-------------------------------+-------------+-----------+---------------------+
| dewiki|completeness|                  0|                  5689|                              0|    238782673| 0.99997617| 2025-10-31 10:24:...|
+-------+------------+-------------------+----------------------+-------------------------------+-------------+-----------+---------------------+
| dewiki|freshness   |                  0|                     0|                              0|        24706|     1.0000| 2025-10-31 10:24:...|
+-------+------------+-------------------+----------------------+-------------------------------+-------------+-----------+---------------------+
| itwiki|completeness|                  0|                  2981|                              0|    138035666| 0.99997840| 2025-10-31 14:40:...|
+-------+------------+-------------------+----------------------+-------------------------------+-------------+-----------+---------------------+
| itwiki|freshness   |                  0|                     0|                              0|        13908|     1.0000| 2025-10-31 14:38:...|
+-------+------------+-------------------+----------------------+-------------------------------+-------------+-----------+---------------------+

+-----------+---------------------+---------------------+-------+
|total_count|inconsistencies_count|revision_completeness|wiki_id|
+-----------+---------------------+---------------------+-------+
|23446      |86                   |0.9963319969291137   |dewiki | (freshness)
+-----------+---------------------+---------------------+-------+
|238782673  |58550                |0.99975479795387     |dewiki | (completeness)
+-----------+---------------------+---------------------+-------+
|13626      |44                   |0.9967708792015265   |itwiki | (freshness)
+-----------+---------------------+---------------------+-------+ 
|138035666  |19115                |0.9998615212969668   |itwiki |(completeness)
+-----------+---------------------+---------------------+-------+

I feel that just counting the rows in the wmf_content.inconsistent_rows_of_mediawiki_content_history_v1 does not represent the real status of the table due to:

rows showed in the count could have been actually ingested in the event.mediawiki_page_content_change_v1 and therefore are available in the final wmf_content.mediawiki_content_history_v1 table.
this count does not allow us to take action in improving the quality of the table since they are not really representative of the status.

Additionally, in the freshness calculation I am taking in account the row_visibility_update_dt to consider also the most recent visibility changes in the count. This of course changes the total count in the freshness metric but I feel that this is more accurate.

Finally, the SLO metrics would be an aggregation of all the wikis and the ratio would be calculated in the same way on the new aggregated results.

Draft values for X, Y, W and Z could be:
X: 0.95
Y: all time
W: 0.95
Z: 24 hr

Let me know what you think about these results!
Next actions would be runinning ont he whole set of wikis and get how much time the whole process takes.

I have created the P84877 with the results of the query. I have also changed the final columns to fit a more generic approach and not specialised for the mwch. The query now also runs on group by wiki_id and there is no more reason to iterate over them.
The spark configuration tested is the following:

config = {
            "spark.driver.memory": "16g",
            "spark.driver.cores": 4,
            "spark.driver.maxResultSize": "8g",
            "spark.dynamicAllocation.maxExecutors": 32,
            "spark.executor.memory": "16g",
            "spark.executor.cores": 2,
            "spark.sql.shuffle.partitions": 512,
}

I feel it could be reduced in the final development stages. Please lmk what you think about this!

updated the document: https://wikitech.wikimedia.org/wiki/SLO/MediaWiki_Content_History_Table

APizzata-WMF moved this task from Blocked/Paused to In progress on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.Nov 21 2025, 11:02 AM

Action items on the previous linked document:

AP to expand regarding the testing and reason behind the 99.
AP to change to 99.5 or 99.7 and provide how many days of outage would bring us to that
AP to change the definition: remove D-1, provide a formula if needed and change the wording of backward
AP to change the title of the SLI to just completeness \
AP to change the hard and soft dependencies (letter B of GG list)
GG to rework the client facing section
AP to remove the TBD in the client facing section
AP to remove the Data Quality comment to avoid more confusion and future questions
AP to change from 90 days to 12 w
AP to redraw the system diagram and post it in the Architectural section

All the action items from my side have been published on the document

Change #1218226 had a related patch set uploaded (by Elukey; author: Elukey):

[operations/puppet@production] Pyrra: add the MWH completeness SLO under Data Platform

https://gerrit.wikimedia.org/r/1218226

gerritbot added a project: Patch-For-Review.Dec 15 2025, 11:12 AM

APizzata-WMF moved this task from In progress to Ready to Deploy on the Data-Engineering (Q2 FY25/26 October 1st - December 31th) board.Dec 15 2025, 4:36 PM

Change #1218226 merged by Elukey:

[operations/puppet@production] Pyrra: add the MWH completeness SLO under Data Platform

https://gerrit.wikimedia.org/r/1218226

Maintenance_bot removed a project: Patch-For-Review.Dec 16 2025, 1:30 PM

Me and Antonio merged the Pyrra changes to create the new dashboard, but the results are not great..

Pyrra assumes that both error and total metrics are counters, and behind the scenes it creates a recording rule for each of the following:

sum(increase(wmf_content_mediawiki_content_history_v1_completeness_sli_errors[4w]))

sum(increase(wmf_content_mediawiki_content_history_v1_completeness_sli_total[4w]))

(You can see them plotted here)

Increase takes a window, 4 weeks in this case, and for each datapoint it removes the value that it had a month ago. In the MWCH use case, total increases very little daily (orders of millions, while its absolute value is orders of magnitude more) and errors grows with the same order of magnitude. So the ratio between the two makes it look like errors are dominant, when they are not.

We'd need to come up with a different strategy, for example a simple one could be to use as total the number of days from $beginning (basically a simple +1 everyday) and errors a counter that increases if during one day, completeness is not met. In this way we'd compare two simpler counters, that should be measurable via Pyrra.

Uploaded a new set of metrics with the following names:

wmf_content_mediawiki_content_history_v1_completeness_sli_days: counter of the days that the metric has been executed
wmf_content_mediawiki_content_history_v1_completeness_sli_alerts: count of alerts in case the completeness of the table is under the SLO threshold.

They are current being published to Prometheus.
Since the day metric is not starting from 1 @elukey and I have decided to test how Pyrra behaves with these data points. If it will be necessary in the future I can recompute the metric for past days or simply reset the day counter.

I have also updated the SLO document describing these new developments.

Ahoelzl edited projects, added: Data-Engineering (Q3 FY25/26 January 1st - March 31th); removed: Data-Engineering (Q2 FY25/26 October 1st - December 31th).Jan 8 2026, 4:34 PM

Ahoelzl moved this task from Next Up to Ready to Deploy on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.

Change #1225594 had a related patch set uploaded (by Elukey; author: Elukey):

[operations/puppet@production] pyrra: update the MWHC SLO

https://gerrit.wikimedia.org/r/1225594

gerritbot added a project: Patch-For-Review.Jan 12 2026, 3:39 PM

Change #1225594 merged by Elukey:

[operations/puppet@production] pyrra: update the MWHC SLO

https://gerrit.wikimedia.org/r/1225594

Change deployed, new dashboard up!

Maintenance_bot removed a project: Patch-For-Review.Jan 12 2026, 4:30 PM