Topic 4 of 5
Transactional and analytical workloads
The same data serves two very different purposes: recording the business day to day and analyzing what happened. Each use needs systems designed differently. The exam asks about the ACID properties, the pieces of an analytics architecture, and the difference between a data lake, a data warehouse, and a lakehouse.
Contents
Transactional processing (OLTP)
A transactional system records transactions: specific events the organization wants to track. It could be financial (moving money between accounts) or retail (charging a customer for a product). Think of a transaction as a small, discrete unit of work.
- They're often high-volume: sometimes millions of transactions a day, with data that must be accessible quickly.
- This work is called OLTP (Online Transactional Processing).
- They rely on databases optimized for both reads and writes, with create, retrieve, update, and delete operations (CRUD).
- They support live business applications, called LOB (line of business) applications.
ACID properties
OLTP systems enforce transactions with ACID semantics to ensure integrity. Example: transferring $40 from account A (balance $100) to account B (balance $50).
Atomicity
The transaction is a single unit that succeeds completely or fails completely. If B can't be credited, A isn't debited either.
Consistency
The database only moves from one valid state to another. When finished, the money has moved from one account to the other.
Isolation
Concurrent transactions don't interfere. A balance query during the transfer doesn't see one account before and the other after.
Durability
Once committed, the transaction remains. Even if the system is switched off, the new balances are there when it comes back.
Analytical processing
Analytical processing typically uses read-only (or read-mostly) systems that store vast volumes of historical data or business metrics. Analytics can be based on a snapshot of the data at a point in time or on a series of snapshots.
Typical analytics architecture
Extract and load: Operational data is extracted, transformed, and loaded (ETL) into a data lake; or extracted and loaded first and transformed afterward (ELT), a common pattern in modern lakehouses.
Schema of tables: Data is loaded into tables: in a lakehouse, with tabular abstractions over data lake files, or in a data warehouse with a fully relational SQL engine.
Analytical model: Data can be aggregated into an OLAP model, today usually called a semantic model (historically, a cube).
Consumption: Data in the lake, warehouse, and model is queried to produce reports, visualizations, and dashboards.
The difference between ETL and ELT is the order: transform before loading or after. Domain D4 covers it in depth.
Data lake, data warehouse, and lakehouse
Data lake
Common in large-scale analytics, where a large volume of file-based data must be collected and analyzed.
Data warehouse
An established way to store data in a relational schema optimized for reads: reporting and visualization queries.
Lakehouse
A more recent innovation that combines the flexible, scalable storage of a data lake with the relational querying semantics of a warehouse.
Denormalize to analyze
The analytical schema often denormalizes some of the data that came normalized from OLTP: it introduces some duplication so queries run faster. That's the opposite of what a transactional system aims for.
OLAP and semantic models
An OLAP model (or semantic model) is aggregated storage optimized for analytics. It calculates aggregated numeric values (measures) from fact tables for intersections of dimensions from dimension tables: for example, revenue by date, customer, and product.
- Aggregations are organized in levels, so you can drill down or up: sales by region, by city, or by address.
- Because the data is preaggregated, queries that return those summaries run quickly.
- Power BI semantic models are a common example.
| User | Where they usually work |
|---|---|
| Data scientists | Directly with data lake files, to explore and model |
| Data analysts | Querying data warehouse tables for complex reports |
| Business users | Consuming preaggregated model data in reports and dashboards |
Medallion architecture
A common pattern for organizing lakehouse data in three layers:
Bronze
Raw data ingested as-is from source systems, untransformed, so it can be reprocessed
Silver
Cleansed, conformed data: duplicates removed and data types standardized
Gold
Aggregated, business-ready data modeled for specific reporting and analytics
It creates clear quality boundaries at each layer, and you can always reprocess from Bronze if requirements change. The platforms that implement it, Microsoft Fabric and Azure Databricks, are covered in D4.
OLTP vs. analytics
| Transactional (OLTP) | Analytical | |
|---|---|---|
| Goal | Record business operations | Analyze and report |
| Operations | Reads and writes (CRUD) | Read-only or read-mostly |
| Data | Current, transaction by transaction | Historical, large volumes |
| Schema | Normalized | Often denormalized |
| Users | LOB applications | Analysts, data scientists, business |
Exam scenarios
| Situation | Answer | Why |
|---|---|---|
| Record millions of payments a day and query them instantly | Transactional workload (OLTP) | Fast reads and writes with integrity. |
| A transfer must debit one account and credit another, or neither | Atomicity | All or nothing. |
| During a transfer, another query doesn't see half-updated balances | Isolation | Concurrent transactions don't interfere. |
| After a transaction is committed, it survives a shutdown | Durability | Committed work is persisted. |
| Analyze five years of sales by region and product | Analytical workload | Historical, read-only data. |
| Store raw files from many sources to analyze later | Data lake | Large-scale file store. |
| See total sales by region and drill down to city | OLAP / semantic model | Aggregates across dimension hierarchies. |
| Separate raw, cleansed, and business-ready data | Medallion architecture | Bronze, Silver, and Gold layers. |
Common exam traps
❓ Which ACID property stops a query from seeing one account before and the other after a transfer?
Isolation.
❓ Which property guarantees that a committed transaction survives a shutdown?
Durability.
❓ Are analytical systems optimized for heavy writes?
No. They're read-only or read-mostly; OLTP systems are the ones optimized for reads and writes.
❓ Is a lakehouse just a data warehouse with another name?
No. It combines a data lake's flexible storage with a warehouse's relational querying.
❓ Which medallion layer holds raw data?
Bronze. Silver holds cleansed data and Gold holds business-ready data.
❓ What is an OLAP model usually called today?
A semantic model (historically, a cube).
❓ In ELT, is data transformed before it's loaded?
No. In ELT it's loaded first and transformed afterward; in ETL it's transformed before.
Did you get this topic?
Test what you just learned
During a bank transfer, the credit to the destination account fails. The system also rolls back the debit from the source account. Which ACID property is being applied?
Ready to test "Transactional and analytical workloads"?
Original questions, each with a full explanation.
Start a mock exam