BigQuery Backfill Script

A BigQuery scripting block that loops over partitions and rebuilds a fact table. Lineage shows partition-by-partition flow into one mart.

The lineage

The SQL

CREATE TABLE raw_events (
  event_date DATE,
  user_id STRING,
  event_name STRING,
  amount NUMERIC
);

CREATE TABLE fact_user_events (
  event_date DATE,
  user_id STRING,
  events INT64,
  amount NUMERIC
);

DECLARE start_date DATE DEFAULT DATE '2026-01-01';
DECLARE end_date   DATE DEFAULT DATE '2026-04-01';

WHILE start_date < end_date DO
  INSERT INTO fact_user_events (event_date, user_id, events, amount)
  SELECT event_date, user_id, COUNT(*) AS events, SUM(amount) AS amount
  FROM raw_events
  WHERE event_date = start_date
  GROUP BY event_date, user_id;
  SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
END WHILE;

What the lineage shows

• fact_user_events.events traces back to COUNT(raw_events.event_name).
• fact_user_events.amount traces back to SUM(raw_events.amount).
• Scripting variables don't change column lineage — only the rows touched.