Data Warehousing: Building Analytics Powerhouses

Every time a passenger books a ticket on IRCTC, a transaction fires against a production database: a seat gets locked, a fare gets calculated, a PNR gets written, a row gets committed. That database is tuned for exactly one job — take a booking request and finish it in milliseconds, correctly, even when a hundred thousand people are hitting "Book Now" in the same second during Tatkal hours. Now suppose a revenue analyst at Indian Railways wants to know: "What was the average fare paid per kilometre travelled, broken down by class and by railway zone, for every month of the last five years?" That query has to read hundreds of millions of historical rows, group them several different ways, and return an answer in seconds, not lock a row, not touch today's live bookings, and not slow down the Tatkal rush happening at that exact moment. Running that query against the booking system directly would be like asking a sprinter to also run a marathon on the same track, at the same time, in the same lane. This is the problem data warehousing exists to solve: separating the system that records what happened from the system that explains what it means.

Why the production database cannot also be the analytics database

You already know how to design a database for correctness: normalize it. A well-normalized OLTP (Online Transaction Processing) schema stores each fact exactly once, uses foreign keys to avoid duplication, and enforces integrity constraints so that updating a passenger's phone number touches one row, not thousands of copies scattered across the table. This design is optimized for writes and for point lookups: insert one booking, fetch one PNR, update one seat status. The row-store engines under most OLTP databases store all the columns of a row physically next to each other on disk, because a typical transaction needs every column of that one row at once. Analytical work has the opposite shape. An OLAP (Online Analytical Processing) query rarely wants one row; it wants an aggregate over millions of rows, and usually only two or three columns out of the twenty a row might have — say, fare_amount and booking_date, ignoring passenger name, seat number, and berth preference entirely. Running that kind of query against a normalized, row-oriented, write-tuned OLTP schema means joining across many small normalized tables and scanning full rows just to throw most of each row away. It also means holding locks and consuming I/O bandwidth that the live booking system needs for its own transactions. A data warehouse exists to give analytical queries their own copy of the data, modeled and stored specifically for scanning and aggregating, disconnected from the operational system that must stay fast and available for transactions.

Bill Inmon's classic definition captures this: a data warehouse is subject-oriented (organized around business concepts like "bookings" or "revenue," not around the tables an application happens to use), integrated (data from multiple source systems reconciled into one consistent model), time-variant (it keeps history, not just the current state), and non-volatile (once loaded, warehouse data is not overwritten by daily operations — it is appended to and corrected through controlled processes, not by an application's ordinary write path). Every one of those four properties is a direct answer to something an OLTP system deliberately does not do, because doing it would slow transactions down.

The star schema: modeling for scans, not lookups

The dominant modeling pattern inside a data warehouse is the star schema: one large fact table at the center, holding numeric measures and foreign keys, surrounded by smaller dimension tables that hold the descriptive attributes you group and filter by. For IRCTC's booking analytics, the fact table might be fact_booking, and its dimensions would be dim_date, dim_train, dim_class, and dim_station. The single most important design decision in building a fact table is its grain: the precise definition of what one row represents. Get this wrong and every aggregate built on top of it is wrong, even though the SQL runs without error. Suppose the grain of fact_booking is declared as "one row per passenger, per travel class, within a single booking." A PNR booking three passengers in AC3 and two passengers in Sleeper on the same train produces two fact rows, one per class, each carrying its own passenger_count and fare_amount for that class. If instead the warehouse stored one row per PNR (a coarser grain), a booking that spans two classes could not be represented without either losing the per-class fare split or duplicating the row and double-counting revenue. The grain must be fixed and documented before a single dimension is designed, because it determines what a SUM() or COUNT() over the fact table is even allowed to mean.

Here is a minimal version of that schema, with the fact table at the grain "one row per passenger-class line item":

CREATE TABLE dim_date (
  date_id      INT PRIMARY KEY,
  full_date    DATE,
  month        INT,
  quarter      INT,
  fiscal_year  INT
);

CREATE TABLE dim_train (
  train_id     INT PRIMARY KEY,
  train_name   VARCHAR(60),
  zone         VARCHAR(20)
);

CREATE TABLE dim_class (
  class_id     INT PRIMARY KEY,
  class_name   VARCHAR(20),
  class_tier   VARCHAR(10)   -- 'AC' or 'Non-AC'
);

CREATE TABLE dim_station (
  station_key    INT PRIMARY KEY,   -- surrogate key
  station_code   VARCHAR(10),
  station_name   VARCHAR(60),
  effective_from DATE,
  effective_to   DATE,
  is_current     CHAR(1)
);

CREATE TABLE fact_booking (
  booking_id       BIGINT PRIMARY KEY,
  date_id          INT REFERENCES dim_date(date_id),
  train_id         INT REFERENCES dim_train(train_id),
  class_id         INT REFERENCES dim_class(class_id),
  station_key      INT REFERENCES dim_station(station_key),
  passenger_count  INT,
  fare_amount      DECIMAL(10,2)
);

Now trace a real query against a small, hand-built set of six fact rows, so every number is checkable by hand rather than taken on faith:

-- date_id=1: 2024-01-05, date_id=2: 2024-02-10
-- train_id=100: Rajdhani Express, train_id=200: Shatabdi Express
-- class_id=1: AC1, class_id=2: AC3, class_id=3: Sleeper

INSERT INTO dim_date (date_id, full_date, month, quarter, fiscal_year) VALUES
 (1, '2026-04-05', 4, 1, 2026),
 (2, '2026-04-06', 4, 1, 2026);

INSERT INTO dim_train (train_id, train_name, zone) VALUES
 (100, 'Rajdhani Express', 'NR'),
 (200, 'Shatabdi Express', 'NR');

INSERT INTO dim_class (class_id, class_name, class_tier) VALUES
 (1, 'AC1', 'AC'),
 (2, 'AC3', 'AC'),
 (3, 'Sleeper', 'Non-AC');

INSERT INTO fact_booking (booking_id, date_id, train_id, class_id, passenger_count, fare_amount) VALUES
 (1, 1, 100, 1, 2, 4800.00),
 (2, 1, 100, 2, 3, 5400.00),
 (3, 1, 200, 3, 5, 3500.00),
 (4, 2, 100, 1, 1, 2400.00),
 (5, 2, 200, 2, 4, 6800.00),
 (6, 2, 200, 3, 2, 1400.00);

SELECT c.class_name,
       SUM(f.fare_amount)      AS revenue,
       SUM(f.passenger_count)  AS passengers
FROM fact_booking f
JOIN dim_class c ON f.class_id = c.class_id
GROUP BY c.class_name;

Work it row by row. Rows 1 and 4 are AC1: fares 4800 + 2400 = 7200, passengers 2 + 1 = 3. Rows 2 and 5 are AC3: fares 5400 + 6800 = 12200, passengers 3 + 4 = 7. Rows 3 and 6 are Sleeper: fares 3500 + 1400 = 4900, passengers 5 + 2 = 7. As a check, sum every fare directly in insertion order: 4800 + 5400 + 3500 + 2400 + 6800 + 1400 = 24300, and 7200 + 12200 + 4900 also equals 24300 — the two independent totals agree, so the grouped result is: AC1 → ₹7,200 / 3 passengers, AC3 → ₹12,200 / 7 passengers, Sleeper → ₹4,900 / 7 passengers. This single join against one small dimension table is what a star schema is built for: the fact table carries the numbers, the dimension carries the label you group by, and the join path from fact to any one dimension is always exactly one hop.

The misconception: "denormalizing is sloppy design"

Every student who has been taught to normalize an OLTP schema to third normal form meets the star schema and reacts the same way: dimension tables look under-normalized on purpose. dim_class could arguably split class_tier into its own lookup table (a design called a snowflake schema), and a stickler for normal forms would want to. The instinct that this is "bad design" is the misconception to correct directly. Normalization exists to protect a database against update anomalies during writes: if "AC" tier information were duplicated across many rows and stored redundantly, an update to that tier would need to touch every duplicate consistently, and a partial update leaves the database inconsistent. But a warehouse dimension table is not updated the way an OLTP table is — it changes rarely (a train class list is edited a handful of times a year, not per transaction) and it is tiny compared to the fact table (a few hundred rows in dim_class against hundreds of millions in fact_booking). The redundancy a snowflake schema would remove is redundancy inside a table that is already small; removing it saves almost no storage, while it adds an extra join that every single analytical query must now pay for, every time it runs, against a fact table that is enormous. A star schema is a deliberate, informed trade: pay a storage cost that is negligible in absolute terms, in exchange for a query cost saved on every one of the millions of aggregate queries the warehouse will ever run. It is not an oversight a "better" design would fix — normalizing a dimension table in a warehouse usually makes the system strictly worse for the job the warehouse exists to do.

Getting data in: ETL and ELT

Data does not appear in the warehouse on its own. A pipeline extracts it from operational systems, transforms it into the warehouse's model, and loads it — hence ETL (Extract, Transform, Load). Extraction pulls rows from the booking system, the passenger-master system, and the train/station-master system, typically incrementally (only rows changed since the last run, identified by a timestamp or change-log). Transformation is where the real engineering work sits: deduplicating records that arrived twice, resolving a passenger's OLTP surrogate ID into the warehouse's own dimension key, validating that a fare amount is non-negative, and computing derived fields such as quarter and fiscal_year for dim_date. Loading writes the cleaned, conformed data into the fact and dimension tables, usually in large batch inserts that a column-store engine handles far more efficiently than many small ones. Modern cloud warehouses (Snowflake, Google BigQuery, Amazon Redshift) increasingly favor ELT instead: load the raw extracted data into the warehouse first, then run the transformation as SQL inside the warehouse itself, using its own scan-optimized compute rather than a separate transformation server. This works because these platforms separate storage from compute — a heavy nightly transformation job can borrow extra compute capacity for an hour without touching the capacity serving live dashboard queries, and without ever touching the OLTP fleet running the actual booking system. Either way, the destination shape is the same: a star schema, sized and structured for scans.

Slowly changing dimensions: modeling change without erasing history

Dimension attributes are not permanently fixed, and the warehouse has to decide what to do when they change. Allahabad Junction was officially renamed Prayagraj Junction on 20 October 2018. If dim_station simply overwrote the name in place — a technique called SCD Type 1 — then a report titled "Revenue by Station, 1990–2020" would show every year, including 1995 and 2005, labeled "Prayagraj Junction," a name that did not exist yet. The report would not be miscomputed in the arithmetic sense; every number would trace back to a real booking. But it would misrepresent history, because the dimension no longer records what was true at the time each transaction happened. SCD Type 2 fixes this by never overwriting a dimension row — it inserts a new one and marks the old one as expired, using a surrogate key that has no meaning outside the warehouse:

Every row loaded into fact_booking before the rename carries station_key = 1, permanently; every row loaded after carries station_key = 2. A query grouping by station_name now correctly shows revenue attributed to "Allahabad Junction" for bookings made under that name and "Prayagraj Junction" for bookings made after, with no row ever edited after the fact. This is precisely why dim_station needs its own surrogate key instead of just using the natural station code as its primary key: the same real-world station can legitimately correspond to more than one dimension row over time, and only a surrogate key can distinguish "this station, as it was known then" from "this station, as it is known now." The ETL transform step is what decides, for each incoming record, whether an attribute changed enough to warrant a new SCD2 row or can be corrected in place (SCD Type 1) — that decision is made per attribute, not per table, based on whether the business needs to reconstruct history for it.

Why columnar storage makes warehouse scans fast

The other structural difference between an OLTP engine and a warehouse engine is how data sits on disk. A row-store keeps all of a row's columns contiguous, which is efficient when a query needs the whole row. A column-store keeps each column contiguous instead, across all rows, which is efficient when a query needs only a few columns out of many but touches a large fraction of the rows — exactly the shape of an aggregate query. Make this concrete. Suppose fact_booking has 10 columns, each averaging 8 bytes, giving a row width of 10 × 8 = 80 bytes, and holds 100,000,000 rows for one year of bookings. A query computing total revenue by month needs only two columns: date_id and fare_amount, 16 bytes per row. A row-store engine, even with no other optimization, must still pull 80 bytes for every qualifying row off disk, because that is the physical unit it stores — reading a row means reading the whole row. Total I/O: 100,000,000 × 80 bytes = 8,000,000,000 bytes, 8 × 10⁹ bytes, roughly 8 GB. A column-store engine reads only the two column files it needs: 100,000,000 × 16 bytes = 1,600,000,000 bytes, 1.6 GB. Check the ratio two independent ways: 8 GB ÷ 1.6 GB = 5, and directly from the per-row widths, 80 bytes ÷ 16 bytes = 5. Both agree: for this query, column-store I/O is one-fifth of row-store I/O, a 5× reduction, before any compression or indexing is even applied. This is why an aggregate query that would be I/O-bound and slow against an OLTP row-store can return in seconds against a warehouse column-store holding the same data — the engine simply never touches the columns the query does not ask for.

Active recall

Attempt each question before reading its answer.

1. The fact table's grain is declared as "one row per passenger-class line item." If IRCTC instead stored one row per PNR, and a PNR could span two travel classes, what breaks when you try to compute total passengers per class?
2. Using the six-row fact_booking sample above, what is total revenue and total passenger count for class = Sleeper?
3. Why would adding a B-tree index on fare_amount in the OLTP booking table not meaningfully speed up the query "average fare by class by quarter, over three years"?
4. A station is renamed and the dimension is updated using SCD Type 1 instead of Type 2. What specifically goes wrong in a report titled "Revenue by Station Name, 1990–2020"?
5. A warehouse has 15 columns of 8 bytes each (row width 120 bytes) and 200,000,000 rows. A query needs 3 of those columns. Compute the bytes scanned under row-store and column-store, and the reduction factor.
6. Why is a star schema usually preferred over a fully normalized snowflake schema for a warehouse feeding BI dashboards, even though the snowflake schema uses less storage?

Answers.

1. A single PNR row cannot cleanly hold two different class_id values with their own passenger counts and fares, so grouping by class either forces the whole PNR's passengers into one class (undercounting the other class) or forces duplicate PNR rows (double-counting revenue). The fact grain must match the level of detail the business wants to aggregate by; a coarser grain than "one row per class" cannot answer a per-class question correctly.

2. Rows 3 and 6 are Sleeper: revenue 3500 + 1400 = ₹4,900; passengers 5 + 2 = 7.

3. That query is a large aggregate scan touching a big fraction of all rows across every class and quarter, not a single-row lookup. A B-tree index speeds up equality or range lookups that return a small subset of rows; it does not help when the query must visit almost the entire table anyway. The genuine fix is column pruning (reading only fare_amount, class_id, date_id) and, often, pre-aggregated summary tables — not an index built for point lookups.

4. SCD Type 1 overwrites the old station name in place, so every historical fact row now joins to the new name regardless of when the booking actually happened. The 1995 and 2005 figures would display under a name that would not exist for another two decades — the totals are arithmetically correct but historically false. SCD Type 2 avoids this by keeping the old name as a separate, expired dimension row that historical fact rows continue to reference.

5. Row-store: 200,000,000 × 120 bytes = 24,000,000,000 bytes (24 GB). Column-store: 200,000,000 × 24 bytes = 4,800,000,000 bytes (4.8 GB). Reduction factor: 120 ÷ 24 = 5, confirmed independently by 24 GB ÷ 4.8 GB = 5.

6. Dimension tables are small relative to the fact table, so normalizing them saves storage that is negligible in absolute terms. But every analytical query that needs a dimension attribute must join to get it, and a more normalized dimension means more joins per query, run repeatedly across the warehouse's entire query workload. The star schema trades a small, one-time storage cost for a query-time saving paid back on every single query — the same reasoning that makes controlled denormalization the right call rather than a design flaw.

Think About It

Think about this: How would you explain data warehousing: building analytics powerhouses to a friend who has never seen a computer? What real-world analogy would you use? Imagine you had to build a system using these concepts — what would be your first step? Try this: before moving on, write down three things you learned and one question you still have.

Practice Exercises

Now it is time to practice! Complete these challenges to solidify your understanding:

• Exercise 1: Write a short program that demonstrates the core concept from this chapter. Test it with at least 3 different inputs.
• Exercise 2: Find a real-world example where data warehousing: building analytics powerhouses is used in an Indian company (like TCS, Infosys, Flipkart, or ISRO). Write a paragraph explaining the connection.
• Exercise 3: Create a mind-map connecting data warehousing: building analytics powerhouses to at least 3 other topics you have studied.