I have started all backfills except for

backfill 2020-07-01 through 2026-05-31 (inclusive)
base_moderator_actions.sql

I expect the backfills to finish in a day or two.

Status update:

• backfill 2020-07-01 through 2026-05-31 (inclusive)

base_moderator_actions_checkuser.sql
base_moderator_actions_history.sql
base_moderator_actions_logging.sql
base_moderator_actions_reverts.sql
base_moderator_actions_suppression.sql

• backfill 2025-03-01 through 2026-04-30 (inclusive)

base_moderator_actions_html_historical.sql

• backfill 2026-05-01 through 2026-05-31 (inclusive)

base_moderator_actions_html.sql

Backfill complete, @CMyrick-WMF - please validate.

The following model backfill needs to be rerun without sync_all_columns

Note: MR !125 must be merged first. Updated status: MERGED.

backfill 2020-07-01 through 2026-05-31 (inclusive)
base_moderator_actions.sql

[updated Jul 2, 2026]

Incomplete backfill for these base models:

These appear complete:

@JMonton-WMF Per the updated metadata noted in the tables above,

These models need backfilling for 2020-07-01 through 2025-11-01 (inclusive)

• base_moderator_actions_logging.sql
• base_moderator_actions_history.sql
• base_moderator_actions_reverts.sql

These models need backfilling for 2020-07-01 through 2026-05-31 (inclusive)

• base_moderator_actions_checkuser.sql
• base_moderator_actions_html.sql

After the backfills above are completed, this model should be backfilled 2020-07-01 through 2026-05-31 (inclusive) using run --full-refresh on the first run

• base_moderator_actions.sql

The backfill is running.

I run a --full-refresh for the first month:

start_date=2020-07-01
sudo -u analytics /opt/conda-analytics/bin/dbt run --full-refresh --project-dir . --profiles-dir . --target production  --select "+base_moderator_actions" --exclude "base_moderator_actions_html" --vars "{\"start_date\": \"${start_date}\"}"

and a loop backfilling all months:

start_date=2020-08-01

while [ "$start_date" != 2026-06-01 ]; do
sudo -u analytics /opt/conda-analytics/bin/dbt run --project-dir . --profiles-dir . --target production  --select "+base_moderator_actions" --exclude "base_moderator_actions_html" --vars "{\"start_date\": \"${start_date}\"}"
start_date=$(date -I -d "$start_date + 1 month")
done

The process is running in a screen in an-launcher1003, it has been running for around 15h, and will need around 25h more to finish.

Just wanted to point out that this process takes that long because it needs to do around 72 runs. I think we could help providing some guidelines about how to convert these type of models into truly dbt incremental models, so that on a monthly basis they do the same, but when running a --full-refresh they process the data since the beginning, grouping by months and writing everything in a single run. The run will need to scan more data, but the processing will be done only once, not 72.

javiermonton opened https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs/-/merge_requests/130

Fix: Snapshot variable

javiermonton merged https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs/-/merge_requests/130

Fix: Snapshot variable

After https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs/-/merge_requests/130

I'm running now:

start_date=2020-07-01
sudo -u analytics /opt/conda-analytics/bin/dbt run --full-refresh --project-dir . --profiles-dir . --target production  --select "base_moderator_actions_logging+" --exclude "base_moderator_actions_html" --vars "{\"start_date\": \"${start_date}\"}"

start_date=2020-08-01
while [ "$start_date" != 2026-06-01 ]; do
sudo -u analytics /opt/conda-analytics/bin/dbt run --project-dir . --profiles-dir . --target production  --select "base_moderator_actions_logging+" --exclude "base_moderator_actions_html" --vars "{\"start_date\": \"${start_date}\"}"
start_date=$(date -I -d "$start_date + 1 month")
done

We ran into a number of different issues during this backfill. The latest update is that it seems like not all source tables contain data for the whole time range of the backfill, so we're doing small test runs on 2024 data to see if shortening the backfill time range would give us some results.

I'm making the following revisions to the task description:

• Updated all instances of "2026-05-31" with "2026-06-30", since we have June 2026 data available now
• Added notes regarding base_moderator_actions_checkuser and base_moderator_actions_html_historical MIN months, so that we don't expect to see data prior to those months when checking the backfill
• Added notes regarding base_moderator_actions_html_historical and base_moderator_actions_html with rationale for the time ranges given so as not to duplicate data

Update:

2020-07 onwards for
base_moderator_actions_history
base_moderator_actions_logging
base_moderator_actions_reverts
base_moderator_actions_suppression

2024-09 onwards for
base_moderator_actions_checkuser

2025-03-01 through 2026-04-30
base_moderator_actions_html_historical

2026-05-01 through 2026-06-30
base_moderator_actions_html

After the above, the following backfill can be run:

2020-07-01 onwards
base_moderator_actions.sql

Backfill complete