Warehouse Architectures & Data Marts

A data mart: the warehouse, scoped down

A full enterprise warehouse is big and serves everyone; most user groups only need a slice. A data mart is *a data warehouse that is limited in scope* — a smaller, focused subset customized for the decision-making needs of a particular end-user group (marketing, finance, supply chain). The branch-library analogy is apt: same idea as the main library, smaller and tuned to a specific audience. The critical question is *where a mart gets its data*, and that answer is the architecture.

Three architectures

1. Independent data marts. Each mart runs its own ETL straight from the operational source systems — there is no central warehouse in the middle. Fast to deploy and politically easy (no enterprise-wide agreement needed), but it *recreates the very fragmentation warehouses were meant to cure*: now you have inconsistent marts instead of inconsistent operational databases. Its five limitations:

1. Separate ETL for every mart → costly, redundant extract/transform work repeated per mart.
2. Inconsistency across marts → built with different tools/definitions, the numbers disagree.
3. No cross-mart drill-down → you can't relate facts in one mart to detail in another.
4. Excessive scaling cost → each new mart repeats the entire extraction from scratch.
5. Prohibitive cost to reconcile afterward → making separate marts consistent later is extremely expensive.

2. Dependent data marts & the EDW (hub-and-spoke / Corporate Information Factory). A single enterprise data warehouse (EDW) is built first as the hub — the integrated, normalized, historical *control point and single source of truth*. Source systems feed it (input spokes); dependent data marts are filled exclusively from the EDW (output spokes). This cures all five independent-mart problems — one ETL, one consistent definition, cross-mart analysis — at the cost of building the EDW up front. An operational data store (ODS) often sits alongside: integrated, *current-valued, continuously updateable* detail for operational decision support, and frequently the staging area for loading the EDW.

3. Logical data marts & the real-time warehouse. A logical data mart is just a relational *view* of the EDW — no separate physical database, no separate load, never stale. New marts appear instantly as new views. A real-time data warehouse takes near-real-time source feeds and can relay decision rules back to operational systems (fraud detection, dynamic pricing).

Reconciled vs. derived data

The three-layer architecture stores three kinds of data:

operational data  → in the source systems        (current, application-scoped, varied quality)
reconciled data   → in the EDW / ODS             (detailed, current, NORMALIZED, comprehensive, quality-controlled)
derived data      → in the data marts            (selected, formatted, AGGREGATED for a user group)

Reconciled data is the cleaned, integrated, authoritative foundation; derived data is the further transformation of it into the denormalized, aggregated form a specific analytical audience consumes. Users touch derived data; reconciled data is what makes it trustworthy.

Worked example: the four steps to an *independent* data mart

The sample exam asks for the steps to build an independent data mart specifically. Because it bypasses any EDW, the work is the full ETL pipeline aimed straight at one mart:

1. Map / model    — decide the mart's scope and design; document which source
                    elements map to which mart columns (the metadata/mapping).
2. Extract        — capture the needed subset directly from the operational
                    source systems (static for the first load, incremental after).
3. Scrub & transform — cleanse the data (fix errors/inconsistencies) and convert
                    it to the mart's target format (select, join, aggregate).
4. Load & index   — write the cleansed, transformed data into the mart and build
                    its indexes for query performance.

That is the same ETL you'll formalize in Lesson 9.4 — for an independent mart it simply runs once per mart, source-to-mart, with no warehouse in between (which is exactly why it doesn't scale).