System Design Analytics
Data Lakes & Warehouses
Google Search and ChatGPT both depend on more than the live product database. Click logs, training corpora, feature tables, and finance dashboards need a second path: land raw history cheaply, curate it, and serve trusted SQL without slowing the online request path.
This note is about choosing among a , a , and a . Then keeping the pipeline from becoming a swamp.
Analytics journey
Ingest events & dumps
CDC, logs, and batch extracts land raw.
Validate, dedupe, compact files.
Trusted dimensions for dashboards.
Who owns what, and can we replay?
What actually happens
Operational databases answer live product queries. Analytics systems answer slower, wider questions: retention, ranking features, fraud aggregates, board KPIs. A lake favors cheap storage and flexible ingestion. A warehouse favors curated schemas and interactive BI. A lakehouse tries to keep open files with stronger table contracts.
Pipelines are either dual-path (online store + analytics store) or replay-first (events are the source of truth and warehouses rebuild). Formats matter early: raw JSON may land first, but curated zones usually move to a . Without a and , listing and planning collapse.
Try it: compare lake vs warehouse query shape
The same SQL-looking question can scan thousands of small files or hit a sorted, columnar table. Stores differ in ingest flexibility, planner work, and dollars per terabyte scanned.
Filter one day of orders by region for a governed KPI tile.
- Planner steps
- —
- Estimated data read
- —
- Estimated latency
- —
- Fit
- —
Lake-shaped plan
list + scan- List partition prefixes and apply catalog ACLs.
- Prune files by coarse event date if present.
- Read column chunks from many Parquet objects.
- Apply schema-on-read casts for messy JSON fields.
Warehouse-shaped plan
sort + prune- Resolve governed table and role grants.
- Use zone maps or sort keys to skip blocks.
- Vectorized scan on narrow column bundles.
- Aggregate inside warehouse compute slots.
Choose a store and a query shape, then run the planner. Compare how file listing, column pruning, and curated sort keys change work.
Run the dashboard on both stores. The warehouse plan prunes columns and zones; the lake plan lists many files and applies schema guards before aggregating.
Run the exploratory scan. Lakes tolerate messy history; warehouses charge for wide reads unless you export to open files first.
Run the point lookup. Neither store is a OLTP database, but curated warehouse tables with sort keys beat raw JSON prefixes for selective filters.
How to choose lake vs warehouse
1. Who is the audience? Finance KPIs and governed dashboards lean warehouse. ML exploration and multi-format raw history lean lake or lakehouse.
2. How fresh must it be? Batch hours later is fine for many reports. Near-real-time features may need streaming into curated tables, not only nightly .
3. Do you need replay? Prefer landing raw (often ) when models and metrics will be redefined. Prefer strict when auditors only trust cleaned marts.
Simple rule of thumb
Keep the online database thin. Land analytics history in a lake/lakehouse with zones and a catalog, then publish trusted marts for BI. Pick engines after you know freshness SLAs, query audiences, and compliance boundaries, not before.
Lake vs warehouse vs lakehouse
A lake stores datasets, often as files in object storage, with flexible choices of format and processing engine. A warehouse provides managed analytical tables and query execution optimized for governed SQL workloads. A lakehouse adds table metadata, snapshots, and transaction rules over lake files so several engines can treat them as reliable tables. These are architectural patterns with overlapping products, not a rule that one is always newer or better.
Lakes favor cheap storage and flexible ingestion; warehouses favor curated dimensions and fast Business Intelligence (BI); lakehouses try to bridge both with transactional table layers on columnar files.
| Feature | Data lake | Data warehouse | Lakehouse |
|---|---|---|---|
| Schema | Often | Schema-on-write | Evolving schemas with table contracts |
| Typical storage | Object store files (many formats) | Vendor-managed columnar storage | Object store + table metadata layer |
| Query latency | Seconds to minutes (batch-heavy) | Sub-second to minutes for BI SQL | Depends on engine (batch + interactive) |
| Atomicity, Consistency, Isolation, Durability (ACID) | Weak unless layered | Strong inside warehouse | Yes with transactional table formats |
| Common fits | ML features, raw exploration | Finance dashboards, Key Performance Indicators (KPIs) | Unified analytics on open formats |
| Examples | Object storage + batch jobs | Managed analytical warehouse | Open table metadata + shared engines |
Modern lakehouse shape
A table is more than a folder of Parquet files. Its metadata identifies which files form the current snapshot and how their schemas and partitions should be interpreted. A writer creates new files, then commits a new metadata version; readers continue using a consistent snapshot until they choose another. Concurrent writers need conflict detection and retries. Old snapshots and unreferenced files need controlled cleanup so recovery remains possible without retaining everything forever. See Apache Iceberg reliability documentation for the precise contract.
The lake is not just a bucket. It needs zones, table formats, a catalog, lineage, access policies, and serving paths for Business Intelligence (BI) and ML.
Apps change data capture logs events
Batch or streaming ingest
Raw object storage
Validation dedupe schema checks
Curated lake tables
Warehouse marts BI
Feature store and ML
Ad hoc SQL engines
Catalog lineage access policies (governs raw, curated, marts)
Dual-path vs replay-first pipelines
Dual-path systems maintain batch and speed layers then merge results; replay-first systems bet on a durable log plus stream processors and replay. Teams often land pragmatic mixes rather than pure textbooks.
Dual-path architecture
Replay-first architecture
Replay the log to rebuild tables or fix bugs: one primary processing path.
vs
ETL shapes data before landing in the warehouse; ELT lands raw bytes then transforms with warehouse compute. Modern stacks often preserve raw zones for audit and reprocessing.
ETL (extract → transform → load)
Transform upstream of the warehouse. Strong control before bytes hit governed tables; slower to onboard novel sources.
ELT (extract → load → transform)
Load raw or lightly typed files, run transformations inside analytical compute. Flexible iteration; watch storage and compute bills.
Reference pipeline shape
Tooling varies: connectors, batch engines, stream engines, SQL modeling, and workflow orchestrators.
How to choose lake or warehouse
Frame the choice around freshness, trust, cost, and who queries the data. Most real designs use both: a lake for raw/curated history and a warehouse for governed serving.
Choose a lake first when...
- Data arrives in many formats and schemas are still changing.
- You need cheap retention for raw logs, ML features, or replay.
- Multiple engines should read the same open files.
- Query latency can be seconds to minutes for many workloads.
Choose a warehouse first when...
- Business users need reliable SQL dashboards and governed metrics.
- Schema, quality checks, and access policies must fail fast.
- Concurrency and predictable BI latency matter more than raw storage cost.
- The team wants fewer knobs and accepts managed analytical storage.
Avoid the "data swamp" answer
If you say "put everything in object storage," immediately add zones, table formats, ownership, retention, sensitive-data tagging, quality checks, and lineage. Those controls are what make a lake usable.
Analytics retention and growth math
A lake conversation should include daily ingest, file sizing, compaction, retention, and how fast raw data becomes curated analytical tables.
Apps and services
Raw daily partitions
Curate path
Compact small files
Curated column tables
Serving marts
Lifecycle path
Retention and archive
Catalog and lineage (describes raw and curated)
Million-user events
1M users x 200 events/day x 1 KB ≈ 200 GB/day raw. At 13 months retention, raw storage is roughly 78 TB before compression and replicas.
Small-file pressure
If agents flush every minute, millions of tiny files can overwhelm metadata. Compact into 128-512 MB analytical files per partition.
Curated growth
Columnar compression may shrink event data 3-10x, but derived tables and feature snapshots can add back 30-100% depending on use cases.
Lifecycle policy
Keep recent raw data hot for incident replay, move older raw partitions to cold tiers, and retain curated aggregates longer when audits need them.
Interview sizing shortcut
retained_bytes = daily_ingest x retention_days x compression_factor x derivative_factor. Call out metadata and compaction separately.
Analytics retention worksheet
raw_retained = daily_raw x raw_days x compression_factor
curated_retained = daily_curated x curated_days
aggregate_retained = snapshot_size x snapshot_count
metadata_retained = files x metadata_per_file + table_versions x metadata_per_version
200 GB/day retention example
Raw events at 200 GB/day for 13 months are about 78 TB before compression. With 5x compression, raw retained bytes are about 15.6 TB. If curated data is 40 GB/day for 13 months, add about 15.6 TB. If monthly aggregate snapshots are 500 GB and kept seven years, add 42 TB.
Small files, metadata, and
A lake can fail before reading data if it has too many tiny files. Query planning must list files, read metadata, and build scan tasks before useful work starts.
File explosion
500 services x 60 flushes/hour x 24 hours = 720K files/day before partitions and retries.
Metadata pressure
At 2 KB planning metadata per file, 720K files/day adds about 1.4 GB/day of metadata to inspect and govern.
Compaction cost
Compaction rewrites bytes and consumes compute, but it reduces future query planning time and object listing pressure.
Table metadata includes
Partitions, manifests, file statistics, schema versions, table versions, lineage records, sensitivity tags, and access policies. Too many files slow planning before a query reads even one byte of useful data.
Replay, late data, and backfills
Event time records when an action happened; processing time records when the pipeline handled it. A mobile device can upload yesterday’s actions today, so a daily metric cannot assume arrival order equals business time. Define the accepted lateness window and how later corrections update aggregates. Use stable event identifiers to prevent replay from double-counting, and isolate backfills so they do not overwrite newer transformations or overload the live pipeline.
Keep raw immutable partitions so bad code can be fixed by replay. Backfill into a shadow table, validate it, then promote it atomically.
Watermark
Decide how late events may arrive before a window is considered mostly complete.
Correction window
Allow bounded reprocessing for late or corrected events without rewriting all history.
Shadow backfill
Write fixed output to a new table version while current dashboards keep reading old data.
Promote safely
Compare row counts and aggregates, then atomically switch serving tables or catalog pointers.
Governance link
Retention, ownership, sensitivity tags, legal hold, and deletion workflows must be tied to the catalog. Otherwise stale raw data and derived tables can survive policy deletes.
Processing engines
Batch engines excel at large scans and micro-batches; stream engines target lower-latency event-at-a-time semantics with checkpointed exactly-once ambitions.
Batch engines
Dataframe-style APIs, SQL, machine-learning libraries, and connectors to lake formats. Common for nightly rollups and heavy ETL on columnar tables.
Stream engines
Stateful streaming windows, event pattern detection, and tighter latency targets than classic micro-batch processing for many teams.
Storage formats
Columnar formats group values by column, enabling compression and reading only the columns a query needs. Partition pruning skips whole files or partitions using predicates, while file statistics can skip additional row groups. Too many tiny files add listing, planning, and open-request overhead; overly large files can reduce parallelism or make rewrites expensive. Choose file size and partitioning from the actual query and ingestion pattern, then compact small arrivals into practical units.
Columnar formats skip untouched columns during scans; row formats suit streaming writes. Lakehouse table formats add transactions and time travel atop columnar bases.
| Format | Layout | Best for |
|---|---|---|
| Columnar files | Columnar | Analytics scans, compression |
| Row binary files | Row-oriented | Streaming ingestion schemas |
| Text row files | Row text | Interchange, not warehouse finals |
| Transactional table files | Columnar + metadata | ACID, evolution, incremental reads |
Wide analytical queries reading few columns see large I/O wins from column pruning versus raw text dumps.
vs
Schema-on-write keeps dashboards trustworthy because rejects land early. Schema-on-read accelerates landing messy telemetry but needs catalogs and tests or you build a data swamp.
Schema-on-write
Enforce types and constraints before serving BI. Faster trusted queries; slower change pipelines.
Schema-on-read
Store now, interpret later. Pair with validation jobs, contracts, and lineage to stay governable.
Catalog and governance
A catalog should explain meaning as well as location: who owns a dataset, which units a field uses, when it was last refreshed, and which source transformations produced it. Validate schemas and important invariants before promoting raw data into trusted tables. For example, a sudden drop in purchase events should not silently become a business conclusion if the ingestion job failed. Track data freshness and completeness alongside compute success.
A catalog answers who owns a dataset, which columns contain Personally Identifiable Information (PII), and how freshness is guaranteed. Without it, reuse and compliance audits grind to a halt.
Catalogs typically cover:
- Discovery: search tables, columns, and docs.
- Lineage: upstream jobs and transforms.
- Access: row/column policies, encryption context.
- Quality: freshness monitors and anomaly checks.
Examples include managed catalogs, open-source catalogs, and internal metadata portals.
Illustrative mega-scale pipeline
Large consumer platforms combine log-based ingestion, object storage landing zones, batch processing, stream processing, and specialized analytical serving for dashboards. Numbers drift; patterns recur.
Ingestion
High-volume event buses plus CDC from online transaction processing (OLTP) into landing buckets or streams.
Storage
Columnar files on object storage; optional incremental table formats when you need upserts.
Processing
Batch or streaming clusters for transforms.
Serving
Ad hoc lake SQL engines, sub-second slice engines, and warehouse exports when needed.
Degraded modes and lake drills
Lakes fail operationally through small files, bad partitions, broken catalogs, or pipeline lag, not only through missing storage capacity.
Ingest pipeline down
Buffer at the source or durable queue; keep serving last curated tables. Do not silently drop events to protect dashboards.
Catalog / metastore outage
Engines cannot discover tables. Fail closed on new jobs; keep a warm snapshot of critical table locations for emergency reads if policy allows.
Small-file explosion
Pause ultra-frequent flushers, compact aggressively, and reject queries that would list millions of tiny objects.
Late data / bad partition
Isolate late arrivals into correction jobs; avoid rewriting hot partitions under concurrent readers without snapshot isolation.
Test and drill notes
- Replay one day of events into a shadow lake zone; compare curated row counts and key aggregates.
- Compaction drill: measure metadata list time before/after merging to 128–512 MB files.
- Rule of thumb: quote daily ingest × retention × compression, then name ownership, zones, and quality checks that prevent a swamp.
Takeaway
Land raw data in cheap object storage with immutable partitions, layer curated tables with a catalog, and choose batch versus stream processing from latency requirements. Prefer open table formats when multiple engines must share the same files safely.
Primary sources
- Apache Iceberg Docs: table format for lakehouse reliability.
- Delta Lake Documentation: ACID on object storage.
- AWS: Building Data Lakes: zones, governance, lifecycle.
Practice and further reading
Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.
Focus for this lesson: Operate lakehouse data with table-format transactions, schema/partition evolution, compaction, lineage, quality, governance, and late corrections.
Explain the decision
What does a table format add beyond Parquet files in object storage?
Read the reasoning
Atomic metadata snapshots, consistent commits, schema/partition evolution, time travel, manifests, and safe concurrent readers/writers.
Apply it to a production scenario
Late events correct last week’s revenue. How do you update without corrupting readers?
Read one approach
Write an idempotent correction/upsert into a new snapshot, validate quality and lineage, then atomically publish it while retaining rollback history.
Work through the numbers
One billion 1 KiB events/day compact 5:1 into columnar storage. Estimate daily compressed data.
Read the worked calculation
About 190.7 GiB/day before table metadata, indexes, replicas, and temporary compaction space.
Reason through a failure
What must a compaction job preserve?
Read the recovery reasoning
Readers see either the old or new valid snapshot, never a partial file set; orphan files are cleaned only after no retained snapshot references them.