Analytical vs. Transactional Workloads

The difference you met on the last page has a formal name. The "needle" work applications do is called OLTP — Online Transaction Processing. The "net" work analysts do is OLAP — Online Analytical Processing. You do not need to memorize the acronyms, but understanding what each workload demands explains why analytical databases like DuckDB are built so differently from the database behind a typical app.

A transaction vs. an analysis

A transaction is a small, complete unit of business activity: place an order, transfer money, update an address. It touches a few rows, must be fast, and must be safe even if thousands happen at once.

An analysis is the opposite in almost every dimension: it reads a huge slice of history and condenses it into understanding.

A simple way to remember it: OLTP records what happened; OLAP explains what it means. Every order an OLTP system saves becomes a row that, much later, an OLAP query sweeps up to find a trend.

Why the physical shape differs: rows vs. columns

Here is the idea that ties everything together — and the reason DuckDB feels fast for analysis. Databases can store a table in two ways.

• Row storage keeps all of a row's values together. Perfect for OLTP: "give me everything about order #4821" reads one contiguous chunk.
• Column storage keeps all values of one column together. Perfect for OLAP: "average amount over 10 million orders" reads just the amount column and skips everything else.

When an analytical query only needs amount, a column store reads only the amounts — not the ids, dates, or regions sitting beside them. On a wide table that can mean reading 5% of the data instead of 100%. That single design choice is why purpose-built analytical engines crush row-oriented databases on summary queries. DuckDB is column-oriented, which is a big part of why DuckDB exists (more on that soon).

You write SQL the same way either way

Reassuringly, you do not change your SQL to get these benefits. You write the same SELECT ... GROUP BY you already know; the engine's storage layout does the rest. The lesson is not "write different SQL for OLAP" — it is "the questions you ask, and the engine you ask them on, are tuned for analysis."

The query below is a perfectly ordinary GROUP BY. What makes it "analytical" is the intent — summarizing a column across many rows — not any special syntax.

Five thousand rows in, four rows out. The query never needed id or order_date, so a column store would never read them.

A side-by-side summary

This course lives entirely on the OLAP side of that table.

Check your understanding

QuestionSelect one

What does OLAP stand for, and what is it for?

Online Transaction Processing — recording day-to-day business events.

Online Analytical Processing — summarizing large amounts of data to understand it.

Offline Logical Archive Processing — backing up old data.

Open Ledger Accounting Protocol — a finance standard.

QuestionSelect one

Why does a column-oriented store make analytical queries faster?

It compresses rows so transactions commit faster.

A query that needs only a few columns can read just those columns and skip the rest of each row.

It guarantees every query touches every column.

It removes the need to write GROUP BY.

QuestionSelect one

A query that scans five million orders to report monthly revenue is best described as:

An OLTP transaction, because it touches the orders table.

An OLAP analytical workload, because it reads many rows to produce a small summary.

Invalid, because analytical queries cannot read order data.

A backup operation.

Next: how analysts actually reason about a dataset they have never seen before — the mental moves behind exploratory analysis.