Unit
- Definition: A data warehouse is a centralized repository that stores integrated data from multiple source systems for analysis and reporting. It is subject-oriented, integrated, time-variant, and non-volatile.
- Subject orientation: Data is organized around key business subjects such as customers, products, and sales, rather than around application processes. This enables cross-functional analysis.
- Integration: Data from heterogeneous sources is transformed into a consistent format with unified naming conventions, measurements, and encoding before loading.
- Time variance: The warehouse retains historical data over long periods, often years, to support trend analysis and time-based comparisons.
- Non-volatility: Once loaded, data is rarely updated or deleted. Read-only access is the norm, ensuring a stable and auditable data set.
- Business driver: Decision makers need a single version of truth to answer strategic questions. A data warehouse eliminates the silos and inconsistencies of operational reporting.
- Comparison with operational databases: OLTP systems handle many small, concurrent transactions with fast updates. A data warehouse (OLAP) optimizes for complex, large-scale analytical queries.
- Evolution: Modern warehouses incorporate cloud storage, real-time ingestion, and support for unstructured data, building on the classic Inmon and Kimball philosophies.
- Three-tier architecture: A common data warehouse architecture follows a three-tier model: the bottom tier consists of the database server that stores and manages the data; the middle tier includes an OLAP server for analytical processing; and the top tier provides front-end tools for reporting, visualization, and ad hoc queries. This layered separation ensures scalability, performance, and ease of use.
- ETL processes: ETL processes (Extract, Transform, Load) are fundamental to data warehousing. Extraction gathers raw data from various source systems. Transformation cleans, standardizes, and integrates the data, addressing inconsistencies and missing values. Loading inserts the transformed data into the warehouse tables, often using incremental updates to maintain efficiency while ensuring data quality and consistency.
- Data marts: Data marts are smaller, focused subsets of a data warehouse that serve specific business lines or departments. Dependent data marts are derived from the central warehouse, ensuring consistency and a single version of truth. Independent data marts are built directly from source systems, offering faster deployment and localized control but risking data silos. Both types improve query performance and simplify access for specialized analytical needs.
- Dimensional modeling: The star schema and snowflake schema are two common dimensional modeling techniques. The star schema features a central fact table joined directly to denormalized dimension tables, enabling simple and fast queries. The snowflake schema normalizes dimensions into multiple related tables, reducing data redundancy and storage costs but increasing query complexity. The choice depends on the balance between query performance and data maintenance.
Code Example
SQL
CREATE TABLE dim_customer (
customer_id INTEGER PRIMARY KEY,
customer_name VARCHAR(100),
customer_region VARCHAR(50),
created_date DATE
);
CREATE TABLE fact_sales (
sale_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES dim_customer,
product_id INTEGER REFERENCES dim_product,
sale_amount DECIMAL(10,2),
sale_date DATE
);This SQL shows a simple dimension table and a fact table in a star schema, illustrating the subject-oriented and integrated nature of warehouse tables.
Quick Revision
- A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data for decision support.
- It differs from OLTP systems by emphasizing read-heavy, analytical workloads over transaction throughput.
- Key business motivations include enabling a single version of truth and supporting historical trend analysis.
- Modern data warehouse architectures evolve from classic approaches to embrace cloud and real-time capabilities.