Skip to main content
WalletWall’s BigQuery migration moved five analytical surfaces from provider-backed serving toward owned, bounded snapshots without one global cutover. This page explains how each surface earned serving authority independently, how scheduled refresh work replaced expensive request-time computation, and how the same controls keep that state fresh after migration. The serving architecture reads small, validated snapshots. This page covers the migration and the scheduled side that produces them. The design goal is simple: a request must never see a table that is half built, and a refresh must never spend more than it said it would.

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:
A failed crossing stays local. Holding one herd on the previous bank is safer than forcing every surface through the same cutover window.
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.
The useful connection to WalletWall’s quantum work is not physical superposition. It is the discipline of keeping uncertainty explicit until observation justifies a stronger claim. A candidate result does not become authoritative merely because it exists.
This is an epistemic metaphor only. Nothing in the migration is physically in quantum superposition, and no quantum mechanism participates in BigQuery promotion.

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.
Daily slices are staggered after the six-hourly ones and after each other, so a day’s refreshes do not start together and each has the day it needs complete.

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.
The migration metaphor helps here too. The watermark is not the fastest wildebeest. It is the boundary behind which the system is willing to claim the crossing is complete.

Single runner: the lease

A refresh must never run twice at once. Two layers enforce it:
  1. 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.
  2. The scheduler. Each pipeline’s workflow job runs in its own concurrency group that never cancels an in-progress run.
A crashed run therefore blocks the next scheduled run for at most 30 minutes, which is inside every cadence. The lease also prevents stale authority from returning. If an old runner wakes after a newer generation owns the claim, its earlier authority no longer matches current state and the write is refused. The interactive lab’s Lease race mode makes that failure visible.

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

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.
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.
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.
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.
The rule throughout the migration is the same: previous known-good state plus an explicit refusal is better than newer partial state plus false confidence.

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.
The deepest change was therefore not that WalletWall began using GCP. It was that analytical serving state became something WalletWall can bound, measure, attribute, refuse, and deliberately promote. In one sentence:
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.