OLTP vs OLAP, Explained

oltp vs olap is the split that quietly decides the shape of every data platform you will ever build — because the database that takes a customer's payment in one millisecond and the system that tells the finance team last quarter's revenue by region are not the same machine tuned two different ways; they are two physically different machines built around opposite assumptions about how bytes are laid out on disk and how queries touch them. online transaction processing optimises for a flood of tiny, indexed, short-lived reads and writes that each touch one row and finish before the user notices. online analytical processing optimises for a handful of enormous queries that each sweep millions of rows, read only a few columns, and collapse the result into a chart. The same data lives in both, but the moment you demand both access patterns from one storage layout, you lose — which is why real systems keep the two apart and copy data between them.

This guide is the walkthrough you wished existed the first time an interviewer said "walk me through OLTP versus OLAP and why one database can't do both well," or "why did the warehouse get fifty times faster when we switched to columnar," or "how does data actually get from the production database into the dashboard." It works through the whole picture: why the workload split forces an architecture split, how a transactional database uses a row store to win point access, how a data warehouse uses a column store to win analytical queries, the physical row store vs column store difference that drives every trade-off, star schema dimensional modelling, and finally HTAP plus the CDC and ETL path that moves data from the operational system into the analytical one. Each section pairs a teaching block with a worked interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the database practice library →, and sharpen the pipeline axis with the data-processing practice library →.

---

On this page

• Why the OLTP/OLAP split determines your whole data architecture
• OLTP — row-stores tuned for point reads and writes
• OLAP — column-stores tuned for large scans and aggregation
• Row vs column storage — the physical difference that drives everything
• HTAP and moving data OLTP → OLAP
• Cheat sheet — OLTP vs OLAP recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the OLTP/OLAP split determines your whole data architecture

Two workloads, two physical machines — the split you cannot design around

The one-sentence invariant: OLTP and OLAP are not two settings of one database but two physically distinct designs — a row-oriented store built for many tiny point transactions with strict consistency, and a column-oriented store built for few enormous scanning-and-aggregating queries with high throughput — and because the storage layout, the indexing strategy, the concurrency model, and the freshness contract that make one fast make the other slow, mature architectures keep them separate and copy data from the transactional system into the analytical one. The choice is not "which database is better"; it is "which workload is this query," and getting the workload wrong is the single most common cause of a system that is both slow to write and slow to analyse.

The four axes interviewers actually probe.

• Access pattern. OLTP queries are point access: SELECT * FROM orders WHERE id = 42, UPDATE accounts SET balance = balance - 10 WHERE id = 7. They touch one row or a small handful, identified by key. OLAP queries are scans: SELECT region, SUM(total_cents) FROM orders GROUP BY region. They touch millions of rows but only a few columns, and reduce the result with aggregation. This single distinction — point versus scan — predicts almost every downstream design decision.
• Storage layout. OLTP stores rows contiguously (row-major) so that fetching one whole record is one disk read. OLAP stores columns contiguously (column-major) so that scanning one column of a billion rows reads only that column's bytes and skips the rest. The layout is the mechanism; the workload is the reason. Interviewers open here because it separates people who understand the physics from those who have only memorised product names.
• Concurrency and latency. OLTP serves thousands of concurrent short transactions, each finishing in ~1 ms, protected by locks or MVCC and durability via a write-ahead log. OLAP serves a few concurrent long queries, each running for seconds to minutes, optimised for total throughput rather than per-query latency. A warehouse that answers one analyst's query in 8 seconds is healthy; a transactional database that takes 8 seconds to record a payment is on fire.
• Freshness and consistency. OLTP is the source of truth and must be transactionally consistent to the microsecond — money must not be double-spent. OLAP is a derived copy and can tolerate minutes or hours of staleness — a revenue dashboard that is 15 minutes behind is fine. This asymmetry is what makes the copy between the two systems acceptable in the first place.

The 2026 reality — copy by default, fuse only when you must.

• The default architecture is a row-store OLTP database (PostgreSQL, MySQL, SQL Server, Oracle, or a cloud equivalent) as the system of record, feeding a column-store OLAP warehouse (Snowflake, BigQuery, Redshift, ClickHouse, Databricks SQL) through a pipeline. The pipeline is either batch ETL (nightly or hourly bulk loads) or incremental CDC (change data capture streaming every mutation). The warehouse is where analysts, dashboards, and machine-learning feature pipelines live.
• The dimensional layer on the OLAP side is usually a star schema: a central fact table of events (one row per order line, per click, per payment) surrounded by dimension tables (customer, product, date, region). This model is not an accident — it is the shape that column stores scan and aggregate fastest, and the shape analysts find easiest to reason about.
• HTAP — Hybrid Transactional/Analytical Processing — is the attempt to collapse the two systems into one engine that keeps both a row store and a column store internally and routes each query to the right one. It removes the copy step and the freshness lag, at the cost of a more complex, more expensive engine. It is the right answer for a narrow band of use cases (operational analytics on live data) and the wrong answer for most classic warehousing.
• The lakehouse is the modern warehouse variant: columnar files (Parquet/ORC) in object storage with a table format (Iceberg, Delta, Hudi) providing transactions and schema evolution on top. It is still fundamentally OLAP — column-major, scan-optimised — just decoupled from a fixed compute cluster.

What interviewers listen for.

• Do you frame the split as workload, not product — "this is a point-access transactional workload" rather than "we'd use Postgres"? — senior signal.
• Do you name row store vs column store as the physical reason, not just say "OLAP is for analytics"? — required answer.
• Do you say OLAP is a derived copy of OLTP, so it can be stale and denormalised? — required answer.
• Do you name the copy mechanism (batch ETL or CDC) rather than hand-waving "we sync the data"? — senior signal.
• Do you position HTAP as a trade-off (removes the copy, adds engine complexity) rather than as a magic "best of both"? — senior signal.

Worked example — classifying a query by its workload signature

Detailed explanation. The most useful reflex in this entire topic is looking at a query and instantly classifying it OLTP or OLAP by its signature — how many rows it touches, how many columns, whether it filters by key or scans, whether it writes, and how fresh the answer must be. Interviewers hand you queries and expect the classification in one sentence. Walk through building the signature checklist against a hypothetical e-commerce system.

• Row count touched. One or few (OLTP) vs millions (OLAP).
• Column count touched. Most/all columns of the row (OLTP) vs a few columns across many rows (OLAP).
• Predicate shape. Equality on a primary key or unique index (OLTP) vs range/scan with GROUP BY (OLAP).
• Mutation. Frequent small writes (OLTP) vs read-mostly, bulk-append (OLAP).
• Freshness. Must-be-current source of truth (OLTP) vs can-be-stale derived copy (OLAP).

Question. Classify five queries against the e-commerce schema and state which system each belongs on.

Input.

Query	Rows touched	Columns	Predicate	Workload
Fetch order by id for the order page	1	all	PK equality	OLTP
Decrement inventory on checkout	1	1-2	PK equality + write	OLTP
Monthly revenue by region	~50M	3	scan + GROUP BY	OLAP
Top 20 products by units last quarter	~50M	4	scan + GROUP BY + ORDER	OLAP
Customer lifetime value cohort analysis	~200M join	5	multi-table scan	OLAP

Code.

-- OLTP: point read — touches one row via the primary key
SELECT id, customer_id, total_cents, status, created_at
FROM   orders
WHERE  id = 42;

-- OLTP: point write inside a transaction — one row, must be durable now
BEGIN;
UPDATE inventory
SET    on_hand = on_hand - 1
WHERE  sku = 'ABC-123' AND on_hand > 0;
COMMIT;

-- OLAP: scan-and-aggregate — millions of rows, three columns, reduced by GROUP BY
SELECT region,
       date_trunc('month', created_at) AS month,
       SUM(total_cents) / 100.0        AS revenue_usd
FROM   orders
WHERE  created_at >= '2026-01-01'
GROUP  BY region, date_trunc('month', created_at)
ORDER  BY month, region;

Step-by-step explanation.

1. The first query fetches one row by primary key. A row store answers it with a single index seek and one block read — the whole record sits contiguously. This is the archetypal OLTP query: point access, all columns, sub-millisecond.
2. The second is a point write wrapped in a transaction. It must be atomic and durable the instant it commits (you cannot oversell inventory), so it belongs on the transactional system with its lock/MVCC and write-ahead log. Latency and correctness dominate; throughput per query is trivial.
3. The third query scans every order in the year, reads only three columns (region, created_at, total_cents), and collapses millions of rows into a few dozen. On a row store this reads every column of every row to use three of them; on a column store it reads three columns and skips the rest — the OLAP signature.
4. The fourth and fifth are heavier variants of the same scan-and-aggregate shape, adding sort and multi-table joins. They can tolerate seconds of runtime and minutes of staleness, so they belong on the warehouse where compute is optimised for throughput.
5. The classification never depends on the product name — it depends on the signature. The same PostgreSQL engine could run all five, but queries 3-5 would hammer the transactional database's buffer pool and compete with the payment path, which is exactly why you copy them out to an OLAP system.

Output.

Query	System	Why
Fetch order by id	OLTP (transactional DB)	point read, latency-critical
Decrement inventory	OLTP (transactional DB)	point write, must be consistent now
Monthly revenue by region	OLAP (warehouse)	scan + aggregate, staleness-tolerant
Top 20 products	OLAP (warehouse)	scan + aggregate + sort
Lifetime-value cohorts	OLAP (warehouse)	multi-table scan, heavy compute

Rule of thumb. Classify by signature, never by product. Count the rows and columns a query touches and whether it writes; point-few-columns-write is OLTP, scan-few-columns-aggregate is OLAP. The product is downstream of the workload.

Worked example — what happens when you run OLAP on the OLTP box

Detailed explanation. The fastest way to understand why the split exists is to watch it fail. A common startup pattern is to skip the warehouse and run analytics directly on the production PostgreSQL replica "to save money." It works until it doesn't. Walk through the failure mode of running a heavy aggregation against a row-store transactional database under production load.

• The setup. A SELECT region, SUM(total_cents) ... GROUP BY region scanning 80M orders on the primary's read replica.
• The symptom. The query takes 40 seconds, the replica's buffer cache is evicted, and OLTP point-read latency on that replica spikes from 1 ms to 60 ms.
• The root cause. The scan reads every 8 KB page of the orders heap to extract three columns, flooding the buffer pool and evicting the hot pages the point reads depend on.

Question. Explain, in bytes and cache behaviour, why the analytics query degrades the transactional workload, and state the fix.

Input.

Factor	Row-store OLTP replica	Dedicated column-store OLAP
Bytes read for the aggregate	full heap (~all columns)	3 columns only
Buffer-pool impact	evicts hot OLTP pages	isolated compute
Point-read latency during scan	1 ms → 60 ms	unaffected
Query runtime	~40 s	~1-2 s
Blast radius	shared with production	isolated

Code.

-- The offending analytics query, run on the OLTP replica
EXPLAIN (ANALYZE, BUFFERS)
SELECT region, SUM(total_cents)
FROM   orders                       -- 80,000,000 rows, ~40 columns
GROUP  BY region;

-- Simplified plan output (annotated):
--   Seq Scan on orders
--     rows=80000000
--     Buffers: shared read=6,250,000   <- reads ~48 GB of heap pages
--   HashAggregate
-- The scan pulls every 8 KB page holding all ~40 columns
-- just to read region + total_cents. The buffer pool is 16 GB;
-- the scan evicts the entire hot OLTP working set.

Cache behaviour timeline
========================
t=0s   OLTP point reads: p99 = 1 ms   (hot pages cached)
t=2s   analytics Seq Scan begins pulling cold heap pages
t=8s   buffer pool now full of orders heap pages
t=8s+  OLTP point reads must go to disk: p99 = 60 ms
t=40s  analytics query finishes; cache slowly re-warms
t=90s  OLTP p99 back to ~1 ms

Step-by-step explanation.

1. The aggregation is a sequential scan: to compute SUM(total_cents) grouped by region it must visit every row. On a row store, each row's ~40 columns are stored together, so reading region and total_cents forces the engine to read the entire ~48 GB heap into the buffer pool.
2. The buffer pool is finite (say 16 GB). As the scan streams cold pages in, the LRU policy evicts the pages the OLTP point reads keep hot — the small set of index and heap pages the payment path touches thousands of times per second.
3. Once those hot pages are evicted, every OLTP point read that used to be a memory hit becomes a disk read. p99 latency jumps ~60×. The payment path, order page, and inventory writes all slow down — the analytics query has degraded the entire transactional service.
4. This is not a tuning problem; it is a layout problem. The row store must read all columns to use a few, so any large aggregation is inherently hostile to the cache that OLTP depends on. No index fixes it, because the query legitimately needs to scan.
5. The fix is architectural: move the aggregation off the transactional box entirely, onto a column store where reading three columns of 80M rows reads only three columns' bytes, runs in ~1-2 s, and shares no cache with production. That is the whole reason the OLTP → OLAP copy exists.

Output.

Metric	Analytics on OLTP replica	Analytics on OLAP warehouse
Aggregate runtime	~40 s	~1-2 s
Bytes read	~48 GB (all columns)	~3.6 GB (3 columns)
OLTP p99 during scan	60 ms	1 ms (unaffected)
Production blast radius	high	none
Monthly cost illusion	"free" (existing box)	separate but isolates risk

Rule of thumb. Never run large aggregations on the transactional database, even a replica. A scan on a row store evicts the OLTP working set and turns a "free" query into a production latency incident. Isolate analytical queries on a column store where scanning a few columns is cheap.

Data-engineering interview question on the OLTP/OLAP split

A senior interviewer often opens with: "A startup runs everything on one PostgreSQL instance — the app and the analytics dashboards. The dashboards are getting slow and, worse, the checkout path now has latency spikes every hour when the finance reports run. Walk me through how you'd diagnose it, why it's happening at the storage level, and the target architecture you'd migrate to."

Solution Using workload separation with a column-store warehouse fed by CDC

Target architecture
====================

  [ PostgreSQL OLTP ]  --- WAL / CDC --->  [ Snowflake OLAP ]
   row store, source          Debezium         column store,
   of truth, point            + Kafka          scan + aggregate,
   reads/writes, ACID                          dashboards + BI

  App  ---------------> reads/writes ------> PostgreSQL
  Analysts / BI  -----> read-only queries -> Snowflake

-- Step 1 — confirm the diagnosis on the OLTP box:
-- large sequential scans competing with the OLTP working set.
SELECT query,
       calls,
       mean_exec_time,
       shared_blks_read           -- pages pulled from disk
FROM   pg_stat_statements
ORDER  BY shared_blks_read DESC
LIMIT  10;
-- The finance reports dominate shared_blks_read: they are
-- scanning the whole orders/line_items heap every hour.

-- Step 2 — the same aggregation, once it lives on the columnar warehouse,
-- reads only the columns it needs and runs isolated from production.
SELECT d.region,
       date_trunc('month', f.created_at) AS month,
       SUM(f.total_cents) / 100.0        AS revenue_usd
FROM   fact_orders   f
JOIN   dim_region    d ON d.region_id = f.region_id
WHERE  f.created_at >= dateadd('year', -1, current_date)
GROUP  BY 1, 2
ORDER  BY 2, 1;

# Step 3 — the CDC connector that keeps the warehouse fresh (~minutes)
name: orders-to-warehouse
config:
  connector.class: io.debezium.connector.postgresql.PostgresConnector
  plugin.name: pgoutput
  database.dbname: production
  table.include.list: public.orders,public.line_items,public.customers
  snapshot.mode: initial        # one-time bulk copy, then stream changes
  topic.prefix: warehouse
  tombstones.on.delete: true    # deletes propagate to the warehouse too

Step-by-step trace.

Step	Before (one Postgres)	After (OLTP + OLAP)
Where analytics runs	production row store	dedicated column store
Bytes read per report	full heap (all columns)	few columns only
Checkout p99 during reports	60 ms spike	1 ms (isolated)
Report runtime	40 s	1-2 s
Freshness of analytics	live (but harmful)	~minutes behind via CDC
Source of truth	Postgres	Postgres (unchanged)

After the migration, PostgreSQL only ever serves point reads and writes — the checkout path never competes with a scan again. The finance reports run on Snowflake against a star schema (fact_orders + dim_region + dim_date), reading three columns of tens of millions of rows in seconds. A Debezium CDC connector streams every insert, update, and delete into the warehouse, keeping it a few minutes behind the source without ever touching the OLTP buffer pool during business hours.

Output:

Metric	Before	After
Checkout p99 during reports	60 ms	1 ms
Report latency	40 s	1-2 s
OLTP cache stability	evicted hourly	stable
Analytics freshness	live	~minutes (CDC)
Scaling model	one box for both	scale each independently

Why this works — concept by concept:

• Workload separation — the transactional database serves only point access; the warehouse serves only scans. Neither competes for the other's cache, locks, or CPU, so a heavy report can never degrade checkout.
• Column store for the aggregate — reading region + created_at + total_cents on a column store reads three columns' bytes, not the whole ~40-column heap. That is the 40 s → 2 s difference, and it comes purely from the storage layout.
• Star schema on the OLAP side — a central fact table plus small dimension tables is the shape a column store scans and joins fastest, and the shape analysts model against. It is the natural target for the copied data.
• CDC keeps the copy fresh — because OLAP is a derived copy, it can be minutes stale. Debezium streams WAL changes so the warehouse tracks the source without the nightly full reload or the live-scan tax.
• Cost — you now run two systems instead of one, but each scales independently and neither is oversized to absorb the other's spikes. The eliminated cost is the recurring production latency incident — O(few columns) scans on OLAP versus O(all columns) scans that evict the OLTP working set.

Database
Topic — database
Database design and workload-separation problems

Practice →

SQL Topic — sql SQL point-query vs scan problems

Practice →

---

2. OLTP — row-stores tuned for point reads and writes

online transaction processing is the row store that serves thousands of tiny indexed transactions with ACID guarantees

The mental model in one line: online transaction processing is the workload — and the row-oriented storage engine built to serve it — where a stream of small, short-lived transactions each read or write one row (or a few) by key, under strict ACID guarantees, at high concurrency and single-digit-millisecond latency, backed by B-tree indexes for fast key lookup, MVCC or locking for isolation, and a write-ahead log for durability. Every operational system your business runs — checkout, login, inventory, messaging, bookings — is an OLTP workload, and the row store is the shape that makes it fast because a whole record lives together on one page.

The workload signature of OLTP.

• Point access. Queries filter by primary key or a selective index and return one row or a small handful — WHERE id = 42, WHERE email = '[e-mail]'. The engine seeks straight to the row via an index; it never scans.
• Small, frequent writes. Inserts and updates touch one row at a time and commit immediately. Throughput is measured in transactions per second (thousands to hundreds of thousands), each finishing in ~1 ms.
• High concurrency. Thousands of connections run simultaneously, each holding a short transaction. Isolation (locks or MVCC) keeps them from corrupting each other's view.
• Full-row reads. When OLTP reads a record it usually wants most or all of its columns (render the order page, load the user profile), so storing the whole row together is exactly right.

The physical enablers — what makes a row store fast at this.

• Row-major storage. All of a row's columns are stored contiguously on the same 8 KB page. Fetching one record is one page read; the whole record arrives together. This is the single design choice that defines a row store.
• B-tree indexes. A balanced tree keyed on a column (or set of columns) turns a key lookup into O(log n) page reads — typically 3-4 for millions of rows. The index leaf points at the row's physical location; the engine seeks and reads.
• MVCC and locking. Multi-version concurrency control lets readers see a consistent snapshot without blocking writers; row-level locks serialise conflicting writers. Both are tuned for many short transactions, not a few long ones.
• Write-ahead log (WAL). Every change is written to a sequential log before the data page is updated, so a crash can be replayed to the last committed transaction. Durability — the D in ACID — is the WAL's job.
• Buffer pool. Hot pages (the working set of indexes and recently touched rows) live in memory; point reads hit the cache and never touch disk. This is why the earlier "run a scan on the OLTP box" example is so damaging — it evicts exactly these pages.

The ACID contract — non-negotiable for a system of record.

• Atomicity. A transaction's writes all commit or all roll back. Debiting one account and crediting another is one atomic unit; you never see half of it.
• Consistency. Constraints (foreign keys, uniqueness, checks) hold at every commit. The database rejects a write that would violate an invariant.
• Isolation. Concurrent transactions behave as if serialised (to the chosen isolation level). Two checkouts decrementing the same inventory row cannot both succeed past zero.
• Durability. Once committed, a transaction survives a crash. The WAL guarantees this.

Common interview probes on OLTP.

• "Why is a row store good for OLTP?" — required answer: a whole record lives on one page, so point reads and writes touch one page.
• "How does a point lookup avoid a scan?" — B-tree index seek, O(log n) page reads.
• "What does the WAL do?" — sequential durability log written before data pages; enables crash recovery.
• "Why not run analytics here?" — scans evict the buffer pool and starve the point-read working set.

Worked example — indexed point lookup versus full scan

Detailed explanation. The defining OLTP optimisation is the index seek: turning "find the row where id = 42" from a scan of millions of rows into a handful of page reads. Walk through the difference on a 50M-row orders table, with and without the index, in page reads and latency.

• With a primary-key B-tree. The engine descends the tree (root → internal → leaf), reads the leaf pointer, and fetches the one heap page holding the row.
• Without an index (or with a non-selective predicate). The engine reads every page of the heap until it finds the row — a sequential scan, catastrophic for OLTP latency.

Question. Show the plan and cost of a point lookup with and without the supporting index, and quantify the page reads.

Input.

Access path	Rows examined	Page reads	Latency
PK index seek	1	~4	~0.3 ms
Sequential scan	50,000,000	~390,000	~3,000 ms

Code.

-- The table and its primary-key index
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,      -- B-tree index created automatically
    customer_id BIGINT      NOT NULL,
    total_cents BIGINT      NOT NULL,
    status      TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Point lookup by primary key — an index seek
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE id = 42;
--  Index Scan using orders_pkey on orders
--    Index Cond: (id = 42)
--    rows=1  actual time=0.03..0.03
--  ~4 page reads: 3 to descend the B-tree, 1 for the heap row.

-- Lookup on an UNINDEXED column — forced sequential scan
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'refunded' AND total_cents = 999;
--  Seq Scan on orders
--    Filter: (status = 'refunded' AND total_cents = 999)
--    rows=1  actual time=2950..2950
--  ~390,000 page reads: the entire heap.

Step-by-step explanation.

1. The primary key creates a B-tree index automatically. A lookup by id descends the tree from root to leaf — about 3 page reads for 50M rows because the tree is shallow and wide — then follows the leaf's pointer to the single heap page holding the row. Total: ~4 page reads, ~0.3 ms.
2. The heap page holds the entire row contiguously, so once the engine reaches it, all columns arrive in that one read. This is the row-store payoff for OLTP: point read = one seek + one page.
3. The second query filters on status and total_cents, neither of which has an index. The engine has no seek path, so it falls back to a sequential scan: read every one of the ~390,000 heap pages, apply the filter, discard 49,999,999 rows. Latency is ~3 seconds — a thousand times slower.
4. This is why OLTP schema design is index-driven: every access path the application uses in a hot code path needs a supporting index, or it degrades to a scan. The scan is not just slow for that query; it evicts the buffer pool and hurts everything else.
5. The lesson generalises: a row store is fast for OLTP precisely when queries are selective (touch few rows) and indexed. The moment a query is non-selective (touches most rows), the row store's whole-row pages become a liability — which is the OLAP case, handled by a column store.

Output.

Query	Access path	Page reads	Latency
WHERE id = 42	index seek	~4	~0.3 ms
WHERE status='refunded' AND total_cents=999	seq scan	~390,000	~3,000 ms

Rule of thumb. In OLTP, every hot-path predicate needs a supporting index, or it silently becomes a full scan. Point access is only fast when the engine can seek; design indexes for the queries the application actually runs.

Worked example — the transaction boundary and ACID in action

Detailed explanation. OLTP's headline feature is the transaction: a set of writes that commit atomically and durably. The canonical demonstration is a money transfer — debit one account, credit another — which must be all-or-nothing under concurrency. Walk through the transaction and why each ACID property matters.

• Atomicity. Both the debit and the credit commit, or neither does.
• Isolation. Two concurrent transfers on the same account cannot interleave to produce a lost update.
• Durability. Once committed, the transfer survives a crash via the WAL.

Question. Write a safe money-transfer transaction and explain how row locking and the WAL enforce correctness under concurrency.

Input.

Concern	Mechanism
All-or-nothing	BEGIN / COMMIT / ROLLBACK
No lost update	row lock via SELECT ... FOR UPDATE
Survives crash	write-ahead log
No overdraft	CHECK (balance >= 0) constraint

Code.

CREATE TABLE accounts (
    id      BIGINT PRIMARY KEY,
    balance BIGINT NOT NULL CHECK (balance >= 0)   -- consistency invariant
);

-- Transfer 500 cents from account 1 to account 2, safely
BEGIN;                                              -- atomicity boundary opens

-- Lock both rows to serialise concurrent transfers on these accounts
SELECT balance FROM accounts WHERE id IN (1, 2) FOR UPDATE;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;   -- debit
UPDATE accounts SET balance = balance + 500 WHERE id = 2;   -- credit

COMMIT;    -- both writes become durable together (WAL flushed); locks released
-- If the debit violated CHECK (balance >= 0), the whole txn ROLLBACKs:
-- account 2 is never credited from money account 1 didn't have.

Step-by-step explanation.

1. BEGIN opens the atomicity boundary. Everything until COMMIT is one unit; a failure or explicit ROLLBACK undoes all of it, so you never leave account 1 debited without account 2 credited.
2. SELECT ... FOR UPDATE takes a row-level lock on both accounts. If a second transfer touches the same accounts concurrently, it blocks until this one commits — serialising the two so neither reads a stale balance and overwrites the other's change (the lost-update anomaly).
3. The two UPDATEs modify one row each. Because the rows are locked, the arithmetic is safe: the balance each transaction reads is the balance it writes back against.
4. The CHECK (balance >= 0) constraint enforces consistency: if the debit would drive account 1 negative, the constraint fails, the transaction rolls back, and — crucially — the credit to account 2 is undone too. Atomicity and consistency work together.
5. COMMIT flushes the WAL records for both updates to durable storage before returning success. If the server crashes one instant later, recovery replays the WAL and the transfer is intact. This is durability — and it is why OLTP databases fsync the log on commit, trading a little latency for the guarantee.

Output.

Event	account 1	account 2	Committed?
Before	500	0	—
After successful transfer	0	500	yes (WAL durable)
Debit would overdraw (balance 200, transfer 500)	200	0	no (CHECK fails, full rollback)
Crash mid-commit	recovered to last consistent state	recovered	WAL replay

Rule of thumb. Wrap multi-row invariants in an explicit transaction, lock the rows you compute against with FOR UPDATE, and lean on constraints for consistency. ACID is not overhead in OLTP — it is the product; the WAL fsync on commit is the price of durability.

Worked example — concurrency and connection pooling under load

Detailed explanation. OLTP databases serve thousands of concurrent short transactions, but each connection consumes memory and a backend process/thread, so raw connection counts must be bounded. The standard pattern is a connection pool that multiplexes many application requests over a small, fixed set of database connections. Walk through why, and how to size it.

• The problem. 5,000 app instances each opening a direct connection = 5,000 Postgres backends, each ~10 MB, plus lock and scheduler contention → the database falls over.
• The fix. A pooler (PgBouncer, or the app framework's pool) holds ~100 connections and hands them to requests transiently.
• The sizing. Pool size ≈ number of CPU cores × a small factor, not number of app instances.

Question. Explain why unbounded connections crush an OLTP database and compute a sane pool size.

Input.

Parameter	Value
App instances	5,000
Naive direct connections	5,000
DB CPU cores	16
Memory per backend	~10 MB
Target pool size	~2-4 × cores ≈ 40-64

Code.

# Application-side pool — many requests share few DB connections
from psycopg2.pool import ThreadedConnectionPool

pool = ThreadedConnectionPool(
    minconn=8,
    maxconn=48,          # ~3x the 16 cores; NOT one-per-app-instance
    host="oltp-primary.internal", dbname="production", user="app",
)

def get_order(order_id: int) -> dict:
    conn = pool.getconn()                 # borrow briefly
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT id, customer_id, total_cents, status "
                        "FROM orders WHERE id = %s", (order_id,))
            row = cur.fetchone()
    finally:
        pool.putconn(conn)                # return immediately after the point read
    return {"id": row[0], "customer_id": row[1],
            "total_cents": row[2], "status": row[3]}

Why unbounded connections fail
==============================
5,000 direct backends × 10 MB      = 50 GB just for connection state
   + context-switch storm across 5,000 processes on 16 cores
   + lock-manager and snapshot contention
=> throughput COLLAPSES though each query is tiny.

48 pooled connections on 16 cores
   = ~3 in-flight per core, queue the rest for microseconds
=> full CPU utilisation, minimal contention, stable p99.

Step-by-step explanation.

1. Each OLTP transaction is tiny (~1 ms), so a single connection can serve hundreds of requests per second. The bottleneck is not connections but CPU cores actually executing work — a 16-core box can only run 16 queries truly in parallel.
2. Opening one connection per app instance (5,000) creates 5,000 backend processes competing for 16 cores. The context-switching, per-backend memory, and lock-manager contention overwhelm the server long before the queries themselves would.
3. A pool caps live connections at a small multiple of cores (~48 here). Requests borrow a connection for the millisecond they need it and return it; the pool queues excess requests for microseconds rather than spawning unbounded backends.
4. The pool size is derived from cores, not from load: more app instances do not need more connections, because each transaction is so short that a small pool achieves high utilisation. This is counter-intuitive to newcomers who scale connections with traffic.
5. This concurrency model — many short transactions multiplexed over few connections — is characteristic of OLTP and completely unlike OLAP, where a handful of long-running queries each want a big slice of CPU and memory. The concurrency shapes are opposite, which is another reason the two workloads want different systems.

Output.

Setup	Live backends	CPU behaviour	p99 latency
5,000 direct connections	5,000	thrashing	seconds / timeouts
48-connection pool	48	~full utilisation	~1 ms stable

Rule of thumb. Size the OLTP connection pool from CPU cores (a few per core), never from app-instance count. Short transactions multiplex beautifully over a small pool; unbounded connections crush the database with overhead unrelated to the actual query work.

Data-engineering interview question on OLTP design

A senior interviewer might ask: "Design the write path for a payments service on PostgreSQL. It must record a payment, decrement a wallet balance, and never double-charge under retries or concurrency. Cover the schema, the transaction, the index strategy for the read path, and how you keep this from ever competing with analytics."

Solution Using an idempotent transactional write on a row store

-- 1. Schema — row store, indexed for the point-access paths
CREATE TABLE wallets (
    user_id      BIGINT PRIMARY KEY,
    balance_cents BIGINT NOT NULL CHECK (balance_cents >= 0)
);

CREATE TABLE payments (
    id              BIGSERIAL PRIMARY KEY,
    user_id         BIGINT      NOT NULL REFERENCES wallets(user_id),
    amount_cents    BIGINT      NOT NULL CHECK (amount_cents > 0),
    idempotency_key TEXT        NOT NULL,           -- client-supplied, unique per attempt
    status          TEXT        NOT NULL DEFAULT 'captured',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (idempotency_key)                        -- the double-charge guard
);
CREATE INDEX idx_payments_user ON payments (user_id, created_at DESC);

-- 2. The idempotent write path
BEGIN;

-- Insert the payment; the UNIQUE(idempotency_key) makes a retry a no-op
INSERT INTO payments (user_id, amount_cents, idempotency_key)
VALUES (7, 500, 'req-abc-123')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;
-- If RETURNING gives no row, this is a duplicate retry -> ROLLBACK and return
-- the original result; the balance is NOT decremented again.

-- Lock the wallet row and decrement atomically
SELECT balance_cents FROM wallets WHERE user_id = 7 FOR UPDATE;
UPDATE wallets SET balance_cents = balance_cents - 500 WHERE user_id = 7;

COMMIT;

# 3. Application wrapper — safe under retries and concurrency
def capture_payment(pool, user_id: int, amount: int, idem_key: str) -> str:
    conn = pool.getconn()
    try:
        with conn:                               # BEGIN / COMMIT (rollback on error)
            with conn.cursor() as cur:
                cur.execute("""
                    INSERT INTO payments (user_id, amount_cents, idempotency_key)
                    VALUES (%s, %s, %s)
                    ON CONFLICT (idempotency_key) DO NOTHING
                    RETURNING id
                """, (user_id, amount, idem_key))
                row = cur.fetchone()
                if row is None:
                    return "duplicate"           # retry: already captured, no re-charge
                cur.execute("SELECT balance_cents FROM wallets "
                            "WHERE user_id = %s FOR UPDATE", (user_id,))
                cur.execute("UPDATE wallets SET balance_cents = balance_cents - %s "
                            "WHERE user_id = %s", (amount, user_id))
        return "captured"
    finally:
        pool.putconn(conn)

Step-by-step trace.

Step	State / effect	Guarantee
INSERT payment (new idem key)	payment row created	atomicity boundary open
ON CONFLICT DO NOTHING (retry)	no row inserted; RETURNING empty	idempotency (no double-charge)
SELECT wallet FOR UPDATE	wallet row locked	isolation (serialise concurrent captures)
UPDATE balance -= amount	balance decremented once	consistency (CHECK >= 0)
COMMIT	payment + balance durable together	durability (WAL fsync)
Concurrent second capture	blocks on the wallet lock, runs after	no lost update

After deployment, a client that retries a timed-out request sends the same idempotency_key; the UNIQUE constraint turns the second insert into a no-op, RETURNING is empty, and the wallet is never decremented twice. Concurrent captures on the same wallet serialise on the FOR UPDATE lock. Every read path (payment history, wallet balance) is a point/index access, so the payments service never issues a scan and never competes with analytics — those queries live on the warehouse.

Output:

Scenario	payments rows	wallet balance	Result
First request (balance 800)	1	300	captured
Retry, same idem key	1 (unchanged)	300 (unchanged)	duplicate (safe)
Concurrent second capture (300 left, amount 500)	insert ok, UPDATE fails CHECK	300 (rolled back)	rejected, no overdraft
Crash after COMMIT	1	300	recovered via WAL

Why this works — concept by concept:

• Row store + point indexes — payments and wallets are read by key (id, user_id), so a row store with B-tree indexes answers every hot-path query with a seek, not a scan. The whole record lives on one page.
• Idempotency key + UNIQUE — the double-charge guard. A retried request carries the same key; the unique constraint makes the second insert a no-op, so the balance decrement — gated on a successful insert — runs exactly once.
• FOR UPDATE row lock — serialises concurrent captures on the same wallet, eliminating the lost-update anomaly without locking the whole table. Isolation scoped to the one row keeps concurrency high.
• Transaction + CHECK + WAL — atomicity binds the payment insert and the balance decrement; the CHECK (balance >= 0) constraint blocks overdrafts and rolls the whole unit back; the WAL makes the commit durable across crashes.
• Cost — a few index seeks and one WAL fsync per payment: O(log n) per operation, sub-millisecond, at high concurrency. The workload never scans, so it never touches the analytical path — that isolation is what keeps p99 flat under report load.

Database
Topic — database
Database transaction and indexing problems

Practice →

SQL Topic — sql SQL transaction and constraint problems

Practice →

---

3. OLAP — column-stores tuned for large scans and aggregation

online analytical processing is the column store that scans few columns of many rows and reduces them with aggregation

The mental model in one line: online analytical processing is the workload — and the column-oriented storage engine built to serve it — where a small number of long-running queries each scan a huge number of rows but only a few columns, reduce the result with aggregation and grouping, tolerate seconds of latency and minutes of staleness, and win by storing each column contiguously so a scan reads only the columns it needs, compresses them heavily, and executes over batches of values at a time. Every dashboard, BI report, cohort analysis, and machine-learning feature table is an OLAP workload, and the column store is the shape that makes it fast because a scan of three columns reads three columns' worth of bytes, not the whole table.

The workload signature of OLAP.

• Large scans, few columns. A revenue query reads region, created_at, total_cents across every order ever placed — three columns, hundreds of millions of rows. It never fetches a whole record; it fetches slices of a few columns.
• Aggregation and grouping. The scan's output is collapsed by SUM, COUNT, AVG, GROUP BY, window functions. Millions of input rows become a few dozen output rows — the answer is small even though the input is huge.
• Low concurrency, high throughput. A handful of analysts and dashboards run queries; each query wants a large share of CPU and memory to finish a big scan quickly. Throughput (bytes scanned per second) matters more than per-query latency.
• Read-mostly, bulk-append. Data arrives in batches (loads, CDC micro-batches) and is rarely updated in place. There is no per-row transactional churn, so the engine can optimise ruthlessly for reads.

The physical enablers — what makes a column store fast at this.

• Columnar storage. Each column's values are stored contiguously in their own segments. Scanning one column reads only that column's blocks; the other columns are never touched. This is the single design choice that defines a column store, and it is the mirror image of the row store.
• Compression. Because a column holds values of one type with lots of repetition (many region = 'US', many identical dates), run-length encoding, dictionary encoding, and delta encoding shrink it 5-10× or more. Less data on disk means less I/O per scan — compression is a performance feature, not just a storage saving.
• Vectorised execution. The engine processes columns in batches of thousands of values at once (a "vector"), amortising per-row overhead and using SIMD CPU instructions. A row-at-a-time engine cannot compete on a big aggregation.
• Partition pruning and min/max zone maps. Data is partitioned (often by date) and each block carries min/max metadata, so a query with WHERE created_at >= '2026-01-01' skips entire partitions and blocks whose range cannot match — reading a fraction of even the columns it needs.
• Denormalised / star-schema modelling. A central fact table plus small dimension tables minimises the joins a scan must do and maximises the rows-per-block a scan streams. The model is chosen to suit the engine.

The star schema — the dimensional model OLAP is built around.

• Fact table. One row per business event (an order line, a payment, a page view), holding foreign keys to dimensions plus numeric measures (quantity, amount_cents). It is long and narrow and grows forever; it is what scans hit.
• Dimension tables. Small descriptive tables (customer, product, date, region) joined to the fact by key. They give the fact's measures human context ("revenue by region by month").
• Why it fits OLAP. Facts are scanned and aggregated; dimensions are small enough to sit in memory for the join. The star shape keeps joins shallow and scans wide — exactly what a vectorised column engine wants.
• Grain. The "grain" is what one fact row represents (one order line, one day-store-product). Fixing the grain first is the cardinal rule of dimensional modelling; everything else follows from it.

Common interview probes on OLAP.

• "Why is a column store good for OLAP?" — required answer: a scan reads only the columns it needs, and columns compress heavily.
• "Why does columnar compress better than row?" — one type, one column, lots of repetition → RLE/dictionary/delta encoding.
• "What is a star schema and why here?" — fact + dimensions; shallow joins, wide scans, analyst-friendly.
• "Why can OLAP be stale?" — it is a derived copy of the source of truth, not the source of truth.

Worked example — a wide aggregation scan on a column store

Detailed explanation. The archetypal OLAP query aggregates a measure over a huge fact table, grouped by a couple of dimensions, filtered by a date range. Walk through what a column store reads to answer it and why it is orders of magnitude cheaper than the same query on a row store.

• The query. Revenue by region by month over the last year, from a 500M-row fact_orders.
• The columns touched. region_id, created_at, total_cents — three of, say, twenty columns.
• The pruning. The date filter skips all partitions before this year.

Question. Show the query, estimate the bytes scanned on a column store versus a row store, and explain the difference.

Input.

Factor	Column store	Row store
Columns physically read	3 of 20	all 20
Compression	~8× on the 3 columns	little (mixed types per page)
Partition pruning	skips older partitions	scans full heap
Bytes scanned (500M rows, ~200 B/row raw)	~5-6 GB	~100 GB
Runtime	~1-2 s	minutes

Code.

-- Fact table in the warehouse (columnar, partitioned by day)
-- fact_orders(order_id, region_id, customer_id, product_id, created_at,
--             total_cents, tax_cents, discount_cents, ... ~20 columns)

SELECT r.region_name,
       date_trunc('month', f.created_at) AS month,
       SUM(f.total_cents) / 100.0        AS revenue_usd,
       COUNT(*)                          AS order_count
FROM   fact_orders f
JOIN   dim_region  r ON r.region_id = f.region_id
WHERE  f.created_at >= date_trunc('year', current_date)
GROUP  BY r.region_name, date_trunc('month', f.created_at)
ORDER  BY month, revenue_usd DESC;

What the column store actually reads
====================================
Partition pruning: keep only 2026 day-partitions   -> ~40% of rows
Column projection: read region_id, created_at, total_cents only  -> 3 / 20 columns
Compression:       those 3 columns encoded ~8x       -> ~1/8 the bytes
dim_region:        tiny (dozens of rows) -> broadcast join in memory
Net bytes scanned: ~5-6 GB, streamed in vectorised batches -> ~1-2 s

Same query on the row store
===========================
No projection benefit: every 8 KB page holds all 20 columns
-> must read ~100 GB heap to use 3 columns -> minutes + cache eviction

Step-by-step explanation.

1. Partition pruning uses the day-partition metadata to discard every partition outside the current year before reading a single value. A time-filtered analytical query touches only the relevant partitions — the first big reduction.
2. Column projection reads only region_id, created_at, and total_cents. Because each column is stored contiguously, the engine reads three columns' segments and never touches the other seventeen. On a row store this is impossible — the columns are interleaved on every page.
3. Compression shrinks those three columns ~8×: region_id is low-cardinality (dictionary/RLE), created_at is sorted-ish (delta encoding), total_cents packs into few bits. Less data on disk means less I/O, and the engine decompresses in vectorised batches.
4. The dim_region join is against a tiny table (dozens of regions), so the engine broadcasts it into memory and joins without a shuffle. Star-schema joins are cheap precisely because dimensions are small.
5. The aggregation reduces ~200M scanned rows to a few dozen output rows via a hash aggregate over vectorised batches. The result is small; the work is the scan, and the scan is cheap because of layout + pruning + compression. Net: ~1-2 s versus minutes on a row store — the entire justification for OLAP.

Output.

region_name	month	revenue_usd	order_count
US	2026-01	4,182,900.00	210,441
EU	2026-01	2,904,110.00	148,203
US	2026-02	4,391,220.00	219,884
APAC	2026-02	1,772,505.00	96,010

Rule of thumb. An OLAP query's cost is dominated by bytes scanned, and a column store minimises that three ways at once — partition pruning (fewer rows), column projection (fewer columns), and compression (fewer bytes per column). Select only the columns you need; SELECT * on a fact table throws all three advantages away.

Worked example — why columns compress and rows do not

Detailed explanation. Compression is the quiet superpower of column stores. It works because a column contains values of a single type with heavy repetition, which the classic encodings exploit. Walk through the three main encodings on a real column and compute the ratio.

• Run-length encoding (RLE). Sorted or low-cardinality columns collapse runs of identical values into (value, count) pairs.
• Dictionary encoding. Low-cardinality strings map to small integer codes; the dictionary is stored once.
• Delta encoding. Sorted numeric/timestamp columns store differences between consecutive values, which are small and pack tightly.

Question. Estimate the compression ratio for three columns of a fact table and explain why a row store cannot match it.

Input.

Column	Cardinality	Best encoding	Raw size	Encoded size
region_id (5 regions)	very low	RLE / dictionary	8 B × 500M = 4 GB	~50 MB
status ('paid'/'refunded')	very low	dictionary	8 B × 500M = 4 GB	~30 MB
created_at (sorted)	high but ordered	delta	8 B × 500M = 4 GB	~500 MB

Code.

Column: region_id  (values repeat heavily, low cardinality)
  Raw   : [1,1,1,1,2,2,2,3,3,3,3,3, ...]  (8 bytes each)
  RLE   : [(1,4),(2,3),(3,5), ...]         -> a few pairs per block
  Ratio : ~80x

Column: status  (2 distinct values)
  Dictionary: {0:'paid', 1:'refunded'}     stored once
  Data      : [0,0,0,1,0,0,1, ...]          packed to ~1 bit/row
  Ratio     : ~64x+

Column: created_at  (monotonic-ish timestamps)
  Raw   : [1735689600, 1735689601, 1735689605, ...]
  Delta : [1735689600, +1, +4, +3, ...]     small ints, bit-packed
  Ratio : ~8x

Row store, same data:
  A page interleaves region_id, status, created_at, total_cents, ...
  Mixed types + low intra-page repetition -> generic page compression ~2-3x at best.

Step-by-step explanation.

1. region_id has five distinct values across 500M rows, so within any block the same value repeats thousands of times. RLE stores each run as a (value, count) pair, collapsing 4 GB to tens of megabytes — ~80×.
2. status has two values. Dictionary encoding maps them to codes {0,1} and packs the data down to about one bit per row plus a tiny dictionary — ~64× or better.
3. created_at is (roughly) sorted because rows are appended over time. Delta encoding stores the difference between consecutive timestamps; those deltas are tiny integers that bit-pack tightly — ~8×.
4. All three work because the column is homogeneous: one type, one meaning, contiguous. The encoder sees long stretches of similar values and exploits them. This is only possible when values of the same column are physically adjacent — the column-store layout.
5. A row store interleaves a region_id, a status, a created_at, a total_cents, and more on every page. The bytes next to each other are different types with little repetition, so generic page compression manages maybe 2-3×. The layout, not the algorithm, is the difference — which is why compression is fundamentally an OLAP advantage.

Output.

Column	Row-store compression	Column-store compression
region_id	~2-3×	~80×
status	~2-3×	~64×
created_at	~2-3×	~8×
Net scan bytes for the 3	high	~10-20× lower

Rule of thumb. Columnar compression is a scan-speed feature, not just a storage discount: fewer bytes on disk means fewer bytes read per query. Low-cardinality and sorted columns compress best, so model dimensions as small keys and keep fact tables sorted by the columns you filter on.

Worked example — building a star schema from an operational model

Detailed explanation. OLAP data is modelled as a star, not as the normalised operational schema. The transform denormalises the source's many small tables into one fact table plus a few dimensions. Walk through converting an operational order model into a star schema for the warehouse.

• Operational (OLTP). Normalised: orders, order_items, products, customers, regions — many tables, foreign keys everywhere, no redundancy.
• Dimensional (OLAP). fact_order_items (the grain: one row per item sold) + dim_product, dim_customer, dim_date, dim_region.

Question. Design the fact grain and the dimensions, and write the query that populates the fact table from the operational source.

Input.

Concept	Operational	Dimensional
Grain	rows across 5 normalised tables	one row per order item
Measures	scattered	quantity, unit_price_cents, line_total_cents
Descriptors	in product/customer/region tables	in dim_* tables
Time	created_at column	dim_date with date_key

Code.

-- Fact table: grain = one row per order item
CREATE TABLE fact_order_items (
    order_item_id    BIGINT  NOT NULL,
    date_key         INT     NOT NULL,   -- FK -> dim_date
    product_key      BIGINT  NOT NULL,   -- FK -> dim_product
    customer_key     BIGINT  NOT NULL,   -- FK -> dim_customer
    region_key       INT     NOT NULL,   -- FK -> dim_region
    quantity         INT     NOT NULL,   -- measure
    unit_price_cents BIGINT  NOT NULL,   -- measure
    line_total_cents BIGINT  NOT NULL    -- measure (quantity * unit_price)
);

-- Populate the fact by denormalising the operational tables
INSERT INTO fact_order_items
SELECT oi.id                                     AS order_item_id,
       to_char(o.created_at, 'YYYYMMDD')::int    AS date_key,
       oi.product_id                             AS product_key,
       o.customer_id                             AS customer_key,
       c.region_id                               AS region_key,
       oi.quantity                               AS quantity,
       oi.unit_price_cents                       AS unit_price_cents,
       oi.quantity * oi.unit_price_cents         AS line_total_cents
FROM   orders       o
JOIN   order_items  oi ON oi.order_id   = o.id
JOIN   customers    c  ON c.id          = o.customer_id;

-- A typical analyst query is now a shallow star join:
SELECT p.category, d.year, SUM(f.line_total_cents)/100.0 AS revenue
FROM   fact_order_items f
JOIN   dim_product p ON p.product_key = f.product_key
JOIN   dim_date    d ON d.date_key    = f.date_key
GROUP  BY p.category, d.year;

Step-by-step explanation.

1. The first decision is the grain: one fact row per order item. This is the finest level analysts need (they can always roll up to order or customer level, never down). Fixing the grain first is the cardinal rule; every measure and dimension is defined relative to it.
2. Measures — quantity, unit_price_cents, line_total_cents — are the numeric values that get aggregated. line_total_cents is pre-computed at load time so scans never recompute it, trading a little storage for scan speed.
3. Dimensions replace the operational foreign keys with dimension keys: date_key, product_key, customer_key, region_key. Each points at a small descriptive table. Note region_key is denormalised up from the customer — in a star, the fact carries all the keys directly so joins stay one hop deep.
4. The populate query does the denormalisation once, at load time, joining the five normalised operational tables into the flat fact. This is the "T" in ETL: the expensive joins happen during the load, not during every analyst query.
5. The resulting analyst query is a shallow star join — fact to a couple of small dimensions — which a column store executes with in-memory broadcast joins and a vectorised aggregate. Compare this to the operational model, where the same question would require joining five normalised tables at query time, on a row store, competing with production. The star schema is the shape that makes OLAP fast and analyst-friendly.

Output.

category	year	revenue
Electronics	2026	12,904,551.00
Apparel	2026	7,118,220.00
Home	2026	5,442,905.00

Rule of thumb. Model OLAP as a star: fix the fact grain first, pre-compute measures at load time, and denormalise foreign keys into the fact so analyst joins stay one hop deep. The normalised operational schema is correct for OLTP and wrong for OLAP — the transform between them is the whole point of the pipeline.

Data-engineering interview question on OLAP modelling

A senior interviewer might ask: "Design the warehouse model and query for a revenue dashboard that must answer 'revenue by product category by month by region' over three years of orders in under two seconds. Cover the storage layout, the schema, the partitioning, and why this can't just run on a read replica of production."

Solution Using a partitioned columnar fact table on a star schema

-- 1. Columnar fact table, partitioned by month, clustered by the filter columns
CREATE TABLE fact_order_items (
    date_key         INT     NOT NULL,
    month_key        INT     NOT NULL,   -- partition column: YYYYMM
    product_key      BIGINT  NOT NULL,
    region_key       INT     NOT NULL,
    customer_key     BIGINT  NOT NULL,
    quantity         INT     NOT NULL,
    line_total_cents BIGINT  NOT NULL
)
PARTITION BY RANGE (month_key)          -- partition pruning on the date filter
CLUSTER BY (region_key, product_key);   -- co-locate values that get filtered/grouped

-- 2. Small dimensions (broadcast-joined in memory)
-- dim_date(date_key, month_key, month, quarter, year)
-- dim_product(product_key, product_name, category)
-- dim_region(region_key, region_name)

-- 3. The dashboard query
SELECT dp.category,
       dd.month,
       dr.region_name,
       SUM(f.line_total_cents) / 100.0 AS revenue_usd
FROM   fact_order_items f
JOIN   dim_date    dd ON dd.date_key    = f.date_key
JOIN   dim_product dp ON dp.product_key = f.product_key
JOIN   dim_region  dr ON dr.region_key  = f.region_key
WHERE  f.month_key BETWEEN 202401 AND 202612   -- prunes to 36 partitions
GROUP  BY dp.category, dd.month, dr.region_name
ORDER  BY dd.month, revenue_usd DESC;

Why it hits < 2 s (and why the replica can't)
=============================================
Column projection : reads month_key, product_key, region_key, line_total_cents (4 of 7)
Partition pruning : month_key filter -> only 36 monthly partitions scanned
Compression       : region_key/product_key dictionary+RLE; measures bit-packed
Dimension joins   : dim_* are tiny -> broadcast in memory, no shuffle
Vectorised agg    : hash aggregate over batches of thousands of rows

On an OLTP read replica (row store):
  every page holds all 7+ columns -> must read whole heap to use 4 columns
  no columnar compression, no vectorised aggregate
  and the scan evicts the OLTP buffer pool -> production latency spike

Step-by-step trace.

Layer	Choice	Effect
Storage	columnar	read 4 of 7 columns only
Partitioning	RANGE(month_key)	3-year filter prunes to 36 partitions
Clustering	(region_key, product_key)	min/max zone maps skip non-matching blocks
Dimensions	small, broadcast	one-hop joins, no shuffle
Aggregation	vectorised hash agg	millions of rows → dozens, in batches
Isolation	dedicated warehouse	never touches production cache

After deployment, the dashboard query reads four columns of 36 monthly partitions, decompresses them in vectorised batches, broadcast-joins three tiny dimension tables, and hash-aggregates to a small grid of category × month × region — all in well under two seconds. The same query on the production replica would read the full heap of all columns, run for minutes, and evict the OLTP working set, which is exactly why the warehouse exists as a separate columnar system.

Output:

Metric	Columnar warehouse	OLTP replica (row store)
Columns read	4 of 7	all
Partitions scanned	36 of 108	full heap
Runtime	< 2 s	minutes
Production impact	none	buffer-pool eviction
Freshness	~minutes (loaded)	live but harmful

Why this works — concept by concept:

• Columnar storage — the dashboard touches four columns; the column store reads four columns' bytes and skips the rest. Projection is free because columns are stored apart.
• Partition pruning — partitioning by month_key lets the three-year filter discard all but the 36 relevant partitions before reading data, and zone maps skip further blocks by min/max.
• Compression + vectorised execution — low-cardinality keys and packed measures shrink the bytes scanned, and the engine aggregates over batches of thousands of values using SIMD, not one row at a time.
• Star-schema broadcast joins — the dimensions are small enough to sit in memory, so the fact-to-dimension joins are one hop with no data shuffle — the shape the engine is optimised for.
• Cost — the query is O(bytes of 4 columns × 36 partitions), dominated by a cheap scan, isolated from production. On a row store it is O(full heap) plus a production incident. The layout is the entire difference.

Aggregation
Topic — aggregation
Aggregation and GROUP BY problems

Practice →

Analysis Topic — data-analysis Data-analysis and dimensional-query problems

Practice →

---

4. Row vs column storage — the physical difference that drives everything

row store vs column store is the same table laid out two ways — and the layout decides which bytes every query must read

The mental model in one line: row store vs column store is not two kinds of data but two physical arrangements of the same logical table — row-major stores each record's columns contiguously so one record is one read (great for point access, terrible for wide scans), while column-major stores each column's values contiguously so one column is one read (great for scanning-and-aggregating, terrible for fetching whole records) — and every OLTP-vs-OLAP trade-off in this article ultimately reduces to which bytes a given query is forced to touch under a given layout. Understand this one diagram and you understand the whole topic.

The two layouts, concretely.

• Row-major (OLTP). On disk, a block holds complete rows: [id=1,name=A,amt=10,ts=..][id=2,name=B,amt=20,ts=..].... To read row 2 you read one block and get all its columns. To read the amt of every row you must read every block (because amt is scattered inside each row).
• Column-major (OLAP). On disk, a block holds one column's values: an id block [1,2,3,...], a name block [A,B,C,...], an amt block [10,20,30,...]. To read the amt of every row you read one contiguous amt region. To read all of row 2 you must touch every column's block and stitch the pieces back together.
• The symmetry. Each layout is optimal for exactly the access pattern the other is bad at. There is no free lunch — a layout that makes point access one read makes wide scans read everything, and vice versa.

What a point query reads in each layout.

• Row store. One index seek + one block = the whole record. O(1) blocks. This is why OLTP loves row-major.
• Column store. The row's value is in a different physical block for every column; reconstructing one full record touches N column-blocks. O(columns) scattered reads — slow for a single record, which is why OLAP is bad at point access.

What a scan-and-aggregate reads in each layout.

• Row store. To aggregate one column over all rows, it reads every block (all columns), using a fraction of what it reads. O(all columns × all rows) bytes. This is why OLTP is bad at analytics.
• Column store. It reads only the scanned columns' contiguous regions, compressed. O(scanned columns × all rows) bytes, often 10-20× less after compression. This is why OLAP loves column-major.

The refinements — encoding, late materialisation, and hybrids.

• Encoding for free compression. Because a column is homogeneous, RLE / dictionary / delta encodings shrink it dramatically (Section 3). Row-major cannot, because adjacent bytes are different types.
• Late materialisation. A column engine filters and aggregates on compressed column codes and only reconstructs full rows (stitches columns back together) at the very end, for the small result — minimising the expensive part.
• Vectorised, cache-friendly scans. Contiguous same-type values stream through CPU caches and SIMD lanes efficiently; row-major's interleaved layout wastes cache on unused columns.
• Hybrid / PAX layouts. Formats like Parquet/ORC and engines with PAX store column chunks within row groups: columnar benefits (projection, compression) with block-level locality. HTAP engines (Section 5) keep both a true row store and a true column store and route each query to the right one.

Common interview probes on storage layout.

• "Why did the warehouse get faster switching to columnar?" — required answer: scans read only the needed columns' bytes, compressed, instead of the whole heap.
• "Why is a column store bad at point lookups?" — one record's columns are in N different blocks; reconstruction touches all of them.
• "What is late materialisation?" — filter/aggregate on column codes, reconstruct full rows only for the final small result.
• "What is Parquet?" — a hybrid columnar file format (column chunks within row groups) — the lakehouse storage layer.

Worked example — bytes-read accounting for a point query and a scan

Detailed explanation. The cleanest way to internalise the layout difference is to count bytes for the same two queries under both layouts on the same table. Walk through a 10-column, 100M-row table with a point lookup and a single-column aggregate.

• Table. 100M rows × 10 columns × ~20 B/column = ~20 GB logical.
• Query A (point). WHERE id = 42 — fetch one whole row.
• Query B (scan). SUM(amount) — aggregate one column over all rows.

Question. Compute the bytes each query must read under row-major and column-major layouts, and state which layout wins each.

Input.

Query	Row-major reads	Column-major reads
A: point, one full row	~1 block (~200 B) via index	10 scattered column-blocks (~one value each)
B: SUM(amount), all rows	whole heap (~20 GB)	amount column only (~2 GB raw, ~0.3 GB compressed)

Code.

Table: 100,000,000 rows x 10 columns x ~20 B = ~20 GB

Query A — SELECT * FROM t WHERE id = 42   (point access)
  Row-major   : index seek -> 1 heap block -> the full 200 B row.   WINS.
  Column-major: id-block seek finds position p, then read value p
                from EACH of 10 column segments -> 10 scattered reads.

Query B — SELECT SUM(amount) FROM t        (wide scan)
  Row-major   : must read all 20 GB (every column of every row)
                to extract the one 'amount' field per row.
  Column-major: read ONLY the amount column: 100M x 20 B = 2 GB raw,
                ~0.3 GB after delta/bit-packing.                  WINS (~60x less).

Step-by-step explanation.

1. Query A under row-major is the ideal OLTP case: the B-tree seek lands on one heap block, and that block holds the entire 200-byte row contiguously. One logical read returns every column. Cost: ~1 block.
2. Query A under column-major is the OLAP weakness: the engine finds row 42's ordinal position, then must read that position from all ten separate column segments and reassemble the record — ten scattered reads for one row. A column store can do point lookups, but it pays N reads to reconstruct one row.
3. Query B under row-major is the OLTP weakness laid bare: to sum amount it reads all 20 GB because amount is interleaved inside every row on every page. Ninety percent of the bytes read are the other nine columns, discarded. Cost: whole heap.
4. Query B under column-major is the OLAP payoff: the amount values are one contiguous region, so the engine reads ~2 GB raw (or ~0.3 GB compressed) and never touches the other columns. Roughly 60× fewer bytes than row-major for the same answer.
5. The accounting makes the whole article concrete: layout does not change what the data is, only which bytes a query is forced to read. Point-access workloads want row-major; scan-aggregate workloads want column-major; and because a table can only be physically laid out one way at a time, you keep two copies — OLTP row-major and OLAP column-major.

Output.

Query	Row-major bytes	Column-major bytes	Winner
A: point lookup	~200 B (1 block)	~10 scattered reads	row-major
B: SUM one column	~20 GB (full heap)	~0.3 GB (compressed col)	column-major

Rule of thumb. Reason about any storage-layout question by counting bytes read: a point query wants the whole row in one place (row-major), a scan wants one column in one place (column-major). The workload that dominates decides the layout; the other workload gets its own copy.

Worked example — encoding a column three ways

Detailed explanation. Column-major unlocks encodings that are impossible row-major. Walk through encoding the same region column with run-length, dictionary, and bit-packing, and show why the encoded form is also faster to scan, not just smaller.

• Run-length. Consecutive equal values → (value, count).
• Dictionary. Distinct values → integer codes + a small dictionary.
• Bit-packing. Codes that fit in k bits are packed k bits each instead of a full byte/word.

Question. Encode a low-cardinality column and show the scan operating directly on codes.

Input.

Stage	Representation	Size (per 1M rows)
Raw strings	'US','US','EU',...	~3 MB
Dictionary codes	0,0,1,... (1 byte)	~1 MB
Bit-packed (2 bits/code)	packed	~0.25 MB
RLE on sorted runs	(0,N),(1,M),...	~few KB

Code.

region raw : ['US','US','US','EU','EU','APAC','APAC','APAC','APAC', ...]

Dictionary : {0:'US', 1:'EU', 2:'APAC'}       (stored once per block)
Codes      : [0,0,0,1,1,2,2,2,2, ...]          (1 byte each -> bit-pack to 2 bits)

RLE (if block is sorted by region):
             [(0,3),(1,2),(2,4), ...]          (a handful of pairs)

Scan: SELECT region, COUNT(*) ... GROUP BY region
  operates on CODES, not strings:
    counts = [0,0,0]
    for (code, run_len) in rle_pairs:  counts[code] += run_len
  -> aggregate never decodes strings until the final small result.

-- The query the engine answers straight off the encoded column
SELECT region, COUNT(*) AS orders
FROM   fact_orders
WHERE  created_at >= '2026-01-01'
GROUP  BY region;

Step-by-step explanation.

1. The raw column stores repeated strings — wasteful in space and slow to compare. Dictionary encoding maps the few distinct regions to integer codes and stores the dictionary once per block, so the data becomes a stream of small integers.
2. Those codes fit in 2 bits (three values), so bit-packing stores them 2 bits each instead of 8-32 bits — another 4-16× on top of the dictionary. The column is now a fraction of its raw size.
3. If the block is sorted (or naturally clustered) by region, run-length encoding collapses runs of the same code into (code, count) pairs — a handful of pairs per block instead of a million codes.
4. The scan aggregates directly on the encoded form: to COUNT(*) GROUP BY region it adds run lengths into per-code counters, never materialising a single string until the final three-row result. This is late materialisation — work on codes, decode at the end.
5. This is why columnar compression is a speed feature: fewer bytes to read and cheaper operations (integer/SIMD math on codes vs string comparisons). None of it is possible row-major, where the region value is wedged between unrelated columns on every page.

Output.

region	orders
US	3,204,118
EU	1,902,551
APAC	1,118,942

Rule of thumb. Columnar encoding shrinks the bytes and speeds the operators at once, because the engine computes on compact codes and decodes only the final result. Design fact tables with low-cardinality dimension keys and sort by your common filter columns to maximise RLE and pruning.

Data-engineering interview question on storage layout

A senior interviewer might ask: "Your team moved the analytics workload from a Postgres read replica to a columnar warehouse and a report that took 90 seconds now takes 1.5 seconds — a 60× speedup — even though it's the same SQL over the same data volume. Explain, at the storage level, exactly where the 60× comes from, and where a columnar store would actually be slower."

Solution Using a bytes-read decomposition of the 60x speedup

Report: SELECT category, SUM(revenue) FROM sales_wide GROUP BY category
Table : 300,000,000 rows x 24 columns, ~250 B/row = ~75 GB logical

--- Row store (Postgres replica) ---
Seq Scan reads every 8 KB page (all 24 columns) to use 2 columns:
   bytes read  ~= 75 GB
   compression ~= 1.2x (generic page compression on mixed types)
   execution    = row-at-a-time
   + evicts OLTP buffer pool
   => ~90 s

--- Column store (warehouse) ---
Column projection: read category + revenue (2 of 24 columns)
   raw 2-column bytes ~= 75 GB * (2/24)      ~= 6.25 GB
   columnar compression on those 2 columns   ~= 5x -> ~1.25 GB
   partition/zone-map pruning (if filtered)   -> less still
   vectorised aggregate (SIMD, batches)
   => ~1.5 s

-- The identical SQL on both systems
SELECT category, SUM(revenue) / 100.0 AS revenue_usd
FROM   sales_wide
GROUP  BY category;

Step-by-step trace.

Factor	Row store	Column store	Contribution to speedup
Columns read	24 of 24	2 of 24	~12×
Compression	~1.2×	~5×	~4×
Execution	row-at-a-time	vectorised/SIMD	~2-3×
Cache effect	evicts OLTP set	isolated	(removes incident)
Net	~90 s	~1.5 s	~60×

The 60× is not one trick but a product of independent factors: reading 2 columns instead of 24 (~12×), compressing those columns ~5× versus ~1.2× (~4× more), and vectorised execution over batches (~2-3×). Multiplied, they land near 60×. The column store is slower, however, for a point lookup — SELECT * FROM sales_wide WHERE id = 42 reconstructs one row from 24 scattered column blocks, which the row store answers in a single index seek + block read.

Output:

Query type	Row store	Column store	Winner
SUM(revenue) GROUP BY category	~90 s	~1.5 s	column (~60×)
SELECT * WHERE id = 42	~0.3 ms	~2-5 ms (reassemble)	row
UPDATE ... WHERE id = 42	fast in place	expensive (rewrite segments)	row
Bulk append of a batch	fine	fine/optimal	tie

Why this works — concept by concept:

• Column projection — the report uses 2 of 24 columns; the column store reads 2 columns' bytes, the row store reads all 24. That ratio alone is ~12× and it is pure layout.
• Columnar compression — homogeneous columns encode ~5× versus ~1.2× for mixed-type row pages, cutting the already-smaller byte count further and multiplying into the speedup.
• Vectorised execution — computing over batches of column values with SIMD beats a row-at-a-time engine by another few ×, and it is only possible because values of one column are contiguous.
• Where column loses — a point lookup or in-place update must gather or rewrite N scattered column segments, which the row store does in one block. Layout that wins scans loses point access; that is the whole reason both copies exist.
• Cost — the aggregate goes from O(all columns × rows) uncompressed, row-at-a-time to O(2 columns × rows) compressed, vectorised — the factors multiply to ~60×. The trade is O(1) point access degrading to O(columns) reconstruction on the column side.

Optimization
Topic — optimization
Query and storage optimization problems

Practice →

Data processing Topic — data-processing Data-processing and columnar-format problems

Practice →

---

5. HTAP and moving data OLTP → OLAP

Because no single layout wins both workloads, you copy OLTP into OLAP — or run HTAP to keep both in one engine

The mental model in one line: because a row store and a column store are physically opposite and a table can only be laid out one way at a time, real systems keep two copies — a row-store transactional database as the source of truth and a column-store data warehouse as the analytical copy — and move data between them by batch ETL or incremental CDC; HTAP is the alternative that fuses both layouts inside one engine (a row store for writes, an in-memory column store for scans, kept in sync) to remove the copy and the freshness lag, at the cost of a more complex and expensive system. The copy is the default; HTAP is the specialised escape hatch when you need analytics on truly live operational data.

The copy path — how data gets from OLTP to OLAP.

• Batch ETL. On a schedule (nightly, hourly), a job bulk-extracts new/changed rows from the source, transforms them into the star schema, and loads them into the warehouse. Simple, robust, high-latency (hours). The workhorse of legacy analytics.
• Incremental CDC. A change-data-capture reader streams every insert/update/delete from the source's write-ahead log into the warehouse continuously, keeping it minutes (or seconds) behind. Lower latency, captures deletes, more moving parts.
• The landing → staging → marts flow. Data lands raw, is cleaned/conformed in staging, and is modelled into dimensional marts (the star schemas analysts query). This medallion-style layering keeps the transform auditable and re-runnable.
• ELT vs ETL. Modern warehouses are powerful enough to transform after loading (ELT): dump raw data in, then use warehouse SQL (dbt-style) to build the marts. The T moves from a separate engine into the warehouse.

HTAP — fusing the two layouts in one engine.

• The idea. One system accepts OLTP writes into a row store and serves OLAP scans from a column store, keeping the column store in sync with the row store automatically (often via an in-memory delta store that periodically flushes to columnar).
• How engines do it. Dual-format storage (a row store + a column store index over the same table), in-memory column stores layered on a row-store primary, or distributed engines with a transactional row layer and an analytical column layer.
• When it wins. Operational analytics that must see live data — fraud scoring on the latest transaction, real-time inventory dashboards, personalisation on current session state — where even minutes of CDC lag is too much.
• When it loses. Classic warehousing (large historical analytics, many sources, heavy transforms) where the copy is cheap, staleness is fine, and a dedicated column store is cheaper and simpler than an HTAP engine sized for both.

The freshness spectrum — pick the least you can tolerate.

• Nightly batch. Hours stale. Fine for finance close, historical reporting. Cheapest.
• Micro-batch / hourly. Tens of minutes stale. Fine for most dashboards.
• Streaming CDC. Seconds-to-minutes stale. For near-real-time dashboards and reverse-ETL.
• HTAP. Effectively zero lag (query the live system). For operational analytics that cannot tolerate a copy delay.
• The rule. Freshness costs money and complexity; choose the loosest freshness the business actually needs, not the tightest you can imagine.

Common interview probes on OLTP → OLAP movement.

• "How does data get from the transactional DB to the warehouse?" — batch ETL or streaming CDC; landing → staging → marts.
• "ETL vs ELT?" — transform before load (external engine) vs after load (warehouse SQL); ELT is the modern default.
• "What is HTAP and when do you use it?" — one engine, both layouts; use it for operational analytics on live data, not classic warehousing.
• "How fresh should the warehouse be?" — the loosest the business tolerates; freshness is a cost, not a virtue.

Worked example — a CDC micro-batch merge into the warehouse

Detailed explanation. The modern incremental copy streams source changes and merges them into the warehouse fact/dimension tables. Walk through a CDC micro-batch that applies inserts, updates, and deletes to a warehouse table idempotently by key and change-sequence.

• The stream. Debezium change events (op = c/u/d) landing in a staging table every minute.
• The merge. A MERGE upserts by primary key, taking the latest change per key, applying deletes as tombstones.
• Idempotency. Ordered by the source LSN so replays are safe.

Question. Write the micro-batch MERGE that applies a batch of CDC changes to the warehouse dim_customer table correctly under out-of-order and duplicate delivery.

Input.

CDC field	Meaning
op	c (insert), u (update), d (delete)
lsn	source log position; orders changes per key
after	new row image (c/u)
before	old row image (d)

Code.

-- Staging table receives the raw CDC batch (last minute of changes)
-- stg_customer_cdc(customer_id, name, email, region_id, op, lsn)

-- Keep only the LATEST change per customer in this batch (highest lsn)
WITH latest AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY lsn DESC) AS rn
    FROM   stg_customer_cdc
)
MERGE INTO dim_customer AS tgt
USING (SELECT * FROM latest WHERE rn = 1) AS src
   ON tgt.customer_id = src.customer_id
-- delete arrives -> remove (or soft-delete) the dimension row
WHEN MATCHED AND src.op = 'd' THEN DELETE
-- update arrives and is newer than what we have -> apply it
WHEN MATCHED AND src.op IN ('c','u') AND src.lsn > tgt.src_lsn THEN UPDATE SET
        name      = src.name,
        email     = src.email,
        region_id = src.region_id,
        src_lsn   = src.lsn
-- brand-new customer -> insert
WHEN NOT MATCHED AND src.op IN ('c','u') THEN INSERT
        (customer_id, name, email, region_id, src_lsn)
        VALUES (src.customer_id, src.name, src.email, src.region_id, src.lsn);

Step-by-step explanation.

1. The CDC reader streams every change from the source's WAL into a staging table each minute. A single customer may appear multiple times in one batch (created then updated), and batches can be replayed, so the merge must be idempotent and order-aware.
2. The latest CTE uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY lsn DESC) to keep only the highest-LSN change per customer in this batch. Applying only the latest state per key avoids re-applying superseded intermediate versions.
3. WHEN MATCHED AND op='d' THEN DELETE propagates deletes — a customer removed in the source is removed from the dimension. This is why CDC beats naive timestamp polling, which is blind to physical deletes.
4. WHEN MATCHED AND lsn > tgt.src_lsn THEN UPDATE is the idempotency guard: it applies a change only if it is newer than what the warehouse already has. A replayed or out-of-order older change is ignored, so re-running the batch is safe.
5. WHEN NOT MATCHED THEN INSERT handles new keys. The stored src_lsn on the target row is the high-water mark that makes step 4's comparison work. Net: the dimension converges to the source's latest state regardless of duplicates or reordering — the correctness contract every CDC merge needs.

Output.

customer_id	op in batch	Action on dim_customer
7	u (lsn 105, > stored 100)	UPDATE to new values
7	u (lsn 98, replay)	ignored (98 < 105)
12	c (new key)	INSERT
19	d	DELETE

Rule of thumb. A CDC merge into the warehouse must dedupe to the latest change per key by log position, apply deletes explicitly, and guard updates with lsn > stored_lsn for idempotency. That makes replays and out-of-order delivery harmless — the warehouse converges to the source's current state.

Worked example — batch ELT versus streaming CDC trade-off

Detailed explanation. Choosing between nightly batch and streaming CDC is a freshness-versus-cost decision. Walk through the same orders → warehouse feed built both ways and compare on latency, cost, delete handling, and complexity.

• Batch ELT. One nightly job: bulk-copy yesterday's orders, transform in warehouse SQL, swap the mart.
• Streaming CDC. Continuous Debezium stream + per-minute merge (previous example).

Question. Compare batch ELT and streaming CDC for the orders feed and state when each is the right call.

Input.

Dimension	Batch ELT (nightly)	Streaming CDC
Freshness	~24 h	seconds–minutes
Deletes	needs full-reload or reconcile	native
Cost	one big job/day	always-on connector + compute
Complexity	low	higher (slots, offsets, merges)
Best for	finance close, historical	near-real-time dashboards

Code.

-- Batch ELT: nightly full-refresh of a daily-grain mart (simple, robust)
CREATE OR REPLACE TABLE mart_daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       region_id,
       SUM(total_cents) / 100.0      AS revenue_usd,
       COUNT(*)                      AS orders
FROM   raw.orders                    -- bulk-loaded snapshot of the source
GROUP  BY 1, 2;
-- Runs at 02:00; dashboards read a table that is up to ~24 h stale.

# Streaming CDC: continuous connector feeding the per-minute MERGE
name: orders-cdc
config:
  connector.class: io.debezium.connector.postgresql.PostgresConnector
  plugin.name: pgoutput
  table.include.list: public.orders
  snapshot.mode: initial      # one-time backfill, then stream
  tombstones.on.delete: true  # deletes reach the warehouse
  topic.prefix: warehouse
# Downstream: a job consumes the topic and runs the MERGE every minute.

Step-by-step explanation.

1. Batch ELT is one scheduled job. It bulk-loads a snapshot of the source, then builds marts with warehouse SQL and swaps them atomically. There are no slots, offsets, or per-row merges — the operational surface is tiny, which is why it still runs most finance and historical reporting.
2. Its cost is one large compute burst per day and its freshness is bounded by the schedule — up to ~24 h stale. Deletes are handled implicitly by full-reload (the snapshot simply lacks deleted rows) or by a reconcile step for incremental variants.
3. Streaming CDC is always-on: a connector tails the WAL and a job merges changes every minute (previous example). Freshness drops to seconds-to-minutes and deletes are captured natively as tombstones — the CDC advantages.
4. The price is complexity and continuous cost: replication slots to monitor, consumer offsets, idempotent merges, and compute running around the clock instead of once a night. More moving parts means more failure modes to operate.
5. The decision is freshness-driven: if the business tolerates day-old analytics (finance close, trend reports), batch ELT is cheaper and simpler and you should stop there. If a product feature or ops dashboard needs minutes-fresh data, pay for CDC. Many mature stacks run both — batch for the bulk historical marts, CDC for the few tables that need to be live.

Output.

Requirement	Right choice
Finance close, historical reports	batch ELT
Executive dashboard refreshed hourly	batch/micro-batch
Near-real-time ops dashboard	streaming CDC
Deletes must propagate exactly	streaming CDC (or reconcile)
Lowest cost / complexity	batch ELT

Rule of thumb. Default to batch ELT and only reach for streaming CDC on the specific tables whose freshness the business actually pays for. Freshness is a cost curve, not a goal; the loosest acceptable latency is the cheapest correct answer.

Worked example — an HTAP engine routing queries by workload

Detailed explanation. An HTAP engine keeps both layouts internally and routes each query to the right one, so writes hit the row store and scans hit the column store, with the column store kept in sync via an in-memory delta. Walk through the model and when it beats the copy architecture.

• Write path. Transactions land in the row store (full ACID) and into an in-memory delta.
• Read path. Point reads use the row store; analytical scans use the column store plus the delta for recent changes.
• Sync. The delta periodically flushes into the compressed columnar segments.

Question. Describe how an HTAP engine serves both a point write and a live aggregate over the just-written data, and when this beats OLTP+CDC+warehouse.

Input.

Path	Storage used	Latency
Point write / read	row store	~1 ms
Analytical scan	column store + in-memory delta	~sub-second on live data
Freshness of analytics	zero lag (same engine)	—
Cost	single larger engine	higher per-node

Code.

-- HTAP: one table, two internal representations, automatic routing.
-- OLTP write -> row store (+ in-memory delta)
BEGIN;
INSERT INTO orders (id, region_id, total_cents, status, created_at)
VALUES (98765, 3, 4200, 'paid', now());
COMMIT;                       -- row store durable; delta updated instantly

-- OLAP scan moments later -> column store + delta, sees the new row with ZERO lag
SELECT region_id, SUM(total_cents) / 100.0 AS revenue_usd
FROM   orders                 -- engine routes this scan to the columnar side
WHERE  created_at >= current_date
GROUP  BY region_id;
-- The order just written is already included: no CDC hop, no warehouse copy.

HTAP vs copy architecture
=========================
OLTP + CDC + warehouse:
  write -> Postgres -> WAL -> Debezium -> Kafka -> merge -> Snowflake -> query
  freshness: seconds-to-minutes ; systems: 4+ ; cost: modular, cheap per part

HTAP engine:
  write -> row store + delta ;  scan -> column store + delta  (same engine)
  freshness: zero lag ; systems: 1 ; cost: one big engine sized for both

Step-by-step explanation.

1. The transaction writes to the row store with full ACID (durable, isolated) and simultaneously updates an in-memory delta structure. The write is a normal OLTP write — ~1 ms, no analytical penalty.
2. Moments later, an analytical scan over the same table is routed by the engine to the columnar representation, which it combines with the in-memory delta to include changes not yet flushed to compressed segments. The just-inserted order is visible with zero lag.
3. Periodically the delta flushes into the compressed columnar segments in the background, so the column store stays efficient for large scans without blocking writes. This background merge is the engine's core complexity.
4. Compared to OLTP+CDC+warehouse, HTAP removes the entire copy pipeline (WAL reader, Kafka, merge job, separate warehouse) and its freshness lag — the analytics see live data because they run on the same engine.
5. The trade is cost and specialisation: an HTAP engine must be sized and licensed for both workloads at once and is generally more expensive and less flexible than a modular row-store + column-store-warehouse split. It wins when zero-lag operational analytics is a hard product requirement; the copy architecture wins for classic, staleness-tolerant warehousing at lower cost.

Output.

Scenario	OLTP + CDC + warehouse	HTAP engine
Analytics freshness	seconds–minutes	zero lag
Number of systems	4+	1
Point-write latency	~1 ms	~1 ms
Cost model	modular, cheap per part	one large engine
Best fit	classic warehousing	operational analytics on live data

Rule of thumb. Reach for HTAP only when analytics must see live operational data with zero copy lag; otherwise the OLTP + CDC + column-store-warehouse split is cheaper, simpler, and scales each side independently. The copy is the default; HTAP is the exception you justify.

Data-engineering interview question on OLTP → OLAP freshness

A senior interviewer might ask: "Product wants an 'operational analytics' feature: a live dashboard inside the app showing each merchant their sales in the last hour, updating within a few seconds, over data that is still being written to the transactional database. Walk me through the options — replica, CDC-to-warehouse, HTAP — and justify the one you'd ship and the freshness/cost trade-off."

Solution Using CDC into a fast column store for near-real-time operational analytics

Options considered
==================
1. Query the OLTP replica directly (row store)
   - live data, BUT scans evict the OLTP cache and don't scale per-merchant
   - rejected: production risk + slow scans

2. Nightly/ hourly batch ELT to the warehouse
   - cheap, simple, BUT hours stale
   - rejected: "last hour, within seconds" needs freshness batch can't give

3. HTAP engine
   - zero lag, one system, BUT re-platforming the source of truth is huge
   - deferred: too big a change for one feature

4. Streaming CDC -> fast column store (ClickHouse/warehouse streaming) [CHOSEN]
   - seconds-fresh, isolated from OLTP, columnar scans per merchant

# CDC connector: stream orders to the analytics store continuously
name: orders-operational-analytics
config:
  connector.class: io.debezium.connector.postgresql.PostgresConnector
  plugin.name: pgoutput
  table.include.list: public.orders
  snapshot.mode: initial
  tombstones.on.delete: true
  topic.prefix: ops
  heartbeat.interval.ms: 10000    # keep the slot advancing during quiet spells

-- Fast columnar analytics store, partitioned by day, ordered for merchant scans
CREATE TABLE ops_orders (
    order_id     BIGINT,
    merchant_id  BIGINT,
    total_cents  BIGINT,
    created_at   TIMESTAMP
)
PARTITION BY toYYYYMMDD(created_at)
ORDER BY (merchant_id, created_at);   -- co-locate a merchant's recent rows

-- The per-merchant "last hour" dashboard query (sub-second, columnar)
SELECT toStartOfMinute(created_at) AS minute,
       SUM(total_cents) / 100.0    AS revenue_usd,
       COUNT(*)                    AS orders
FROM   ops_orders
WHERE  merchant_id = 42
  AND  created_at >= now() - INTERVAL 1 HOUR
GROUP  BY minute
ORDER  BY minute;

Step-by-step trace.

Layer	Choice	Why
Source	untouched OLTP Postgres	no production risk
Transport	Debezium CDC (WAL)	seconds-fresh, captures deletes
Store	columnar, ORDER BY (merchant_id, created_at)	fast per-merchant scans
Freshness	~2-5 s end to end	meets "within seconds"
Isolation	separate store	scans never hit OLTP cache
Cost	one connector + small columnar cluster	far cheaper than HTAP re-platform

After deployment, each order committed to PostgreSQL flows through the WAL, Debezium, and a streaming merge into ops_orders within a few seconds. The dashboard's per-merchant "last hour" query is a columnar scan of one day-partition, clustered by merchant_id, so it reads a tiny slice and returns sub-second. The transactional database only ever sees the CDC reader (near-zero load) and never a dashboard scan, so checkout latency is untouched, and the business gets seconds-fresh operational analytics without re-platforming the source of truth onto HTAP.

Output:

Metric	Value
End-to-end freshness	~2-5 s
Dashboard query latency	sub-second
OLTP impact	~0 (CDC reader only)
Delete propagation	native (tombstones)
Cost vs HTAP re-platform	far lower

Why this works — concept by concept:

• CDC over the WAL — streaming changes off the write-ahead log gives seconds-fresh data and captures deletes, without the OLTP database ever serving a scan. The source of truth is untouched.
• Columnar analytics store — per-merchant "last hour" is a scan-and-aggregate, so it belongs on a column store where reading a few columns of a day-partition is cheap and isolated from production.
• ORDER BY (merchant_id, created_at) — physically co-locating each merchant's recent rows means the dashboard scans a small contiguous slice, and zone-map pruning skips the rest — sub-second even under many merchants.
• Freshness matched to need — the requirement is "within seconds," which CDC meets; HTAP's zero-lag was unnecessary and its re-platform cost was unjustified for one feature. Batch's hours were too stale. CDC is the least you can tolerate, so the cheapest correct choice.
• Cost — one connector plus a small columnar cluster, O(few columns × one partition) per dashboard query, versus re-platforming the system of record onto an HTAP engine. The copy stays cheap and isolated while delivering the freshness the product actually needs.

ETL
Topic — etl
ETL and CDC pipeline problems

Practice →

Data processing
Topic — data-processing
Data-processing and warehouse-loading problems

Practice →

---

Cheat sheet — OLTP vs OLAP recipes

• Classify by workload, not product. Point access on few rows, few writes, latency-critical, must-be-current → OLTP (row store). Scan of few columns over many rows, aggregate, staleness-tolerant → OLAP (column store). Count the rows and columns a query touches before you name a system.
• Row store vs column store in one line. Row-major stores a record's columns together → one point read is one block (OLTP wins). Column-major stores a column's values together → one scan reads one column's bytes, compressed (OLAP wins). Same table, opposite layouts, no free lunch.
• OLTP physical checklist. Row-major storage, B-tree index on every hot predicate, MVCC/locking for isolation, FOR UPDATE on rows you compute against, WAL for durability, CHECK/UNIQUE constraints for consistency, connection pool sized from CPU cores (a few per core), and an idempotency key on any retryable write.
• OLAP physical checklist. Columnar storage, partition by date, cluster/sort by common filter columns, low-cardinality dimension keys for RLE/dictionary encoding, vectorised engine, SELECT only the columns you need (never SELECT * on a fact), and a star schema (fact + small dimensions) so joins stay one hop deep.
• Star schema template. Fix the grain first (one fact row = one order item / one event). Fact table holds dimension foreign keys + numeric measures (pre-compute derived measures at load). Dimensions are small descriptive tables (date, product, customer, region). Denormalise foreign keys into the fact so analyst joins are one hop; keep dimensions broadcast-joinable.
• Never run analytics on the OLTP box. A large aggregation on a row store reads the whole heap to use a few columns, evicts the buffer pool, and spikes OLTP p99. Move scans to a column store; a "free" query on the production replica is a latency incident waiting to happen.
• OLTP → OLAP copy recipe. Landing (raw) → staging (clean/conform) → marts (star schemas). Prefer ELT (transform in warehouse SQL) over external ETL. Batch for staleness-tolerant bulk; streaming CDC only for the tables whose freshness the business pays for.
• CDC merge idempotency. Dedupe the batch to the latest change per key by log position (ROW_NUMBER() OVER (PARTITION BY pk ORDER BY lsn DESC)), apply deletes explicitly (tombstones), and guard updates with lsn > stored_lsn. This makes replays and out-of-order delivery harmless.
• Freshness spectrum. Nightly batch (hours) → micro-batch/hourly (tens of minutes) → streaming CDC (seconds–minutes) → HTAP (zero lag). Pick the loosest latency the business actually needs; freshness is a cost curve, not a goal.
• HTAP when-to-use. Use HTAP only for operational analytics on live data where copy lag is unacceptable (fraud, live inventory, in-app dashboards). For classic historical warehousing across many sources, the OLTP + CDC + column-store-warehouse split is cheaper, simpler, and independently scalable.
• Bytes-read is the master metric. Every OLTP/OLAP question reduces to which bytes a query reads under a layout. Point query wants the whole row in one place; scan wants one column in one place. Optimise by minimising bytes read: indexes for OLTP, projection + pruning + compression for OLAP.
• Index vs sort-key mapping. OLTP: B-tree indexes turn point predicates into O(log n) seeks. OLAP: there are usually no per-row indexes — partitioning + clustering/sort keys + zone maps + compression do the pruning. Design OLTP for selective seeks; design OLAP for cheap wide scans.

Frequently asked questions

What is OLTP vs OLAP in one sentence?

OLTP (online transaction processing) is the workload and row-oriented database that serve many small, indexed, short-lived transactions — point reads and writes with ACID guarantees, at high concurrency and millisecond latency — while OLAP (online analytical processing) is the workload and column-oriented system that serve a few large queries that scan many rows but few columns and reduce them with aggregation. The two are physically different because the storage layout that makes one fast (rows together for point access) makes the other slow (rows together forces scans to read every column). Almost every real data platform runs both: a transactional database as the source of truth and a data warehouse as the analytical copy, with a pipeline moving data between them.

Why can't one database do both OLTP and OLAP well?

Because the optimal physical storage layout is opposite for the two workloads and a table can only be laid out one way at a time. OLTP wants row-major storage so a point read fetches one record in one block; OLAP wants column-major storage so a scan reads only the columns it needs, compressed. Run a big aggregation on a row-store OLTP database and it must read the entire heap (all columns) to use a few, which evicts the buffer pool and spikes transactional latency. Run point lookups on a column store and each record must be reassembled from many separate column blocks. This is why mature systems keep two copies and copy data from OLTP to OLAP — and why HTAP engines, which try to serve both, keep two internal layouts rather than one clever compromise.

Row store vs column store — which is faster?

Neither in the abstract; each wins the workload it was built for. A row store is faster for point access — fetching or updating one whole record — because all of a row's columns sit together, so it is one index seek and one block read. A column store is faster for scan-and-aggregate — summing one column across millions of rows — because each column is stored contiguously and compressed, so the scan reads only that column's bytes (often 10-60× fewer bytes than the equivalent row-store scan) and executes vectorised over batches. The right question is never "which is faster" but "what is this query's workload": point-few-columns-write wants row-major; scan-few-columns-aggregate wants column-major.

What is a data warehouse and how does it differ from a transactional database?

A data warehouse is a column-store OLAP system that holds a denormalised, historical, analysis-optimised copy of data drawn from one or more transactional sources, modelled as star schemas (fact tables of events plus small dimension tables) and queried by analysts, dashboards, and BI tools. A transactional database is a row-store OLTP system that is the live source of truth for an application, normalised for write integrity, indexed for point access, and governed by strict ACID guarantees. The warehouse can be minutes or hours stale because it is derived, not authoritative; the transactional database must be consistent to the microsecond because it holds the real state (money, inventory, bookings). Data flows one way — from the transactional database into the warehouse — via batch ETL/ELT or streaming CDC.

What is HTAP and when should I use it?

HTAP (Hybrid Transactional/Analytical Processing) is an architecture where a single engine serves both OLTP writes and OLAP scans by keeping both a row store and a column store internally and routing each query to the appropriate one, kept in sync (often through an in-memory delta store that flushes into compressed columnar segments). Its advantage is zero copy lag — analytical queries see live transactional data without a CDC hop or a separate warehouse — and one system to operate. Its cost is a more complex, more expensive engine that must be sized for both workloads at once. Use HTAP for operational analytics that must act on live data (fraud scoring, real-time inventory, in-app merchant dashboards); for classic historical warehousing across many sources where staleness is acceptable, the OLTP + CDC + column-store-warehouse split is cheaper, simpler, and scales each side independently.

How does data get from OLTP to OLAP?

Two mechanisms, chosen by how fresh the analytics must be. Batch ETL/ELT runs on a schedule (nightly or hourly): it bulk-extracts new or changed rows from the transactional source, transforms them into the star schema (increasingly after loading, as ELT with warehouse SQL), and loads them into the warehouse — simple and cheap but hours stale. Streaming CDC (change data capture, e.g. Debezium reading the write-ahead log) continuously streams every insert, update, and delete into the warehouse and applies them with an idempotent, order-aware MERGE, keeping it seconds-to-minutes behind and capturing deletes natively. The standard flow layers the data as landing (raw) → staging (cleaned/conformed) → marts (dimensional models analysts query). Default to batch for staleness-tolerant workloads and reserve streaming CDC for the specific tables whose freshness the business actually pays for.

Practice on PipeCode

• Drill the SQL practice library → for the point-query, transaction, join, and aggregation problems that separate OLTP and OLAP thinking.
• Rehearse on the database practice library → for transactional design, indexing, ACID, and concurrency problems on the row-store side.
• Sharpen the analytical axis with the aggregation practice library → and the dimensional-modeling practice library → for star-schema, GROUP BY, and warehouse-query scenarios.
• Work the pipeline with the ETL practice library → and the data-processing practice library → for the CDC merges and batch loads that move data OLTP → OLAP.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the OLTP-vs-OLAP decision framework against real graded inputs.

Lock in OLTP vs OLAP muscle memory

Docs explain the definitions. PipeCode drills explain the decision — when a query is point-access OLTP versus scan-aggregate OLAP, why a row store evicts the cache on a big scan, why a column store gets 60x faster by reading a few columns, when a star schema beats the normalised model, and when HTAP earns its cost over a CDC copy. Pipecode.ai is Leetcode for Data Engineering — workload-first practice tuned for the production trade-offs data engineers actually face.

Practice database problems →
Practice aggregation problems →