Problem and constraints
Incentive numbers were assembled by hand across several tools, so a wrong row could sit in the report unnoticed and a corrected week could silently change a number somebody had already been paid against.
Approach
Moved every source query into version control and let the pipeline run on an hourly trigger: Metabase and Zoho cards compute, the sync writes raw tabs, the sheet computes the aggregates, and the dashboard only reads. Added a divergence layer that compares frozen weeks against their source, and reconciliation queries that must return zero difference before the reporting console is treated as authoritative.
Measured result
Numbers became auditable end to end: each divergence carries a first_seen and last_seen, only real changes are logged (instead of thousands of rows of already-known state every day), and a card that outgrew the query endpoint's row cap was migrated to a full export with a written runbook.
| What | Value | Scope and source |
|---|---|---|
| SQL cards synced hourly | 8 | each card mapped to one sheet tab; the query text lives in the repo, not in the BI card |
| refresh cadence | hourly | installable Apps Script time trigger, one write path per tab |
| known-state rows per day no longer rewritten | ~12,400 | divergence state is stored once with first_seen and last_seen instead of re-writing 518 rows on every run |
| row cap that forced a migration | 2,000 | one card sat at 1,566 rows of the query endpoint's 2,000-row cap, growing about 32 rows a day, so it moved to a full export |