Data warehouse design

You have been handed a warehouse to design, or inherited one already running, and most of what you can find on the subject hands you a diagram instead of a reason: staging on the left, a core layer in the middle, marts on the right, arrows pointing forward. The diagram is not wrong, but it does not explain why any of those boxes exist rather than one. The reason underneath it is the workload. A system that is written to constantly, under a user’s hand, and read back a few rows at a time by key, is a different machine from one loaded on a schedule and read by scanning most of a column — and every decision that follows, from how a table is shaped to how a load reruns, is downstream of which of those two machines is actually being built.

The workload is the whole argument#

Put the two workloads side by side and the shape argues for itself before a single table gets named. The traditional label for the first is OLTP, online transaction processing; for the second it is OLAP, online analytical processing. The names sound like they describe two different technologies, but the distinction they draw is about what a system is asked to do, not about which product does it — the same relational engine can run either workload, and what actually changes is the pattern of reads and writes it sits under.

Normalisation is what buys the left column its cheap, non-duplicated writes: it makes every fact live in exactly one place, so a single write touches one row and nothing in the schema can disagree with itself. A warehouse does not have that problem. It has the opposite one — the row was already written, checked and settled, somewhere else, and the only work left is answering a wide, repeated question against many of them cheaply. That is the sentence the rest of this article hangs off: model for the reads, because the writes are already someone else’s problem.

What you decide before you write any SQL#

Four decisions get made before a single CREATE TABLE runs, and skipping one does not avoid it — it just means the decision gets made by accident, later, by whichever query happens to run first.

-- Scope both statements to the same partition key so the load can
-- run once or ten times for the same date and leave the same table.
DELETE FROM fact_orders
WHERE order_date_key = :run_date_key;

INSERT INTO fact_orders (
  order_date_key, customer_key, product_key, quantity, revenue
)
SELECT
  order_date_key, customer_key, product_key, quantity, revenue
FROM staging_orders
WHERE order_date_key = :run_date_key;

The pattern buys repeatability, not speed: deleting the target partition before inserting it means a rerun replaces what was there instead of adding to it, so a job that fails halfway through and gets restarted cannot leave the table holding two copies of the same date.

Where it goes wrong#

The warehouse gets built by copying the production schema and pointing a reporting tool at the copy. It is not a bad start, and for a while nothing about it looks wrong — the tables are already there, the joins already work, and a dashboard goes live fast. It keeps working while the data stays small and the questions stay close to the ones the operational schema already answered well.

The copy was never a warehouse. It was a second database with the same shape and none of the guarantees — no agreed grain, no versioned dimensions, no load that reruns safely — and the cost of not choosing a model up front does not disappear. It gets paid later, usually by whoever is debugging a dashboard that used to be right.

None of this makes copying production the wrong first move. It is often exactly the right one — cheap, fast, good enough for a while, before the real questions are fully known. What makes it wrong is leaving it there once somebody starts making a decision from what it says, because that is the moment an unmodelled copy stops being a shortcut and starts being a liability nobody agreed to take on.

Optimising for reads at the expense of writes, or the other way round, is not a decision unique to how a warehouse’s tables are modelled — the article on two storage engines built around opposite assumptions about reads and writes covers the same fork one layer down, inside a single engine rather than across a schema. The persistence path is the roadmap this article aligns with most closely.