The Core Idea
Two Fundamentally Different Workloads
A production application database is optimized for OLTP (Online Transaction Processing) — many small, fast, frequent reads and writes, like 'insert this new order' or 'update this user's profile.' A data warehouse is optimized for OLAP (Online Analytical Processing) — large, complex queries scanning huge volumes of historical data, like 'total revenue by region, by month, for the last 3 years.'
Running heavy analytical queries directly against a production OLTP database is a common but serious mistake: those large scans compete for the same resources (CPU, disk I/O, locks) that live customer transactions need, potentially slowing down or even blocking real-time application traffic. A data warehouse exists specifically to move analytical workloads onto a separate system, purpose-built and optimized for exactly that kind of heavy historical querying.
💡 Memory Trick
Think of a retail store's cash registers (OLTP) versus its corporate headquarters' quarterly business review (OLAP). The cash registers need to process one sale at a time, instantly, hundreds of times a minute — they cannot pause to calculate 'total sales by region for the last 3 years' in the middle of ringing up a customer. That kind of massive historical analysis happens somewhere else entirely, on data that's been extracted and organized specifically for that purpose, without ever slowing down the actual checkout line.
The Key Concepts
ETL and Star Schema
1
ETL — Extract, Transform, Load
The process that moves data FROM production OLTP systems INTO the data warehouse: Extract pulls raw data from one or more source systems; Transform cleans, reshapes, and combines that data into the warehouse's analytical structure; Load writes the transformed data into the warehouse. ETL typically runs on a schedule (hourly, nightly), meaning warehouse data is usually somewhat behind real-time — an acceptable trade-off for most historical analysis.
2
Star Schema — The Typical Warehouse Structure
Data warehouses are commonly organized around a 'star schema': a central FACT table holding the actual measured events (like individual sales transactions, with numeric measures like quantity and revenue), surrounded by several DIMENSION tables holding descriptive context (like a dates dimension, a products dimension, a customers dimension) that the fact table references. This structure is deliberately denormalized compared to a typical OLTP schema, trading some redundancy for much simpler, faster analytical queries.
Why Separate the Two Systems
Isolating Analytics From Production
Beyond the performance-isolation benefit (analytics queries never compete with production transactions for resources), a data warehouse is also structured completely differently — optimized for the very different query patterns of analytics (large aggregations across huge historical spans) versus OLTP (small, precise lookups and updates on current data). A schema well-suited to fast individual transactions (highly normalized, per the 3NF lessons) is often poorly suited to fast aggregate reporting across millions of historical rows, and vice versa.
Modern architectures sometimes use specialized columnar-storage database engines specifically for the warehouse layer (separate from the row-oriented storage typical of OLTP systems), since columnar storage is particularly efficient for the kind of 'aggregate this one column across millions of rows' queries that dominate analytical workloads.
🖥️ Applied Scenario
Your e-commerce site's checkout process has started timing out during peak hours, and you discover a scheduled analytics report is running directly against the same production database, generating a company-wide sales dashboard.
1
You identify that the analytics report's large historical aggregation queries are competing with live checkout transactions for the same database's CPU and I/O resources, causing the timeouts.
2
You set up an ETL pipeline that extracts order data from the production database on a schedule, transforms it into a star schema (a sales fact table with date, product, and customer dimensions), and loads it into a separate data warehouse.
3
You redirect the analytics dashboard to query the new data warehouse instead of the production database, completely isolating the two workloads from each other.
4
Conclusion: checkout performance returns to normal because it's no longer competing with analytical queries, and the dashboard's report actually runs faster too, since the warehouse's star schema is purpose-built for exactly this kind of aggregation.
📌 Exam Application
Exam questions frequently ask you to distinguish OLTP from OLAP workloads by their typical query characteristics (many small transactions vs. few large aggregations), and to explain why running analytics directly against a production OLTP database is problematic. You may also be asked to describe the role of ETL in populating a data warehouse, or to identify the fact table and dimension tables in a described star schema.
⚠️ Most Common Data Warehouses Mistakes
The most common mistake is assuming a data warehouse is just 'a bigger version' of a production database rather than a genuinely differently-structured system optimized for a different workload — a warehouse's denormalized star schema would actually be a poor, redundancy-prone choice for a live transactional application, just as a normalized OLTP schema is a poor, slow choice for large-scale historical aggregation. Another frequent error is expecting warehouse data to be perfectly real-time — because ETL typically runs on a schedule, warehouse data normally lags production data by some interval (minutes to a day), which is an accepted, deliberate trade-off for most historical analysis use cases, not a bug.
✓ Quick Self-Test
Can you explain, in one sentence, the key difference between an OLTP workload and an OLAP workload? Can you describe what ETL stands for and explain why warehouse data is typically not perfectly real-time?
Next Lesson
Database Sharding
→
← All Databases Lessons