TutorialKart.comData warehouse architecture defines how operational data moves from source systems through ingestion, staging, transformation, storage, and presentation layers. The exact design varies by organisation: a warehouse may use batch or streaming ingestion, an operational data store, one central repository, multiple data marts, or a combination of these components.
A well-defined architecture separates operational workloads from analytical workloads and gives reporting tools a consistent source of historical, integrated data. This tutorial explains the principal layers, common architecture types, data marts, ETL and ELT processing, and top-down and bottom-up implementation approaches.
Data Warehouse Architecture Layers and Data Flow
Most data warehouse designs can be understood as a flow from business applications to analytical users. Individual products may combine or rename layers, but the responsibilities remain broadly similar.
- Data source layer: Supplies data from transactional databases, ERP and CRM applications, files, APIs, logs, sensors, and external services.
- Data ingestion layer: Extracts or receives source data through batch jobs, change data capture, file transfers, APIs, or event streams.
- Staging area: Temporarily holds data before it is validated and transformed. Staging tables commonly preserve source-level detail for processing and recovery.
- ETL or ELT layer: Cleans, standardises, deduplicates, integrates, and applies business rules to the data.
- Data storage layer: Retains integrated historical data in warehouse tables, dimensional models, or other analytical structures.
- Data logic and semantic layer: Defines measures, dimensions, hierarchies, calculations, and shared business terminology.
- Data presentation layer: Makes governed data available to reports, dashboards, OLAP tools, ad hoc queries, analytics applications, and data science workloads.
- Metadata layer: Records schemas, definitions, mappings, lineage, ownership, refresh schedules, and data quality information.
- Operations and governance layer: Handles orchestration, monitoring, access control, auditing, backup, recovery, retention, and performance management.
ETL and ELT in Data Warehouse Architecture
ETL means extract, transform, and load. Data is extracted from source systems, transformed in a processing or staging environment, and then loaded into the warehouse. This approach is useful when data must be cleaned, filtered, or reshaped before it reaches warehouse tables.
ELT means extract, load, and transform. Source data is first loaded into the target analytical platform and transformed there. ELT can take advantage of the processing capacity of a modern warehouse, but it still requires governance, quality checks, and controls for sensitive raw data.
| Process | Operation order | Typical design consideration |
|---|---|---|
| ETL | Extract → Transform → Load | Transformation occurs before data is placed in final warehouse structures. |
| ELT | Extract → Load → Transform | Transformation runs inside the target analytical platform after ingestion. |
Single-Tier, Two-Tier, and Three-Tier Data Warehouse Architecture
Data warehouse architecture types are often described by the number of logical tiers between source data and analytical users. A tier is a separation of responsibilities, not necessarily a separate physical server.
Single-Tier Data Warehouse Architecture
A single-tier design attempts to minimise intermediate layers and may provide analytical access close to the stored data. It can be simpler for a limited use case, but tight coupling between storage, transformation, and reporting makes workload isolation, governance, and scaling more difficult.
Two-Tier Data Warehouse Architecture
In a two-tier architecture, client reporting or analytical tools connect to a warehouse or data mart tier. This design can work for a small group of users, but direct client-to-warehouse connections may become harder to manage as data volume, concurrency, and the number of analytical tools increase.
Three-Tier Data Warehouse Architecture
A three-tier architecture separates storage, analytical processing, and user access:
- Bottom tier: Data sources, ingestion processes, staging facilities, and the warehouse database.
- Middle tier: OLAP services, semantic models, business rules, aggregates, or query-processing services.
- Top tier: Dashboards, reports, visualisation tools, ad hoc query interfaces, and other user applications.
This separation allows each tier to be governed and scaled according to its workload. The middle tier also helps different reports use consistent definitions for measures such as revenue, inventory, and customer count.
Data Warehouse Types by Organisational Scope
Warehouses can also be classified according to the business scope they serve.
- Centralised data warehouse: Integrated enterprise data is maintained in a central analytical repository. The warehouse may be queried directly or used to supply dependent data marts.
- Functional data warehouse: The warehouse supports a particular business function, such as finance, marketing, sales, or supply chain management.
- Federated data warehouse: Independently managed warehouses or analytical stores are connected through shared definitions, integration services, or a common access layer. Data may remain distributed rather than being copied into one physical database.
Basic Data Warehouse Architecture
In a basic architecture, data from operational source systems is integrated into a central warehouse. End users access the resulting data through reporting, query, or analytical tools.
The warehouse can retain detailed historical data as well as precomputed summaries. Metadata describes the warehouse structures and helps tools interpret tables, dimensions, measures, and relationships. Precomputed summaries can reduce the amount of work required for frequently executed analytical queries.
Data Warehouse Architecture with a Staging Area
A staging area provides temporary storage between source systems and the warehouse. Data can be validated, standardised, deduplicated, joined, and reconciled before it is loaded into production warehouse structures.
Staging also supports restartable loads and data-quality investigation. If a transformation fails, the ingestion process may not need to retrieve the same data from every operational system again. A staging area is generally an internal processing component and should not be treated as the normal reporting interface.
Data Warehouse Architecture with Staging and Data Marts
An organisation may add data marts for departments or subject areas such as sales, finance, or inventory. Each mart presents a focused set of facts, dimensions, and measures for its users.
A dependent data mart receives governed data from an enterprise warehouse. An independent data mart is populated directly from operational or external sources. Dependent marts usually make enterprise-wide consistency easier, while independent marts may be quicker to establish for a limited requirement but can create duplicated definitions and integration work.
Data Warehouse and Data Mart Differences
| Data Warehouse | Data Mart |
|---|---|
| Usually covers multiple departments or the enterprise. | Usually covers one department, subject area, or business process. |
| Integrates data from several operational domains. | Contains a focused subset of analytical data. |
| Supports an organisation-wide view and cross-functional analysis. | Supports a narrower set of reports and analytical questions. |
| May supply data to multiple dependent data marts. | May receive data from an enterprise warehouse or directly from source systems. |
| Can use normalised, dimensional, or hybrid storage models. | Frequently uses dimensional fact and dimension tables for analytical access. |
| Typically requires broader governance and integration rules. | Has a smaller scope but should still follow shared definitions and access controls. |
Top-Down and Bottom-Up Data Warehouse Approaches
Two established approaches describe how an organisation can structure warehouse development. The choice affects integration, modelling, delivery sequence, and governance.
| Bottom-Up Approach | Top-Down Approach |
|---|---|
| Commonly associated with Ralph Kimball. | Commonly associated with Bill Inmon. |
| Begins with dimensional data marts for defined business processes. | Begins with an integrated enterprise data warehouse. |
| Uses conformed dimensions to keep facts and dimensions consistent across marts. | Often maintains detailed enterprise data in a normalised model before supplying downstream marts. |
| Can deliver a scoped analytical capability incrementally. | Establishes the enterprise integration model early in the programme. |
| Requires coordination so independently delivered marts do not become incompatible silos. | Requires substantial cross-functional analysis and enterprise data governance. |
| Suitable when delivery is organised around prioritised business processes. | Suitable when an integrated enterprise view is the primary architectural requirement. |
Real implementations may combine these ideas. For example, an organisation can establish enterprise governance and shared data definitions while delivering dimensional subject areas in stages.
Operational Data Store in a Warehouse Architecture
An operational data store, or ODS, integrates current or near-current data from several operational systems. It is generally designed for operational reporting and short-term integrated views rather than long-term historical analysis.
An ODS does not automatically replace a data warehouse. The ODS may be updated frequently and hold limited history, while the warehouse preserves historical snapshots and supports analytical models. Some architectures use the ODS as an additional source for warehouse loading.
Data Warehouse Architecture Design Considerations
- Business questions: Identify the decisions, measures, dimensions, history, and reporting latency the warehouse must support.
- Source-system behaviour: Document schemas, ownership, update frequency, keys, deletion rules, and known data-quality issues.
- Batch and real-time needs: Select ingestion patterns based on required freshness instead of applying streaming to every dataset.
- Historical tracking: Define how changes to customers, products, accounts, and other dimensions will be preserved.
- Data quality: Establish validation, reconciliation, exception handling, and ownership for failed records.
- Security and privacy: Apply least-privilege access, encryption, masking, auditing, retention, and appropriate separation of sensitive data.
- Metadata and lineage: Record where data originated, which transformations were applied, and which reports depend on it.
- Performance: Consider partitioning, indexing or clustering, aggregates, workload isolation, caching, and query concurrency.
- Reliability: Design idempotent or restartable pipelines, monitoring, backup, recovery, and service-level objectives.
- Cost and growth: Estimate storage, compute, data movement, concurrency, maintenance, and expected volume growth.
Data Warehouse Architecture Review Checklist
- Are source systems, ingestion methods, owners, and refresh schedules documented?
- Does the design clearly distinguish staging, warehouse, ODS, and data mart responsibilities?
- Are ETL or ELT transformations traceable through metadata and data lineage?
- Are shared facts, dimensions, measures, and business terms defined consistently?
- Can warehouse loads be reconciled with source totals and restarted safely after a failure?
- Are historical-change rules defined for important dimensions and business events?
- Are access controls, sensitive-data handling, retention, auditing, backup, and recovery included?
- Has the design been tested against expected data volume, query concurrency, freshness, and cost?
Data Warehouse Architecture FAQs
What is three-tier architecture in a data warehouse?
Three-tier data warehouse architecture separates the warehouse database and ingestion processes in the bottom tier, OLAP or semantic processing in the middle tier, and reports, dashboards, and analytical tools in the top tier.
What is ETL in data warehouse architecture?
ETL is the process of extracting data from source systems, transforming it according to quality and business rules, and loading it into the data warehouse. It converts data from different sources into consistent analytical structures.
Why is a staging area used in a data warehouse?
A staging area temporarily stores extracted data so it can be validated, cleaned, combined, and transformed before loading. It also supports troubleshooting and restartable processing without exposing unfinished data to normal reporting users.
How is a data mart different from a data warehouse?
A data warehouse usually integrates data across several business domains, while a data mart focuses on a department, subject area, or business process. A mart may receive governed data from the warehouse or load data independently from source systems.
Which data warehouse architecture is suitable for a growing organisation?
There is no single architecture for every growing organisation. The appropriate design depends on source complexity, reporting latency, governance, data volume, skills, budget, and analytical workloads. A layered and modular design can allow ingestion, storage, transformation, and presentation components to evolve independently.
Summary of Data Warehouse Architecture
Data warehouse architecture connects operational data sources to governed analytical services through ingestion, staging, transformation, storage, semantic, and presentation layers. Basic architectures may use a central warehouse, while larger designs can add an operational data store, a staging area, dependent data marts, metadata services, and separate analytical tiers. The suitable combination depends on the required scope, freshness, history, governance, performance, and cost.
In this Data Warehouse tutorial, we examined data warehouse layers, architecture types, staging, ETL and ELT, data marts, and implementation approaches.