In this post we are going to cover late-arriving dimensions, and how to load a fact when its dimension row has not shown up yet.
A late-arriving dimension happens when a fact arrives before its dimension row. A common example: a new customer buys something today, the point of sale system sends the sale to the warehouse right away, but the CRM only sends the new customer tomorrow. When the fact load looks up the customer's surrogate key, there is nothing to find.
We will see how a plain lookup silently loses that sale, compare the three usual ways teams handle it, and then build the recommended one, the inferred member, step by step in SQL.
Question 30 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- Let's create some sample data
- Step 1: The naive lookup drops a sale
- Three ways to handle it
- Step 2: Create the inferred member
- Step 3: Load the facts
- Step 4: The real customer arrives
- Step 5: The report is now correct
- The SCD Type 2 twist
- Follow-up questions interviewers ask
- The full script
The interview answer
A late-arriving dimension happens when a fact arrives before its dimension row, like a sale for a customer the CRM has not sent yet. The best practice is an inferred member: insert a placeholder dimension row with the natural key and a flag, load the fact with its real surrogate key, and overwrite the placeholder in place when the real data arrives.
The key idea: the fact gets its final surrogate key on day one, and only the small dimension row is fixed later.
Let's create some sample data
We need a customer dimension, a staging table with today's sales, and an empty fact table. The dimension already has an unknown member with key -1, and every row has an is_inferred flag.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(10), -- natural key from the CRM
name VARCHAR(50),
city VARCHAR(50),
is_inferred CHAR(1) -- 'Y' = placeholder created by the fact load
);
INSERT INTO dim_customer VALUES
(-1, 'N/A', 'Unknown', 'Unknown', 'N'),
( 1, 'C101', 'Sara Khan', 'Toronto', 'N'),
( 2, 'C102', 'Omar Ali', 'Dallas', 'N');
-- today's sales from the POS system; C104 is a brand-new customer
-- that the CRM has not sent to the warehouse yet
CREATE TABLE stg_sales (sale_id INT, sale_date DATE, customer_id VARCHAR(10), amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
(5001, '2026-04-01', 'C101', 40.00),
(5002, '2026-04-01', 'C104', 75.00),
(5003, '2026-04-02', 'C102', 30.00);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
customer_key INT REFERENCES dim_customer(customer_key),
amount DECIMAL(10,2)
);Let's look at the customer dimension first.
SELECT * FROM dim_customer ORDER BY 1;| customer_key | customer_id | name | city | is_inferred |
|---|---|---|---|---|
| -1 | N/A | Unknown | Unknown | N |
| 1 | C101 | Sara Khan | Toronto | N |
| 2 | C102 | Omar Ali | Dallas | N |
We know two real customers, C101 and C102, plus the unknown row. Now the sales waiting in staging.
SELECT * FROM stg_sales ORDER BY 1;| sale_id | sale_date | customer_id | amount |
|---|---|---|---|
| 5001 | 2026-04-01 | C101 | 40.00 |
| 5002 | 2026-04-01 | C104 | 75.00 |
| 5003 | 2026-04-02 | C102 | 30.00 |
Three sales for 145.00 in total. Sale 5002 is for C104, a brand-new customer that is not in dim_customer yet.
Step 1: The naive lookup drops a sale
Let's start with the lookup most people write first: join staging to the dimension on the natural key customer_id to get the surrogate key.
SELECT s.sale_id, d.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer d
ON d.customer_id = s.customer_id
ORDER BY s.sale_id;| sale_id | customer_key | amount |
|---|---|---|
| 5001 | 1 | 40.00 |
| 5003 | 2 | 30.00 |
Only two rows come back, 5001 and 5003. Sale 5002 has no matching customer, so the inner join simply drops it. We staged 145.00 but would load only 70.00, and there is no error and no warning. That is the dangerous part: the revenue just goes missing.
Three ways to handle it
Before we fix it, here are the three options teams usually choose from.
| Option | Totals today | Fix later |
|---|---|---|
| 1. Hold in staging | ✗ missing | retry the load |
| 2. Unknown member (-1) | ✓ correct | update the FACT keys |
| 3. Inferred member | ✓ correct | update one DIM row |
- Hold in staging - keep the fact back until the customer arrives. Nothing is wrong in the warehouse, but today's revenue is missing from reports and you need a retry process.
- Unknown member (-1) - load the fact against the unknown row. Totals are correct, but later you must find those facts and update their keys, and updating a large fact table is slow and risky.
- Inferred member - create the customer row now as a placeholder with a flag. The fact gets its final key today, and only one small dimension row is updated later. This is the Kimball recommendation.
Let's build option three.
Step 2: Create the inferred member
Now let's find every customer in staging that is not in the dimension yet and insert a placeholder for it. Each new row gets the next surrogate key, Unknown for the attributes it does not know yet, and is_inferred = 'Y'.
INSERT INTO dim_customer
SELECT m.max_key + ROW_NUMBER() OVER (ORDER BY s.customer_id),
s.customer_id, 'Unknown', 'Unknown', 'Y'
FROM (SELECT DISTINCT customer_id FROM stg_sales) s
CROSS JOIN (SELECT MAX(customer_key) AS max_key
FROM dim_customer) m
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d
WHERE d.customer_id = s.customer_id);Now let's check the result.
SELECT * FROM dim_customer ORDER BY customer_key;| customer_key | customer_id | name | city | is_inferred |
|---|---|---|---|---|
| -1 | N/A | Unknown | Unknown | N |
| 1 | C101 | Sara Khan | Toronto | N |
| 2 | C102 | Omar Ali | Dallas | N |
| 3 | C104 | Unknown | Unknown | Y |
C104 now has customer key 3. It is a real row with a real surrogate key, it just does not have real attributes yet. The NOT EXISTS check means customers we already know, like C101 and C102, are not touched.
Step 3: Load the facts
Now we run exactly the same kind of inner join as in Step 1, but this time to load the fact table.
INSERT INTO fact_sales
SELECT s.sale_id, s.sale_date, d.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer d
ON d.customer_id = s.customer_id;Now let's check the result.
SELECT * FROM fact_sales ORDER BY sale_id;| sale_id | sale_date | customer_key | amount |
|---|---|---|---|
| 5001 | 2026-04-01 | 1 | 40.00 |
| 5002 | 2026-04-01 | 3 | 75.00 |
| 5003 | 2026-04-02 | 2 | 30.00 |
All three sales are loaded, 145.00 in total, and sale 5002 points to customer key 3. Nothing was dropped, and nothing went to the unknown member.
Step 4: The real customer arrives
The next day the CRM sends C104: her name is Lina Park, from Boston. Let's update the placeholder row in place with the real name and city, and set the flag back to N. The WHERE clause only touches rows that are still inferred.
UPDATE dim_customer
SET name = 'Lina Park', city = 'Boston', is_inferred = 'N'
WHERE customer_id = 'C104'
AND is_inferred = 'Y';Now let's check the result.
SELECT * FROM dim_customer ORDER BY customer_key;| customer_key | customer_id | name | city | is_inferred |
|---|---|---|---|---|
| -1 | N/A | Unknown | Unknown | N |
| 1 | C101 | Sara Khan | Toronto | N |
| 2 | C102 | Omar Ali | Dallas | N |
| 3 | C104 | Lina Park | Boston | N |
Row 3 now shows Lina Park from Boston, with is_inferred set to N. The surrogate key is still 3, so the fact table does not need to change at all.
Step 5: The report is now correct
Finally, let's run a sales report by customer name.
SELECT d.name, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer d
ON d.customer_key = f.customer_key
GROUP BY d.name
ORDER BY sales DESC;| name | sales |
|---|---|
| Lina Park | 75.00 |
| Sara Khan | 40.00 |
| Omar Ali | 30.00 |
Lina Park shows her 75.00, Sara Khan 40.00 and Omar Ali 30.00. The sale was never lost, and the fact row was never updated. Only one dimension row changed.
The SCD Type 2 twist
If the customer dimension is SCD Type 2, should the real data create a new version of the row? No. The placeholder was never true, so we overwrite it in place, even for Type 2 columns. Versioning starts only after the real data has arrived.
Count the rows that still have is_inferred = 'Y' and alert if any stay inferred for days. It usually means the source system has a problem.
The reverse case, where the fact itself arrives late, is a different problem, and it is the next question in the series.
Follow-up questions interviewers ask
Why not just use the unknown member (-1)?
Totals are right, but you must later find and re-key those facts, which means updating a large fact table.
SCD Type 2 dimension: new version when real data arrives?
No. The placeholder was never true, so overwrite it in place; versioning starts after that.
How do you monitor inferred members?
Count rows with is_inferred = Y and alert if any stay inferred longer than expected.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #30 - Late-arriving dimensions
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q30; SET search_path TO dwh_q30;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(10), -- natural key from the CRM
name VARCHAR(50),
city VARCHAR(50),
is_inferred CHAR(1) -- 'Y' = placeholder created by the fact load
);
INSERT INTO dim_customer VALUES
(-1, 'N/A', 'Unknown', 'Unknown', 'N'),
( 1, 'C101', 'Sara Khan', 'Toronto', 'N'),
( 2, 'C102', 'Omar Ali', 'Dallas', 'N');
-- today's sales from the POS system; C104 is a brand-new customer
-- that the CRM has not sent to the warehouse yet
CREATE TABLE stg_sales (sale_id INT, sale_date DATE, customer_id VARCHAR(10), amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
(5001, '2026-04-01', 'C101', 40.00),
(5002, '2026-04-01', 'C104', 75.00),
(5003, '2026-04-02', 'C102', 30.00);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
customer_key INT REFERENCES dim_customer(customer_key),
amount DECIMAL(10,2)
);
-- Step 1: The naive lookup: an inner join silently drops the sale
SELECT s.sale_id, d.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer d
ON d.customer_id = s.customer_id
ORDER BY s.sale_id;
-- Step 2: Create an inferred member for every customer that is not in the dimension yet
INSERT INTO dim_customer
SELECT m.max_key + ROW_NUMBER() OVER (ORDER BY s.customer_id),
s.customer_id, 'Unknown', 'Unknown', 'Y'
FROM (SELECT DISTINCT customer_id FROM stg_sales) s
CROSS JOIN (SELECT MAX(customer_key) AS max_key
FROM dim_customer) m
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d
WHERE d.customer_id = s.customer_id);
-- check the result
SELECT * FROM dim_customer ORDER BY customer_key;
-- Step 3: Load the facts: now every sale finds its key
INSERT INTO fact_sales
SELECT s.sale_id, s.sale_date, d.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer d
ON d.customer_id = s.customer_id;
-- check the result
SELECT * FROM fact_sales ORDER BY sale_id;
-- Step 4: Next day: the CRM sends C104, so overwrite the placeholder in place
UPDATE dim_customer
SET name = 'Lina Park', city = 'Boston', is_inferred = 'N'
WHERE customer_id = 'C104'
AND is_inferred = 'Y';
-- check the result
SELECT * FROM dim_customer ORDER BY customer_key;
-- Step 5: The report is now correct, without touching the fact
SELECT d.name, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer d
ON d.customer_key = f.customer_key
GROUP BY d.name
ORDER BY sales DESC;
Key points
- Fact arrives before its dimension row
- A plain inner join silently drops the fact
- Inferred member: placeholder row + natural key + flag
- Fact gets its final key on day one
- Real data: overwrite in place, even for SCD2
Question 31 — Late-arriving facts How do you handle late-arriving facts?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.