Data Warehousing

Definition

A warehouse holds integrated historical data organised for analysis: typically dimensional models (facts and dimensions) in a columnar store, loaded on a schedule from operational systems.

Why separation is non-negotiable

Analytical queries scan enormous row counts, hold long-running reads, and are written by people who are not thinking about production. Running them against the operational database is the single most reliable way to cause an incident that looks inexplicable — a report at month-end saturating the primary while the application times out.

Beyond protection, the models genuinely differ. Operational schemas are normalised for correct writes; analytical schemas are denormalised for fast aggregation and, critically, for history. An operational system usually knows only the current state; a warehouse must answer "what did we believe last March", which requires deliberately recording change over time.

Dimensional modelling essentials

• Facts are measurements at a grain — one row per order line, per shipment, per click. Declaring the grain first is the most important modelling step, and getting it wrong causes double-counting that is discovered months later in a board report.
• Dimensions are the descriptive context — product, store, customer, date.
• Slowly changing dimensions handle attributes that change: overwrite (type 1) loses history; adding a new row with effective dates (type 2) preserves it. Choosing type 1 for something the business later needs historically is a common and expensive mistake.
• Conformed dimensions — one shared definition of "store" or "product" across all fact tables — are what make cross-domain analysis possible at all.

Industry example

Retail at Walmart's scale is the canonical warehousing problem, and it shows why the discipline exists. Analysis must join point-of-sale transactions, e-commerce orders, inventory movements, supply-chain events and store attributes — data originating in dozens of systems with different grains, different update frequencies and different definitions of the same word.

The hard part is not storage or query speed. It is definitional: what counts as a sale (is a return a negative sale?), what counts as a store (does a fulfilment centre count?), and what date a transaction belongs to (transaction date, settlement date, or fiscal date?). Those questions are resolved once, in the conformed dimensions, or they are resolved differently by every analyst, and then two reports disagree and trust collapses.

Failure scenarios

• The grain not declared, so a join fans out and every measure is inflated.
• Type 1 chosen where type 2 was needed, so historical reports change retroactively when a product is recategorised.
• Loading raw operational schemas and expecting analysts to model at query time, which means every analyst models differently.
• No lineage, so when a number looks wrong nobody can say where it came from.
• The warehouse becoming an operational dependency, serving a customer-facing feature and therefore inheriting an availability requirement it was never designed for.

Trade-offs

Bought: analytical performance, historical truth, one agreed set of definitions, and protection of operational systems. Sold: freshness (data is as of the last load), the cost of a second copy of everything, and a modelling discipline that requires ongoing ownership.

Interview question

"A finance report and a marketing report give different revenue figures for the same month. Where do you look, and what would have prevented it?"