The migration in one idea
The important change was not simply “WalletWall started using BigQuery.” The migration changed where expensive work happens and what evidence a new serving path must provide before it can answer a user-facing request. Before the cutover, a BigQuery result could exist as a candidate without being authoritative. WalletWall could build it, compare it with the existing path, check completeness and freshness, measure cost, probe the deployed route, and still keep the previous path in control. Promotion happened only after the new path satisfied its gate. That produces a different operating model:Heavy data movement happens ahead of the request. User-facing reads consume small, verified snapshots. Serving authority moves only after the new path earns it.The migration was deliberately decomposed so a problem in one surface could stay in that surface. Wallet activity, stablecoin flows, volume history, wallet holdings, and signature-exposure facts each own their own refresh state and route decision.
Explore the migration
The lab below is a local teaching model of the migration contracts. Try breaking freshness, completeness, byte limits, promotion evidence, or lease ownership and watch the model preserve the previous authority instead of inventing progress.The lab does not read live deployment state and its result labels are explanatory, not runtime status values or WWEP states. The canonical behavior is the prose, tables, and implementation contracts on this page.
One migration, five independent crossings
The migration moved one surface at a time. Each move is a slice: an owned dataset, a refresh pipeline, a request-time read and a route switch, built and proven on its own.
Slices 1 to 4 derive from the public Ethereum token-transfer dataset in BigQuery; slice 5 derives from the public transaction relations of six EVM chains (a seventh was quarantined in October 2026 after it stopped supporting partition pruning, which would have made every read unaffordable).
Intuition pump: the wildebeest migration
From far away, the Great Migration looks like one herd moving from one place to another. Up close, it is a sequence of crossings. Different groups reach the river at different times, some are already on the far bank, and one successful crossing says nothing about whether another group is ready. WalletWall’s migration has the same useful shape. The river is not “BigQuery.” The risky boundary is promotion to serving authority. Building rows on the far bank is not enough. A slice crosses only after its candidate result is complete, fresh, bounded, compared, and probed. That is why the migration looks like this conceptually:The wildebeest framing is a teaching metaphor, not technical authority. The actual controls are the pipelines, watermarks, leases, budgets, comparisons, probes, and route switches documented below.
A quantum-flavored view of promotion
Before a cutover, WalletWall can have two operationally possible future states at once: the existing path remains authoritative while the BigQuery path exists as a candidate. The readiness gate resolves that uncertainty by measurement.Why slices are independently owned
Independence is what makes a problem in one surface stay in that surface.- Own dataset, own tables. A slice’s refresh identity can update only its own tables.
- Own watermark. Each pipeline records how far its data is complete; no slice reads another’s.
- Own lease. Each pipeline has its own single-runner claim, so two slices can refresh at the same time and two runs of one slice cannot.
- Own byte budget and schedule. A refusal in one pipeline publishes nothing and changes nothing for the others.
- Own route switch. Promotion to primary and rollback to legacy are decided per surface.
A refresh run, step by step
The migration established the serving path, but the same safety model has to run every time a slice refreshes. A scheduled run claims one pipeline, plans only from verified progress, prices the work, replaces a bounded window, verifies it, moves the watermark, and publishes a complete result.1
Claim the lease
One row per pipeline records who holds the refresh claim. Acquiring it is a compare-and-set update that succeeds only if the claim is released or expired. A run that loses that race refuses before reading anything else.
2
Plan from the watermark
The window to load runs from the watermark (minus an overlap, so late or re-flagged rows are picked up) to the chain tip minus a 15-minute safety lag, in bounded chunks. A pipeline that has fallen more than seven days behind refuses rather than catching up on its own; an operator decides.
3
Price before sending
Every statement is dry-run and priced. The run reserves 1.25 times the estimate for each half of a window replace plus a fixed follow-on reserve. If the remaining budget cannot cover the whole replace, nothing is sent.
4
Replace the window
The window is deleted and reloaded with an
INSERT … SELECT against the public source. The two statements are not atomic, which is why both are priced first and why the load is verified before anything downstream moves. The lease is renewed before every write; a renewal that fails stops the run with nothing more written.5
Verify, then advance
The loaded window must hold exactly the rows the insert reported, with every hour populated and no hour far below what the source showed. Only then is the watermark advanced, by a compare-and-set that refuses if another run moved it first.
6
Rebuild and publish
Snapshots, cohorts and 90-day ranges are rebuilt only over a gap-free window and published with statements that replace the previous content atomically, so a reader sees the old snapshot or the new one, never a mixture.
7
Report and release
The run reads its own status, evaluates six health checks and releases the lease. The receipt and a progress trail are kept as workflow artefacts for 30 days, and an unhealthy or refused run is a red run.
Watermarks and complete days
A watermark is the exclusive upper bound of data that has been loaded and verified. It moves only by compare-and-set, only after verification, and it is what the serving read judges staleness against. The newest observation and the watermark answer different questions. The newest observation says how far the source has been seen. The watermark says how far WalletWall is willing to claim completeness. A newer row beyond the watermark therefore does not prove the territory between them is complete.- Slices 1 and 2 keep hour-aligned watermarks and re-read a two-hour overlap on each refresh.
- Slices 3, 4 and 5 keep UTC-day watermarks. A day is loaded only once it is complete: the chain tip must be past the day’s end plus the safety lag, and the daily crons fire after midnight UTC for that reason. Volume, balances and signer facts are therefore always stated for complete days, never for a partial one.
- The serving tolerances (14 hours, 2 days, 3 days, 4 days, 3 days) are set from the cadence so that a single missed refresh degrades to a warning and a longer outage falls back, never to a silent stale answer.
Single runner: the lease
A refresh must never run twice at once. Two layers enforce it:- The lease. Acquire by compare-and-set; renew before every data write; release at the end. The lease lives for 30 minutes — twice the longest wait the job client will give one statement — so a job that ran under a claim is finished or stopped before the claim can expire. If a run ends with a write whose outcome is unknown, it deliberately leaves the claim to expire so that no other run starts while that write might still commit.
- The scheduler. Each pipeline’s workflow job runs in its own concurrency group that never cancels an in-progress run.
Budgets and refusals
Cost control is enforced by BigQuery, not by arithmetic the run could get wrong: every statement carries a byte ceiling, and the ceilings sit under a code-owned 4 GiB absolute maximum. On top of the per-statement ceilings each pipeline has a run budget — 6 GiB and 4 GiB for the six-hourly slices, 8 GiB for daily volume, 96 GiB for the two heaviest daily rebuilds — and three refusals that fire before anything is sent:
Estimates are perishable (one closed day’s estimate once moved by over twenty percent within ninety minutes), which is why reservations carry headroom and why a refusal is cheaper than a half-loaded window. In late October 2026 the signer-snapshot rebuild outgrew its 64 GiB budget and was refused on two consecutive days. A refusal publishes nothing, so the previously published snapshot remained the serving state under the serving read’s own staleness gate; the budget was then raised to 96 GiB and the publication split into smaller ranges.
Serving budget is a separate contract
The migration’s biggest architectural payoff is that the expensive work happens before the request. A request reads prepared state instead of re-running the migration. Every BigQuery serving read is capped at 64 MiB. That is deliberately separate from the multi-GiB scheduled refresh budgets above. A refresh run being allowed 96 GiB does not make a 96 GiB request-time read acceptable, and the refresh run budget does not waive its per-statement ceilings. This is why “BigQuery can execute it” and “WalletWall permits it” are different claims. Cost is part of correctness.Why partial data never becomes the serving state
The watermark moves last
The watermark moves last
Delete, reload and verification all happen before the watermark advances. A run that stops anywhere before the compare-and-set leaves the watermark where it was, and the serving read judges the snapshot by that watermark.
Snapshots are rebuilt only over gap-free windows
Snapshots are rebuilt only over gap-free windows
A snapshot or cohort build refuses when any hour of its window is missing, naming the gap as a repair an operator can run. It never aggregates over a hole.
Publication is atomic
Publication is atomic
Cohorts and ranges are published with one replacing statement each, so there is no instant at which a reader could observe the old and the new rows together. Where a table is rebuilt between two jobs, the serving read detects the gap (a newest flow far short of the watermark) and declines to serve or cache the result.
The serving read is the last guard
The serving read is the last guard
Even a published snapshot is served only if its watermark is inside tolerance and the read returned its whole result. Otherwise the route’s legacy path answers.
Scheduling and identity
Refreshes run as scheduled jobs in the repository’s CI platform, never from the web runtime:- Five crons, staggered: the two six-hourly pipelines thirty minutes apart, then the three daily pipelines after midnight UTC once their day is complete.
- Each job can compose exactly one command — the incremental refresh under the pipeline’s registered budget, followed by a status read. Backfill, schema changes, repairs and every optional flag are refused by the entry point, so a scheduler cannot be made to do anything but refresh.
- The job authenticates with the platform’s OpenID Connect token through Workload Identity Federation; there is no long-lived key. The refresh identities are separate from the runtime’s and likewise keyless; each can read and update the tables its pipeline maintains and nothing else.
- Each job carries a per-pipeline live-mutation token, so a job prepared for one pipeline cannot run another.
Observability
Every run produces a receipt: the run id, outcome, old and new watermark, each window’s state, the lease outcome and the bytes billed for the refresh and for the status read. The six health checks — refresh outcome, claim released, watermark present, watermark fresh within two cadences, claim free after the run, and one slice-specific completeness check such as “the snapshot ends at the watermark” — decide whether the run is green. Reconciliation against the public source is heavier and runs on an operator cadence and inside the cutover gate, not in every scheduled run.A WalletWall migration
The implementation uses GCP and BigQuery, but the migration follows the same design discipline as the rest of WalletWall:- Evidence before authority. A candidate does not become authoritative merely because it exists.
- Provenance before confidence. A served result should identify where it came from.
- Unknown before invention. Refused, incomplete, or stale state remains explicit.
- Bounded systems over unlimited capability. BigQuery’s maximum capability is not WalletWall’s permitted capability.
- Independent failure domains. One slice failing does not invalidate another.
- Earned state transitions. Computed, verified, published, and authoritative are separate claims.
- Staleness changes meaning. Old evidence does not silently retain current authority.
WalletWall moved five analytical surfaces across independently gated serving boundaries, shifted heavy computation into scheduled refresh pipelines, tracked only verified progress with watermarks, bounded cost before execution, prevented stale or concurrent writers from claiming authority, published snapshots only after verification, and reduced the request path to small provenance-carrying reads.
Where to go next
Data Serving Architecture
How the request-time reads consume these snapshots, and how responses are labelled.
BigQuery Serving & Canary
The readiness gates a slice must pass before its route is switched to BigQuery, and how rollback works.

