Skip to main content

ClickHouse Rollups

There is nothing to rebuild any more

Despite its name and its mv:rebuild npm alias, scripts/rebuild-mv-ad-daily-rollup.ts no longer rebuilds anything. Since ClickHouse migration 024, mv_ad_daily_rollup is a plain VIEW, not a materialized view. It stores nothing, so it cannot drift and there is nothing to re-materialize.

The script is now a verifier. It is read-only unless you pass --drop-legacy-backup.

Symptom​

One of:

  • Reporting numbers look inflated or otherwise disagree with the base table.
  • You need to confirm ClickHouse migration 024 is in effect after applying migrations.
  • You are about to trust a parity result and need to rule out a fanned-out join first.

Diagnosis​

1. Why this ever went wrong — the inflation incident​

Migration 016 created mv_ad_daily_rollup as a SummingMergeTree materialized view: an incremental trigger. Every INSERT into marketing_paid_acquisition (a ReplacingMergeTree(synced_at)) fired it and summed another copy into its storage.

The source table deduped. The MV only ever added. Result:

DateInflationCause
2026-08-018.0×a seed re-run 8 times
2026-08-072.9×accumulated 30-minute production re-syncs

A rebuild fixed the stored number but not the mechanism, so drift restarted with the next sync. The rebuild was a countdown, not a fix.

Migration 024 replaced the MV with a plain view over marketing_paid_acquisition FINAL WHERE segment_key='all'. Deduped truth is now computed at read time, by construction.

2. Verifier limitation after latest-state precedence​

npm run mv:rebuild
# equivalently: npx tsx scripts/rebuild-mv-ad-daily-rollup.ts

Do not use a green exit from this script as the current rollup recovery gate. The script compares the rollup to marketing_paid_acquisition FINAL, while migration 058 defines the rollup over v_paid_acquisition_latest with archive/native precedence. Those are intentionally different truth sources.

The legacy script performs four checks, but check 2 is not authoritative until the script is updated to compare the maintained latest-state source at the rollup grain:

  1. mv_ad_daily_rollup is a VIEW — i.e. migration 024 has been applied. If it is still a MaterializedView the script fails loudly and tells you to run the migration. It deliberately refuses to "fix" the number, because rebuilding would only reset a counter that is about to start climbing again.
  2. Legacy parity: rollup sum(spend) vs base FINAL sum(spend), within $0.01. This comparison predates migration 058 and can disagree by design.
  3. v_ad_performance agrees with the rollup, proving the three-way join has not started fanning out spend.
  4. Optional cleanup of the incident backup table (below).
Money rule

spend is a decimal in dollars throughout this script and everywhere else. There is no unit conversion anywhere in it, and none should be added.

3. If parity fails​

  1. Do not truncate or re-insert anything. With a plain view there is no stored copy to correct, and with a materialized view you would only reset a counter.
  2. Confirm migrations 024 and 058 are applied — check the schema_migrations ledger. Use a maintained latest-state parity query; do not replay migrations.
  3. If check 3 fails but check 2 passes, the problem is in the v_ad_performance join, not the rollup.
  4. If the base table itself looks wrong, the issue is upstream in ingestion — see Sync failures.

Fix​

There is no fix to apply to the rollup itself — see Diagnosis. The only writable action here is deciding what to do with the legacy backup table left over from the 024 migration.

mv_ad_daily_rollup_backup_20260807 was created by step 1 of migration 024's apply procedure. It is the rollback path for that migration.

npx tsx scripts/rebuild-mv-ad-daily-rollup.ts --drop-legacy-backup

Dropping it is never automatic — removing the only rollback path is an explicit human decision. The script additionally refuses to drop it if the parity checks failed, so the backup outlives any run that cannot prove the current state is correct.

Only drop it once the fix has been observed stable in production.

Prevent​

  • Never write to a rollup directly. It is derived state by design.
  • Never add an accumulating materialized view over a ReplacingMergeTree source. That is precisely the bug migration 024 removed.
  • Ship schema changes as a new migration file — the ledger keys on filename, so edits to an already-applied file never re-run.