Core data concepts · Lesson 3

Distinguishing transactions from analysis

9 min read

Lesson objectives

  • Separate event recording from aggregation by counting unit and freshness requirement
Reading tools

A transactional workload records small business events accurately: creating an order, changing its status, deducting a balance, or confirming a seat. It cares about each operation's response time and correct concurrent updates. A transaction may need to make two linked changes succeed together or fail together. If two users try to reserve the last seat at the same moment, an old seat count is insufficient; concurrency control must prevent two sales of the same seat. Success is the correct state after each event rather than a polished dashboard. OLTP commonly names this operational processing pattern. A transactional database can still produce reports; its central design concern is many short operations against identified records. State the integrity requirement before choosing a particular Azure service.

An analytical workload reads broader sets to answer a trend or comparison: sales by month, late deliveries by city, or demand after a campaign. It may aggregate millions of rows without writing into each live order. OLAP refers to a pattern of analytical queries and aggregation, not a product name. For a report called late-order rate, define numerator, denominator, and time window: orders delivered late divided by orders whose delivery deadline fell in the period, for example. Putting orders whose deadline has not arrived into the denominator falsely lowers the rate; counting update events rather than unique orders can inflate it. Analytical design therefore starts with the grain or unit of each row to be aggregated, rather than merely selecting a faster engine.

One system may need both workloads. A typical path records the order in an operational store, copies or exports events on a schedule or stream, cleans timestamps and identifiers, stores an analytical representation, and builds a report. Every boundary poses a question. What delay can the decision tolerate? Do later cancellations revise the report? Is there one current record per order or many events per order? If a manager needs a dashboard each morning, a nightly batch may suffice. If operators need a warning within seconds of a machine failure, next-day processing misses the requirement. Choose batch or stream from decision freshness, not only from data size. Never describe a dashboard as real-time when its latest data load was yesterday. Record a refresh timestamp alongside the measure.

To test freshness, imagine a gate worker sees ticket state at 09:00 while a report refreshes at 06:00 the next morning. Rejecting repeated entry needs current state at scan time; an older report copy cannot decide this, even if its aggregates are correct. Comparing attendance over two weeks may not need an update on every scan, but the report must say whether late cancellations revised earlier counts. Draw inputs and outputs: create, cancel, and scan events enter the operational layer; one final state per booking enters the analytical layer; the report yields valid bookings and distinct attendance. Counting scan operations can inflate arrival count. Calculating validity only from current state without cancellation history can make yesterday's number inexplicable. Decide whether a metric means as known at day close or after current reconciliation. Both definitions may be useful; mixing them in one time series misleads. In an exam scenario, look for operation verbs: quickly record a change to one row, or scan and aggregate historical data. Large by itself does not mean analytical. Millions of short operations can remain transactions, and a small comparison report can be analytical. The trap is confusing volume with the decision and access pattern.

Test the effect of cutoff changes on the rates. A booking canceled after departure cannot be removed from the denominator for valid bookings at gate opening without redefining that measure. If a successful scan is later reversed because of a reader fault, a correction event must explain why the report differs from the operational screen. Keep one stable booking ID across source and report and retain event and processing times. Their difference explains delay but cannot justify duplicate attendance. That makes 56 arrivals auditable from events rather than a displayed total alone. On the exam, OLTP or OLAP describes the asked operation, not the whole organization; one system may need both.

A cancellation arrives after the attendance report closes. Ask when cancellation occurred and when it reached reporting. The source transaction can be correct while the report reflects an older copy. Adopt an explicit rule for reopening a period or recognising a correction later. Retain the previous version so the difference can be explained. Separate scan events from the current reservation state: rereading the state a week later may change how past attendance is interpreted. Put numerator, denominator and time definitions beside the percentage. These steps connect operations and analysis. Operations protect state transitions during work; analysis creates traceable historical comparisons. Each depends on information from the other even when they use different data copies.

Lesson summary

Separate event recording from aggregation by counting unit and freshness requirement.