PostgreSQL Star Schema DDL with Surrogate Keys and Unknown Members

Hello! Last time, you traced a normalized sales order line into a star schema and established the key rule: each source reference must resolve to the warehouse dimension row that describes it. This lesson makes that design concrete in PostgreSQL. You will create dimension and fact tables, enforce their relationships, and insert explicit unknown members without confusing “unknown” with “not yet loaded.”

The schema keeps the previous lesson’s grain: one fact row per source order line. Customer history is represented by multiple customer dimension rows when needed; product attributes use a single current row per source product for now.

Surrogate keys need both generation and constraints

A warehouse surrogate key identifies a dimension row, not an operational customer or product. PostgreSQL’s identity column supplies generated values, while a primary key enforces their uniqueness. Those are distinct responsibilities: identity generation alone is not a uniqueness constraint.

We will reserve key -1 for an unknown member in each dimension. Ordinary generated keys begin at 1. The explicit override for -1 is intentional; normal dimension loads should omit the surrogate-key column and let PostgreSQL generate it.

The brief SQL tutorial below offers a useful comparison. Its customer example enforces UNIQUE (customer_id), which fits its Type 1 overwrite policy. It would not fit our customer dimension: one source customer may have several historical rows with different surrogate keys.

Define the dimensions

We will isolate the example in a dw schema. The customer dimension retains the source-system-qualified identifier from the previous lesson. Its uniqueness rule includes valid_from, allowing more than one version of the same customer while rejecting two versions that start on the same date. The constraint does not prevent overlapping validity intervals; the historical load must check those.

CREATE SCHEMA dw;

CREATE TABLE dw.dim_customer (
    customer_key BIGINT GENERATED ALWAYS AS IDENTITY
        (START WITH 1 MINVALUE 1) PRIMARY KEY,
    source_system TEXT,
    source_customer_id BIGINT,
    customer_name TEXT NOT NULL,
    region_name TEXT NOT NULL,
    valid_from DATE,
    valid_to DATE,
    is_current BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT customer_source_version_unique
        UNIQUE (source_system, source_customer_id, valid_from),
    CONSTRAINT customer_member_shape CHECK (
        (customer_key = -1
            AND source_system IS NULL
            AND source_customer_id IS NULL
            AND valid_from IS NULL
            AND valid_to IS NULL
            AND NOT is_current)
        OR
        (customer_key > 0
            AND source_system IS NOT NULL
            AND source_customer_id IS NOT NULL
            AND valid_from IS NOT NULL
            AND (valid_to IS NULL OR valid_to > valid_from)
            AND is_current = (valid_to IS NULL))
    )
);

The valid_to boundary is exclusive, as in the previous lesson. The check establishes a coherent individual row: a current real row has no end date; a closed row does. It does not establish that versions for a source customer never overlap. We will implement and test that loading rule in the next lesson.

For this design, product descriptions use Type 1 semantics: a later category correction would change the description seen by earlier sales. If historical product categories are required, product also needs versioned rows and a date-sensitive fact lookup. That is a modeling decision, not something a foreign key can fix.

CREATE TABLE dw.dim_product (
    product_key BIGINT GENERATED ALWAYS AS IDENTITY
        (START WITH 1 MINVALUE 1) PRIMARY KEY,
    source_system TEXT,
    source_product_id BIGINT,
    product_name TEXT NOT NULL,
    category_name TEXT NOT NULL,

    CONSTRAINT product_source_unique
        UNIQUE (source_system, source_product_id),
    CONSTRAINT product_member_shape CHECK (
        (product_key = -1
            AND source_system IS NULL
            AND source_product_id IS NULL)
        OR
        (product_key > 0
            AND source_system IS NOT NULL
            AND source_product_id IS NOT NULL)
    )
);

A date dimension provides a real calendar date and descriptive attributes for grouping. Its unknown member will have no invented calendar year or month.

CREATE TABLE dw.dim_date (
    date_key BIGINT GENERATED ALWAYS AS IDENTITY
        (START WITH 1 MINVALUE 1) PRIMARY KEY,
    calendar_date DATE UNIQUE,
    calendar_year SMALLINT,
    calendar_month SMALLINT,
    date_label TEXT NOT NULL,

    CONSTRAINT date_month_range
        CHECK (calendar_month BETWEEN 1 AND 12),
    CONSTRAINT date_member_shape CHECK (
        (date_key = -1
            AND calendar_date IS NULL
            AND calendar_year IS NULL
            AND calendar_month IS NULL)
        OR
        (date_key > 0
            AND calendar_date IS NOT NULL
            AND calendar_year IS NOT NULL
            AND calendar_month IS NOT NULL)
    )
);

The UNIQUE (calendar_date) rule prevents two ordinary date rows for the same day. PostgreSQL permits nulls in a unique column, so the separate shape constraint is what reserves the null calendar date for key -1.

Define the fact at its declared grain

The fact stores warehouse foreign keys, measures, and source identifiers for auditability. It does not store source_customer_id as its customer foreign key. source_order_id can group lines back into an order, but it is not unique per line.

Read the fact-table DDL portion of the same tutorial before comparing its choices with ours.

Here is our fact table:

CREATE TABLE dw.fact_sales_line (
    sales_line_key BIGINT GENERATED ALWAYS AS IDENTITY
        (START WITH 1 MINVALUE 1) PRIMARY KEY,
    source_system TEXT NOT NULL,
    source_order_line_id BIGINT NOT NULL,
    source_order_id BIGINT NOT NULL,

    date_key BIGINT NOT NULL
        REFERENCES dw.dim_date (date_key),
    customer_key BIGINT NOT NULL
        REFERENCES dw.dim_customer (customer_key),
    product_key BIGINT NOT NULL
        REFERENCES dw.dim_product (product_key),

    quantity INTEGER NOT NULL CHECK (quantity > 0),
    gross_amount NUMERIC(18, 2) NOT NULL
        CHECK (gross_amount >= 0),

    CONSTRAINT sales_source_line_unique
        UNIQUE (source_system, source_order_line_id)
);

There are two different uniqueness guarantees here. sales_line_key is the fact row’s generated database identity. The composite constraint on (source_system, source_order_line_id) protects the business grain against loading the same source line twice, assuming a line ID is unique within its source system. Neither the fact surrogate key nor the combination of dimension foreign keys would provide that guarantee: two distinct order lines can legitimately have the same customer, product, and date.

NOT NULL plus each REFERENCES constraint ensures that a fact names an existing dimension row. It does not prove that the row is the right historical customer version. Likewise, the quantity and amount checks express this example’s positive sale-line policy; returns or credit notes would require an explicitly defined policy rather than being silently forced into these rows.

Insert and inspect the unknown members

An unknown member is an actual dimension row. Insert it before loading any fact that might refer to it. Because the keys are GENERATED ALWAYS, PostgreSQL requires an explicit override to supply our reserved value:

INSERT INTO dw.dim_customer (
    customer_key, source_system, source_customer_id,
    customer_name, region_name, valid_from, valid_to, is_current
)
OVERRIDING SYSTEM VALUE
VALUES (-1, NULL, NULL,
        'Unknown customer', 'Unknown region', NULL, NULL, FALSE);

INSERT INTO dw.dim_product (
    product_key, source_system, source_product_id,
    product_name, category_name
)
OVERRIDING SYSTEM VALUE
VALUES (-1, NULL, NULL, 'Unknown product', 'Unknown category');

INSERT INTO dw.dim_date (
    date_key, calendar_date, calendar_year, calendar_month, date_label
)
OVERRIDING SYSTEM VALUE
VALUES (-1, NULL, NULL, NULL, 'Unknown date');

A small inspection query makes the intended distinction visible:

SELECT customer_key, source_customer_id, customer_name, is_current
FROM dw.dim_customer
WHERE customer_key = -1;

SELECT date_key, calendar_date, date_label
FROM dw.dim_date
WHERE date_key = -1;

The unknown customer is not a historical version of a real source customer. Its source ID and validity dates are null, and is_current is false. Similarly, the unknown date does not masquerade as a real day in a monthly report.

You can confirm that the fact’s foreign keys accept the reserved rows:

INSERT INTO dw.fact_sales_line (
    source_system, source_order_line_id, source_order_id,
    date_key, customer_key, product_key, quantity, gross_amount
)
VALUES (
    'sales_app', 123, 9,
    -1, -1, -1, 1, 4.50
);

That insert demonstrates constraint behavior, not a recommended routine load. If source customer 42 is identified but its dimension record is late, assigning customer_key = -1 without retaining and following up that source identity would obscure what needs repair. Conversely, a missing source identifier may legitimately require the unknown member. The loading policy must distinguish those cases; the DDL merely makes an intentional unknown reference possible and prevents references to nonexistent dimension keys.

A useful final check is to try the same fact insert with a customer key that does not exist, using a different source line ID. PostgreSQL should reject it on the customer foreign key. Try reusing ('sales_app', 123) as well: the source-line uniqueness constraint should reject the duplicate independently of the foreign keys.

What the DDL now guarantees

You have a PostgreSQL star whose dimension surrogate keys are generated and primary-key constrained, whose fact foreign keys point to those dimensions, and whose unknown members are explicit, joinable rows. The fact also carries a source-qualified line identifier that enforces the declared one-line-per-row grain.

Keep the boundary clear: DDL protects row shape, referential integrity, and specified uniqueness; ETL protects the meaning of key resolution. In the next lesson, you will put that second half into practice by loading customer history with effective dates and a current-row indicator.