A record of what actually happened when the cost-of-trading labels for Bitcoin bars were computed and written on the production machine — the finished table holds 5,958,856 unique labels covering essentially all 5,958,874 distinct bars with no duplicates, but reaching it took two aborted runs, a bug caught in review before it shipped, and five code fixes, several of whose follow-ups are still being worked through.
Lifecycle, not result. This says where the audit sits in its process — never whether what it found was good.
**Status**: COMPLETE — BTC labels backfilled (5,958,856 unique, 0 dupes, ~100% coverage, non-null audited). Backfiller robustness follow-ups OPEN for next session.
2026-08-17. Adjudicated 2026-08-17: the folder's own header declares 'COMPLETE - BTC labels backfilled (5,958,856 unique, 0 dupes, ~100% coverage)'. The four robustness follow-ups are carry-forward items, not a stop rule; they are recorded in open_items. Challenge pass said: Quote verified verbatim at CLAUDE.md:3. But ACTIVE cannot survive: every remaining item is either operator-gated or dead. (1) Named blocker: 06-hardening-plan.md:76 'Nothing runs on bigblack until the operator authorizes each prod step.' and 07-operator-runbook.md:7 'Every step b
The audit's own conclusion, reproduced in full from the source below. Not a summary — this is the document, rendered. Links inside it that point at unpublished files are shown as plain text rather than as links that would 404 here.
Hub for the operational audit of the BTC label backfill. The implementation audit (PR-level oracle/guard verdicts) lives in the twin folder ../2026-07-08-crypto-cost-realism-labels/. This folder documents what happened when we actually ran the backfill on bigblack — the procedure, the incidents, the fixes, the telemetry, and the follow-up work needed to make the backfiller robust.
> Author: MD NASIM · Read-only-verified · No Telegram sent · All claims backed by telemetry in 04-telemetry-artifacts/.
BTCUSDT cost-realism labels are complete, deduped, and audited.
| Metric | Value | Source |
|---|---|---|
Distinct BTC bars (count() FINAL) | 5,958,874 | ch-audit-queries.md |
| Unique labels written | 5,958,856 | same |
| Coverage (all 4 thresholds) | ~100% (16-bar live-edge tail) | same |
Duplicate label rows after OPTIMIZE FINAL | 0 (raw == unique) | same |
| Non-null on all 8 columns | 99.26–99.97% (rest = empty-window NULL taxonomy) | same |
| Value sanity (crossed fills) | 0 (vwap band = f64 noise ~1e-15) | same |
The label table is correct. The remaining work is making the backfiller robust so the incidents below can't recur — the gaps are in 05-backfiller-robustness-followups.md; the buildable fix-and-test plan is in 06-hardening-plan.md.
| Spoke | What it covers |
|---|---|
01-procedure-timeline.md | The full chronological procedure we followed — phases, runs, deploys, audits |
02-incidents.md | Every incident: OOM crash, duplicate rows, the silent-gap blocker caught in review, and the bars-table dupes (Terry's) |
03-fixes-and-prs.md | Every fix with PR # + commit SHA + what it changed and why |
04-telemetry-artifacts/ | The evidence — captured execution logs, JSONL slices, and the exact ClickHouse audit SQL + outputs |
05-backfiller-robustness-followups.md | Where the backfiller currently breaks, where it's unstable, and the prioritized follow-up fixes (R1–R10) |
06-hardening-plan.md | The buildable plan — R1 decision (staged, script-only), per-gap fix + proof-test, chdb/testcontainers test strategy, 5-PR sequence, verification/rollout. R1 (#635) + R5 (#648) + R6 (#631) merged. |
07-operator-runbook.md | The step-by-step operator runbook — preconditions, knobs, dry-run, the A/B memory rehearsal + systemd-run MemoryMax fence, execute, crash-safe resume, coverage audit, rollback, trap table. |
We shipped a correctness+perf fix (windowed tick slice + GIL release, PR #625) that made the label compute ~instant. That moved the bottleneck from CPU to memory, and the 48-worker default OOM-crashed at 48% on the extreme-volatility days. Restarting at --workers 12 avoided the OOM but exposed a second defect: the parallel path had no resume, so the re-run re-wrote every bar and accumulated ~4.5M duplicate rows while redoing the slow dense days. We root-caused this against the reference repair_direct_parquet.py (which skips already-done work) and added an idempotent day-scoped anti-join resume (PR #626). An adversarial attacker/defender pass on #626 caught a silent-gap blocker (the anti-join key was narrower than the read VIEW's consistency key → a kintsugi-recomputed bar would be permanently NULL) which we fixed before merge. The final resume run completed in ~10 min at ~2 GB RAM, 100% coverage, zero duplicates. Separately, we confirmed the heartbeat's "11M bars" is the raw count() inflated by ~5.96M unmerged ReplacingMergeTree duplicates written by Terry's warehouse_fill.py reprocess — not our work, and not a bug (transient pre-merge state).
Every row pairs a claim with the file it came from and the verbatim text in that file. The sources sit above the deploy root, so the quote is embedded and the path is printed as text rather than linked — a link would resolve on a laptop and 404 here.
| Claim | Evidence |
|---|---|
| Label coverage against the bars table is ~100% at every one of the four thresholds after dedup. MEASURED BTCUSDT: @100 4,819,222 labelled of 4,819,236 bars; @250 872,980/872,980; @500 180,326/180,327; @750 86,328/86,329 — all 100% (n = 4 thresholds, deduped uniqExact) | 100 │ 4,819,236 │ 4,819,222 │ 100 findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/04-telemetry-artifacts/ch-audit-queries.md |
| After the closing OPTIMIZE FINAL the label table contains zero duplicate rows — the raw row count equals the unique-key count. MEASURED raw 5,958,856 rows = unique 5,958,856 keys, 0 duplicates (n = all BTCUSDT label rows; key = threshold, first/last_agg_trade_id, close_time_us) | -- → 5958856 5958856 0 findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/04-telemetry-artifacts/ch-audit-queries.md |
| All eight measured label columns are populated on 99.26–99.97% of rows; the remaining sub-1% are rows whose forward window contained no ticks, which are NULL by design rather than gaps. MEASURED 99.26–99.97% non-null across 8 columns × 4 thresholds; lowest cell = roundtrip cost @100 at 98.95% | | Non-null on all 8 columns | **99.26–99.97%** (rest = empty-window NULL taxonomy) | same | findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/README.md |
| No label pair is economically impossible: zero crossed buy/sell fills, and the apparent VWAP-band breaches are floating-point noise rather than real violations. MEASURED crossed_entry = 0 and crossed_exit = 0 at all 4 thresholds; max relative VWAP excursion 9.4e-15 (@100) down to 4.7e-16 (@750) | -- max_rel_oob: 9.4e-15 (@100) … 4.7e-16 (@750) ⇒ f64 machine-epsilon, not a real crossed value. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/04-telemetry-artifacts/ch-audit-queries.md |
| Incident A — the fast 48-worker run exhausted memory and died just under halfway through, after a steadily climbing memory footprint. CONFIRMED crash at ~48% = 5,635,485 rows written; RSS 2.5 → 14.4 GB over 5-minute probes; failed allocation of 2,560,264 bytes | RSS trajectory before the crash (5-min probes): 2.5 → 6.7 → 10.2 → 14.4 GB, then a spike to OOM. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/02-incidents.md |
| The out-of-memory root cause is that the performance fix made the compute nearly free, moving the bottleneck from processor to memory — 48 day-workers each holding a roughly 10-million-tick day at once. CONFIRMED 48 concurrent workers × ~10M ticks/day on the 2020–2023 cluster; the --workers 12 mitigation held at ~1–2 GB | Fix A (PR #625) made the label compute ~instant, which **moved the bottleneck from CPU to memory**. The parallel path fans **48 day-workers** across one symbol; on the extreme-volatility cluster (2020–2023) each worker holds a ~10M-tick day in memory at once → 48 concurrent giant reads → RAM+swap exhausted. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/02-incidents.md |
| Incident B — restarting at 12 workers produced roughly 4.5 million duplicate rows because the parallel code path had no resume and re-wrote already-completed days. CONFIRMED raw ~10.3M rows vs ~5.8M unique ⇒ ~4.5M duplicates; true progress flat at ~46% while the raw count read 84% | **Symptom.** The `--workers 12` re-run's **raw** `count()` climbed to ~10.3M while the **deduped** unique count sat at ~5.8M — i.e. ~4.5M duplicate rows, and the true progress was effectively flat (re-doing done work). findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/02-incidents.md |
| Incident C — the first resume design would have left recomputed bars unlabelled forever; it was caught in adversarial review before merge, and the widened key subsequently re-labelled about 175,000 bars that would otherwise have read empty. CONFIRMED ~175,000 stale-consistency bars re-labelled; anti-join key widened from 2 to 4 columns (threshold, first_agg_trade_id, last_agg_trade_id, close_time_us) | **This is why the review matters — the final resume run actually re-labelled ~175k stale-consistency bars.** findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/02-incidents.md |
| Incident D — the ~11M bar count reported by the heartbeat is inflated by unmerged duplicate rows; the true distinct bar count is 5,958,874, established by three concordant measurements. MEASURED raw count 11,920,268; distinct 5,958,874; duplicate rows 5,961,394 (BTCUSDT bars table); count() FINAL independently returns 5,958,874 | -- → 11,920,268 5,958,874 5,961,394 findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/04-telemetry-artifacts/ch-audit-queries.md |
| Those bars-table duplicates were written by another writer (Terry's warehouse_fill.py reprocess), not by the label runner, which only reads the bars table. CONFIRMED 5,953,273 rows tagged 13.77.0 written 2026-07-14 16:22 → 2026-07-15 12:06, alongside 5,937,728 rows tagged 13.76.0+repair-parquet from 2026-07-07 | | `13.77.0` (bare) | 5,953,273 | Jul-14 16:22 → Jul-15 12:06 | **Terry's `warehouse_fill.py`** #603 reprocess | findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/02-incidents.md |
| The performance fix (windowed tick slice plus releasing the interpreter lock) was validated as producing identical labels through a large differential test. CONFIRMED 25,000 differential cases, 0 mismatches; 8 new tests | **Verification.** Attacker ran **25,000 differential cases → 0 mismatch**; defender's proof verified; **8 tests** added findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/03-fixes-and-prs.md |
| The windowed-slice fix collapsed a quadratic scan into a near-instant one, measured on a micro-benchmark. MEASURED 1,000,000 ticks × 1,000 anchors in 179.6 ms; dry-run reproduced 172,654 would-writes in 16.6 min versus 28.5 min sequentially | Micro-benchmark confirmed Fix A live: 1M ticks × 1000 anchors in **179.6 ms** (was seconds). findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/01-procedure-timeline.md |
| The final resume run completed the backfill in about ten minutes at roughly 2 GB of memory. MEASURED 608.8 s wall, 340,123 rows written, 18 bars held at the live edge, ~2 GB RAM, 12 workers | Resume execute at `--workers 12`: **DONE in 608.8s (~10 min)**, `written=340,123`, `live_edge_held=18`, ~2 GB RAM. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/01-procedure-timeline.md |
| Three earlier script bugs (wrong tick-cache key, a microsecond-versus-millisecond unit error, and a concurrency panic) were found in dry runs and fixed before the real backfill. CONFIRMED 3 pre-execution bugs (E1/E2/E3) closed by PRs #621, #622 and a to_numpy change folded into #625 | | E1 | `read_ticks` used bare symbol, not `BINANCE_SPOT_*` cache key → 0 ticks | PR #621 (`get_tick_cache_key`) | findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/02-incidents.md |
| The memory model is still the highest-severity weakness in the runner, and at the time of writing was mitigated only by a hand-picked worker count. ASSERTED 11 follow-up items catalogued: R1–R10 plus cross-team C1; 1 rated HIGH, 5 MED, 5 LOW or LOW-MED | | **R1** | Worker/memory model → OOM | 🔴 HIGH | mitigated (`--workers 12` manual) | in-runner memory backpressure / process-pool / Arrow path | findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/05-backfiller-robustness-followups.md |
| Running the sequential path to finish a partial parallel run was a latent data-loss footgun, because its watermark resume can skip earlier unlabelled bars. CONFIRMED | `process_partition` resumes by **watermark = max(first_agg_trade_id) labelled**, but parallel writes are **out-of-order**, so the watermark can sit above un-labelled earlier bars → those bars are **skipped (gapped)**. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/05-backfiller-robustness-followups.md |
| The existing test suite could not have caught a malformed resume query — its stub client merely checks that the SQL string contains the words NOT IN. CONFIRMED 2 stub-only resume tests cited (test_cost_realism_labels.py lines 959 and 1042) | its resume tests use a `StubClient` whose `.query()` merely checks `"NOT IN" in sql` (`tests/test_cost_realism_labels.py:959,1042`) — a **malformed anti-join ships green**. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/06-hardening-plan.md |
| The hardening work is planned as five dependency-ordered pull requests, of which only the first is described as in flight in this folder. OPEN 5 planned PRs covering gaps R1–R10; 1 in flight, 4 pending at the time of writing | - **PR-1 in flight** (this session) — chdb harness + R6 anti-join integration tests, verified locally against in-process ClickHouse. - PRs 2–5 pending, in the order above. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/06-hardening-plan.md |
| A later spoke states that three of the follow-ups shipped, and rewrites the operator procedure around them. ASSERTED 3 merged PRs claimed: #635 (R1), #648 (R5), #631 (R6) | after the hardening in [`06-hardening-plan.md`](06-hardening-plan.md) shipped (R1 memory backpressure #635, R5 order-independent resume #648, R6 real-CH test harness #631) findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/07-operator-runbook.md |
| A hard operating rule was added: any rebuild of the bars table must be followed by wiping and re-running the matching label partition, otherwise orphan label rows are left that no later run can clean up. ASSERTED enforced by a startup orphan preflight plus --fail-on-orphans (exit code 3) and integrity_audit.py Check 4 (min-year coverage ≥ 99.5% and orphan_label_rows == 0) | **The hard rule:** any `DROP PARTITION` / genesis / boundary-changing rebuild of the **bars** table (`repair_direct_parquet.py`, `warehouse_fill.py`, the `ch-clean-slate` skill, …) **must be followed by a clean-slate + re-run of the corresponding label partition.** findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/07-operator-runbook.md |
| The cross-team duplicate-bars issue was deliberately removed from this folder's buildable plan rather than resolved. OPEN | > - **C1 (bars-table RMT duplicates) is dropped** — Terry's `warehouse_fill.py`, his domain. findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/06-hardening-plan.md |
Source of record: findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/ — not published, so these are listed rather than linked.
| File | Role |
|---|---|
01-procedure-timeline.md | Chronological Phase 0–10 record of the backfill, including every stop, deploy and re-run. |
02-incidents.md | The four incidents (out-of-memory crash, duplicate rows, the pre-merge silent-gap catch, and the foreign bars-table duplicates) with root causes and telemetry pointers. |
03-fixes-and-prs.md | Every fix with PR number, squash-merge commit SHA, what changed and why, plus the non-code production operations applied. |
04-telemetry-artifacts/README.md | Artifact-to-incident map for the captured execution logs and JSONL slices, plus a note on where telemetry failed (the zero-byte log). |
04-telemetry-artifacts/ch-audit-queries.md | The authoritative evidence: exact read-only ClickHouse SQL and its outputs for coverage, dedup, non-null, value sanity and the duplicate provenance. |
05-backfiller-robustness-followups.md | Prioritised weakness register R1–R10 plus cross-team C1: where the runner breaks, where it is unstable, and what each fix would be. |
06-hardening-plan.md | The buildable plan: per-gap fix with a negative-control proof test, the real-ClickHouse test strategy, a five-PR sequence and the rollout gates. |
07-operator-runbook.md | Forward-looking operator procedure: preconditions, knobs, dry run, A/B memory rehearsal under a systemd fence, execute, resume, coverage audit, rollback, rebuild coupling and a trap table. |
CLAUDE.md | Auto-load navigator: status line, spoke index and one-line verdict; explicitly defers the hub role to README.md. |
README.md | Operator-chosen hub: outcome table with the six headline numbers, spoke index and a one-paragraph narrative of the whole episode. |
findings/dashboard/build_audits.py from findings/evolution/audits/2026-07-15-crypto-cost-realism-labels-backfill/AUDIT_LEDGER.json — never hand-edited. Each quote was verified to occur in the file named beside it when the ledger was written.