Analytical Data Modeling For Banking Metrics

What's being tested

Interviewers probe your ability to design backend systems that produce, serve, and maintain correct banking metrics (balances, deposit volume, delinquency rates) under real-world constraints: throughput, latency, storage cost, auditability, and evolution. They want to see system-design judgment (aggregation patterns, API contracts, caching), algorithmic choices for large-scale counting/aggregation, and tradeoff reasoning around accuracy vs. cost and operational complexity — all from a Software Engineer’s implementation lens.

Core knowledge

Signal sources: Upstream systems (transaction processing, ledgers) are treated as event sources only; design assumes ingest via streaming (`Kafka`) or batch snapshots (`S3`/`Parquet`), and you must handle out-of-order and duplicate events at the service boundary.
Aggregation patterns: Know pre-aggregation, materialized views, and on-demand aggregation tradeoffs: pre-agg reduces query latency but increases storage and freshness complexity; on-demand reduces storage but increases compute and latency.
Time-window semantics: Implement clear windowing — event-time vs processing-time, fixed vs tumbling vs sliding windows; choose watermarking and lateness handling to balance freshness and correctness guarantees.
Stateful streaming: For near-real-time metrics use stateful operators (e.g., keyed windows in `Spark Structured Streaming`), and design state snapshots and compaction strategies to bound memory for N keys.
Approximate algorithms: Use HyperLogLog for distinct client counts (relative error ≈ 1.04/√m), Count-Min Sketch for heavy hitters with additive error εN (width ≈ e/ε, depth ≈ ln(1/δ)), and Reservoir Sampling for uniform samples when full retention is impossible.
Idempotency & deduplication: APIs and aggregation components must accept idempotency keys or dedupe by (event_id, source); for retries avoid double-counting using persistent dedupe state or tombstoning.
Auditability & correctness: For regulatory metrics prefer deterministic, replayable pipelines with durable input logs, materialized audit tables, and reconciliation jobs (daily diff between raw ledger and metric).
Storage/layout: Choose columnar OLAP formats (`Parquet`/`ORC`) for historical analytics, normalized OLTP stores (`Postgres`) for low-cardinality lookups, and in-memory caches (`Redis`) for hot metric reads; consider partitioning by date + customer shard.
Query patterns & APIs: Design read APIs that support time-bounded queries, group-bys, and pagination; expose pre-aggregated rollups (daily, weekly, monthly) and provide a path for ad-hoc drill-downs.
Consistency vs latency tradeoff: Decide between near-real-time approximate metrics and strictly accurate daily metrics; document SLOs like freshness (e.g., 5m for dashboard, end-of-day reconciled accuracy).
Monitoring & SLIs: Track freshness, drift (reconciliation delta), processing lag, `p99` read latency, and error rates; alert on reconciliation drift exceeding thresholds.
Schema evolution & contracts: Use backward-compatible changes (additive fields, nullable), API versioning, and schema registry for event formats; plan migrations to avoid breaking historical aggregations.

Worked example — designing daily banking metrics service

Frame the problem: clarify which metrics (e.g., daily active accounts, total deposit inflows), expected QPS, freshness SLO, and regulatory audit needs. A strong answer structures the design around three pillars: ingest layer (consume canonical transactions, dedupe, write append-only journal), compute layer (batch nightly job for definitive aggregates; streaming path for near-real-time approximations), and serve layer (materialized views + read API + caching). Key tradeoff: produce a single source-of-truth nightly aggregate for auditability, and maintain a streaming approximate view for dashboards — reconcile nightly and surface reconciliation deltas on the API. Flag design choices for deduplication (persist seen-event IDs for 30 days) and schema evolution (use schema registry and tolerant parsers). Close with next steps: if more time, sketch data retention policy, test harness for replay, and performance budgeting (storage cost vs query latency).

A second angle — heavy-hitter detection and sampling

Apply the same system ideas to detecting high-value accounts or fraud signals: use a streaming aggregator keyed by account with a sliding window, maintain top-k heavy hitters via a Count-Min Sketch or a bounded heap depending on accuracy needs. For auditability, maintain exact counts for top-N candidates by promoting them from the sketch into a precise store. Constraints change the design: when memory is tight or cardinality is enormous, rely on sketches with periodic promotion; when regulators require exact counts for flagged accounts, ensure the promotion path and durable re-computation exist.

Common pitfalls

Pitfall: Thinking accuracy is free — many candidates propose always-accurate real-time aggregates without cost tradeoffs; instead articulate storage, compute, and complexity implications and offer approximate alternatives with reconciliation plans.

Pitfall: Conflating ingestion guarantees with application-level idempotency — don’t assume upstream exactly-once; design your service to dedupe and to handle out-of-order events explicitly.

Pitfall: Hiding operational needs — failing to propose monitoring, reconciliation jobs, or reprocessing/replay strategies will make otherwise-correct designs untenable in production.

Connections

This topic often pivots to data engineering (ingestion, partitioning, retention) and data science (metric definitions, statistical significance); expect follow-ups on schema design, replayability, and how model-serving systems consume these metrics.