Page MenuHomePhabricator
Create Task
Maniphest T429341

dbt-jobs backfill: PP3 API hourly and known clients aggregate jobs
Closed, ResolvedPublic
Actions

Description

We have developed several dbt-jobs as part of responsible pathways metric calculation, the jobs have been merged and deployed. However, the daily jobs need a manual backfill from 1 Feb 2026 to 20 Jun 2026 (approx).

The following should be backfilled manually (along with upstream models) in the following order:

After the above, the following can be run in parallel:

all the above to be completed before running this

After the above are complete, dbt-jobs/MR-106 is merged, then the objective metric job should be backfilled as well.

  • mrt_traffic_pathways_daily

The dependent datasets (wmf_traffic.api_requests_refined and wmf_traffic.user_agent_compliance_classified_hourly) are themselves being backfilled, haven't yet caught up to the latest.

So the above models have to be backfilled in batches month-wise every few days:

  • 2026-02-01 to to 2026-03-31 (Feb and Mar can be done together)
  • 2026-04-01 to 2026-04-30
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-30
  • 2026-07-01 to 2026-07-15 (estimated)

FYI dependent datasets DAG monitoring:

Related Objects
Search...

Event Timeline

KCVelaga_WMF created this task.Jun 16 2026, 1:44 PM
KCVelaga_WMF triaged this task as High priority.
KCVelaga_WMF updated the task description. (Show Details)Jun 17 2026, 7:51 PM
KCVelaga_WMF updated the task description. (Show Details)Jun 18 2026, 7:56 AM
amastilovic added a comment.EditedJun 18 2026, 4:00 PM

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.

KCVelaga_WMF moved this task from Triage to Tracking on the Product-Analytics board.Jun 18 2026, 4:17 PM
KCVelaga_WMF updated the task description. (Show Details)Jun 19 2026, 8:26 AM
KCVelaga_WMF added a comment.Jun 19 2026, 8:50 AM
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.

Thank you!

Also wanted to note, the models need not be exactly one after the other, some of them can be run in parallel. I have updated the description to reflect this. It can be something like this:

mrt_api_requests_hourly
            ↓

   ratelimit_class_daily
   media_ua_compliance_daily
   media_known_clients_daily       (can be run in parallel)
   pageview_ua_compliance_daily
   pageview_known_clients_daily

            ↓
mrt_known_clients_daily  →  review/merge MR-106  →  mrt_traffic_pathways_daily
KCVelaga_WMF added a parent task: T424729: PP3 - Percentage of identified automated traffic through responsible pathways.Jun 22 2026, 7:08 AM
KCVelaga_WMF mentioned this in T427474: Request for backfill of webrequest with is_api_request and ip_provenance columns.
amastilovic updated the task description. (Show Details)Jun 22 2026, 9:27 AM
GGoncalves-WMF moved this task from Next Up to In progress on the Data-Engineering (Q4 FS25/26 April 1st - June 30st) board.Jun 22 2026, 3:33 PM
amastilovic added a comment.EditedJun 23 2026, 2:34 PM

Status update:

mrt_api_requests_hourly:

  • 2026-02-01 to to 2026-03-31 (Feb and Mar can be done together)
  • 2026-04-01 to 2026-04-30
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-20 (estimated)

mrt_ratelimit_class_daily
mrt_media_traffic_ua_compliance_daily
mrt_media_traffic_known_clients_daily
mrt_pageview_ua_compliance_daily
mrt_pageview_known_clients_daily

  • 2026-02-01 to to 2026-03-31 (Feb and Mar can be done together)
  • 2026-04-01 to 2026-04-30 (code changes were needed, IN PROGRESS)
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-20 (estimated)
amastilovic added a comment.Jun 24 2026, 5:05 PM

Running the backfill for wmf_traffic.responsible_traffic_pathways_daily Feb 2026 and Mar 2026...

amastilovic added a comment.Jun 25 2026, 10:47 AM

Status update:

mrt_api_requests_hourly:

  • 2026-02-01 to to 2026-03-31 (Feb and Mar can be done together)
  • 2026-04-01 to 2026-04-30
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-20 WAITING

mrt_ratelimit_class_daily
mrt_media_traffic_ua_compliance_daily
mrt_media_traffic_known_clients_daily
mrt_pageview_ua_compliance_daily
mrt_pageview_known_clients_daily

  • 2026-02-01 to to 2026-03-31 (Feb and Mar can be done together)
  • 2026-04-01 to 2026-04-30 (code changes were needed
  • 2026-05-01 to 2026-05-31 IN PROGRESS
  • 2026-06-01 to 2026-06-20 (estimated)

wmf_traffic.responsible_traffic_pathways_daily

  • 2026-02-01 to to 2026-03-31
  • 2026-04-01 to 2026-04-30 IN PROGRESS
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-20 (estimated)
Ahoelzl subscribed.Jun 25 2026, 2:43 PM

@amastilovic this should complete by ~Monday?

amastilovic added a comment.EditedJun 26 2026, 8:14 AM

@Ahoelzl I think it will, the big and slow jobs are up to 2026-05-18 today so May should be done by Monday. wmf_traffic.responsible_traffic_pathways_daily should be backfilled today, up to and including May.

KC told me to hold off backfilling June so I'm only doing Feb - May for all jobs.

mrt_ratelimit_class_daily
mrt_media_traffic_ua_compliance_daily
mrt_media_traffic_known_clients_daily
mrt_pageview_ua_compliance_daily
mrt_pageview_known_clients_daily
  • 2026-02-01 to to 2026-03-31 (Feb and Mar can be done together)
  • 2026-04-01 to 2026-04-30 (code changes were needed
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-20 (estimated)

wmf_traffic.responsible_traffic_pathways_daily

  • 2026-02-01 to to 2026-03-31
  • 2026-04-01 to 2026-04-30
  • 2026-05-01 to 2026-05-31
  • 2026-06-01 to 2026-06-20 (estimated)
amastilovic updated the task description. (Show Details)Jun 29 2026, 8:30 AM
amastilovic added a comment.Jun 29 2026, 9:02 AM

Backfill completed.

KCVelaga_WMF updated the task description. (Show Details)Jul 8 2026, 7:14 AM
Ahoelzl edited projects, added: Data-Engineering (Q1 FS26/27 July 1st - September 30th); removed: Data-Engineering (Q4 FS25/26 April 1st - June 30st).Jul 8 2026, 12:45 PM
Ahoelzl moved this task from Next Up to In progress on the Data-Engineering (Q1 FS26/27 July 1st - September 30th) board.
KCVelaga_WMF updated the task description. (Show Details)Jul 17 2026, 10:55 AM
Ottomata moved this task from In progress to Done on the Data-Engineering (Q1 FS26/27 July 1st - September 30th) board.Aug 3 2026, 4:22 PM
Ahoelzl closed this task as Resolved.Mon, Aug 24, 9:51 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits