DWH Interview Question 31 - How do you handle late-arriving facts?

Data Warehouse Interview Q&A · Question 31

In this post we are going to cover late-arriving facts, and the one lookup mistake that quietly puts them in the wrong place.

A late-arriving fact is a fact that reaches the warehouse long after the event happened. A store upload gets stuck, a partner sends a file weeks later, or a correction comes in after month end. The fact itself is fine. The problem is which dimension row it should point to, because the dimension may have changed since the event.

We will load an old sale against an SCD Type 2 customer dimension, see the wrong lookup and the right one, keep both the sale date and the load date, and then measure how late the fact was.

Question 31 of the Real DWH Question and Answers series on TechBrothersIT.

On this page
  1. The interview answer
  2. Let's create some sample data
  3. Step 1: The wrong lookup - the current row
  4. Step 2: The right lookup - as of the sale date
  5. Step 3: Load the facts and keep both dates
  6. Step 4: How late was each fact?
  7. What else to refresh after the load
  8. Follow-up questions interviewers ask
  9. The full script

The interview answer

Say this first

A late-arriving fact is a fact that reaches the warehouse long after the event, for example a sale from March that is loaded in April. The key is to look up the dimension row that was valid on the event date, not the current row, store both the event date and the load date, and refresh any aggregates for the affected periods.

Let's create some sample data

We need an SCD Type 2 customer dimension, a staging table with the sales loaded on 2026-04-20, and an empty fact table that stores both a sale date and a load date.

Sample data — run this first
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS dim_customer;

-- SCD Type 2 customer dimension: Sara moved from Toronto to Vancouver on 2026-03-20
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,
  customer_id  VARCHAR(10),
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   CHAR(1)
);
INSERT INTO dim_customer VALUES
  (1, 'C101', 'Toronto',   '2025-01-01', '2026-03-19', 'N'),
  (2, 'C101', 'Vancouver', '2026-03-20', '9999-12-31', 'Y'),
  (3, 'C102', 'Dallas',    '2025-01-01', '9999-12-31', 'Y');

-- loaded on 2026-04-20; sale 6001 happened back on 2026-03-05 (a store upload was stuck)
CREATE TABLE stg_sales (sale_id INT, sale_date DATE, loaded_on DATE, customer_id VARCHAR(10), amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
  (6001, '2026-03-05', '2026-04-20', 'C101', 80.00),
  (6002, '2026-04-20', '2026-04-20', 'C101', 50.00);

CREATE TABLE fact_sales (
  sale_id      INT PRIMARY KEY,
  sale_date    DATE,
  load_date    DATE,
  customer_key INT REFERENCES dim_customer(customer_key),
  amount       DECIMAL(10,2)
);

Let's look at the customer dimension.

Query
SELECT * FROM dim_customer ORDER BY 1;
Result3 rows · 6 columns
customer_keycustomer_idcityvalid_fromvalid_tois_current
1C101Toronto2025-01-012026-03-19N
2C101Vancouver2026-03-209999-12-31Y
3C102Dallas2025-01-019999-12-31Y

Customer C101, Sara, has two versions. Key 1 is Toronto, valid until 2026-03-19. Key 2 is Vancouver, valid from 2026-03-20 and marked as the current row. C102 has a single row in Dallas.

Now the staged sales.

Query
SELECT * FROM stg_sales ORDER BY 1;
Result2 rows · 5 columns
sale_idsale_dateloaded_oncustomer_idamount
60012026-03-052026-04-20C10180.00
60022026-04-202026-04-20C10150.00

Both sales were loaded on 2026-04-20. Sale 6002 is from that same day, but sale 6001 happened back on 2026-03-05, when Sara still lived in Toronto. Its store upload was stuck. So which customer key should it get?

Step 1: The wrong lookup - the current row

Let's start with the mistake. Most daily fact loads look up the dimension by natural key and take the current row, is_current = 'Y'. Let's try that here.

Query
SELECT s.sale_id, s.sale_date, d.customer_key, d.city
FROM stg_sales s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current = 'Y'
ORDER BY s.sale_id;
Result2 rows · 4 columns
sale_idsale_datecustomer_keycity
60012026-03-052Vancouver
60022026-04-202Vancouver

Both sales get customer key 2, Vancouver. For sale 6002 that is right. But sale 6001 happened when Sara lived in Toronto, so Vancouver now gets 80.00 it never earned and Toronto loses it.

Nothing fails and nothing warns us. The error only shows up when someone compares the warehouse report with the store's own numbers.

Step 2: The right lookup - as of the sale date

Now let's look up the version that was valid on the sale date. Instead of is_current, we find the row where the sale date falls between valid_from and valid_to.

Query
SELECT s.sale_id, s.sale_date, d.customer_key, d.city
FROM stg_sales s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND s.sale_date BETWEEN d.valid_from AND d.valid_to
ORDER BY s.sale_id;
Result2 rows · 4 columns
sale_idsale_datecustomer_keycity
60012026-03-051Toronto
60022026-04-202Vancouver

Sale 6001 from 2026-03-05 gets key 1, Toronto. Sale 6002 from 2026-04-20 gets key 2, Vancouver. Each sale gets the version that was true when it happened.

This lookup also works for facts that arrive on time, so many teams use it for every fact load. Then late facts need no special code path at all.

Step 3: Load the facts and keep both dates

Now let's load the fact table with the as-of lookup. We store two dates: sale_date, when it happened, and load_date, when the row reached the warehouse.

Statement
INSERT INTO fact_sales
SELECT s.sale_id, s.sale_date, s.loaded_on,
       d.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND s.sale_date BETWEEN d.valid_from AND d.valid_to;

Now let's check the result.

Query
SELECT * FROM fact_sales ORDER BY sale_id;
Result2 rows · 5 columns
sale_idsale_dateload_datecustomer_keyamount
60012026-03-052026-04-20180.00
60022026-04-202026-04-20250.00

Sale 6001 is stored with key 1 and sale 6002 with key 2. Reporting uses sale_date, so the March sale lands in March. The load_date of 2026-04-20 tells us when we actually received it.

Step 4: How late was each fact?

With both dates in the fact table, finding late facts is a simple filter: the load date is after the sale date. Let's also calculate how many days late each one was.

Query
SELECT sale_id, sale_date, load_date,
       load_date - sale_date AS days_late
FROM fact_sales
WHERE load_date > sale_date
ORDER BY sale_id;
Result1 row · 4 columns
sale_idsale_dateload_datedays_late
60012026-03-052026-04-2046

Only sale 6001 comes back, and it arrived 46 days late. That is a stuck upload someone should look at, so this is a good query to track and alert on.

Note

Subtracting two dates gives a number of days in PostgreSQL and Oracle. In SQL Server use DATEDIFF(day, sale_date, load_date).

What else to refresh after the load

Loading the late row is not the end of the job. Three more things to think about:

  • Aggregates and snapshots - March totals were already calculated without this sale. Rebuild only the affected periods, and in partitioned tables reload only the affected partitions.
  • Closed periods - if finance has already closed March, many teams leave it alone. They post the late sale as an adjustment in the current period and keep the original sale date for analysis. Agree this rule with the business.
  • One code path - use the as-of lookup everywhere, so late and on-time facts are handled the same way.

Follow-up questions interviewers ask

Why not use is_current = Y for every load?

It only works for facts loaded on the day they happen; late facts would get the wrong version.

What if the period is already closed?

Many teams post the late fact as an adjustment in the current period and keep the original event date; agree the rule with finance.

How do you detect late facts?

Store load_date next to the event date and track rows where the load date is after the event date.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom
-- ========================================================================
-- Data Warehouse Interview Q&A #31 - Late-arriving facts
-- 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_q31;  SET search_path TO dwh_q31;
-- ========================================================================

-- 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;

-- SCD Type 2 customer dimension: Sara moved from Toronto to Vancouver on 2026-03-20
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,
  customer_id  VARCHAR(10),
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   CHAR(1)
);
INSERT INTO dim_customer VALUES
  (1, 'C101', 'Toronto',   '2025-01-01', '2026-03-19', 'N'),
  (2, 'C101', 'Vancouver', '2026-03-20', '9999-12-31', 'Y'),
  (3, 'C102', 'Dallas',    '2025-01-01', '9999-12-31', 'Y');

-- loaded on 2026-04-20; sale 6001 happened back on 2026-03-05 (a store upload was stuck)
CREATE TABLE stg_sales (sale_id INT, sale_date DATE, loaded_on DATE, customer_id VARCHAR(10), amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
  (6001, '2026-03-05', '2026-04-20', 'C101', 80.00),
  (6002, '2026-04-20', '2026-04-20', 'C101', 50.00);

CREATE TABLE fact_sales (
  sale_id      INT PRIMARY KEY,
  sale_date    DATE,
  load_date    DATE,
  customer_key INT REFERENCES dim_customer(customer_key),
  amount       DECIMAL(10,2)
);


-- Step 1: Wrong: look up the CURRENT customer row
SELECT s.sale_id, s.sale_date, d.customer_key, d.city
FROM stg_sales s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current = 'Y'
ORDER BY s.sale_id;

-- Step 2: Right: look up the row that was valid on the sale date
SELECT s.sale_id, s.sale_date, d.customer_key, d.city
FROM stg_sales s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND s.sale_date BETWEEN d.valid_from AND d.valid_to
ORDER BY s.sale_id;

-- Step 3: Load the facts with the as-of key, and keep both dates
INSERT INTO fact_sales
SELECT s.sale_id, s.sale_date, s.loaded_on,
       d.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND s.sale_date BETWEEN d.valid_from AND d.valid_to;

-- check the result
SELECT * FROM fact_sales ORDER BY sale_id;

-- Step 4: How late was each fact? (monitor the lag)
SELECT sale_id, sale_date, load_date,
       load_date - sale_date AS days_late
FROM fact_sales
WHERE load_date > sale_date
ORDER BY sale_id;

Key points

  • The fact arrives long after the event
  • Never look up the current row
  • Use the row valid on the event date
  • Store sale_date and load_date
  • Rebuild affected periods; agree a rule for closed ones
Next in this series

Question 32 — Slowly changing dimensions: types 0, 1, 2, 3 What are slowly changing dimensions? Explain Types 0, 1, 2 and 3

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.

Subscribe to: Post Comments (Atom)