A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data in support of management's decision-making process.
- Subject-Oriented: Data is organized around major subjects of the enterprise (e.g., customers, products, sales) rather than specific business processes.
- Integrated: Data from disparate sources (different operational systems, external data) is cleaned, transformed, and combined into a consistent format.
- Time-Variant: Data is associated with specific time periods (e.g., daily, monthly, yearly snapshots). Historical data is preserved, allowing for trend analysis.
- Non-Volatile: Once data is in the data warehouse, it is generally not updated or deleted. New data is added incrementally.
Data warehouses are typically characterized by star schemas or snowflake schemas, which optimize for analytical queries. These schemas involve:
- Fact Tables: Store quantitative measurements (metrics) of a business process (e.g., sales amount, quantity sold) and foreign keys to dimension tables.
- Dimension Tables: Store descriptive attributes related to the facts (e.g., product name, customer demographics, date details).

The heart of populating and maintaining a data warehouse is the ETL process:
1. Extract:
- Purpose: To read and gather data from various source systems (e.g., relational databases, flat files, spreadsheets, ERP systems, CRM systems).
- Process: Identifying relevant data, extracting it in raw form, and often loading it into a temporary staging area.
2. Transform:
- Purpose: To clean, cleanse, convert, and consolidate the extracted data into a consistent and usable format for the data warehouse. This is often the most complex and time-consuming step.
- Process:
- Data Cleansing: Handling missing values, correcting errors, removing duplicates.
- Data Formatting: Converting data types, standardizing units, resolving inconsistencies (e.g., different date formats).
- Data Integration: Combining data from multiple sources.
- Data Aggregation: Summarizing data (e.g., calculating daily sales totals from individual transactions).
- Derivation: Creating new calculated fields.
3. Load:
- Purpose: To deliver the transformed data into the data warehouse (and potentially data marts).
- Process: Loading data into the fact and dimension tables. This can be a full load (replace all data) or an incremental load (add new or changed data). Loading strategies are crucial for performance.

These are two fundamental paradigms for database system usage:
- OLTP (Online Transaction Processing):
- Purpose: Handles day-to-day operational tasks, such as sales transactions, order entry, banking withdrawals, etc.
- Characteristics:
- Focus: High volume of small, atomic transactions.
- Operations: Primarily INSERT, UPDATE, DELETE operations, with simple SELECTs.
- Data: Current, highly normalized data.
- Users: Many concurrent users.
- Performance Metric: Transaction throughput (transactions per second), response time for individual transactions.
- Examples: E-commerce systems, ATM systems, airline reservation systems.
- OLAP (Online Analytical Processing):
- Purpose: Supports complex analytical queries for business intelligence, reporting, and decision-making.
- Characteristics:
- Focus: Fewer, but very complex queries that read large amounts of historical data.
- Operations: Primarily SELECT queries involving aggregations, joins, and complex calculations (e.g., trends, comparisons).
- Data: Historical, summarized, denormalized data (often in star/snowflake schemas).
- Users: Fewer, but typically power users or analysts.
- Performance Metric: Query response time for complex analytical queries.
- Examples: Sales forecasting, market trend analysis, financial reporting.
Data warehouses are purpose-built for OLAP, complementing the operational OLTP systems rather than replacing them.