Dimensional Modeling and Data Warehousing

Data Engineering & Modern Data Platforms · Lesson 10 · Data Engineering Foundations

Lesson Overview

Finance asked for revenue by course, by country of the learner, and by the month the course was published. The operational model from Data Modeling Fundamentals can answer that, and the query joins accounts, orders, lines, seats, learners, courses, and instructors, then argues about whether list_price_cents or unit_price_cents is revenue. It is the right query for a database that takes the order. It is a poor interface for every analyst who will ask a variation tomorrow. Someone will join seats before aggregating and multiply a team pack by twelve again.

A dimensional model is a published contract for those questions. You declare the grain of a fact table in one sentence. You attach measurements that are true at that grain. You attach dimensions that describe who, what, when, and where, using surrogate keys the warehouse owns. You decide, attribute by attribute, what happens when a learner changes country or a course changes title. That decision is a slowly changing dimension policy, not a surprise discovered in a dashboard. Star schemas keep dimensions one join from the fact. Snowflake schemas normalize a dimension when the hierarchy is large and shared. Both are analytical shapes. Neither replaces the third-normal-form store that checkout writes.

This lesson builds TechLearningPro's first warehouse slice: a sales fact at order-line grain, a learning-progress fact, shared learner and course dimensions, and a date dimension used in more than one role. It covers transaction facts, periodic snapshots, and accumulating snapshots; conformed dimensions; degenerate dimensions such as the order number; late-arriving data; and the reason a modern columnar warehouse still benefits from a declared grain even when a team is tempted to store one big table. Module 2 will load these tables. The load cannot invent the grain. If you do not write it down here, the pipeline will invent it in a join.

Learning Objectives

By the end of this lesson, you should be able to:

1. Declare a fact grain in one sentence and reject columns that are not true at that grain.
2. Distinguish fact tables from dimension tables, and list the measurements versus the descriptors in the TechLearningPro sales model.
3. Choose a transaction fact, a periodic snapshot, or an accumulating snapshot for a stated business process.
4. Draw a star and a snowflake for the same process, and say what the extra normalized table is buying.
5. Assign a slowly changing dimension type per attribute: overwrite, new version, or a single previous-value column, and implement type 2 with effective dates and a current flag.
6. Use warehouse surrogate keys, and keep the operational business key as a non-primary attribute of the dimension.
7. Explain conformed dimensions with a bus matrix for sales and learning.
8. Place degenerate dimensions, role-playing date dimensions, and junk or mini-dimensions where they earn their keep.
9. Handle a late-arriving fact and a late-arriving dimension without corrupting history.
10. Compare a star with a one-big-table publish for a columnar engine, and keep the grain either way.
11. State what the operational model still owns, and what the warehouse is allowed to rebuild.
12. Hand Module 2 a load order: dimensions before facts, idempotent loads, and a reconciliation to unit_price_cents and to the payment provider.

Prerequisites

• Data Modeling Fundamentals: entities, snapshots versus current state, and the order, line, seat, payment, and completion grains.
• Advanced SQL and Window Functions for the analytical queries these tables must make cheap and unambiguous.
• Relational Database Internals only as context for why the warehouse is not the primary. You will not tune vacuum in this lesson.
• Familiarity with the idea of a foreign key. Warehouse foreign keys are sometimes left unenforced for load speed; the lesson will say when that is an explicit risk.

Detailed Core Concepts

Grain first

The grain is the meaning of one row. For TechLearningPro sales, a defensible grain is: one row per order line, the sold quantity of one course on one order, at the price captured at purchase. It is not one row per seat, and it is not one row per payment attempt. Those are other facts.

Write the sentence above the CREATE TABLE. Every column has to be legal at that sentence. quantity and unit_price_cents and extended_amount_cents are legal. seat_assignee_email is not legal, because a line has many seats. payment_status is not legal as a single value if you mean the full attempt history; the line can carry order_paid_flag only if "the order reached a succeeded payment" is defined and the whole order shares it. Putting order_paid_flag on every line duplicates an order-level fact. Analysts will SUM it and count paid orders as if they were lines. Prefer a separate order-grain fact, or a dimension of the order that is not summed. The discipline is: additive measurements live on the fact at the right grain; descriptors live on dimensions; flags that are not additive are labeled so a SUM looks wrong in review.

If two stakeholders disagree about the grain, you do not have one fact. You have two. "Revenue including refunds" and "seats assigned" are different grains. A table that tries to be both will use nulls in half the columns and invite the twelve-times bug.

Facts and dimensions

A fact table records measurements of a business process. A dimension table records the context you filter and group by. In the sales star:

Measurements on fact_order_line: quantity, unit_price_cents, extended_amount_cents, discount_cents. The extended amount is stored, not only computed, so a report does not re-derive a formula that someone changes. It must equal quantity * unit_price_cents - discount_cents at load, and the load rejects rows that fail the check. Storing a derived additive measure is normal in a fact table when the formula is part of the contract. It is the same class of decision as the progress snapshot in lesson 9, with the loader as the single writer.

Foreign keys on the fact point at dimension surrogate keys: date_key, course_key, account_key, learner_key when the line has a single learner. A team line does not have one learner. Do not put a nullable learner_key on an order-line fact and then teach people to SUM revenue only when it is not null. Revenue for team packs would vanish. The line fact joins to account and course. A separate fact_seat at seat grain joins to learner and can be empty of revenue, or can carry an allocated amount you have defined. Allocation is a business rule. Splitting the pack price evenly across seats is a rule you write down. Leaving revenue only on the line avoids pretending you know the split.

Three kinds of fact

Transaction facts record an event that happened. The order line is one. A completion fact, one row per completion, is another. They are inserted and rarely updated. Refunds are new rows or new facts with a negative amount if the grain is "revenue movement," or a status on a refund fact at refund grain. Updating the original line to subtract revenue destroys the ability to see both the sale and the refund. Prefer an additive refund row or a separate fact, and teach analysts which column is net.

Periodic snapshots record a level at a regular interval. "Seats assigned and seats still open, per account, at the end of each day" is a snapshot. It is not a transaction. You cannot reconstruct the end-of-day open-seat count from transactions without replaying every assignment, which you can do, and which is more expensive and easier to get wrong in every report. A daily snapshot fact makes the question a filter on date_key. The measurements are semi-additive: you may sum open seats across accounts on one day, and you may not sum open seats across days. Label semi-additive measures. A SUM across a month of a snapshot balance is the bug.

Accumulating snapshots record a process with a known set of milestones and update the row as it progresses. "One row per enrollment, with viewed_at, paid_at, first_lesson_at, completed_course_at" is the funnel from lesson 7 stored as a table rather than recomputed from events every time. The row updates. The grain stays "one enrollment." This is the right publish for a pipeline that has already defined the milestone rules. It is a poor operational table, because the product does not update milestone columns inside checkout. The warehouse loader updates them. If events arrive late, the loader recomputes the affected enrollments. That is the same late-data rule as lesson 7, applied to a table.

Star and snowflake

A star joins the fact to dimensions that are one table each. dim_course contains course_title, track_name, level_name, and instructor_name in one row per version. The analyst's query is short. The loader repeats track_name on every course in the track. When the track is renamed and you use a type-1 overwrite, one update touches many course rows, or one update touches a single dim_track if you snowflaked.

A snowflake moves track to dim_track and stores track_key on dim_course. You do it when the outrigger is large, when many facts must share the same hierarchy definition, or when the hierarchy changes on its own schedule. You pay for an extra join and for a loader that must insert parents before children. TechLearningPro's course catalog is small. A star with track attributes on dim_course is the default. Snowflake the track only when track ownership, track-level metrics, or a shared hierarchy across several course-like products makes the extra table the simpler contract. Do not snowflake by reflex because a modeling poster showed third normal form. The warehouse is allowed to repeat track_name. The operational catalog.courses table is not the place you denormalize instead. Each store keeps the shape that matches its writer.

Slowly changing dimensions

Operational learning.learners holds the current email and country. The warehouse must answer "revenue from learners whose country at the time of purchase was India" and also "how many learners live in India today." Those are different questions. Slowly changing dimension types are the menu. Pick per attribute, not per table, even though implementation often versions the whole row.

Type 2 is the one to implement carefully. dim_learner gains learner_key (surrogate, primary key), learner_id (business key from the operational table), email, country_code, effective_start, effective_end, and is_current. A change to country closes the open row (effective_end set, is_current false) and inserts a new row with a new learner_key. Facts loaded after the change use the new key. Facts already loaded keep the old key, so last year's revenue stays in last year's country. That is the point.

Do not use the operational learner_id as the primary key of a type-2 dimension. It is not unique across versions. It stays as the business key, with a unique constraint on (learner_id) only among rows where is_current. A unique constraint on (learner_id, effective_start) keeps history honest.

Type 1 on country is also a valid product decision if every report should show the learner's country as of this morning, including a restatement of history. The failure mode is silent restatement: finance thought the number was frozen. Write the type next to the attribute in the model document. Mixed policy on one row is normal: type 1 for a corrected spelling, type 2 for country. Implementing mixed policy means the loader compares columns and only opens a new version when a type-2 column changed. A type-1 column change updates the current row and, if you want history corrected everywhere, updates prior versions too. Say which one you do. Updating all versions for a type-1 fix is the usual reading of "the past should show the corrected spelling."

Type 3 stores country_code and previous_country_code. It answers one rollback question and becomes awkward at the second change. Prefer type 2 when the attribute is a real historical category.

There is a practical hybrid, often called type 6 in warehouse writing: a type-2 row for history, plus a current-value column overwritten on all versions so a report can group history by today's label without a second join. Use it when analysts constantly ask "bucket last year by this year's course track." It is more loader logic. It is not required to ship the first sales star.

Surrogate keys, conformed dimensions, and the bus

The warehouse surrogate is local to the warehouse. It can be a bigint identity assigned at load. The operational key rides along as learner_id or course_id. Facts store the surrogate. Joins between facts and dimensions are on integers you control, even when the source uses uuids for events. The completion fact can store event_id as a degenerate dimension: a dimension key with no dimension table, because the identifier has no attributes worth a table. The order's natural order number, if you show it to customers, is the same idea on the line fact: order_id stored on the fact for drill-through, not joined to a dimension unless the order has attributes you group by. If you group by order status, that status belongs in dim_order or on a fact at order grain. Stuffing high-cardinality text you never group by into a dimension still costs a join. Degenerate storage on the fact is the lighter tool.

A junk dimension is the opposite problem: many low-cardinality flags (is_refunded, channel, device_class) that do not deserve a dimension each and do not belong as a pile of uncoordinated columns analysts will sum. Combine them into one small dim_order_profile and store one profile_key on the fact. Do this when the combinations are stable and queried together. A mini-dimension is the same idea for a fast-changing slice of a large dimension, such as a learner's engagement band, so the main dim_learner type-2 row does not open a new version on every band change. TechLearningPro does not need either on day one. The line fact's grain is enough. Add a junk dimension when the flag list becomes a review argument, not before.

Conformed dimensions mean dim_learner and dim_date and dim_course are the same tables for the sales fact and the completion fact. "Learner" does not mean one thing in finance and another in product. A bus matrix lists business processes against dimensions and marks which process uses which dimension:

The date dimension is role-playing. One physical dim_date is joined multiple times under different foreign keys: sold_date_key, enrolled_date_key. In SQL that is two aliases of the same table. You do not copy dim_date into dim_sold_date and dim_enrolled_date as separate loads unless a tool forces you to. The role is the foreign-key name.

Building the bus before the second fact is how you avoid a product_dim and a course_dim that disagree on title. The first fact's convenience is the second fact's inconsistency. Conformed does not mean "identical ETL code copied twice." It means one loader writes dim_course and every fact references it.

Late data

A late fact arrives after the day it belongs to. The sale's sold_date_key is the business date, not the load date. You insert the fact into the partition or the date range of the business date, and you recompute any aggregate you already published for that date. A load_date column is allowed so operations can see when the row landed. It is not the column analysts filter for "October revenue."

A late dimension is a fact that references a learner the dimension load has not seen. The loader inserts a stub dimension row: unknown attributes, real business key, a surrogate, is_current true, and a flag is_inferred. When the real attributes arrive, type-2 or type-1 rules apply to the stub. Dropping the fact because the dimension is missing undercounts revenue. Parking the fact in an error table is correct when the business key itself is invalid. A missing dimension row for a valid key is a load-order problem, and a stub is the safety valve if facts and dimensions are processed in parallel. The default schedule is simpler: load dimensions, then facts, so stubs are rare. Module 2's orchestration will sequence those tasks. The model still documents the stub rule for the nights the sequence slips.

One big table, and what it does not cancel

Columnar warehouses make a wide, denormalized table attractive: one row per order line with course title, learner country, and account name repeated, no join at query time. That publish can be the serving layer. It is not a reason to skip the grain sentence or the SCD policy. The wide table is a type-1 or type-2 decision flattened into columns. If you overwrite country on the wide table, you restate history. If you need history, the wide table must contain the country as of the sale, which is exactly what a type-2 dimension would have supplied at load. Keep the dimensional model as the contract. Generate the one-big-table extract from it if a tool or a cost profile wants fewer joins. Teams that start with the wide table and no grain document discover, six months later, that two pipelines flattened different joins and revenue no longer matches.

Inmon-style corporate information models and Kimball-style dimensional buses are different sequences toward an integrated warehouse. One starts from an enterprise normalized store and publishes data marts. The other starts from process-grained stars that share conformed dimensions. TechLearningPro is small enough to start with the bus: a few facts, shared dimensions, fed from the operational model you already normalized. You do not need a second enterprise model between PostgreSQL and the star. You need the grain, the conformed keys, and a loader. If the company later has many source systems for "learner," the conformed dimension becomes the place those sources merge, with a source-system column and a survivorship rule. That is integration work. It is still a dimension.

Architecture and Design Diagrams

mermaidCopy
flowchart TD
  OLTP[(Operational PostgreSQL lesson 9)]
  DIMLOAD[Dimension load]
  FACTLOAD[Fact load]
  DIML[dim_learner type 2]
  DIMC[dim_course]
  DIMD[dim_date]
  DIMA[dim_account]
  FACT[fact_order_line grain = one line]
  SEAT[fact_seat grain = one seat]
  SNAP[fact_enrollment_snapshot accumulating]
  AGG[Published daily revenue]
  BI[Finance and product dashboards]

  OLTP --> DIMLOAD --> DIML
  DIMLOAD --> DIMC
  DIMLOAD --> DIMA
  DIMD
  OLTP --> FACTLOAD
  DIML --> FACTLOAD
  DIMC --> FACTLOAD
  DIMA --> FACTLOAD
  DIMD --> FACTLOAD
  FACTLOAD --> FACT
  FACTLOAD --> SEAT
  FACTLOAD --> SNAP
  FACT --> AGG --> BI
  SEAT --> BI
  SNAP --> BI

Star for the line sale:

mermaidCopy
flowchart LR
  F[fact_order_line]
  F --> DD[dim_date as sold_date]
  F --> DC[dim_course]
  F --> DA[dim_account]
  F --> DI[dim_instructor]

Type-2 learner history for one person who moved country:

mermaidCopy
sequenceDiagram
  participant SRC as Operational learner
  participant DIM as dim_learner
  participant FACT as fact_order_line

  Note over DIM: learner_key 10, IN, effective to 2026-10-31, not current
  SRC->>DIM: Country changes to DE on 2026-11-01
  DIM->>DIM: Close key 10, insert key 11 current DE
  FACT->>DIM: October sale still references key 10
  FACT->>DIM: November sale references key 11

Technical Explanation

Tables

sqlCopy
CREATE TABLE warehouse.dim_date (
  date_key        integer PRIMARY KEY, -- YYYYMMDD
  full_date       date NOT NULL UNIQUE,
  month_start     date NOT NULL,
  month_name      text NOT NULL,
  quarter         integer NOT NULL,
  year            integer NOT NULL
);

CREATE TABLE warehouse.dim_learner (
  learner_key      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  learner_id       bigint NOT NULL,
  email            text,
  country_code     char(2),
  effective_start  date NOT NULL,
  effective_end    date NOT NULL,
  is_current       boolean NOT NULL,
  is_inferred      boolean NOT NULL DEFAULT false,
  UNIQUE (learner_id, effective_start)
);

CREATE TABLE warehouse.dim_course (
  course_key           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_id            bigint NOT NULL,
  slug                 text NOT NULL,
  title                text NOT NULL,
  track_name           text NOT NULL,
  instructor_name      text NOT NULL,
  effective_start      date NOT NULL,
  effective_end        date NOT NULL,
  is_current           boolean NOT NULL,
  UNIQUE (course_id, effective_start)
);

CREATE TABLE warehouse.dim_account (
  account_key      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id       bigint NOT NULL,
  account_type     text NOT NULL,
  effective_start  date NOT NULL,
  effective_end    date NOT NULL,
  is_current       boolean NOT NULL,
  UNIQUE (account_id, effective_start)
);

CREATE TABLE warehouse.fact_order_line (
  order_line_id          bigint PRIMARY KEY,
  order_id               bigint NOT NULL,
  sold_date_key          integer NOT NULL REFERENCES warehouse.dim_date (date_key),
  course_key             bigint NOT NULL REFERENCES warehouse.dim_course (course_key),
  account_key            bigint NOT NULL REFERENCES warehouse.dim_account (account_key),
  quantity               integer NOT NULL CHECK (quantity > 0),
  unit_price_cents       bigint NOT NULL,
  discount_cents         bigint NOT NULL DEFAULT 0,
  extended_amount_cents  bigint NOT NULL,
  load_date              date NOT NULL,
  CHECK (extended_amount_cents = quantity * unit_price_cents - discount_cents)
);

order_line_id from the operational store is the fact's primary key at this grain, which makes the load idempotent: insert or update on conflict of the same line. Using a new warehouse identity as the only key, without a unique constraint on order_line_id, lets a replay double revenue. The operational key is the idempotency key of a transaction fact. The dimension's surrogate is different: it must change when a type-2 version opens, so the fact stores course_key captured at load, not course_id alone. Store course_id as well if drill-through to the source is easier. Do not join facts to dimensions on course_id if type-2 versions exist. You will fan out.

The date key YYYYMMDD as an integer is a convention, not a law. It is readable in raw files and it joins to dim_date. A real date column on the fact can play the same role. Pick one and use it on every fact so the conformed date dimension is actually shared.

Type-2 load logic

For each source learner, find the current dimension row by learner_id and is_current. If none exists, insert. If the type-2 columns match, do nothing, and apply type-1 column updates if any. If a type-2 column differs, set effective_end to the day before the change and is_current false on the old row, then insert the new version. Do this in one transaction per learner or in a set-based merge that cannot leave two current rows. PostgreSQL MERGE (15 and later) or UPDATE then INSERT both work. The invariant to test is: exactly one is_current row per learner_id, and no overlapping effective ranges.

Facts resolve the key they need with a range predicate:

sqlCopy
SELECT l.learner_key
FROM warehouse.dim_learner l
WHERE l.learner_id = $1
  AND $2::date >= l.effective_start
  AND $2::date <= l.effective_end;

If the sale date falls in a gap, the load fails that row into an exception table. Filling gaps with an open-ended end date (9999-12-31) on the current row avoids a moving target. The close step replaces 9999-12-31 with the real end. Queries for "current" use is_current rather than comparing to today, so a current row remains current even when effective_end is a sentinel.

What is additive

extended_amount_cents sums across courses, accounts, and dates. quantity sums the same way if quantity means seats sold, and it double-counts a business question that wanted distinct courses. COUNT(*) on the fact counts lines, not orders. Distinct orders are COUNT(DISTINCT order_id), which is not additive across arbitrary groups: the sum of distinct orders per course is not distinct orders overall, because a multi-course order is in two groups. Publish "orders" from an order-grain fact if the company will misuse distinct counts. This is the grain sentence saving you again.

A daily snapshot of open seats stores open_seats semi-additive on date. The report SQL for "open seats yesterday" filters one date_key. A chart of the month plots the measure by day and does not sum it. Put semi_additive in the column comment or the metric dictionary. The dictionary is a table or a document the dashboard links to. A warehouse without that note will be summed.

Accumulating enrollment snapshot

sqlCopy
CREATE TABLE warehouse.fact_enrollment_progress (
  enrollment_id     bigint PRIMARY KEY,
  learner_key       bigint NOT NULL,
  course_key        bigint NOT NULL,
  enrolled_date_key integer NOT NULL,
  paid_date_key     integer,
  first_lesson_date_key integer,
  completed_date_key integer,
  reached_paid      boolean NOT NULL,
  reached_complete  boolean NOT NULL
);

The loader implements the ordered-funnel rules from lesson 7. paid_date_key stays null until a payment qualifies. The row is updated in place. Analysts count reached_complete and do not infer completion from a non-null lesson event that happened before payment. Because the row updates, the table is mutable, and the pipeline's idempotency key is enrollment_id. A reload replaces milestone columns from source events rather than incrementing them. Incremental logic that sets a timestamp and never clears it will keep a milestone that a corrected source removed. Full recompute of open enrollments, plus enrollments touched since the last run, is the safer batch rule.

Reconciliation

Every load publishes a control row: source sum of quantity * unit_price_cents for lines whose order has a succeeded payment, warehouse sum of extended_amount_cents for the same business dates, and the difference. A non-zero difference stops the dashboard publish. The payment provider total will not match line revenue when orders are paid in a different month than they are placed, or when taxes and processor fees exist. Reconcile like with like. Fees belong on a payment fact if finance wants them. They do not belong silently inside extended_amount_cents one month and not the next.

Physical storage in the warehouse

If this warehouse is still PostgreSQL, cluster or partition fact_order_line by month of the sold date, and index the foreign keys you join. If the warehouse is a columnar engine, partitioning by sold date still prunes, and the star can be flattened into a serving table after the load validates grain. Lesson 11's columnar formats are how that serving table is often stored in a lake. The dimensional contract does not change when the file format does. Module 2 chooses the engine and the schedule. This lesson chooses the rows.

Unenforced foreign keys in some cloud warehouses are a feature of the engine. The logical reference remains. A load test must find facts whose course_key is missing from dim_course. The database will not always do it for you. Treat that test as part of the model, not as optional data-quality theater.

Practical Implementation or Design Walkthrough

Problem

Publish a sales star and a seat fact for TechLearningPro so finance can report October revenue by course track and by the learner country in effect on the sold date, and product can count assigned seats without multiplying revenue.

Steps

1. Write the grain sentences for fact_order_line, fact_seat, and fact_enrollment_progress on one page. Strike any column that fails its sentence.
2. Mark each learner and course attribute type 1 or type 2. Country and course title are type 2. Email typo fixes are type 1 unless compliance needs the old email, in which case email is type 2 and you restrict who can select it.
3. Build dim_date for the reporting horizon, including dates with no sales, so outer joins are unnecessary for a daily chart.
4. Load dimensions from operational tables. Assign surrogates. Open type-2 rows with a sentinel end date.
5. Load lines. Resolve course_key and account_key as of placed_at. Compute extended_amount_cents in the loader. Reject rows that fail the check. Use INSERT ... ON CONFLICT (order_line_id) DO UPDATE so a replay replaces the line instead of duplicating it.
6. Load seats with no revenue column. Join to dim_learner only when learner_id is not null.
7. Reconcile extended amount to the operational line sum for the month, filtered to orders with a succeeded payment if that is the published definition. Document the filter in the metric dictionary.
8. Run the misuse query on purpose: sum revenue after joining to seats, watch it explode on the quantity-12 order, and keep that query in the test suite as a failing example named do_not_sum_revenue_at_seat_grain.
9. Give Module 2 the task order: dimensions, then facts, then reconciliation, then dashboard refresh. A fact load that runs first must use the stub-dimension rule or it must fail closed.

Expected outcome on the worked example

The company order from lesson 9 is one line: quantity 12, extended amount 58800 cents, one course_key, one account_key, no learner on that fact. Twelve seat rows exist in fact_seat, nine with a learner_key. October revenue grouped by track includes 58800 once. A country report that needs a learner uses self-serve lines, or it uses seats and does not call the result revenue unless an allocation rule assigns cents to seats. The model makes the illegal sum visible because the cents are not on the seat.

Enterprise Real-World Scenarios

TechLearningPro finance and product share a bus

Finance adopted fact_order_line for revenue by track. Product built a separate "course performance" table keyed by course title text, loaded by a different job, with title taken from the catalog this morning. In September a course was renamed. October's finance report still showed the old title for September sales, because dim_course was type 2 and the fact pointed at the old course_key. Product's table showed the new title for both months. The leadership meeting compared two numbers that were the same metric and two labels that were not. The repair was conformance: product's job reads dim_course and fact_order_line, and the title on a historical row is the title of the course_key on that row. A "current title" view uses is_current for people who want today's catalog names. Both views are easy. Having two loaders invent them is what created the meeting.

Volume did not force the design. Correctness did. The same star still answered a track-level question with one join, which is the original reason to denormalize track onto the course dimension. Security: dim_learner.email is type 2 and is personal data. The finance role that only needs country receives a view of dim_learner without email. The fact table does not need email at all. Hiding a column on a view is a grant decision. Putting email on every fact row "for convenience" spreads the personal data into every extract. Lesson 9's owner boundaries continue here. The warehouse role that loads can read operational email. The published mart should not.

TechLearningPro late refunds and an accumulating funnel

A refund posted three days after month close changed a line that finance had already published by editing extended_amount_cents in place. September moved after the board pack was exported. The replacement design added fact_refund at one row per refund, negative amount_cents, with its own refund_date_key. September's sale fact stayed. September's net view sums sales and refunds and is labeled net. The board pack says which view it used. Meanwhile the accumulating progress fact had set completed_date_key from an event that was later identified as a duplicate and removed in the operational store. The loader's incremental "set if null" logic never cleared the milestone. The fix was to recompute milestones for any enrollment whose source events changed, including deletions, which means the extract must surface deletes or the loader must periodically full-recompute active keys. Module 2's change-data discussion will make deletes explicit. The dimensional lesson is that an accumulating snapshot is a cache of rules, and a cache needs a refresh policy that can move a value back to null.

Why large retailers talk about grain in the first design review

The transferable habit is the sentence at the top of the fact, the ban on summing a measure at the wrong grain, and conformed dimensions so two departments do not each load a product table. Retail lines, shipments, and returns are separate facts, just as lines, seats, and refunds are. Copying their dimension list without writing TechLearningPro's grain imports a store hierarchy you do not have and skips the seat-versus-line bug you do have. The bus matrix is the tool that stays.

Best Practices

• Write the grain sentence before the column list. Review columns against the sentence.
• Store additive money at the grain you will sum. Keep a second fact for a second grain instead of widening one table with nulls.
• Conform dim_date, dim_learner, and dim_course across facts. Role-play dates with aliases, not with divergent copies.
• Choose SCD type per attribute and record it. Implement type 2 with a surrogate, a business key, non-overlapping dates, and exactly one current row.
• Make transaction-fact loads idempotent on the operational key of that grain.
• Load dimensions before facts. Define the stub or fail-closed behavior when a key is missing.
• Reconcile warehouse sums to the operational source at the same grain and the same payment filter. Stop publish on a mismatch.
• Label semi-additive snapshot measures. Do not sum them across time.
• Keep personal identifiers off fact tables when a dimension view can serve the few roles that need them.
• Treat a one-big-table extract as a serving product generated from the star, not as a second definition of revenue.
• Sequence Module 2 tasks to match the load order, and recompute published aggregates for business dates that received late facts or refunds.
• Prefer inserting refunds to editing historical sale amounts after a close.

Common Mistakes and Anti-Patterns

No grain sentence. The table accumulates columns from every request. Consequence: nulls, double counts, and reviews that cannot say what a row is. Fix: one sentence, and a new fact when the sentence would have to change.

Summing line revenue after joining seats. The join multiplies the pack. Consequence: the lesson 9 bug returns inside the warehouse. Fix: revenue queries do not join fact_seat. Seat queries do not select extended_amount_cents.

Type-2 history with the operational id as the primary key. The second version fails the key, or the loader overwrites the first. Consequence: no history, or a load error every change. Fix: warehouse surrogate plus business key.

Overwriting country and still describing the report as historical. Type 1 restates the past. Consequence: last quarter moves because a learner updated a profile. Fix: type 2 for country, or a label on the report that says "current country, restated."

Two course dimensions. Finance and product each load titles. Consequence: a rename splits the trend. Fix: one dim_course on the bus.

"Set milestone if null" on an accumulating snapshot. Deletes and corrections never flow. Consequence: funnels only grow. Fix: recompute from source events for touched enrollments.

Editing a closed month's sale row for a refund. Board numbers change in place. Consequence: no audit of sale versus refund. Fix: a refund fact and a net view.

Enforcing nothing because the warehouse engine has no foreign keys. Missing dimension keys load quietly. Consequence: inner joins drop revenue. Fix: a post-load anti-join test that fails the task.

Flattening to one big table with a different join in each job. Grain and SCD become accidental. Consequence: two nets. Fix: one loader writes the star; marts select from it.

Troubleshooting and Design Review

Design review for a new fact:

1. Read the grain sentence aloud. Does every measure pass?
2. Is the measure additive, semi-additive, or non-additive? What will a careless SUM do?
3. Which dimensions are conformed, and which date roles are aliases of dim_date?
4. What is the SCD type of each attribute that can change?
5. What is the idempotency key of the load?
6. What reconciliation must match before publish, and which business dates reload when data is late?

Interview Questions and Answers

Q1. What is fact grain, and why is it the first decision?

Grain is what one row represents, stated as a sentence. For TechLearningPro, one row in fact_order_line is one sold course quantity on one order at the captured price. Columns that are not true at that grain do not belong on the table. A seat assignee is a different grain. If you skip the sentence, later columns widen the table until sums double-count. Two questions that need two grains need two fact tables, not a row full of nulls.

Q2. How does a star differ from a snowflake, and when would you snowflake course track?

A star stores each dimension as one table joined directly to the fact, so track name can sit on dim_course. A snowflake moves track to its own table and leaves a key on the course dimension. Snowflake when the hierarchy is large, shared by many dimensions, or loaded on its own lifecycle. For a small catalog, repeating track_name on the course dimension is the better default. The operational database can stay normalized either way. Snowflaking the warehouse is not how you satisfy third normal form for checkout.

Q3. What does a type-2 slowly changing dimension do to a learner who changes country?

The loader closes the current dim_learner row, setting effective_end and clearing is_current, and inserts a new row with a new learner_key, the same learner_id, the new country, and is_current true. Facts already loaded keep the old learner_key, so historical revenue stays in the old country. New facts use the new key. The operational primary key cannot be the dimension primary key, because it would not be unique across versions. A type-1 treatment would overwrite country on the existing row and restate history. The choice is per attribute and must be written down.

Q4. Why are warehouse surrogate keys used even when the source already has a bigint id?

The source id identifies the business entity. A type-2 dimension has many rows for that entity, so the source id is not a primary key in the warehouse. Facts need a stable pointer to the exact version that applied. The surrogate is that pointer. The source id remains on the dimension as the business key for loads, reconciliation, and drill-through. Joining a type-2 dimension on the business key alone multiplies every fact by the number of versions.

Q5. What is a conformed dimension, and what does a bus matrix show?

A conformed dimension is one physical dimension shared by multiple facts, with one meaning. dim_course serves sales and completions. A bus matrix is a table of business processes against dimensions, marking which process uses which dimension and which role a date plays. It stops finance and product from loading two course tables that diverge on the day a title changes. Conformance is a loader ownership rule: one job writes the dimension.

Q6. Where do you put the customer-visible order id, and what is a degenerate dimension?

On the fact, as order_id, if the fact's grain is the line and the order id has no attributes you need a table for. That is a degenerate dimension: a dimension identifier stored on the fact without a join. If you group and filter by order status and order attributes, those attributes belong on dim_order, and the fact stores order_key. High-cardinality identifiers you only drill to, such as a completion event_id, stay degenerate so you do not build an empty dimension.

Q7. Transaction fact, periodic snapshot, and accumulating snapshot: which one holds open seats at day end?

A periodic snapshot, one row per account or line per day, storing open_seats as of that date. The measure is semi-additive: sum across accounts for one day, and do not sum across days. A transaction fact of assignments can rebuild the balance only if every report replays history. An accumulating snapshot fits a process with milestones on one row, such as an enrollment moving from paid to completed, and that row is updated. Open seats are a level, not a milestone list, so the daily snapshot matches the question.

Q8. A refund arrives after September is published. How do you model it?

Insert a refund fact at refund grain with its own date key and a negative or separate amount, and define a net view that sums sales and refunds. Leave the original sale row unchanged so the closed month's gross number still explains the board pack. Editing extended_amount_cents in place restates history without a refund record. The reconciliation has to say whether a number is gross or net. Late facts use the business date, not the load date, and any aggregate already published for that business date is recomputed.

Q9. How do you load a fact when the learner dimension row does not exist yet?

The scheduled order is dimensions first, then facts, which makes this rare. If a valid business key is still missing, insert an inferred dimension stub with the business key, unknown attributes, and a new surrogate, then point the fact at it and enrich the stub when source data arrives. If the key is invalid, reject the fact to an exception table. Dropping the fact undercounts revenue. Loading the fact with a null dimension key makes inner joins drop it later, which is a quieter version of the same undercount.

Q10. Why can COUNT(DISTINCT order_id) by course not be added up to get company order count?

An order with two course lines appears in two course groups. Summing the distinct counts double-counts that order. Distinct counts are not additive across overlapping groups. Publish order counts from a fact whose grain is the order, and publish line revenue from the line fact. Analysts then cannot add the wrong column, because the wrong column is not on the table they are grouping.

Q11. A teammate wants to skip the star and land one big table in a columnar warehouse. What do you keep?

You can serve a flattened table if the engine and the query pattern prefer it. You still declare grain, you still decide whether country on that row is as-of sale or current, and you still reconcile the amount to the operational lines. The flattened table should be produced from the dimensional load, or it becomes a second, unreviewed join. One big table does not remove SCD policy. It hides it inside the flatten step, which is where you must make the policy visible in code.

Q12. How does this model connect to the operational schema and to Module 2?

Lesson 9's tables remain the system of record: lines, frozen prices, attempts, seats, completions. The warehouse copies and reshapes them. It does not become the table checkout updates. Module 2's pipelines run the dimension load, then the fact load, then reconciliation, on a schedule, with idempotent keys and a late-data lookback. ETL and ELT choose where the SQL runs. They should not choose a different meaning for an order line. The handoff artifact is the grain sentence, the SCD list, the idempotency key, and the reconciliation query.

Hands-On Exercises

Exercise 1. Grain and the bus

Problem. Design the dimensional model for sales, seats, and lesson completion.

Tasks.

1. Write three grain sentences.
2. Build a bus matrix with date roles.
3. Mark SCD types for learner country, learner email, and course title.
4. List columns you refuse to put on fact_order_line, and why.
5. State the reconciliation query in words, including the payment filter.

Acceptance. A quantity-12 team line contributes its extended amount once to revenue. Seats can be counted without that amount. Historical title and current title are both definable.

Exercise 2. Type-2 and an idempotent fact load

Problem. Implement dim_learner, dim_course, and fact_order_line on PostgreSQL. Load a learner, a sale, a country change, and a second sale. Replay the fact load.

Tasks.

1. Create the tables with a unique (learner_id, effective_start) and a check on the extended amount.
2. Load the first sale against the first learner_key. You may use a self-serve line that has a learner, and note that team revenue stays on the line fact without requiring one.
3. Change country. Show two dimension rows and one current row.
4. Load the second sale against the new key. Show the first fact still pointing at the old key.
5. Run the fact insert twice. Show one row, not two.
6. Write the anti-join you would fail the job on if course_key were unknown.

Rubric.

Lesson Summary

A warehouse table is safe to sum when its grain is explicit, its measures match that grain, and its dimensions mean the same thing in every fact. TechLearningPro's line fact carries frozen price and quantity. Its seat fact carries assignment and not revenue. Its accumulating progress fact stores funnel milestones and must be recomputed when source events change. Type-2 dimensions keep historical country and title on the surrogate the fact already stored. Type-1 overwrites restate the past and have to be labeled when they do.

The star is the contract analysts join. A snowflake is optional for a real hierarchy. A flattened serving table is optional for an engine. Neither is allowed to invent a second revenue. Conformed dimensions, idempotent loads, and a reconciliation to the operational line sum are the handoff to Data Integration Patterns and ETL and ELT Architecture. Those lessons will move and schedule the rows. The next lesson, Data Formats and Serialization, is how the rows are written down between systems: the files and payloads Module 2 will land, evolve, and compact, without changing the grain you just declared.

Further Reading

• Kimball Group / Kimball and Ross, The Data Warehouse Toolkit. Use it for grain, fact types, SCD patterns, and the bus matrix. Apply the patterns to the grains in this lesson rather than copying a retail case wholesale.
• PostgreSQL, MERGE for versioned dimension maintenance on PostgreSQL 15 and later, and INSERT ON CONFLICT for idempotent fact loads on older versions.
• TechLearningPro, Constraints and Creating Tables, for the physical keys behind dim_learner and fact_order_line.
• The operational source of the measures remains Data Modeling Fundamentals. When a warehouse number disagrees with the product, reconcile at line grain before you change a dimension.