System design

Sources: DDIA Ch. 1, 7–9, 12 · Kimball (warehouse architecture)

Start with requirements (DDIA)

• Data volume, growth rate, retention
• Latency needs (batch OK vs real-time)
• Consistency vs availability trade-offs
• Who consumes the data (BI, ML, ops, external API)

Reliability, scalability, maintainability

• Fault tolerance — replicas, retries, idempotent writes, dead-letter queues
• Scalability — partition by key; avoid single hot spot; measure tail latency not just averages
• Evolvability — schema evolution (Avro/Protobuf), backward-compatible changes, versioned pipelines

Transactions & consistency (DDIA Ch. 7–9)

• ACID — atomicity, consistency, isolation, durability (OLTP systems)
• Linearizability — strongest consistency; expensive in distributed systems
• Eventual consistency — replicas converge; common in distributed stores
• Most data pipelines aim for at-least-once + idempotency rather than perfect distributed transactions

Design framework

1. Sources → ingestion → storage → processing → serving
2. Draw data lineage — where does each field originate?
3. Identify failure modes — what happens if a stage retries or runs twice?
4. Name trade-offs explicitly — cost, complexity, latency, correctness

Kimball warehouse architecture

• Staging area → ETL/conform → dimensional presentation (star schemas)
• Separate marts per department possible, but share conformed dimensions via the bus
• Data quality and audit dimensions track lineage and load metadata