It is the same API. I took it from them.
Yes, we could find a way to export from one another.
For KAPOW, the data needs to be in a data lake table.

Merged! Thank you.

The MR has been merged.
The two datasets should be query ready every month moving forward.

It seems all data is being sqooped.
The globalimagelinks table was still not being repaired by the mediawiki_history_load DAG, so I added it there. Here's the MR:
https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2684
When this is merged, I think we can close this task.

Thank you @Novem_Linguae!
Note that a good part of the technical documentation will go on a private document, since it contains clues that could be used by bot net creators to bypass our bot detection system.
I will use Wikitech whenever we can, though.
🙏

I'm OK that wmf_traffic is used for both core and non-core traffic datasets, provided they are all Iceberg tables.
However, it seems inconvenient that dbt jobs owned by other teams generate data that is owned by the analytics service user (good point @xcollazo).

I created the legacy table,
copied all the existing production data into it,
vetted that the data copy looks good.
Deployed the query and the DAG and unpaused it.
The DAG is running and the new legacy table is being populated from pageview_actor.
I think this is done.

Merged! Should take effect after the next CIM run in a couple days.
Thank you!

@Isaac Here are 2 examples IIUC:

• A request issued by the mediawiki_browser_js software could be served by the mediawiki_desktop_web_ui or the mediawiki_mobile_web_ui or the mediawiki_api, depending on the uri of the request.
• Most requests issued by the apps software (commons_app, wikipedia_android_app, wikipedia_ios_app) would likely be served by the mediawiki_api interface.

Good question, we could maybe add this as part of the recurrent bot traffic audit?

I will take this, although I will prioritize KAPOW work.

@Ladsgroup hi! (Not sure if it's you who I should ask, if not sorry!) We Data Engineering are experiencing Sqoop failures because IIUC the new wiki is present in our allow-lists, but not yet in the wiki replicas.
We could remove the wiki from our side, but it seems it's not easy, because of: https://phabricator.wikimedia.org/T433009
Do you know if it will take a long time before the new wiki is in the wiki replicas?
And, for the future, could we delay the "Analytics Refinery" step in this type of tasks to do it only after the Wiki Replicas step is done?
Thank you 🙏🙏🙏

I gave a quick look, and here are the things that we could/should do:

• We should change the PageviewDefinition to include developer.wikimedia.org page view requests as pageviews. This would mark those requests as pageviews and they would be treated as such by the rest of the downstream pipelines. However, those pipelines assume pageviews have certain fields populated: last_access_per_project_family (cookie), last_access_per_domain (cookie), namespace_id (x_analytics), page_id (x_analytics), page_title (parsed from URI by GetPageviewInfoUDF). We should make sure those fields are populated, to avoid corrupting the downstream pipelines.
• We should make sure that the GetPageviewInfoUDF correctly parses the page title into the corresponding page_title field.
• Is there a way to implement the unique devices cookies in developer.wikimedia.org front-end code? This is probably the correct way, no?
• Is there a way to implement the inclusion of the namespace_id and page_id in the x_analytics header in developer.wikimedia.org?

Exciting work!
My thoughts:

• Are we going to store only bot-like User-Agents or all of them? Maybe, since we can not extract contact/agent/policy information from them and they are also not useful for KAPOW, we might not want to store them to save storage space.
• There might be cases where a User-Agent comes from more than one source. Will we store an array of sources?
• The contact method and contact info could be a single map field maybe? Since, whenever there's an i.e. an "email" method, there should be an email value no? Like {"email": "foo@bar.com", "url": "https://foo.bar.com", ...}. Please, disagree with this, it's just a suggestion.
• Maybe it would be useful to have a request count for each User-Agent? This helps identifying which User-Agents are common vs. long-tail, which can sometimes be valuable? But not sure the request count concept applies to all sources. So.. maybe not? (Oh, I see that Joseph said something similar, well +1)

@Ottomata Do you mean that we should sample after webrequest 2.0? Or that webrequest 2.0 should be good enough to query without creating the sampled table?
I agree with the first. The latter, we would have to partition by a sample hash or something similar. I think it would work, but we would lose partitioning freedom that we could use otherwise to optimize for common queries, like partition by request:_type (pageview, etc.), no?

The dashboard is at: https://superset.wikimedia.org/superset/dashboard/791 (Heartbeat metrics >> Unique devices)

https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2282

https://gerrit.wikimedia.org/r/c/analytics/refinery/+/1285415

It's merged, moving this to done :-)

Looking into this!

This was merged and deployed!
CIM DAGs are running now.

Perhaps we should figure what are our most compute intensive jobs (i.e. biggest bang for the buck) and go from there?

+1

In T384166#11565913, @xcollazo wrote:
Since the vast majority of analytic jobs are scheduled via Airflow, we could certainly change the schedule cron definitions of each DAG to achieve this, but it would require careful planning, as today we rely heavily on the @daily alias, which makes >>everything run at midnight UTC.

That seems to be 7pm virgina time. If we push the default a bit earlier, that could automatically have the intended effect.

Hm, I don't think we can push to earlier. We need the last hour of the UTC day worth of source data to compute the day. Which is when the daily job triggers currently.
We could shift the end/start of day to earlier (maybe that's what you meant). Like instead of computing days from 00:00UTC to 23:59UTC, we could do it from 20:00UTC to 19:59UTC? This should work the same in theory, but might be quite confusing for people consuming the resulting data though...

@mforns, @MNeisler, ok if we delete these?

OK by me!

I'd be totally in favor of setting a long-time retention period for event_sanitized.
The overall plan looks great to me!

The deployment finished successfully.
The pageview and unique devices data will be using the new JA3N-JA4H list starting on 2026-04-07T11:00:00.

I prepared a deployment plan for the automated traffic detection changes:

1. Get an approval for airflow-dag changes.
2. Pause webrequest_actor_metrics_hourly DAG in Airflow UI.
3. Wait until webrequest_actor jobs have finished.
4. Merge airflow-dags changes.
5. Make sure the Blunderbuss pipeline runs.
6. ALTER TABLE wmf.webrequest_actor_metrics_hourly ADD COLUMNS (bot_ja3n_ja4h_pageview_count bigint COMMENT 'Number of pageviews with suspicious JA3N+JA4H pairs');
7. ALTER TABLE wmf.webrequest_actor_metrics_rollup_hourly ADD COLUMNS (bot_ja3n_ja4h_pageview_share double COMMENT 'Percentage of pageviews with suspicious JA3N+JA4H pairs');
8. Unpause webrequest_actor_metrics_hourly DAG in Airflow UI.
9. Quickly vet the data generated by the production DAGs.

I don't think it's a good idea to deploy today Friday before a long weekend, and most of the team on holiday. So, I'll do it when we come back next week.

I've finished the tests.
Hamid and I have checked that both actor counts and pageview counts for different dates within the incident match Hamid's analysis.
Given this, and the 2 approvals for the code, I just merged https://gitlab.wikimedia.org/repos/data-engineering/refinery-private/-/merge_requests/2

Here's the backfilling plan document.

@Ahoelzl @GGoncalves-WMF
@CMyrick-WMF reached out to us, interested in this task, since she will soon be working on time-to-revert insights, and this fix would benefit her work.

I started the tests on Jan 14th. The code I'm testing is in the GitLab MR.
I will regenerate all the pipeline from webrequest_actor_metrics_hourly to unique_devices_*_daily.
If there's more time I will test other dates (Dec 1st, Dec 26th, Feb 19th) but only until pageview_actor.

@JAllemandou Ah! I understand now.
Like:

• Initially, we set valid_until to NULL.
• Then, the day we decide to turn off some of those JA3N/JA4H pairs, we manually set their valid_until to that date.

OK, I will do it like this.
🙏

but I don't think we should automatically disable the filtering when we reach the valid_until date.

So we keep it null for now and when audited that it should be stopped to be used we add the value?

How about the table schema being created_at timestamp, updated_at timestamp, ja3n string, ja4h string, reference string (phab task),
and at each audit, we manually add/delete/update the records that we decide?

OK, will switch to an Iceberg table and put it in wmf_traffic! Thanks for the feedback.
The valid_until field makes sense as a reminder, but I don't think we should automatically disable the filtering when we reach the valid_until date.
Otherwise, we could revert to previous incident's traffic level without noticing.
In our last meeting with @GGoncalves-WMF we informally agreed that there would be a periodical audit of this list, and other automated traffic parameters, and we would reevaluate, remove and add as necessary.