Data lakes, warehouses and lakehouses

Where analytical data lives: data warehouses versus data lakes, columnar files like Parquet, table formats such as Iceberg and Delta Lake, partitioning and file sizing, ingestion with ELT, medallion layers, query engines, governance and cost.

Reading is half of it. See this used in a real interview: walk through Design an Ad Click Aggregator →

Every large product keeps an analytical copy of its data: events, logs, database snapshots, model features. Analysts query it, dashboards read it, machine learning trains on it, and billing jobs reconcile from it. Interviews for data-heavy products ("design an analytics pipeline", "how do you compute daily reports?") expect you to know where that data lives and how it is organised. The vocabulary has shifted from warehouses to lakes to lakehouses; this guide explains each.

Warehouse versus lake

Examples: Snowflake, BigQuery and Redshift are warehouses; a lake is a bucket of Parquet files plus engines.

Columnar files

Analytical data is stored in columnar formats such as Parquet or ORC:

• Values of one column are stored together, so a query reading 3 of 200 columns reads only those.
• Similar values compress extremely well (often 5 to 10 times).
• Files carry statistics (min and max per column per row group), so engines can skip blocks that cannot match a filter.

See OLTP versus OLAP.

Table formats: the lakehouse

A folder of Parquet files is not a table: concurrent writers can corrupt it, readers may see half-written data, and there are no updates or deletes. Open table formats (Apache Iceberg, Delta Lake, Apache Hudi) add a metadata layer over the files:

• ACID transactions: commits are atomic snapshots; readers see a consistent version.
• Updates, deletes and merges (needed for CDC data and privacy deletions).
• Schema evolution and partition evolution without rewriting data.
• Time travel: query the table as of an earlier snapshot, useful for debugging and reproducible training sets.

A lakehouse is object storage plus a table format plus SQL engines: warehouse-like features on cheap, open storage. Warehouses increasingly read these formats too.

Partitioning and file sizes

• Partition large tables by date (and maybe another low-cardinality column), so queries for a day read only that day's files.
• Avoid too many small files: streaming ingestion creates thousands of tiny files that slow every query. Run compaction jobs that rewrite them into files of a few hundred megabytes.
• Sort or cluster data within partitions by frequently filtered columns to make block skipping effective.

Getting data in

• Events from Kafka written by a streaming job into the lake every few minutes. See how Kafka works.
• Database tables replicated with change data capture, applied as merges into lake tables.
• Third-party data loaded by scheduled jobs.

Modern pipelines are ELT: load raw data first, then transform inside the lake or warehouse with SQL (tools like dbt), so raw data can be reprocessed when logic changes.

Layers

A common organisation (sometimes called medallion):

1. Raw (bronze): data as received, append-only, kept for reprocessing.
2. Cleaned (silver): deduplicated, typed, joined, with bad records quarantined.
3. Curated (gold): business-level aggregates and models for dashboards and reports.

Each layer is produced by scheduled or streaming jobs with data quality checks. See batch and stream processing and workflow orchestration.

Query engines

Spark for large transformations and machine learning, Trino or Presto for interactive SQL across sources, warehouse engines for dashboards, and real-time OLAP stores (ClickHouse, Druid, Pinot) for sub-second queries on fresh data. Interactive dashboards on huge data usually read pre-aggregated gold tables or an OLAP store, not raw events.

Governance and cost

• A catalog of tables with owners, schemas, lineage and access control.
• Column-level controls and masking for personal data; deletion support for privacy requests. See privacy and data deletion.
• Storage is cheap, but scanning is not: partition pruning, column selection and pre-aggregation control query costs. See cost-aware system design.

In the interview

For pipelines like Design an Ad Click Aggregator or Design a Monitoring System: raw events land in the lake as Parquet in an Iceberg table partitioned by hour, compacted regularly; batch jobs build cleaned and aggregated tables used for billing reconciliation and reports; a real-time OLAP store serves live dashboards.

Checklist

• Columnar formats with statistics for skipping.
• A table format for transactions, deletes, schema evolution and time travel.
• Date partitioning, compaction of small files, clustering.
• ELT from Kafka and CDC; raw data kept for reprocessing.
• Raw, cleaned and curated layers with quality checks.
• Catalog, access control, privacy deletion and query cost control.