Operational vs. Analytical Databases

Not all databases are built for the same purpose.

A database that handles customer orders at a supermarket checkout needs to be fast at writing small pieces of data. A database that analyzes years of sales data to detect trends needs to be fast at scanning massive amounts of data.

These two types are called Operational Databases (OLTP) and Analytical Databases (OLAP).

Understanding the difference is essential for designing scalable systems and is a frequently asked interview topic.

---

Learning Objectives

After completing this chapter, you will be able to:

• Define OLTP and OLAP.
• Explain the key differences between operational and analytical databases.
• Understand when to use each type.
• Recognize the role of data warehouses.
• Understand ETL pipelines.
• Identify star schemas and snowflake schemas.
• Answer interview questions about database purpose.

---

What is an Operational Database (OLTP)?

OLTP stands for Online Transaction Processing.

An OLTP system handles day-to-day transactions.

Examples:

• A customer buys a product on Amazon.
• A user logs into Instagram.
• A bank deducts ₹500 from an account.
• A hotel books a room for a guest.

OLTP databases are optimized for fast writes and small reads.

---

Characteristics of OLTP

Feature	Description
Workload	Many small transactions per second
Queries	Simple INSERT, UPDATE, DELETE, SELECT by primary key
Data	Current, real-time data
Users	Thousands of concurrent users
Design	Highly normalized (3NF or BCNF)
Speed	Millisecond response time
Concurrency	Heavy — uses row-level locking, MVCC
Recovery	Must be fast and complete

---

Real-World OLTP Example

A railway reservation system like IRCTC.

When a user books a ticket:

1. Check seat availability (READ)
2. Reserve the seat (UPDATE)
3. Generate the ticket (INSERT)
4. Process payment (UPDATE)

Hundreds of users do this simultaneously.

The database must handle all of this without losing any booking.

This is OLTP.

---

What is an Analytical Database (OLAP)?

OLAP stands for Online Analytical Processing.

An OLAP system analyzes large volumes of historical data to support business decisions.

Examples:

• A CEO wants to see revenue trends over the last 5 years.
• A marketing team wants to know which product category sold the most last quarter.
• An operations team wants to find the most common reason for refunds.

OLAP databases are optimized for complex read queries that scan millions of rows.

---

Characteristics of OLAP

Feature	Description
Workload	Few, complex queries
Queries	Aggregations: SUM, COUNT, AVG, GROUP BY over large datasets
Data	Historical, summarized, multi-source
Users	Analysts, data scientists, managers
Design	De-normalized (star schema, snowflake schema)
Speed	Seconds to minutes (acceptable for analysis)
Concurrency	Low to moderate
Recovery	Less critical (data can be reloaded from source)

---

Real-World OLAP Example

A retail chain like Walmart.

An analyst runs a query:

“Show me total sales for each store in Maharashtra for the last year, broken down by month, compared to the previous year.”

This query scans millions of transactions.

The result helps management decide which stores need more inventory and which months have the highest sales.

This is OLAP.

---

OLTP vs OLAP — Side by Side

Dimension	OLTP	OLAP
Purpose	Run the business	Analyze the business
Data	Current, operational	Historical, aggregated
Queries	Simple, short	Complex, long-running
Read/Write	Balanced reads and writes	Mostly reads
Normalization	High (3NF, BCNF)	Low (star schema)
Response Time	Milliseconds	Seconds to minutes
Concurrency	Very high	Low
Example	Banking transaction	Yearly sales report
Indexing	B+Tree indexes	Bitmap indexes
Storage	Row-oriented	Column-oriented

---

Why Separate OLTP and OLAP?

A common mistake is trying to use one database for both purposes.

Problem

An OLTP database is optimized for fast writes.

Running a huge analytical query on an OLTP database would:

• Lock tables for minutes.
• Block customer transactions.
• Slow down the entire application.
• Frustrate users.

Solution

Separate the systems.

1. Keep the OLTP database for daily operations.
2. Periodically copy data to an OLAP database.
3. Run analytical queries on the OLAP database.

This is called a Data Warehouse architecture.

---

Data Warehouse

A Data Warehouse is a central repository that stores historical data from multiple sources, optimized for analysis.

Source A (OLTP)  ─┐
                   ├──→ ETL ──→ Data Warehouse (OLAP)
Source B (OLTP)  ─┘

ETL Pipeline

ETL stands for Extract, Transform, Load.

1. Extract — Read data from source systems (OLTP databases, logs, APIs).
2. Transform — Clean, deduplicate, aggregate, and normalize the data.
3. Load — Insert the processed data into the data warehouse.

ELT

Modern systems sometimes use ELT (Extract, Load, Transform).

Data is loaded raw into the warehouse, then transformed using the warehouse’s compute power.

This is common with cloud warehouses like Snowflake, BigQuery, and Redshift.

---

Star Schema

A Star Schema is the simplest data warehouse design.

It has one central Fact Table surrounded by Dimension Tables.

Example

Fact Table: Sales

Sale_ID	Date_ID	Product_ID	Store_ID	Amount
1	20240101	P01	S01	₹500
2	20240101	P02	S01	₹300

Dimension Tables:

• Date (Date_ID, Day, Month, Year, Quarter)
• Product (Product_ID, Name, Category, Price)
• Store (Store_ID, City, State, Region)

Advantages

• Simple to understand.
• Fast for aggregations.
• Common in reporting tools.

---

Snowflake Schema

A Snowflake Schema is a normalized version of the star schema.

Dimension tables are further split into sub-dimensions.

Example

Instead of a single Store table:

Store
  └── City
        └── State
              └── Region

Advantages

• Less storage (no redundancy).
• Better data integrity.

Disadvantages

• More complex queries.
• Slower performance (more joins).

---

Row-Oriented vs Column-Oriented Storage

This is a key technical difference between OLTP and OLAP databases.

Row-Oriented (OLTP)

Data is stored row by row.

Row 1: 1, Rahul, Delhi, ₹1000
Row 2: 2, Priya, Mumbai, ₹2000
Row 3: 3, Amit, Bangalore, ₹1500

Best for: Queries that need entire rows (SELECT * WHERE id = 1).

Example: PostgreSQL, MySQL, Oracle.

Column-Oriented (OLAP)

Data is stored column by column.

Column 1 (ID): 1, 2, 3
Column 2 (Name): Rahul, Priya, Amit
Column 3 (City): Delhi, Mumbai, Bangalore
Column 4 (Amount): 1000, 2000, 1500

Best for: Queries that need specific columns (SELECT SUM(amount) WHERE city = ‘Mumbai’).

Example: ClickHouse, BigQuery, Redshift, Snowflake.

---

Lambda Architecture

Modern systems often use a Lambda Architecture that combines both.

Real-time Stream ──→ Speed Layer ──→
                                    ├──→ Serving Layer
Batch Data ────────→ Batch Layer ──→

• Batch Layer: Processes historical data (OLAP).
• Speed Layer: Processes real-time data (OLTP).
• Serving Layer: Combines results for queries.

This is used by companies like Netflix, Uber, and LinkedIn.

---

When to Use Each

Scenario	Use
Customer checking account balance	OLTP
Generating monthly payroll report	OLAP
Processing a credit card payment	OLTP
Analyzing 5 years of sales trends	OLAP
Updating inventory after a sale	OLTP
Building a recommendation engine	OLAP
User registration and login	OLTP
Data science and machine learning	OLAP

---

Interview Deep Dive

---

Key Takeaways

• OLTP systems handle daily transactions and need fast writes.
• OLAP systems handle complex analysis and need fast scans.
• Mixing both in one database causes performance problems.
• Data warehouses store historical data for analysis.
• ETL/ELT pipelines move data from OLTP to OLAP.
• Star schemas and snowflake schemas are common warehouse designs.
• Row-oriented storage suits OLTP; column-oriented suits OLAP.