We are tackling this with @Antoine_Quhen .
We use this document to organise our work: https://docs.google.com/spreadsheets/d/1Ies3ec2IhLVymt4H51Fo8v1l2lVzun1gTetugV-CIEo

Change #1101807 had a related patch set uploaded (by Joal; author: Joal):

[analytics/refinery@master] Add HQL scripts to backfill actor tables

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

Change #1101807 merged by Joal:

[analytics/refinery@master] Add HQL scripts to backfill actor tables

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

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/956

Backfill webrequest actor metrics 2024 12

The 6 month retention period will be closed by end of December, then we start losing data if we don't carry it forward.
We should have processed all retained webrequest data into pageview_actor by then.
Continuing with the backfilling of further downstream data sets in January might leave us with a gap of a few days to bridge... Ideas?
@JAllemandou @Antoine_Quhen

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/966

Backfill 2024 12: webrequest actor metrics rollup hourly

In T378852#10395498, @Ahoelzl wrote:

The 6 month retention period will be closed by end of December, then we start losing data if we don't carry it forward.
We should have processed all retained webrequest data into pageview_actor by then.
Continuing with the backfilling of further downstream data sets in January might leave us with a gap of a few days to bridge... Ideas?

Given how the backfill moves (no jinxing, I promise), we'll have all pageview_actor backfilled by this end of week.
I suggest we use next week to backfill jobs dependent on `pageview_actor. There should be enough time if we follow the same pattern we have used so far (dedicated DAGs with high parallelization)

Writing updates here as well as on slack.
Yesterday, 2024-12-10 T10:30UTC @Antoine_Quhen and myself have started the backfilling of the webrequest_actor_metrics_hourly table, using a dedicated backfill DAG and a modified HQL code. The job processed data for about one day (until 2024-10-11T11:30UTC) and now all of the table is done.
At about the same time the first job finished its backfill (2024-12-11T100:00UTC), we started the second job for webrequest_actor_metrics_rollup_hourly, using another dedicated DAG (no HQL change needed for this one). It's been running since then but is unfortunately not as fast as the first one. As of now (2024-12-11T18:00UTC) a bit less than 20% of the tasks have been done, making an estimated finish time during Thursday night UTC.
We plan on releasing the third backfilling DAG, for webrequest_actor_label_hourly, tomorrow morning UTC, and start it straight away, concurrently backfilling jobs 2 and 3.

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/969

Backfill 2024 12: webrequest actor label hourly

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/970

Backfill 2024 12: pageview actor hourly

Today's update:

• backfill_webrequest_actor_metrics_rollup_hourly_2024_12 DAG is progressing well. As of now 2024-12-12T19:00UTC, about 80% is done.
• backfill_webrequest_label_hourly_2024_12 DAG has been deployed and started at 11:30UTC. It moves at the same pace as the one above, so we expect it to be done between Friday and Saturday.
• backfill_pageview_actor_2024_12 DAG has been deployed along with its updated HQL code, and is ready to be started tomorrow morning, with expected completion during the weekend. Note: this DAG doesn't write its data in the production table, so don't expect to see any change there before we manually swap old/new data on Monday morning :)

Looks like the current webrequest_actor_label_hourly run is failing.

Seeing e.g.

20:48:26 [@an-launcher1002:/home/otto] $ sudo -u analytics yarn logs -appOwner analytics -applicationId application_1732360673415_694206
[...]
Caused by: org.apache.hadoop.ipc.RemoteException(java.io.FileNotFoundException): File does not exist: /wmf/data/wmf/webrequest_actor/label/hourly/_temporary/0/_temporary/attempt_202412121948226401602153516590635_0000_m_000004_57/year=2024/month=12/day=12/hour=17/part-00004-f5bdbc40-9894-43f3-bd85-9795b96e0d72.c000.snappy.parquet (inode 2544591883) Holder DFSClient_NONMAPREDUCE_-1110153479_41 does not have any open files.

I cleared the task but it is still failing and retrying. Could I leave this one with you all?

In T378852#10401809, @Ottomata wrote:

I cleared the task but it is still failing and retrying. Could I leave this one with you all?

After a few more retries it succeeded.
I'm really eager to find a better FileOutputCommiter that will allow us to not have this type of issues anymore :(

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/972

Backfill 2024 12: pageview hourly

Thank you all for the quick turn around on the backfill!

The result has been a dramatic drop in the overinflated numbers of unique devices that we had seen in the last 5 months. Some observations:

1. Our revised YoY unique devices changes for August is +~4%, Sep: +~2%, Oct: +~2%, Nov: ~+4.7%. These YoY changes are within our 'normal range'. July remains elevated since it was not backfilled due to missing data in the first 4 days.
2. It turns out that the redirect fix had a larger fix than we had thought and accounted for pretty much all of the anomalous increases (besides Singapore).
3. Singapore is back to its normal range of unique devices that it has been since the beginning of 2024. These numbers are still higher than 2023 by ~100%, but whatever is going on here is not making a huge impact on global uniques and looks unrelated to the fixes we implemented.
4. The project family daily and domain daily charts resemble each other again in the months that the fixes was deployed for which fixed a divergence that started in July (at least in recent history).
5. It might still be worthwhile to go over other possible sources of noise, but given the magnitude of the effect these fixes had without creating other anomalies, it is very hard to imagine new findings that would be worth the time and effort as we are essentially back to 'normal' on all of the metrics.
6. We will have to backfill our own movement-metrics intermediate tables and repo historical tsvs.

1st stage backfill completed for

• pageview_hourly
• referer (daily)
• unique_devices (daily and monthly)
• interlanguage (daily)
• cassandra (daily)
• clickstream (monthly)

https://docs.google.com/spreadsheets/d/1Ies3ec2IhLVymt4H51Fo8v1l2lVzun1gTetugV-CIEo/edit?gid=0#gid=0

aqu merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/978

Backfill 2024 12: cassandra_load_pageview_per_article

And, this is done! all jobs have been backfilled.

Hooray! Congratulations and huge thank you @JAllemandou and team!