Correcting Dimensional Model Design Errors

Hello! In the previous lesson, you used PostgreSQL plans to decide whether a slow star-schema query needed an index, partitioning, or a precomputed summary—without changing the report’s meaning. This final lesson moves one step earlier: is the model itself capable of producing the right answer? A fast query cannot repair a duplicated invoice total or recover history that a dimension load overwrote.

You will review a deliberately flawed sales model, correct its grain, keys, hierarchies, measures, and history policy, then define checks that would catch the same mistakes during a load. Allow about 40 minutes. This follows the design-review approach in Chapter 8 of Dimensional Modeling: In a Business Intelligence Environment, while expressing the corrections in terms you can apply to a PostgreSQL warehouse.

---

Start the review with a business sentence

A table diagram can look plausible while concealing incompatible meanings for its rows. Before reviewing columns, state the business process and complete this sentence: “One row in this fact table represents…” The answer must identify a business occurrence or measurement, not merely list foreign keys.

Ralph Kimball’s Declaring the Grain explains why this declaration precedes the choice of dimensions and facts. Read the opening discussion through “In Business Terms,” then the subsection “Keep Facts True to the Grain” (about 7 minutes).

For a brief visual refresher, watch the dimensions and facts portions of Master Dimensional Modeling Lesson 02 - The 4 Step Process by Bryan Cafferky (about 5 minutes). You have already studied the four-step process; here, listen specifically for the tests you can apply to a draft, rather than treating the video as a new design procedure.

Our review process has six questions, in this order:

1. What business event does one fact row record, and what source identifier distinguishes two such events?
2. Can every proposed dimension be identified once for that event?
3. Does every proposed measure belong to that event, and how may it be aggregated?
4. Do dimension keys identify the intended historical versions?
5. Do descriptive hierarchies support the requested grouping and drilling?
6. Can source records be loaded and reports reproduced without multiplying or losing rows?

The order matters. Choosing an index or polishing a hierarchy is premature if the fact row has not been defined.

---

A draft that looks reasonable

Suppose an online retailer asks for daily net sales and margin by product category and customer region. Analysts also need to see those results as the categories and regions stood when each invoice was issued. The source supplies invoice headers and separately identified invoice lines. Each line has a product, quantity, net sales amount after discount, and a line-level cost amount recorded at invoicing time. Product category and customer region can subsequently change.

The draft is described as follows:

Draft table	Relevant columns or design choice
fact_invoice_sales	invoice_id, product_sku, customer_id, month_key, quantity, net_sales_amount, line_cost_amount, invoice_total, year_to_date_sales, margin_percent, inventory_on_hand
dim_product	product_sku primary key, product_name, subcategory_id; existing row updated when its category changes
dim_subcategory and dim_category	Separate tables joined from dim_product to obtain the category name
dim_customer	Source customer_id primary key, current region_name; existing row updated when the region changes
dim_month	One row per month

The load joins invoice lines to their headers on invoice_id, but the draft documentation describes its fact grain only as “sales by product and month.” It copies each header’s invoice_total onto every associated line. This mixture is realistic: none of its individual columns looks absurd in isolation. The errors emerge when you ask what one row means and what an analyst will do with several rows.

Repair the grain before the schema

The source provides a finer, identifiable event than “product and month.” Declare the corrected grain:

One row in fact_invoice_line represents one issued invoice line, uniquely identified within its source system by invoice ID and invoice line number.

A day-level invoice date belongs to each line; the month is an attribute of that day. If one invoice contains two lines for the same product, those remain two fact rows. Product, customer, and date foreign keys alone cannot be assumed to distinguish them.

The source identifier must support that claim. If invoice IDs can repeat across source systems, the stable source-grain identifier is the combination of source_system_id, invoice_id, and invoice_line_no. A warehouse-generated fact key could additionally be used as a primary key, but it would not replace a uniqueness check on that source-grain combination.

Now inspect the suspect measures. An invoice containing line amounts of 100 and 40 has a header total of 140. Copying 140 onto both line rows makes SUM(invoice_total) return 280, although only 140 was invoiced. At the declared grain, the line amounts belong; the repeated header total does not. An invoice total can be calculated from its lines when the line values reconcile with the header, or maintained separately at invoice-header grain if its independent header-level meaning must be retained.

The other misplaced columns fail different tests. year_to_date_sales aggregates many events and will be repeated or double-counted when lines are grouped. inventory_on_hand measures stock at a particular product, location, and observation time—not an invoicing event. It needs its own inventory snapshot fact. Neither should be rescued by renaming it a line-level measure.

---

Correct keys and hierarchies without erasing history

The draft’s product_sku and customer_id identify source business entities. They cannot, by themselves, identify two historical versions of the same entity. Give each dimension row a warehouse surrogate key, retain the source identifier as an attribute, and use the surrogate key in the invoice-line fact.

A compact corrected design is:

Table	Grain and key columns	Descriptive or measured columns
dim_product	One product version; product_key primary key; retained source_system_id, product_sku	product_name, subcategory_name, category_name, effective period, current-row indicator
dim_customer	One customer version; customer_key primary key; retained source customer identifier	customer_name, region_name, effective period, current-row indicator
dim_date	One calendar day; date_key primary key	Full date, calendar month and year, relevant fiscal labels
fact_invoice_line	One source invoice line; unique source-system/invoice/line identity	product_key, customer_key, invoice_date_key, quantity, net_sales_amount, line_cost_amount

The invoice identifier can remain on the fact as a degenerate dimension for grouping lines into invoices; no separate invoice dimension is necessary solely to hold that identifier. If a distinct invoice header has useful descriptive attributes, decide separately whether a header dimension or header-grain fact is justified.

Flattening category_name and subcategory_name into dim_product makes category grouping available with a direct fact-to-product join. The draft’s chain of product, subcategory, and category tables is a snowflaked hierarchy, not an automatic error in every workload, but it adds joins for a routine reporting path without an established benefit here. A hierarchy also needs a business rule: at a given product-version effective time, which subcategory and category does that product belong to? If that classification changes, updating the only product row would silently rewrite earlier reports.

Use history policies per attribute, based on the required interpretation:

• Type 1—overwrite: Correct a misspelled product description when the previous spelling was erroneous and has no analytical meaning.
• Type 2—add a version: Change product category or customer region when reports must retain the classification at invoice time. Close the old row’s effective period and create a new surrogate-keyed row.
• Type 3—retain a limited prior value in another attribute: Consider this only when users explicitly need a bounded previous-versus-current comparison. It is not a substitute for the multiple historical category or region changes required here.

For example, an invoice line issued while product P7 belonged to “Devices” refers to the Devices product-version key. A later line issued after P7 moved to “Accessories” refers to a different product-version key. Joining either fact to product_sku alone would match both versions and multiply sales; updating one row in place would move old sales into “Accessories.” The surrogate key and effective-time lookup solve different parts of the problem: the lookup selects the correct version during loading, and the key preserves that choice for reporting.

Use the invoicing event’s timestamp—or its business-defined effective date at appropriate precision—for that lookup, not the ETL run date. For late-arriving invoices, this distinction is essential. A dimension’s effective periods for a given source identity must not overlap, and each loadable fact line must resolve to exactly one applicable version. If a reference is genuinely missing, an explicit unknown dimension member is preferable to silently choosing today’s version; reconcile it when the missing identity becomes available.

---

Decide what each measure can answer

Once the keys identify invoice lines correctly, additivity determines which reports are valid. For this draft:

Measure or request	Correct treatment
quantity, net_sales_amount, line_cost_amount	Line-level measures that can be summed across the relevant dimensions, assuming consistent units and currency and complete cost data
invoice_total	Header-grain value; do not repeat and sum it on lines
year_to_date_sales	Calculate from dated line amounts or a separately declared summary; do not store it as though it describes one invoice line
margin_percent	Non-additive ratio; calculate after summing its components
inventory_on_hand	Stock snapshot; keep in a separate fact and do not sum balances across observation dates

For the requested margin report, define the business numerator first. If margin means net sales less the recorded line cost, calculate it at the requested reporting level as:

$$\text{margin percent}=\frac{\sum \text{net sales amount}-\sum \text{line cost amount}}{\sum \text{net sales amount}}$$

This is not SUM(margin_percent) and generally not an unweighted average of line percentages. A zero sales denominator needs an explicit reporting policy. If some invoice lines lack historical line cost, a complete margin cannot be inferred from the product’s current cost: mark the measure incomplete or defer that report until an agreed cost rule and source exist.

An inventory snapshot has a different aggregation contract. Stock counts can generally be combined across distinct products or locations at the same observation time, but adding Monday’s balance to Tuesday’s balance does not give Tuesday’s stock. This is why moving it to another fact table is a semantic correction, not merely a performance choice.

---

Turn the corrections into review evidence

A corrected diagram is a proposal. Before approving it, use source data and representative reports to verify its claims.

First, check that the source identifier really distinguishes invoice lines; include the source system if identifiers are only locally unique:

SELECT source_system_id, invoice_id, invoice_line_no, COUNT(*) AS row_count
FROM staging.invoice_line
GROUP BY source_system_id, invoice_id, invoice_line_no
HAVING COUNT(*) > 1;

Any returned group requires investigation before the fact load. It might represent duplicate extraction, a revision to a line, or an incorrect grain declaration; arbitrarily retaining one row would conceal the problem.

Next, validate historical dimension resolution. For each source line, the product identity and invoice time should match exactly one product version. In a PostgreSQL implementation using half-open effective periods, the lookup condition can take this form:

ON p.source_system_id = l.source_system_id
AND p.product_sku = l.product_sku
AND l.invoice_at >= p.effective_from
AND l.invoice_at <  p.effective_to

Count matches per source line, including zero-match lines. More than one match indicates overlapping versions or an incorrect join; zero indicates missing coverage or an unresolved reference. Apply the same principle to historically versioned customers. Validate both the source-grain row count and the total line amounts before and after dimension resolution: a successful SQL join can still multiply money.

Finally, test reports against the requirements rather than against the diagram alone. Daily totals should reconcile to eligible invoice lines. Category and region totals should use the historical versions requested by the business. An invoice-level report should total its line amounts once per line. A margin report should compute its ratio from aggregated, complete amounts. If a question is actually about stock on hand, route it to the separately declared inventory fact rather than forcing it through invoice lines.

These checks make a useful design-review record: the precise grain sentence, the source identity rule, the dimension-version lookup rule, the allowed aggregation for each measure, and the reconciliation results. They also tell you which failures are logical-model defects. PostgreSQL tuning comes afterward; EXPLAIN ANALYZE can test a correct report’s physical plan, but it cannot certify its business semantics.

---

Final perspective

You have completed the book-centered design path: business questions become a process and a grain; the grain constrains dimensions and measures; history and conformance preserve their meaning; and PostgreSQL implementation and plan review test whether the design works at scale.

The decisive habit is to review one row’s meaning before reviewing its columns. In this case, that exposed a repeated header total, an incompatible stock balance, an unusable month-only date, source keys that could not represent history, and a hierarchy that made routine category analysis harder. Correcting those errors—and proving the load preserves row counts and amounts—produces a model worth optimizing.