A Year-Long Backfill Overwhelmed the Source Database

A new analytics team needs a year of history loaded for a pipeline that has only ever run incrementally (nightly, pulling the previous day's rows via a timestamp watermark against a production Postgres replica). An engineer writes a script that loops over all 365 days and, for each day, runs the same daily-incremental query and load logic used by the nightly job, sequentially, with no changes:

for day in date_range(start, end):  # 365 iterations
    rows = query_source(f"WHERE updated_at::date = '{day}'")
    merge_into_warehouse(rows)

Two things go wrong: (1) around day 40, the production database team pages the on-call, reporting sustained high query load on the read replica that's affecting other consumers; the backfill script, still running, is the cause; (2) once the backfill finishes and downstream dbt models are checked, several derived tables (built on top of the table just backfilled) still reflect data from before the backfill — nobody re-ran them.

1. Diagnose why running the daily job's logic 365 times, unchanged, caused the source database load problem, and propose a fix.
2. Diagnose why the downstream derived tables are stale, and propose a fix.
3. Is there a risk that the backfilled data doesn't actually match what the source table looked like on each of those historical dates? Under what condition would that risk be real here specifically, given the source is a timestamp-watermark pipeline, not CDC?