Skip to main content
The dex.orderflowtable provides transaction-level swap data to illustrate DEX transaction volumes through frontends.

​
Data Lineage

The dex.orderflow model builds upon swap events identified from both dex.trades and dex.aggregator_trades. The orderflow table aggregates swaps by transaction hash. The exception here is cow-protocol swaps, which are treated as individual transactions. Within each transaction, we roll up all swaps that are routed by aggregators and all swaps that are executed by pools. The usd_volume of the swap is determined by the max usd_amount of the swap within the entire transaction. The volume and swaps executed by all dex pools are aggregated in the liquidity_details column. For volume and swaps routed through dex aggregators, they are wrapped in the aggregator_details

​
Frontend Labeling

The orderflow model utilises both contract interactions and the integrator field in dex.aggregator_tradesto identify the most probably frontend where the transaction was executed. Labelling based on Contract Interaction Internally, Allium maintains a mapping of key telegram bots, MEV contracts, and aggregator routers which we use to label the transaction by the most probable frontend by the transaction’s to_address
Labelling Based on Event Log In the event logs of certain transactions, the aggregator may emit fields which tell us where the swap source is executed from. This can be from the details of the swap referrals or the actual name of the frontend that was used.
  • In OpenOcean’s event swap log, the referrer field indicates the fee recipient and frontend integrator where the swap was executed from.
    • In this swap, the referrer points to the Rabby Fee Wallet.
  • In Kyberswap Aggregator’s client data event log, the hex-decoded data will tell us the source of the swap: {"Source":"dextools","AmountInUSD":"27.418064752878504","AmountOutUSD":"25.55357350955812","Referral":"...}
These fields are parsed and used to label the integratorfields in the dex.aggregator_tradesmodel. Heuristics to determine frontend A combination of integrator and contract labels is used to infer the frontend. The orderflow currently uses the following logic. Users can use the dex.unhydrated_orderflowto tune their labelling logic. 
select
    coalesce(
        orderflow.integrators[0]::varchar,
        entities.project::varchar,
        case
            when orderflow.aggregator_source[0] is not null
            then orderflow.aggregator_source[0]::varchar || ' Integrator'
        end
    )::varchar as frontend,
    orderflow.*
from <chain>.dex.unhydrated_orderflow orderflow
left join
    common.identity.entities
    on entities.address = orderflow.to_address
    and lower(entities.chain) = '<chain>'
    and entities.category in ('dex', 'dex_aggregator')

​
Sample Query

select
  date_trunc('week', block_timestamp) as date,
  -- Alternatively, one may assume the first dex aggregator or pools swapped, using:
  -- coalesce(frontend, aggregator_source[0]::varchar, liquidity_source[0]::varchar) as frontend,
  coalesce(frontend, 'others') as frontend,
  count(distinct senders) as user_count,
  sum(usd_volume) as usd_volume
from ethereum.dex.orderflow
group by all
order by 1 desc

​
Table Columns

Unique Key: unique_id
Column NameData TypeDescription
frontendVARCHARThe frontend interface used for the transaction
block_numberBIGINTThe block number in which the transaction was included
block_timestampTIMESTAMP_NTZ(9)The timestamp of the block in which the transaction was included
transaction_hashVARCHARThe hash of the transaction
transaction_indexBIGINTThe index of the transaction within the block
swapper_addressVARCHARThe address of the user performing the swap. For most trades, this comes from the transaction_from_address field in dex.trades / dex.aggregator_trades. However, for UniswapX and CowProtocol swaps, it comes from the sender field instead.
from_addressVARCHARThe address initiating the transaction (msg.sender)
to_addressVARCHARThe address receiving the transaction
is_aggregatorBOOLEANFlag indicating if the transaction is an aggregator
usd_volumeNUMERICThe max usd_amount of the swap within the entire transaction
total_usd_volumeNUMERICThe sum of the usd_amount of all swaps within the transaction
sendersVARIANTArray of addresses sending tokens in the transaction
recipientsVARIANTArray of addresses receiving tokens in the transaction
user_token_inputVARCHARAddress of the token the user is selling
user_token_input_symbolVARCHARSymbol of the token the user is selling
user_token_outputVARCHARAddress of the token the user is buying
user_token_output_symbolVARCHARSymbol of the token the user is buying
tokens_soldVARIANTArray of tokens sold in the transaction
tokens_boughtVARIANTArray of tokens bought in the transaction
integratorsVARIANTArray of integrator names involved in the transaction
integrator_tagsVARIANTArray of integrator contract addresses
aggregator_sourceVARCHARSource of the aggregator
aggregator_usd_volumeNUMERICUSD volume of the aggregator portion
aggregatorsVARIANTArray of aggregator contracts involved
aggregators_countBIGINTCount of aggregators involved
aggregator_detailsVARIANTJSON details of aggregator swaps
liquidity_sourceVARIANTArray of liquidity sources used
liquidity_usd_volumeFLOATUSD volume from liquidity sources
liquidity_poolsVARIANTArray of liquidity pool addresses used
liquidity_pools_countBIGINTCount of liquidity pools involved
liquidity_detailsVARIANTJSON details of liquidity pool swaps
total_tradesBIGINTTotal number of trades in the transaction
unique_idSTRINGUnique identifier for the transaction
⌘I