We develop a blockchain data lake — a layer that solves the core problem of on-chain data storage: raw blockchain data is not suited for complex queries. JSON-RPC nodes answer 'what happened in block X' but not 'show all Uniswap V3 swaps in the last 30 days for addresses with high volume'. The data lake transforms raw blocks into structured, indexed, and quickly queryable tables.
Ethereum mainnet today — about 20 million blocks, roughly 2 billion transactions, and terabytes of event logs. The full Ethereum history in Parquet format takes 3–4 TB. On each new block (every 12 seconds), hundreds of transactions and thousands of log records are added. Add BSC, Polygon, Arbitrum, Base — each network has its own history and growth rate. Our data lake unifies them into a single analytical environment.
Why a Data Lake Is Necessary for Blockchain Analytics
Three data classes with different characteristics:
Blocks & transactions — structured, predictable schema. The main challenge: reorgs — temporary forks after which the chain rewrites. The EVM data pipeline must be able to roll back already written data.
Event logs — the most valuable for analytics. Transfer, Swap, Liquidation, Mint — all EVM events. Problem: ABI decoding. Without a contract's ABI, a log is just bytes with topics. We create an ABI registry via Etherscan API and Sourcify to decode millions of events automatically.
Traces (internal transactions) — calls between contracts that do not create a direct transaction. Without traces, a significant part of DeFi is invisible: flash loans inside a single tx, recursive liquidations, MEV bundles. Getting traces via debug_traceTransaction is a heavy operation, available only on archive nodes.
Reorg Handling
Reorg is the main headache of any blockchain indexing pipeline. Ethereum with Proof-of-Stake has probabilistic finality after a few blocks and full finality after ~12.8 minutes (2 epochs). L2 networks have an even more complex model.
Standard approach:
- Write blocks with confirmation lag (wait for N confirmations before writing to the final layer). For Ethereum: 32–64 blocks.
- Keep a staging layer for the last M blocks — data is written immediately but marked as
pending. - Subscribe to
Reorganizationevents from the node (WebSocketnewHeads+ compare parentHash). On reorg — delete affected blocks from staging and reapply the new chain.
For Iceberg this is elegantly solved via time travel and merge operations. For ClickHouse — via ReplacingMergeTree with a version column.
Data Lake Architecture
Ingestion Layer
Two approaches:
- Node-based ingestion — direct connection to a node via WebSocket. Subscribe to new blocks + backfill via
eth_getLogsbatch calls. Requires an archive node. For backfilling millions of blocks, we use parallel processing with asyncio. - Third-party data providers — Goldsky, Envio, Substreams. Faster start but vendor lock-in and more expensive at scale.
Storage: Format and Engine Selection
For raw blockchain data, columnar storage is optimal:
- Apache Parquet on S3/GCS — the standard. zstd compression reduces volume 5–10x. Partitioning by date and block number.
- Apache Iceberg on top of Parquet — ACID, schema evolution, time travel. Critical for reorgs.
- ClickHouse — OLAP for hot queries. Hundreds of millions of rows in seconds. Using ClickHouse for blockchain data is up to 1000x faster than querying JSON-RPC nodes directly.
Typical two-layer architecture:
Raw layer (S3 + Parquet/Iceberg) ↓ ETL (dbt / Spark / Flink) Serving layer (ClickHouse / BigQuery) ↓ Query API Analytics / Trading systems / Dashboards ABI Decoding and Enrichment
Raw event logs contain topics (event signature hashes) and data (ABI-encoded). For Ethereum events decoding, an ABI registry is needed:
from eth_abi import decode from web3 import Web3 TRANSFER_TOPIC = Web3.keccak(text="Transfer(address,address,uint256)").hex() def decode_transfer(log: dict) -> dict | None: if log["topics"][0] != TRANSFER_TOPIC: return None from_addr = "0x" + log["topics"][1][-40:] to_addr = "0x" + log["topics"][2][-40:] amount = decode(["uint256"], bytes.fromhex(log["data"][2:]))[0] return {"from": from_addr, "to": to_addr, "amount": amount} For mass decoding, we create an ABI registry — a table mapping contract_address → ABI. Sources: Etherscan API, Sourcify, 4byte.directory. Unknown contracts are processed as raw bytes, enrichment as ABI becomes available.
Token metadata enrichment: for ERC-20 transfers we need decimals, symbol, price. Prices are taken from Uniswap V3 TWAP records or external APIs (historical data).
Data Schema and Key Tables
CREATE TABLE decoded_events ( block_number UInt64, block_timestamp DateTime, tx_hash FixedString(66), log_index UInt32, contract FixedString(42), event_name LowCardinality(String), chain_id UInt32, params String, -- JSON INDEX idx_contract (contract) TYPE bloom_filter GRANULARITY 4, INDEX idx_event (event_name) TYPE set(100) GRANULARITY 4 ) ENGINE = ReplacingMergeTree(block_number) PARTITION BY toYYYYMM(block_timestamp) ORDER BY (chain_id, contract, block_number, log_index); Separate tables for high-frequency event types: erc20_transfers, uniswap_v3_swaps, aave_liquidations. Partitioning by month.
Example of partitioning and optimization
For the Ethereum network, we partition the event table by month. This allows quick deletion of outdated data and efficient scanning of time ranges. Bloom filter indexes on the contract speed up filtering by address.Example query:
SELECT sum(amount) FROM erc20_transfers WHERE contract = '0xdAC17F958D2ee523a2206206994597C13D831ec7' AND block_timestamp >= '2023-01-01' executes in sub-seconds on 50M rows.
Comparison of Data Acquisition Methods
| Parameter | Node-based | Third-party (Goldsky) |
|---|---|---|
| Startup speed | Medium (node setup) | High (API key) |
| Data control | Full | Limited by vendor |
| Cost at scale | Low (own nodes) | High (pay per volume) |
| Reorg handling | Custom mechanism | Built-in (but opaque) |
What Is Included in the Work
You receive:
- A working data lake with selected networks and events.
- Documentation of the data schema and ETL pipeline.
- Access to ClickHouse (or other serving layer) with query examples.
- Lag monitoring and alerts for issues.
- Team training and 1 month of support.
Typical project cost ranges from $15,000 to $50,000 depending on network count and event complexity. Extensibility: adding new contracts or networks is done through configuration without code changes.
Development Phases
| Phase | Content | Duration |
|---|---|---|
| Design | Scope definition, networks/events, data schema | 1–2 weeks |
| Core ingestion | WebSocket listener, backfill, reorg handler | 3–4 weeks |
| ABI registry | ABI accumulation, decoding, enrichment | 2–3 weeks |
| Storage layer | Parquet/Iceberg, ClickHouse, ETL | 3–4 weeks |
| Serving API | REST/GraphQL, rate limiting | 2–3 weeks |
| Monitoring & ops | Airflow, alerts, documentation | 1–2 weeks |
Why Work with Us
Our expertise — over 5 years in blockchain engineering, 20+ data pipelines implemented for DeFi protocols and crypto funds. We develop turnkey — from schema design to deployment and monitoring. We’ll assess your project in 2 days — contact us for a consultation. Order end-to-end data lake development to accelerate on-chain data analytics.
Trust words: quality guarantee, certified specialists, extensive experience with L1/L2. More about blockchain structure can be read on Wikipedia: Blockchain.