Data Storage Architectures

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

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

Data Modeling Designs

Most common warehouse design with fact tables (metrics) surrounded by dimension tables (context).

Popular Platforms

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;

When to Use

Pros & Cons

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

Architecture: Zones/Layers

Data lakes typically organize data into zones representing data maturity.

Popular Platforms

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}

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;

When to Use

Pros & Cons

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

Architecture: Open Table Formats

Lakehouses use open table formats that add structure and governance to data lakes.

Popular Platforms & Formats

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';

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';

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';

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 *;

Schema Evolution

-- Add new column without rewriting all data
ALTER TABLE customer_orders
ADD COLUMN discount_applied BOOLEAN;

When to Use

Pros & Cons

Side-by-Side Comparison

Choosing the Right Architecture

Real-World Data Platform Architecture

Most organizations use a combination of these architectures. Here's a modern data platform:

Layer Breakdown

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.