Subgraphs provide a highly curated source of data that can be used for historical data analytics compared to archive nodes. While historically used primarily to power frontend dashboards with recent data, subgraphs are similar to archive nodes because they contain all historical data for a protocols set of contracts.

We query historical Uniswap swap data from two subgraphs on The Graphs decentralized network with Subgrounds:

• streamingfast univ3

• univ2

Use these links to find a list of available query fields and schemas.

This example repository provides example scripts to get started. The repository wraps additional functionality to:

• control the query date range

• pre-defined query fields for a complete swaps dataset

• asynchronous query per day

• idempotent local data storage

Currently playgrounds gives 5000 free query credits to start with when signing up for their API key. Each query credit is equivalent to 1000 rows queried. You can sign up for an API key here.

Data Exploration with Polars

The pre-defined query fields contain the following columns with respective types. These schemas are defined using polars types, which leverages the apache arrow columnar data format.

univ3_schema = {
    '': pl.Int64,
    'protocol': pl.Utf8,
    'swaps_timestamp': pl.Int64,
    'swaps_transaction_id': pl.Utf8,
    'swaps_transaction_blockNumber': pl.Int64,
    'swaps_transaction_timestamp': pl.Int64,
    'swaps_transaction_gasPrice': pl.Float64,
    'swaps_transaction_gasUsed': pl.Int64,
    'swaps_logIndex': pl.Int64,
    'swaps_sqrtPriceX96': pl.Float64,
    'swaps_pool_id': pl.Utf8,
    'swaps_pool_feeTier': pl.Int64,
    'swaps_pool_liquidity': pl.Float64,
    'swaps_pool_token0Price': pl.Float64,
    'swaps_pool_token1Price': pl.Float64,
    'swaps_recipient': pl.Utf8,
    'swaps_sender': pl.Utf8,
    'swaps_origin': pl.Utf8,
    'swaps_amount0': pl.Float64,
    'swaps_amount1': pl.Float64,
    'swaps_token0_name': pl.Utf8,
    'swaps_token0_decimals': pl.Int64,
    'swaps_token0_symbol': pl.Utf8,
    'swaps_token1_name': pl.Utf8,
    'swaps_token1_decimals': pl.Int64,
    'swaps_token1_symbol': pl.Utf8,
}

univ2_schema = {
    '': pl.Int64,
    'protocol': pl.Utf8,
    'swaps_transaction_id': pl.Utf8,
    'swaps_transaction_blockNumber': pl.Int64,
    'swaps_transaction_timestamp': pl.Int64,
    'swaps_logIndex': pl.Int64,
    'swaps_amount0In': pl.Float64,
    'swaps_amount0Out': pl.Float64,
    'swaps_amount1In': pl.Float64,
    'swaps_amount1Out': pl.Float64,
    'swaps_amountUSD': pl.Float64,
    'swaps_from': pl.Utf8,
    'swaps_id': pl.Utf8,
    'swaps_sender': pl.Utf8,
    'swaps_to': pl.Utf8,
    'swaps_pair_id': pl.Utf8,
    'swaps_pair_reserve0': pl.Float64,
    'swaps_pair_reserve1': pl.Float64,
    'swaps_pair_token0_decimals': pl.Int64,
    'swaps_pair_token0_id': pl.Utf8,
    'swaps_pair_token0_name': pl.Utf8,
    'swaps_pair_token0_symbol': pl.Utf8,
    'swaps_pair_token1_decimals': pl.Int64,
    'swaps_pair_token1_id': pl.Utf8,
    'swaps_pair_token1_name': pl.Utf8,
    'swaps_pair_token1_symbol': pl.Utf8,
}

The notebook in the repository demonstrates some basic joins and charts to get started analyzing data between the two protocols. Note the query size is set at 2,500 so there are only 17,500 rows for each protocol in the sample data.

The main benefit from using subgraphs for historical analysis is that the data is already nicely curated. To concat the dataframes together, only the column names need to be standardized. Below shows an example of the nicely curated dataset.

From this dataset, a simple scatterplot is made to show the number of swaps in a pool and the total volume from those number of swaps. Each dot represents a unique pool.

Limitations and Conclusion

The main benefit from querying a subgraph for historical data is access to a curated dataset, full of information. In contrast with an archive node, schemas and metadata (such as USD volume and token data) must be written from scratch.

While subgraphs provide an easy means to access curated datasets with minimal effort, they come with limitations. The decentralized graph network is currently undergoing growth challenges, and the technology stack is not fully matured. Query performance depends on the indexers, and optimizing their stacks and establishing sustainable businesses around the decentralized network is an ongoing process.

Historical blockchain data poses challenges for analysis. Despite its general accessibility, obtaining and analyzing such data has been historically hindered by paywalls and restrictions imposed by node service providers. Setting up a personal archive node is also a non-trivial task, introducing extra steps before data analysis becomes feasible.

By leveraging Cryo to extract historical data and utilizing the free Merkle RPC with archive node support, researchers can now easily access Ethereum historical data at no cost. This breakthrough provides quantitative researchers with production-grade access, allowing more time for data exploration and less time spent on building data pipelines.

Cryo

Cryo, a recent addition to data extraction tools, was announced in July 2023. It employs ethers.rs for JSON-RPC requests, making it compatible with various chains, including Ethereum, Optimism, Arbitrum, Polygon, BNB, and Avalanche. Since Cryo is built in rust, querying data is embarassingly parallel. This actually makes Cryo so fast that by default, it will be too fast to use with most node providers.

When extracting data from a historical node, a common challenge involves preprocessing raw blockchain data to make it human-usable. Cryo takes care of this and standardizes the dataset across a wide variety of datasets. y default, data is saved into Apache's free, universal, and open-source column-oriented storage format—parquet files. These files use the lz4 compression method by default (modifiable with the --compression syntax).

Some example datasets that are available in Cryo already:

• balance_diffs

• balances

• blocks

• erc20_balances

• erc20_supplies

• erc20_transfers

• erc721_transfers

• eth_calls

• geth_calls

• geth_code_diffs

• geth_balance_diffs

• geth_opcodes

• logs (alias = events)

• native_transfers

• slots (alias = storages)

• storage_diffs (alias = slot_diffs)

• storage_reads (alias = slot_reads)

• traces

• trace_calls

• transactions (alias = txs)

Cryo is user-friendly, accessible through the CLI or Python bindings, significantly simplifying the process of extracting and curating historical data for research. The Cryo GitHub readme provides a set of starter commands. Additionally, Cryo is idempotent, enabling researchers to resume interrupted pipelines without duplicating queried data.

Merkle RPC

Cryo's efficiency is limited by rate limits and throttling from various node endpoints or personal node hardware. Merkle addresses this bottleneck by offering a free RPC with no throttling and unlimited requests. The endpoint can be found here. How is this possible?

Merkle is a private mempool provider and operates a group of RETH nodes, allowing them to save ~$250,000 annually on expenses (source) and improve performance compared to other mempool services like Kolibrio and Bloxroute. Of equal importance, their cloud provider, OVH, grants them unlimited outgoing/incoming bandwidth so don’t feel guilty using the node!

While Cryo currently supports BSC and Polygon, Reth does not. However, Merkle plans to offer similar public endpoints for BSC and Polygon once they support them.

Short Example

Here is an example of how I am using Cryo CLI to build a dataset with blocks, transactions, and intra-block balance changes. Two lines of code downloads ~50gb of historical data for the month of September. The limiting factor was my internet speed! The pipeline is largely self managed because of the idempotent nature of Cryo so the only thing I needed to do was create a data folder, Cryo takes care of the subfolder management with --subdirs datatype

`cryo blocks_and_transactions -b 18039828:18251969 -o /home/evan/Documents/blockspace/data/cryo_september/ --rpc "https://eth.merkle.io" --subdirs datatype --hex`

`cryo balance_diffs -b 18039828:18251969 -o /home/evan/Documents/blockspace/data/cryo_september/ --rpc "https://eth.merkle.io" --subdirs datatype --hex`

Paradigm recently released Artemis: An Open-Sourced MEV Bot Framework with a cross-market NFT arbitrage strategy implementation included. This is a brief technical commentary focusing on how the initial strategy is setup and how all of the pieces of the library come together.

Artemis-core

Artemis-core provides a shared codebase in which strategies can be executed on. Artemis-core contains a lot of boilerplate overhead. The boilerplate is split into three folders:

• collectors - provides boilerplate for streaming general events for every new block from the blockchain and transactions in the mempool.

• executors - contains execution logic to send transactions (strategy actions) to the mempool or flashbots relay.

• utilities - contains logic to update and maintain the internal state for a given strategy

• engine.rs - not a folder, but contains the core logic (aka the engine) of Artemis. engine.rs is used to orchestrate the dataflow between the data stream, the internal state, and sending transactions back on-chain for execution.

Smart Contracts

strategy.rs utilizes two smart contracts - SudoOpenseaArb.sol and SudoPairQuoter.sol.

• SudoOpenseaArb.sol contains the executeArb and withdraw.

• SudoPairQuoter.sol contains getSellQuote and getMultipleSellQuotes. These are both view functions that return the on-chain Sudo pool quote price.

strategy.rs logic

In the opensea-sudo-arb folder, strategy.rs holds the bulk of the code for executing this arbitrage strategy. The logic for the strategy can be broken down into these core async functions:

• sync_state gets the on-chain state of all Sudo pools. The list of Sudo pool addresses are retrieved by searching within a block range for all pools deployed. This set of addresses is used to initialize an empty internal state.

• process_event updates the internal state with Opensea orders from the API and sudo pool offers. Sudo pool offers are retrieved by using the view functions in SudoPairQuoter.sol

• process_order_event filters out the state space for orders that are not on ethereum, not denominated in eth, and are not profitable. This leaves the only remaining pool addresses as the profitable arb transactions to submit for execution.

• process_new_block_event updates the internal state space with new Sudo pools created in the last block and retrieves updated quotes for Sudo pools that have changed price since the last block.

Conclusion

Paradigm has done the heavy lifting for taking a bot project from 0 to 1. New strategies that are added benefit from Artemis-core, which contains a lot of boilerplate code for retrieving blockchain data, building a strategy around the inputs, and executing transactions.

The main package in DataStreams is the Streamer class. Streamer streamlines the Subgraph query process in Python, exposing key information such as the queryable Subgraph schemas and filter clauses. In v1.0.0, functionality includes:

• DRY Subgraph Query Code - reuse code to reduce complexity of Subgraph queries

• Parallelized Subgraph Queries - Leverage the standard Python package concurrent to unlock concurrency at the Subgraph query level

Example #1 - Access Historical Chainlink Node Data

Query the Chainlink Subgraph node for multiple token prices over a customized ranged period. Example notebook found here

Example #2 - Parallelized Multi Schema Query

Query the Cowswap Subgraph node for four specific schemas with parallelization support. Example notebook found here