Transforming a Normalized PostgreSQL Model into a Star Schema

Hello! This module turns the modeling decisions you have made—grain, dimensions, measures, and history policy—into a working PostgreSQL design. We will start with a normalized source schema and derive an analytical star schema from it. The central challenge is not flattening tables; it is ensuring that every source event remains traceable and joins to the correct warehouse dimension row, including when a dimension has historical versions.

By the end of this lesson, you should be able to identify the source joins that construct each dimension, define the mapping from source identifiers to surrogate keys, and sketch a grain-preserving fact load. The next lesson will turn that design into PostgreSQL DDL.

Start with an event, not a collection of tables

Imagine a normalized PostgreSQL sales source with these relationships:

Suppose the analytical question is: How much did we sell by product category, customer region, and day? The business process is sales order placement. Declare the fact grain before choosing warehouse tables: one row per source order line. order_line is therefore the principal source of fact rows, while order_header supplies the customer and date associated with each line.

This does not mean that every source table becomes a warehouse table. A useful dim_customer can contain both customer and region descriptions, assembled from src.customer and src.region. Likewise, dim_product can contain product and category descriptions. An analyst can then group sales by region_name or category_name without navigating those normalized source relationships.

Watch the following demonstration for the distinction between a source schema, an event-grained fact, and flattened dimensions. It is a modeling illustration, not a prescription to copy source IDs directly into warehouse foreign-key columns.

The video makes the structure tangible. Kimball’s “A Dimensional Modeling Manifesto” supplies the broader conversion rule: one enterprise ER diagram can yield several stars because it often contains several business processes. Read only its conversion discussion; the numerical measures and descriptive dimensions from the video will give you the context you need.

For our sales process, the conceptual target is straightforward:

• fact_sales_line, at one row per order line: warehouse customer, product, and date keys; quantity; and an extended sales amount calculated from the line’s recorded quantity and unit price.
• dim_customer, at one row per customer version: customer name and region name, alongside source identification and version information.
• dim_product, at one row per product version if product history is required: product name and category name.
• dim_date, at one row per calendar date.

The source order_id and order_line_id are still valuable for tracing transactions and detecting duplicate loads. They do not replace the warehouse keys that join facts to dimensions.

A star needs two kinds of identity

A source identifier answers, “Which operational record was this?” A dimension surrogate key answers, “Which warehouse dimension row describes this fact?” Those questions diverge when sources overlap or attributes change.

For instance, two source systems might each issue customer_id = 42. A safe identity is scoped by source system plus source customer ID. Now suppose customer 42 moves to a different reporting region and the business has chosen to preserve region history. The warehouse might hold:

A sale dated 2025-05-10 must resolve to customer key 501; a sale dated 2025-06-10 must resolve to 734. Using the most recent customer key for both sales would silently rewrite the historical regional analysis. The mapping must therefore retain not just source identity and warehouse key, but—where history is tracked—the interval in which that key is valid. Here, “valid to” is an exclusive boundary.

The diagram is helpful as a picture of key substitution, but do not treat its “Most Recent Key Map” boxes as a universal loading rule. It also uses inconsistent labels around the promotion identifier. In an implementation, define each lookup explicitly and test its historical behavior.

You can retain the source-to-target association in the dimension itself—for example, dim_customer(source_system, source_customer_id, customer_key, valid_from, valid_to, ...)—or maintain a separate persistent lookup with those fields. Either way, the mapping must survive batch runs. Regenerating surrogate keys from a transient row order would make previous facts and repeatable loads unreliable.

Joy Mundy’s discussion of the surrogate key pipeline examines what happens at the point where source keys are exchanged for warehouse keys. Read it with the distinction between a genuinely missing identifier and an identifier whose dimension row has not yet arrived in mind.

For the present design, record a policy even if you will implement it later. If the source key is absent because a dimension is inapplicable, an explicit dimension member can represent that state. If a source key is present but its descriptive record has not arrived, a placeholder tied to that specific source identity can preserve traceability until the details arrive. Sending every unresolved source key to one generic “unknown” member loses that distinction unless you preserve the original identifier elsewhere.

Sketch the PostgreSQL transformations

You do not need warehouse DDL yet to specify the transformation. First, describe how a flat customer row is sourced. This illustrative query gathers attributes; the persistent customer key and historical-version policy are applied when those rows are loaded into dim_customer.

SELECT
    'retail' AS source_system,
    c.customer_id AS source_customer_id,
    c.customer_name,
    r.region_name
FROM src.customer AS c
JOIN src.region AS r
  ON r.region_id = c.region_id;

A corresponding product transformation joins src.product to src.category on category_id. Do not carry category_id into fact_sales_line merely because it appears in the source: the fact’s product_key identifies a dimension row that already describes the product category. If category must be reported as it was at sale time, ensure the product dimension’s history policy and key lookup preserve that meaning.

Assume for the next sketch that the dimensions have been populated, dim_product has one applicable row per source product, and src.order_header.order_date is the agreed business date. The SELECT shows the shape of an atomic fact load:

SELECT
    o.order_id AS source_order_id,
    l.order_line_id AS source_order_line_id,
    c.customer_key,
    p.product_key,
    d.date_key,
    l.quantity,
    l.quantity * l.unit_price AS gross_amount
FROM src.order_line AS l
JOIN src.order_header AS o
  ON o.order_id = l.order_id
JOIN dim_customer AS c
  ON c.source_system = 'retail'
 AND c.source_customer_id = o.customer_id
 AND o.order_date >= c.valid_from
 AND (o.order_date < c.valid_to OR c.valid_to IS NULL)
JOIN dim_product AS p
  ON p.source_system = 'retail'
 AND p.source_product_id = l.product_id
JOIN dim_date AS d
  ON d.calendar_date = o.order_date;

Notice what this query does not do. It does not GROUP BY product or day: that would change the declared order-line grain. It does not use customer_id as customer_key. It does not fetch the customer’s current region from the source at fact-load time. Instead, it resolves the customer version applicable on the sale date.

The gross_amount expression uses the unit price recorded on the source line. If the analytical requirement is net sales after discounts or returns, its formula and source fields must be defined separately; changing the measure’s label would not make this expression net sales. Similarly, order_date must be the chosen business date rather than whichever timestamp is easiest to extract.

An inner join makes the query concise but can also silently drop source lines with unresolved mappings. Before loading, test each lookup’s cardinality. For example, this check finds lines with zero or multiple matching customer versions:

SELECT
    l.order_line_id,
    COUNT(c.customer_key) AS customer_matches
FROM src.order_line AS l
JOIN src.order_header AS o
  ON o.order_id = l.order_id
LEFT JOIN dim_customer AS c
  ON c.source_system = 'retail'
 AND c.source_customer_id = o.customer_id
 AND o.order_date >= c.valid_from
 AND (o.order_date < c.valid_to OR c.valid_to IS NULL)
GROUP BY l.order_line_id
HAVING COUNT(c.customer_key) <> 1;

A result of zero means the key was not resolved; a result greater than one means customer-version intervals overlap or the source mapping is duplicated. Apply the same one-match check to product and date lookups. Also reconcile the number of eligible source lines with loaded fact rows, and use the source-system-qualified line identifier to detect duplicate loads. These checks are how you verify that a source-to-target map preserves the declared grain rather than multiplying or losing measurements.

Bringing the design together

The transformation is governed by three contracts: one fact row means one source order line; dimension rows provide the descriptive context for that line; and each source reference resolves to exactly one appropriate warehouse key. Flattening customer/region and product/category makes the star usable, while retaining source identifiers, source-system scope, and any required validity intervals keeps it auditable and historically correct.

In the next lesson, you will express this design as PostgreSQL tables with surrogate keys, foreign keys, and explicit unknown-member rows. That DDL will formalize the relationships sketched here; it will not remove the need to validate the key mappings during loading.