BI & Dashboards: Finality-Aware Analytics

Real-time blockchain data engineering for the dashboards people trust.

The latest block lies.

TL;DR

Production dashboards must model finality explicitly, surface freshness and latency, and gracefully revise when the chain changes its mind. As Blocks get reorg’d, finality differs by chain, and “fresh” ≠ “final.” This post is a practical proposal field guide to building BI that’s fast and correct.

---

Why “latest block” is a trap

In web2, the newest event is usually safe to show. On-chain, the newest block can be:

• Pending (from mempool, not even in a block)

• Included (in the canonical head, but may be reorg’d)

• Safe/Justified (unlikely to be reorg’d, yet not fully final)

• Finalized (economically or protocol-final)

If your charts don’t differentiate these states, users will see numbers jump, dip, then “mysteriously” settle. That erodes trust, triggers false alerts, and undermines your whole stack.

The Finality Model (that actually works)

Think of every metric as living in three lenses:

1. Now — includes mempool + head blocks for ultra-low latency.

2. Safe — excludes the most recent K blocks (chain-tunable); strong for ops.

3. Final — finalized by protocol or after N confirmations; strong for finance.

Expose these as first-class selectors in your UI and as dimensions in your warehouse.

metric_name: swap_volume_usd
dimensions: [chain_id, dex, token, finality_tier ∈ {now, safe, final}]
facts: value, as_of_block, as_of_time, block_distance, ingestion_latency_ms

How big should K / N be?

Make it configurable per chain and per product surface. Ops pages tolerate “now.” Executive KPIs want “final.” Default to “safe” for general dashboards. (Your SRE will thank you.)

UX patterns that build trust

1) Finality badges

Show a compact badge on every widget:

• Now (−0…2 blocks)

• Safe (≥K blocks deep)

• Final (finalized / ≥N confirmations)

Include a tooltip with as_of_block, block_time, block_distance, and pipeline_latency. Users shouldn’t guess how fresh their view is.

2) Dual-pane “Now vs Final”

For volatile metrics (e.g., liquidations), render a split view:

• Left: Now (what’s happening)

• Right: Final (what history will keep)

Users see the story and the record at once.

3) Time window “settling” ribbon

When showing the last 5–10 minutes, overlay a ribbon labeled “settling window”. Animate subtle adjustments when reorgs roll back recent points. Communicate correction, not glitch.

4) Alerts with confidence escalation

Send alerts at Now with “Low confidence,” then auto-escalate to Safe and Final as confirmations accrue (and auto-retract if invalidated).

Data contracts for BI (yes, you need them)

Define a Finality Contract between your stream processor and BI:

• Columns you guarantee to every downstream table:

  • as_of_block, as_of_time

  • finality_tier (now|safe|final)

  • block_distance (head_height - as_of_block)

  • reorg_applied (bool)

  • version (semantic change counter)

• Idempotent upserts keyed by (metric_key, as_of_block, finality_tier)

• Allowed lateness (max time you may revise a value for a given tier)

This lets Looker/Tableau/Preset (pick your flavor) query consistent shapes, even while the numbers evolve.

---

Reference pipeline (from node to chart)

1. Ingest

   • Topics: mempool.tx, block.head, block.finalized

   • Partition by block_number and/or tx_hash

2. Stream processing

   • Watermarks by block_number

   • Two outputs per metric:

     • Hot table (Now/Safe) in a low-latency store (DuckDB-LSM, ClickHouse, Materialize, Pinot, BigQuery BI Engine, etc.)

     • Final table in your durable store (Snowflake/BigQuery/Delta)

   • Reorg handler:

     • On reorg_event, emit compensating updates (DELETE/UPSERT) for affected ranges

3. BI serving layer

   • Semantic model with finality_tier and freshness baked in

   • Caching keyed by (query_hash, finality_tier, max_block_age)

Watermarks & allowed lateness (the heart of it)

Watermark: “I’m confident all events up to block B have arrived (for tier T).”

• watermark_now = head_block

• watermark_safe = head_block - K

• watermark_final = finalized_block (or) head_block - N

Your stream job should only close windows (and publish aggregates) when the tier’s watermark passes the window end. If a reorg arrives that moves the watermark backwards, reopen and revise—that’s normal. Design for it.

---

Rollback & reprocess without tears

A practical pattern:

• Store delta logs of your aggregations (the adds and the subtracts).

• For each metric record, keep:

  • op ∈ {+1, -1}

  • source_block, source_tx

  • finality_tier

• On reorg:

  • Emit -1 deltas for invalidated blocks at impacted tiers.

  • Replay with new canonical blocks.

• Downstream stores can sum deltas idempotently and stay correct.

This avoids heavy full recomputes while preserving auditability.

---

Example: Finality-aware SQL (streaming-friendly)

A proposal on how to materialize a freshly made swap:

CREATE MATERIALIZED VIEW swaps_now AS
SELECT
  chain_id,
  dex,
  date_trunc(’minute’, block_time) AS ts_minute,
  SUM(usd_amount) AS volume_usd,
  MAX(block_number) AS as_of_block,
  ‘now’::text AS finality_tier,
  (SELECT head_block FROM control_plane) - MAX(block_number) AS block_distance
FROM stream_swaps
WHERE block_number >= (SELECT head_block FROM control_plane) - 2
GROUP BY 1,2,3;

With the final perspective where “Final” gets updated when finalized_block advances or when confirmations >= N:

CREATE MATERIALIZED VIEW swaps_safe AS
SELECT
  chain_id,
  dex,
  date_trunc(’minute’, block_time) AS ts_minute,
  SUM(usd_amount) AS volume_usd,
  MAX(block_number) AS as_of_block,
  ‘safe’::text AS finality_tier,
  (SELECT head_block FROM control_plane) - MAX(block_number) AS block_distance
FROM stream_swaps
WHERE block_number <= (SELECT head_block FROM control_plane) - {{K}}
GROUP BY 1,2,3;

The control_plane table is where your stream job writes head_block, finalized_block, and chain-specific thresholds. BI reads it to render badges and freshness timers.

---

What the user should see (and why it matters)

• A freshness clock: “Data as of block 21,345,678 (12:34:56 UTC), 3 blocks behind head.”

• A finality badge on each tile (Now/Safe/Final).

• A hover diff for unsettled windows: “This value may revise by ±1–3% as blocks finalize.”

• A consistent story: the number you saw “now” reappears as “final” a few minutes later, or it is clearly superseded with an audit trail.

Trust grows when users can explain a change in plain terms: “it finalized.”

---

Costs, latency, and the 80/20

You don’t need every tile to be “now.” In fact, you probably shouldn’t.

• Keep the hot path tiny. Only business-critical metrics belong in Now.

• Make Safe your default. It balances correctness and speed.

• Reserve Final for finance. P&L, revenue share, custody, and compliance.

This trims infra costs and reduces reorg churn across your estate.

---

Testing & SLIs

Measure what you promise:

• Freshness SLI: P95(as_of_time → now) ≤ target by tier

• Correctness SLI: |final - safe| / final ≤ epsilon over last T hours

• Reorg Repair SLI: P99 time_to_correct(value) after reorg ≤ target

Alert when the gap between Now and Final grows—it’s a canary for upstream issues (lagging nodes, dropped partitions, rate limits).

---

Common anti-patterns (and fixes)

• Anti-pattern: “We cache for 5 minutes.”
  Fix: Cache by (query, finality_tier, max_block_age). The tier is the contract, not a wall-clock.

• Anti-pattern: “We recompute the last hour if a reorg happens.”
  Fix: Delta-log your aggregates and only roll back the impacted range.

• Anti-pattern: “Our tiles silently jump.”
  Fix: Show badges, settling ribbons, and small inline changelogs.

---

Production checklist

• Tiers defined (now|safe|final) and chain-tunable thresholds

• Watermarks and allowed lateness per tier

• Reorg-aware idempotent upserts (keyed by block + tier)

• Delta-log or compensating updates for rollback

• Finality & freshness visible in every widget

• Alerting escalates confidence over time

• SLIs for Freshness, Correctness, and Reorg Repair

---

Closing

Real-time on the chain is a negotiation between speed and truth. Dashboards that acknowledge that: by modeling finality, signaling freshness, and embracing revision will feel faster and more reliable at once. Build BI that respects the chain’s rhythm. Your users (and your on-call rotation) will feel the difference.

Coming next in the series

• Observability: latency SLOs that keep you honest

• Conclusion: build for block time