Katkat
Back

Design a Payment-Count Dashboard That Scales with Readers and Updates

RampSoftware Engineer · Onsite
HardSoftware EngineerOnsiteSystem Design

Last updated

Tests candidate's ability to design a real-time, low-latency financial metrics dashboard that evolves from a simple relational database setup to a horizontally scalable, stream-aggregated architecture resilient to duplicate events and counter drift.

Scenario

Ramp provides internal risk, operations, and finance teams with administrative dashboards tracking transaction metrics in real time (e.g., total volume, settled payments, declined authorization counts across merchants and card tiers).

Initially, this dashboard ran simple SELECT COUNT(*) queries against the primary relational database. As transaction volume grew and downstream microservices (like fraud velocity checks) began querying these aggregated counters, read latencies spiked and the primary database experienced severe lock contention. You are tasked with redesigning this aggregation and query system to scale robustly.

Requirements

Functional Requirements

  • Provide real-time payment counts and total amounts aggregated across dimensions (e.g., status, merchant ID, card tier) over variable time windows (last 1 hour, last 24 hours, month-to-date).
  • Support low-latency read APIs for both administrative UI users and automated risk microservices.
  • Provide a reliable audit and reconciliation mechanism to correct discrepancies caused by dropped events, network retries, or counter drift.

Non-Functional Requirements

  • Freshness: Payment updates should be reflected in query results within 2 seconds.
  • Low Latency: Counter queries must return within 30ms (p99).
  • High Availability: 99.99% read uptime; write ingestion must be resilient to downstream aggregate store downtime.
  • Exactness & Consistency: Financial metrics must be deduplicated and auditable against the source of record.

Scale Assumptions

  • Peak ingestion: 20,000 payment state change events/second.
  • Peak read traffic: 10,000 QPS across internal dashboards and automated rule engines.
  • Total merchants: 200,000; total active cards: 5 million.

Key Areas to Address

  1. Architecture Evolution: Transitioning from on-demand OLTP queries to decoupled stream processing.
  2. Deduplication & Ordering: Ensuring out-of-order and duplicated payment events do not corrupt counters.
  3. Storage & Caching Strategy: Schema and data stores for serving real-time point lookups versus historical range queries.
  4. Reconciliation & Drift Recovery: Detecting and fixing discrepancies between real-time cached aggregates and the persistent ledger.

Hints

Hint 1

Decouple writes from reads by publishing payment lifecycle events to an append-only distributed log, processing updates asynchronously into pre-aggregated counters.

Hint 2

Use idempotent event processing with unique transaction version IDs and bounded window deduplication caches to prevent double-counting during retries.

Hint 3

Implement a Lambda or Kappa-style reconciliation loop where a periodic batch job audits fast-moving stream counters against the authoritative ledger and reconciles drift.

Follow-up questions

  • How would you support rolling sliding windows (e.g., 'past 15 minutes') without continuously aggregating raw events on every read?
  • If a stream processor crashes mid-window and restarts from a checkpoint, how do you prevent over-counting in external aggregate stores like Redis?
  • How does your architecture accommodate late-arriving settlement events that settle transactions authorized days earlier?