# Data lakes and warehouses > A data lake keeps large amounts of data, often as files in object storage, for flexible processing and reuse. A data warehouse organizes data for analytical queries, reporting, and shared business metrics. In system design, they help separate historical analysis from transactional traffic, and they often work together. Source: https://sysround.com/concepts/data-lake Updated: 2026-11-05 ## How data lakes and warehouses works Start with the questions and freshness requirements. Checkout needs small, reliable transactions; a monthly revenue report needs scans and joins across many records. Keep the transactional database responsible for operational state, and send events or captured database changes to an analytics pipeline. A data lake commonly stores raw and curated datasets in object storage. For example, preserve incoming click events, then produce validated Parquet files partitioned by event date. A catalog tracks tables, schemas, and file locations so processing engines can discover and query them. A data warehouse exposes managed analytical tables with defined schemas and consistent metric definitions. Transform source data into tables such as click facts and campaign dimensions, then use SQL to calculate daily clicks by campaign. Columnar storage, partition pruning, and suitable clustering reduce the data each query reads. Connect ingestion, validation, transformation, and publication explicitly. Batch jobs or streaming consumers deduplicate events, handle schema changes, and publish complete table updates. Use replay-safe writes and track processing progress so a retry or backfill does not silently double the totals. These are not mutually exclusive categories. A lake can hold the replayable history while a warehouse serves curated reports; lakehouse designs add table metadata, transactions, and schema enforcement over object storage. Choose based on query latency, concurrency, governance, and operational effort rather than treating a lake as unstructured storage and a warehouse as the only place SQL can run. ## Worked example: Replayable web analytics with curated reporting Suppose a web analytics service receives 100 million events per day at an average encoded size of 500 bytes: about 50 GB per day before compression and storage overhead. Collectors append events to a durable log. Consumers preserve source events in object storage, while transformation jobs validate schemas and deduplicate by tenant and event ID. Curated warehouse tables support daily funnels and campaign reports with a 15-minute freshness target. Partition by event date and choose clustering based on common tenant and campaign filters. Events arriving within 48 hours trigger updates to affected results; older corrections use controlled backfills. If attribution logic changes, replay retained history and publish a new result version rather than mixing definitions in an existing report. ## Trade-offs - **Flexible retention versus data management.** A lake makes it practical to retain diverse source data, but useful datasets still need catalogs, ownership, validation, and access rules. - **Predictable reporting versus modeling effort.** Curated warehouse tables simplify repeatable analysis, but shared dimensions and metric definitions require maintenance as business rules change. - **Storage cost versus query cost.** Object storage can be economical for retained history, while repeated broad scans, transformations, and duplicated datasets can make compute expensive. - **Freshness versus complexity.** Streaming ingestion reduces reporting delay but adds continuous processing, late-event handling, and more complicated correction logic. - **Historical replay versus privacy obligations.** Retaining source history enables recomputation, but deletion and retention policies must cover raw files, curated tables, snapshots, and derived copies. - **File efficiency versus write latency.** Frequent tiny writes improve availability of new data but create small-file and metadata overhead that may require buffering and compaction. ## When to use it in a system design interview - The system needs historical scans, joins, funnels, or reports that would compete with transactional database traffic. - Multiple teams need consistent analytical datasets and shared definitions of metrics such as revenue or conversion rate. - You need to retain source events so corrected processing logic can rebuild historical results. - Different consumers need the same history for SQL analysis, batch processing, or model training. - The interview asks for both fresh dashboards and corrected historical totals, making separate processing and serving paths useful. ## How to bring it up in the interview - State the workload before naming the storage: retention period, daily ingestion volume, query patterns, concurrency, and acceptable data delay. - Draw the full path from source to report, including durable ingestion, raw retention, validation, curated tables, and the query engine; a storage bucket alone is not an analytics system. - Explain why you need a lake, a warehouse, or both. For a small reporting workload, exporting directly into a warehouse may be enough. - Define correctness under retries and replay: event identity, deduplication scope, late-arrival policy, and atomic publication of backfilled results are likely follow-up questions. - Be ready to defend physical layout and operations: partition pruning, avoiding high-cardinality partitions, compaction, tenant access controls, and monitoring ingestion lag and data completeness. ## Common mistakes - Describing a lake as having no schema or governance, rather than explaining where schemas are validated and enforced. - Assuming every warehouse query is fast without considering bytes scanned, joins, concurrency, and table layout. - Using a lake or warehouse as the default store for latency-sensitive transactional operations. - Treating a raw event count as a correct business metric without handling duplicate events, late arrivals, and changed definitions. - Adding both systems by default without explaining which workload or retention requirement justifies the extra pipeline and copies. ## Interview questions where it comes up - [Design Google Analytics](https://sysround.com/questions/design-google-analytics): Historical event retention and curated analytical tables support funnel queries, distinct-user analysis, and corrections for late data. - [Design an ad click aggregator](https://sysround.com/questions/design-ad-click-aggregator): Retained source events and curated historical totals enable replay and reconciliation between live counts and final billing results. - [Design an A/B testing platform](https://sysround.com/questions/design-ab-testing-platform): Joining exposure and outcome history into validated analytical datasets is central to reproducible experiment results. ## FAQ ### What is the difference between a data lake and a data warehouse? A data lake emphasizes retaining diverse data in storage that multiple processing engines can use. A data warehouse emphasizes managed analytical tables and efficient querying for reporting. Modern systems overlap, so compare their actual query, governance, and operational capabilities rather than relying only on the labels. ### Do I need both a data lake and a data warehouse? No. A warehouse alone may cover structured reporting, while a lake with a suitable query engine may cover batch analysis and exploration. Use both when replayable source retention and curated, concurrent reporting justify the extra processing and management. ### What is a lakehouse? A lakehouse combines object-storage-based datasets with table management features such as transactions, schema enforcement, and snapshots. It lets analytical engines work with governed tables without requiring every dataset to be copied into a separate warehouse. It still needs thoughtful layout, compaction, access controls, and workload management. ## Related concepts - [Columnar storage](https://sysround.com/concepts/columnar-storage) - [Object storage](https://sysround.com/concepts/object-storage) - [Change data capture](https://sysround.com/concepts/change-data-capture) - [Schema evolution](https://sysround.com/concepts/schema-evolution) --- SysRound (https://sysround.com) is an AI practice tool for system design interviews: a voice or typed AI interviewer, a shared whiteboard, then feedback on every skill and a model answer. Its feedback is AI-generated and can be wrong.