2.2.1. Transactional (OLTP) vs. Analytical (OLAP)

💡 First Principle: OLTP and OLAP represent opposite ends of an optimization spectrum. OLTP systems are optimized for many small, fast writes that maintain data integrity—like a high-speed assembly line where each station must complete its task perfectly before the next can begin. OLAP systems are optimized for few large, complex reads that aggregate historical data—like a research library where you're reading thousands of documents to synthesize insights. The architectures that make each excel are mutually exclusive.

Scenario: An e-commerce platform processes 1,000 orders per minute. Each order must update inventory, charge the customer, and create a shipping record—all atomically (all succeed or all fail). Separately, the business intelligence team runs daily queries analyzing sales trends across 100 million historical orders.

OLTP (Online Transaction Processing)

• Goal: Support day-to-day business operations
• Operations: Heavy INSERT, UPDATE, DELETE with some SELECT
• Data Volume: Current/recent data (operational window)
• Query Pattern: Simple queries affecting few rows (lookup by ID)
• Key Trait: ACID Compliance
  • Atomicity: All operations in a transaction succeed or all fail
  • Consistency: Database moves from one valid state to another
  • Isolation: Concurrent transactions don't interfere
  • Durability: Committed data survives system failures
• Normalization: High (3NF or higher) to prevent data duplication and anomalies
• Azure Services: Azure SQL Database, Azure Database for PostgreSQL/MySQL

OLAP (Online Analytical Processing)

• Goal: Support decision-making through data analysis
• Operations: Heavy SELECT with complex aggregations (SUM, AVG, COUNT)
• Data Volume: Historical data (months/years of records)
• Query Pattern: Complex queries scanning millions of rows
• Key Trait: Read Optimization (columnar storage, parallel processing)
• Normalization: Low (Denormalized/Star Schema) to speed up reads
• Azure Services: Azure Synapse Analytics, Azure Databricks, Power BI

Visual: OLTP vs. OLAP Architecture

Comparative Table: OLTP vs. OLAP

⚠️ Exam Trap: Running analytical queries directly on OLTP databases is a critical anti-pattern. This degrades transactional performance and may cause timeouts for real users. Always extract data to a separate analytical system.

Key Trade-Offs:

• Normalization vs. Read Speed: OLTP normalizes data to prevent duplication (requires JOINs to read). OLAP denormalizes data to eliminate JOINs (faster reads, accepts duplication).
• Data Freshness vs. Query Performance: OLTP has real-time data but slow analytical queries. OLAP has fast queries but data may be hours/days old.
• Write Optimization vs. Read Optimization: You cannot optimize for both. Choose based on primary workload.

Reflection Question: Why would running a complex report query (scanning 50 million rows) on a production OLTP database be problematic, even if the query is technically valid SQL?