OLAP vs OLTP Workloads

OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are two categories of data processing workloads. They exist because the requirements for recording business transactions are fundamentally different from those for analyzing historical data. OLTP systems are designed to handle a large number of short, atomic transactions — typically INSERT, UPDATE, DELETE, or small SELECT queries — with high concurrency and low latency. Every transaction must be ACID-compliant (Atomic, Consistent, Isolated, Durable) to ensure data integrity. Examples: bank ATM transactions, e-commerce order placement, airline reservation systems. OLAP systems, on the other hand, are optimized for complex queries that aggregate large volumes of data — often millions or billions of rows — to support business intelligence (BI) and reporting. They are not ACID-focused; instead they prioritize query throughput and data compression. Examples: monthly sales reports, customer churn analysis, financial forecasting.

The fundamental difference in storage engine design is row-oriented vs. column-oriented storage. OLTP databases (e.g., SQL Server, Azure SQL Database) store data row by row on disk. Each row contains all columns for a single record, stored contiguously. This design is ideal for row-level operations: when you UPDATE a customer's address, the database reads the entire row, modifies it, and writes it back. Indexes (like B-trees) are used to locate rows quickly by key values. In contrast, OLAP systems (e.g., Azure Synapse Analytics, SQL Server Analysis Services) store data column by column. Each column's values for all rows are stored together. This allows the query engine to read only the columns needed for a query, dramatically reducing I/O. For example, a query that sums sales by region only reads the 'sales amount' and 'region' columns, skipping the other 50 columns. Column storage also enables better compression because values within a column often have low cardinality (e.g., region names repeat). Compression ratios of 5x-10x are common, reducing storage cost and improving scan speeds.

In Azure, you don't configure OLTP vs. OLAP directly; you choose the service. However, understanding T-SQL differences helps. For OLTP, you might use:

-- High-concurrency OLTP example
BEGIN TRANSACTION;
UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductID = 123;
INSERT INTO Orders (CustomerID, ProductID, Quantity) VALUES (456, 123, 1);
COMMIT TRANSACTION;

For OLAP, typical queries use aggregations:

-- OLAP aggregation query
SELECT d.Category, SUM(f.SalesAmount) AS TotalSales
FROM FactSales f
JOIN DimProduct d ON f.ProductKey = d.ProductKey
WHERE f.DateKey BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY d.Category;

In Azure Synapse, you can view distribution and compression using:

DBCC PDW_SHOWSPACEUSED('FactSales');

This shows row count, reserved space, and data compression ratio.

The DP-900 exam will present a scenario — e.g., "A company needs to process thousands of customer orders per second with high consistency" — and ask you to choose the correct workload type and the best Azure service. Alternatively, they may ask which characteristic belongs to OLTP vs. OLAP. Key differentiators: read/write pattern, latency, concurrency, data freshness, schema design.

A global online retailer uses Azure SQL Database for its OLTP workload. The system processes over 10,000 transactions per second during peak hours, including order placement, payment processing, and inventory updates. Each transaction must be ACID-compliant to ensure no double charges or overselling. The database is scaled using Azure SQL Database's Hyperscale tier, which allows up to 100 TB of storage and auto-scaling compute. The schema is highly normalized with tables for Customers, Orders, OrderDetails, Products, and Inventory. Indexes are carefully designed: clustered index on primary keys, non-clustered indexes on foreign keys and frequently filtered columns (e.g., OrderDate). The team uses read replicas for reporting to offload analytical queries. Common misconfigurations include insufficient indexing causing deadlocks, and choosing the wrong service tier (e.g., Basic instead of Standard) leading to throttling. The solution handles 99.99% uptime with geo-replication for disaster recovery.

A retail chain with 500 stores uses Azure Synapse Analytics to analyze sales data. The OLAP system ingests nightly batches of transaction data from each store's OLTP system via Azure Data Factory. The data is loaded into a star schema: a FactSales table (with columns SalesAmount, Quantity, DateKey, StoreKey, ProductKey) and dimension tables (DimDate, DimStore, DimProduct). The fact table contains 2 billion rows and is hash-distributed on StoreKey to balance query load. Columnstore indexes provide 10x compression and fast aggregation. Analysts run complex queries to identify trends, such as "total sales per region for the last quarter compared to same period last year." The system uses materialized views to pre-aggregate common metrics, reducing query time from minutes to seconds. A common issue is data skew — if one store has significantly more sales than others, its distribution node becomes a bottleneck. The team mitigates this by using round-robin distribution for the fact table instead of hash distribution. The OLAP system also integrates with Power BI for interactive dashboards.

A mid-sized company uses Azure SQL Database with columnstore indexes to support both OLTP and OLAP workloads on the same database. The OLTP part handles order entry during the day, while the OLAP part runs nightly reports. The columnstore index is created on a non-clustered columnstore index on the Orders table, allowing fast aggregations without affecting OLTP performance. However, the system experiences higher latency for OLTP updates because columnstore indexes are not optimized for point updates. The solution is to use a memory-optimized table for high-frequency updates and a columnstore index for historical data. This hybrid approach is a compromise and not recommended for large-scale OLAP; the exam focuses on the pure separation of workloads.