Skip to main content
Schema: solana.core Table: fact_token_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 token balances for each account and mint combination on the Solana blockchain. It creates a complete time series by forward-filling the most recent balance when thereā€™s no activity on a given day, ensuring every account-mint combination has a balance record for each day since their first balance change. When multiple balance updates occur within a day, only the last balance is retained, providing a consistent end-of-day view. 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 account-mint combinations will ā€œdisappearā€ from the daily snapshots when their balance reaches zero and ā€œreappearā€ when they receive tokens again.

ā€‹
Key Use Cases

  • Daily balance tracking and portfolio analysis over time
  • Time-series analysis of token holdings and distribution patterns
  • Historical balance lookups for any account-mint combination on any date
  • Whale tracking and large holder analysis with daily granularity
  • DeFi protocol analytics requiring daily balance snapshots
  • Token distribution studies and holder concentration analysis

ā€‹
Important Relationships

  • Sources data from core.fact_token_balances for balance change events
  • Links to core.fact_token_account_owners through post_owner for ownership attribution
  • Connects to price.ez_asset_metadata via mint for token metadata and pricing
  • Joins with core.ez_transfers for transfer context and flow analysis

ā€‹
Commonly-used Fields

  • balance_date: The date for the balance snapshot (primary time dimension)
  • account: Token account address holding the balance
  • mint: Token mint address identifying the specific token
  • amount: The token balance amount (decimal adjusted) at end of day
  • owner: The owner of the token account (for attribution)
  • last_balance_change: The last date when this accountā€™s balance actually changed (only tracks dates when account had a positive balance)
  • balance_changed_on_date: Boolean indicating if the balance changed on this specific date

ā€‹
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 SPL tokens, this is the token account address. Used to attribute balances and transfers to specific accounts.
MINTTEXTUnique address representing a specific token
AMOUNTFLOATThe accountā€™s balance after the transaction, decimal-adjusted. For tokens, this is in the tokenā€™s native units (adjusted for mint decimals).
OWNERTEXTThe owner of the token account at the end of the block. Used for block-level attribution of balances and transfers. This is a base58-encoded address.
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.
TOKEN_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:
  • SPL Token: 9xQeWv... (token account address)

ā€‹
AMOUNT

Example:
  • USDC: 50.00 (represents 50 USDC tokens after a transfer)

ā€‹
OWNER

Example:
  • 7GgkQ2...

ā€‹
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.