Uniswap V3 Swap Table

Description

Every Uniswap V3 swap with before/after sqrt price, amounts, USD values, and routing details.

Schema

Field	Type	Description
id	BYTES	Deterministic ID (MD5 of pool_id, txn_hash, log_index)
uniswap_v3_pool_id	STRING	FK → uniswap_v3_pool.id.
txn_hash	STRING	Transaction hash.
datetime	TIMESTAMP	Swap time (UTC).
block_number	INTEGER	Block containing the swap.
log_index	INTEGER	Log index within transaction.
token_in_contract_id	STRING	FK → token_contract.id (input token).
token_out_contract_id	STRING	FK → token_contract.id (output token).
sender_address	STRING	Initiator of the swap.
origin_address	STRING	Original sender if routed via aggregator (nullable).
recipient_address	STRING	Swap recipient.
sqrt_price_x96_before	BIGNUMERIC	sqrt price before (× 2^96).
sqrt_price_x96_after	BIGNUMERIC	sqrt price after (× 2^96).
raw_amount_in	BIGNUMERIC	Amount in (smallest unit).
raw_amount_out	BIGNUMERIC	Amount out (smallest unit).
usd_value_in	FLOAT	USD value of inputs at swap time.
usd_value_out	FLOAT	USD value of outputs at swap time.
ingested_at	TIMESTAMP	Insert time (UTC).

Example Queries

Price impact per swap (last 7d), top pools by volume

SELECT
  p.name,
  SUM(s.usd_value_in) AS volume_usd,
  AVG(ABS(POW(SAFE_DIVIDE(CAST(s.sqrt_price_x96_after AS FLOAT64),
                           NULLIF(CAST(s.sqrt_price_x96_before AS FLOAT64), 0)), 2) - 1)) AS avg_price_impact
FROM `defi_prepared.uniswap_v3_swap` s
JOIN `defi_prepared.uniswap_v3_pool` p ON s.uniswap_v3_pool_id = p.id
WHERE s.datetime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND s.sqrt_price_x96_before IS NOT NULL
  AND s.sqrt_price_x96_after IS NOT NULL
GROUP BY p.name
ORDER BY volume_usd DESC
LIMIT 25;

Large routed trades (≥ $50k) in the last 30 days

SELECT p.name, s.txn_hash, s.datetime, s.usd_value_in,
       CASE WHEN s.sender_address != s.origin_address THEN 'Routed' ELSE 'Direct' END AS route_type
FROM `defi_prepared.uniswap_v3_swap` s
JOIN `defi_prepared.uniswap_v3_pool` p ON s.uniswap_v3_pool_id = p.id
WHERE s.datetime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND s.usd_value_in >= 50000
ORDER BY s.usd_value_in DESC
LIMIT 100;