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,DELETEwith someSELECT - 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
SELECTwith 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
| Characteristic | OLTP | OLAP |
|---|---|---|
| Purpose | Run the business | Analyze the business |
| Users | Clerks, customers, apps | Analysts, executives |
| Operations | INSERT, UPDATE, DELETE | SELECT (complex) |
| Query Scope | Single record or few rows | Millions of rows |
| Data Age | Current (real-time) | Historical (weeks/months/years) |
| Normalization | High (3NF) | Low (Star/Snowflake schema) |
| Response Time | Milliseconds | Seconds to minutes |
| Concurrency | Thousands of users | Fewer users, heavy queries |
| Azure Service | Azure SQL DB | Azure Synapse Analytics |
⚠️ 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?
ACID: What "Transactional" Actually Guarantees
A transaction is a group of operations the database treats as a single unit of work. ACID names the four guarantees that make that unit trustworthy — and they are the reason transactional systems exist.
| Letter | Guarantee | What it prevents |
|---|---|---|
| A — Atomicity | All or nothing: every operation in the transaction commits, or none of them do | Money leaving one account without arriving in the other |
| C — Consistency | The database moves from one valid state to another; every constraint still holds afterwards | An order row pointing at a customer who does not exist |
| I — Isolation | Concurrent transactions cannot see each other's half-finished work | Two customers both being sold the last seat |
| D — Durability | Once committed, the change survives a crash or power cut | A confirmed payment vanishing when the server reboots |
Atomicity in practice: an e-commerce checkout deducts stock, charges the card and creates a shipment. If the card is declined at step two, atomicity guarantees the stock deduction is rolled back — the database ends up exactly as if the checkout never started. This is what COMMIT and ROLLBACK control (see 3.1.2).
Durability in practice: the database writes the change to durable storage before acknowledging the commit, which is why an application can trust an "order placed" response even if the machine dies a second later.
⚠️ Exam Trap: the C in ACID is not the same "consistency" as Cosmos DB's consistency levels, and the exam tests both. ACID consistency means the database's constraints and rules still hold after the transaction. Distributed consistency (Strong, Bounded Staleness, Session, Consistent Prefix, Eventual — see 4.2.1) means how quickly replicas in different regions agree with each other. Same word, unrelated concepts. When a question mentions replicas or regions, it is asking about the distributed kind.
