How to Query Blockchain Data Without Bad Metrics

TL;DR:

• Start with a decision and a metric definition, not the easiest table.
• Set the grain of every model before joining blockchain data.
• Validate a recurring metric against independent chain state where possible.
• A plausible number is not a trustworthy number.

A query can be plausible and still be wrong

Blockchain data looks objective because the ledger is public. The difficult part is not retrieving a log. It is deciding what that log means, then proving that the resulting metric still corresponds to chain state and the business question.

I built a small public sUSDS indexer to make this problem explicit. It indexes ERC-4626 deposit, withdrawal and yield-accrual events, then reconciles the indexed result with live contract state at a pinned finalized block.

The tempting check was "event totals are close enough to total assets." That check is wrong for this contract. totalAssets() extrapolates yield between events, so an event-derived total can diverge even when the indexer has every event. A tolerance would hide both the expected difference and real mistakes.

Instead, the indexer tests four exact invariants. Net minted shares must equal total supply. The latest indexed yield accumulator must equal the contract's stored value. The event-derived asset balance must equal the contract balance. And the indexer must reproduce the contract's yield extrapolation at the exact pinned block. Any divergence fails the run.

That is the mindset I use for onchain metrics that influence a product, investor or roadmap decision. A query that runs is not enough.

Define the metric before opening the table

Write down the population, qualifying event, window, chain scope, exclusions and reporting grain. For example:

Weekly active borrowers are distinct wallet addresses that initiated or increased a borrow position during the week, excluding known contracts and liquidator addresses.

That definition can be challenged and maintained. "Weekly users" cannot.

The definition also reveals what you do not know. Does wallet mean a person? Are cross-chain addresses treated as one entity? Is a router a user? Treat those as modelling choices, not silent facts.

Set the grain before you join

One transaction can produce several logs, token transfers and internal calls. Joining transaction rows to transfers and aggregating without a stated grain can multiply value without producing an obvious error.

Model	One row represents	Typical use
Raw event	One decoded contract log	Protocol actions and state changes
Transaction	One transaction hash	Submission and execution analysis
Wallet-day	One address per day	Activity, retention and cohorts
Entity-period	One attributed counterparty per period	Concentration and exposure

Aggregate the many-side of a join before joining it to a higher-grain model. Do not use distinct as a repair for a grain you have not defined.

Keep entity logic separate from raw chain activity

Address counts are not automatically measures of independent users or capital sources. In a private analysis of a Solana DEX's top 100 pools, fewer than 1% of LP wallets controlled roughly 82% of the cohort's TVL. More than 18 addresses mapped to one automated-vault entity.

The raw address result was real. It simply answered a different question from the one leadership needed to make product and risk decisions. Keep raw activity, labels and entity mapping as separate layers so a reviewer can see which one changed the metric.

Validate against a second source of truth

For one-off exploration, inspect sample transaction hashes, logs and contract addresses. For recurring models, build validation into the pipeline:

1. Pin the reporting data to a known block or finalized range.
2. Record provenance: chain, block, transaction hash and log index.
3. Test keys, accepted event types, freshness and amount bounds.
4. Reconcile aggregates against a contract read, independent query or known event.
5. Fail loudly when the model diverges, then diagnose the direction of the difference.

Finality matters. A dashboard showing the latest head may be fine for an operational monitor, but a recurring report should state the confirmation or finality rule behind the number. More frequent is not automatically more trustworthy.

Use the right access pattern

Decoded datasets are efficient for exploration, dashboards and common protocol questions. RPC reads are useful when you need direct state, a specific receipt or an event stream that is not available in an index. A governed pipeline is appropriate when a metric repeatedly informs revenue, risk, incentives or executive reporting.

The choice is not ideological. Use the lightest system that can produce a validated answer, then build more infrastructure only when the decision requires repeatability and ownership.

The output is a trusted decision system

The sUSDS example is deliberately narrow, but the principle generalizes. A precise event stream can still be insufficient when the contract's state evolves between events. A large analytics table can still mislead when its join grain is wrong. A wallet count can still hide one entity.

Reliable onchain analytics combines explicit definitions, layered models, source provenance and validation that fails when it should. That is how public ledger data becomes evidence a team can use.

For recurring onchain metrics and governed data models, see Unchain Data's Onchain Pipelines service.

Frequently asked questions

What is the first step in querying blockchain data?

Define the decision and the metric. State the population, qualifying event, time window, chain scope, exclusions and reporting grain before choosing a dataset. This prevents the common failure mode of producing a clean chart from a convenient table that does not answer the question.

Why do blockchain queries double-count data?

A transaction can create several logs, transfers and internal calls. If a one-to-many table is joined to a transaction-level model without aggregating first, transaction value can be repeated across rows. Set the grain of each model explicitly and aggregate before broad joins.

How should an onchain metric be validated?

Inspect raw samples first, then add automated checks for recurring logic. Compare the output with an independent query, a contract state read or a known event. Pin the comparison to the same block or finalized range so both sides describe the same state.