Hi @Snwachukwu - quick note, I just created a MR today for the models I maintain in dbt: dbt-jobs/163

Chris already created a patch: https://gerrit.wikimedia.org/r/1320964 - just needs to be deployed.

I have cleared the task instances for https://airflow.wikimedia.org/dags/dbt_daily/grid?search=dbt_daily&task_id=product-analytics.known_clients_aggregate_daily&tab=details from 5-11 August, and re-ran. Everything is good now, thanks for quick fix.

Thanks for enabling this feature, and supporting the transition. We have the first job with hourly schedule: https://airflow.wikimedia.org/dags/dbt_api_requests_hourly/grid?search=dbt_api_requests_hourly

Closing this as duplicate as User-Agents that appear in Dumps logs will be populated in wmf_traffic.user_agents_info with source = 'dumps'.

The initial version of the table is setup and is being populated:

In T430637#12146398, @GGoncalves-WMF wrote:

Now that we're updating the bot detection model in the data platform, you should be able to run a query like:

select agent_type, count(*) as freq
from wmf_traffic.webrequest_v2 where dt >= make_timestamp(2026, 7, 2, 0, 0, 0) and dt < make_timestamp(2026, 7, 2, 1, 0, 0) and target.uri_host like "%developer.wikimedia.org%"
group by agent_type;

to get human pageviews for developer.wikimedia.org. Naturally you'll probably want to filter this down to specific URLs etc, which I think Halley will be able to inform.

Great time supporting the rate limits roll-out. Resolving this now, we can have additional tasks for future rollouts.

• 2026-04-21: development of a few query templates for reuse
• 2026-04-29: user agents had change significant changes in overall request volume or 429 errors (on 28 Apr compared to 27 Apr).
• 2026-05-18: investigation of rl_null for various endpoints, in support of T419522.

I am happy to review :) I will do that today/tomorrow.

In T431249#12089206, @GGoncalves-WMF wrote:

Can you please expand on the "track activity" part? I'd like to understand what we need to measure here so we can find the best table(s) to create and change.

It sounds like what you're looking for is: How many pageviews came from a particular client, whether that is a self-identified bot or assumed human, aggregated to hourly counts.

Is that it?

Hi @BTullis @Ottomata: can we move the schema out of development namespace to a permanent one, and also generate canary events?

In T430020#12070515, @GGoncalves-WMF wrote:

That would mean we'd have to regularly import the JSON to be able to exact-match individual UAs against it, rather than extracting information from the UA ourselves like we currently do. Is that right?

I think this makes sense as a future improvement for user_agents_info, but we probably just want to file it as a follow-up ticket.

I do see that bot_name and bot_operator would be good to have.

About bot or agent, if I'm reading the link right, this one is less useful to us. It just means that Cloudflare is able to verify the identity of the bot and pass it on to its client for use in firewall rules. This is done not using the UA, but with IP lists or Web Bot Auth. Since we don't do the same verification, the field doesn't quite carry the same meaning.

I just had a chat with @Pablo - reviewing the Cloudflare's taxonomy.

In T430020#12066596, @GGoncalves-WMF wrote:

Looks good to me!

I'm just wondering whether is_bot_like should be a subcategory under bot_category: semantically, it's a fallback that says "we think this is a bot, but we don't know how to categorize it".

I have the updated the proposed schema in the description based on the discussion and also additional stats columns.

Thanks to @BTullis for quick help on this; filing this task for posterity.

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.

This will make it much easier to calculate the required metrics downstream, rather than aggregating from webrequest. Thanks for proposing this @GGoncalves-WMF.

Summary of planned changes from Slack discussion

Note: We have decided not to backfill webrequest. The other task for dbt jobs backfill is already in progress: T429341

In T429280#12036689, @GGoncalves-WMF wrote:

I don't have a strong opinion on "one row per source", that's sensible if you two agree to it :) For what it's worth, in KAPOW, we'll probably not care about what is the source of a UA, we'd just join to that table based on (unique) UAs.
Let me try to flesh out the resulting table, then. How do you feel about something like this?

user_agent	source	user_agent_map	contact_method	contact_info	ua_policy_compliance_level	category	agent	ts

In T429341#12034706, @amastilovic wrote:

I've started backfilling wmf_traffic.mrt_api_requests_hourly and one day's backfill takes about half an hour. That means the first batch 2026-02-01 to 2026-03-31 will be done in approx. 30 hours.

@JAllemandou and I discussed about the schema changes on Slack.

I think it makes sense to have one canonical table that contains structured data for the User-Agents we've seen anywhere (dumps logs as well as webrequest). We'd probably still want to keep the original source as a dimension to allow you to do analysis on dumps alone.

In T427474#12026589, @Ahoelzl wrote:

@JAllemandou @KCVelaga_WMF can you provide an update on the progress?

How do we currently compute user_agent_compliance_classified_hourly? Is this logic still in a notebook?

@JAllemandou @GGoncalves-WMF can you please review this and what do you think about the proposed approach?

In T425087#11987928, @KCVelaga_WMF wrote:

In T425087#11987887, @BTullis wrote:

Great! How much more testing to we have to do before promoting the schema from the development namespace, and who should make that call?

Thank you all for working on this. I can explore the data today or Monday and report back. Not sure about the schema decision though.

In T425087#11987887, @BTullis wrote:

Great! How much more testing to we have to do before promoting the schema from the development namespace, and who should make that call?

In T427840#11984475, @JAllemandou wrote:

Summarizing the conversation we just had with @KCVelaga_WMF :

• KC owns the schedule to move the HQL and DAGs to the analytics folders, I help him with reviews and operations.
• KC will ask the DE team about pyspark jobs repository. The DE team only have a specific pyspark job today (content_history) and uses only HQL and scala. Discussion needs to happen about if we wish to own pyspark jobs or have everything in java/scala (I'm in favor of having pyspark jobs too :), if we agree that we should have pyspark jobs, how we organize them (one repo, multi repos, with HQL or seprated etc).

We talked with @GGoncalves-WMF yesterday, and we think that since the data could be defined as "core datasets", it would be a good idea to have it within the analytics realm.
For schema definition and HQL jobs, the place would be the HQL folder of the refinery repo .

In T427840#11975217, @JAllemandou wrote:

In T427840#11973679, @KCVelaga_WMF wrote:need.

Hm, I wonder what would be best here: give analytics-product write writes or move the jobs to the analytics realm.
Ping @Ahoelzl and @GGoncalves-WMF on this.
Note: the table definitions (schemas and jobs) for so called "production" tables is now spread across multiple repos, with different conventions. I'd really like if we could define common practice for all this.

@JAllemandou and I had a quick meeting, reviewing the outputs from test runs, which was very useful - thanks Joseph! We decided on the following:

In T427840#11975266, @JAllemandou wrote:

@KCVelaga_WMF : I don't understand why you use explicit (year, month, day, hour) partitioning for those tables when we have iceberg. You should use a timestamp field, and a function over this timestamp to partition, even if the partitioning is hourly. It'll make querying a lot easier.

Also, for future use cases, if it isn't an issue, can we consider enabling analytics-product user to be able to create tables under wmf_traffic?

@amastilovic @JAllemandou can one of you help with this please? As you are closest to this work.

Sounds good.

@JAllemandou That should work. This is the DAG I wrote for that: refine_api_requests_hourly_dag.py

As per discussions, I will be taking this up with DPE support (from @amastilovic).

API for article topics model output: https://api.wikimedia.org/wiki/Lift_Wing_API/Reference/Get_articletopic_outlink_prediction