Data Warehouses & Analytics Storage
You'll learn to
- -Distinguish OLTP from OLAP workloads
OLTP vs OLAP
Everything built so far in this course is an OLTP workload (Online Transaction Processing), many small, fast reads and writes, one row at a time: place an order, update a balance, fetch a profile. OLAP (Online Analytical Processing) is the opposite shape: a handful of huge queries scanning millions of rows to answer questions like "total revenue by region, by month, for the last two years."
A data warehouse is like a massive library designed specifically for answering BIG questions. Your regular database (PostgreSQL, MySQL) is optimized for doing one thing at a time quickly: 'Get user #42's profile,' 'Update order #5678 status to shipped.' A data warehouse is optimized for scanning BILLIONS of rows to answer analytical questions: 'What's our total revenue by product category by region for the last 2 years?' 'Which acquisition channel brings users who spend the most in their first 30 days?' It stores data in COLUMNS instead of rows (columnar storage), which is perfect for analytics. When you need the SUM of a 'revenue' column across 2 billion rows, the database only reads that one column from disk, ignoring the other 50 columns. This makes analytical queries 10-100x faster than row-based databases. Data gets loaded into the warehouse from your production databases, event streams, and third-party sources through an ETL pipeline (Extract, Transform, Load). Business analysts, data scientists, and dashboards all query the warehouse. NEVER your production database!
Examples: Snowflake (most popular cloud DW), Google BigQuery (serverless, pay-per-query), AWS Redshift, ClickHouse (open-source, incredibly fast), Apache Druid (real-time analytics), Databricks Lakehouse
Running analytical queries directly against your production OLTP database causes real damage: large scans hold locks and burn I/O that your live traffic needs. The standard fix is to periodically extract data out of the OLTP database and load it into a warehouse (often column-oriented storage, which is much faster for "scan this one column across every row" queries than row-oriented storage) purpose-built for exactly this analytical access pattern.
Your product team wants a live dashboard of "revenue by region" that updates in real time, querying the production database directly. What's the concern?
"No concern: it's just a read query, so it shouldn't affect anything."
"An aggregation like that scans a large fraction of the orders table, and on a row-oriented OLTP database that means touching every column of every row even though the query only needs one or two: slow, and it holds resources that live transactional traffic needs. I'd route analytics to a column-oriented warehouse fed by an async ETL/CDC pipeline instead, accepting a short data-freshness lag in exchange for never letting analytics compete with production traffic."
What is the key limitation of object storage compared to block storage?
Level 14 (Google Drive) and Level 15 (Web Search Engine) apply this module directly.