Part VII — Advanced Topics and Applications

Chapter 25. Data Warehousing and Analytical SQL

Everything so far has been about the database that runs the university — enrollments committed, grades posted, transactions per second. This chapter builds the database that understands the university: five years of enrollment history, trends by department and cohort, the dean's questions. Analytical workloads differ from operational ones in shape, in schema, in SQL — and the differences are systematic enough to have their own architecture: the data warehouse, with its star schemas, its ETL pipelines, and its own SQL habits (the window functions and pivots of Chapter 13, promoted to a design discipline).

The chapter's project is the university's own warehouse: the canonical six-table operational schema, transformed into a star, loaded by real ETL, and answering questions the operational schema answers painfully. Chapter 7's promise — denormalization, done consciously — is the throughline.

After studying this chapter you will be able to:

• Contrast operational (OLTP) and analytical (OLAP) workloads structurally.
• Define the warehouse and its properties.
• Design fact and dimension tables with a declared grain and classified measures.
• Build star and snowflake schemas and know when each wins.
• Write ETL (and ELT) pipelines that load them.
• Answer analytical questions with window functions over history.
• Produce aggregate reports with pivots and grouping sets.
• Choose indexing and partitioning for scans and time ranges.
• Judge PostgreSQL and MySQL for analytical duties — and know when neither is the tool.

25.1 Operational databases versus analytical databases

Two workloads, two shapes:

The conflict is structural: a dean's five-year enrollment scan on the operational database holds buffers, competes with registration writes, and forces indexes useless to it; conversely, the operational schema hurts analysis — six-table joins for every question, history overwritten (the UPDATE that posted the grade erased the pre-grade state), and dimensions scattered. The two workloads want different databases — and the industry's answer is to build both, with a pipeline between them.

25.2 Introduction to data warehouses

A data warehouse is the analytical database: a subject-oriented, integrated, time-variant, non-volatile collection of data supporting decision-making (Inmon's classic definition, unpacked):

• Subject-oriented: organized around subjects — enrollments, sales, payments — not applications.
• Integrated: one conformed truth — the registrar's "CSE" and the finance system's "Computer Science" become one dimension value, once.
• Time-variant: it keeps history — the grade before and after posting, the enrollment as of any date — where the operational database keeps only now.
• Non-volatile: data arrives by load, not by transaction; nothing is updated in place; yesterday's fact is forever (restated only deliberately).

Around the warehouse sit its satellites: data marts (subject- or department-scoped subsets), data lakes (raw, unshaped storage — files, JSON, events — feeding the warehouse), and the BI layer (Chapter 24's tools). The university warehouse this chapter builds: enrollment facts from 2024 through 2026, conformed dimensions, the dean's dashboard on top.

25.3 Fact tables and dimension tables

A warehouse table is one of two kinds. Dimensions are the nouns — who, what, where, when: student, course, instructor, department, date. Facts are the events — measured occurrences at the intersection of dimensions: this student enrolled in that section on that date and earned those credits.

Three design declarations make a fact table lawful:

1. Grain — the precise meaning of one row, stated before any column is chosen: "one row per student per section" (the enrollment grain). Every measure must be true at that grain; every question must roll up from it.
2. Measures — the numeric columns, classified: additive (safe to sum across any dimension — credits_earned), semi-additive (sum across some dimensions only — a balance sums across accounts but not across time), non-additive (never sum — ratios, averages, gpa).
3. Keys — a surrogate key per warehouse row, natural/operational keys kept as attributes (the Chapter 6 discipline, again), and dimension links as foreign keys. A dimension attribute carried in the fact (course_id) is a degenerate dimension — legal and common.

Slowly changing dimensions (SCD) — the classic dimensional problem: Farhana's title changes; the department renames. Type 1: overwrite (current truth — history loses the old value); Type 2: add a row with validity dates (full history — facts keep pointing at the old row, so history stays true); Type 3: keep previous + current columns (limited history). The choice is a business decision per attribute: Type 2 for anything reports slice by ("as-it-was-then"), Type 1 for cosmetic values.

25.4 Star and snowflake schemas

The warehouse layout: the star schema — one fact table surrounded by denormalized dimensions:

                 dim_date
                    │
 dim_student ─── fact_enrollment ─── dim_course
                    │              ─── dim_instructor
                 dim_department

The university star, built from the canonical schema:

CREATE TABLE dim_student (
    student_key   integer PRIMARY KEY,
    student_id    integer NOT NULL,          -- natural key
    full_name     varchar(60),
    major_name    varchar(50),               -- denormalized from department
    admission_year smallint
);

CREATE TABLE dim_course (
    course_key    integer PRIMARY KEY,
    course_id     varchar(8),
    title         varchar(80),
    dept_name     varchar(50),               -- the join, pre-done
    credits       smallint
);

CREATE TABLE dim_date (
    date_key      integer PRIMARY KEY,       -- e.g. 20261009
    calendar_date date,
    year          smallint, semester varchar(6), month smallint
);

CREATE TABLE fact_enrollment (
    date_key       integer REFERENCES dim_date,
    student_key    integer REFERENCES dim_student,
    course_key     integer REFERENCES dim_course,
    instructor_key integer,
    grade          char(2),
    grade_points   numeric(3,1),
    credits_earned numeric(4,1)              -- additive measure
);

Why the star answers better: the dean's "average grade points by department, by year" is one join (fact → dim_course, whose dept_name arrived denormalized) versus the operational schema's three — and every dimension is one hop away. The snowflake normalizes dimensions further (dim_course → dim_department, restored to 3NF): smaller storage, more joins — chosen for enormous, slow-changing dimensions; the star is the default because analytic queries are join-count-sensitive.

25.5 ETL and ELT processes

ETL — Extract, Transform, Load — is the pipeline between operational and analytical:

• Extract — read the sources (the operational university database; other systems in a real warehouse): COPY/SELECT out, or incremental extracts (only rows changed since the last load — timestamps, or the CDC of Chapter 26's logical replication).
• Transform — conform and clean: map operational keys to dimension surrogates, resolve the "CSE" vs "Computer Science" spellings, apply SCD logic, classify grades, compute measures.
• Load — bulk insert into the warehouse (Chapter 15/16's COPY/LOAD DATA), in the batch discipline: drop or disable indexes, load, rebuild, ANALYZE — Chapter 19's write-amplification economics, embraced.

ELT — the modern variant — loads raw first and transforms inside the warehouse (the transformations are SQL — CTE pipelines, the staging-table pattern), because the warehouse's own engine is the cheapest place to run set logic. The university load, in one ETL statement per table pair:

INSERT INTO fact_enrollment (date_key, student_key, course_key,
                             instructor_key, grade, grade_points, credits_earned)
SELECT d.date_key, s.student_key, c.course_key, i.instructor_key,
       e.grade, grade_points(e.grade),
       CASE WHEN e.grade IN ('D','F') OR e.grade IS NULL THEN 0
            ELSE cs.credits END
FROM   enrollment e
JOIN   course_section cs ON cs.section_id = e.section_id
JOIN   dim_date d  ON d.calendar_date = ...   -- the section's term date
JOIN   dim_student s ON s.student_id = e.student_id
JOIN   dim_course c ON c.course_id = cs.course_id
JOIN   dim_instructor i ON i.instructor_id = cs.instructor_id
WHERE  e.grade IS NOT NULL;                   -- the 20 graded facts

The machinery of the whole book is in that statement: Chapter 12's joins, Chapter 22's function, Chapter 11's CASE — ETL is SQL, applied on schedule (Chapter 16's events, cron, or an orchestrator).

25.6 Analytical queries and window functions

History plus window functions is where analytical SQL lives. The enrollment trend (the Chapter 13 year data, now on the warehouse):

SELECT d.year, COUNT(*) AS enrollments,
       LAG(COUNT(*)) OVER (ORDER BY d.year) AS previous_year,
       ROUND(100.0 * COUNT(*) /
             NULLIF(LAG(COUNT(*)) OVER (ORDER BY d.year), 0) - 100, 1)
           AS pct_change
FROM   fact_enrollment f JOIN dim_date d USING (date_key)
GROUP  BY d.year ORDER BY d.year;

 year | enrollments | previous_year | pct_change
------+-------------+---------------+------------
 2024 |          11 |               |
 2025 |           9 |            11 |      -18.2
 2026 |           8 |             9 |      -11.1

The analytical toolbox beyond the trend: running totals and moving averages (the Chapter 13 frames, over time: a rolling three-term average enrollment), cohort analysis (group by admission year — the Chapter 11 cohort, now with facts), rankings within time slices (each year's top department — the top-N-per-group pattern with PARTITION BY year), and period-over-period deltas (LAG by 1 or by 4 quarters). The single habit that characterizes all of it: time is a first-class column, always present via dim_date — never a WHERE afterthought.

25.7 Aggregation and reporting

The dean's dashboard, warehouse-shaped — grouping sets for subtotals, pivots for crosstabs:

SELECT COALESCE(dept_name, 'All departments') AS dept,
       COALESCE(year::text, 'All years')     AS yr,
       ROUND(AVG(grade_points), 2)           AS avg_points,
       SUM(credits_earned)                   AS credits
FROM   fact_enrollment f
JOIN   dim_course c USING (course_key)
JOIN   dim_date  d USING (date_key)
GROUP  BY GROUPING SETS ((dept_name), (year), ())
ORDER  BY dept, yr;

Grouping sets produce the subtotals (GROUP BY ROLLUP is the common special case — Chapter 13), and the pivot idiom (conditional aggregation) cross-tabs grade bands by year — every technique is Chapter 13's, with the star's single-hop joins making them readable. The reporting rule the warehouse adds: pre-aggregate what the dashboard asks weekly — a materialized summary table (Chapter 13's refresh discipline, or Chapter 16's event) turns the dashboard from "scans the fact table" into "reads 50 rows."

25.8 Indexing and partitioning for analytical workloads

Scan-shaped workloads want scan-shaped structures (Chapter 19's menu, applied):

• Fact-table foreign keys: B-tree indexes on every dimension key — the star join's entry points ((date_key, course_key) composites for the common filters).
• BRIN (PostgreSQL): block-range summaries shine on append-only fact tables ordered by load time — near-zero size, huge pruning for date-range scans.
• Partitioning — the big fact table's real structure: PostgreSQL's declarative PARTITION BY RANGE (calendar_date) (a fact per year, new partitions added as time advances), MySQL's native partitioning likewise. Queries with date filters prune partitions (whole partitions skipped before the plan runs); loads swap by partition (ATTACH/DETACH — load elsewhere, attach atomically — the ETL batch without the load window).
• Statistics after every load — ANALYZE (both platforms) — the Chapter 19 law, where a stale estimate misprices an entire scan.

The general analytical indexing law, one line: index the joins' keys, partition the time, BRIN the append-only, and let the scans scan — the operational instinct (index every lookup) is exactly wrong here.

25.9 PostgreSQL and MySQL for analytical applications

Both platforms run warehouses fine to a scale that surprises people:

• PostgreSQL's analytical case: parallel query (multi-core scans and joins), BRIN and GIN, mature window functions and grouping sets, materialized views, and pgvector for the AI-adjacent workloads of Chapter 27 — the strongest open-source general engine for mixed and medium analytics.
• MySQL's case: fast scans on the InnoDB clustered key, partitioning, and rock-solid operational familiarity — but fewer analytical features (no materialized views, no BRIN-analog, later window/grouping-set arrival): chosen when the operational estate is MySQL and the warehouse is modest.
• The honest ceiling: past a few hundred gigabytes or heavy columnar patterns, specialized stores win — columnar warehouses (ClickHouse, DuckDB for local analytics, Redshift/BigQuery/Snowflake in the cloud) exist because scan-shaped workloads reward column storage, aggressive compression, and vectorized execution. The judgment is Chapter 24's ladder, continued: tune → replica → warehouse (relational) → columnar, each rung triggered by measured need.

The university's warehouse — three years, thousands of facts — is a rounding error on either platform, which is precisely why it is the right classroom: the design (star, grain, SCD, ETL) is the durable skill; the engine is a deployment detail.

Chapter Summary

• OLTP is by-key, current-state, write-heavy, normalized; OLAP is scanning, historical, read-heavy, denormalized — different enough for different databases.
• The warehouse is subject-oriented, integrated, time-variant, non-volatile; marts, lakes, and BI sit around it.
• Facts are measured events at a declared grain; dimensions are the nouns; measures are additive, semi-additive, or non-additive; SCD Types 1/2/3 decide history per attribute.
• Star schemas denormalize dimensions around the fact (one-hop joins); snowflakes re-normalize dimensions (storage at join cost).
• ETL extracts, conforms, and bulk-loads; ELT loads raw and transforms in SQL; loads drop indexes, bulk insert, rebuild, ANALYZE.
• Window functions over history: trends with LAG, rolling windows, cohorts, period-over-period — time as a first-class column.
• Reporting uses grouping sets/ROLLUP subtotals, conditional-aggregation pivots, and pre-aggregated materialized summaries.
• Analytical structure: B-tree on dimension keys, BRIN on append-only facts, RANGE partitioning on time with pruning and swap-by-partition loads, statistics after every load.
• PostgreSQL (parallel, BRIN, matviews) out-features MySQL (partitioning, clustered scans) for analytics; both are honest to a few hundred GB, where columnar stores take over — the ladder is tune → replica → relational warehouse → columnar.

Key Terms

Laboratory Exercises

1. Build the university star: the four dimensions and fact_enrollment of Section 25.4 in university_dw; build dim_date with generate_series over 2024-01-01 to 2026-12-31 (one row per day, date_key as YYYYMMDD); load all 20 graded enrollments through the ETL statement of Section 25.5. Expected results: dim_date 1,096 rows; 20 fact rows; verification: SUM(credits_earned) over passing grades = 60 credits (18 passing enrollments × 3 credits each, MAT116's 4 counted once — compute: 17 enrollments at 3 credits plus MAT116's 4 = verify against the canonical data and record your figure).
2. SCD Type 2 rehearsal: add a second title attribute to dim_instructor; promote Farhana Rahman with effective dating (two rows, validity columns); re-load one fact and show it points to the row that was true then. Expected result: two dimension rows, one natural key; the historical fact's instructor reflects the old title.
3. The dean's questions on the star: average grade points by department and by year (one join), the top department per year (window ranking), and the three-year enrollment trend with LAG — each with its expected output computed from the canonical data before running. Expected results: e.g., 2024 CSE-led averages; trend 11/9/8 — reconcile every number with Chapter 13's operational-schema answers.
4. Pivot and subtotals: the grade-band-by-year crosstab and the GROUPING SETS report of Section 25.7, with the "All" rows verified by hand. Expected results: bands per year summing to each year's enrollments (11/9/8); All-years average points 3.51 (70.3 points over 20 graded enrollments).
5. Structure for scale: extend fact_enrollment to the Chapter 19 synthetic 100,000 rows (with dates), partition it by year (both platforms' syntax), and show a date-filtered query's plan pruning partitions; add BRIN on the date (PostgreSQL) and compare plans with and without. Expected results: partition-pruned plans touching one year's partitions; BRIN plan measurably cheaper than the sequential scan on the date range.
6. The materialized dashboard: build the per-department-per-year summary as a materialized view (PostgreSQL) or event-refreshed summary table (MySQL); measure the dashboard query against the fact table versus the summary. Expected result: the summary answers in rows-read terms orders of magnitude cheaper — the pre-aggregation payoff, measured.

Review Questions and Exercises

1. Give three structural differences between the operational enrollment table and the enrollment fact table. Current-state vs history; operational keys vs surrogates with dimension hops; normalized grade vs grain-level measures (grade_points, credits_earned) — history and measures for rolling up.
2. Why is a balance a semi-additive measure, and how do reports treat it? It sums across accounts but not across time (summing daily balances double-counts); reports take a snapshot (latest date per account) rather than SUM over time.
3. State the grain of fact_enrollment and name one measure that would break it. *One row per student per section; a section_capacity measure would repeat per enrolled student and over-sum — capacity belongs to the section dimension or a section-grain fact.*
4. When does snowflake beat star? Enormous, slow-changing dimensions where the normalized sub-dimension saves real storage and the extra joins are rare — the default is star for its single-hop joins.
5. Your ETL runs nightly. Why did the dean's "yesterday's enrollments" report miss today's drop? Non-volatile, batch-loaded: the warehouse is as fresh as the last load — freshness is a stated business decision (Chapter 24's replica rung, or more frequent loads).
6. What is the difference between Type 1 and Type 2 for a title change, in one row each? Type 1: one row, new title, history re-attributes silently; Type 2: a new row effective-dated, facts keep pointing at the old row — history stays as-it-was.
7. Why is ELT displacing ETL, in one sentence? The warehouse's own engine is the cheapest, most governed place to run set logic — transformations become reviewed SQL inside the system of record.
8. Write the window clause for a rolling three-year average of enrollments. *AVG(COUNT(*)) OVER (ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).*
9. Why do fact loads drop and rebuild indexes? Bulk inserts into indexed structures pay per-row maintenance; an empty structure then one sorted build is cheaper — Chapter 19's write-amplification, embraced by the batch window.
10. What does partition pruning do that an index does not, for a five-year date-range query? It excludes whole tables' worth of storage before execution — an index still visits entries; pruning skips partitions wholesale (and enables the swap-by-partition load).
11. When is MySQL a defensible warehouse platform, and what is the honest ceiling for both platforms? Modest warehouses in a MySQL-operated estate, using clustered-key scans and partitioning; both are honest to a few hundred GB — beyond that, columnar stores win the scan-shaped work.
12. Name the four rungs of the analytics ladder and each rung's trigger. Tune (slow queries), replica (read load), relational warehouse (history + conformed truth), columnar (scan scale) — every climb triggered by measured need.

Mini-Project

Build the university warehouse, university_dw, as a complete deliverable: (1) the four dimensions and date dimension (generated, populated); (2) fact_enrollment at the declared grain with additive measures and SCD Type 2 on instructor titles (documented per attribute: which type and why); (3) the ETL scripts — full load, then the incremental load (only grades posted since the last watermark) with idempotency (re-running adds nothing); (4) the dean's dashboard as five warehouse queries (trend with LAG, department×year pivot with subtotals, cohort study, top department per year, grade-band crosstab) each reconciled against the operational schema's answers; (5) the scale pass: the 100,000-row synthetic fact, partitioned by year, BRIN where it pays, ANALYZE discipline documented; (6) the materialized summary and its refresh job; (7) a one-page WAREHOUSE.md — grain, measure classifications, SCD decisions, freshness SLA, and the honest paragraph: at what fact volume this design outgrows PostgreSQL or MySQL and graduates to columnar, and what would move first. This warehouse is Chapter 30's analytics deliverable, built early.