Operational vs Analytical Systems: Transaction Shape, History, Concurrency, Query Complexity, and Data Freshness

Learning outcomes

AtlasMart's checkout service is optimized to find and change a few rows quickly, but finance and operations also need questions such as “paid merchandise value by day and customer segment” across months of history. The same data participates in both workloads, yet the access pattern, concurrency profile, history requirement, and acceptable freshness are different. This lesson makes those differences observable before any warehouse schema is designed.

1. Start with workload shape, not database labels

Online transaction processing (OLTP) describes workloads dominated by many small, bounded operations that maintain operational state: place an order, change a shipping address, reserve inventory, or read one account. Correctness under concurrent writes and predictable response for short transactions usually matter more than scanning years of history.

Online analytical processing (OLAP) describes workloads that read and combine substantial data to answer business questions: revenue trends, cohort behavior, fulfillment latency, inventory exposure, or variance to plan. Queries are commonly read-heavy, aggregate many rows, and may join several business subjects. They often tolerate data that is seconds, minutes, or hours behind the source if that delay is explicitly contracted.

The distinction is therefore a workload model, not a rule that one product can only perform one class of query. Chapter 17 and later courses will examine physical engines; Chapter 01 is concerned with semantics and responsibility boundaries.

2. One operational request touches little data; one analytical question can touch a lot

Consider AtlasMart order O1003. The checkout service can retrieve its current status by primary key. That request has a narrow predicate and returns one row. Finance instead asks for paid merchandise value by day and customer segment. The analytical question joins orders to line items and customers, filters by business status, groups, and sums measurements. Both queries are valid, but their computational footprints and failure consequences differ.

SELECT status, updated_atFROM ordersWHERE order_id = 'O1003';

SELECT  substr(o.order_ts, 1, 10) AS order_date,  c.segment,  SUM(ol.quantity * ol.unit_price) AS paid_gmv,  COUNT(DISTINCT o.order_id) AS paid_ordersFROM orders AS oJOIN order_lines AS ol USING (order_id)JOIN customers AS c USING (customer_id)WHERE o.status = 'paid'GROUP BY 1, 2ORDER BY 1, 2;

The grouped result must reconcile to the source fixture: paid merchandise value is 625 across 4 paid orders. All line-item value, including the pending order, is 1025. The 400 difference is explained by the business filter, not by missing data.

3. History is a contract about what can be reconstructed

An operational customers row can be updated from segment SMB to Enterprise. If the old value is overwritten and no change history exists, a later analyst cannot prove what segment AtlasMart believed the customer belonged to when an earlier order was placed. Warehousing therefore treats historical meaning as a deliberate requirement rather than assuming the source’s current row is enough.

This does not mean every warehouse must preserve every field forever. It means each analytical attribute needs a stated policy: overwrite, preserve versions, take periodic snapshots, derive from immutable events, or deliberately keep only current state. Slowly changing dimensions and effective dating are taught in Chapter 07. Here, the important mechanism is that historical questions require historical evidence.

4. Freshness is a timestamp chain, not a marketing adjective

For a record committed at the source at 10:00 UTC, an extract might observe it at 10:10, a transformation might publish it at 10:14, and a certified dashboard might refresh at 10:20. The consumer-visible freshness lag is then 20 minutes. A pipeline can run every five minutes and still miss a 15-minute freshness objective if source extraction, transformation, queueing, or BI cache time dominates.

5. Controlled failure: run BI directly against the checkout schema

A tempting shortcut is to connect a dashboard to the transactional database and let analysts issue unrestricted joins. With the tiny fixture this appears efficient: there are only five orders. At production scale, however, unbounded scans compete with checkout traffic, schema changes made for application needs can break dashboards, current rows may not preserve historical meaning, and business metrics become duplicated across reports.

The repair is not “copy everything once per night” by reflex. The repair is to separate responsibilities: define analytical questions and freshness, capture source evidence, isolate heavy analytical work from operational write paths, preserve required history, and expose governed metrics. The appropriate batch/CDC and physical architecture are chosen from those requirements later.

6. Hands-on lab — build the source fixture and prove the control totals

Create an empty directory. Save the following as setup_atlasmart.py and run it with a local Python interpreter. The script deletes only atlasmart_oltp.db in the current directory, prints the runtime versions, creates the synthetic source database, and reports row counts.

from pathlib import Pathimport sqlite3, sysDB = Path("atlasmart_oltp.db")if DB.exists():    DB.unlink()con = sqlite3.connect(DB)print("Python:", sys.version.split()[0])print("SQLite:", sqlite3.sqlite_version)con.executescript(r'''PRAGMA foreign_keys = ON;CREATE TABLE customers (  customer_id TEXT PRIMARY KEY,  customer_name TEXT NOT NULL,  segment TEXT NOT NULL,  region TEXT NOT NULL,  updated_at TEXT NOT NULL);CREATE TABLE products (  product_id TEXT PRIMARY KEY,  product_name TEXT NOT NULL,  category TEXT NOT NULL,  list_price NUMERIC NOT NULL,  updated_at TEXT NOT NULL);CREATE TABLE orders (  order_id TEXT PRIMARY KEY,  customer_id TEXT NOT NULL REFERENCES customers(customer_id),  order_ts TEXT NOT NULL,  status TEXT NOT NULL,  channel TEXT NOT NULL,  updated_at TEXT NOT NULL);CREATE TABLE order_lines (  order_id TEXT NOT NULL REFERENCES orders(order_id),  line_no INTEGER NOT NULL,  product_id TEXT NOT NULL REFERENCES products(product_id),  quantity INTEGER NOT NULL CHECK (quantity > 0),  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),  PRIMARY KEY (order_id, line_no));CREATE TABLE inventory (  product_id TEXT PRIMARY KEY REFERENCES products(product_id),  on_hand INTEGER NOT NULL CHECK (on_hand >= 0),  snapshot_ts TEXT NOT NULL);INSERT INTO customers VALUES('C001','Ada Retail','SMB','North','2026-09-20T08:00:00Z'),('C002','Ben Home','Consumer','West','2026-09-20T08:00:00Z'),('C003','Cyra Labs','Enterprise','East','2026-09-20T08:00:00Z'),('C004','Dara Studio','Consumer','South','2026-09-20T08:00:00Z');INSERT INTO products VALUES('P100','Keyboard','Accessories',50,'2026-09-20T08:00:00Z'),('P200','Mouse','Accessories',25,'2026-09-20T08:00:00Z'),('P300','Monitor','Displays',200,'2026-09-20T08:00:00Z'),('P400','Dock','Accessories',100,'2026-09-20T08:00:00Z');INSERT INTO orders VALUES('O1001','C001','2026-09-18T09:15:00Z','paid','web','2026-09-18T09:16:00Z'),('O1002','C002','2026-09-18T11:30:00Z','paid','mobile','2026-09-18T11:31:00Z'),('O1003','C001','2026-09-19T15:00:00Z','paid','web','2026-09-19T15:01:00Z'),('O1004','C003','2026-09-19T16:40:00Z','pending','sales','2026-09-19T16:40:00Z'),('O1005','C004','2026-09-20T07:10:00Z','paid','mobile','2026-09-20T07:11:00Z');INSERT INTO order_lines VALUES('O1001',1,'P100',2,50),('O1001',2,'P200',1,25),('O1002',1,'P300',1,200),('O1003',1,'P400',1,100),('O1003',2,'P200',2,25),('O1004',1,'P300',2,200),('O1005',1,'P100',1,50),('O1005',2,'P400',1,100);INSERT INTO inventory VALUES('P100',40,'2026-09-20T08:00:00Z'),('P200',60,'2026-09-20T08:00:00Z'),('P300',12,'2026-09-20T08:00:00Z'),('P400',25,'2026-09-20T08:00:00Z');''')con.commit()print({name: con.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]       for name in ["customers", "products", "orders", "order_lines", "inventory"]})con.close()

Expected row-count evidence is {'customers': 4, 'products': 4, 'orders': 5, 'order_lines': 8, 'inventory': 4}. Then open the database with a SQLite-capable client or extend the script with the two SQL statements above.

SELECT SUM(ol.quantity * ol.unit_price) AS paid_gmv,       COUNT(DISTINCT o.order_id) AS paid_ordersFROM orders AS oJOIN order_lines AS ol USING (order_id)WHERE o.status = 'paid';-- expected: 625 | 4SELECT SUM(quantity * unit_price) AS all_line_valueFROM order_lines;-- expected: 1025SELECT SUM(on_hand) AS on_hand_unitsFROM inventory;-- expected: 137

Verification checklist: record Python/SQLite versions; confirm all five table counts; confirm paid GMV 625 and four paid orders; confirm all-line value 1025; confirm inventory 137 units; explain the 400 value excluded by the paid-status rule. These are deterministic fixture assertions, not performance measurements.

Cleanup: close database clients and delete only atlasmart_oltp.db and the local script from the lab directory.

7. Production judgment

Use operational systems to protect operational state and analytical systems to answer analytical questions under explicit history, freshness, quality, security, and cost contracts. The boundary can be implemented by separate products, separate clusters, replicas, shared storage, or managed services; Chapter 01 does not prescribe a vendor topology.

The next lesson names the common layers around that boundary—warehouse, mart, ODS, lake, lakehouse, semantic layer, and BI platform—so AtlasMart can stop using them as interchangeable words.

Knowledge check

Summary and next step

Operational and analytical systems can share data without sharing the same workload contract. Keep transaction shape, history, query complexity, concurrency, and freshness explicit; preserve source control totals; and never convert a tiny local query into a performance claim.

Next: Warehouse, Data Mart, ODS, Data Lake, Lakehouse, Semantic Layer, and BI Platform: Responsibilities and Boundaries.

Authoritative references

• Big Data Academy — Data Warehousing and Dimensional Modeling — Authoritative course curriculum and reserved lesson sequence.
• Kimball Group — Grain — Why a fact-row meaning must be declared precisely before later dimensional design.
• Kimball Group — Fact Tables — Measurement-event and grain concepts used later in the course.
• Python documentation — sqlite3 — Official Python interface used by the mandatory local fixture.
• SQLite — Transactions — Authoritative transaction behavior for the lab engine; not a warehouse-performance reference.