Tooling, reproducibility and validation

Almost every crypto trace is produced with software, much of it proprietary, fast-changing and opinionated. The court does not need to trust the tool, but it does need to trust the finding. That trust comes from showing that the facts can be re-derived from raw chain data, that the tool was tested for the job, that every setting, version and label source was recorded, and that someone else running the same queries on the same data gets the same rows. The habits that make queries cheap (tight scopes, pinned ranges, explicit versions) are the same ones that make them reproducible.

The tool landscape

Product names do not matter; what each category adds to the evidence does.

Most units use several at once: a platform to explore and generate leads, a node and their own queries to establish facts, and a report built from both. The data stack itself is covered in Lesson 2.7.

Facts, transforms and interpretations

Every tool output mixes three layers. Facts are raw chain data: txids, outputs, amounts, blocks. Deterministic transforms turn raw data into readable form: unit conversion, event decoding, traces. Interpretations add judgment: clusters, labels, change calls, allocation results.

A tool can go wrong at each layer. It can misparse (a token's decimals, an omitted internal transfer, a mishandled reorganization), lack coverage (a token not indexed, a chain not supported, data lagging behind the tip), or apply defaults the examiner never chose (a change heuristic switched on, an allocation method preset). The working rule: every fact in a forensic report must be traceable to raw data that a third party can verify on their own node, and every interpretation must name its method and settings. A screenshot of a graph view illustrates; it does not prove. Keep the underlying exports.

Re-running a trace from raw data

Before a trace is reported, re-derive it outside the tool that produced it:

1. export the tool's path as a hop table: txid, output or log reference, block height, amount in base units;
2. re-derive the same fields from your own node or a warehouse reconciled with one;
3. compare them field by field;
4. recompute allocations with your own script under the declared method;
5. record differences and how each was resolved.

-- Compare the tool's exported hops with values re-derived from our own node
SELECT t.hop_id, t.txid, t.out_ref,
       t.amount_base  AS tool_amount,   n.amount_base  AS node_amount,
       t.block_height AS tool_height,   n.block_height AS node_height,
       CASE
         WHEN n.txid IS NULL THEN 'NOT FOUND ON NODE'
         WHEN t.amount_base <> n.amount_base
           OR t.block_height <> n.block_height THEN 'MISMATCH'
         ELSE 'OK'
       END AS check_result
FROM tool_hops t
LEFT JOIN node_hops n ON n.txid = t.txid AND n.out_ref = t.out_ref
ORDER BY t.hop_id;

The left join keeps every hop the tool reported, so a hop missing from the node data shows as "NOT FOUND ON NODE" rather than disappearing. Amounts are compared as integers in base units, so a decimals error appears as a mismatch by a power of ten. The query checks facts only; agreement says nothing about whether the tool's labels or change calls are right.

Validating tools

Laboratory standards require methods, and the tools that implement them, to be shown fit for purpose. ISO/IEC 17025 requires laboratories to validate non-standard methods, laboratory-developed methods, and standard methods used outside their intended scope or otherwise modified (clause 7.2.2). ISO/IEC 27041 gives guidance on assuring that investigative methods are suitable and adequate. The quality system around this is covered in Lesson 8.3; here the focus is the tools.

SWGDE's Minimum Requirements for Testing Tools Used in Digital and Multimedia Forensics (document 18-Q-001, version 2.1, March 2024) describes three testing strategies, all applicable to blockchain tools: known dataset testing (input with a known answer), comparison testing (at least two tools, checked for consistency) and empirical testing (output checked against other data). It asks for test records stating purpose and scope, tester, date, procedure, datasets and expected results, results with anomalies, and limitations found, and recommends retesting in-house tools at first use and after every revision, and search tools after major updates. The NIST Computer Forensics Tool Testing (CFTT) program offers a model for structuring tests: it is functionality-driven, writing a specification of requirements, assertions and test cases for each tool function before testing.

A practical test plan lists each function of a blockchain tool and gives it a known answer:

Controlled test transactions are especially valuable because the unit knows the truth about ownership and change. Everything found goes into a known limitations register that examiners read before relying on the tool and that is available for disclosure.

Queries that are cheap and repeatable

Full-history tables run to billions of rows, and the same question can cost cents and take seconds, or cost real money and run for an hour.

How engines read data. Warehouses store data by column, so a query reads only the columns it names; SELECT * also reads bulky calldata, and pay-per-scan bills are proportional to bytes read. Large tables are partitioned, usually by day of block timestamp or by block range, and a filter on the partition column lets the engine skip whole partitions (partition pruning). LIMIT 10 does not make a scan cheaper on most warehouses, and a filter on a non-partition column such as to_address prunes nothing. Check bytes or partitions with a dry run or EXPLAIN before touching full history. For scale, Ethereum produces about 7,200 blocks a day (one per 12-second slot), so a week is roughly 50,000 blocks; Bitcoin produces about 144.

Block ranges and time windows. Analysts think in block heights; tables are partitioned by time. Read the small blocks table to find the timestamps covering the range, then filter the large table on both: literal timestamps widened to whole days on the raw partition column, and the exact block range. A window supplied by a join or subquery, or a column wrapped in a function such as CAST(block_timestamp AS DATE), may disable pruning on some engines.

Extract once. Pull every transfer touching the case's addresses in the case's block range in one well-filtered query, save it as a small table or Parquet file, and iterate locally. The expensive scan happens once, and the extract doubles as a fixed snapshot of the evidence.

Sampling suits exploring a table or testing logic, never totals, tracing or anything rare: on-chain value is concentrated in a few large transfers that a 1% sample usually misses. Storage sampling (TABLESAMPLE SYSTEM) returns clustered rows and, on some engines, a different sample each run. A hash-derived filter, such as transaction hashes ending in 00, keeps about 1 in 256 (0.39%), the same rows every run, though it does not make the scan cheaper.

Incremental loads. Monitoring jobs should process only new blocks, but recent blocks can be reorganized, and an append-only job keeps rows from blocks that no longer exist. Each run should delete and reload a lookback range (for example, the highest loaded block minus 64) up to the latest finalized block in one database transaction, so that re-running gives the same table: the job is idempotent. Ethereum finality normally arrives after about two epochs, roughly 13 minutes; on Bitcoin a lookback of at least six blocks is a common convention.

Recording versions, settings and label sources

A result is reproducible when someone else, or you a year later, can run the same query on the same data and get the same rows. That requires a pinned scope (block ranges, never CURRENT_DATE or "last 30 days"); SQL kept under version control and cited by commit; deterministic output, ordered by a full key such as block number then log index, with no approximate functions in reported figures; and recorded data versions for the dataset snapshot and mutable inputs such as label and price tables. Every analytical run then gets a record, kept with the case file:

{
  "run_id": "CF-2026-0142-R07",
  "started_utc": "2026-03-05T08:40:12Z",
  "tool": {"category": "investigation platform", "version": "8.4.1", "data_through_block": 939336},
  "node": {"client": "Bitcoin Core", "version": "29.0", "tip_height": 939388},
  "settings": {"allocation": "haircut", "hop_limit": 6, "change_heuristic": "on",
               "stop_at": "labeled custodial services"},
  "labels_used": [
    {"address": "bc1q…dx7e", "label": "Exchange K deposit", "source": "platform cluster 4471",
     "basis": "co-swept with known deposits", "retrieved_utc": "2026-03-05T08:52:40Z"}
  ],
  "queries": [{"file": "rederive_hops.sql", "commit": "9c41e2a", "blocks": [938940, 939050]}],
  "exports": [{"file": "R07_hops.csv", "sha256": "3b1f…9e02"}],
  "examiner": "EX-07"
}

The record pins the tool version and the block height its data reached, which may lag behind the node's tip, and every setting that changes results. Each label carries its source, basis and retrieval time, because labels change. Each export is hashed, so a reviewer can confirm they reproduced exactly the same rows. The tool's real name goes in the real record. Preserving these files is covered in Lessons 8.2 and 8.3.

Validating data against a node

Datasets are built by software that can fail. Before a figure goes into a report, check a sample of it against the source (Lesson 2.6): compare a computed balance with eth_getBalance, or with balanceOf called through eth_call, at the same block; compare stored block hashes with the node's (getblockhash on Bitcoin, eth_getBlockByNumber on EVM chains) to detect rows from orphaned blocks; compare each block's transaction count with the rows actually loaded; and check continuity.

-- Continuity check: gaps in block numbers and broken parent links
SELECT number, prev_number, parent_hash, prev_hash
FROM (SELECT number, parent_hash,
             LAG(number) OVER (ORDER BY number) AS prev_number,
             LAG(hash)   OVER (ORDER BY number) AS prev_hash
      FROM blocks
      WHERE number BETWEEN 21100000 AND 21107199) AS s
WHERE number <> prev_number + 1          -- a missing block
   OR parent_hash <> prev_hash;          -- a block from another fork

Any row returned is a problem: a missing block, or a stored block that does not link to its predecessor. An empty result over the case's block range, plus matching spot checks, is what lets you write that the data was validated.

When tools disagree

Disagreement between tools is normal and useful: it marks where interpretation begins. Take the trail from Lesson 3.2. Tool A reports that 0.90909091 BTC of the ransom reached bc1q…dx7e and labels it an Exchange K deposit. Tool B reports 1.20000000 BTC and leaves the address unlabeled. Work from the first point of divergence, back to raw data:

Common causes include different clustering (one tool merged two clusters), different change calls, different label sources or dates, different allocation defaults, missing internal transfers or tokens, and different cut-off blocks. Record the disagreement, its cause and its resolution in the case file, and never settle it by choosing whichever tool gives the more convenient answer. Defense experts often probe tool opacity and label provenance (Lesson 8.4).

Exam focus: tool categories and what each adds to the evidence; facts, deterministic transforms and interpretations, and how tools fail at each layer; why reported facts must be re-derivable from raw data and why screenshots only illustrate; re-running a trace and comparing tool exports with node data; ISO/IEC 17025 clause 7.2.2 and ISO/IEC 27041; SWGDE's three testing strategies, test records and retesting; NIST CFTT's functionality-driven model; known-answer tests and the known limitations register; columnar cost, partition pruning with literal ranges and translating block ranges to time windows; when sampling is acceptable; incremental loads with a reorg lookback to the finalized head; the elements of a reproducible query and the fields of a run record; node checks for balances, block hashes, counts and continuity; diagnosing and documenting tool disagreements.