Transactional and Analytical Workloads

What it is

A workload is the kind of work a system does with data. There are two main kinds. A transactional workload records business events as they happen. An analytical workload studies large amounts of past data to find patterns.

They need different designs, which is why most organisations run both on separate systems.

How it works

Transactional workloads (OLTP)

Online transaction processing handles many small, quick operations: placing an order, transferring money, booking a seat. Each operation reads or writes a few rows and must be correct at once.

A transaction is a group of operations treated as one unit. Moving 100 dollars between accounts is a debit and a credit, and both must happen or neither. Transactional databases guarantee this with four properties known as ACID.

OLTP data is usually normalised, which means split into related tables to avoid duplication.

Analytical workloads (OLAP)

Online analytical processing reads large volumes of data and summarises it: total sales by region over five years, or the trend in customer sign ups. It rarely changes the data, and a single query may scan millions of rows.

Analytical data usually sits in a data warehouse, a central store of cleaned data collected from many source systems. Warehouse data is often arranged as a star schema.

• A fact table holds numeric measures of events, such as sales amount and quantity.
• Dimension tables hold descriptions used to slice the facts, such as customer, product and date.

Comparing the two

Getting data from one to the other

Analytical systems are fed by a pipeline. The classic order is ETL: extract data from the sources, transform it by cleaning and reshaping, then load it into the warehouse. A variation, ELT, loads the raw data first and transforms it inside the analytical store.

A worked scenario

A retailer's tills write every sale to an order database within milliseconds, and each sale must be exact: that is OLTP. Every night the day's sales are copied into a warehouse, joined with product and store descriptions, and cleaned. Managers then open a dashboard that compares this quarter with the last five: that is OLAP. Running the dashboard on the tills' database would slow down every sale, which is the reason the two are kept apart.

A transfer written as a transaction

The statements below move 100 from one account to another. They are grouped so that both happen or neither does.

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

If the second update fails, the database issues a ROLLBACK, which undoes the first. Atomicity makes the pair indivisible, and durability makes the committed result permanent.

A small star schema

A question such as "revenue by product category per quarter" joins the fact table to two dimensions and adds up the Amount column. Because the dimensions are wide and flat, the query needs few joins and runs quickly.

Why the two workloads are kept apart

Transactional systems value speed for single records and strict accuracy, so they avoid long scans. Analytical queries scan millions of rows and could lock or slow the tables that the business depends on. Copying data into a separate warehouse protects the live system and also lets you combine many sources, such as sales, web visits and support tickets, into one view.