TL;DR; the data pipeline up to AQS seems fine, my guess is we're not filtering properly to exclude redirects in AQS 2, timeline corresponds with the reported problem. Sorry for the inconvenience, working on a fix.

Indeed, the numbers are as @Spnq reports here:

mysql:research@dbstore1007.eqiad.wmnet [lvwiki]> select page_namespace, page_is_redirect, count(*) count from page group by page_namespace, page_is_redirect order by count desc;

So the problem must be coming later down the pipe. Checking mediawiki history reduced, the source dataset:

unfiltered, mediawiki_history_reduced seems to agree:

select snapshot,
       page_type,
       count(*) as count
  from mediawiki_history_reduced
 where snapshot in ('2023-06', '2023-07', '2023-08', '2023-09', '2023-10', '2023-11', '2023-12')
   and project = 'lv.wikipedia'
   and event_entity = 'page'
   and event_type = 'create'
 group by snapshot, page_type
 order by snapshot, page_type

But the other_tags field is supposed to help with this, to filter out redirects, and I remember we ran into this during testing but I thought we had fixed it. Indeed splitting by is_redirect brings the count of content pages much closer to what we would expect, namely 121053:

select snapshot,
       contains(other_tags, 'redirect') as is_redirect,
       count(*) as count
  from mediawiki_history_reduced
 where snapshot in ('2023-06', '2023-07', '2023-08', '2023-09', '2023-10', '2023-11', '2023-12')
   and project = 'lv.wikipedia'
   and event_entity = 'page'
   and event_type = 'create'
   and page_type = 'content'
 group by snapshot, contains(other_tags, 'redirect')
 order by snapshot, is_redirect;

So the problem then is how we're querying Druid. I'll look in AQS 2.0 code next.

Taking a look at the code, this issue seems to be related with something similar we found in editor-analytics some months ago (https://gerrit.wikimedia.org/r/c/generated-data-platform/aqs/editor-analytics/+/973139). We had to change the way we considered other_tags to filter properly and we didn't do the same for edit-analytics at that moment.

We have already made this change locally in edit-analytics and it seems to work fine with our local test environment. We're going to push a patch to the repo to be able to deploy to the staging environment to be able to test it using production data. If it works, we'll be sure that we can deploy the change to production.

Change 987965 had a related patch set uploaded (by Santiago Faci; author: Santiago Faci):

[generated-data-platform/aqs/edit-analytics@main] Incorrect values when requesting edited-pages/new endpoint

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

Change 987965 merged by jenkins-bot:

[generated-data-platform/aqs/edit-analytics@main] Incorrect values when requesting edited-pages/new endpoint

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

Change 987967 had a related patch set uploaded (by Santiago Faci; author: Santiago Faci):

[operations/deployment-charts@master] deploying a new edit-analytics version to staging environment

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

Change 987967 merged by jenkins-bot:

[operations/deployment-charts@master] deploying a new edit-analytics version to staging environment

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

After merging the patch and deploy the service to the staging environment (where we can make requests against production data) we were able to confirm that the fix is working fine. We got the same values as Dan got querying directly the source dataset.

Next Monday we will deploy the service to production.

Change 988486 had a related patch set uploaded (by Santiago Faci; author: Santiago Faci):

[operations/deployment-charts@master] Deploying edit-analytics to production

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

Change 988486 merged by jenkins-bot:

[operations/deployment-charts@master] Deploying edit-analytics to production

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

At this moment the fix is already deployed to production and it seems to work fine

The "Content page" count now looks to be correct 👍

But now there's another issue with the page count - stats shows that the total number of pages to date is 337k (https://stats.wikimedia.org/#/lv.wikipedia.org/content/pages-to-date/normal|line|2-year|~total|monthly), but in actuality it's a little over 500k -> the issue appears to be with the "Non-content" page count.

Maybe the same pages that previously were incorrectly counted as "Content" haven't been added to the "Non-content" page count?

P.S. Should I report this as a new bug or will this be resolved within this ticket?

Let us take a look. I think you're right and we are discarding always "redirect" pages from results. I think it's the reason you missed some pages from results when requesting 'non-content' or 'all-page-types'

I think we can keep this ticket to work in the issue. It's completely related to the previous one. We should have realized this before. We'll keep you posted here.

Change 989094 had a related patch set uploaded (by Santiago Faci; author: Santiago Faci):

[generated-data-platform/aqs/edit-analytics@main] Incorrect number of content pages on stats.wikimedia.org

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

Change 989094 merged by jenkins-bot:

[generated-data-platform/aqs/edit-analytics@main] Incorrect number of content pages on stats.wikimedia.org

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

Change 989098 had a related patch set uploaded (by Santiago Faci; author: Santiago Faci):

[operations/deployment-charts@master] Deploying to staging to test the fix with production data

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

Change 989098 merged by jenkins-bot:

[operations/deployment-charts@master] Deploying to staging to test the fix with production data

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

After pushing a new fix and testing it in the staging environment (where we have access to the production data), we are getting the following results:

• 124714 for 'content' pages (without considering 'redirect' ones) (124 918 shown in the Statistics page for 'Satura lapas'). This is what we fixed with the previous patch. The current value keeps being the right one.
• 503747 for 'all-page-types' pages which I think is what you mean when you say just 'pages' (504 410 is shown in the Statistic page for 'Lapas'). In this case, after the last patch, we are already considering 'redirect' cases for this count. This is what we are trying to fix right now and it seems to work fine.
• 225637 for 'non-content' pages (which coincides with the results than Dan got from Druid directly). In this case 'redirect' cases are also considered. This number keeps being the right one

So it seems results are working fine at this moment. Any concern about the current results? Just in case we are missing something.

I reviewed the data available to me and realised that I had previously misread some results. The data on Wiki Stats is indeed correct. Thank you, Sfaci!

Do you mean we don't need the last patch?

Which should be the right value for the total number of pages? I'm not sure if I'm understanding well. You said that wikistats was wrong because it's showing 337k and it should show around 500k (the same value that lv.wikipedia shows as "Lapas"). Could you confirm that?

Yeah, I thought that wikistats was wrong, because it said that there were around 300k pages instead of around 500k, but then I noticed that the description of that statistic states: "The running count of all pages created, excluding pages being redirects". So I assume that that's the reason for the difference.

So I, as a user, don't see the need for any additional fixes.

You are right! Statistics page in every wiki includes redirects when showing "Lapas" results but wikistats shouldn't do that in any case. Ok!, because the second patch was not deployed to production, everything is already done here and we can consider the initial bug as solved.

Thank you very much!

After discarding the last issue I have redeployed again the previous version in the staging environment. The original bug is solved and the service working fine in both environments (staging and production)

Change 990595 had a related patch set uploaded (by Santiago Faci; author: Santiago Faci):

[generated-data-platform/aqs/edit-analytics@main] Changing an integration test case according to the last issue we fixed

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

Change 990595 merged by jenkins-bot:

[generated-data-platform/aqs/edit-analytics@main] Changing an integration test case according to the last issue we fixed

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