Blocks and headers as data

Blocks are the unit in which a blockchain records time and order, so every timeline, daily total and "before or after" statement an analyst makes depends on reading them correctly. Blocks can also be replaced: the most recent part of any chain is provisional. Knowing what a header contains, how far its timestamp can be trusted and when a block is safe to rely on keeps provisional data out of your findings.

What a header contains

A block header is a small record that summarizes the block and links it to its parent. A Bitcoin header is exactly 80 bytes with six fields:

The block hash is not a header field: it is the double SHA-256 of the 80 header bytes, and explorers display it in reversed byte order, which is why it begins with zeros. Bitcoin Core's getblock adds derived values such as height, mediantime, chainwork and confirmations.

An Ethereum header has more fields because it also commits to state and receipts. The ones analysts use most:

Fields appear only from the upgrade that introduced them, so a pipeline must accept their absence in older blocks. Since the Merge, difficulty and nonce are zero and mixHash carries the beacon chain's randomness value. Pre-Merge data has one more feature: ommer (uncle) blocks, valid blocks that lost a race but were referenced by a later block through sha3Uncles and the uncles list. Their miners received a reduced reward without any transaction, so mining-era reward analysis must include them; since the Merge the list is always empty.

Height versus hash

Height (Ethereum's number) is a position: the count of blocks since genesis. The block hash is an identity. Two different blocks can share a height when the chain forks, and only one of them ends up in the main chain. A hash, by contrast, identifies exactly one block forever.

Use height for convenience and ranges ("blocks 21,000,000 to 21,000,999") and hash for identity. In storage, keep both and treat the hash as the key. When you cite a block in a report, give both: "height 880,214, hash 0000…3f1a." The Bitcoin RPC getblockhash returns the hash currently at a height, which can change near the tip; Ethereum offers both eth_getBlockByNumber and eth_getBlockByHash.

Timestamps and their reliability

A block's timestamp is the time an analyst attaches to every transaction in it. How precise it is depends on the chain.

Bitcoin timestamps are set by the miner within two rules: the time must be greater than the median of the previous 11 blocks (the median time past), and nodes reject blocks more than two hours ahead of their own clock (older versions of Bitcoin Core used a "network-adjusted" time from peers instead). A block can therefore carry an earlier timestamp than its parent, and any single timestamp can be off by an hour or more. For ordering, use height; for a monotonic clock, Bitcoin Core's mediantime never decreases. In reports, state Bitcoin times at a precision the data supports.

Ethereum since the Merge (September 2022) has fixed 12-second slots, and a block's timestamp equals the genesis time plus 12 seconds times its slot number. Timestamps are exact and regular; a gap of 24 or 36 seconds between consecutive blocks means one or two slots had no block. Before the Merge, miners set timestamps under looser rules. On rollups, the sequencer sets timestamps under each rollup's own rules.

Two cautions apply everywhere. The block time is when a transaction was included, not when it was signed or broadcast. And "first seen" times shown by explorers come from their own mempool observers: useful clues, but local to one observer.

Reorganizations

A reorganization (reorg) happens when nodes switch from the chain they were following to a competing branch. The replaced blocks are called stale or, in common usage, orphaned.

On Bitcoin, two miners occasionally find blocks at almost the same height and time. Nodes follow the branch with the most cumulative work (chainwork), and when the next block extends one side, the other side's block goes stale. Its transactions usually reappear in the winning branch, but possibly in a different block and position, or not at all if a conflicting transaction confirmed instead. Bitcoin Core lists known branches with getchaintips, and getblock reports confirmations of −1 for a block that is not on the main chain.

On Ethereum, proof of stake makes reorgs rarer and usually one block deep. Logs delivered by subscriptions or filters carry removed: true when a reorg drops them, a signal pipelines must handle.

Finality for analytics

Finality is the point at which a block can be treated as permanent.

• Bitcoin finality is probabilistic. Each additional block (a confirmation) makes replacement exponentially more expensive. Six confirmations is a common convention for value transfers, and newly mined coins (coinbase outputs) cannot be spent for 100 blocks.
• Ethereum has explicit economic finality. Slots are grouped into epochs of 32 slots (6.4 minutes); validators vote on epoch checkpoints, which become justified and then finalized, normally after about two epochs, roughly 13 minutes. Reverting a finalized block would require at least one third of all staked ETH to be destroyed.

Ethereum JSON-RPC exposes this through block tags accepted in place of a block number: earliest (genesis), latest (the newest block the node follows), safe, finalized and pending. The meaning of safe depends on the consensus client: many return the latest justified checkpoint, a few minutes behind latest, and some clients are adopting a fast confirmation rule that moves it within seconds of the head. Check what your node returns rather than assuming.

The query below counts daily transactions only up to a cutoff fetched from the node with eth_getBlockByNumber("finalized", false):

-- Daily transaction counts, never beyond the finalized block
SELECT DATE_TRUNC('day', b.block_time) AS day_utc,
       COUNT(*)                         AS tx_count
FROM transactions t
JOIN blocks b ON b.block_hash = t.block_hash
WHERE b.block_number <= :finalized_block
GROUP BY DATE_TRUNC('day', b.block_time)
ORDER BY day_utc;

Joining on block_hash rather than height keeps transactions from stale blocks out of the count. DATE_TRUNC exists in PostgreSQL and several warehouses; other engines use TIMESTAMP_TRUNC or a date cast.

Handling orphaned data

Any pipeline that ingests recent blocks will meet reorgs. A robust design does four things: stores block_hash and parent_hash for every block; on each new block, checks that its parent hash matches the stored hash one height below; when it does not, walks back to the common ancestor, marks the rows from replaced blocks as non-canonical and ingests the new branch; and keeps the replaced rows in an audit table instead of deleting them. The check below finds breaks in the stored chain:

-- Blocks whose parent is missing: gaps or unhandled reorgs
SELECT c.block_number, c.block_hash, c.parent_hash
FROM blocks c
LEFT JOIN blocks p ON p.block_hash = c.parent_hash
WHERE p.block_hash IS NULL
  AND c.block_number > (SELECT MIN(block_number) FROM blocks);

The audit table matters in casework. Suppose a deposit was credited after one confirmation, the block went stale and a conflicting transaction spending the same coins confirmed instead. An explorer's cached page may still show the first transaction. Only stored stale-block data lets an analyst explain why a "confirmed" deposit never existed on the final chain.

Exam focus: the six Bitcoin header fields and that the block hash is computed, not stored; key Ethereum header fields and the upgrades that added them; height as position versus hash as identity; Bitcoin timestamp rules (median time past, two-hour limit) and why timestamps can be out of order; Ethereum's 12-second slots and exact timestamps; reorgs, stale blocks and removed: true logs; confirmation depth, epochs, justified and finalized checkpoints and the JSON-RPC tags latest, safe and finalized; detecting and recording orphaned data.