Lesson 4 — Architecture, Storage Roles & History
Think in roles, not database names
A DW/BI architecture explains where data comes from, where it goes, when and why it moves, how it is transformed, where history persists, how consumers access it, and what cross-cutting controls keep the environment reliable.
A useful lifecycle picture is:
SOURCE → STAGE → INTEGRATE / TRANSFORM → STORE HISTORY → PRESENT → ANALYZE → MONITOR / MAINTAIN
Security, Metadata, lineage, Data Quality, availability, scalability, retention, and operations span the flow.
Staging
Purpose: temporary/intermediate preparation.
Typical work: land extracts/events, profile, cleanse, standardize, enrich, join, reconcile, transform, validate, and prepare for controlled loading.
The strongest clue is preparation rather than end-user analytical consumption. A staging database can physically persist for operational reasons, but its architectural role remains intermediate.
Central/atomic warehouse
Purpose: persistent integrated history at useful atomic grain.
It holds the historical analytical foundation from which multiple presentations and summaries can be derived. Physical performance optimizations may be added after requirements are known.
ODS
Purpose: current/near-current integrated operational use.
Lower latency, shorter history, and more volatility distinguish it from the historical warehouse.
Data Mart
Purpose: subject/process/user-oriented analytical presentation or subset.
A Finance profitability mart, for example, narrows and organizes governed analytical data for a defined community.
Cube / OLAP structure
A cube supports multidimensional analytical access. Classic implementation families are: - ROLAP — relational; - MOLAP — multidimensional; - HOLAP — hybrid.
A cube is not the same thing as a mart: the mart is a data/presentation store; the cube is an OLAP structure/access optimization.
Preserve history deliberately
The current package uses a history ladder:
Current state → point-in-time state → atomic history → summaries → predictive/advanced use
The lesson is not that every system must keep infinite history. It is that the required time horizon, grain, auditability, and reproducibility must come from business requirements.
“Summarize first” is risky because summaries discard dimensions of future inquiry. Preserve the useful atomic foundation, then create aggregates or precomputed outputs for demonstrated performance/usability needs.
Data Vault recognition
Chapter 11 mentions Data Vault as a historical pattern that can retain granular normalized historical relationships/keys and support traceability and later rebuilding/adaptation of dimensional presentations. For Fundamentals-level questions, recognize its contribution to atomic history, auditability, and adaptable downstream presentation rather than treating it as a replacement for all Chapter 11 architecture decisions.
Stop and check
Staging vs Mart: temporary preparation vs targeted analytical consumption.
ODS vs DW: current/low latency/more volatile vs historical/stable.
Mart vs Cube: analytical subset/store vs multidimensional access structure.
Source anchor: pp. 365–372.