Skip to main content
Schema: solana.core Table: fact_sol_daily_balances Type: Base Table

View DBT Documentation

View the complete technical documentation and data lineage for this table

ā€‹
Description

This table provides daily snapshots of native SOL balances for each account on the Solana blockchain. It creates a complete daily time series by forward-filling the most recent balance when thereā€™s no activity on a given day, ensuring every account has a balance record for each date since their first transaction. When multiple balance updates occur within a day, only the last balance is retained. The table tracks native SOL only (mint address: So11111111111111111111111111111111111111111). Important: If an account is left with a 0 balance at the end of the day, it is not included in the table. This means accounts will ā€œdisappearā€ from the daily snapshots when their balance reaches zero and ā€œreappearā€ when they receive SOL again.

ā€‹
Key Use Cases

  • Daily balance analysis: Track SOL holdings over time for accounts, wallets, and protocols
  • Portfolio tracking: Monitor SOL balance changes and trends for specific addresses
  • Whale watching: Identify large SOL holders and track their balance movements
  • Protocol analysis: Analyze SOL reserves and treasury balances for DeFi protocols
  • Time series analytics: Perform historical balance analysis and trend identification
  • Snapshot reporting: Generate point-in-time balance reports for any historical date

ā€‹
Important Relationships

  • Sources data from core.fact_sol_balances which contains all SOL balance changes
  • Uses crosschain.core.dim_dates for generating complete daily time series
  • Complements core.fact_token_daily_balances which handles SPL token balances
  • Related to core.ez_transfers for understanding SOL movement patterns

ā€‹
Commonly-used Fields

  • balance_date: Essential for time-based analysis and filtering to specific dates
  • account: Core field for account-specific balance tracking and wallet analysis
  • amount: The SOL balance amount (in decimal SOL, not lamports) for value calculations
  • last_balance_change: Critical for understanding when balances were last updated (only tracks dates when account had a positive balance)
  • balance_changed_on_date: Key for filtering to only dates with actual balance activity

ā€‹
Sample Queries

ā€‹
Get current SOL balance for a specific account

SELECT
    account,
    amount as sol_balance,
    last_balance_change
FROM solana.core.fact_sol_daily_balances
WHERE account = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
    AND balance_date = CURRENT_DATE() - 1

ā€‹
Track SOL balance changes over time for an account

SELECT
    balance_date,
    amount as sol_balance,
    balance_changed_on_date
FROM solana.core.fact_sol_daily_balances
WHERE account = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
    AND balance_date >= CURRENT_DATE() - 30
ORDER BY balance_date DESC

ā€‹
Find accounts with largest SOL balances on a specific date

SELECT
    account,
    amount as sol_balance
FROM solana.core.fact_sol_daily_balances
WHERE balance_date = '2024-01-01'
    AND amount > 0
ORDER BY amount DESC
LIMIT 100

ā€‹
Analyze SOL balance distribution

SELECT
    CASE
        WHEN amount >= 10000 THEN '10K+ SOL'
        WHEN amount >= 1000 THEN '1K-10K SOL'
        WHEN amount >= 100 THEN '100-1K SOL'
        WHEN amount >= 10 THEN '10-100 SOL'
        ELSE '<10 SOL'
    END as balance_tier,
    COUNT(*) as account_count,
    SUM(amount) as total_sol
FROM solana.core.fact_sol_daily_balances
WHERE balance_date = CURRENT_DATE() - 1
GROUP BY 1
ORDER BY total_sol DESC

ā€‹
Columns

Column NameData TypeDescription
BALANCE_DATEDATEThe date for which the balance snapshot is recorded. This field represents the calendar date (in UTC) for the daily balance record, providing the time dimension for balance analysis.
ACCOUNTTEXTThe base58-encoded address of the account holding the asset. For native SOL, this is the wallet address. Used to attribute balances and transfers to specific accounts.
AMOUNTFLOATThe accountā€™s balance after the transaction, decimal-adjusted. For SOL, this is in units of SOL (not lamports).
LAST_BALANCE_CHANGEDATEThe date when this account last had an actual balance change that resulted in a positive balance. This field tracks the most recent date when the balance was modified to a positive amount, which may be earlier than the balance_date due to forward-filling of balances on days with no activity. Note that accounts with zero balances are excluded from daily balance tables.
BALANCE_CHANGED_ON_DATEBOOLEANBoolean flag indicating whether the balance actually changed on this specific date. TRUE means there was a balance-changing transaction on this date, FALSE means the balance was forward-filled from a previous date to maintain a complete daily time series.
SOL_DAILY_BALANCES_IDTEXTA unique, stable identifier for each record in this table. The primary key (PK) ensures that every row is uniquely identifiable and supports efficient joins, lookups, and data integrity across models. The PK may be a natural key (such as a blockchain transaction hash) or a surrogate key generated from one or more fields, depending on the tableā€™s structure and requirements.
INSERTED_TIMESTAMPTIMESTAMP_NTZThe timestamp when this transaction record was first inserted into the analytics database. Used for data freshness tracking and incremental model logic. Format: YYYY-MM-DD HH:MI:SS. Not derived from the blockchain, but from the ETL process.
MODIFIED_TIMESTAMPTIMESTAMP_NTZThe timestamp when this transaction record was last updated in the analytics database. Used for tracking updates and supporting incremental model logic. Format: YYYY-MM-DD HH:MI:SS. Not derived from the blockchain, but from the ETL process.
_INVOCATION_IDTEXT

ā€‹
Column Details

ā€‹
BALANCE_DATE

Example:
  • 2024-01-15
  • 2024-01-16
Business Context:
  • Essential for time-series analysis of token balances, portfolio tracking, and historical balance lookups.
Analytics Use Cases:
  • Daily balance trending, portfolio performance analysis, holder distribution over time, and balance change pattern analysis.

ā€‹
ACCOUNT

Example:
  • Native SOL: 7GgkQ2... (wallet address)

ā€‹
AMOUNT

Example:
  • SOL: 0.23456789 (represents 0.23456789 SOL after a transfer)

ā€‹
LAST_BALANCE_CHANGE

Example:
  • 2024-01-10 (when balance_date is 2024-01-15, indicating no changes for 5 days)
Business Context:
  • Used to identify active vs. inactive accounts, understand balance change frequency, and distinguish between actual balance changes and forward-filled values.
  • Only accounts with positive balances are tracked.
Analytics Use Cases:
  • Account activity analysis, dormant account identification, balance change frequency tracking, data freshness assessment, and identifying when accounts last held positive balances.

ā€‹
BALANCE_CHANGED_ON_DATE

Example:
  • TRUE (balance changed on this date)
  • FALSE (balance carried forward from previous day)
Business Context:
  • Distinguishes between days with actual balance activity versus days where balances are carried forward. Critical for understanding account activity patterns and data completeness.
Analytics Use Cases:
  • Account activity tracking, identifying active trading days, filtering for actual balance changes, and understanding transaction frequency patterns.