Loading Fact Tables with Surrogate Keys and Late-Arriving Dimensions

Hello! In the previous lesson, you loaded Type 2 customer versions and used a sale’s business date to find the right historical version. Now you will turn that lookup into a repeatable atomic fact load. The difficult cases are not the ordinary sales: they are sales whose customer record has not arrived yet, sales with no customer identifier, and sales whose product details arrive after the transaction.

We will use the existing dw.dim_customer from the previous lesson. A small product dimension and sales fact table make the example runnable in PostgreSQL. By the end, each published fact will have valid dimension surrogate keys, while unresolved sales will remain available for a controlled retry.

Decide what an unresolved reference means

Our fact grain is one source sales line. Its source identity is (source_system, source_line_id), not the combination of its dimension keys: two distinct sales can involve the same customer, product, and date.

A fact load translates source identifiers into warehouse keys. For a Type 2 customer, that translation must include the sale date:

JOIN dw.dim_customer AS d
  ON d.source_system = s.source_system
 AND d.source_customer_id = s.source_customer_id
 AND s.sale_date >= d.valid_from
 AND (d.valid_to IS NULL OR s.sale_date < d.valid_to)

This is the historical lookup from the previous lesson. Using d.is_current instead would assign an earlier sale to whichever customer version happens to be current when the load runs.

A failed lookup needs a business meaning, not just a technical default:

The crucial distinction is between “the source did not identify a customer” and “the source identified customer 45, but the warehouse cannot resolve customer 45 yet.” Mapping both to -1 preserves totals, but discards the information needed to repair the second case.

Watch Tech Coach’s Handle Late Arriving Dimensions | Early Arriving Fact for a compact visual comparison of holding a fact and creating an inferred dimension row.

One technical qualification: SQL foreign keys alone generally permit a null reference if the fact column is nullable. Our fact keys are explicitly NOT NULL and foreign keys, so a published line must point to an existing dimension row.

Joy Mundy’s Kimball Group design tip frames these choices in terms of whether users need immediate totals, eventual correction, or both.

Our product placeholder is intentionally simpler than a Type 2 customer version: the demonstration product dimension has one row per source-qualified product. If you use inferred members in a Type 2 dimension, filling previously unknown attributes requires an explicit policy; blindly treating that fill as a substantive Type 2 change could leave the early fact pointing to an uninformative historical row.

Establish the destination and the durable holding area

Run the following setup once in the database used for the previous lesson. It assumes dw.dim_customer still contains its -1 unknown row and the customer versions for source customer 42: West before July 1, 2025, and East from July 1.

The new names end in _demo so they need not replace other course tables. The product dimension retains the source-qualified identifier alongside its surrogate key. The backlog retains the whole sales line, including the unresolved source identifier and measures, so a later attempt can reproduce the lookup.

CREATE TABLE dw.dim_product_fact_demo (
    product_key BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    source_system TEXT,
    source_product_id TEXT,
    product_name TEXT NOT NULL,
    is_inferred BOOLEAN NOT NULL DEFAULT FALSE,
    UNIQUE (source_system, source_product_id)
);

INSERT INTO dw.dim_product_fact_demo (
    product_key, source_system, source_product_id,
    product_name, is_inferred
)
VALUES (-1, NULL, NULL, 'Unknown product', FALSE);

INSERT INTO dw.dim_product_fact_demo (
    source_system, source_product_id, product_name
)
VALUES ('sales_app', 'P10', 'Standard item');

CREATE TABLE dw.fact_sales_line_demo (
    source_system TEXT NOT NULL,
    source_line_id BIGINT NOT NULL,
    sale_date DATE NOT NULL,
    customer_key BIGINT NOT NULL
        REFERENCES dw.dim_customer (customer_key),
    product_key BIGINT NOT NULL
        REFERENCES dw.dim_product_fact_demo (product_key),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    sales_amount NUMERIC(14, 2) NOT NULL,
    PRIMARY KEY (source_system, source_line_id)
);

CREATE TABLE dw.fact_sales_line_backlog_demo (
    source_system TEXT NOT NULL,
    source_line_id BIGINT NOT NULL,
    source_customer_id BIGINT,
    source_product_id TEXT,
    sale_date DATE NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(12, 2) NOT NULL,
    reason TEXT NOT NULL,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (source_system, source_line_id)
);

We retain sale_date in the fact because it determines the customer version and makes the result easy to inspect. In a fuller star schema, the same load would also resolve an applicable date-dimension key. Here, the customer and product keys are the two surrogate-key lookups under study.

Before implementing the load, read the opening and publication passages of Implementing a Dimensional Data Warehouse with Databricks SQL, Part 3. The SQL dialect differs from PostgreSQL, but the distinction between source keys in staging and surrogate keys in the published fact is the same.

A modification timestamp or CDC feed can determine which source lines to stage. It does not determine which historical customer surrogate key a line needs; that still depends on sale_date and the Type 2 validity windows.

Load and resolve an atomic batch

The following is one transaction. Run it as a unit. The staging table contains six distinct sales lines:

• 201 and 202 refer to customer 42 on opposite sides of the Type 2 change.
• 203 refers to as-yet-unloaded customer 45; it should enter the backlog.
• 204 has no customer identifier; its documented policy is the unknown member.
• 205 names product P20, whose details have not arrived; it should get its own inferred product row.
• 206 has no product identifier; its documented policy is the unknown member.

The staging primary key rejects duplicate source line identities within the batch. On a retry, load_candidates includes backlog lines unless a newly staged version of that same line is available.

BEGIN;

CREATE TEMP TABLE stg_sales_line_batch (
    source_system TEXT NOT NULL,
    source_line_id BIGINT NOT NULL,
    source_customer_id BIGINT,
    source_product_id TEXT,
    sale_date DATE NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL,
    PRIMARY KEY (source_system, source_line_id)
) ON COMMIT DROP;

INSERT INTO stg_sales_line_batch (
    source_system, source_line_id, source_customer_id,
    source_product_id, sale_date, quantity, unit_price
)
VALUES
    ('sales_app', 201, 42,   'P10', DATE '2025-06-30', 2, 10.00),
    ('sales_app', 202, 42,   'P10', DATE '2025-07-01', 1, 12.00),
    ('sales_app', 203, 45,   'P10', DATE '2025-07-05', 3, 10.00),
    ('sales_app', 204, NULL, 'P10', DATE '2025-07-05', 1, 10.00),
    ('sales_app', 205, 42,   'P20', DATE '2025-07-02', 1,  7.00),
    ('sales_app', 206, 42,   NULL,  DATE '2025-07-02', 1,  5.00);

CREATE TEMP TABLE load_candidates ON COMMIT DROP AS
SELECT
    s.source_system, s.source_line_id, s.source_customer_id,
    s.source_product_id, s.sale_date, s.quantity, s.unit_price
FROM stg_sales_line_batch AS s

UNION ALL

SELECT
    b.source_system, b.source_line_id, b.source_customer_id,
    b.source_product_id, b.sale_date, b.quantity, b.unit_price
FROM dw.fact_sales_line_backlog_demo AS b
WHERE NOT EXISTS (
    SELECT 1
    FROM stg_sales_line_batch AS s
    WHERE s.source_system = b.source_system
      AND s.source_line_id = b.source_line_id
);

CREATE UNIQUE INDEX ON load_candidates (source_system, source_line_id);

INSERT INTO dw.dim_product_fact_demo (
    source_system, source_product_id, product_name, is_inferred
)
SELECT DISTINCT
    c.source_system,
    c.source_product_id,
    'Pending product details',
    TRUE
FROM load_candidates AS c
WHERE c.source_product_id IS NOT NULL
ON CONFLICT (source_system, source_product_id) DO NOTHING;

CREATE TEMP TABLE resolved_sales ON COMMIT DROP AS
SELECT
    c.source_system,
    c.source_line_id,
    c.source_customer_id,
    c.source_product_id,
    c.sale_date,
    c.quantity,
    c.unit_price,
    CASE
        WHEN c.source_customer_id IS NULL THEN -1
        ELSE dc.customer_key
    END AS customer_key,
    CASE
        WHEN c.source_product_id IS NULL THEN -1
        ELSE dp.product_key
    END AS product_key
FROM load_candidates AS c
LEFT JOIN dw.dim_customer AS dc
    ON dc.source_system = c.source_system
   AND dc.source_customer_id = c.source_customer_id
   AND c.sale_date >= dc.valid_from
   AND (dc.valid_to IS NULL OR c.sale_date < dc.valid_to)
LEFT JOIN dw.dim_product_fact_demo AS dp
    ON dp.source_system = c.source_system
   AND dp.source_product_id = c.source_product_id;

At this point, do not publish merely because the joins ran. A LEFT JOIN can yield no match; an accidental overlap between customer validity windows can yield two matches. The latter is particularly dangerous: it can silently duplicate an atomic measurement.

The next check rejects any sales line that resolved to more than one row. It also rejects a present product identifier that somehow failed to resolve after inferred-member insertion. A present customer identifier with no matching historical version is not treated as an error for the transaction; that line goes to the backlog.

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM resolved_sales
        GROUP BY source_system, source_line_id
        HAVING COUNT(*) <> 1
    ) THEN
        RAISE EXCEPTION
            'A source sales line resolved to multiple dimension versions';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM resolved_sales
        WHERE source_product_id IS NOT NULL
          AND product_key IS NULL
    ) THEN
        RAISE EXCEPTION
            'A source product identifier was not resolved';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM resolved_sales AS r
        JOIN dw.fact_sales_line_demo AS f
          ON f.source_system = r.source_system
         AND f.source_line_id = r.source_line_id
        WHERE r.customer_key IS NOT NULL
          AND r.product_key IS NOT NULL
          AND (
              f.sale_date IS DISTINCT FROM r.sale_date
           OR f.customer_key IS DISTINCT FROM r.customer_key
           OR f.product_key IS DISTINCT FROM r.product_key
           OR f.quantity IS DISTINCT FROM r.quantity
           OR f.sales_amount IS DISTINCT FROM
              (r.quantity * r.unit_price)
          )
    ) THEN
        RAISE EXCEPTION
            'An existing source line has changed: use a correction process';
    END IF;
END;
$$;

INSERT INTO dw.fact_sales_line_demo (
    source_system, source_line_id, sale_date,
    customer_key, product_key, quantity, sales_amount
)
SELECT
    r.source_system,
    r.source_line_id,
    r.sale_date,
    r.customer_key,
    r.product_key,
    r.quantity,
    r.quantity * r.unit_price
FROM resolved_sales AS r
WHERE r.customer_key IS NOT NULL
  AND r.product_key IS NOT NULL
ON CONFLICT (source_system, source_line_id) DO NOTHING;

INSERT INTO dw.fact_sales_line_backlog_demo (
    source_system, source_line_id, source_customer_id,
    source_product_id, sale_date, quantity, unit_price, reason
)
SELECT
    r.source_system,
    r.source_line_id,
    r.source_customer_id,
    r.source_product_id,
    r.sale_date,
    r.quantity,
    r.unit_price,
    'No customer version covers sale_date'
FROM resolved_sales AS r
WHERE r.customer_key IS NULL
   OR r.product_key IS NULL
ON CONFLICT (source_system, source_line_id) DO UPDATE
SET source_customer_id = EXCLUDED.source_customer_id,
    source_product_id = EXCLUDED.source_product_id,
    sale_date = EXCLUDED.sale_date,
    quantity = EXCLUDED.quantity,
    unit_price = EXCLUDED.unit_price,
    reason = EXCLUDED.reason,
    last_attempt_at = now();

DELETE FROM dw.fact_sales_line_backlog_demo AS b
USING resolved_sales AS r
WHERE b.source_system = r.source_system
  AND b.source_line_id = r.source_line_id
  AND r.customer_key IS NOT NULL
  AND r.product_key IS NOT NULL;

COMMIT;

This batch is replay-safe for unchanged source lines. A repeat publication encounters the same source-line primary key and inserts nothing; a replay that tries to change an already published line raises an exception rather than quietly ignoring the change. That is an intentional insert-only transaction fact contract. Corrected transactions or cancellations need their own agreed correction policy.

The backlog is durable, unlike the temporary batch tables. Its source identity prevents repeated retries from creating repeated backlog entries. Keeping publication, backlog changes, and inferred-product creation in one transaction prevents a partially applied batch.

Verify the load, then retry a late customer

Inspect the first publication:

SELECT
    f.source_line_id,
    f.sale_date,
    f.customer_key,
    dc.region_name,
    f.product_key,
    dp.product_name,
    dp.is_inferred,
    f.quantity,
    f.sales_amount
FROM dw.fact_sales_line_demo AS f
JOIN dw.dim_customer AS dc
  ON dc.customer_key = f.customer_key
JOIN dw.dim_product_fact_demo AS dp
  ON dp.product_key = f.product_key
ORDER BY f.source_line_id;

You should have five fact rows, not six. Line 201 points to customer 42’s West surrogate key; line 202 points to its different East surrogate key. Lines 204 and 206 use -1 only for their respective absent source identifiers. Line 205 points to P20’s own inferred product key, not to unknown product -1.

Check the held line independently:

SELECT source_line_id, source_customer_id, sale_date, reason
FROM dw.fact_sales_line_backlog_demo
ORDER BY source_line_id;

Only 203 should appear. Its quantity and price are still in the backlog, so its amount has not yet entered published fact totals. This is the trade-off of holding a fact rather than publishing it immediately with an inferred customer. A reporting requirement that demands fully balanced same-day sales might justify a different policy.

Suppose customer 45’s dimension data now arrives and is valid from January 1, 2025. For this demonstration, insert that initial version; in a real pipeline, the customer dimension load from the previous lesson would do this work.

INSERT INTO dw.dim_customer (
    source_system, source_customer_id,
    customer_name, region_name,
    valid_from, valid_to, is_current
)
VALUES (
    'sales_app', 45,
    'Customer D', 'North',
    DATE '2025-01-01', NULL, TRUE
);

Now rerun the entire fact-load transaction, beginning with BEGIN and the temporary staging-table creation; do not rerun the one-time destination-table setup. The batch again includes the original six lines, and its candidate selection also considers any backlog line. Afterward, you should see six published source line IDs, backlog row count zero, and line 203 attached to customer 45’s North version.

Finally, the product details for P20 can be filled without changing its surrogate key or updating line 205:

UPDATE dw.dim_product_fact_demo
SET product_name = 'Accessory',
    is_inferred = FALSE
WHERE source_system = 'sales_app'
  AND source_product_id = 'P20'
  AND is_inferred = TRUE;

The result is the main benefit of a unique inferred member: its known source product ID was retained, so later attributes enrich the same dimension row that the early fact already references. Monitor inferred rows that remain unresolved; creating one placeholder for every permanently invalid source ID would otherwise accumulate misleading dimension members. Similarly, monitor aged backlog rows and escalate references that never acquire a valid historical customer version.

Key takeaways

An atomic fact load publishes one row per declared source event, resolves each source-qualified business key to a warehouse surrogate key, and uses a Type 2 dimension’s business-date validity window rather than its current-row flag. It must distinguish absent identifiers from present-but-unresolved ones.

This lesson used two deliberate late-arrival policies: a durable backlog for an unresolved historical customer version and a product-specific inferred member for a product whose descriptive data arrived late. Source-line uniqueness, lookup-cardinality checks, foreign keys, and transactional publication make those policies testable rather than merely aspirational.

Next, you will query across two fact tables sharing conformed dimensions, where preserving each fact table’s grain is just as important as preserving it during this load.