Design a Large-Scale Financial Market Data Lake

Problem statement

Design a large-scale financial market data lake: a central repository for all intraday trades, quotes, and order book snapshots arriving from multiple exchanges and alternative venues. The lake must support multi-year compliance queries, bit-faithful replay for backtesting, and large-scale analytics through HPC or Spark integration. The attached brief pins four functional requirements — real-time stream ingestion per exchange, partitioning by date/time and symbol, multi-year compliance queries, and HPC/Spark integration — and four non-functional requirements — petabyte scalability, high-throughput ingestion with minimal lag, retention with BFS-style historical replay, and security for sensitive trading information.

This is not a generic data lake. Financial tick data has properties that break naive lake designs. First, volume is dominated by quotes, not trades: consolidated quote-to-trade ratios of 20:1 to 100:1 are normal in modern electronic markets, and order book updates add another order of magnitude. Second, correctness is regulatory: a compliance examiner reconstructing what a trader could have seen at 10:31:07.482 must get the same answer the market saw, not a later-restated one. Third, replay is a product feature: quant backtesting requires point-in-time correctness, meaning corrections arriving at T+1 must not leak into a simulation of day T. Fourth, arrival patterns are extreme: 70%+ of volume concentrates inside cash market hours, with opening and closing auctions producing multi-x spikes over already-high session rates.

Why simpler solutions fail

A relational warehouse cannot absorb a million messages per second of quote updates cost-effectively. A raw object dump without schema enforcement becomes an unusable swamp within a year of exchange protocol revisions. A kdb+ tick database gives superb query latency but historically struggled with petabyte-tier retention, broad analyst concurrency, and the governance a regulated institution needs. The winning architecture is a streaming lakehouse: raw capture preserved as ground truth, a normalized canonical event stream, columnar lake tables with transactional metadata and time-travel, and separate query engines for interactive SQL, Spark/HPC batch, and replay.

The four architectural planes

1. Capture plane: lossless feed capture per venue — binary protocol decoding, sequence tracking, gap detection and retransmission, dual timestamping.
2. Lake plane: immutable canonical events in columnar format with table-format metadata, partitioned by time, venue, and symbol; compacted, tiered, and retained under policy.
3. Serving plane: SQL gateways, Spark/HPC bridges, replay materialization, real-time derived feeds, and reference/symbology services.
4. Governance plane: catalog, lineage, schema registry, data quality, retention and legal-hold enforcement, access audit, compliance evidence.

A strong answer keeps these planes separate. Capture must never block on serving. Serving must never mutate captured ground truth. Governance must be able to freeze, hold, and purge any dataset without breaking the others.

Public operating baseline versus design assumptions

Public industry signals establish the scale class. Consolidated US equity tape processors (CTA/UTP) are commonly described at roughly ten billion messages per day with peak rates above one million messages per second; the options consolidated feed (OPRA) has publicly reported record volume days through 2023-2024; and the SEC's public materials describe the Consolidated Audit Trail (CAT) as designed for on the order of one hundred billion events per day. These are cited context figures, not requirements for our fictional system. For capacity planning this answer explicitly assumes a mature multi-asset lake with 25 venues, 10 billion messages per day, 1.2M messages/sec session peak, 2.5M messages/sec design burst, 500 concurrent analysts, and a 7-year retention horizon. Every uncited number in this answer is a stated design assumption, target, or budget — never a claim about any company's private architecture.