Why this decision matters
If you've ever wondered why the company's "data lake" or "warehouse" feels totally different from the operational database it pulled from — this is why. Operational systems (OLTP) are normalized for fast writes; analytical systems (OLAP) are denormalized for fast reads across huge time ranges. Mixing them is one of the most common and most expensive design mistakes in enterprise data work.
By the end of this topic you'll be able to
Distinguish OLTP from OLAP and explain when each is appropriate; sketch a star schema and a snowflake schema; explain the steps of a typical ETL pipeline (or modern ELT); reason about data marts as topic-specific warehouses; identify which design — operational, warehouse, or mart — fits a given business need.
Materials
Key concepts to know
- OLTP vs. OLAP — different purposes, different shapes.
- Star schema — fact table in the middle, dimensions around it.
- Snowflake schema — star with normalized dimensions.
- Slowly changing dimensions (SCDs) — Type 1, 2, 3. How to handle history.
- ETL vs. ELT — transform before load (classic) vs. transform after load (modern cloud).
- Data marts — focused subsets of a warehouse for specific business teams.
Topic modules (deep-dive notes)
Homework 3 — ETL / PL-SQL
HW-3 brings the operational schema (HealthOne) and the analytical view together. You'll write the ETL that moves operational rows into a warehouse-friendly star schema. Details and template.