Data Analyst Guide to Aptos: DeFi Swaps (pt.2)

By Ying Wu, Data Engineer @ Aptos Labs

DeFi (decentralized finance) is revolutionizing finance by leveraging immediate order settlement and providing users direct control over their assets. Some primary categories of DeFi applications include

1. Asset trading: Spot trading (AMM, CLOB), Futures, and Perps
2. Asset lending
3. Liquid Staking Derivatives

Asset trading is one of the most popular applications and is similar to buying stock or exchanging currencies in traditional finance. This article will explore 3 swap transactions in detail on Aptos where assets are exchanged using AMM pools. As mentioned in the data overview (part1), assets on Aptos are split between the legacy coin standard (coin) and the Fungible Asset standard (FA). This distinction will be important to understand as the transactions are explained.

SQL examples will use the dataset in bigquery.

Identifying defi swap transactions

Defillama has a list of list of dexs (decentralized exchanges) on Aptos, let’s start by listing transactions from liquidswap.

Clicking swap on their website brings up Petra wallet with transaction simulation. Use the ‘Function:’ string shown to filter for transactions (can also look at source for defillama liquidswap page to find which address to use)

SELECT *
FROM `bigquery-public-data.crypto_aptos_mainnet_us.transactions`
WHERE tx_type = 'user'
AND success
AND payload.entry_function_id_str = '0x9dd974aea0f927ead664b9e1c295e4215bd441a9fb4e53e5ea0bf22f356c8a2b::router::swap_exact_coin_for_coin_x1'
AND block_timestamp BETWEEN '2025–01–01' AND '2025–01–31'
LIMIT 100

For this table, note that the difference between payload.function and payload.entry_function_id is left 0 padding. For example, another liquidswap address 0x163df34fccbf003ce219d3f1d9e70d140b60622cb9dd47599c25fb2f797ba6e is not 0 padded to conform to AIP-40. In payload.functionfield, it will show up as properly padded: 0x0163df34fccbf003ce219d3f1d9e70d140b60622cb9dd47599c25fb2f797ba6e::scripts::swap (ex. tx_version = 2294166635.) In these situations, the following logic will normalize the address length

IF(LENGTH(SPLIT(payload.entry_function_id_str, '::')[0]) != 64,
  '0x' || LPAD(LTRIM(SPLIT(payload.entry_function_id_str, '::')[0],'0x'),64, '0') ||
    SUBSTR(payload.entry_function_id_str, LENGTH(SPLIT(payload.entry_function_id_str, '::')[0])+1,
      LENGTH(payload.entry_function_id_str)),
  payload.entry_function_id_str) AS entry_function_id_str_normalized

After deciding on a transaction to look at, use the following tables to get additional context

• Events — for the messages emitted by the transaction
• Resources — for change to on-chain state

Resource modified by a transaction are in the latter table, however, these changes are often difficult to parse and only show state at end of transaction. Events are useful for showing intermediate steps and often used for off-chain indexing that powers a dapp frontend.

For example, a flash loan will borrow and repay within the same transaction so the balance of that pool that’s being borrowed from will stay the same. Typically, an event is emitted to mark that a flash loan occurred in a transaction.

Swapping Coins (Liquidswap)

Example transaction 2224014200

Balance changes

Balance changes can be used to infer that the sender (odix) swapped 16.182 APT for 48k MOOMOO. Balance changes can be retrieved by querying fungible_asset_activities from our indexer graphql endpoint (example).

query get_balance {
fungible_asset_activities(
where: {transaction_version: {_eq: "2224014200"}}) {
    event_index
    storage_id
    owner_address
    asset_type
    type
    amount
  }
}

Fungible asset activities endpoint is parsed from events and has event_index (identifier) and (event) type. Since this endpoint serves both coins and fungible assets, storage_id serves as an unique identifier to get amount for any given owner + CoinStore/FungibleStore for asset.

Events

0. APT leaving odix’s coinstore
1. Fee accounting for liquidswap pool (indicating a 0.1% fee)
2. Oracle pricing update for liquidswap
3. Liquidswap liquidity swap event (with fees included)
4. MOOMOO token entering odix’s coinstore
5. Gas fee statement (all transactions have this)

Event index 3 deserves special attention since that is the event used to track liquidswap pool activity. The in/out asset types can be parsed from event type (split on comma + SwapEvent not <).

Event index 0 and 4 are Withdraw/Deposit from CoinStore with amount but not coin type. Use creation_num and map to CoinStore resource to get coin type.

Looking at the raw transaction, withdraw/deposit events have a guid struct that maps to a Coinstore (change 0 and 1) struct for coin type lookup.

Ex. Change 0 is a CoinStore for APT

{
  "address": "0x22640edec47a907370ce5d2eaf10d60004177a874f8d998df9281e1c9093463e",
  "state_key_hash": "0x220fdb58f7df47b3d28951e6c65356c5a97dc0a6cb594fa6e660b802c0e300f6",
  "data": {
    "type": "0x1::coin::CoinStore<0x1::aptos_coin::AptosCoin>",
    "data": {
      "coin": {
        "value": "999500"
      },
      "deposit_events": {
        "counter": "5817",
        "guid": {
          "id": {
            "addr": "0x22640edec47a907370ce5d2eaf10d60004177a874f8d998df9281e1c9093463e",
            "creation_num": "2"
          }
        }
      },
      "frozen": false,
      "withdraw_events": {
        "counter": "5727",
        "guid": {
          "id": {
            "addr": "0x22640edec47a907370ce5d2eaf10d60004177a874f8d998df9281e1c9093463e",
            "creation_num": "3"
          }
        }
      }
    }
  },
  "type": "write_resource"
}

Event 0 is Withdraw (from CoinStore) with matching creation_num and addresses allowing for type lookup

{
  "guid": {
    "creation_number": "3",
    "account_address": "0x22640edec47a907370ce5d2eaf10d60004177a874f8d998df9281e1c9093463e"
  },
  "sequence_number": "5726",
  "type": "0x1::coin::WithdrawEvent",
  "data": {
    "amount": "1618243104"
  }
},

Here is a query for dune that implements this lookup logic.

Changes

0. Coinstore holding APT owned by Odix
1. Coinstore holding MOOMOO owned by Odix
2. Odix’s account
3. Liquidswap pools’ fee storage
4. Liquidswap pools’ fee event store
5. Liquidswap pools’ event store
6. Liquidswap pool info
7. Table item change (APT supply tracking) — in almost every transaction

Using aptoscan explorer, there is a handy ‘Main action’ that’s parsed from events (recently added to labs explorer by 0xbe1 of Thalalabs) as well as diff button in changes tab

Previous and current coin reserves for pool

Diff of pool reserves (change 6, above) can be used to calculate how much price impact the swap had on the AMM pool. Arbitrary states can be retrieved by querying get_account_resources and providing an address and version.

What can be alarming in this example is that the APT leaves odix’s CoinStore and disappears (WithdrawEvent with no DepositEvent). This is because coins don’t have to be stored in a CoinStore and can be put into resources (see the increase in coin_y_reserve.value above).

Swapping Fungible Asset (Cellana)

Next, lets have a look at a cellana transaction by filtering on their entry function 0x4bf51972879e3b95c4781a5cdcb9e1ee24ef483e7d22f2d903626f126df62bd1::router::swap_route_entry_from_coin

Transaction 2293960645 had 13.387 APT is swapped for 100.12 (native) USDt. Unlike the liquidswap, Cellana uses fungible assets (rather than coins) for swapping.

Coin vs Fungible Asset

An analogy to think about coins vs FA (fungible assets) is that coins can be stored in pockets of a backpack (address) called CoinStore but can also be stored in different pockets (like within resources as seen in the liquidswap example above). These custom pockets are not guaranteed to have Withdraw/Deposit events. In contrast, only one FA can be stored in a backpack (address) with the caveat that now you’ll need to own one backpack per FA (FungibleStore has no type parameter).

Finding all coins a user has can be achieved by scanning through resources of type 0x1::coin::CoinStore<% at the user’s address. However, this might not be comprehensive if coins are hidden in other pockets.

Finding all FA a user has requires finding all FungibleStore objects owned by that user and will require off-chain indexing for performant lookups (more on this below).

Coins (left side) vs Fungible Assets (right side) with ovals representing resources and squares being addresses

Balance changes

Balance changes show APT withdraw and USDt Deposit at the top and bottom to the sender (0xc4bd), however, there are many more transactions in between.

APT is deposited into Cellana’s coin vault, then the contract mints an FA (Asset 0xedc2704f2cef417a06d1756a04a16a9fa6faaed13af469be9cdfcac5a21a8e2e) to represent this APT which I’ll refer to as (Cellana FA) APT. Lastly, this asset is swapped through the pool for USDt and deposited in the sender’s USDt FungibleStore.

Inspecting Cellana APT-USDt pool object 0xe8797d7adaf48abd7becddc9c4494f1309d9a83a6ea0c4be708482cbc54e36af balances shows both (Cellana FA) APT and USDt. You’ll need to click on ‘ALL’ on the coins page to show the Cellana FA APT (by default assets with certain symbols are hidden) in two FA stores (one for pool, one for fees)

(all) Coin balances page for Cellana APT-USDt pool

These balances are held in 4 FungibleStore objects that are owned by the pool.

Excludes FungibleStore holding fees

This representation makes tracking FA AMM pools straightforward

• LP supply can be found in ConcurrentSupply resource
• Pool amounts can be found in Coins tab
• All swaps (transactions modifying a resource owned by the pool) are on transactions tab

Events

0. Withdraw APT (coin) from 0xc4bd (sender)
1. Deposit APT (coin) into Cellana’s coin vault 0x3b38
2. Deposit (cellana FA) APT (minus fees) into Pool’s store 0x829f
3. Deposit (cellana FA) APT (fee) into Pool’s store 0xeb36
4. Withdraw USDt from Pool’s store (0x11f2)
5. Cellana pool swap event
6. Cellana pool reserves
7. Deposit USDt into store 0x3553 (owned by 0xc4bd)

Similarities between CoinStore and FungibleStore

• Emits Deposit/Withdraw events
• Need to map to another resource to get asset information (CoinInfo or FungibleAsset Metadata)
• No mint event: (Cellana FA) APT

FungibleStore has the additional step of having to look up the object owner to understand who got the asset.

Changes

Internally, resources are stored in slots keyed on state_key_hash (AIP-9). There can be multiple resource types stored in the same slot (resource_group_member). Any changes to a resource within a resource group will cause all resources to be emitted. This can be useful for getting context (ex. object owner resource is emitted whenever FungibleStore balance changes)

0. FungibleStore holding USDt for pool that was withdrawn from
1,2. Same state_key_hash as 0 so also emitted even though no change, owner of FA can be found in ObjectCore (1)
3. FungibleStore belonging to sender that USDt is deposit into
4,5. Same state_key_hash as 3 so also emitted
6. CoinStore holding APT belonging to Cellana coin vault
7. FungibleStore holding (Cellana FA) APT for pool that was deposited into
8. Same state_key_hash as 7 so also emitted
9. CoinStore holding APT for sender
10. Sender’s account
11–17. Cellana pool (same state_key_hash) emit due to change on Fee Accounting (16)
18. FungibleStore holding (Cellana FA) APT for Pool fee
19. Same state_key_hash as 18 so also emitted
20–23. (Cellana FA) APT (same state_key_hash) emit due to change on supply (21)
24. APT supply as table item

Unlike CoinStore, an address can have multiple FungibleStore (as seen above with the regular pool and fee pool). These will be represented as two different objects each with FungibleStore and having the same owner and metadata (mapping to same FungibleAsset).

Finding FungibleStores

For most use cases, each user only needs one FungibleStore per asset type. We special case this as a ‘primary fungible store’ and the address for this is deterministic and derived using sha3_256(32-byte account address | 32-byte metadata object address | 0xFC) (ref)

Below is python code that verifies this mapping for

• Account: 0xc4bd (sender)
• FA metadata: 0x357b (USDt)
• FungibleStore: 0x3553 (Sender’s USDt primary store)

import hashlib

def get_primary_fs_address(account_address, metadata_address):
    """
    Args:
        account_address: A 32-byte hex string representing the account address.
        metadata_address: A 32-byte hex string representing the metadata address.

    Returns:
        A 32-byte hex string representing primary fungible store address.
    """

    # Ensure inputs are 32 bytes (64 hex characters)
    if len(account_address) != 66 or len(metadata_address) != 66 or not account_address.startswith("0x") or not metadata_address.startswith("0x"):
        raise ValueError("Account address and metadata address must be 32-byte hex strings (including 0x prefix).")

    # Remove the "0x" prefix and convert to bytes
    account_bytes = bytes.fromhex(account_address[2:])
    metadata_bytes = bytes.fromhex(metadata_address[2:])
    fc_byte = bytes.fromhex("fc")  # 0xFC as a byte

    # Concatenate the inputs
    input_bytes = account_bytes + metadata_bytes + fc_byte

    # Calculate the SHA3-256 hash
    sha3_hash = hashlib.sha3_256(input_bytes).digest()

    # Convert the hash to a hex string
    return "0x" + sha3_hash.hex()


# Example usage:
account_address = "0xc4bd9cc8a2d85e9a9c89628dad734041477f86351eafc0b634114bc16d3451e8"  # sender
metadata_address = "0x357b0b74bc833e95a115ad22604854d6b0fca151cecd94111770e5d6ffc9dc2b"  # USDt

try:
    hash_result = get_primary_fs_address(account_address, metadata_address)
    print(f"Primary FA address: {hash_result}")  # 0x355303f5f1db46cc5917392cc8bf0b499c77846aedad54d7e2d7ada7328f477a
except ValueError as e:
    print(f"Error: {e}")

For cases where non-primary fungible stores are used (such as in the pool example above) we can use the current_fungible_asset_balances (cfab) endpoint in graphql

query cfab {
  current_fungible_asset_balances(
    where: {owner_address: {_eq: "0xe8797d7adaf48abd7becddc9c4494f1309d9a83a6ea0c4be708482cbc54e36af"}}
  ) {
    storage_id
    is_primary
    asset_type
    amount
    last_transaction_version
  }
}

The storage_id returns address for FungibleStore and asset_type returns the address for FungibleAsset

Note here that there are also separate _v1 (coin) and _v2 (FA) values for amount and asset_type for natively migrated assets (see below). amount will contain the sum of both coin and FA.

Swapping migrated coins (Panora aggregator)

Composability is a powerful feature of smart contracts that significantly enhances the flexibility and potential of DeFi. This allows contracts to interact seamlessly with each other; for example, Contract A can call Contract B, which can then call Contract C, all within the same transaction. The above examples showed a Defi swaps through one pool, however, using a combination of pools often gives a better exchange rate.

Panora is a defi swap aggregator that will find the best route (series of swaps) and submit it as one transaction. For example, the following swap for 100 APT goes through Thetis pool and ThalaSwap v2 pools to maximize the amount of lzUSDC received at the end.

Submitting two transactions could also accomplish the same thing, however, the on-chain state might have changed between the transactions potentially giving a worse exchange rate. Aggregators construct a multi swap that is atomic by constantly retrieving on-chain state to find the best route.

As an aside, this is why the field that is filtered on to get contract address is named entry function. Only the first function that is called by the transaction is recorded, events can be used to infer other functions that are being called within the transaction. Additionally, it is also possible to not call any functions and submit a transaction that runs a binary (script).

As more contracts are written for FA, it is cumbersome for coins to be locked and minted as a defi platform specific FA (ex. Cellana FA APT). To solve this, we introduced a native way to migrate coin ⮂ FA (AIP-63).

Walking through 2221267359 user 0xa052 swaps 3.79.. lzUSDC for 0.004 APT using several Thala v1 and Thala v2 pools.

Balance changes

In octa (no decimals) the flow of assets goes

3_797_958 lzUSDC → 379_877_329 MOD (Thala v1 pool)
379_877_329 MOD → 1_145_181_697 THL (Thala v1 pool)
1_133_729_880 THL → 40_361_958 APT (Thala v1 pool)
11_451_817 THL → 407_662 APT (Thala v2 pool)

A subset of THL (coin) is migrated to FA for swap through Thala v2 pool and the resulting APT (FA) is migrated back to coin.

Events

FungibleStore abbreviated as FS

0. Withdraw lzUSDC from 0xa052 (sender)
1. Thala v1 Pool lzUSDC — MOD fee
2. Thala v1 Pool lzUSDC — MOD swap
3. Panora Swap
4. Thala v1 Pool MOD — THL fee
5. Thala v1 Pool MOD — THL swap
6. Panora Swap
7. Thala v1 Pool THL — APT fee
8. Thala v1 Pool THL — APT swap
9. Panora Swap
10, 11. THL (FA) moves in/out of FS 0x7bda
12. Thala v2 Pool THL — APT fee FS deposit THL
13. Thala v2 Pool THL — APT FS deposit THL
14. Thala v2 Pool THL — APT FS withdraw APT
15. Thala v2 Pool THL — APT swap
16, 17. APT (FA) moves in/out of FS 0x3ad
18. Panora Swap
19. Panora Swap Summary
20. Deposit APT to sender
21. Gas fee statement

Panora outputs an event after every swap and a summary at the end.

Thala v1 pools use coins and Thala v2 pools use FA. Coins moving between the various Thala v1 pools have no events since not using CoinStore. Once the assets are migrated to FA, moving in and out of Thala v2 pool has many events (10–17). Migrations are silent (no event) but can infer from supply changes on FA.

Changes

0xa is the native FA APT
0xb7cb is Panora

0,1,2,3,4,5. APT FA (0xa) supply change -407_662 (since migrated to coin)
6. THL coin supply -11_451_817 (since migrated to FA)
7. Panora Eventstore
8. Panora Eventstore
10,11,12,13,14. THL FA (0x377a) changes to supply +11_451_817 (migrated from coin)
15,16. FungibleStore for APT (0x3ada) has balance 0
17. Thalaswap v1 CoinStore for THL
18. Thalaswap v1 CoinStore for MOD
19. Thalaswap v1 CoinStore for lzUSDC
20. Thalaswap v1 pool lzUSDC — MOD
21. Thalaswap v1 pool lzUSDC — MOD Oracle
22. Thalaswap v1 pool THL — APT
23. Thalaswap v1 pool MOD — THL
24,25. FungibleStore for THL (0x7bda) has balance 0
26,27. FungibleStore for THL (0x84af) for fees has balance 152_563_757
28. APT CoinStore for 0xa052 (sender) supply change +40_697_120
29. lzUSDC CoinStore for 0xa052 (sender) supply change -3_797_958
30. 0xa052 (sender) account
31,32. FungibleStore for CELL (0xd060) has balance 0 (not sure why this is here)
33,34. FungibleStore for THL (0xd970) owned by THL-APT pool
35,36. FungibleStore for APT (0xf472) owned by THL-APT pool
37. table item tracking APT (coin) supply
38. table item for Thalaswap v1 pool MOD — THL
39. table item for Thalaswap v1 pool THL — APT
40. table item for Thalaswap v1 pool lzUSDC — MOD

Change 28 is not exactly the same as APT from event because of gas payment

Finding migrated fungible assets

Native migration has a deterministic mapping between coin_type and FA address

Below is python code that verifies this mapping for THL

• coin_type: 0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL
• FA: 0x377adc4848552eb2ea17259be928001923efe12271fef1667e2b784f04a7cf3a

import hashlib

def get_paired_fa(coin_type):
    """
    https://github.com/aptos-labs/aptos-core/blob/677bfd1846bb6c064ea2dc9f7450790d3e305c23/sdk/src/types.rs#L151
    Args:
        coin_type: A string representing the coin type.

    Returns:
        A 32-byte hex string representing Fungible Address address.
    """

    # Ensure inputs are 32 bytes (64 hex characters)
    if not coin_type.startswith("0x") or len(coin_type.split("::")) != 3:
        raise ValueError("Check coin type")

    # convert to bytes
    apt_metadata_bytes = bytes.fromhex("000000000000000000000000000000000000000000000000000000000000000a")
    coin_type_bytes = coin_type.encode('utf-8')
    object_suffix = bytes.fromhex("fe")

    # Concatenate the inputs
    input_bytes = apt_metadata_bytes + coin_type_bytes + object_suffix

    # Calculate the SHA3-256 hash
    if coin_type == "0x1::aptos_coin::AptosCoin":
        sha3_hash = apt_metadata_bytes
    else:
        sha3_hash = hashlib.sha3_256(input_bytes).digest()

    # Convert the hash to a hex string
    return "0x" + sha3_hash.hex()


# Example usage:
coin = "0x7fd500c11216f0fe3095d0c4b8aa4d64a4e2e04f83758462f2b127255643615::thl_coin::THL" # THL

try:
    hash_result = get_paired_fa(coin)
    print(f"migrated FA address: {hash_result}")  # 0x377adc4848552eb2ea17259be928001923efe12271fef1667e2b784f04a7cf3a
except ValueError as e:
    print(f"Error: {e}")

Aside on stables and oracles

Before native stables, Aptos had bridged stables (USDC, USDT) from wormhole (bridge) and layerzero (bridge).

Layerzero bridged assets became more popular because the wormhole implementation created different assets depending on source chain, thus splitting liquidity.

Below are some of the coin_type for bridged stables (FA addresses left as an exercise to reader)

1. 0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC layerzero bridged USDC (lzUSDC)
2. 0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT layerzero bridged USDT (lzUSDT)
3. 0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T wormhole bridged USDC from ethereum (whUSDC)
4. 0xa2eda21a58856fda86451436513b867c97eecb4ba099da5775520e0f7492e852::coin::T wormhole bridged USDT from ethereum (whUSDT)
5. 0xc91d826e29a3183eb3b6f6aa3a722089fdffb8e9642b94c5fcd4c48d035c0080::coin::T wormhole bridged USDC from solana (whUSDCso)
6. 0x79a6ed7a0607fdad2d18d67d1a0e552d4b09ebce5951f1e5c851732c02437595::coin::T wormhole bridged USDC from BSC (whUSDCbs)

FA addresses for native stables below

• 0x357b0b74bc833e95a115ad22604854d6b0fca151cecd94111770e5d6ffc9dc2b USDT
• 0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b USDC

We have some addresses labeled in the code for our explorer and the community maintains a token list. Stablecoin and real world assets (RWAs such as loans) metrics can be found on RWA.xyz

We have at least 3 oracles that push price data on-chain. We have some sponsored price feeds allowing any dapp to access recent prices. Since the data is on-chain, it can be accessed in the following ways:

Pyth pushes prices into table items and can be retrieved using

SELECT
    tx_version,
    block_timestamp,
    JSON_VALUE(key.name, '$.bytes') AS price_feed_id,
    -- JSON_VALUE(value.content, "$.price_feed.price_identifier.bytes") AS price_feed_id,
    TIMESTAMP_SECONDS(CAST(JSON_VALUE(value.content, "$.attestation_time") AS INT64)) as attestation_time,
    TIMESTAMP_SECONDS(CAST(JSON_VALUE(value.content, "$.price_feed.price.timestamp") AS INT64)) as price_time, 
    TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP_SECONDS(CAST(JSON_VALUE(value.content, "$.price_feed.price.timestamp") AS INT64)), INTERVAL 1 MINUTE), MINUTE) as ts_minute,
    CAST(JSON_VALUE(value.content, "$.price_feed.price.price.magnitude") AS BIGNUMERIC) as price,
    CAST(JSON_VALUE(value.content, "$.price_feed.price.expo.magnitude") AS BIGNUMERIC) as decimals,
    CAST(JSON_VALUE(value.content, "$.price_feed.price.conf") AS BIGNUMERIC) as ci,
    -- ema
    CAST(JSON_VALUE(value.content, "$.price_feed.ema_price.price.magnitude") AS BIGNUMERIC) as price_ema,
    CAST(JSON_VALUE(value.content, "$.price_feed.ema_price.conf") AS BIGNUMERIC) as ci_ema,
FROM `bigquery-public-data.crypto_aptos_mainnet_us.table_items`
WHERE 1=1
AND address = '0xd1321c17eebcaceee2d54d5f6ea0f78dae846689935ef53d1f0c3cff9e2d6c49' -- this is table_handle
AND DATE(block_timestamp) = '2025-01-31'
LIMIT 100

Switchboard pushes prices into resources and can be retrieved using

-- https://app.switchboard.xyz/aptos/mainnet (take a while to load)
SELECT
  tx_version,
  block_timestamp,
  address AS oracle_address,
  TIMESTAMP_SECONDS(CAST(JSON_VALUE(resource, "$.round_open_timestamp") AS INT64)) as round_open,
  TIMESTAMP_SECONDS(CAST(JSON_VALUE(resource, "$.round_confirmed_timestamp") AS INT64)) as round_confirmed, 
  -- skipping max/min/median parsing
  CAST(JSON_VALUE(resource, '$.result.value') AS BIGNUMERIC) AS price,
  CAST(JSON_VALUE(resource, '$.result.dec') AS BIGNUMERIC) AS decimals,
FROM `bigquery-public-data.crypto_aptos_mainnet_us.resources`
WHERE 1=1
AND address IN (
  '0xb8f20223af69dcbc33d29e8555e46d031915fc38cb1a4fff5d5167a1e08e8367', -- apt oracle
  '0x94a63284ace90c56f6326de5d62e867d915370a7d059bf974b39aefc699b8baa', -- usdt oracle
  '0xdc1045b4d9fd1f4221fc2f91b2090d88483ba9745f29cf2d96574611204659a5', -- usdc oracle
  '0xdc7f6fbc4efe2995e1e37b9f73d113085e4ee3597d47210a2933ad3bf5b78774', -- btc oracle
  '0x5af65afeeab555f8b742ce7fc2c539a5cb6a6fb2a6e6d96bc1b075fb28067808', -- sol oracle
  '0x7b5f536d201280a10d33d8c2202a1892b1dd8247aecfef7762ea8e7565eac7b6', -- eth oracle
  '0x4531f956f68ccf05ab29a1db5e73f7c828af5f42f2018b3a74bf934e81fef80f' -- cake oracle
)
AND type_str = '0x7d7e436f0b2aafde60774efb26ccc432cf881b677aca7faaf2a01879bd19fb8::aggregator::AggregatorRound<0x7d7e436f0b2aafde60774efb26ccc432cf881b677aca7faaf2a01879bd19fb8::aggregator::CurrentRound>'
AND DATE(block_timestamp) = '2025-01-31'
LIMIT 100

Chainlink pushes prices into a SmartTable (:c) latest prices can be retrieved via view function

curl --request POST \
  --url https://fullnode.mainnet.aptoslabs.com/v1/view \
  --header 'Accept: application/json, application/x-bcs' \
  --header 'Content-Type: application/json' \
  --data '{"function":"0x3f985798ce4975f430ef5c75776ff98a77b9f9d0fb38184d225adc9c1cc6b79b::registry::get_feeds","type_arguments":[],"arguments":[]}'

Conclusion

The transparency afforded by on-chain data, combined with the ability to parse and interpret transactions is a crucial differentiator for DeFi. This article provides a practical guide to dissecting swap transactions on Move-based chains allowing for effective analysis of DeFi swaps.

Starting with raw data (events, resources) can be challenging, derived table useful for analytics can be built from parquet files created by the fungible asset processor.

A deeper dive into coins vs FA can be found in a recent Ottersec blog post.

Lastly, as an exercise to readers interested in testing their understanding, can you parse out what is happening in these DeFi agg transactions (2264429263, 2134790741, 2266956702, 2142575114)?