Performance, cost and reproducibility

Full-history blockchain tables run to billions of rows, and the same question can cost a few cents and take seconds, or cost real money and run for an hour, depending on how the query is written. The habits that keep queries fast and cheap (tight scopes, pinned ranges, explicit versions) are also the habits that make results reproducible, which is what turns a query result into evidence. This lesson covers both, and ends with how to check a result against a node.

How analytic engines read data

Warehouses and lake engines store data by column, not by row. A query reads only the columns it names, so SELECT * on a transactions table also reads the bulky input calldata column even when you need three narrow columns. On pay-per-scan services, the bill is proportional to the bytes read, so naming columns is the first cost control.

The second is partitioning. Large tables are split into partitions, usually by day of block timestamp or by block range, stored as separate files or segments. When a filter on the partition column rules a partition out, the engine skips it entirely: this is partition pruning. Within partitions, data may be clustered (sorted) by a column such as block number or address, and engines keep minimum and maximum values per chunk so they can skip chunks that cannot match.

Some intuitions to correct:

• LIMIT 10 does not make a scan cheaper on most warehouses; the filter and scan happen before the limit.
• A filter on a non-partition column (such as to_address) does not prune partitions, though clustering on that column can help.
• Most services offer a dry run or EXPLAIN that reports the bytes or partitions a query will read. Use it before running anything on full history.

Scale helps estimate cost. Ethereum produces one block per 12-second slot, so about 7,200 blocks per day; Bitcoin about 144. A one-week scope on Ethereum is roughly 50,000 blocks, a small fraction of the more than 20 million in the chain's history.

Partition and block-range filters

Analysts think in block heights; tables are partitioned by time. The reliable pattern is to translate the block range into a time window first, then filter on both.

-- Step 1: find the time window that covers the block range
SELECT MIN(block_timestamp) AS t0, MAX(block_timestamp) AS t1
FROM blocks
WHERE number BETWEEN 21100000 AND 21107199;

-- Step 2: filter on the partition column with literals, then on blocks
SELECT transaction_hash, from_address, to_address, amount_raw
FROM token_transfers
WHERE block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'  -- step 1, widened
  AND block_timestamp <  TIMESTAMP '2024-11-05 00:00:00'  -- to whole days
  AND block_number BETWEEN 21100000 AND 21107199           -- exact scope
  AND token_address = '0x7a11…0001';

Step 1 reads the small blocks table. Step 2 uses the time filter so the engine reads only a few daily partitions, and the block filter to return exactly the intended rows. Widening the window to whole days costs little and guards against timestamp edge cases.

Two cautions. Many engines prune only when the partition filter compares the column with constants, so a join or subquery that supplies the window may scan everything; pass the values as literals or query parameters. And wrapping the partition column in a function, such as CAST(block_timestamp AS DATE) = …, can disable pruning on some engines; compare the raw column with a range instead.

A further saving comes from extracting once and iterating locally. An investigation typically runs dozens of variations of the same question over the same addresses and blocks. Rather than scanning full-history tables each time, run one well-filtered query that pulls every transfer touching the case's addresses in the case's block range, save it as a small table or Parquet file, and do the exploration there, for example in an embedded engine on a laptop. The expensive scan happens once, the iterations are nearly free, and the saved extract doubles as a fixed snapshot of the evidence, which helps reproducibility as much as cost.

Sampling

Sampling is useful for exploring an unfamiliar table, testing query logic, and estimating distributions such as typical fee levels. It is wrong for totals, for tracing, and for anything rare. On-chain value is heavily concentrated: a handful of transfers can carry most of a day's volume, and a 1% sample will usually miss them.

There are two kinds of sample with different effects:

• Storage sampling. TABLESAMPLE SYSTEM (1 PERCENT) (supported in some form by BigQuery, PostgreSQL, Trino and DuckDB) reads a random subset of storage blocks. It cuts the bytes read, but the rows are clustered rather than independent, and on some engines a rerun returns a different sample.
• Deterministic sampling. A filter on a hash-derived value returns the same rows every time.

-- Repeatable sample: txids are uniform hashes, so '%00' keeps ~1 in 256
SELECT COUNT(*) AS sampled
FROM transactions
WHERE block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND block_timestamp <  TIMESTAMP '2024-12-01 00:00:00'
  AND hash LIKE '%00';

Because transaction hashes are effectively random, keeping those that end in 00 selects about 1 in 256 (0.39%), the same ones on every engine and every run. The filter still reads the hash column for the whole range, so it makes results smaller and repeatable, not the scan cheaper. Combine it with a tight partition filter.

Incremental models

Monitoring jobs (a watchlist of addresses, daily flows to exchanges) should not recompute history every run. An incremental model processes only new blocks and adds them to a derived table. The trap is the chain head: recent blocks can be reorganized, so a job that only appends will keep rows from blocks that no longer exist (see the lesson Blocks and headers as data).

-- Incremental refresh with a reorg lookback; safe to re-run
-- :from_block = highest block already loaded minus 64
-- :to_block   = latest finalized block
DELETE FROM watched_transfers
WHERE block_number >= :from_block;

INSERT INTO watched_transfers
SELECT tt.*
FROM token_transfers AS tt
WHERE tt.block_number BETWEEN :from_block AND :to_block
  AND EXISTS (SELECT 1 FROM watchlist AS w
              WHERE w.address IN (tt.from_address, tt.to_address));

Each run deletes the lookback range and reloads it, so running the job twice gives the same table: the job is idempotent. Loading only up to the finalized block (on Ethereum, finality normally arrives after about two epochs, roughly 13 minutes) avoids most reorg issues; on Bitcoin, a lookback of at least six blocks is a common convention. EXISTS is used instead of a join so that a transfer between two watched addresses is inserted once, not twice. Run both statements in one database transaction so readers never see a half-loaded range. Parameter syntax varies: :name in many clients, @name in BigQuery, $1 in PostgreSQL, ? in JDBC. Open-source tools such as dbt implement this pattern as "incremental models", and a periodic full rebuild catches drift the increments missed.

Versioned, reproducible queries

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:

• Pinned scope. Use block ranges, never CURRENT_DATE or "last 30 days".
• Version control. Keep SQL in a repository and cite the commit in the report; no hand-edited copies.
• Deterministic output. Order by a full key (block number, then log index); without ORDER BY, row order can change between runs. Avoid approximate functions in reported figures.
• Data versions. Record the dataset, its snapshot or extraction date, and the version of mutable inputs such as label and price tables.
• A run record saved next to the result:

{"query": "flows_to_exchanges.sql", "commit": "9c41e2a",
 "dataset": "eth_transfers_parquet", "snapshot": "2026-10-01",
 "blocks": [21100000, 21107199], "labels_version": "v14",
 "engine": "duckdb 1.x", "run_utc": "2026-10-28T14:05:00Z",
 "rows": 1832, "result_sha256": "5be1…a7d0"}

The hash of the exported result lets a reviewer confirm they reproduced exactly the same rows. The lesson Writing the analytical report covers how these records go into the appendix.

Validating results 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 (see the lesson Data quality, normalization and provenance):

• Balances. Compare a balance computed in SQL with eth_getBalance for the address at the same block, or with balanceOf (selector 0x70a08231) called through eth_call at that block.
• Blocks. Compare stored block hashes with the node's (getblockhash on Bitcoin, eth_getBlockByNumber on EVM chains) to detect rows from orphaned blocks.
• Counts. Compare each block's transaction_count with the number of rows actually loaded in transactions.
• Continuity. Check that block numbers have no gaps and that each block's parent hash matches the previous block's hash.

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

Exam focus: why columnar engines charge for columns read and why SELECT * and LIMIT mislead; partition pruning and why the partition filter should use literal ranges on the raw column; translating block ranges into time windows; when sampling is acceptable and the difference between storage and deterministic hash sampling; incremental models with a reorg lookback, finalized heads and idempotent delete-and-insert; the elements of a reproducible query (pinned block range, version control, deterministic order, data versions, run record); validating against a node with balance, block hash, count and continuity checks.