Data Warehousing

A data warehouse is a database optimised for answering analytical questions over historical data from more than one source system. It exists because the databases that run a business are built for the opposite workload — many small transactions touching few rows, each as fast as possible — and running large aggregations against them is both slow and dangerous. The warehouse is where data is integrated, conformed to shared definitions, kept as history, and queried without risk to anything operational.

Why analytical databases are built differently

Transactional (OLTP) systems store rows contiguously, index heavily for point access, and optimise for concurrent small writes. Analytical (OLAP) systems store columns contiguously, because an analytical query reads few columns across very many rows. Columnar storage has three compounding effects: only the referenced columns are read from storage at all; values within a column are homogeneous and therefore compress far better than mixed row data; and compressed columnar data can often be filtered and aggregated without being fully decompressed.

On top of that sits data skipping. Each block of column data carries summary metadata — minimum and maximum values, null counts — so a query with a predicate can discard entire blocks without reading them. This is why the physical ordering of rows on disk matters so much in a warehouse: if rows for one customer or one day are scattered across every block, nothing can be skipped. Sorting or clustering the table by the columns people actually filter on is the single most effective performance lever available, and it is a data-layout decision rather than an index.

Most warehouses also separate storage from compute, so that data lives once and independent compute clusters read it concurrently. This is what allows a heavy backfill and an interactive dashboard to coexist without competing, and it changes the cost model: you are billed for work done rather than for a machine that exists, which makes query efficiency an ongoing financial concern rather than a one-off tuning exercise.

Dimensional modelling

The dominant warehouse modelling approach separates facts — the measurable events, such as an order line, a payment, a page view — from dimensions, the descriptive context by which those events are sliced: customer, product, date, channel. A fact table is long and narrow with numeric measures and foreign keys; dimension tables are shorter and wide with descriptive attributes. This arrangement, the star schema, is durable because it matches how analytical questions are actually asked: aggregate a measure, grouped and filtered by attributes.

Grain is the decision everything else follows from

The grain of a fact table is what exactly one row represents — one order line, one shipment, one day per account. Declaring it explicitly, in words, before writing any SQL is the highest-value discipline in warehouse modelling, because almost every double-counting bug in analytics is a grain error. Joining tables of different grain fans rows out and multiplies measures silently; nothing errors, the totals simply become wrong, and they are usually wrong in a direction that looks plausible. If a measure exists at a different grain from the rest of the table, it belongs in its own fact table.

Slowly changing dimensions

Dimension attributes change: a customer moves to another region, a product is recategorised. The question is whether history should be preserved. Type 1 overwrites, so all history is restated as if the current value had always applied — simple, and it silently rewrites last year's regional totals. Type 2 inserts a new version of the row with validity dates and a current flag, so facts joined on the version that was active at the time report what was actually true then. Type 2 costs more and is usually correct for anything used in reporting that people compare year over year. The mistake is choosing by convenience rather than by asking whether a restated past would mislead anyone.

Normalised, dimensional, or one big table

A highly normalised integration layer (the Inmon tradition) stores the enterprise model once and derives marts from it; it is resilient to change and demanding to build. A dimensional warehouse (the Kimball tradition) builds conformed facts and dimensions directly for analysis. The modern third option is the wide denormalised table, made viable by columnar storage and cheap compute: one table per subject with everything pre-joined, which is fast and simple to query and duplicates definitions everywhere. Most working warehouses are a hybrid — normalised staging, dimensional core, denormalised marts for specific consumers — and that is a reasonable outcome as long as each layer's purpose is stated.

The semantic layer and metric definitions

The most expensive failure in warehousing is not slow queries, it is two dashboards disagreeing about revenue. This happens when metric logic lives in the reporting tools rather than in the warehouse, so every tool, notebook and spreadsheet re-implements the same definition with slightly different filters. A semantic layer — metric definitions held once, in version control, and served to every consumer — removes an entire category of organisational argument. Concretely: keep the join keys, the filters (what counts as an active customer, whether refunds subtract) and the time grain in one place, and make the BI tool a presentation layer rather than a transformation layer.

Warehouse, lake and lakehouse

A warehouse manages its own storage and gives you transactions, a planner, governance and predictable performance in one integrated system. A data lake is files in object storage that any engine can read, with weak guarantees and maximum flexibility. The lakehouse pattern places an open table format over lake files to recover atomic commits, schema evolution and time travel while keeping storage open and engine-independent. The choice is mostly about reversibility and the mix of workloads: warehouses remain the better answer for concurrent, governed, SQL-first analytics; open formats matter more when machine learning and multiple engines need the same data. See big data architecture for how that decision interacts with the rest of the platform.

Failure modes

• Grain confusion. A join across mismatched grain fans out rows and multiplies measures. No error is raised; the number is simply inflated.
• Type 1 where Type 2 was needed. A reorganisation restates every historical report, and last year's numbers change without explanation.
• Unbounded scans. Queries without a filter on the clustering or partition key read the whole table. In a consumption-priced warehouse this is a cost incident, not just a slow query.
• SELECT * in a columnar store. It defeats the mechanism the entire architecture is built on, reading every column to display a handful.
• Dashboards doing the transformation. Business logic in the BI tool cannot be tested, versioned or reused, and it guarantees that definitions diverge.
• Full refresh forever. A model that rebuilds from the beginning of time works for a year and then becomes the longest job in the schedule; incremental models with a defined merge key are the fix, and they need the idempotency discipline described in data pipelines.
• Late-arriving facts against closed periods. Without a rolling reprocessing window, corrections never land and the warehouse permanently disagrees with the source.
• No ownership of tables. Nobody can say who owns a model or whether it is still consumed, so nothing is ever retired and every change is risky.

When a warehouse is the wrong answer

If all the data lives in one transactional database, the questions are simple aggregations, and the volume is modest, a read replica plus a few well-chosen indexes will serve analytics with far less machinery — see database administration for the operational side of that. A warehouse starts to earn its cost when data from multiple systems must be integrated under shared definitions, when history must be preserved that source systems overwrite, when analytical load would threaten the production database, or when enough people need self-service access that the definitions have to be centralised.

When you do build one, order the work by risk rather than by ambition: declare grain before modelling, conform the handful of dimensions that everyone uses before building marts, define metrics once, make every model incremental and idempotent, and put a freshness and row-count assertion in front of anything a person will act on. For the continuously-updating variant of the same problem, see real-time analytics.