Data warehouse design

A shop wants last year’s sales by product and region. Its order tables record current transactions, but a report also needs an agreed meaning for a sale, the relevant historical region and a way to correct a failed data load. This shop is an illustrative example. Start with those questions before choosing staging tables, reporting tables or a new database product.

The workload is the whole argument#

A checkout changes one order; the annual report combines many orders. OLTP, online transaction processing, names the first workload. OLAP, online analytical processing, names the second. One engine may serve both. The row and column layout example separates query access patterns from the schema decision made here.

Normalisation reduces duplicated facts and the updates needed to keep copies consistent. It does not make incorrect data impossible or turn every write into one row. The warehouse still receives writes: source records can be late, duplicated or corrected, and dimensions can change. Preparing data for reports moves work into the loading process; it does not remove that work.

Microsoft’s warehouse architecture example includes batch and streaming ingestion, quality checks and prepared analytical layers. Those are options rather than a required vendor stack. A star schema can help express the report, but it neither requires a column store nor guarantees that every query scans most rows.

What you decide before you write any SQL#

For a dimensional model, settle four questions before relying on its reports. They describe the data contract, not a mandatory number of warehouse layers.

-- Illustrative load: prepare a stable staging batch first.
-- Supply the same bound :run_date_key in both statements.
BEGIN;
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;
COMMIT;
-- On an error before COMMIT, ROLLBACK the transaction.

The transaction boundary matters as much as the date filter. Without it, a failure after DELETE can leave that date empty. With a rollback, the old rows remain until a complete load commits. This simplified example assumes a stable staging batch and one writer for the date; it does not solve duplicate source rows, concurrent loaders or changing input. PostgreSQL’s transaction guide explains why the two statements must commit together.

Where it goes wrong#

A copy or read replica of the application database can be a useful reporting starting point. It retains the source’s schema, so check whether that schema and its available history answer the question. The database and warehouse comparison explains when reporting isolation is enough and when integrated history is needed.

Keep the simpler copy if its reports and freshness meet the requirements. Introduce a prepared analytical model when a specific history, integration or reporting problem calls for it. A warehouse label, a star diagram or a successful load does not establish that its metrics are correct.

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.