Xatu Parquet Data

# Xatu Parquet Data > Public Ethereum network data in Parquet format - no authentication required ## Looking for ClickHouse Access? **If you need direct database access with lower latency and no redactions**, see https://raw.githubusercontent.com/ethpandaops/xatu-data/refs/heads/master/llms/clickhouse/llms.txt for ClickHouse-specific documentation. ## Overview Xatu provides comprehensive Ethereum network data as publicly accessible Parquet files: - **No authentication required** - freely accessible to everyone - **Updated daily** with 1-3 day delay - **Privacy-conscious** - some columns redacted for user protection - **Organized by network** - mainnet, holesky, sepolia, hoodi ## ⚠️ Critical: Partitioning Requirements **ALWAYS filter on the partitioning column** when querying: - Datetime partitions: Filter on date/time columns (e.g., `slot_start_date_time`) - Integer partitions: Filter on numeric ranges (e.g., `block_number`) - **Failure to partition = extremely slow queries** on billions of rows **NEVER use wildcards (*) for file paths:** - ❌ `*.parquet` will NOT work - ✅ Use explicit paths: `{20,21,22}.parquet` - ✅ Use ranges: `{20000..20010}000.parquet` - The endpoint is NOT S3-compatible ## URL Patterns ### Datetime Partitioned Tables ``` https://data.ethpandaops.io/xatu/NETWORK/databases/DATABASE/TABLE/YYYY/M/D.parquet https://data.ethpandaops.io/xatu/NETWORK/databases/DATABASE/TABLE/YYYY/M/D/H.parquet ``` ### Integer Partitioned Tables ``` https://data.ethpandaops.io/xatu/NETWORK/databases/DATABASE/TABLE/INTERVAL/CHUNK_NUMBER.parquet ``` ### Parameters - **NETWORK**: `mainnet`, `holesky`, `sepolia`, `hoodi` - **DATABASE**: Usually `default` - **TABLE**: Table name (see datasets below) - **YYYY/M/D/H**: Year/Month/Day/Hour for datetime partitioning - **INTERVAL**: Partition size (e.g., `1000` for blocks) - **CHUNK_NUMBER**: Partition number (e.g., `0`, `1000`, `2000`) ## Quick Start Examples ### Single Day Query (Python + Pandas) ```python import pandas as pd url = "https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/2024/4/1.parquet" df = pd.read_parquet(url) print(df.head()) ``` ### Multiple Days (Python + Polars) ```python import polars as pl from datetime import datetime, timedelta # Generate explicit URLs (NEVER use wildcards) base = "https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/" start = datetime(2024, 4, 1) urls = [f"{base}{(start + timedelta(days=i)).strftime('%Y/%-m/%-d')}.parquet" for i in range(3)] # 3 days # Lazy load and query df = pl.concat([pl.scan_parquet(url) for url in urls]) result = df.select(["slot", "block_root", "propagation_slot_start_diff"]).collect() ``` ### ClickHouse + Parquet URL ```sql -- Single file SELECT toDate(slot_start_date_time) AS date, quantile(0.50)(propagation_slot_start_diff) AS median_ms FROM url('https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/2024/4/1.parquet', 'Parquet') GROUP BY date; -- Multiple files with explicit glob (NOT wildcards) SELECT COUNT(*) FROM url('https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/2024/4/{1,2,3}.parquet', 'Parquet'); -- Integer partitioned (blocks) SELECT COUNT(*) FROM url('https://data.ethpandaops.io/xatu/mainnet/databases/default/canonical_execution_block/1000/{19000..19010}000.parquet', 'Parquet'); ``` ### R + Arrow ```r library(arrow) url <- "https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/2024/4/1.parquet" df <- read_parquet(url) head(df) ``` ## Available Datasets ### Beacon API Event Stream > Events derived from the Beacon API event stream. This data is usually useful for 'timing' events, such as when a block was seen by a sentry. Because of this it usually has the same data but from many different instances. These tables are usually fairly slim and contain only a few columns. These tables can be joined with the canonical tables to get a more complete view of the data. For example, you can join the beacon_api_eth_v1_events_block table on block_root or slot with the canonical_beacon_block table to get the block data for each block. **Table Prefix**: `beacon_api_` ### Execution Layer P2P > Events from the execution layer p2p network. This data is usually useful for 'timing' events, such as when a transaction was seen in the mempool by an instance. Because of this it usually has the same data but from many different instances. **Table Prefix**: `mempool_` ### Canonical Beacon > Events derived from the finalized beacon chain. This data is only derived by a single instance, are deduped, and are more complete and reliable than the beacon_api_ tables. These tables can be reliably JOINed on to hydrate other tables with information **Table Prefix**: `canonical_beacon_` ### Canonical Execution > Data extracted from the execution layer. This data is only derived by a single instance, are deduped, and are more complete and reliable than the execution_layer_p2p tables. These tables can be reliably JOINed on to hydrate other tables with information **Table Prefix**: `canonical_execution_` ### Consensus Layer P2P > Events from the consensus layer p2p network. This data is usually useful for 'timing' events, such as when a block was seen by a sentry. Because of this it usually has the same data but from many different instances. **Table Prefix**: `libp2p_` ### MEV Relay > Events derived from MEV relays. Data is scraped from multiple MEV Relays by multiple instances. **Table Prefix**: `mev_relay_` ## Table Catalog **Find specific tables and their partitioning:** ### `beacon_api_eth_v1_beacon_committee` - **Partitioning**: `slot_start_date_time` (datetime, hourly) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API /eth/v1/beacon/states/{state_id}/committees data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_attestation` - **Partitioning**: `slot_start_date_time` (datetime, hourly) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "attestation" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_blob_sidecar` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "blob_sidecar" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_block` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "block" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_block_gossip` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "block_gossip" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_chain_reorg` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "chain reorg" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_contribution_and_proof` - **Partitioning**: `contribution_slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "contribution and proof" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_data_column_sidecar` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, hoodi, sepolia - **Description**: Contains beacon API eventstream "data_column_sidecar" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_finalized_checkpoint` - **Partitioning**: `epoch_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "finalized checkpoint" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_head` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "head" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_events_voluntary_exit` - **Partitioning**: `wallclock_epoch_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API eventstream "voluntary exit" data from each sentry client attached to a beacon node. ### `beacon_api_eth_v1_validator_attestation_data` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API validator attestation data from each sentry client attached to a beacon node. ### `beacon_api_eth_v2_beacon_block` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API /eth/v2/beacon/blocks/{block_id} data from each sentry client attached to a beacon node. ### `mempool_transaction` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Each row represents a transaction that was seen in the mempool by a sentry client. Sentries can report the same transaction multiple times if it has been long enough since the last report. ### `beacon_api_eth_v1_proposer_duty` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a proposer duty from a beacon block. ### `beacon_api_eth_v3_validator_block` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon API /eth/v3/validator/blocks/{slot} data from each sentry client attached to a beacon node. ### `canonical_beacon_block` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains beacon block from a beacon node. ### `canonical_beacon_committee` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical beacon API /eth/v1/beacon/committees data. ### `canonical_beacon_block_attester_slashing` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains attester slashing from a beacon block. ### `canonical_beacon_block_proposer_slashing` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains proposer slashing from a beacon block. ### `canonical_beacon_block_bls_to_execution_change` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains bls to execution change from a beacon block. ### `canonical_beacon_block_execution_transaction` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains execution transaction from a beacon block. ### `canonical_beacon_block_voluntary_exit` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a voluntary exit from a beacon block. ### `canonical_beacon_block_deposit` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a deposit from a beacon block. ### `canonical_beacon_block_withdrawal` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a withdrawal from a beacon block. ### `canonical_beacon_blob_sidecar` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a blob sidecar from a beacon block. ### `canonical_beacon_proposer_duty` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a proposer duty from a beacon block. ### `canonical_beacon_elaborated_attestation` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains elaborated attestations from beacon blocks. ### `canonical_beacon_validators` - **Partitioning**: `epoch_start_date_time` (datetime, hourly) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a validator state for an epoch. ### `canonical_beacon_validators_pubkeys` - **Partitioning**: `index` (integer, 50) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains a validator state for an epoch. ### `libp2p_gossipsub_beacon_attestation` - **Partitioning**: `slot_start_date_time` (datetime, hourly) - **Networks**: mainnet, holesky, sepolia - **Description**: Table for libp2p gossipsub beacon attestation data. ### `libp2p_gossipsub_beacon_block` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Table for libp2p gossipsub beacon block data. ### `libp2p_gossipsub_blob_sidecar` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Table for libp2p gossipsub blob sidecar data ### `libp2p_gossipsub_data_column_sidecar` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, hoodi, sepolia - **Description**: Table for libp2p gossipsub data column sidecar data. ### `libp2p_gossipsub_aggregate_and_proof` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Table for libp2p gossipsub aggregate and proof attestation data ### `libp2p_connected` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p connected events ### `libp2p_disconnected` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p disconnected events ### `libp2p_add_peer` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p add peer events ### `libp2p_remove_peer` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p remove peer events ### `libp2p_recv_rpc` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p recv rpc events ### `libp2p_send_rpc` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p send rpc events ### `libp2p_drop_rpc` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p drop rpc events ### `libp2p_join` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p join events ### `libp2p_leave` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p leave events ### `libp2p_graft` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p graft events ### `libp2p_prune` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p prune events ### `libp2p_duplicate_message` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p duplicate message events ### `libp2p_deliver_message` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p deliver message events ### `libp2p_handle_metadata` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p handle metadata events ### `libp2p_handle_status` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p handle status events ### `libp2p_rpc_meta_control_ihave` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta control ihave events ### `libp2p_rpc_meta_control_iwant` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta control iwant events ### `libp2p_rpc_meta_control_idontwant` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta control idontwant events ### `libp2p_rpc_meta_control_graft` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta control graft events ### `libp2p_rpc_meta_control_prune` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta control prune events ### `libp2p_rpc_meta_subscription` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta subscription events ### `libp2p_rpc_meta_message` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p rpc meta message events ### `mev_relay_bid_trace` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains MEV relay block bids data. ### `mev_relay_proposer_payload_delivered` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains MEV relay proposer payload delivered data. ### `mev_relay_validator_registration` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains MEV relay validator registrations data. ### `canonical_execution_block` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution block data. ### `canonical_execution_transaction` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution transaction data. ### `canonical_execution_traces` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution traces data. ### `canonical_execution_logs` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution logs data. ### `canonical_execution_contracts` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution contract data. ### `canonical_execution_four_byte_counts` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution four byte count data. ### `canonical_execution_address_appearances` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution address appearance data. ### `canonical_execution_balance_diffs` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution balance diff data. ### `canonical_execution_balance_reads` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution balance read data. ### `canonical_execution_erc20_transfers` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution erc20 transfer data. ### `canonical_execution_erc721_transfers` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution erc721 transfer data. ### `canonical_execution_native_transfers` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution native transfer data. ### `canonical_execution_nonce_diffs` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution nonce diff data. ### `canonical_execution_nonce_reads` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution nonce read data. ### `canonical_execution_storage_diffs` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: mainnet, holesky, sepolia - **Description**: Contains canonical execution storage diffs data. ### `canonical_execution_storage_reads` - **Partitioning**: `block_number` (integer, 1000) - **Networks**: holesky, sepolia - **Description**: Contains canonical execution storage reads data. ### `canonical_execution_transaction_structlog` - **Partitioning**: `block_number` (integer, 100) - **Networks**: mainnet - **Description**: Contains canonical execution transaction structlog data from [`debug_traceTransaction`](https://geth.ethereum.org/docs/interacting-with-geth/rpc/ns-debug#debugtracetransaction). ### `canonical_execution_transaction_structlog_agg` - **Partitioning**: `block_number` (integer, 100) - **Networks**: mainnet - **Description**: Contains aggregated canonical execution transaction structlog data from [`debug_traceTransaction`](https://geth.ethereum.org/docs/interacting-with-geth/rpc/ns-debug#debugtracetransaction). ### `consensus_engine_api_get_blobs` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet - **Description**: Contains consensus engine API getBlobsV1 request data from each sentry client. ### `consensus_engine_api_new_payload` - **Partitioning**: `slot_start_date_time` (datetime, daily) - **Networks**: mainnet - **Description**: Contains consensus engine API newPayload request data from each sentry client. ### `libp2p_rpc_data_column_custody_probe` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p RPC data column custody probe events. ### `libp2p_identify` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Table for libp2p identify events containing peer identification data including agent version, protocols, and connection metadata. ### `libp2p_synthetic_heartbeat` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, holesky, hoodi, sepolia - **Description**: Table for libp2p synthetic heartbeat events. ### `node_record_consensus` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Contains consensus layer node records from discv5 discovery. ### `node_record_execution` - **Partitioning**: `event_date_time` (datetime, daily) - **Networks**: mainnet, hoodi, sepolia - **Description**: Contains execution layer node records from discv4/discv5 discovery. ### `execution_state_size` - **Partitioning**: `block_number` (integer, 5000000) - **Networks**: mainnet, hoodi, sepolia - **Description**: Contains execution layer state size metrics per block. ### `execution_transaction` - **Partitioning**: `block_number` (integer, 5000000) - **Networks**: mainnet, hoodi, sepolia - **Description**: Contains execution layer transaction data. ## Schema Discovery ### View Table Schema ```bash # Get CREATE TABLE statement for any table curl -s https://raw.githubusercontent.com/ethpandaops/xatu-data/refs/heads/master/schema/clickhouse/default/TABLE_NAME.sql # Example: beacon block events curl -s https://raw.githubusercontent.com/ethpandaops/xatu-data/refs/heads/master/schema/clickhouse/default/beacon_api_eth_v1_events_block.sql ``` ### List All Available Tables ```bash # List all schemas curl -s https://api.github.com/repos/ethpandaops/xatu-data/contents/schema/clickhouse/default | jq -r '.[].name | select(endswith(".sql"))' ``` ### Common Fields Most tables include: - `meta_client_name` - Client that collected the data - `meta_client_id` - Unique session ID - `meta_network_name` - Network (mainnet, holesky, etc.) - `event_date_time` - When event was recorded - Partition column (check table details above) ## Common Query Patterns ### Network Timing Analysis ```python # Analyze block propagation across the network import polars as pl url = "https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/2024/4/1.parquet" df = pl.read_parquet(url) # Calculate propagation statistics by slot stats = df.group_by("slot").agg([ pl.col("propagation_slot_start_diff").min().alias("min_ms"), pl.col("propagation_slot_start_diff").quantile(0.5).alias("p50_ms"), pl.col("propagation_slot_start_diff").quantile(0.95).alias("p95_ms"), pl.col("meta_client_name").n_unique().alias("observer_count") ]) ``` ### Joining Data Sources ```sql -- Join canonical blocks with event observations WITH blocks AS ( SELECT * FROM url('https://data.ethpandaops.io/xatu/mainnet/databases/default/canonical_beacon_block/2024/4/1.parquet', 'Parquet') ), events AS ( SELECT * FROM url('https://data.ethpandaops.io/xatu/mainnet/databases/default/beacon_api_eth_v1_events_block/2024/4/1.parquet', 'Parquet') ) SELECT b.slot, b.proposer_index, COUNT(DISTINCT e.meta_client_name) as observers, MIN(e.propagation_slot_start_diff) as fastest_observation_ms FROM blocks b LEFT JOIN events e ON b.slot = e.slot GROUP BY b.slot, b.proposer_index ORDER BY b.slot; ``` ## Data Freshness & Updates - **Update frequency**: Daily - **Delay**: 1-3 days behind current time - **Privacy**: Some sensitive fields redacted - **Historical data**: Stable, not rewritten - **Retention**: Check network date ranges in table catalog above ## Additional Resources - **Full Documentation**: https://github.com/ethpandaops/xatu-data - **ClickHouse Access**: https://raw.githubusercontent.com/ethpandaops/xatu-data/refs/heads/master/llms/clickhouse/llms.txt for database credentials and advanced queries - **Schema Repository**: https://github.com/ethpandaops/xatu-data/tree/master/schema/clickhouse - **Config File**: https://raw.githubusercontent.com/ethpandaops/xatu-data/master/config.yaml ## Hints (VERY IMPORTANT) - It is HIGHLY recommended to explore the available tables and understand the data before querying. - The CBT tables (in the $network database) MUST be your first choice for data. Inspect all the available tables and understand the data before querying. - A large amount of CBT tables have _head and _canonical variants. Make sure you know which one you are querying as the data can be different. - To check when a block was first seen by nodes in the Xatu network, check the $network.fct_block_first_seen_by_node table. - A "best effort" table in $network.dim_node contains validator information like the entity running the validator. In devnets this also contains the node name that is running the validator. - When asked for Grafana compatible queries you should make use of $__timeFilter() over the tables partition column. If asked for Grafana timeseries queries, you need to use $__timeInterval() over the tables partition column, usually with something like toStartOfFiveMinutes() or toStartOfHour(). ## License Data licensed under CC BY 4.0