Data Warehouse 101

data Strategic

data warehouse

design patterns

What is a Data Warehouse?

A centralized repository of integrated data from multiple sources, structured for query and analysis rather than transaction processing.

drawing

Dimension	OLTP Online Transaction Processing	OLAP Online Analytical Processing
Purpose	Operational tasks, transaction processing	Business intelligence, decision support
Data Structure	Normalized, entity-relationship model	Denormalized, star/snowflake schema
Users	Front-line workers, clerks, cashiers	Knowledge workers, analysts, managers
Operations	Simple, read/write operations	Complex, read-only queries
Response Time	Milliseconds - immediate	Seconds to minutes - analytical
Database Design	Application-oriented	Subject-oriented
Data Volume	GBs of current data	TBs to PBs of historical data
Examples	ATM, Order entry, POS systems	Financial analysis, sales forecasting

Dimension	Traditional Architecture	Modern Architecture
Processing	ETL (Extract, Transform, Load)	ELT (Extract, Load, Transform)
Storage	On-premise hardware	Cloud-native services
Data Model	Structured schemas	Flexible (structured + semi)
Updates	Batch processing	Real-time streaming
Access	Limited to BI tools	Multiple interfaces (APIS, SQL)

Star Schema
- Central fact table with dimension tables
- Denormalized dimension tables
- Simple structure, fast queries
- Best for Simple analytics
Snowflake Schema
- Normalized dimension tables
- Reduced redundancy, complex structure
- Better for maintenance
- Best for Complex dimensions
Galaxy Schema
- Multiple fact tables sharing dimensions
- Supports complex business models
- Also called Fact Constellation
- Best for Enterprise-wide

Transactional Fact Table
- Records individual transactions
- Captures at the lowest grain
- Most common fact table type
Periodic Snapshot Fact Table
- Captures state at regular intervals
- Contains semi-additive measures
- Good for trend analysis
Accumulating Snapshot Fact Table
- Tracks process milestones
- One row per business process
- Updated as process evolves

drawing

ETL (Extract - Transform - Load)

Key Characteristics
- Transforms data before loading
- Processing in separate server
- Better for complex transformations
ETL Preferred
- Complex transformations before loading
- Legacy systems integration
- Strict data quality requirements
- Regulatory compliance needs

ELT (Extract - Load - Transform)

Key Characteristics
- Transforms data after loading
- Processing in target system
- Better for large datasets
ELT Preferred
- Big data volumes processing
- Cloud-native environments
- Real-time data needs
- Flexible analytics requirements

Hybrid Approach

Data Pipelines
- Orchestrated data flow
- Automated data movement
- Components: Extract, Transform, Load
- Schema:
  - Batch: Scheduled processing
  - Streaming: Real-time processing
  - Micro-batch: Small frequent batches
Batch vs Real-time Processing
- Batch processing
  - High throughput
  - Lower cost
  - Complex transformations
- Real-time Processing
  - Low latency
  - Immediate insights
  - Event-driven
Lambda & Kappa Architecture
- Lambda
  - Dual paths: batch + speed layer
  - Lambda: Complex, fault-tolerant
- Kappa
  - Single path: streaming only
  - Kappa: Simpler, unified processing
Data Ingestion Methods
- Push-based: Source sends data
- Pull-based: Destination fetches data
- Change Data Capture: Tracks changes
- API-based: REST/GraphQL endpoints

Data Profiling
- Data assessment
- Analyzing data to discover content, structure, and relationships to identify quality issues
Data Validation
- Quality assurance
- Ensuring data meets business rules and requirements through automated checks
Data Cleansing
- Error correction
- Correcting or removing or inaccurate, incomplete, or inconsistent data
Metadata Management
- Data documentation
- Data Managing about data, to provide context, meaning, and usability
Data Lineage
- Data provenance
- Tracking data origin, movement, and transformation throughout its lifecycle
Master Data Management
- Data consistency
- Creating a single source of truth for criticalbusiness data

Partitioning
- Faster query scans
- Dividing large tables into smaller, manageable parts based on specific criteria
Columnar Storage
- Ideal for aggregation queries that access few columns across many rows
- Storing data by column rather than row to improve analytical query performance
Indexing
- Reduced I/O operations
- Creating data structures for faster data retrieval without scanning entire tables
Query Pushdown
- Minimized network overhead
- Moving computation closer to data to reduce data transfer and improve performance
Materialized Views
- Instant complex queries
- Pre-computed query results stored as physical objects for faster access

Caching
- Store frequently accessed data in memory
- Result set and metadata caching
- Automatic cache invalidation
- 10-100x faster queries
Clustering
- Physically organize data based on query patterns
- Co-locate related data together
- Automatic re-clustering maintenance
- Reduced I/O operations
Compute Scaling
- Adjust resources based on workload
- Vertical scaling (bigger nodes)
- Horizontal scaling (more nodes)
- Cost-performance balance
Query Execution Plans
- Visualize query processing steps
- Identify bottlenecks and inefficiencies
- Cost-based optimization
- Strategic query optimization

Lakehouse Architecture
- combines data lake & warehouse
- Single copy of data for multiple uses
- Supports BI & ML workloads
Delta Lake
- Brings ACID transactions to data lakes
- Time travel & version control
- Handles streaming & batch workloads
Fabric Warehouse
- Microsoft’s unified analytics platform
- Integrates Power BI, Synapse, Data Factory
- OneLake for all data

Data Mesh
- Decentralized data ownership
- Domain-oriented data products
- Self-serve data platform
Medallion Architecture
- Bronze: Raw data layer
- Silver: Cleansed & validated
- Gold: Business-ready tables
Reverse ETL
- oves processed data back to operational systems
- Enables actionable insights in business tools
- Creates data-driven workflows