Data quality, normalization and provenance

Most wrong numbers in blockchain analysis come not from bad heuristics but from mundane data errors: an amount off by a factor of 10^12, a day boundary in the wrong time zone, a transfer counted twice, a Polygon transaction mixed into Ethereum totals. These errors are easy to make and hard to spot in a finished chart. This lesson sets out the normalization rules that prevent them and the provenance record that lets anyone check your work.

Units: keep integers until the end

Blockchains store amounts as integers of the smallest unit. Decimal points are a display convention.

Token decimals vary: 18 is common, but USD Coin uses 6 and Wrapped Bitcoin uses 8. The decimals value is optional in the ERC-20 standard and is purely informative: the contract's balances are always raw integers. Read it from each contract on each chain rather than assuming, because a bridged version of a token can differ from the original. The cost of a wrong assumption is large: a raw amount of 2,500,000,000 is 2,500 tokens at 6 decimals but 0.0000000025 at 18 decimals, a factor of 10^12.

Two traps follow. First, floating point. Bitcoin Core returns amounts in BTC as JSON numbers such as 0.00485900, and a careless parser turns them into binary floating-point values that cannot represent most decimal fractions exactly, so sums drift by tiny amounts and reconciliations fail. Parse amounts as decimal strings and convert to integer satoshis immediately. Second, overflow. Ethereum amounts are 256-bit unsigned integers, up to about 1.16 × 10^77. A signed 64-bit BIGINT tops out at 9,223,372,036,854,775,807, which in wei is only about 9.22 ETH, and even a 38-digit DECIMAL cannot hold every possible value. Store raw amounts as text or in a wide numeric type, and convert at the last step:

-- Raw amounts stay exact; conversion happens only for display
SELECT tt.transaction_hash,
       tt.log_index,
       tt.amount_raw,                     -- exact integer kept as text
       CAST(tt.amount_raw AS DECIMAL(38, 0))
         / POWER(10, tk.decimals)          AS amount_display  -- approximate
FROM token_transfers tt
JOIN tokens tk
  ON tk.chain_id = tt.chain_id
 AND tk.token_address = tt.token_address;

The join on both chain_id and contract address picks the right decimals for each token on each chain. The cast fails for values beyond 38 digits, which flags spam tokens with absurd supplies rather than silently corrupting totals; some engines offer 76-digit decimals or native 256-bit types instead. POWER returns a floating-point number in most engines, so amount_display is an approximation, acceptable for display only; sums and reconciliations use amount_raw. Do not divide by a DECIMAL(38, 0) instead: some engines give the result the scale of the operands, zero here, and silently truncate it to a whole number.

Time: UTC and block time

Block timestamps are Unix time: seconds since 1 January 1970, UTC. Keep every timestamp in UTC, bucket days and months in UTC, and say so in the report. Converting to a local time zone moves transactions across day boundaries, and daylight-saving changes create hours that occur twice or not at all. A transaction mined at 23:30 UTC on 31 March belongs to March in a UTC report but to 1 April for an analyst whose tools silently display UTC+2; if two teams compare monthly totals made that way, they will disagree without either noticing why. Store timestamps as UTC values (or raw Unix seconds) and convert only in the presentation layer, labeling the zone used.

Remember what the timestamp means. It is the block's time, not the moment of signing or broadcast, and on Bitcoin it can be earlier than the previous block's. Order events by height (and position within the block), not by timestamp. When matching activity across chains, allow for different block intervals and timestamp rules.

Identifiers and their formats

The same identifier can be written several ways, and joins fail silently when formats differ.

• EVM addresses appear in EIP-55 mixed-case checksum form (0xA1b2…9F) in wallets and explorers, but in lowercase in logs and most datasets. Lowercase all hex addresses and hashes for storage and joins; keep the checksum form only for display.
• Padded values: an address in a log topic is 32 bytes; the address is the last 20.
• Bitcoin txids are displayed in the reverse of their internal byte order; store the display form.
• Bitcoin addresses: bech32 and bech32m addresses (bc1…) are case-insensitive and conventionally lowercase, but Base58 addresses (1…, 3…) are case-sensitive. Lowercasing a Base58 address destroys it.
• Hex quantities from Ethereum JSON-RPC ("0x1406f52") must be parsed as base 16; reading them as decimal strings or dropping the prefix produces nonsense.

Chain identifiers

The same address can exist on dozens of EVM chains, controlled by the same key but holding unrelated balances, and transaction hashes look identical in format everywhere. Every row needs a chain identifier. EVM chains use the chain ID introduced for replay protection by EIP-155 and returned by eth_chainId:

A community-maintained registry of chain IDs is the usual reference for others. Use eth_chainId rather than net_version, whose network ID can differ from the chain ID. Exclude testnets explicitly, and treat bridged tokens as distinct assets with their own contract addresses on each chain; the lesson Bridges, layer 2s, sidechains and other chain designs covers mapping flows between them. Bitcoin has no chain ID, but its test networks use different address prefixes (tb1… instead of bc1…).

Deduplication and natural keys

Duplicates enter through overlapping backfills, retried requests, reorgs (the same transaction recorded in two blocks) and merged exports. Defining a natural key for each record type makes them detectable:

Why does a Bitcoin transaction need the block hash? Because txids are not guaranteed unique: two early coinbase transactions were repeated exactly, in blocks 91,842 and 91,880, which BIP 30 grandfathered as exceptions and BIP 34 later made impossible by requiring the block height in every coinbase. The query below keeps one copy of each log and only logs from canonical blocks:

-- One row per log, from canonical blocks only
WITH ranked AS (
  SELECT l.*,
         ROW_NUMBER() OVER (
           PARTITION BY l.chain_id, l.block_hash, l.log_index
           ORDER BY l.ingested_at DESC) AS rn
  FROM raw_logs l
)
SELECT *
FROM ranked
WHERE rn = 1
  AND block_hash IN (SELECT block_hash FROM canonical_blocks);

ROW_NUMBER numbers the copies of each key and keeps the latest ingested one; the final filter drops logs from blocks that a reorg replaced. Logical duplicates matter too: counting a Transfer log and the trace of the same token call, or native ETH plus WETH, double-counts value.

Recording provenance

Provenance is the record of where a dataset came from and how it was made. Attach one to every extract that supports a finding:

{
  "dataset": "token_transfers_to_target",
  "chain_id": 1,
  "block_range": [21000000, 21000999],
  "anchor_block_hash": "0x5e1c…a7d0",
  "finality": "finalized",
  "source": "own archive node",
  "client_version": "Geth/v1.16.x",
  "method": "eth_getLogs",
  "query_file": "queries/transfers_to_target.sql",
  "retrieved_at_utc": "2026-10-28T14:05:00Z",
  "row_count": 37,
  "output_sha256": "b94d…e9f1"
}

The block range and anchor hash fix the data; the finality status shows it cannot change; source, client version and method identify how it was obtained; the query file records the transformation; the retrieval time in UTC dates it; and the row count and SHA-256 hash of the output file let anyone confirm they hold the same data. A reviewer with a node can rerun the query and compare. The lesson Tooling, reproducibility and validation extends this to versioned queries in a warehouse.

Common data errors

A cheap final test catches many of these at once: pick three to five rows that matter most to a finding and check each against a node or a second source, comparing block hash, amount in base units and timestamp. If a single row disagrees, stop and find out why before trusting the aggregate.

Exam focus: satoshi, wei, gwei and token decimals, and why amounts stay integers; floating-point and 64-bit overflow risks (BIGINT holds only about 9.22 ETH in wei); UTC timestamps and ordering by height; EIP-55 checksums versus lowercase storage and the case sensitivity of Base58; chain IDs (EIP-155, eth_chainId) and why every row needs one; natural keys for deduplication and the BIP 30 duplicate txids; the fields of a provenance record; the common data errors and how to prevent them.