Trade-derived candles, cohorts or wallet activity within the available history.
Your strategy + fill model↓
Your research resultWith assumptions and missing data recorded.
Choose the history your question needs.
A launch-cohort study, a wallet investigation and a price model need different tables. Check the overlap before you choose the research period.
Query decoded DEX activity in ClickHouse. Turn swaps into a research dataset, then test your own strategy with explicit assumptions about costs and execution.
Documented coverage Pump.fun, PumpSwap, Raydium AMM / CPMM / Launchpad, LetsBonk.fun through the Launchpad tables, and Meteora DLMM / Dynamic Bonding Curve. Helpers include transfers, Jito tips and transaction timestamps.
Published retention varies by table. Confirm the current bounds of your export.
Research input
Published window
What it changes
Pump.fun creation and core swaps
Full table history
Creation reaches back to 17 Jan 2024 in the published snapshot. Legacy swaps start later; v2 swaps start on 2 Nov 2024.
PumpSwap swaps
1 year
Trade amounts, pool reserves and fee components. Newly added columns have their own population dates.
Raydium AMM / CPMM, Meteora DLMM
90 days
A rolling window. A date in an older documentation snapshot is not a promise that the row remains available today.
Token transfers and SOL top-ups
31 days
A longer wallet study needs an export or separately arranged retention.
Transaction timestamps and Jito tips
14 days
Export in time if the study needs sub-slot arrival observations or tip spend.
One start date does not describe the whole database.
Each table has its own start date and retention window. The published dates and counts are a 16 Aug 2026 snapshot; verify the current range, gaps and field availability before exporting.
Start with one SOL-quoted Pump.fun v2 mint. Use a fixed UTC research interval, verified mint decimals and successful swap rows. Your SQL derives the candles.
INPUT
One base mint SOL quote UTC interval [start, end)
TRANSFORM
Normalize raw amounts Order events within the block Aggregate by minute
OUTPUT
OHLC + base volume Quote volume in SOL Swap-row count
ohlcv.sql / example
-- Unexecuted ClickHouse example. Run preflight.sql first; reject ambiguous keys.
-- Client binds mint, base_decimals, start and end; all times UTC.
-- SOL quote only; decimals must come from separately verified mint metadata.
WITH normalized AS
(
SELECT
block_time, slot, tx_idx, ix_idx,
toFloat64(base_coin_amount) / pow(10, {base_decimals:UInt8}) AS base_units,
toFloat64(quote_coin_amount) / 1e9 AS quote_sol
FROM pumpfun_v2_swaps
PREWHERE block_date_utc >= toDate({start:DateTime('UTC')})
AND block_date_utc <= toDate({end:DateTime('UTC')})
AND failed = 0
WHERE block_time >= {start:DateTime('UTC')} AND block_time < {end:DateTime('UTC')}
AND base_coin = {mint:String}
AND quote_coin = 'So11111111111111111111111111111111111111112'
AND base_coin_amount > 0 AND quote_coin_amount > 0
)
SELECT
toStartOfMinute(block_time) AS minute_utc,
argMin(quote_sol / base_units, (slot, tx_idx, ix_idx)) AS open_sol,
max(quote_sol / base_units) AS high_sol,
min(quote_sol / base_units) AS low_sol,
argMax(quote_sol / base_units, (slot, tx_idx, ix_idx)) AS close_sol,
sum(base_units) AS base_volume,
sum(quote_sol) AS quote_volume_sol,
count() AS swap_rows
FROM normalized
GROUP BY minute_utc
ORDER BY minute_utc
Bind mint, base_decimals, start and end. Decimals are a verified external input, not a column in this table. The result is SOL per base token, not USD.
Sample outputSynthetic rows
12:00 UTC
Open
0.0010
High
0.0012
Low
0.0009
Close
0.0009
SOL per base token
Base volume 300Quote volume 0.31 SOLSuccessful swap rows 3
12:01 UTCNo row
Preserve empty minutes as gaps in the dataset.
Sample amounts illustrate the calculation and event ordering. Run the SQL against your chosen dataset and validate the result before using it in a backtest.
Before open and close
Reject ambiguous (slot, tx_idx, ix_idx) keys in the chosen sample. Equal ordering values can make argMin and argMax nondeterministic.
A nonempty result means the example cannot reliably order open and close. Investigate the event grain and check coverage before aggregating.
preflight.sql / example
-- Unexecuted example. A nonempty result blocks this example's OHLCV ordering.
-- Distinct signatures do not prove unique instruction-level events.
SELECT slot, tx_idx, ix_idx, count() AS rows_for_order_key
FROM pumpfun_v2_swaps
PREWHERE block_date_utc >= toDate({start:DateTime('UTC')})
AND block_date_utc <= toDate({end:DateTime('UTC')})
AND failed = 0
WHERE block_time >= {start:DateTime('UTC')} AND block_time < {end:DateTime('UTC')}
AND base_coin = {mint:String}
AND quote_coin = 'So11111111111111111111111111111111111111112'
AND base_coin_amount > 0 AND quote_coin_amount > 0
GROUP BY slot, tx_idx, ix_idx
HAVING count() > 1
LIMIT 20
What this price means
The normalized quote amount divided by the normalized base amount. It is a trade-derived price. Reconcile fee and wallet-flow semantics before treating it as a net execution price.
What this scope excludes
Other quote assets, venues and failed attempts. USD candles need a separate historical conversion series. Routed legs need a defined counting rule before you consolidate markets.
The Indexer handles decoding and storage. A backtest also needs a historical universe, time-aware features, a strategy and a model of what could have filled.
For trading teams and analysts building their own research pipeline. Supanode supplies indexed data; your code runs the backtest.
SUPANODE
Decoded history
Confirmed DEX activity in typed ClickHouse tables, with table-specific retention.
YOUR APPLICATION
Validate the export
Record tables, interval, metadata sources and code version. Check gaps, order keys, duplicate costs and population dates.
Build features
Use data available at the decision time. Freeze the universe and keep outcomes separate from signals.
Run the strategy
Choose order size, delay, routes, costs and failure behavior. Save the assumptions with the result.
Challenge the result
Keep an untouched test period, record how many variants you tried, and stress costs and delay. Then compare with forward observations.
A candle closes before it can inform a decision.
Candle closesComplete price interval
Data is availableConfirmation + ingestion
Your code decidesFeatures + strategy
Execution attemptDelay + costs + failure
The Docs describe about 15 seconds from block confirmation to a queryable row. Model availability delay as a scenario because historical block_time does not record when each row became queryable.
Avoid choosing winners later.
For a launch study, define the cohort at launch and retain tokens that never migrated or stopped trading. Future peak market cap and future migration status can be outcome labels; they cannot be earlier signals.
Keep future features out.
Use closed intervals and past-only features. Full-series averages, future shifts and repeatedly tuning on the test set can inflate results. One train/test split does not remove every selection bias.
A later order has its own size, market state and delivery path. Make the expense and fill assumptions visible before reading a simulated result.
Component
Available evidence
Your model must decide
Network fee
fee records the transaction fee in lamports.
Count it once per transaction, even if several instruction rows repeat it. Do not add its priority fee twice.
DEX fees
PumpSwap exposes fee components and reserves. The Pump.fun v2 example has a different schema.
Match the instruction version and amount semantics. Fields populated later are missing measurements on older rows, not proof of zero fees.
Tips
Jito tip transfers have a 14-day window.
Join with checked cardinality. Missing data beyond retention is not zero spend.
Slippage and impact
Observed trade amounts; reserve fields on some tables.
Choose size, delay and route. Use matching historical protocol rules for a pool model; a candle close is not a firm quote.
Failed attempts
Pump.fun v2 retains failed transactions. The other documented swap tables do not.
Exclude failed rows from prices and volume, but account for recorded failed-transaction costs. Dropped or never-landed attempts need separate logs.
Model execution separately
For fill simulation, transaction landing or a full account, oracle and lending-position replay, build and validate a model using the relevant historical state changes and protocol versions.
Check each column's “populated since” date. Before it, default zero or empty values may stand in for unavailable data.
A timestamp is an observation.
tx_timestamps records shred arrival at the Amsterdam receiver. Use it for that observation point; use separate evidence for transaction creation and finalization. A missing join means "not observed."
Retention sets the join window.
A year of swaps joined to 14 days of tips still yields only 14 days of supported tip analysis. Arrange exports before the short window expires.
Empty bars need a policy.
Preserve gaps in the research dataset. If your model carries prices forward, label that assumption and prevent it from fabricating traded liquidity.
The minimum offline setup is the Solana Indexer plus your own SQL or notebook and backtest code. Live streaming and transaction delivery are separate choices when you move to forward validation.
Solana
Historical Data Indexer
Separate subscription
Decoded Solana DEX activity through ClickHouse SQL.
Full documented schema access
Direct ClickHouse SQL queries
No per-request fees; agreed usage limits apply
Custom REST and Python ETL quoted separately
$300
/ month
Free trial up to 24h on request, no card; our team confirms availability and setup. Crypto-only, prepaid subscriptions.
Direct ClickHouse SQL. Query volume is subject to the published reasonable-use / tier limits; confirm capacity for your workload. Custom REST endpoints and Python ETL are quoted separately.
Budget separately: your compute and storage, any external historical price series, and scoped REST / ETL work. Simulated network fees and tips belong to the trading model; reading the historical database does not send a Solana transaction.
When is a different setup needed?
For unindexed programs or historical account state, arrange a suitable data source and protocol-specific decoder or replay. Run repeated transforms on your own scheduler or request scoped ETL. For forward validation, collect the live events and client timestamps your strategy needs.
Before you start.
Is OHLCV a ready-made API?
Calculate candles with ClickHouse SQL over the documented swap tables. For an API tailored to your dataset, ask the team about a custom REST endpoint. Your application runs the backtest.
Does every DEX have history from January 2024?
No. That is the deepest published Pump.fun creation history. Swap tables start at different dates and retain different windows. Inspect the tables your question needs and confirm the current bounds before exporting.
Do I need gRPC, ShredStream or Sender for this?
Start this offline workflow with Indexer access and your research code. Add a live feed and transaction delivery when you move to forward validation or live execution.
Can I study failed trades?
The documented Pump.fun v2 swap table retains failed transactions. Keep them out of trade prices and volume, but study their recorded costs separately. This does not recover every request rejected before landing or every transaction that was never submitted.
Can I reproduce the exact fill I would have received?
Observed swaps and candles alone cannot establish that. Your proposed order has its own size, route, delay and constraints. Exact replay needs the relevant historical state, protocol version and a validated execution model.
How do I retain a longer research window?
Discuss extended retention or one-off exports with the team before the relevant tables expire. Do not assume that older timing, transfer or tip data can be recovered after the rolling window has passed.
Inspect the evidence.
Use the coverage, schema and research references below to define and validate your dataset.