A Flipkart-style analytics team runs the same query every morning: total revenue by city for the last 7 days. On one warehouse table, it scans 40GB, finishes in four seconds, and costs a few rupees. On a different table holding the exact same data, the same query scans 2TB, takes six minutes, and the monthly bill for this one report alone crosses six figures. Nothing about the SQL changed. Nothing about the data changed. What changed is how the table was physically laid out before the query ever ran - whether the engine could prove that large sections of data could not possibly match the filter, or whether it had to read everything to find out. * A **data warehouse** is a database optimised for reading and analysing large volumes of historical data - built for the query pattern "scan millions of rows, aggregate them," not "look up and update one row." * This matters because the physical design decisions in this module are the difference between a query that costs rupees and a query that costs lakhs, on identical data. * You reach for these decisions any time you are loading a new table, seeing a query scan far more data than the question requires, or reviewing a bill that grew for no obvious reason. > 📌 **Remember:** In a data warehouse, "how much work did the engine prove was > unnecessary" usually matters more than "how clever is the SQL." A well-designed > table makes a simple query fast. A poorly designed one makes even clever SQL slow. > **Note:** Warehouse products differ a lot in their internals - Snowflake, > BigQuery, Redshift, and Databricks SQL each implement these ideas differently. This > module teaches the underlying principles that hold across all of them. Exact > syntax and vendor-specific behaviour are covered in the Modern Warehousing and > Lakehouse module later in this roadmap. ---
| | Operational Database | Data Lake | Data Warehouse | |:---|:---|:---|:---| | Optimised for | Fast small transactional writes | Cheap, flexible raw storage of any format | Fast large-scale reads and aggregation | | Schema | Required upfront | Schema-on-read, structure applied later | Required upfront, structured for querying | | Typical user | The application itself | Data engineers, data scientists | Analysts, BI tools, dashboards | A **data lake** stores data cheaply and flexibly in its original format without requiring a schema before you write to it. A warehouse takes data that has already been cleaned and modeled - often using the star schema patterns from the Data Modeling module - and stores it in a form built specifically for fast querying. **Concept check:** Why might a company keep both a data lake and a data warehouse, instead of just picking one? ---
Older warehouse architectures bundled storage and compute together on the same fixed hardware - if you needed more query power, you also had to buy more disk, whether you needed it or not. Most modern cloud warehouses separate the two: Traditional warehouse: Modern cloud warehouse: +-------------------+ +----------------+ | storage + compute | | object storage | | (bundled) | +----------------+ +-------------------+ | +----------------+ | compute cluster| | (independent) | +----------------+ Data lives in cheap, durable object storage. Compute is a separate resource that reads from that storage on demand and can scale up, scale down, or pause entirely without touching the data itself. This is why modern warehouses can run several independent workloads against the same stored data, pause compute to save cost while keeping data intact, and scale query power up or down without ever copying data. > **Note:** This separation is also what makes "pay per query" tools like Athena > possible - the compute that answers your query is provisioned just for that query, > against data that was never tied to any particular compute cluster in the first > place. ---
A traditional OLTP database stores data **row-oriented** - every column of one row together on disk, because OLTP workloads mostly fetch or update one whole row at a time. A warehouse stores data **column-oriented** instead - values from the same column organised so they can be read and compressed together, because analytical workloads mostly touch a handful of columns across millions of rows. Row storage (OLTP): Columnar storage (warehouse): row1: [cust_id, city, amount] col_cust_id: [1, 2, 3, ...] row2: [cust_id, city, amount] col_city: [Pune, Delhi, ...] row3: [cust_id, city, amount] col_amount: [500, 1200, ...] If a query only needs `SUM(amount_rs)`, a columnar engine only has to read the `amount_rs` column. In a typical full scan, row-oriented storage often reads substantially more irrelevant data than a query actually needs, because every column of a row is stored together - though indexes and other techniques can reduce this in specific cases. Columnar storage removes the problem at the root by organising data so a query only ever has to touch the columns it actually references. Values in a column also tend to have lower structural variety than a complete row - a `city` column repeats a small set of values, a `status` column even fewer - which lets columnar formats apply efficient encodings and compression that a row full of mixed, unrelated types cannot use nearly as well. Exactly how much compression you get depends on the specific encoding and how the data is distributed, but the general pattern holds broadly across columnar formats. > **Note:** A wide table is not automatically slow in a columnar warehouse, because > unused columns are simply never read for a query that does not reference them. The > real cost of unnecessary columns is storage, schema complexity, and the risk of an > accidental `SELECT *` pulling in far more than a query needs - not query speed by > itself. > 💡 **Practice:** You have a `fct_orders` table with 60 columns, but the daily > revenue dashboard only ever queries `order_date`, `city`, and `amount_rs`. In a > row-oriented database, does adding those extra 57 columns slow this dashboard > query down? In a columnar warehouse, does it? Explain the difference. ---
For large analytical workloads, warehouse engines commonly use **Massively Parallel Processing (MPP)** - splitting a scan, join, or aggregation across many compute workers at once and combining the partial results. Small queries, serverless execution stages, and certain configurations may not meaningfully spread across many workers, but for the large scans this module is concerned with, parallel execution across nodes is the default assumption. Large query | v Warehouse distributes work Node 1 -> scans part of the data Node 2 -> scans part of the data Node 3 -> scans part of the data Node 4 -> scans part of the data | v Partial results combined -> final answer This is a large part of why a warehouse can aggregate billions of rows in seconds - the scan and the aggregation both happen in parallel, not sequentially on one machine. It is also why warehouse sizing matters: a query against a larger compute allocation has more workers to split the work across. ### Data movement during joins MPP works cleanly when each worker can process its slice of data independently. Joins complicate this: if two large tables are distributed across workers differently, matching rows may sit on different workers, and the engine has to move data between them before it can complete the join. > 📌 **Engineering Decision:** Large joins become expensive specifically when > matching rows have to move between compute workers before they can be joined, not > just because the tables are large. Different warehouses manage this differently - > some let you declare a distribution or clustering key so commonly-joined tables are > co-located, others handle this automatically. The underlying principle to carry > forward is one you will meet again in the Spark module: moving data between > workers to complete a join is expensive, and designing tables so related data sits > close together reduces how much movement a join requires. ### Data skew (Good to Know) If work is distributed unevenly across workers - say, 80% of orders come from Delhi and the engine splits work by city - the worker handling Delhi does far more work than the others, and the whole query waits on that one overloaded worker to finish. This is called **skew**, worth recognising when a query's slowness does not match how much total data it touches. **Concept check:** Two workers are each assigned an equal number of rows, but one finishes in 2 seconds and the other takes 40 seconds. What might explain that, beyond simply "one worker has more data"? ---
This is the idea that connects everything else in this module. Warehouses and modern file formats maintain **metadata and statistics** about the data they hold - typically, at minimum, the minimum and maximum value of key columns within each file, partition, or storage block. File / partition metadata +----------------------------+ | min(order_date) = 2024-01-01| | max(order_date) = 2024-03-01| | row_count = 480,000 | +----------------------------+ When a query filters `WHERE order_date >= '2026-08-09'`, the engine checks this metadata before reading any actual data. A file whose `max(order_date)` is `2024-03-01` cannot possibly contain a matching row, so it is skipped entirely. A file whose range overlaps the filter has to actually be read. This single mechanism shows up under several different names depending on the system and the granularity involved: * **Partition pruning** - skipping entire partitions (directories, or a managed warehouse's internal partition structures) based on their statistics. * **Row-group or block skipping** - the same idea applied at a finer grain, inside a single file, on the storage blocks Parquet and similar formats organise data into. * **Predicate pushdown** - applying the query's filter as close to the data source as possible, at scan time, rather than reading everything and filtering afterward in a later stage of the query. Pruning and skipping are effectively predicate pushdown applied at the partition and block levels. > 📌 **Remember:** Partitioning divides a table into logical or physical segments > based on a key, so the engine can eliminate segments that cannot satisfy a filter. > In file-based systems those boundaries often correspond directly to directories or > files; in managed warehouses the same idea is usually implemented as internal > storage partitions you never see directly. Either way, the engine is doing the > same thing: checking statistics first, reading data only where the statistics say > it might be needed. **Concept check:** A table is fully columnar and well compressed, but its `order_date` column was written in random order across files instead of roughly sorted by date. Would partition pruning based on min/max statistics still work well here? Why not? ---
A Flipkart-style analytics team runs the same query every morning: total revenue by city for the last 7 days. On one war...
Operational Database Data Lake Data Warehouse Optimised for Fast small transactional writes Cheap, flexible raw storage ...
Older warehouse architectures bundled storage and compute together on the same fixed hardware - if you needed more query...
A traditional OLTP database stores data row-oriented - every column of one row together on disk, because OLTP workloads ...
For large analytical workloads, warehouse engines commonly use Massively Parallel Processing (MPP) - splitting a scan, j...
This is the idea that connects everything else in this module. Warehouses and modern file formats maintain metadata and ...
Choosing a partition key > 📌 Engineering Decision: Choose the partition key based on how the table is > actually filter...
Bulk load vs incremental load A bulk load loads an entire dataset at once - typical for an initial historical backfill o...
Warehouse performance and cost usually come down to at least two important dimensions: how much data the query reads, an...
You do not need to master every option here - this is about recognising the shape of the decision, revisited with concre...
Prerequisites: [DuckDB](https://duckdb.org) installed locally (pip install duckdb --break-system-packages). This lab gen...
Concept What it does When it matters most Storage/compute separation Lets compute scale independently of stored data Mod...
Assuming that no explicit partitioning always means a full table scan happens because a beginner reads "partitioning hel...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.