> ## Documentation Index
> Fetch the complete documentation index at: https://docs.walletwall.org/llms.txt
> Use this file to discover all available pages before exploring further.

# BigQuery migration and refresh model

> How WalletWall moved five analytical surfaces to BigQuery with independent cutovers, verified watermarks, bounded budgets, leases, and atomic publication.

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](https://docs.walletwall.org/architecture/data-serving-architecture.md) 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.

> [!NOTE]
>
> 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.

Interactive architecture lab

**Break the migration on purpose.** Change the inputs and watch WalletWall preserve old authority, refuse unsafe progress, or permit a bounded promotion.

Lab mode

Migration slice

· refresh run budget  GiB

**Boundary:** this is a local teaching model. It reads no live deployment state and does not reproduce the refresh implementation. Its labels are explanatory, not WWEP states or deployment status values. No mode relaxes the serving contract: every BigQuery serving read is capped at 64 MiB, and refresh run budgets never replace that ceiling.

## 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.

| Slice | Surface | Grain | Cadence | What the refresh builds |
| - | - | - | - | - |
| 1 | Wallet activity (48-hour list) | Hour-aligned windows | Every six hours | Canonical token-transfer events, then a 48-hour activity snapshot ranked by activity |
| 2 | Stable Seer large flows | Hour-aligned windows | Every six hours | Large stablecoin transfers (at least \$100k at par) with a refresh watermark |
| 3 | Stable Seer volume sparklines | Complete UTC days | Daily | Daily transfer volume per stablecoin |
| 4 | Wallet holdings strip | Complete UTC days | Daily | A lifetime stablecoin ledger per wallet and contract, running balances, and the daily holder cohort the route reads |
| 5 | Quantum signature-exposure facts | Complete UTC days | Daily | Signed-transaction facts per day, chain and signer across covered EVM chains, and a 90-day signer snapshot |

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:

```text theme={null}
Wallet activity       ─────╫──────► BigQuery
Large flows             ─────╫────► BigQuery
Volume history             ────╫───► BigQuery
Wallet holdings              ───╫──► BigQuery
Signature facts                ──╫─► BigQuery

                             promotion gates
```

A failed crossing stays local. Holding one herd on the previous bank is safer than forcing every surface through the same cutover window.

> [!NOTE]
>
> 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.

```text theme={null}
Legacy authority ───────┐
                        ├── evidence + gate ──► selected serving authority
BigQuery candidate ─────┘
```

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.

> [!WARNING]
>
> 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.

```mermaid theme={null}
flowchart TD
    accTitle: One scheduled refresh run
    accDescr: The runner acquires the pipeline's lease by compare-and-set and refuses if another run holds it. It plans the window from the watermark, prices every statement by dry run against the run budget and refuses before sending if the budget cannot cover the whole window replace. It deletes and reloads the window, verifies the loaded rows, and only then advances the watermark by compare-and-set. It rebuilds the snapshot or cohort over a gap-free window and publishes it atomically. It reads status, runs the health check and releases the lease. Any refusal leaves the previous published state in place.
    acquire["Acquire lease<br/>compare-and-set"]:::process --> held{"Another run<br/>holds it?"}:::decision
    held -->|"yes"| refuse["Refuse: nothing read,<br/>nothing written"]:::risk
    held -->|"no"| plan["Plan window from watermark<br/>15-minute safety lag · overlap"]:::process
    plan --> price["Price every statement<br/>(dry run) against the budget"]:::process
    price --> budget{"Budget covers the<br/>whole window replace?"}:::decision
    budget -->|"no"| refuse
    budget -->|"yes"| replace["Delete, then reload the window<br/>(lease renewed before each write)"]:::process
    replace --> verify{"Rows exact?<br/>Every hour populated?"}:::decision
    verify -->|"no"| stop["Stop: watermark unchanged"]:::risk
    verify -->|"yes"| advance["Advance watermark<br/>compare-and-set"]:::process
    advance --> snap["Rebuild snapshot or cohort<br/>over a gap-free window"]:::process
    snap --> publish["Publish atomically<br/>(one statement per range)"]:::datastore
    publish --> health["Status read · six health checks<br/>· release lease"]:::output
    classDef process fill:#BF4E32,stroke:#8B3120,color:#FAF8F3,stroke-width:1.5px;
    classDef decision fill:#C9A47A,stroke:#8B6F47,color:#1E1A14,stroke-width:1.5px;
    classDef output fill:#E6DED2,stroke:#9A9186,color:#1E1A14,stroke-width:1.5px;
    classDef risk fill:#8B3120,stroke:#1E1A14,color:#FAF8F3,stroke-width:1.5px;
    classDef datastore fill:#1E1A14,stroke:#9A9186,color:#FAF8F3,stroke-width:1.5px;
```

**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:

| Refusal | When | Effect |
| - | - | - |
| Budget exhausted | The remaining run budget cannot cover the reservations for both halves of the next window replace | Nothing is sent; the previous published state stands |
| Estimate has no headroom | A statement's 1.25× reservation exceeds that statement's own ceiling, so a small drift in the estimate would strand an emptied window | Nothing is sent |
| Catch-up too large | The pipeline is more than seven days behind its source | The scheduler refuses; an operator loads the gap explicitly, under the budget |

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

> [accordion] 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.

> [accordion] 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.

> [accordion] 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.

> [accordion] 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.

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](https://docs.walletwall.org/operations/bigquery-serving-and-canary.md), 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

> [card] [Data Serving Architecture](https://docs.walletwall.org/architecture/data-serving-architecture.md)
>
> How the request-time reads consume these snapshots, and how responses are labelled.

> [card] [BigQuery Serving & Canary](https://docs.walletwall.org/operations/bigquery-serving-and-canary.md)
>
> The readiness gates a slice must pass before its route is switched to BigQuery, and how rollback works.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.