{"title":"Chapter 5: OLTP vs OLAP","description":"","content":"# Chapter 5: OLTP vs OLAP\n\n## Introduction\n\nSo far we have compared databases by data model (relational vs non-relational) and query language (SQL vs NoSQL). But there is another dimension that fundamentally changes how databases are designed: the nature of the workload.\n\nWhen a customer places an order, you need to insert a row, check inventory, process payment, and update multiple tables. This is a transaction, a fast operation touching a few rows with strict consistency requirements. When an analyst asks \"What was our revenue by product category last quarter?\", the database must scan millions of rows and aggregate results. This is an analytical query, a slow operation reading massive amounts of data.\n\nThese two workloads have such different requirements that they typically run on different systems optimized for their specific needs.\n\nIn this chapter, you will learn:\n\n- The fundamental differences between OLTP and OLAP workloads\n- Why the same database cannot optimally serve both\n- How OLAP systems are architected differently from OLTP\n- Common patterns for moving data from OLTP to OLAP\n- Modern approaches that blur the line between the two\n\nUnderstanding this distinction is essential for system design because choosing the wrong system for a workload leads to either poor performance or unnecessary complexity.\n\n---\n\n## Defining OLTP and OLAP\n\n### OLTP: Online Transaction Processing\n\nOLTP systems handle the day-to-day operations of a business: placing orders, updating accounts, processing payments. They are the workhorses of production applications.\n\n\n```mermaid\nflowchart LR\n subgraph OLTP[OLTP Characteristics]\n O1[Many small transactions]:::primary\n O2[Insert, Update, Delete]:::primary\n O3[Few rows per query]:::primary\n O4[Low latency required]:::primary\n O5[High concurrency]:::primary\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n```\n\n\n**Example OLTP operations:**\n\n\n```sql\n-- Insert a new order (touches 3-5 rows)\nINSERT INTO orders (customer_id, total, status)\nVALUES (123, 99.99, 'pending');\n\nINSERT INTO order_items (order_id, product_id, quantity, price)\nVALUES (last_insert_id(), 456, 2, 49.99);\n\nUPDATE inventory SET quantity = quantity - 2\nWHERE product_id = 456;\n```\n\n\n### OLAP: Online Analytical Processing\n\nOLAP systems answer business questions through complex queries over large datasets. They power dashboards, reports, and data science.\n\n\n```mermaid\nflowchart LR\n subgraph OLAP[OLAP Characteristics]\n A1[Few complex queries]:::orange\n A2[Read-heavy, mostly SELECT]:::orange\n A3[Millions of rows scanned]:::orange\n A4[Latency tolerance seconds to minutes]:::orange\n A5[Lower concurrency]:::orange\n end\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n**Example OLAP operations:**\n\n\n```sql\n-- Revenue by category last quarter (scans millions of rows)\nSELECT\n p.category,\n DATE_TRUNC('month', o.order_date) as month,\n SUM(oi.quantity * oi.price) as revenue,\n COUNT(DISTINCT o.customer_id) as unique_customers\nFROM orders o\nJOIN order_items oi ON o.id = oi.order_id\nJOIN products p ON oi.product_id = p.id\nWHERE o.order_date >= '2024-01-01'\n AND o.order_date < '2024-04-01'\nGROUP BY p.category, DATE_TRUNC('month', o.order_date)\nORDER BY month, revenue DESC;\n```\n\n\n---\n\n## Side-by-Side Comparison\n\n\n| Characteristic | OLTP | OLAP |\n|----------------|------|------|\n| **Primary use** | Run the business | Analyze the business |\n| **Query type** | Simple, predefined | Complex, ad-hoc |\n| **Data freshness** | Current, real-time | Historical, periodic |\n| **Query latency** | Milliseconds | Seconds to minutes |\n| **Rows per query** | Few (1-100) | Millions to billions |\n| **Concurrency** | Thousands of users | Few analysts |\n| **Write pattern** | Frequent small writes | Bulk loads, rare writes |\n| **Data model** | Normalized (3NF) | Denormalized (star schema) |\n| **Primary metric** | Transactions per second | Query throughput |\n\n\n\n```mermaid\nflowchart TB\n subgraph Workloads[Workload Comparison]\n subgraph OLTPWork[OLTP Workload]\n OW1[User 1: Place order]:::primary\n OW2[User 2: Update profile]:::primary\n OW3[User 3: Check balance]:::primary\n OW4[User N: ...]:::primary\n end\n\n subgraph OLAPWork[OLAP Workload]\n AW1[Analyst: Monthly report]:::orange\n AW2[Dashboard: Real-time metrics]:::orange\n AW3[Data Scientist: Customer segments]:::orange\n end\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n---\n\n## Why Separate Systems?\n\nRunning OLTP and OLAP on the same database causes problems for both workloads.\n\n### The Problem with Mixing Workloads\n\n\n```mermaid\nflowchart TB\n subgraph Conflict[Workload Conflict]\n OLTP[OLTP Queries
Need fast responses]:::primary\n OLAP[OLAP Query
Scanning millions of rows]:::orange\n\n DB[(Single Database)]:::purple\n\n OLTP --> DB\n OLAP --> DB\n\n Result[Result:
OLTP slowed by OLAP
OLAP blocked by OLTP locks]:::red\n DB --> Result\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\n**Resource contention:**\n\n- OLAP queries consume CPU and memory, starving OLTP\n- Large scans pollute the buffer cache, evicting frequently-accessed OLTP data\n- Long-running analytics hold locks, blocking transactions\n\n**Optimization conflicts:**\n\n- OLTP benefits from row-oriented storage for quick point lookups\n- OLAP benefits from columnar storage for efficient aggregations\n- Indexes that speed up transactions slow down bulk scans\n- Normalization helps OLTP consistency but hurts OLAP query performance\n\n### The Solution: Separate Systems\n\n\n```mermaid\nflowchart TB\n App[Application]:::primary\n App --> OLTP[(OLTP Database
PostgreSQL, MySQL)]:::purple\n\n OLTP --> ETL[ETL Process]:::orange\n ETL --> OLAP[(OLAP Database
Data Warehouse)]:::green\n\n Users[Business Users]:::teal --> OLAP\n Dashboards[Dashboards]:::teal --> OLAP\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nThis separation allows each system to be optimized for its workload:\n\n- OLTP database tuned for transactions\n- OLAP database tuned for analytics\n- ETL process moves data periodically\n\n**Interview Insight:** When designing a system, consider whether analytics requirements can be satisfied by the OLTP database or need a separate OLAP system. The trigger is usually when analytical queries start impacting transactional performance or when you need historical analysis.\n\n---\n\n## OLTP Architecture\n\nOLTP databases are optimized for fast, concurrent transactions.\n\n### Row-Oriented Storage\n\nOLTP databases store data row by row. All columns for a record are stored together on disk.\n\n\n```mermaid\nflowchart TB\n subgraph RowStore[Row-Oriented Storage]\n Row1[\"Row 1: | id:1 | name:Alice | email:alice@ex.com | age:28 |\"]:::primary\n Row2[\"Row 2: | id:2 | name:Bob | email:bob@ex.com | age:35 |\"]:::primary\n Row3[\"Row 3: | id:3 | name:Carol | email:carol@ex.com | age:42 |\"]:::primary\n end\n\n Query[\"SELECT * FROM users WHERE id = 2\"]:::orange\n Query --> |\"One disk read\"| Row2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n**Why row storage works for OLTP:**\n\n- Fetching an entire record requires one disk seek\n- Updates touch a single location\n- Efficient for point queries and small ranges\n\n### B-Tree Indexes\n\nB-tree indexes enable fast lookups by any indexed column.\n\n\n```mermaid\nflowchart TB\n Root[Root Node
50]:::purple\n Root --> L1[Node
25]:::orange\n Root --> L2[Node
75]:::orange\n\n L1 --> D1[Data: 10, 20]:::primary\n L1 --> D2[Data: 30, 40]:::primary\n L2 --> D3[Data: 60, 70]:::primary\n L2 --> D4[Data: 80, 90]:::primary\n\n Query[\"WHERE id = 70\"]:::green --> Root\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**B-tree characteristics:**\n\n- O(log n) lookup time\n- Efficient range scans\n- Support for unique constraints\n- Overhead on writes (index maintenance)\n\n### Transaction Processing\n\nOLTP databases prioritize ACID transactions:\n\n\n```mermaid\nflowchart LR\n T1[Transaction 1]:::primary --> Lock1[Acquire Locks]:::orange --> Op1[Execute]:::orange --> Commit1[Commit]:::green --> Release1[Release Locks]:::green\n\n T2[Transaction 2]:::primary --> Wait[Wait for locks...]:::red --> Lock2[Acquire Locks]:::orange --> Op2[Execute]:::orange\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\n### Common OLTP Databases\n\n\n| Database | Strengths |\n|----------|-----------|\n| **PostgreSQL** | Feature-rich, extensible, excellent JSON support |\n| **MySQL** | Widely deployed, good read performance |\n| **SQL Server** | Enterprise features, Windows integration |\n| **Oracle** | High performance, enterprise support |\n| **MongoDB** | Document model, flexible schema |\n\n\n---\n\n## OLAP Architecture\n\nOLAP databases are optimized for complex analytical queries over large datasets.\n\n### Columnar Storage\n\nOLAP databases store data column by column. All values for one column are stored together.\n\n\n```mermaid\nflowchart TB\n subgraph ColStore[Column-Oriented Storage]\n Col1[\"id column: | 1 | 2 | 3 | 4 | 5 | ... |\"]:::primary\n Col2[\"name column: | Alice | Bob | Carol | Dave | ... |\"]:::orange\n Col3[\"age column: | 28 | 35 | 42 | 29 | ... |\"]:::green\n end\n\n Query[\"SELECT AVG(age) FROM users\"]:::purple\n Query --> |\"Read only age column\"| Col3\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n```\n\n\n**Why columnar storage works for OLAP:**\n\n- Queries typically read few columns but many rows\n- Reading one column avoids loading irrelevant data\n- Same-type values compress extremely well\n- Vectorized processing is more efficient\n\n### Compression in Columnar Databases\n\nColumnar data compresses much better than row data:\n\n\n```mermaid\nflowchart LR\n subgraph Raw[Raw Data: Country Column]\n R1[\"USA, USA, USA, Canada, Canada, USA, USA, ...\"]:::orange\n end\n\n subgraph Compressed[Run-Length Encoding]\n C1[\"USA (3), Canada (2), USA (2), ...\"]:::green\n end\n\n Raw --> |\"Compress\"| Compressed\n\n subgraph Savings[Compression Savings]\n S1[\"Often 10:1 or better\"]:::purple\n end\n\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n```\n\n\nCommon compression techniques:\n\n- **Run-length encoding:** Compress repeated values\n- **Dictionary encoding:** Map values to small integers\n- **Bit-packing:** Use minimum bits needed\n- **Delta encoding:** Store differences between values\n\n### Star Schema\n\nOLAP databases typically use denormalized star schemas for query performance.\n\n\n```mermaid\nflowchart TB\n Fact[(Fact Table:
Sales)]:::primary\n\n Fact --> Dim1[(Dimension:
Products)]:::orange\n Fact --> Dim2[(Dimension:
Customers)]:::orange\n Fact --> Dim3[(Dimension:
Time)]:::orange\n Fact --> Dim4[(Dimension:
Stores)]:::orange\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n**Star schema characteristics:**\n\n- Central fact table contains measures (revenue, quantity)\n- Dimension tables describe attributes (product name, customer region)\n- Joins are simple and predictable\n- Denormalization reduces join complexity\n\n\n```sql\n-- Star schema query\nSELECT\n d.calendar_year,\n p.category,\n s.region,\n SUM(f.revenue) as total_revenue\nFROM fact_sales f\nJOIN dim_time d ON f.time_key = d.time_key\nJOIN dim_product p ON f.product_key = p.product_key\nJOIN dim_store s ON f.store_key = s.store_key\nWHERE d.calendar_year = 2024\nGROUP BY d.calendar_year, p.category, s.region;\n```\n\n\n### Common OLAP Databases (Data Warehouses)\n\n\n| Database | Deployment | Strengths |\n|----------|------------|-----------|\n| **Snowflake** | Cloud | Scalable, separation of storage/compute |\n| **BigQuery** | GCP | Serverless, petabyte scale |\n| **Redshift** | AWS | Columnar, integrates with AWS |\n| **Databricks** | Multi-cloud | Lakehouse, ML integration |\n| **ClickHouse** | Self-hosted / Cloud | Very fast, real-time analytics |\n| **DuckDB** | Embedded | In-process analytics |\n\n\n---\n\n## Moving Data: ETL and ELT\n\nData must flow from OLTP systems to OLAP systems. This is typically done through ETL or ELT processes.\n\n### ETL: Extract, Transform, Load\n\nTraditional approach where data is transformed before loading into the warehouse.\n\n\n```mermaid\nflowchart LR\n subgraph Sources[OLTP Sources]\n S1[(Orders DB)]:::primary\n S2[(Users DB)]:::primary\n S3[(Inventory DB)]:::primary\n end\n\n subgraph ETL[ETL Process]\n E[Extract]:::orange --> T[Transform
Clean, Join, Aggregate]:::orange --> L[Load]:::orange\n end\n\n subgraph DW[Data Warehouse]\n W[(Star Schema
Tables)]:::green\n end\n\n Sources --> E\n L --> DW\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**ETL characteristics:**\n\n- Transform data before loading\n- Transformation logic in ETL tool\n- Data warehouse receives clean, structured data\n- Tools: Informatica, Talend, Apache NiFi\n\n### ELT: Extract, Load, Transform\n\nModern approach leveraging the warehouse's processing power.\n\n\n```mermaid\nflowchart LR\n subgraph Sources[OLTP Sources]\n S1[(Orders)]:::primary\n S2[(Users)]:::primary\n end\n\n subgraph Load[Load Raw]\n L[Load as-is
to staging]:::orange\n end\n\n subgraph DW[Data Warehouse]\n Raw[(Raw Tables)]:::purple --> Transform[Transform
in warehouse]:::orange --> Final[(Analytics Tables)]:::green\n end\n\n Sources --> Load --> Raw\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**ELT characteristics:**\n\n- Load raw data first\n- Transform using SQL in the warehouse\n- Leverage warehouse's parallel processing\n- Tools: dbt, Fivetran, Airbyte\n\n### Change Data Capture (CDC)\n\nInstead of periodic bulk loads, CDC captures changes as they happen.\n\n\n```mermaid\nflowchart LR\n OLTP[(OLTP Database)]:::primary\n CDC[CDC: Read
Transaction Log]:::orange\n Stream[Kafka Stream]:::purple\n Consumer[Stream Consumer]:::orange\n DW[(Data Warehouse)]:::green\n\n OLTP --> CDC --> Stream --> Consumer --> DW\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**CDC benefits:**\n\n- Near real-time data freshness\n- Lower load on source systems\n- Capture every change, not just current state\n- Tools: Debezium, AWS DMS, Fivetran\n\n---\n\n## HTAP: Hybrid Transactional/Analytical Processing\n\nSome modern databases attempt to handle both OLTP and OLAP workloads.\n\n\n```mermaid\nflowchart TB\n subgraph HTAP[HTAP Architecture]\n App[Application]:::primary\n App --> RowStore[(Row Store
OLTP)]:::purple\n App --> ColStore[(Column Store
OLAP)]:::orange\n\n RowStore <--> |\"Sync\"| ColStore\n\n Analytics[Analytics]:::green --> ColStore\n end\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**HTAP databases:**\n\n\n| Database | Approach |\n|----------|----------|\n| **TiDB** | Row store + TiFlash columnar replica |\n| **SingleStore** | Unified row and column storage |\n| **SAP HANA** | In-memory with row and column |\n| **AlloyDB** | PostgreSQL-compatible with columnar engine |\n\n\n**HTAP trade-offs:**\n\n- Simpler architecture (one database)\n- Real-time analytics on fresh data\n- May not match dedicated OLAP performance\n- More complex database internals\n\n---\n\n## Choosing the Right Approach\n\n### Decision Framework\n\n\n```mermaid\nflowchart TD\n Start[Analytics Requirement]:::primary\n\n Start --> Q1{Simple reports on
current data?}\n Q1 -->|Yes| OLTP[Run on OLTP
with read replica]:::green\n Q1 -->|No| Q2{Need historical
analysis?}\n\n Q2 -->|Yes| DW[Data Warehouse
Snowflake, BigQuery]:::purple\n Q2 -->|No| Q3{Real-time analytics
on fresh data?}\n\n Q3 -->|Yes| HTAP[Consider HTAP
or real-time pipeline]:::orange\n Q3 -->|No| Q4{Complex ML or
unstructured data?}\n\n Q4 -->|Yes| Lake[Data Lake /
Lakehouse]:::teal\n Q4 -->|No| DW\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n### When to Use What\n\n\n| Scenario | Solution |\n|----------|----------|\n| Simple dashboard on current data | Query OLTP read replica |\n| Historical trend analysis | Data warehouse |\n| Executive reporting | Data warehouse with BI tool |\n| Real-time operational analytics | HTAP or streaming |\n| Ad-hoc data exploration | Data lakehouse |\n| ML training on historical data | Data lake / lakehouse |\n\n\n### Architecture Patterns\n\n**Pattern 1: Simple (OLTP only)**\n\nFor early-stage products or simple analytics needs.\n\n\n```mermaid\nflowchart LR\n App[App]:::primary --> Primary[(Primary)]:::purple\n Primary --> Replica[(Read Replica)]:::orange\n BI[BI Tool]:::green --> Replica\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\n**Pattern 2: Separate Warehouse**\n\nFor serious analytics with historical data.\n\n\n```mermaid\nflowchart LR\n App[App]:::primary --> OLTP[(OLTP)]:::purple\n OLTP --> ETL[ETL]:::orange --> DW[(Warehouse)]:::green\n BI[BI]:::teal --> DW\n DS[Data Science]:::teal --> DW\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n**Pattern 3: Real-time Analytics**\n\nFor operational dashboards needing fresh data.\n\n\n```mermaid\nflowchart TB\n App[App]:::primary --> OLTP[(OLTP)]:::purple\n OLTP --> CDC[CDC]:::orange --> Kafka[Kafka]:::orange\n Kafka --> RT[(Real-time
ClickHouse)]:::green\n Kafka --> DW[(Batch DW)]:::teal\n\n Dashboard[Live Dashboard]:::pink --> RT\n Reports[Reports]:::pink --> DW\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef pink fill:#f783ac,stroke:#000,color:#000\n```\n\n\n---\n\n## Real-World Example: E-commerce Analytics\n\nConsider an e-commerce company's data architecture:\n\n\n```mermaid\nflowchart TB\n subgraph Production[Production Systems]\n Web[Web App]:::primary\n Mobile[Mobile App]:::primary\n Orders[(Orders DB
PostgreSQL)]:::purple\n Users[(Users DB
PostgreSQL)]:::purple\n Inventory[(Inventory
PostgreSQL)]:::purple\n end\n\n subgraph Streaming[Real-time Layer]\n Kafka[Kafka]:::orange\n Flink[Flink Processing]:::orange\n RT[(ClickHouse
Real-time)]:::green\n end\n\n subgraph Analytics[Analytics Layer]\n S3[(S3 Data Lake)]:::teal\n DW[(Snowflake
Warehouse)]:::teal\n BI[Looker / Tableau]:::pink\n ML[ML Platform]:::pink\n end\n\n Web --> Orders\n Mobile --> Orders\n Orders --> Kafka\n Users --> Kafka\n Kafka --> Flink --> RT\n Kafka --> S3\n S3 --> DW\n DW --> BI\n S3 --> ML\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef purple fill:#9775fa,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef pink fill:#f783ac,stroke:#000,color:#000\n```\n\n\n**OLTP (PostgreSQL):** Handles orders, user accounts, inventory. Optimized for transactions. Normalized schema.\n\n**Real-time (ClickHouse):** Receives CDC events. Powers live dashboards showing orders per minute, current inventory levels, real-time revenue.\n\n**Batch Analytics (Snowflake):** Historical data from data lake. Powers daily/weekly/monthly reports. Star schema with fact and dimension tables.\n\n**ML Platform:** Trains models on historical data from the lake. Recommendation engines, demand forecasting, fraud detection.\n\n---\n\n## Interview Questions\n\n**Q: What is the difference between OLTP and OLAP systems?**\n\nA: OLTP (Online Transaction Processing) systems handle the operational workload of a business. They process many small transactions concurrently (inserts, updates), require low latency (milliseconds), and touch few rows per query. OLAP (Online Analytical Processing) systems handle analytical workloads. They run complex queries that scan millions of rows, aggregate data, and can tolerate higher latency (seconds to minutes). OLTP uses row-oriented storage and normalized schemas for write efficiency. OLAP uses columnar storage and denormalized schemas for read efficiency.\n\n**Q: Why do companies typically use separate databases for OLTP and OLAP?**\n\nA: Running both workloads on the same database causes resource contention. Analytical queries consume CPU, memory, and I/O, slowing down transactions. Long-running analytics can hold locks that block transactions. The storage formats are also optimized differently. Row storage is efficient for OLTP point queries, while columnar storage is efficient for OLAP aggregations. By separating the systems, each can be optimized for its workload, and analytical queries do not impact production transaction performance.\n\n**Q: What is a star schema and why is it used in data warehouses?**\n\nA: A star schema organizes data into a central fact table surrounded by dimension tables. The fact table contains metrics (revenue, quantity sold) and foreign keys to dimensions. Dimension tables contain descriptive attributes (product name, customer region, date). This denormalized structure is used because it simplifies queries (predictable joins), improves query performance (fewer joins than normalized schemas), and aligns with how business users think about data (analyze facts by dimensions).\n\n**Q: How does data get from OLTP systems to OLAP systems?**\n\nA: The most common approaches are ETL, ELT, and CDC. ETL (Extract, Transform, Load) extracts data from sources, transforms it (cleaning, joining, aggregating), then loads it into the warehouse. ELT loads raw data first, then transforms it using the warehouse's processing power. CDC (Change Data Capture) captures changes from the database transaction log in real-time, enabling near real-time analytics. Modern architectures often combine CDC for real-time needs with batch ELT for complex transformations.\n\n---\n\n## Summary\n\nOLTP and OLAP represent fundamentally different workloads requiring different optimizations:\n\n**OLTP systems:**\n\n- Handle operational transactions\n- Require low latency and high concurrency\n- Use row-oriented storage\n- Employ normalized schemas\n- Examples: PostgreSQL, MySQL, MongoDB\n\n**OLAP systems:**\n\n- Handle analytical queries\n- Tolerate higher latency for complex analysis\n- Use columnar storage with compression\n- Employ denormalized star schemas\n- Examples: Snowflake, BigQuery, ClickHouse\n\n**Key architectural decisions:**\n\n- Simple analytics can run on OLTP read replicas\n- Serious analytics needs a separate data warehouse\n- Real-time analytics requires streaming architecture\n- ETL/ELT/CDC pipelines move data between systems\n- HTAP databases attempt to serve both but have trade-offs\n\nUnderstanding this distinction helps you design systems that perform well for both operational and analytical needs without compromising either.\n\nThe next chapter covers data modeling principles, including how to design schemas for both OLTP and OLAP systems, normalization versus denormalization, and practical modeling patterns.\n\n---\n\n## References\n\n- [The Data Warehouse Toolkit](undefined) by Ralph Kimball - Classic guide to dimensional modeling\n- [Designing Data-Intensive Applications](undefined) by Martin Kleppmann - Chapter 3 covers storage engines\n- [ClickHouse Documentation](undefined) - Fast open-source OLAP database\n- [Snowflake Architecture](undefined) - Modern cloud data warehouse\n- [dbt Documentation](undefined) - Modern data transformation tool\n- [Change Data Capture with Debezium](undefined) - Open-source CDC platform","pageType":"system-design"}

Chapter 5: OLTP vs OLAP

Ashish Pratap Singh

Get Premium