Your e-commerce app needs to handle 10,000 checkouts per minute. Your CFO needs a dashboard that shows quarterly revenue by region. Same data, two completely different access patterns. Two completely different schemas.
OLTP — built for transactions
OLTP (Online Transaction Processing) = the database your app writes to.
Optimized for:
- Many small writes per second — INSERT a new order, UPDATE an inventory count.
- Reading one row at a time — fetch the user's cart, look up a product.
- Data integrity — referential constraints, ACID transactions, foreign keys.
Schema characteristics:
- Highly normalized (3NF or beyond). No redundancy. Updates touch one row.
- Row-oriented storage — adjacent columns of the same row sit next to each other on disk.
- Indexes on common lookups — by user_id, by order_id, by SKU.
Example query: SELECT * FROM orders WHERE order_id = 12345; → milliseconds.
Examples: PostgreSQL, MySQL, MongoDB, DynamoDB (in transactional mode).
OLAP — built for analytics
OLAP (Online Analytical Processing) = the database analysts and BI tools read from.
Optimized for:
- Few queries per minute, each scanning millions of rows — "total revenue by region last quarter".
- Aggregations over large slices — SUM, AVG, COUNT across millions of rows.
- Reads dominate writes — bulk loads once a day or in micro-batches.
Schema characteristics:
- Denormalized — pre-joined dimension data, redundancy is fine because writes are rare.
- Columnar storage — adjacent values of the same column sit next to each other on disk.
- Partitioned by date or another high-cardinality column for pruning.
Example query: SELECT region, SUM(revenue) FROM fact_orders WHERE order_month >= '2026-01-01' GROUP BY region; → seconds to minutes.
Examples: Snowflake, BigQuery, Redshift, Databricks, ClickHouse, DuckDB.
Why the same schema can't serve both
| Need | OLTP schema | OLAP schema |
|---|---|---|
| Write speed | Fast (small focused writes) | Slow (bulk only) |
| Single-row read | Fast | Slow (columnar penalty) |
| Big aggregation | Slow (row reads, 50 columns wide) | Fast (columnar) |
| Storage cost | Higher per row (lots of small index pages) | Lower per row (compression) |
| Joins at query time | Required (normalized) | Avoided (denormalized) |
If you tried to run dashboards directly off the OLTP database:
- Your dashboards would time out scanning the orders table.
- Or they'd succeed but slow down the app — production users see lag.
- Schema changes for analytics (adding a dimension column) would require an app deploy.
If you tried to run the app off the OLAP warehouse:
- Single-row reads would be 100× slower than OLTP.
- Concurrent writes would be impossible at scale.
- Updates of single rows would rewrite huge columnar blocks.
That's why every serious company runs both: OLTP for the app, OLAP for analytics, and a pipeline that moves data from one to the other.
The flow
[ App ]
↓ writes
[ OLTP database — Postgres, MySQL ]
↓ extract (CDC, snapshots, change streams)
[ Lake / staging in S3 ]
↓ load + transform (dbt, Airflow, Fivetran)
[ OLAP warehouse — Snowflake, BigQuery ]
↓ queried by
[ BI tools — Looker, Tableau, Metabase ]
Every modern data team operates roughly this stack. The shape of the OLAP warehouse is what this course is about.
Common misconception: "the warehouse is just a copy of OLTP"
Bad warehouses are exactly this — a CDC copy of every OLTP table, with the OLTP schema unchanged. The schema that was good for the app is terrible for analytics. Your dashboards slow down; your metrics get inconsistent; your analytics engineers spend their time joining 14 tables instead of 3.
A good warehouse is a deliberate re-shape of the OLTP data — denormalized, dimensional, partitioned. That re-shape is data modeling.
Takeaway
The OLTP/OLAP split isn't optional. Trying to use one for both is a known anti-pattern. The warehouse is where deliberate analytics modeling happens — and the rest of this course is about how to do that modeling well.