How OLTP and analytical systems differ in purpose, data shape, queries and users.
From Ultra Transcenders DP-900 by Tony Rough (publishing soon)
Most exam-level distinctions between the two workloads come down to a handful of traits. This table brings them together.
| Trait | Transactional (OLTP) | Analytical (OLAP) |
|---|---|---|
| Purpose | Run the business: record events as they happen | Understand the business: analyse history and trends |
| Typical operations | Large numbers of small CRUD operations | Large read queries and aggregations |
| Read/write balance | Optimised for both reads and writes | Read-only or read-mostly |
| Data | Current operational records | Historical data, snapshots, business metrics |
| Schema design | Highly normalised | Denormalised; star or snowflake schemas; preaggregated models |
| Transactions | Yes, with ACID guarantees | Not used; models are typically refreshed or recomputed |
| Freshness | Immediate | Refreshed at intervals |
| Typical users | Line of business applications and their users | Data analysts, data scientists, business users through reports |
Learn’s architecture guidance also notes that most real workloads are not purely OLTP: many need reporting against the operational system too, a mix known as hybrid transactional and analytical processing (HTAP).
This note is one section of Ultra Transcenders DP-900: Microsoft Azure Data Fundamentals, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.
Publishing soon on Amazon in Kindle and paperback editions.
About the book · DP-900 terms in the glossary · All DP-900 study notes
What each ACID property guarantees in a transactional (OLTP) database, with the classic funds-transfer example.
How extract-transform-load and extract-load-transform differ, and why ELT is common in modern lakehouses.
How normalisation splits data into one table per entity, linked by keys, so each fact is stored once.
The three Azure SQL options side by side: IaaS or PaaS, compatibility, management and availability.
Which Azure SQL option or open-source database service fits a requirement, and why.
Storage and access costs, minimum retention periods, Archive rehydration and lifecycle management policies.
The key characteristics of Azure Cosmos DB: schema-agnostic items, automatic indexing, global distribution and low latency.