How we calculate address balances on EVM blockchains.
Conceptually, the current balance of an account is the cumulative sum of tokens that it has received and sent out from its address.
In our balances schema, we cover the following asset types:
- Native token (e.g. ETH / MATIC)
- ERC20 (e.g. USDC, LDO. Note: Wrapped ETH and MATIC will fall under ERC20)
- ERC721 (e.g. Bored Aped Yacht Club)
- ERC1155 tokens (e.g. Opensea Shared Storefront)
To calculate the balance of a particular asset type, we construct a credit-debit table for each asset type, that accounts for both the direction and amount of transfer for each asset.
Identifying the various types of transfers for each asset type is the first step toward balance calculation.
These transfers will be used to construct a credit-debit table for cumulative sum calculation.
For native tokens transfers (i.e. Native ETH, MATIC), the movement of these tokens is mostly identified from traces.
- Value transfer: native token transfer that is neither a delegatecall, callcode or staticcall
- Gas payment to miner:
receipt_gas_used * (receipt_effective_gas_price - base_fee_per_gas)
- Gas burn (EIP-1559):
receipt_gas_used * blocks.base_fee_per_gas
For Polygon, there are events logs to include in this debit-credit table to calculate MATIC balances.
- childERC20 event transfers A child chain transfer is a transfer from Ethereum to Polygon
- These events surface on Polygon as transactions with to and from addresses equal to the null address
- There are no standard transfer traces for those transactions and event logs are used in this case
For ERC20, ERC721, ERC1155 tokens. The movement of assets will be accounted for by the transfer event logs emitted by each asset type.
- ERC20 Transfer() Deposit() and Withdrawal() for wrapped native assets
- ERC721 Transfer()
- ERC1155 TransferSingle() and flattened TransferBatch()
By default, Snowflake’s precision is 38 and scale is 0 (i.e. NUMBER(38, 0)).
With Snowflake native arithmetic functions and precision for cumulative sum will result in balances getting more inaccurate over time.
To retain precision when calculating balances, we cast our numeric variables into VARCHAR and use Java libraries and Snowflake User-Defined Functions (UDFs) to perform a cumulative sum operation