nshahquinn-wmf opened https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/1

Add new queries for the editor_month table

hghani merged https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/1

Add new queries for the editor_month table

Currently blocked on T364359 since I need to sudo as analytics-product to spin up a test Airflow instance.

nshahquinn-wmf opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/702

Draft: Add new editor_month job

nshahquinn-wmf opened https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/2

update_editor_month: Fix performance and typo

hghani merged https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/2

update_editor_month: Fix performance and typo

nshahquinn-wmf opened https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/3

editor_month: Style fixes

hghani merged https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/3

editor_month: Style fixes

In T362594#9814793, @CodeReviewBot wrote:

nshahquinn-wmf opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/702

Draft: Add new editor_month job

This is now finished and awaiting review.

nshahquinn-wmf merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/702

Add new editor_month job

The job has been deployed to production, but failed because wmf_contributors.editor_month hasn't been created yet. I don't have the permissions to do that, so I've asked a member of Data Platform to do it instead.

Once the table has been created, we will need to clear the status of the failed run so it will be re-run and then unpause the DAG. After that the job should be fully operational.

For the record, I confirmed some time ago that the outputs from the new query match the ones from the old query: https://gitlab.wikimedia.org/nshahquinn-wmf/miscellaneous-notebooks/-/blob/main/2024/2024-04_editor_month_new_query.ipynb

Getting the new table created is currently blocked because it's not clear where the data should be stored in HDFS (T367243).

Following the discussion on Slack, I've created the table and changed the dir owner to analytics-product. The long-term discussion about HDSF dir architecture and ownership is here: T367243

Spark allows you to update the location of Iceberg tables, so it shouldn't be a problem to update it later.

The job is unpaused and running correctly.

It turned out that I hadn't coded the job in way that allows us to run a backfill with a fixed mediawiki_history snapshot, and when I looked back at the advice I had gotten on how to do that, all the possible approaches lookedvery complicated. In the end, I decided just to give up on Airflow and write a simple Python script to submit the backfill queries directly. That's running now.

There are some odd inconsistencies between the old table and new table in the early months that have completed so far. I had compared the query outputs for 2024-03 in detail and things looked very consistent, so likely this is specific to early history. Still, I should look into it more.

I did a deep dive into the inconsistencies between the two versions of the table. >99% was due to the underlying shift in mediawiki_history, but there was a tiny error in the new dataset: I accidentally included edits occurring during the very first second of the following month.

Since backfilling is cheap, I'm going to re-do it with a fix for that tiny issue.

nshahquinn-wmf opened https://gitlab.wikimedia.org/repos/movement-insights/sql/-/merge_requests/4

Editor month improvements