wunder beta

📘 How should a data warehouse be modeled?

OLTP (Online Transaction Processing) systems handle many small, concurrent reads and writes that touch a few rows each, such as inserting an order

6
lessons
~30 min
to learn
Adults
level
Start the course →

What you’ll learn

  1. OLTP vs OLAP: The Analytical WorkloadDistinguish transactional from analytical workloads and explain why analytical data warehouses exist as separate, purpose-built systems.OLTP systems serve many small, low-latency reads and writes on whole records, while OLAP systems serve large aggregate scans over few columns of many rows. Their access patterns conflict, so the system of record stays in OLTP and data is moved into an analytical store via ETL or ELT pipelines. The warehouse's role is to integrate and conform data from many sources into one consistent, query-optimized copy. Row-oriented versus column-oriented storage is one of the deepest differences between the two.
  2. Dimensional Modeling: Facts and DimensionsApply Kimball dimensional modeling—facts, dimensions, additivity, surrogate keys—and contrast it with Inmon and normalization tradeoffs.Dimensional modeling separates numeric measures, stored in fact tables, from descriptive context, stored in dimension tables; fact tables may be transaction, periodic snapshot, or accumulating snapshot, and measures are additive, semi-additive, or non-additive. Surrogate keys give dimensions stable, source-independent keys that enable integration and history. Inmon advocates a top-down normalized enterprise core feeding marts, while Kimball builds bottom-up conformed dimensional marts. Normalization removes redundancy and suits write-heavy OLTP, whereas denormalization flattens dimensions to cut joins for read-heavy analytics.
  3. Schema Design: Star, Snowflake, and SCDsCompare star and snowflake schemas, apply grain and conformed dimensions, and handle change with slowly changing dimensions.A star schema centers a fact table among flat denormalized dimensions for single-hop joins, while a snowflake normalizes dimensions into multiple tables, trading storage savings for join complexity. Declaring the grain—the precise meaning of one fact row—is foundational, and conformed dimensions shared across fact tables enable drill-across analysis. When dimension attributes change, slowly changing dimension techniques decide whether to overwrite or preserve history: Type 1 overwrites in place, Type 2 inserts a new versioned row with a new surrogate key and effective dates for full point-in-time history, and Type 3 stores one prior value in an added column. Type 2 is the workhorse when historically accurate reports are required.
  4. Columnar Storage and PerformanceExplain why column-oriented storage and formats like Parquet and ORC make analytical queries fast.Column-oriented storage keeps each column contiguous, so queries read only the columns they need and skip the rest, sharply reducing I/O on wide-table analytics. Grouping like values together also enables strong compression via run-length and dictionary encoding, and per-block min/max statistics allow data skipping. Engines exploit this with vectorized, batched execution. Open columnar formats such as Apache Parquet and ORC bring these benefits to files in object storage, while OLTP keeps row storage for whole-record writes.
  5. Cloud Warehouses and the LakehouseDescribe modern cloud warehouse architecture, including separation of storage and compute, MPP, partitioning, and the lake/warehouse/lakehouse distinction.Modern cloud warehouses decouple storage from compute so each scales independently and multiple compute clusters can share one copy of data, and they use massively parallel processing to scan large tables across many nodes. Partitioning and clustering reduce the bytes scanned by pruning irrelevant data, improving both performance and cost in pay-per-scan systems. A data lake stores cheap raw files without strong guarantees, a warehouse offers managed structured storage, and a lakehouse adds a transactional table layer over open lake files to combine both. Physical design choices like compression and partitioning are simultaneously performance and cost decisions.
  6. Case Study: Critiquing a Warehouse DesignApply dimensional-modeling and storage principles to critique a flawed warehouse design as a peer reviewer.Given a proposed retail warehouse, the reviewer separates correctness flaws from performance and cost flaws. Mixing transaction sales and periodic inventory snapshots in one fact table violates the single-grain rule and the additivity of measures, risking double-counting; the fix is two correctly grained fact tables on conformed dimensions. Overwriting customer addresses is SCD Type 1 and destroys history needed for point-in-time analysis, so Type 2 is warranted. Uncompressed CSV with no partitioning maximizes bytes scanned, so compressed columnar Parquet with date partitioning cuts latency and cost, and overly normalized dimensions should be denormalized into stars.

Questions this course answers

Which characteristic best describes a typical OLAP query as opposed to an OLTP operation?

OLAP queries are analytical: they scan large numbers of rows over a small subset of columns to compute aggregates. OLTP, by contrast, performs many small low-latency reads and writes touching whole records.

In an ELT pipeline (as opposed to ETL), where does the transformation step occur?

ELT means Extract, Load, then Transform: raw data is loaded into the warehouse first and transformed using the warehouse's own compute. ETL transforms before loading.

Why are OLTP and OLAP workloads typically run on separate systems?

Heavy analytical scans compete with the concurrency, locking, and index maintenance that OLTP writes require, degrading both. Separating them lets each be optimized independently.

In a Kimball dimensional model, where do the numeric business measurements live?

Fact tables hold the numeric measures of a business process (e.g., quantity, sales amount) plus foreign keys to dimensions. Dimensions hold descriptive context used for filtering and grouping.

An account balance captured each month-end is which kind of measure?

Balances are snapshots, so they can be summed across accounts or regions but not across time periods, making them semi-additive.

What is the primary purpose of using surrogate keys in dimension tables?

Surrogate keys are warehouse-generated integers independent of natural keys; they insulate the warehouse from source changes, integrate multiple sources, and are essential for SCD Type 2 history.

Grounded in trusted sources

  • Kimball / Ross, The Data Warehouse Toolkit — dimensional modeling
  • Inmon, Building the Data Warehouse — enterprise warehouse context
  • Snowflake / BigQuery / Redshift architecture overviews — cloud warehouses
  • Databricks lakehouse papers / Delta Lake documentation
  • Oracle / ANSI OLAP primers on analytical workloads

Every Wunder lesson is built from real, reputable sources — never invented.

Related courses

📘How does streaming beat the nightly file?📘Keep stakeholder from meaning everyone📘Roadmaps: Product Foundations📘How do you write a brief before you build?📘Keep survey from meaning random poll📘How do you run a usability test?📘Keep North Star from meaning vanity hit📘How do product funnels measure drop-off?

Wunder is a personalized learn-anything platform — tell it any topic and it builds a beautiful, fact-checked course in minutes, with narration, a knowledge check, and a college-style University track.

All topics · Home

© 2026 Wunder Learning LLC · Terms & Privacy