Data Storage Architectures
Data lakes, warehouses, and lakehouses
Modern Data Storage Evolution
As organizations collect more data from diverse sources, traditional databases alone aren't enough. Modern data architectures have evolved to handle massive scale, varied data types, and complex analytics. Data warehouses provide structured analytics, data lakes offer flexible raw storage, and data lakehouses combine the best of both worlds. Understanding these architectures is crucial for building scalable data platforms.
The Evolution of Data Storage
1980s-1990s: Data Warehouses
Structured, curated data for business intelligence and reporting
2000s-2010s: Data Lakes
Store everything raw and cheap, figure out structure later
2020s: Data Lakehouses
Best of both: flexibility of lakes + structure of warehouses
Data Warehouses
Structured, optimized for analytics
A data warehouse is a centralized repository of integrated data from multiple sources, optimized for analysis and reporting. Data is cleaned, transformed, and structured before loading (ETL - Extract, Transform, Load). Think of it as a highly organized library where everything is cataloged and easy to find.
Key Characteristics
📊 Structured Schema
Data follows predefined tables and relationships (star/snowflake schema)
🎯 Optimized Queries
Built for fast analytical queries (OLAP) with aggregations and joins
🧹 Clean Data
Data is transformed and validated before storage (high quality)
📈 Historical Data
Designed for time-based analysis and trend reporting
Data Modeling Designs
Most common warehouse design with fact tables (metrics) surrounded by dimension tables (context).
┌─────────────────┐
│ DIM_DATE │
│ date_key (PK) │
│ date │
│ month, quarter │
└────────┬────────┘
│
┌────────────────┐ │ ┌─────────────────┐
│ DIM_CUSTOMER │ │ │ DIM_PRODUCT │
│ customer_key │ │ │ product_key │
│ name, region │ │ │ name, category │
└────────┬───────┘ │ └────────┬────────┘
│ │ │
│ ┌──────────▼──────────┐ │
└────► FACT_SALES ◄─────┘
│ date_key (FK) │
│ customer_key (FK) │
│ product_key (FK) │
│ quantity │
│ revenue │
│ profit │
└─────────────────────┘Star Schema
Simple, denormalized design for fast queries
Star schema is the simplest dimensional model, consisting of one central fact table surrounded by dimension tables. The fact table contains measures and foreign keys to dimensions, while dimensions hold descriptive attributes. It resembles a star shape.
Key Characteristics
⭐ Central Fact Table
Contains quantitative measures and keys to dimensions
🌟 Denormalized Dimensions
Flat tables with all attributes, minimizing joins
🚀 Query Performance
Optimized for star-join queries in OLAP
🛠 Easy to Understand
Intuitive for business users and BI tools
Pros & Cons
Pros
- Fast query performance
- Simple design
- Easy to implement hierarchies
- Good for reporting
Cons
- Data redundancy
- Less normalized
- Update anomalies possible
- Larger storage needs
Snowflake Schema
Normalized extension of star schema
Snowflake schema normalizes dimension tables into multiple related tables, reducing redundancy. It looks like a snowflake with the fact table at the center and normalized dimensions branching out.
Key Characteristics
❄️ Normalized Dimensions
Dimensions split into sub-tables
💾 Reduced Redundancy
Less data duplication than star
🔗 More Joins
Requires additional joins for queries
📊 Complex Hierarchies
Better for multi-level dimensions
Pros & Cons
Pros
- Efficient storage
- Easier updates
- Less redundancy
- Better data integrity
Cons
- Slower queries (more joins)
- More complex design
- Harder for BI tools
- Increased maintenance
Galaxy Schema
Complex, multi-fact design
Also known as fact constellation schema, galaxy schema features multiple fact tables sharing common dimension tables. It's used for complex reporting across related business processes.
Key Characteristics
🌌 Multiple Fact Tables
Several interconnected facts
🔗 Shared Dimensions
Conformed dimensions across facts
📊 Complex Analysis
Supports cross-fact queries
🏗 Scalable Design
For enterprise-wide data marts
Pros & Cons
Pros
- Handles complex relationships
- Reuses dimensions
- Supports multiple views
- Enterprise scalability
Cons
- More complex queries
- Higher design effort
- Potential performance issues
- Maintenance challenges
Popular Platforms
Snowflake
Cloud-native, auto-scaling, separation of storage/compute
BigQuery
Google's serverless, petabyte-scale, SQL interface
Redshift
AWS managed, columnar storage, MPP architecture
Synapse
Azure's analytics service, unified experience
Example: Warehouse Query
SELECT
d.quarter,
p.category,
SUM(f.revenue) AS total_revenue,
AVG(f.profit) AS avg_profit
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year = 2024
GROUP BY d.quarter, p.category
ORDER BY total_revenue DESC;Q4 | Electronics | $2.5M | $450K
Q4 | Furniture | $1.8M | $320K
Q3 | Electronics | $2.1M | $380K
(Fast aggregation across millions of rows)
When to Use
- Business intelligence and reporting
- Historical analysis and trends
- Structured data with known schema
- Complex SQL queries and joins
- Regulatory compliance requiring governance
- Need for high query performance
Pros & Cons
✅ Pros
- Extremely fast analytics queries
- High data quality and governance
- Optimized for BI tools
- Well-understood SQL interface
- Strong consistency guarantees
- Great for compliance/auditing
❌ Cons
- Expensive storage costs
- Schema must be defined upfront
- ETL pipelines are complex
- Difficult to handle unstructured data
- Not suitable for real-time data
- Vendor lock-in with proprietary formats
Data Lakes
Store everything raw, transform on read
A data lake is a centralized repository that stores all structured and unstructured data at any scale. Data is stored in its raw format and transformed only when needed (ELT - Extract, Load, Transform). Think of it as a massive parking lot where you dump everything, you organize it later when you need it.
Key Characteristics
📦 Schema-on-Read
No predefined schema, structure is applied when data is read
💰 Low-Cost Storage
Built on cheap object storage (S3, Azure Blob, GCS)
🔄 All Data Types
Structured, semi-structured, unstructured (CSV, JSON, Parquet, images, logs)
🔬 Exploratory Analysis
Great for data science, ML, and discovering new insights
Architecture: Zones/Layers
Data lakes typically organize data into zones representing data maturity.
┌───────────────────────────────────────────────────────┐ │ DATA LAKE (S3/ADLS) │ ├───────────────────────────────────────────────────────┤ │ │ │ ┌──────────────┐ ┌──────────────┐ ┌─────────────┐ │ │ │ RAW / BRONZE │ │ PROCESSED / │ │ CURATED / │ │ │ │ ZONE │──► SILVER ZONE │──►GOLD ZONE │ │ │ │ │ │ │ │ │ │ │ │ • Raw files │ │ • Cleaned │ │ • Business │ │ │ │ • No schema │ │ • Validated │ │ ready │ │ │ │ • Immutable │ │ • Enriched │ │ • Aggregated│ │ │ └──────────────┘ └──────────────┘ └─────────────┘ │ │ │ └───────────────────────────────────────────────────────┘
Popular Platforms
AWS S3
+ Athena for queries, Glue for ETL
Azure Data Lake
+ Databricks, Synapse for processing
Google Cloud Storage
+ BigQuery, Dataproc for analytics
Hadoop HDFS
On-premise distributed file system
Example: Querying Raw Data
Store Raw JSON Logs
// Upload to S3
aws s3 cp application.log s3://my-data-lake/raw/logs/2024/01/15/
// File contains raw JSON
{"timestamp": "2024-01-15T10:30:00", "user_id": "123", "action": "purchase", "amount": 49.99}
{"timestamp": "2024-01-15T10:31:15", "user_id": "456", "action": "view", "product": "laptop"}
{"timestamp": "2024-01-15T10:32:00", "user_id": "123", "action": "review", "rating": 5}Raw logs stored in S3, no transformation yet
Query with Athena (Schema-on-Read)
CREATE EXTERNAL TABLE logs ( timestamp STRING, user_id STRING, action STRING, amount DOUBLE, product STRING, rating INT ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://my-data-lake/raw/logs/2024/01/15/'; -- Now query it like a table SELECT action, COUNT(*) as count, AVG(amount) as avg_amount FROM logs WHERE action = 'purchase' GROUP BY action;
purchase | 156 | $67.45
(Schema applied at query time, not storage time)
When to Use
- Storing massive amounts of raw data cheaply
- Machine learning and data science workloads
- Unstructured data (logs, images, videos)
- Exploratory analysis and discovery
- Long-term data retention and archival
- Diverse data sources with unknown future uses
Pros & Cons
✅ Pros
- Very cheap storage
- Flexible, store anything
- Scalable to petabytes
- Great for ML/data science
- No upfront schema design
- Separation of storage and compute
❌ Cons
- Can become "data swamps" (unorganized)
- Poor data quality without governance
- Slower queries than warehouses
- No ACID transactions
- Difficult to ensure data consistency
- Requires skilled engineers to query effectively
Data Lakehouses
Best of both worlds: flexibility + performance
A data lakehouse combines the flexibility and cost-effectiveness of data lakes with the performance and structure of data warehouses. It adds a metadata and governance layer on top of cheap object storage, enabling ACID transactions, schema enforcement, and fast queries, all while maintaining the ability to store any data type.
Key Characteristics
⚡ ACID Transactions
Support for transactions on data lake storage (Delta Lake, Iceberg, Hudi)
📋 Schema Enforcement
Optional schema validation while keeping schema evolution flexibility
🔄 Time Travel
Query historical versions of data for auditing and recovery
🎯 Fast Queries
Warehouse-like performance with indexing and optimization
Architecture: Open Table Formats
Lakehouses use open table formats that add structure and governance to data lakes.
┌─────────────────────────────────────────────────────┐ │ LAKEHOUSE ARCHITECTURE │ ├─────────────────────────────────────────────────────┤ │ │ │ ┌─────────────────────────────────────────────┐ │ │ │ Query Engines (Spark, Presto, Trino) │ │ │ └────────────────────┬────────────────────────┘ │ │ │ │ │ ┌────────────────────▼────────────────────────┐ │ │ │ Table Format (Delta Lake / Iceberg / Hudi) │ │ │ │ • ACID transactions │ │ │ │ • Schema enforcement │ │ │ │ • Indexing & stats │ │ │ │ • Time travel │ │ │ └────────────────────┬────────────────────────┘ │ │ │ │ │ ┌────────────────────▼────────────────────────┐ │ │ │ Object Storage (S3, ADLS, GCS) │ │ │ │ • Parquet/ORC files │ │ │ │ • Cheap, scalable │ │ │ └─────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────┘
Popular Platforms & Formats
Databricks
Delta Lake format, unified analytics platform
Apache Iceberg
Open format, multi-engine support, Netflix-created
Apache Hudi
Upserts and incremental processing, Uber-created
Example: Delta Lake Operations
Create Delta Table
-- Create table with ACID guarantees CREATE TABLE customer_orders ( order_id STRING, customer_id STRING, order_date DATE, amount DECIMAL(10,2) ) USING DELTA LOCATION 's3://my-lakehouse/tables/customer_orders';
Delta table created with transaction log for ACID operations
ACID Updates (Impossible in Pure Data Lake)
-- Update existing records atomically UPDATE customer_orders SET amount = amount * 1.1 WHERE order_date >= '2024-01-01';
Updated 15,234 rows atomically (all or nothing)
Time Travel
-- Query data as it was at a specific version SELECT * FROM customer_orders VERSION AS OF 3; -- Or by specific timestamp SELECT * FROM customer_orders TIMESTAMP AS OF '2024-01-15T10:00:00';
Returns historical snapshot for auditing or rollback
Upserts (Merge)
MERGE INTO customer_orders AS target USING new_orders AS source ON target.order_id = source.order_id WHEN MATCHED THEN UPDATE SET target.amount = source.amount WHEN NOT MATCHED THEN INSERT *;
Updated 50 existing orders, inserted 200 new orders (single transaction)
Schema Evolution
-- Add new column without rewriting all data ALTER TABLE customer_orders ADD COLUMN discount_applied BOOLEAN;
Schema updated, new column added (old records have NULL values)
When to Use
- Need both analytics AND ML/data science
- Want warehouse performance at lake costs
- Require ACID transactions on data lake
- Streaming and batch processing together
- Need to update/delete data efficiently
- Want unified architecture (one platform)
Pros & Cons
✅ Pros
- Combines lake flexibility + warehouse performance
- ACID transactions on cheap storage
- Schema enforcement + evolution
- Time travel and versioning
- Open formats (no vendor lock-in)
- Unified platform for all workloads
❌ Cons
- Newer technology (less mature)
- Requires Spark or similar engine
- More complex to set up than lake/warehouse
- Still evolving best practices
- Metadata overhead
- Learning curve for operations
Side-by-Side Comparison
| Feature | Data Warehouse | Data Lake | Data Lakehouse |
|---|---|---|---|
| Data Types | Structured only | All types | All types |
| Schema | Schema-on-write (predefined) | Schema-on-read (flexible) | Both (enforced + flexible) |
| Storage Cost | High ($$) | Low ($) | Low ($) |
| Query Performance | Excellent | Good to Poor | Very Good |
| ACID Transactions | ✅ Yes | ❌ No | ✅ Yes |
| Data Quality | High (validated) | Variable | High (optional enforcement) |
| Use Cases | BI, reporting, dashboards | ML, data science, archives | All of the above |
| Users | Business analysts | Data scientists, engineers | Everyone |
| ETL Pattern | ETL (transform before load) | ELT (transform after load) | Both |
| Updates/Deletes | Easy | Difficult (rewrite files) | Easy (ACID support) |
Choosing the Right Architecture
Choose Warehouse When...
- Data is all structured
- Schema is stable and known
- Need fastest query performance
- BI/reporting is primary use case
- Budget allows higher costs
- Strong governance required
Choose Lake When...
- Lots of unstructured data
- Exploratory/experimental work
- ML and data science focus
- Need cheapest storage
- Schema unknown/changing
- Long-term archival
Choose Lakehouse When...
- Need both BI AND ML/DS
- Want unified platform
- Require ACID on cheap storage
- Streaming + batch together
- Open formats preferred
- Modern, future-proof choice
Real-World Data Platform Architecture
Most organizations use a combination of these architectures. Here's a modern data platform:
┌────────────────────────────────────────────────────────────────┐
│ DATA SOURCES │
│ Databases | APIs | Logs | IoT Devices | Web Events | Files │
└────────────────────┬───────────────────────────────────────────┘
│
┌──────────▼──────────┐
│ INGESTION LAYER │
│ Kafka | Firehose │
│ Airflow | Dagster │
└──────────┬──────────┘
│
┌───────────────┼───────────────┐
│ │ │
┌────▼─────┐ ┌─────▼──────┐ ┌────▼─────────┐
│ LAKEHOUSE│ │ CACHE │ │OPERATIONAL DB│
│ (Delta │ │ (Redis) │ │(PostgreSQL) │
│ Lake) │ │ │ │ │
│ │ └────────────┘ └──────────────┘
│ Bronze │
│ Silver │ ┌──────────────────────────────┐
│ Gold │ │ PROCESSING ENGINES │
└────┬─────┘ │ Spark | Flink | dbt │
│ └──────────────────────────────┘
│
│ ┌──────────────────────────────┐
└────────► SERVING LAYER │
│ │
│ BI Tools ML Models │
│ (Tableau) (SageMaker) │
│ │
│ APIs Dashboards │
│ (REST) (Streamlit) │
└──────────────────────────────┘Layer Breakdown
Ingestion
Collect data from all sources, handle both streaming and batch
Storage (Lakehouse)
Central repository with Bronze (raw) → Silver (cleaned) → Gold (curated) zones
Processing
Transform data using Spark for big data, dbt for SQL transformations
Serving
Expose data via BI tools, APIs, ML models, and custom applications
Emerging Pattern: Data Mesh
Data Mesh is a newer architectural paradigm that treats data as a product, with domain teams owning their data. Instead of one central data lake/warehouse, each domain has its own data products that others can consume.
Domain Ownership
Teams own their data end-to-end
Data as Product
Treat data like software products
Self-Serve Platform
Infrastructure as a platform
Federated Governance
Global standards with local control
Key Takeaways
- Data Warehouses prioritize structure and performance for analytics but lack flexibility.
- Data Lakes offer cost-effective storage for diverse data but risk becoming ungovernable swamps.
- Data Lakehouses merge benefits, enabling ACID on scalable storage for modern workloads.
- Real-World Platforms layer ingestion, processing, and serving around a central lakehouse.
- Data Mesh decentralizes ownership, treating data as products for organizational scale.
What's Next?
With storage architectures understood, dive into building data pipelines in the next lesson.