Definition
- Row-store – keeps all columns of a row physically together, ideal for OLTP because a transaction reads or writes one whole row.
- Column-store – keeps all values of one column together, ideal for OLAP because analytical queries aggregate a few columns across millions of rows.
- Normalized schema – data split into many related tables so each fact lives in one place, preventing update anomalies.
- Star schema – a central fact table surrounded by dimension tables, with deliberate redundancy for simple analytical joins.
- Snowflake schema – a star schema whose dimension tables are further normalized.
- Resource contention – the reason for separation: heavy scans steal CPU, memory, and I/O from latency-sensitive transactions.
The National Bank of India's live account system is OLTP: data is kept row by row, tidily normalized so each fact lives in one place, and it handles thousands of tiny instant actions like Rajesh paying rent. The analytics warehouse is OLAP: data is kept column by column, deliberately flattened into a star shape with one big fact table surrounded by lookup tables, so a question like "total spend per branch per quarter" scans fast. They look at the same money but are built oppositely: one for quick single touches, one for huge sweeping reads, and mixing them would make the quick system slow.
Understanding the Concepts
What is the difference between row-store and column-store?
Storage orientation is the first deep difference between OLTP and OLAP. A row-store keeps all columns of a row physically together, which is ideal for OLTP because a transaction typically reads or writes one whole row, like fetching Rajesh's account and updating his balance. A column-store keeps all values of one column together, which is ideal for OLAP because an analytical query aggregates a few columns across millions of rows and reads only those columns.
package com.bank.storage;
public class RowVsColumnStore {
public static void main(String[] args) {
System.out.println("Row-store (OLTP):");
System.out.println(" [T1|SAV2001|15000|2024-10-29]");
System.out.println(" [T2|CUR3002|25000|2024-10-29]");
System.out.println(" -> Read one row: fast (all cols together)");
System.out.println();
System.out.println("Column-store (OLAP):");
System.out.println(" txn_id: [T1, T2, T3, ...]");
System.out.println(" amount: [15000, 25000, 30000, ...]");
System.out.println(" -> SUM(amount): fast (reads only amount)");
}
}
Row-store (OLTP):
[T1|SAV2001|15000|2024-10-29]
[T2|CUR3002|25000|2024-10-29]
-> Read one row: fast (all cols together)
Column-store (OLAP):
txn_id: [T1, T2, T3, ...]
amount: [15000, 25000, 30000, ...]
-> SUM(amount): fast (reads only amount)
What is the difference between normalized and star schema?
Schema design is the second deep difference. OLTP uses normalization, splitting data into many related tables so each fact is stored once, preventing update anomalies. OLAP uses a denormalized star schema with a central fact table surrounded by dimension tables, trading redundancy for simple and fast analytical joins.
-- Normalized schema (OLTP)
CREATE TABLE accounts (
account_id VARCHAR PRIMARY KEY,
holder_name VARCHAR,
branch_id INT REFERENCES branches(branch_id)
);
CREATE TABLE branches (
branch_id INT PRIMARY KEY,
branch_name VARCHAR,
city VARCHAR
);
-- Star schema (OLAP)
CREATE TABLE fact_transactions (
txn_id VARCHAR PRIMARY KEY,
account_id VARCHAR,
branch_id INT,
date_id INT,
amount DECIMAL(15,2)
);
CREATE TABLE dim_branch (
branch_id INT PRIMARY KEY,
branch_name VARCHAR,
city VARCHAR,
region VARCHAR -- denormalized: region in branch dim
);
CREATE TABLE dim_date (
date_id INT PRIMARY KEY,
full_date DATE,
quarter VARCHAR,
year INT
);
How does workload shape differ?
OLTP is many small, concurrent point operations with strict latency and ACID guarantees. OLAP is a smaller number of very heavy scan-and-aggregate queries over large historical ranges. Indexing, locking, and caching strategies are tuned oppositely.
-- OLTP workload: thousands per second, milliseconds each
UPDATE accounts
SET balance = balance - 50000
WHERE account_id = 'SAV2001';
-- OLAP workload: few per hour, seconds each
SELECT d.branch_name, SUM(f.amount)
FROM fact_transactions f
JOIN dim_branch d USING (branch_id)
WHERE d.quarter = 'Q3-2024'
GROUP BY d.branch_name;
package com.bank.storage;
public class WorkloadShape {
public static void main(String[] args) {
System.out.println("OLTP workload:");
System.out.println(" 10000 txns/sec, 2ms each, 1 row");
System.out.println();
System.out.println("OLAP workload:");
System.out.println(" 10 queries/hour, 8 sec each, 50M rows");
}
}
OLTP workload:
10000 txns/sec, 2ms each, 1 row
OLAP workload:
10 queries/hour, 8 sec each, 50M rows
Why must you separate OLTP and OLAP?
Separation is essential because of resource contention and design mismatch. A heavy analytical scan on the production database consumes CPU, memory, and I/O, can hold locks or evict the buffer cache, and starves the small transactions so a payment that should take milliseconds suddenly times out.
package com.bank.storage;
public class WhySeparate {
public static void main(String[] args) {
System.out.println("Without separation:");
System.out.println(" Analyst runs: SELECT SUM(amount)"
+ " FROM transactions (50M rows)");
System.out.println(" Rajesh runs: UPDATE balance (1 row)");
System.out.println(" Result: Rajesh waits 3.5 seconds.");
System.out.println();
System.out.println("With separation:");
System.out.println(" Analyst queries OLAP warehouse.");
System.out.println(" Rajesh queries OLTP production.");
System.out.println(" Result: both fast, isolated.");
}
}
Without separation:
Analyst runs: SELECT SUM(amount) FROM transactions (50M rows)
Rajesh runs: UPDATE balance (1 row)
Result: Rajesh waits 3.5 seconds.
With separation:
Analyst queries OLAP warehouse.
Rajesh queries OLTP production.
Result: both fast, isolated.
How do you move data from OLTP to OLAP?
The solution is ETL, ELT, or change-data-capture into a warehouse or lake. This isolates the workloads so analytics scales independently. Read replicas or streaming can keep the analytics copy only seconds behind for near-real-time needs.
# CDC with Debezium: capture every committed change
# PostgreSQL (OLTP) -> Kafka -> Spark -> Redshift (OLAP)
# Kafka message carries the change event
# {
# "op": "u", // update
# "before": {"account_id": "SAV2001", "balance": 500000},
# "after": {"account_id": "SAV2001", "balance": 450000}
# }
# Consumer applies change to OLAP warehouse
# OLAP copy is seconds behind OLTP
What is a snowflake schema?
A snowflake schema is a star schema whose dimension tables are further normalized. For example, a branch dimension might have a sub-table for regions. Snowflake schemas save storage but add join complexity. Star schemas are more common in practice because simplicity matters for analytical query performance.
-- Snowflake: dimension further normalized
CREATE TABLE dim_branch (
branch_id INT PRIMARY KEY,
branch_name VARCHAR,
region_id INT REFERENCES dim_region(region_id)
);
CREATE TABLE dim_region (
region_id INT PRIMARY KEY,
region_name VARCHAR,
country VARCHAR
);
-- Star: dimension is flat (denormalized)
CREATE TABLE dim_branch_flat (
branch_id INT PRIMARY KEY,
branch_name VARCHAR,
region_name VARCHAR, -- denormalized
country VARCHAR -- denormalized
);
What should a senior engineer know?
A senior nuance is that read replicas or streaming can keep the analytics copy seconds behind for near-real-time needs, and HTAP systems attempt both on one engine. But in a bank the safest conventional pattern is clear separation so that an analyst's quarterly report can never degrade a customer's live transaction.
package com.bank.storage;
public class SeniorNuances {
public static void main(String[] args) {
System.out.println("Senior considerations:");
System.out.println(" 1. CDC lag: seconds (near-real-time)");
System.out.println(" 2. Read replica: offloads reads,"
+ " same row-store");
System.out.println(" 3. HTAP (TiDB): both on one engine");
System.out.println(" 4. Bank preference: separate OLTP/OLAP");
System.out.println(" Reason: zero risk to payments.");
}
}
Senior considerations:
1. CDC lag: seconds (near-real-time)
2. Read replica: offloads reads, same row-store
3. HTAP (TiDB): both on one engine
4. Bank preference: separate OLTP/OLAP
Reason: zero risk to payments.
What are common interview follow-up questions?
- What is the difference between a star schema and a snowflake schema?
- How does change-data-capture differ from a nightly ETL batch?
- What is a slowly changing dimension (SCD)?
- How do you handle near-real-time analytics when CDC lag matters?
- Why is column pruning so powerful for wide OLAP tables?
Visual Overview
Row-Store vs Column-Store ───────────────────────── Row-Store (OLTP) Column-Store (OLAP) ┌───────────────────┐ ┌─────────────────────┐ │ [id|amt|dt|branch]│ │ id: T1,T2,T3,...│ │ [id|amt|dt|branch]│ │ amount: 15k,25k,... │ │ [id|amt|dt|branch]│ │ date: Oct,Oct,... │ │ [id|amt|dt|branch]│ │ branch: Mum,Del,... │ └───────────────────┘ └─────────────────────┘ GET row by id: fast SUM(amount): fast SUM(amount): slow GET row by id: slow
Star Schema
───────────
┌───────────┐
│ dim_date │
│ date_id │
│ quarter │
└─────┬─────┘
│
┌──────────┐ ┌───┴──────────┐ ┌───────────┐
│dim_branch│──│fact_txns │──│dim_account│
│branch_id │ │txn_id │ │account_id │
│name,city │ │amount │ │holder_name│
└──────────┘ │branch_id (FK)│ └───────────┘
│date_id (FK) │
│account_id(FK)│
└──────────────┘
Key Takeaway
OLTP and OLAP differ in three deep ways: storage orientation, schema design, and workload shape. On storage, OLTP is a row-store that keeps all columns of a row together, ideal because a transaction reads or writes one whole row, while OLAP is a column-store that keeps each column's values together, ideal because analytical queries aggregate a few columns across millions of rows. On schema, OLTP uses normalization so each fact lives in one place, preventing anomalies, while OLAP uses a denormalized star schema with a central fact table surrounded by dimension tables, trading redundancy for simple analytical joins. On workload, OLTP is many small concurrent point operations under strict latency and ACID, whereas OLAP is a smaller number of heavy scan-and-aggregate queries.
You separate them because of resource contention and design mismatch. A heavy analytical scan on the production database consumes CPU, memory, and I/O, can hold locks or evict the cache, and starves the small transactions so a payment that should take milliseconds times out. The storage model is also wrong, since a row-store forced to aggregate one column across millions of rows reads every column of every row. The fix is to move data from OLTP into an OLAP warehouse or lake through ETL, ELT, or change-data-capture, isolating the workloads so analytics scales independently.
In a banking context at the National Bank of India, the production system was a normalized row-store doing fast single-row updates like debiting Rajesh's account, while the analytics warehouse was a column-store star schema with a transaction fact table and branch, account, and date dimensions. A CDC sync fed the warehouse, and heavy rollups like total balance per branch ran entirely on OLAP.
That way the quarterly branch report could never slow down a live payment, which is exactly the separation a bank depends on.
Remember one sentence: OLTP is a row-store for fast point writes, OLAP is a column-store for big scans, and separating them ensures analytics never degrades live transactions.
---