Production Data Modeling: Scale, Loading & Behavior

You can already design a correct model. Production asks harder questions. A senior interviewer asks five of them about any model:

1. How is it loaded, and in what order?
2. What breaks when data arrives late or twice?
3. How does it behave at one billion rows?
4. How do queries use its layout?
5. How do you know it is still correct next month?

Every size in this chapter, such as “one billion rows”, is an assumption. It shows the shape of a problem. It is not a measurement.

Recap: a surrogate key is a number the warehouse makes for each dimension row. A fact row stores it. SCD Type 2 (Slowly Changing Dimension, Type 2) gives every version of a customer its own key.

The shop data from the earlier chapters runs through this one. Asha has keys 1001 and 1006. Priya, a new customer, has key 1007.

A fact row stores a surrogate key, not a customer id. That key must already exist in the dimension. So the dimension loads first.

An inferred member is a placeholder row for a customer you do not know yet.

A load has three steps:

1. Load new and changed dimension rows.
2. Add an inferred member for every customer the dimension does not know.
3. Look up the surrogate key for each order and load the facts.

Staging is a table where raw rows land first. Today’s staging table has seven orders.

Order 107 is for customer 6, Priya. The dimension has no row for customer 6 yet.

Step 2 adds a placeholder row for customer 6. The new key comes from a sequence. A sequence is a counter in the database that gives the next number.

In this example the next number is 1007.

INSERT INTO dim_customer
  (customer_key, customer_id, name, country,
   valid_from, valid_to, is_current, is_inferred)
SELECT nextval('customer_key_seq'), s.customer_id,
       'Unknown', 'Unknown',
       MIN(s.order_date), DATE '9999-12-31', true, true
FROM stg_orders AS s
WHERE NOT EXISTS (
  SELECT 1 FROM dim_customer AS d
  WHERE d.customer_id = s.customer_id
)
GROUP BY s.customer_id;

Step 3 finds the key for each order. It uses the order date, so Asha’s two orders get two different keys:

SELECT s.order_id, d.customer_key,
       s.order_date, s.status, s.amount
FROM stg_orders AS s
JOIN dim_customer AS d
  ON  d.customer_id = s.customer_id
  AND s.order_date >= d.valid_from
  AND s.order_date <  d.valid_to
ORDER BY s.order_id;

You met this placeholder in Slowly Changing Dimensions (SCD 0–7). When the real row arrives, you update row 1007 in place. Here step 2 covers every unknown customer in one batch.

A load is idempotent when running it twice gives the same table. A plain INSERT is not. A retry would add order 107 a second time.

MERGE compares a batch with a table. It updates rows that match and inserts rows that do not.

Use MERGE on the business key. The business key is the id from the source system, here order_id.

A surrogate key made during the load cannot do this job. A retry makes a new number, so the row never matches.

The next batch has three orders. Order 105 is now paid for 22.00, a payment made up for this chapter.

Order 106 is unchanged. Order 107 is new.

MERGE INTO fact_orders AS t
USING stg_orders_keyed AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND (t.status IS DISTINCT FROM s.status
                  OR t.amount IS DISTINCT FROM s.amount) THEN
  UPDATE SET status = s.status, amount = s.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_key, order_date, status, amount)
  VALUES (s.order_id, s.customer_key, s.order_date,
          s.status, s.amount);

Now run the same batch again. Every order matches, and nothing differs. So nothing updates.

The table keeps the same seven rows.

IS DISTINCT FROM is a not-equal test that also works with NULL. NULL against 22.00 gives true. Two NULLs give false.

Here the status also changes, so the plain not-equal test, <>, would still catch order 105. But picture a batch where only the amount changes, from NULL to 22.00. With <> that test gives NULL, not true, and the update is skipped.

A source can send the same order twice. This happens after a retry.

Keep one row per order_id before the MERGE. We assume the newest copy is the right one.

The staging table has two copies of order 104. This query keeps the newest copy:

SELECT order_id, customer_id, status, amount, loaded_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY loaded_at DESC) AS rn
  FROM stg_orders_raw
) AS ranked
WHERE rn = 1
ORDER BY order_id;

A late fact is a different problem. It reaches the warehouse after its day.

Order 120 is for Asha. The order date is 2026-01-05, but the row is loaded on 2026-01-24. This order is made up for this chapter.

Look up the key with the order date, not the load date. On 2026-01-05 Asha lived in IN, so order 120 needs key 1001.

SELECT s.order_id, d.customer_key, s.order_date
FROM stg_late AS s
JOIN dim_customer AS d
  ON  d.customer_id = s.customer_id
  AND s.order_date >= d.valid_from
  AND s.order_date <  d.valid_to;

The next three sections leave order 120 out, to keep the numbers small. The summary section loads it.

The row belongs to an old date, 2026-01-05. Only that day’s numbers change. Many teams also reload the last few days on every run, to catch rows that are only a little late.

An accumulating snapshot has one row per order. Each step of the journey fills a date in that row. You know this from Fact Table Design.

That means rows are changed, not only added. Each row is inserted when the order is placed. Then it is updated about three times: paid, shipped, delivered.

Why is this costly at scale? A columnar engine stores each column apart, in files. Many engines never change a file in place.

To update one row, the engine rewrites the file that holds it. Some systems, such as Apache Iceberg and Delta Lake, can write a small delete file instead. They rewrite the data file later.

Imagine 1 billion orders. A partition is a part of the table stored apart, here one per order date. The next section shows more.

An order that ships three weeks later touches an old partition, and its files are rewritten.

The cost is the number of files touched, not the number of rows changed. Two small changes in two old partitions can cost two file rewrites.

Two habits keep this cost down. First, only update the open window, the orders that are not finished. Second, filter on the partition column in the MERGE, so the engine skips the old files:

MERGE INTO fact_order_journey AS t
USING stg_order_steps AS s
  ON  t.order_key = s.order_key
  AND t.ordered  >= DATE '2026-01-08'
WHEN MATCHED THEN UPDATE SET
  shipped   = COALESCE(s.shipped, t.shipped),
  delivered = COALESCE(s.delivered, t.delivered),
  days_to_deliver =
    COALESCE(s.delivered, t.delivered) - t.ordered;

COALESCE keeps the old date when the new batch has NULL. Date minus date gives a number of days in PostgreSQL. Other engines use a function such as DATEDIFF.

The filter has a cost. An order older than 2026-01-08 that changes later is skipped, so the rule must match your business. In a real load the date moves, for example the last 30 days.

If late steps are common, store one row per step instead. That table only gets new rows. Old rows never change.

Imagine fact_orders has 1 billion rows. A query for one week must not read all of them.

The last section showed partitions. Here fact_orders has one partition for each order_date. Pruning means the engine skips the partitions your filter rules out.

Clustering sorts the rows inside each partition by another column. Here it is customer_key.

This statement uses BigQuery words. Other engines use other words for the same idea:

CREATE TABLE fact_orders (
  order_id     INT64,
  customer_key INT64,
  order_date   DATE,
  status       STRING,
  amount       NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_key;

Our seven rows fall into seven daily partitions, one order each:

Now ask for paid revenue from 2026-01-10 up to, but not including, 2026-01-16:

SELECT SUM(amount) AS paid_revenue
FROM fact_orders
WHERE order_date >= DATE '2026-01-10'
  AND order_date <  DATE '2026-01-16'
  AND status = 'paid';

Not every filter helps. This table shows how four filters use the partitions:

Clustering works inside a partition. Many engines keep the smallest and largest value of each file.

A filter can then skip files whose range cannot match.

Picture all seven rows in one partition, three rows per file, sorted by customer_key. This layout is an illustration:

A query for customer_key 1003 can skip file 3, because its range is 1007 to 1007. Key 1003 sits in two files, so the engine reads both:

SELECT order_id, amount
FROM fact_orders
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-02-01'
  AND customer_key = 1003
ORDER BY order_id;

The engine first picks partitions by date. Then it skips files by customer_key. The order of filters in WHERE does not matter.

People ask for a customer by customer_id, not by key. Look up the keys first. Asha has two: filter with customer_key IN (1001, 1006).

A join to the dimension does not skip files in every engine.

There are two ways to find the dimension version for an order. You can search for it in every query. Or you can store the key on the fact at load time.

The first way is a range join. It matches customer_id, then tests that the order date falls between valid_from and valid_to. Here it gives revenue by country:

SELECT d.country, SUM(o.amount) AS paid_revenue
FROM orders AS o
JOIN dim_customer AS d
  ON  d.customer_id = o.customer_id
  AND o.order_date >= d.valid_from
  AND o.order_date <  d.valid_to
WHERE o.status = 'paid'
GROUP BY d.country
ORDER BY d.country NULLS LAST;

The second way uses the key stored on the fact. The version was chosen once, at load time. The query joins on one equal key:

SELECT d.country, SUM(f.amount) AS paid_revenue
FROM fact_orders AS f
JOIN dim_customer AS d
  ON d.customer_key = f.customer_key
WHERE f.status = 'paid'
GROUP BY d.country
ORDER BY d.country NULLS LAST;

At 1 billion orders, this matters. Many engines join fast on equal keys.

In the range join, the equal part matches every version of the customer. The date test then drops the wrong versions. You pay for this in every query.

A summary table stores the answer to a common question, already added up. A dashboard then reads a few rows, not 1 billion.

Here is daily_revenue. It has one row per order date, built from the paid rows of fact_orders:

The table holds counts and sums. You can add counts and sums across days. You cannot add averages, so store the parts of an average, not the average.

A dashboard query for 2026-01-10 up to 2026-01-15 reads two rows. It returns the same 40.50 as the fact table did:

SELECT SUM(paid_revenue) AS paid_revenue
FROM daily_revenue
WHERE order_date >= DATE '2026-01-10'
  AND order_date <  DATE '2026-01-16';

The summary becomes wrong when facts change. Late order 120 is paid, 12.00, on 2026-01-05.

Refresh only the dates the batch touched. Delete those dates, then insert them again:

DELETE FROM daily_revenue
WHERE order_date IN (SELECT order_date FROM stg_batch);

INSERT INTO daily_revenue
SELECT order_date, COUNT(*), SUM(amount)
FROM fact_orders
WHERE status = 'paid'
  AND order_date IN (SELECT order_date FROM stg_batch)
GROUP BY order_date;

Run the DELETE and the INSERT in one transaction. A transaction is a group of steps that succeed or fail together. Then a dashboard never sees the day missing.

A MERGE into daily_revenue, without the DELETE, has a gap. If the last paid order of a day is refunded, the GROUP BY returns no row for that day. The old summary row stays.

Change data capture (CDC) is a feed of every insert, update and delete in the source. You know it from the pipeline chapters.

Inserts load like the MERGE you saw before. Deletes need their own rule in the MERGE.

The op column says the kind of change: I is insert, U is update, D is delete.

This batch deletes order 105 and changes the amount of order 106 to 75.00. The new amount is made up for this chapter. Here is the batch:

One MERGE can apply both, using the op column:

MERGE INTO fact_orders AS t
USING stg_cdc AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED AND s.op = 'U' THEN
  UPDATE SET status = s.status, amount = s.amount
WHEN NOT MATCHED AND s.op <> 'D' THEN
  INSERT (order_id, customer_key, order_date, status, amount)
  VALUES (s.order_id, s.customer_key, s.order_date,
          s.status, s.amount);

One batch can hold several changes for one order, for example an insert and then an update. Keep only the last change per order_id, by its sequence number in the feed. Otherwise the MERGE fails, or it applies an old change.

The last change of a new order can be U. The insert rule still adds it, because it checks only that op is not D.

You can also keep the deleted row and mark it with an is_deleted flag. That is a soft delete. It keeps history, but every query must filter the flag.

A soft-deleted row still holds the data, so it is not an erasure.

Erase one person

A privacy law may require you to erase one person’s data. The rules differ by country and company, so ask your privacy team. This part shows only the modeling side.

Keep personal data in the dimension, not in the fact. Then you change one table, not every fact. Facts keep only the surrogate key and the amounts.

Ben is customer 2, key 1002. Erase the personal columns of customer 2. Use customer_id, so that every version of an SCD2 customer is covered:

UPDATE dim_customer
SET name = 'Erased', country = NULL
WHERE customer_id = 2;

If the source still links customer 2 to Ben, the data is only hidden, not anonymous. Your privacy team decides.

The warehouse table is only one place. Check the other places too: staging and raw files, summary tables that hold customer-level rows, backups, and time-travel history. Time travel keeps old versions until they expire.

A load can succeed and still leave bad data. Run three checks after every load. Each one finds a different fault.

This fact table came from a bad load. It has nine rows. Order 104 is there twice, and order 121 points at key 1099:

SELECT
  (SELECT COUNT(*) FROM stg_orders)  AS source_rows,
  (SELECT COUNT(*) FROM fact_orders) AS fact_rows;

The counts do not say which row is wrong. Check 2 looks for orphan keys. An orphan is a fact row whose key is not in the dimension:

SELECT f.order_id, f.customer_key
FROM fact_orders AS f
LEFT JOIN dim_customer AS d
  ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL;

Check 3 looks for duplicate business keys. It finds the extra row that check 1 counted:

SELECT order_id, COUNT(*) AS copies
FROM fact_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Make each check fail the load, or raise an alert, when it returns a bad result. Also run the summary total check from the summary section.

Also watch the number of rows loaded each day. A sudden drop often means a broken source.

Here is each production question and the answer from this chapter:

Test yourself. Cover the page and answer each question:

• Why must the inferred member exist before the fact lookup?
• Why does MERGE use the business key, not the surrogate key?
• Why is a late fact’s key looked up at the order date?
• Why does updating one old row cost a whole file rewrite?
• Which of the three checks finds a row that is missing a dimension?

Each exercise opens a drawing board. You draw the tables, and the review checks them against sample rows.

Start with “Interval readings, estimates, and bill corrections”. It is about corrections at volume.