Data Warehouses, Data Lakes and Lakehouses: Where Analytics Lives

What you'll learn: By the end of this lesson you will be able to explain why analytical queries want a different storage layout from checkout, describe columnar storage and partition pruning as three warehouse products document them, tell a warehouse, a lake and a lakehouse apart, say what a table format such as Delta Lake or Apache Iceberg adds to a pile of files, choose between ETL and ELT, and lay out ShopStream's analytics path with a partitioned fact table.

A shop has a till and a back office. The till records each sale in seconds: one customer, a few items, a receipt. The back office answers different questions: which stores sold the most oat milk each week this year? Nobody walks the till through every receipt to answer that. The sales are copied nightly into ledgers sorted for questions, and the till keeps working.

A data warehouse is those ledgers. A data lake is the storeroom where every receipt, photo and delivery log is kept as it arrived, filed by date, with no particular question in mind. A lakehouse is the storeroom with a catalogue and a sign-out sheet, so people can ask the ledger kind of question without copying everything into the ledger room. The working definitions of OLTP and OLAP and a first ELT picture are in the Stage 3 data model lesson, and row versus column layouts are in storage engines. This lesson is the dedicated treatment of the three platforms.

Verified against the documentation of Google BigQuery, Snowflake, Amazon Redshift, Delta Lake, Apache Iceberg and Apache Parquet, an AWS page on data lakes and one on ETL and ELT, and the CIDR 2021 lakehouse paper, all read on 2026-11-08. The SQL on this page was not run here; these products change, so check the page for your version.

The core idea

Analytical questions scan many rows and read few columns, so the storage is arranged for scans, and the question that remains is where the structure is applied: before the data lands, or when it is read. A warehouse applies structure up front and is optimised for fast SQL. A lake keeps data as it arrived and applies structure later. A lakehouse tries to give lake storage the table behaviour of a warehouse.

Three kinds of example run through the lesson, each labelled:

• Minimal example: a table read by columns, and by partitions, to see what the engine skips.
• Realistic example: ShopStream's order facts, landed as files and loaded into a partitioned warehouse table.
• Failure example: a job that fails halfway through writing a day of files, and a partitioning choice that backfires.

Why columns

Redshift's documentation puts the case in one paragraph. In row-wise storage, "data blocks store values sequentially for each consecutive column making up the entire row", which suits OLTP, where "most transactions involve frequently reading and writing all of the values for entire records." With columnar storage "each data block stores values of a single column for multiple rows." Data warehouse queries "commonly read only a few columns for a very large number of rows", and the page's example is a 100-column table where a query using five columns "will only need to read about five percent of the data contained in the table." Because each block holds one type of data, a compression scheme can be chosen per column.

BigQuery says the same in its storage overview: it "stores table data in columnar format, meaning it stores each column separately", and an analytic query "can read that column data without reading every field of every row." Snowflake stores each micro-partition by column. The Parquet file format, common in lakes, allows "compression schemes to be specified on a per-column level."

Figure 1. The same query on two layouts. How to read this: the row layout must read all of every row. The column layout reads the store and cents blocks and skips the others, which is the saving the Redshift page quantifies for its 100-column example.

Partitions and pruning

Reading fewer columns is half the saving. The other half is reading fewer rows by skipping whole segments of the table, called pruning. The three warehouses do it differently:

BigQuery also offers clustering: "clustered tables in BigQuery sort data in storage blocks based on the values in user-defined clustered columns," and the engine uses "the filter expression and the block metadata to prune the blocks scanned." The page lists when to prefer clustering to partitioning: when you need more granularity than partitioning allows, when partitioning "results in a small amount of data per partition (approximately less than 10 GB)", or when you do not need strict cost estimates before the query runs. The two can be combined.

A BigQuery table partitioned by a date column, in the form the creating-partitioned-tables page shows (names are ShopStream's, illustrative):

CREATE TABLE shopstream_dw.fact_order_items (
  order_id   INT64,
  store_id   INT64,
  product_id INT64,
  order_date DATE,
  quantity   INT64,
  line_cents INT64
)
PARTITION BY order_date
OPTIONS (require_partition_filter = TRUE);

SELECT store_id, SUM(line_cents) AS revenue_cents
FROM shopstream_dw.fact_order_items
WHERE order_date BETWEEN '2026-11-02' AND '2026-11-08'
GROUP BY store_id;

require_partition_filter = TRUE makes the engine reject queries that do not filter on the partition column, so nobody scans every day by accident. With one partition per day, a seven-day filter touches 7 of 365 partitions in a year, about 1.9 percent of them (assuming even daily volume, illustrative).

Warehouse, lake, lakehouse

AWS's data-lake page defines a lake as "a centralized repository that allows you to store all your structured and unstructured data at any scale", and contrasts it with a warehouse: in a lake "the structure of the data or schema is not defined when data is captured", while in a warehouse "the data structure, and schema are defined in advance to optimize for fast SQL queries." That is the schema-on-read and schema-on-write distinction, in the vendor's words. The same page does not define a lakehouse; for that, the Delta Lake documentation says Delta Lake "enables building a Lakehouse architecture on top of data lakes", and the CIDR 2021 paper by Armbrust and colleagues argues that the warehouse architecture "will wither in the coming years" in favour of lakehouses based on "open direct-access data formats, such as Apache Parquet". That paper is an argument by its authors, not a neutral survey, so read it as one position.

What a table format adds to files

A directory of Parquet files has no transaction of its own. A table format puts a commit protocol and metadata on top. The two open formats' documentation describe this concretely.

Delta Lake lists ACID transactions ("serializable isolation levels ensure that readers never see inconsistent data"), time travel ("data versioning enables rollbacks, full historical audit trails"), schema enforcement and unified batch and streaming. Its protocol document states the goals as serializable ACID writes from several writers, snapshot isolation for reads, scalability to billions of partitions or files, and metadata stored alongside the data. Commit entries are newline-delimited JSON files in a _delta_log directory, and, at the version the document describes, tables are Parquet.

Apache Iceberg tracks "individual data files in a table instead of directories." "All changes to table state create a new metadata file and replace the old metadata with an atomic swap"; a snapshot "represents the state of a table at some time", and the swap "provides the basis for serializable isolation." The specification's goals include that "reads will be isolated from concurrent writes and always use a committed snapshot." Iceberg adds hidden partitioning: it "doesn't require user-maintained partition columns", partition values come from transforms of a column such as day(event_time), and "queries no longer depend on a table's physical layout." Partition evolution follows: the layout can change in an existing table, as "a metadata operation" that "does not eagerly rewrite files."

Figure 2. Why a reader never sees half a write. How to read this: new files are invisible until the metadata swap. A failure before the swap changes nothing a reader can see; a reader already running keeps its snapshot.

ETL and ELT

AWS's comparison page defines the two. ETL "uses a set of business rules to process data from several sources before centralized integration", transforming "on a secondary processing server." ELT "loads data as it is and transforms it at a later stage": it loads raw data "directly into the target data warehouse" or lake. ETL needs planning up front ("Analytics must be involved from the start to define target data types, structures, and relationships"). ELT keeps the raw copy, so a transformation that turns out wrong can be re-run. The cost of ELT is that raw data, including personal data, now sits in the analytics platform, so access rules and retention apply there too (privacy and compliance).

Realistic example: ShopStream order analytics

This is the realistic example, an educational scenario on ShopStream. It reuses the shared assumptions (500,000 orders per day, 5,000 stores) and adds one illustrative teaching assumption: about 10 order lines per order, so about 5,000,000 fact rows a day, or about 1.8 billion a year (5,000,000 x 365).

Figure 3. Raw files first, modelled tables second. How to read this: every source is exported into one landing area as it arrived. The modelled tables are built from that raw copy, so a wrong transformation can be re-run. Analysts and dashboards never touch the orders database.

The pipeline in words:

1. Land. Each night, orders, order items, stores and products are exported to object storage as Parquet files in date folders, one folder per day. The files are immutable.
2. Load. The warehouse loads the day's files into raw tables, unchanged (the ELT step).
3. Transform. SQL in the warehouse builds fact_order_items and the dimension tables (dim_store, dim_product, dim_date) of the star schema from the Stage 3 lesson.
4. Serve. Dashboards read the facts; data scientists can read the landing files directly.
5. Reconcile. A check compares the day's order total in the warehouse with the orders database and alerts on a difference.

Edge case: partitions that are too small

At the illustrative 5,000,000 rows a day and, say, 100 bytes a row (a teaching figure), a day is about 500 MB, far below the 10 GB the BigQuery page mentions as a point where clustering may fit better than partitioning. So the first design above, one partition per day, may create many small partitions for no benefit; the page's alternative is to cluster by order_date and store_id, or to partition by a coarser unit. Which is better depends on the engine's rules and your queries; the point is to compute partition size before choosing the key, as the page asks.

Failure example: the half-written day

The nightly export job fails after writing 3 of 8 files for a day. In a plain directory of files, nothing marks the day as incomplete, and a morning query over the folder returns a partial day: revenue looks low, and nobody knows why.

What breaks: reports quietly understate revenue. Detection: the reconciliation check in step 5 compares totals and fails; a file-count manifest per day would also show 3 of 8. Mitigation: re-run the export into a clean folder and re-load the day idempotently, replacing the day, not appending to it. Prevention: write files to a staging path and publish only on success, or use a table format, where files added by an uncommitted write are not part of any snapshot (Figure 2). Orchestration of retries and replays is in the stream-processing and scheduling lessons, which are planned and have no page yet.

Trade-offs

Figure 4. A first-pass platform choice. How to read this: begin with who reads the data and in what form. A lakehouse is the answer only when both kinds of reader need the same data and consistent tables.

In practice: moving ShopStream's reports off the replica

This is an educational scenario on ShopStream, and every number is illustrative.

This is a realistic scenario, not a case history.

The situation

Weekly reports ("revenue by store by week", "late deliveries by district") run on the read replica and compete with browsing. Analysts also ask for courier data that is not in the orders database.

Starting architecture

Reports are SQL queries on a replica. There is no landing area and no history of the courier pings beyond a week.

Options considered

The decision

Option B, with a landing area of raw files so that option C stays open. The files are immutable, partitioned by date, and the day's load replaces its previous load. Freshness target: yesterday's data by 7 a.m., a business choice the owner would set.

Rollout and verification

Load one month, compare weekly revenue per store between the warehouse and the replica, then move the dashboards. Verify bytes scanned for a seven-day query against the partitioning assumption, and check the daily reconciliation passes.

The 2 a.m. failure

The page reads: yesterday's revenue is low. Check the load job log, the file count for the day in the landing area, and the reconciliation result. The fix is to re-run the day into a clean load and replace it. The lasting change is to publish a day only after a manifest says all files are present.

Architect's notes

• Start from the questions and their freshness. Daily is cheap; every minute is a different system, covered by stream processing.
• Keep the raw copy unless privacy rules say otherwise; it is what lets you fix a transformation.
• Make loads replace, not append. A day should be replaceable by re-running it.
• Compute partition size before choosing a partition key. Small partitions hurt.
• Treat personal data as in scope in the lake and the warehouse.
• Check engine support before committing to a table format: documented features vary by engine and version.
• Do not let analytics read the transactional database. Copy it out.

Common mistakes

• Running heavy reports on the primary or a shared replica.
• Loading by appending without a way to replace a day, so retries double-count.
• Partitioning by a column queries never filter on.
• Many tiny partitions (see the edge case).
• A lake with no catalogue or naming rules, where nobody can tell which files are complete.
• Assuming a lakehouse removes the need for modelling.
• Transforming personal data nowhere, under ELT, and never expiring the raw copy.

Key takeaways

• Analytics reads few columns of many rows, so columnar storage and pruning matter; checkout reads whole rows, so row storage fits.
• BigQuery partitions and clusters, Snowflake micro-partitions automatically, and each prunes using metadata; know which one you control.
• A warehouse applies schema up front, a lake applies it at read time, and a lakehouse adds a table layer to lake files.
• Delta Lake and Apache Iceberg add atomic commits, snapshots and schema handling over files, so readers do not see half a write.
• ETL transforms before loading; ELT loads first and keeps the raw copy.
• Compute partition sizes before choosing a partition key.
• Reconcile the warehouse against the source every day.

Practice exercises

Exercise 1 (30 min). For a 40-column table where a report reads 4 columns, estimate the share of data read by a column store, using the Redshift page's reasoning, and state what the estimate ignores.

Exercise 2 (45 min). For ShopStream's fact_order_items, compute the daily size at 5,000,000 rows and 100 bytes a row (illustrative) and decide, with the BigQuery page's guidance, whether to partition by day, partition by month, or cluster.

Exercise 3 (1 hour). Design the nightly load so that re-running a day replaces it. Describe the staging path, the manifest check and the reconciliation query.

Exercise 4 (1 hour). Choose between a warehouse and a lakehouse for a team with one data scientist and ten dashboard users. State what would change your answer.

Related reading

• ShopStream Stage 3: choosing the right data store: OLTP versus OLAP and the first analytics path.
• Storage engines: row and column layouts, LSM and B-tree.
• CDN and object storage: where lake files live.
• Event streaming: feeding the landing area from a log.
• Time-series databases: the store behind courier pings and metrics.
• Compliance and privacy (GDPR): personal data in an analytics platform.

Official documentation

• Google BigQuery: storage overview, partitioned tables, creating partitioned tables, clustered tables.
• Snowflake: micro-partitions and data clustering; Amazon Redshift: columnar storage.
• Delta Lake: introduction and the transaction log protocol.
• Apache Iceberg: the table specification, partitioning and evolution; Apache Parquet: motivation.
• AWS: what is a data lake and ETL versus ELT; Armbrust et al., Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics (CIDR 2021).