Balances Daily Est - Allium Documentation Hub

Table Details

Property	Value
Table Name	`beacon.raw.balances_daily_est`
Table Status	Production-Ready
Unique Key	`slot_timestamp`, `slot_number`, `validator_index`
Clustering Key(s)	`to_date("slot_timestamp")`
Search Optimization	`validator_index`

The beacon.raw.balances_daily_est table provides daily validator balances for all validators on the Beacon Chain using an Eastern Standard Time end-of-day boundary. Each row is one validator’s state for that EST calendar day: the snapshot is the slot at 23:59:59 EST (04:59:59 UTC the next day on slot_timestamp). EST is a fixed UTC−5 offset with no daylight-saving adjustment, so the boundary stays at 04:59:59 UTC all year. Column definitions match beacon.raw.balances_daily, with an additional slot_timestamp_est field for the snapshot time in EST (slot_timestamp minus 5 hours).

Use Cases: Auditing, Accounting & Research

Auditing and compliance: Track balance changes day over day; use withdrawal_address, epoch fields, and finalized / execution_optimistic for reconciliation and certainty of state. Accounting and financial analysis: Daily snapshots in EST for reporting aligned to US Eastern business dates (note: no daylight-saving shift); use balance / raw_balance deltas between consecutive days for accruals. Research and network analytics: Study lifecycle transitions via status and epoch columns; aggregate by date(slot_timestamp_est) for EST-dated network metrics.

Sample Queries

Query 1: Total validators, balance, and effective balance by EST day

select
    date(slot_timestamp_est) as snapshot_day_est,
    status,
    count(*) as validators,
    sum(balance) as total_balance,
    sum(effective_balance) as total_effective_balance
from beacon.raw.balances_daily_est
group by snapshot_day_est, status
order by snapshot_day_est desc

Query 2: Balances for a set of validators

select
    *
from beacon.raw.balances_daily_est
where validator_index in (1, 1000, 145)
order by slot_timestamp_est desc

Table Columns

Column Name	Data Type	Description
slot_number	NUMBER(38,0)	Slot number of the state where the pending deposits to beacon chain is fetched.
slot_timestamp	TIMESTAMP_NTZ(9)	Slot timestamp of the state where the pending deposits to beacon chain is fetched.
slot_timestamp_est	TIMESTAMP_NTZ(9)	Snapshot time in Eastern Time (end-of-day EST for the reporting date).
validator_index	NUMBER(38,0)	916,865
raw_balance	VARCHAR(16777216)	Validator balance as an integer in gwei (1e9 gwei = 1 ETH).
balance	FLOAT	Balance of the validator.
effective_balance	FLOAT	Effective balance of the validator.
raw_effective_balance	VARCHAR(16777216)	Effective balance at stake in gwei.
status	VARCHAR(16777216)	Current status of the validator.
pubkey	VARCHAR(16777216)	0xb20cbf80ded11e4089be8ff68c3150aa19f1a5dc064450a1a10097194f1d09d470261c183d8ca44717a4da7cafa11c4f
withdrawal_credentials	VARCHAR(16777216)	0x010000000000000000000000e839a3e9efb32c6a56ab7128e51056585275506c
withdrawal_prefix	VARCHAR(16777216)	0x01
withdrawal_address	VARCHAR(16777216)	0xe839a3e9efb32c6a56ab7128e51056585275506c
slashed	BOOLEAN	Whether the validator has been slashed.
activation_eligibility_epoch	VARCHAR(16777216)	Epoch when the validator became eligible for activation.
activation_epoch	VARCHAR(16777216)	230889
exit_epoch	VARCHAR(16777216)	-1
withdrawable_epoch	VARCHAR(16777216)	Epoch when validator can withdraw.
execution_optimistic	BOOLEAN	If the slot/epoch is executed optimistically.
finalized	BOOLEAN	If the slot/epoch is finalized.
unique_id	VARCHAR(16777216)	date-2023-05-10_validator_index-459015
_created_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was created in the database.
_updated_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was last updated in the database.

EVM

​Table Details

​Use Cases: Auditing, Accounting & Research

​Sample Queries

​Query 1: Total validators, balance, and effective balance by EST day

​Query 2: Balances for a set of validators

​Table Columns