The analytics data stack

Almost every number in an on-chain report has passed through a pipeline between the node that saw the block and the query that produced the figure. An analyst who knows which layer a table belongs to, who built it, how fresh it is and what rules shaped it can tell a real finding from a pipeline artifact. This lesson maps that stack and the choices you make when picking tools for it.

From node to table

A node stores and serves blockchain data in the form the protocol needs: blocks, transactions, receipts and state, reached through RPC calls (see the lesson Node APIs and data access). Analysts need the same data as rows and columns that can be filtered, joined and summed. The process that turns one into the other is an ETL pipeline: extract, transform, load.

• Extract. The pipeline calls the node for every block in a range: the block with its transactions, the receipts with their logs, and, where the node supports it, the call traces.
• Transform. Nested JSON is flattened into one row per record. Hex strings are converted to numbers, the block timestamp is copied onto every transaction and log so queries do not need a join for time, and identifiers are normalized (for example, addresses in lowercase).
• Load. Rows are written to storage, usually as columnar files such as Apache Parquet partitioned by date or block range, or into database tables. Open table formats such as Apache Iceberg and Delta Lake add versioned snapshots on top of the files.

The key idea is grain: what one row represents. One Ethereum block with 150 transactions and 400 event logs becomes one row in blocks, 150 rows in transactions and 400 rows in logs. Most query errors come from forgetting the grain of a table and counting or summing at the wrong level.

Pipelines run in two modes. A backfill processes history in large batches, once. Streaming or incremental loads follow the chain head block by block. Near the head, blocks can still be replaced by a reorganization, so a pipeline either waits for a confirmation depth or a finalized block, or rewrites recent blocks when the chain changes (see the lesson Blocks and headers as data).

Open-source extractors exist for most of this work: the blockchain-etl project's Ethereum ETL and Bitcoin ETL, and cryo, which writes EVM data straight to Parquet. They are worth knowing even if you never run them, because many public datasets were built with them and inherit their column names.

Raw, decoded and curated tables

Tables in a blockchain warehouse fall into three layers, which mirror the data layers introduced in the lesson The analyst's view of a blockchain.

Each step up makes queries shorter and adds decisions someone else made. A curated token_transfers table answers questions in one line, but you must know its rules. Does it include ERC-721 transfers or only fungible tokens? Are mints and burns rows with the zero address, or excluded? Is the amount the raw integer or already divided by decimals? Two datasets that both call a table token_transfers can disagree on all of these.

The habit that protects you is reconciliation: check a curated table against the raw layer it came from, on a small block range.

-- Does the curated token_transfers table match the raw logs it came from?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- fungible (ERC-20 style) transfers only
  AND block_number BETWEEN 21000000 AND 21000099
UNION ALL
SELECT 'token_transfers', COUNT(*)
FROM token_transfers
WHERE block_number BETWEEN 21000000 AND 21000099;

The first half counts raw Transfer events (the long hex string is the event's signature hash, explained in the lesson Querying EVM logs and traces); the second counts the curated rows for the same 100 blocks. If the numbers differ, find out why before using the table: the curated layer may drop some tokens, include NFTs, or be missing blocks.

Public blockchain datasets

You do not need your own pipeline to start. Several free, public datasets put full chain history in query engines or in files.

• Google BigQuery public datasets. The crypto_bitcoin and crypto_ethereum datasets (with others for several chains) were built with the blockchain-etl tools. The Ethereum dataset has blocks, transactions, logs, traces, token_transfers, contracts and tokens tables; its logs store topics as an array rather than separate columns. Querying them requires a cloud account and is billed by data scanned beyond a free allowance.
• AWS Public Blockchain Data. Compressed Parquet files in a public storage bucket, partitioned by date and updated daily. It covers Bitcoin, Ethereum and several other chains; check its documentation for the current list. Files can be read by any engine that understands Parquet.
• Hosted query platforms. Several companies run community platforms with raw, decoded and curated tables for many chains and a free tier. They are convenient for decoded data, but their curated tables follow each provider's rules.

Public does not mean correct or current. Datasets lag the chain by minutes to a day, change schemas, and occasionally miss blocks after an extraction bug. Before relying on any table, profile it.

-- Profile a table before trusting it: coverage, freshness, duplicates
SELECT
  MIN(block_number)          AS first_block,
  MAX(block_number)          AS last_block,
  MAX(block_timestamp)       AS latest_data_utc,
  COUNT(*)                   AS row_count,
  COUNT(DISTINCT hash)       AS distinct_hashes  -- should equal row_count
FROM transactions;

This query tells you the block range covered, how recent the newest row is, and whether any transaction appears twice. On a full chain it scans only a few columns, but of a very large table, so on a pay-per-scan engine run it on a recent date range first (see the lesson Performance, cost and reproducibility).

Query engines and notebooks

The engine is the software that runs your SQL. The same data can sit behind very different engines.

Core SQL (SELECT, JOIN, GROUP BY, window functions) is shared, but function names, date handling, array syntax and even how a table is named differ.

-- One question, three ways of naming the data
-- Warehouse public dataset (project.dataset.table, BigQuery quoting):
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- Embedded engine reading Parquet files directly (DuckDB syntax):
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Any engine holding the generic schema used in this module:
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

All three count the blocks produced on one UTC day. Only the table reference and the date syntax change; the logic is identical. The lessons in this module use a generic schema and flag the main dialect differences as they appear.

Notebooks (Jupyter and similar) combine SQL cells, code in Python or R, charts and written notes in one document, which suits investigations: query, inspect, plot, write down the reasoning. Some tasks fit SQL poorly, notably iterative graph work such as finding connected components for clustering; analysts export edges from SQL to a graph library for those (see the lesson Querying UTXO data).

Choosing tools

There is no best stack, only a fit to the question, the budget and the sensitivity of the work. Weigh these criteria:

• Coverage. Which chains, which date range, and does it include traces and decoded events?
• Freshness. Do you need data from the last block or is yesterday enough?
• Cost model. Pay per data scanned, per compute time, or fixed infrastructure you run yourself.
• Reproducibility. Can you pin a snapshot or block height and get the same answer next month?
• Confidentiality. Queries on hosted services are logged by the provider, and on some community platforms they are public by default. Typing a suspect's address into a shared platform can reveal an investigation. For sensitive work, extract a block range and query it locally.
• Provenance. Can every row be traced back to a block hash and transaction hash you can verify on a node?

Typical fits:

Whatever you choose, record the dataset, its version or snapshot date, the query text and the run time with every result.

Exam focus: the three ETL stages and what the transform step does; the meaning of grain and why one block becomes many rows; the difference between raw, decoded and curated tables and the risks of each; why curated tables must be reconciled against raw data; the main public datasets (BigQuery crypto datasets, AWS Public Blockchain Data in Parquet) and their limits; categories of query engines and when an embedded engine fits; why notebooks suit investigations; tool-selection criteria, especially confidentiality of hosted queries and reproducibility.