Balances

Table	Description
solana.assets.balances, solana.assets.sol_balances, solana.assets.spl_token_balances	Transaction-level balances from genesis.
solana.assets.balances_latest, solana.assets.spl_token_balances_latest, solana.assets.sol_balances_latest	Latest balances of all Solana addresses, updated on an hourly cadence.

Solana Balances

The solana.assets.balances table provides transaction-level SOL and SPL token balances of all Solana accounts. Each row corresponds to a change in the balance of SOL / SPL tokens caused by the transaction.

Both sol_balances and spl_token_balances are huge tables. It is advised that you query time ranges and specific addresses

spl_token_balances ~ 15bil rows
sol_balances ~ 330bil rows

Sample Query

Generate Daily Balances from tx-level balances data

This query generates daily balances from txn-level balances data.

Replace the address with the desired wallet address.

with
    balance_change_daily as (
        select 
        date(block_timestamp) as block_date, 
        row_number() over (partition by date(block_timestamp), address, token_account, mint order by block_timestamp desc, block_slot desc, txn_index desc) as rn,
        *
        from solana.assets.balances
        where address = '...'
        and is_voting=false
    ),
    changed_balances as (
        select
            *,
            lead(block_date) over (
                partition by address, token_account, mint order by block_date asc
            ) as next_update_day
        from balance_change_daily
        where rn = 1
    ),
    forward_fill as (
        select cast(d.date_timestamp as timestamp) as day, *
        from common.utility.dates d
            left join
                changed_balances b
                on d.date_timestamp >= b.block_date
                and (b.next_update_day is null or d.date_timestamp < b.next_update_day)
            where date_timestamp <= current_date and date_timestamp >= '2020-01-01'
    )
select
    f.day::timestamp_ntz(9) as block_date,
    f.address::varchar as address,
    f.token_account::varchar as token_account,
    f.mint::varchar as mint,
    
    case when f.token_account is null then 'Solana' else ft.name::varchar end as token_name,
    case when f.token_account is null then 'SOL' else ft.symbol::varchar end as token_symbol,
    case when f.token_account is null then 9 else ft.decimals end::number as token_decimals,
    f.raw_balance_str::varchar as raw_balance,
    coalesce(f.balance_str::float, div0(f.raw_balance_str, power(10, ft.decimals)::float)) as balance,
    (f.balance * hp.price)::float as usd_balance,
    hp.price::float as usd_exchange_rate,
    f.block_timestamp::timestamp_ntz(9) as last_activity_block_timestamp,
    f.block_slot::bigint as last_activity_block_slot,
    f.block_hash::varchar as last_activity_block_hash,
from forward_fill f
left join solana.raw.spl_tokens ft on f.mint = ft.address
left join common.prices.hourly hp on f.block_date = hp.timestamp and f.mint = hp.address and hp.chain = 'solana'
where 1=1 
  and raw_balance_str > '0'  -- [Optional]  Exclude non-zero balances
  and usd_exchange_rate is not null -- [Optional] Exclude balances without prices data

Note: the final output will contain all tokens held by the wallet, including undesirable spam tokens. One approach to counter this would be to filter the output of the query with the desired token mints or balances above a certain USD value.

Column Name

Column	Description	Type	Example
token_type	Type of token (SOL or SPL-TOKEN)	varchar	SOL
address	Address of the token holder	varchar	4CKFRwj6YzVygMptmFtZgBNNn7zyCsVibM8Aq5pG252v
token_account	Token account associated with the token holder	varchar
decimals	Number of decimal places	number	9
mint	Mint address of the token	varchar
program_id	Program id associated with the token	varchar
raw_balance_str	Raw balance of the token as a string for precision.	varchar	842899177
raw_balance	Raw balance of the token	float	842899177
balance_str	Balance of the token in string format for precision.	varchar	0.842899177
balance	Balance of the token.	float	0.842899177
usd_balance	USD balance at the block timestamp.	float	124.327628608
usd_exchange_rate	USD value of the balance at the block timestamp.	float	147.5
signer	Transaction Signer.	varchar	4CKFRwj6YzVygMptmFtZgBNNn7zyCsVibM8Aq5pG252v
success	Boolean indicator whether this transaction suceeded.	boolean	TRUE
is_voting	Boolean indicator whether this is a voting transaction.	boolean	TRUE
block_slot	Block slot of the transaction that resulted in the balance change.	number	274980322
block_height	Block height of the transaction that resulted in the balance change.	number	254414017
block_hash	Block hash of the transaction that resulted in the balance change.	varchar	4S4V2zoGKpMLxDmwz5PKamiKcwAy2euwEbdsypkkjNLt
block_timestamp	Block timestamp of the transaction that resulted in the balance change.	timestamp_ntz	2024-07-01 01:55:58.000
txn_id	Transaction ID that resulted in the balance change.	varchar	5oP9oJ2JLYDhTpLi4QyxbNYQooLzmSfTq4auY1Dmm8XkG1bf9R1jNNSmFVDqpB8ByQ1Ta3aqWA6A4kYTyuPeJTp1
txn_index	Transaction index of the transaction id.	number	685
unique_id	Unique id assigned to the balances entry.	varchar	txn_id-5oP9oJ2JLYDhTpLi4QyxbNYQooLzmSfTq4auY1Dmm8XkG1bf9R1jNNSmFVDqpB8ByQ1Ta3aqWA6A4kYTyuPeJTp1_address-4CKFRwj6YzVygMptmFtZgBNNn7zyCsVibM8Aq5pG252v
_updated_at	updated timestamp	timestamp_ntz	2024-07-01 07:11:07.696

Solana Balances Latest

The solana.assets.balances_latest table provides a snapshot of SPL and SOL tokens’ most recent balance information.

Table Columns

Column Name	Description	Type	Example
token_type	Type of token	varchar	SPL-TOKEN
address	Address of the token holder	varchar	AuZ9H13nRXQzQNEeR7ovmPxb1EbFpWiie2fx8rRa8zxC
token_account	Token account associated with the token holder	varchar	8TfpcEJYwJrSx9XNHqLZCBhYuJkbcF3K1z5TySq52pjh
decimals	Number of decimal places the token can be divided into	number	6
mint	Mint address of the token	varchar	3RYgndhvJtpRNAYhc1pmJ8NuEdgCp9H2DUurA1Vvpump
program_id	Program ID associated with the token	varchar	TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA
symbol	Symbol of the token	varchar	$ICE
raw_balance	Raw balance of the token	float	62388106652
raw_balance_str	Raw balance of the token as a string	varchar	62388106652
balance_str	Balance of the token in a string format	varchar	62388.10665
balance	Balance of the token	float	62388.10665
usd_balance_current	Current USD value of the balance	float	0.8110453865
usd_balance_at_block_timestamp	USD value of the balance at the block timestamp	float	5.614929599
usd_exchange_rate_current	Current USD exchange rate of the token	float	1.27E-05
usd_exchange_rate_at_block_timestamp	USD exchange rate of the token at the block timestamp	float	8.96E-05
last_activity_block_timestamp	Timestamp of the last activity involving the token	timestamp_ntz	2024-06-24 21:17:13
last_activity_block_slot	Slot number of the last activity involving the token	number	273800058
last_activity_block_hash	Hash of the block containing the last activity involving the token	varchar	BgaWmyUz3Ns1LJpD9vxTMCg5S21jXSBLrQxbjpcVbziH
last_activity_txn_index	Transaction index of the last activity involving the token	number	244
last_activity_txn_id	Transaction ID of the last activity involving the token	varchar	3FutYunZXCK76pjpPQS2xMajs6sTmk5WkyAGVVGxK38LndMuX52U4FppoR8qUqhc4DzVwejpi8kJUrvhjpRwo3o8
_updated_at__usd_exchange_rate_current	Timestamp of when the current USD exchange rate was last updated	timestamp_ntz	2024-06-26 08:00:00
_updated_at__unhydrated	Timestamp of when the data was last updated but not fully hydrated	timestamp_ntz	2024-06-25 02:33:24
_created_at	Timestamp of when the entry was created	timestamp_ntz	2024-06-26 08:43:39.613 +0000
_updated_at	Timestamp of when the entry was last updated	timestamp_ntz	2024-06-26 08:43:39.613 +0000
_changed_since_full_refresh	Boolean indicating if the entry has changed since the last full refresh	boolean	TRUE

Overview

Data Verticals

Solana

Hyperliquid 🌱

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

Balances

Balances

Solana Balances

Sample Query

Column Name

Solana Balances Latest

Table Columns

Overview

Data Verticals

Solana

Hyperliquid 🌱

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

​Balances

​Solana Balances

​Sample Query

​Column Name

​Solana Balances Latest

​Table Columns

Balances

Solana Balances

Sample Query

Column Name

Solana Balances Latest

Table Columns