Data warehouse concepts and architecture
Clear, exam-focused notes on data warehouse concepts and architecture: definitions, architectures, components, diagrams to draw, formulas, worked numerical ETL/storage examples, common mistakes and exam tips.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
Data warehouses let engineers combine data from many operational systems into a single place for analysis and reporting. Understanding warehouse concepts and architecture helps you design scalable ETL pipelines, choose the right schema and hardware, and meet business reporting SLAs.
Key ideas
Start with clear definitions (each bold term is defined before use):
Operational system (OLTP system): a system built for day-to-day transaction processing (examples: banking transaction DB, order-entry system). OLTP systems emphasise fast writes, concurrency and short transactions.
Analytical system (OLAP system): a system built for analysis and reporting over historical data. OLAP emphasises read performance for complex queries (aggregations, joins) and stores time-variant, integrated data. Know OLAP storage modes for exams: ROLAP (relational), MOLAP (multidimensional/cubes), HOLAP (hybrid).
Data warehouse: a central repository that stores integrated, subject-oriented, time-variant, non-volatile data to support decision making. Precise statement (useful for exams): "A data warehouse is a subject-oriented, integrated, time-variant, non-volatile collection of data in support of management's decision-making process."
- Assumptions: data arrives from multiple source systems; data is cleaned and integrated before loading; queries need history and consistency.
Define the four classic properties used to identify a data warehouse (state each exactly as exam answers expect):
- Subject-oriented: data is organized by major subjects (customers, products, sales) rather than by application.
- Integrated: data from different sources is made consistent (uniform naming, formats, units, keys).
- Time-variant: historical data is kept; each record has a time dimension (e.g., valid-from, snapshot time).
- Non-volatile: once entered into the warehouse, data is not frequently updated or deleted; changes happen via new records (append-only historical storage).
ETL (Extract, Transform, Load): the process that brings data from sources into the warehouse:
- Extract: read data from source systems.
- Transform: clean, deduplicate, conform (create consistent keys and formats), apply business rules, aggregate if needed.
- Load: write data to the warehouse or data marts.
- Note: Modern warehouses often use ELT (Extract, Load, Transform), loading raw data first and performing transformations inside the warehouse engine for scalability.
Staging area: temporary storage (often file system or separate DB) where extracted data is placed before transformation. Purpose: isolate source systems, allow checkpoints and retries.
Metadata: data about data. Types used in warehouses:
- Business metadata (business definitions, measures meanings);
- Technical metadata (schemas, ETL logic, data lineage);
- Operational metadata (load times, job histories).
Data mart: a subset of a data warehouse oriented to a particular line of business (e.g., sales data mart). Two design types:
- Dependent data mart: built from a central enterprise data warehouse.
- Independent data mart: built directly from source systems.
Architectures (exact, exam-style statements and assumptions):
- Basic/Two-layer architecture: sources -> ETL -> data warehouse. Use when small organisation or simple reporting.
- Three-tier architecture: (1) data sources and staging, (2) data warehouse repository (integrated store), (3) front-end tools (OLAP, reporting). Draw and label (see diagram instructions below). Assumption: separation of storage, processing, and presentation.
- Hub-and-spoke (centralized enterprise warehouse with data marts): central warehouse is the hub; data marts are spokes that get data from the hub. Use when needing both central consistency and departmental marts.
- Federated/virtual warehouse: warehouse is a logical layer that leaves data in source systems but provides integrated views. Use when physical consolidation not possible.
- Operational Data Store (ODS): an integrated, current-valued store that consolidates operational data for short-term, near–real-time reporting and can serve as a staging/consolidation layer feeding the data warehouse.
Performance & operational concerns: data freshness (latency), load window, query response time, compression, indexing, partitioning, and aggregation strategies (materialized views, summary tables, cubes). Define:
- Load/refresh window: allowed time to complete ETL loads (e.g., nightly 2:00–6:00). Also called the refresh or load window. If ETL time > window, you must optimize or increase the window.
- Latency: time between source event and its availability in the warehouse.
Where to draw schema diagrams (for exam): if asked to show architecture, draw three boxes vertically: top box labeled "Sources (OLTP, external feeds)" with arrows to middle box; middle box labeled "Staging area -> ETL -> Warehouse (integrated store, metadata repository)"; bottom/right box labeled "Presentation: OLAP, reports, dashboards, data marts". Label arrows: Extract, Transform, Load, and Data marts flow from warehouse.
Note on related topics: schema designs such as star, snowflake, and fact constellation are separate topics in this course and will be covered later. Also ETL details and metadata repository are separate units.
Formulas
S(t) = S0 · (1 + g)^t — where S(t) is data volume after t years, S0 is current volume (GB), g is annual growth rate (decimal). Units: GB, years. Applies when growth compounds annually.
T_load = V / R — where T_load is time to load data (seconds), V is volume to load (bytes), R is ETL pipeline throughput (bytes/second). Units: seconds (or convert to hours). Applies assuming constant throughput and no concurrency limits.
T_query = T_io + T_cpu + T_network — approximate total query response time (seconds); T_io is disk I/O time, T_cpu is processing time, T_network is transfer time. Units: seconds. Applies as a simple linear model for performance budgeting.
Storage_required = Raw_volume · (1 - c) + Overhead — where c is compression fraction (e.g., 0.6 means 60% reduction), Overhead includes indexes and materialized aggregates (GB). Units: GB. Applies when using compression and storing auxiliary structures.
(Note: exact units depend on whether you use GB = 10^9 bytes or GiB = 2^30 bytes; verify which your exam expects.)
Worked examples
Storage and growth calculation (numerical):
- Problem: three source systems produce 500 GB, 200 GB and 300 GB of integrated data today. Annual growth expected 20%. Warehouse stores data for 5 years and uses compression to reduce stored size to 40% of raw. How much storage is needed after 5 years assuming compound growth in annual volume and 5-year retention?
- Step 1: total current annual volume S0 = 500 + 200 + 300 = 1000 GB/year.
- Step 2: cumulative uncompressed volume over 5 years is a geometric series:
Sum_5 = S0 · ((1 + g)^5 − 1) / g = 1000 · (2.48832 − 1) / 0.20 = 7441.6 GB. - Step 3: apply compression to 40%:
Storage_required = 0.40 · 7441.6 = 2976.64 GB. - Final answer: 2976.64 GB (≈2.98 TB).
- Notes: this assumes 5-year rolling retention with compound growth in yearly volume and a constant compression rate.
ETL window and throughput (practical numeric check):
- Problem: you must load 1.0 TB (1000 GB) into the warehouse nightly within an 8-hour window. Your ETL pipeline sustained throughput is 30 MB/s. Can you finish within the window?
- Step 1: convert throughput to GB/hour. 30 MB/s = 30 × 3600 MB/hour = 108000 MB/hour = 108 GB/hour (using MB = 10^6 bytes and GB = 10^9 bytes).
- Step 2: required time
T_load = V / R = 1000 GB / 108 GB/hour = 9.259... hours. - Final answer: No — it requires ≈9.26 hours, which exceeds the 8-hour window.
- Options: increase ETL throughput (parallelism), extend window, or perform incremental loads.
Short model answer (design):
- Question: "Sketch a three-tier data warehouse architecture and explain the role of each tier."
- Model answer (what to write/draw): Draw three horizontal layers from top to bottom. Top: "Data Sources (OLTP DBs, flat files, APIs)" with arrows labelled "Extract" going down to middle layer. Middle: "Staging Area -> ETL -> Data Warehouse (Integrated store)" and a side box "Metadata repository" connected to ETL and warehouse. Bottom: "Presentation Layer: OLAP servers, reporting tools, dashboards, data marts" with arrows from warehouse to presentation labelled "Load / Serve". Explain each box's role in 2–3 lines. This gets full marks if you label ETL, staging, metadata, data marts, and state non-volatility and time-variant nature of the warehouse.
Common mistakes
- Not defining ETL steps or mixing transform logic with OLAP operations.
- Using MB/MiB or GB/GiB inconsistently in calculations without stating assumptions.
- Ignoring metadata and lineage when describing architecture.
- For ETL scheduling problems: forgetting to convert units (seconds ⇄ hours) and forgetting parallelism/overhead.
For your exam
Question types you will see: definitions (short), short notes (differences OLTP vs OLAP), architecture diagrams (draw and label), numerical load/storage calculations, ETL scheduling problems, and design/justification answers (choose hub-and-spoke vs independent marts and justify).
What full-marks answers include:
- For definitions: exact property words (subject-oriented, integrated, time-variant, non-volatile) and a one-line definition.
- For diagrams: neatly drawn layered box diagram labelled with sources, staging, ETL, warehouse, metadata, data marts, OLAP/reporting. Label arrows (Extract, Transform, Load).
- For numericals: show formulas, unit conversions, substitute numbers, carry units through, final bold answer and clear assumptions (e.g., MB = 10^6 bytes).
- For design questions: state requirements, pick an architecture, justify choices (consistency, autonomy, latency), mention metadata and governance.
Quick check
- Give the four properties that define a data warehouse.
- What are the three steps of ETL? Define each briefly.
- If an ETL throughput is 50 MB/s, how many GB can it load in 6 hours? (use MB = 10^6 bytes, GB = 10^9 bytes)
Answers: 1) Subject-oriented, Integrated, Time-variant, Non-volatile. 2) Extract (read from sources), Transform (clean and integrate), Load (write into warehouse). 3) 50 MB/s = 50×3600 = 180000 MB/hour = 180 GB/hour; in 6 hours = 180×6 = 1080 GB.
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?