What's changed: Deepened DP-900 Chapter 1 to the AZ-900 baseline (three data kinds + store/role tables, OLTP/OLAP + ACID/data warehouse/ETL-ELT, scenarios, FAQ, traps, more quizzes)
1.2Transactional and Analytical Workloads (OLTP and OLAP)
Understand the two major data workload categories—transactional (OLTP) and analytical (OLAP)—plus ACID properties and how data warehouses and ETL/ELT relate.
Data is used in two broad ways: transactional processing (OLTP) that runs day-to-day operations, and analytical processing (OLAP) that supports decisions. Even for the same data, the best database design differs depending on whether you "run the business" or "analyze the past to decide." DP-900 covers this distinction and the flow that connects them (ETL/ELT and data warehouses).
1.2.1OLTP and OLAP
OLTP (online transaction processing) handles many small reads/writes quickly—order entry, inventory updates, banking. Here "transactions must be correct" is paramount, which calls for ACID: Atomicity (all-or-nothing), Consistency (no contradictory state), Isolation (concurrent operations don’t interfere), and Durability (committed data is not lost). OLTP databases are normalized to support such transactions.
OLAP (online analytical processing) reads and aggregates large volumes of historical data to produce trend analysis, reports, and dashboards. Against a data warehouse that gathers many sources, it runs aggregation queries like "total sales by region and period." It is read-centric and uses denormalized designs (e.g., star schemas) for easy analysis.
| Aspect | OLTP (transactional) | OLAP (analytical) |
|---|---|---|
| Purpose | Run daily operations | Analyze history to decide |
| Operations | Many small reads/writes | Large reads/aggregations |
| Data | Current, detailed | Historical, aggregated |
| Design | Normalized (ACID) | Denormalized (for aggregation) |
| Example | Order entry, banking | Sales dashboards, BI |
1.2.2From OLTP to OLAP (ETL/ELT)
Often OLTP operational data is moved into an analytics platform (OLAP / data warehouse) for analysis. That movement is ETL (Extract → Transform → Load). Transforming before loading is ETL; loading first and transforming later is ELT, which is common for large cloud-scale data. Either way, this is the core of the pipelines built by the data engineer from the previous section.
Scenario: a bank. ATM and transfers—daily transactions are OLTP (handled reliably with ACID). Meanwhile, executives’ dashboards for "transaction trends by branch and month" are OLAP. Transaction data is consolidated into a data warehouse via ETL/ELT, where aggregation queries run. The same "transaction data" is served by different systems depending on purpose.
Watch the mix-ups: (1) OLTP = many small reads/writes (operations) / OLAP = large reads & aggregation (analysis). "Order processing" = OLTP; "sales dashboard" = OLAP. (2) ACID is asked in the OLTP context (transaction correctness). (3) Mind the word order of ETL (transform then load) vs ELT (load then transform).
Q. What does ACID stand for? Atomicity (all-or-nothing), Consistency (no contradiction), Isolation (no interference between concurrent ops), Durability (survives after commit). Q. Why separate OLTP and OLAP? Heavy analytics on the operational DB would slow daily transactions, so data is moved to a separate analytics platform (data warehouse). Q. Data warehouse vs data lake? A warehouse stores cleaned, structured data for analysis; a data lake holds large volumes of raw data in any format.
A common distinction: OLTP = day-to-day transactions (many small reads/writes, ACID) vs OLAP = analytics/aggregation (large reads over history). "Order processing" = OLTP; "sales dashboard" = OLAP. ACID describes OLTP transaction correctness.
1.2.3Section summary
- OLTP = day-to-day transactions (many small reads/writes, normalized, ACID)
- OLAP = analytics/aggregation (large reads over history, data warehouse, decision support)
- OLTP→OLAP movement is ETL (transform→load) / ELT (load→transform)—data-engineer pipelines
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. Which workload handles many small daily reads/writes, like an order-entry system?
Q2. Which workload aggregates historical sales to build an executive dashboard?
Q3. Which best characterizes OLTP?
Q4. Which ACID property guarantees a transaction "fully succeeds or is fully rolled back"?
Q5. What is the process of extracting, transforming, and loading operational data into an analytics platform generally called?