Swaps and USD prices

Swaps and USD prices for chainstar. Raw blockchain data to clean dimensional tables in your own warehouse, from one command.

chainstar reads DEX swaps on Ethereum, Base and Solana into fct_swaps, derives USD prices from them into fct_token_prices, and values transfers (amount_usd), fees (fee_usd) and swaps (usd_value) at those prices. Every price comes from on-chain swaps: no price service is asked, nothing needs an account, and nothing is assumed except that the USD stablecoins listed below are worth 1 USD. Where a token has no reliable price, its USD columns are NULL rather than a guess.

Venues

Ethereum and Base. cryo's logs dataset, filtered to three Swap events by topic0, one eth_getLogs request per block as for transfers. chainstar decodes them itself, exactly, because their amounts are 256-bit integers (signed in Uniswap v3):

Any contract can emit these events, so for every pool chainstar reads token0(), token1() and factory() (plus fee() and tickSpacing(), or stable()), then asks that factory for the pool of the same tokens (getPair, getPool(tokens, fee), getPool(tokens, tickSpacing) or getPool(tokens, stable)). A pool is verified when the factory answers with the pool's own address and the factory is one of these:

Each factory was also checked on chain (it has code; the Uniswap and SushiSwap v3 ones enable fee tiers 100, 500, 3000 and 10000), and every topic and function selector is recomputed from its signature by the tests. Swaps in other pools with the same events (forks such as BaseSwap, whose factory address could not be found in its own documentation) are kept with venue unknown and pool_verified false; they are never used for prices and get no usd_value. Pool metadata is read once per pool at the chain head and cached in data/<chain>/dex_pools/, like token metadata: reading factories' PairCreated/PoolCreated logs instead would need every block since each factory was deployed. A swap whose pool does not answer token0() and token1() is left out of fct_swaps (it stays in the raw table).

Not covered: Uniswap v4 (one contract for all pools, whose tokens are known only from its Initialize events), PancakeSwap v3 (its Swap event has two more fields, so a different topic0), Curve, Balancer, and aggregators' own events (their swaps show up as the pool swaps they route through).

Solana. A swap through an aggregator crosses several pools and programs; what matters is what the trader gave and got, so chainstar derives one swap per transaction from balance changes. A successful transaction counts when it calls one of these programs (each id from the program's own source or IDL, and checked on chain to be an executable program):

For the first signer whose balances changed like a swap, token changes are summed over the token accounts it owns (preTokenBalances/postTokenBalances), and SOL and wrapped SOL count as one asset, native: its lamport change, plus the fee it paid, plus the lamport changes of its token accounts, which nets out wrapping, unwrapping and the rent of accounts it opens and closes. Exactly one token down and one up is a swap (SOL that moved only for fees, tips or rent is ignored); one token against SOL moving the other way is a swap with SOL; anything else (several tokens each way, an arbitrage that ends where it started) is left out. Limitations: a SOL amount includes tips and rent paid to accounts the signer does not own in the same transaction; programs not listed are not read; a transaction gives at most one swap; Solana swaps have no pool, sender or recipient, and dim_pools has no Solana rows.

How prices are derived

For each chain, asset and UTC hour (and, separately, UTC day), in fct_token_prices:

1. USD stablecoins are 1 USD (usd_stablecoin_peg), identified by address or mint, never by symbol. observed_price shows what their swaps against other stablecoins said, and a dbt test warns when it strays more than 2% from 1.
2. Swaps against a stablecoin give one observation each: the stablecoin amount (its USD value) divided by the other asset's amount. ETH and SOL are priced this way from WETH's and wrapped SOL's swaps, as the asset native.
3. Swaps against the native asset price other tokens through the native asset's price in the same bucket, when that price is high or medium confidence. Nothing is priced through a third token: a token traded only against other tokens has no price.
4. Outliers: observations worth less than 10 USD are dropped (mostly rounding); those more than 10% from the bucket's median are rejected. The price is the median of the rest. Medians here are lower medians (for an even count, the lower middle value), always one of the observed prices, so a price is the same to the last digit on every warehouse and in incremental and full builds.
5. Confidence: high with at least 5 observations kept, 10,000 USD of volume and 2 sources (pools, or Solana traders); medium with at least 3 and 1,000 USD; low otherwise, when usd_price is NULL and observed_price still shows the median. Every chain has a row for its native asset and its stablecoins in each bucket with blocks (none when there were no observations); other tokens only where they were traded.
6. Bitcoin has no on-chain DEX. Its price is that of wrapped BTC (WBTC and cbBTC) on Ethereum and Base in the same bucket (cross_chain_wrapped_btc), at most medium, and only when those chains' swaps for that hour are in the same warehouse. Wrapped BTC can trade slightly away from BTC; otherwise BTC values are NULL.

Checked on real data, chainstar quickstart of 20 blocks of Base and Ethereum and 20 Solana slots into one warehouse on 21 October 2026 (22:36 to 22:51 UTC): 360, 470 and 1,285 swaps (separately, each of the 84 swaps in the test fixtures' Ethereum and Base blocks was checked against its pool's Transfer events, to the unit). ETH came out at 2,489.33 USD on Ethereum (97 observations, 6 pools, high) and 2,489.87 on Base (21, 7 pools, high), 0.02% apart; each WETH/stablecoin Uniswap v3 and Slipstream pool's own price after its last swap (from sqrtPriceX96) was within 0.14% of the hour's price, and the medians of pools with two or more swaps spread 0.15% (Ethereum, 4 pools) and 0.03% (Base, 3). SOL came out at 107.37 USD (medium: 3 swaps of at least 10 USD, through Jupiter, Orca and Meteora, 0.9% apart; a fourth, at 50,319, was rejected). USD values were found for 52% of Ethereum's transfers, 48% of Base's and 78% of Solana's, and for every fee. Incremental builds over the same data, in two halves, equalled a full rebuild.

Only verified pools count on Ethereum and Base. The thresholds are dbt variables in dbt_project.yml (price_min_swap_usd, price_outlier_band, price_high_*, price_medium_*).

The anchors:

(full addresses in the price_assets seed). They come from Circle's USDC addresses page, Tether's supported-protocols page and the Uniswap Labs default token list; their decimals were read on chain.

What gets a USD value

• fct_transfers.amount_usd, fct_fees.fee_usd and fct_swaps.usd_value use the hourly price of the block's UTC hour, when its confidence is peg, high or medium, and are NULL otherwise. Beside each: the price (usd_price, native_usd_price), price_method, price_confidence and price_bucket_start, the hour the price is for, so every value can be checked against fct_token_prices.
• A swap is valued by its stablecoin side if it has one, else its native side, else token_in.
• The transfers export and chainstar wallet spreadsheets carry the same columns.

Wallet histories. chainstar wallet does not read every swap of the range. For each token the wallet moved, it asks the Uniswap v2 and v3 factories (and Aerodrome's on Base) for that token's pools against WETH and USDC, once per token (cached in data/<chain>/.pricing_pools.json), and fetches only those pools' swaps, with eth_getLogs filtered by address, which the public endpoints answer for a whole chunk per request, and the blocks they are in. The lookup asks Uniswap v2, Uniswap v3 (fee tiers 100, 500, 3000 and 10000) and, on Base, Aerodrome v2 (stable and volatile); it does not find SushiSwap or Aerodrome Slipstream pools. Tokens without such a pool stay unpriced, and a wallet's prices rest on fewer swaps than a full run's, so their confidence is often lower. A Bitcoin wallet's warehouse has no Ethereum or Base swaps, so its BTC values are NULL; run Bitcoin and Ethereum into one warehouse to price them.

Prices under sync

fct_token_prices is incremental like the other tables. A bucket's price is computed from all its swaps, so new blocks recompute every bucket from the start of the UTC day of the earliest new block, for every chain at once (Bitcoin's price depends on Ethereum's and Base's swaps). Transfers, fees and swaps are revalued for the hour of their chain's first new block (Bitcoin's: the earliest of Bitcoin's, Ethereum's and Base's), whose price may have changed. The tests check that two syncs give exactly the rows of a full rebuild, and a rollback deletes a chain's swaps and prices with its other facts.

After upgrading from a release without swaps, rebuild once with chainstar sync --full-refresh (or any chainstar run): the tables have new columns. Blocks extracted before the upgrade have no swaps until they are extracted again: a chainstar run over such a range fetches its swaps and reuses the other chunk files.