Data Warehousing

It's quarter-end. The VP of Sales asks: "What was our revenue by product category, by region, for the last 12 months, compared to the same period last year?"

You could run that query against your production PostgreSQL database. But here's what happens:

1. The query scans tens of millions of order rows, joining with product, customer, and region tables
2. It takes 8 minutes to complete
3. During those 8 minutes, it consumes so much I/O and CPU that your production application slows to a crawl, real customers can't check out
4. The VP's next question ("now break it down by quarter") takes another 8 minutes

Running analytical queries on a production database is like using your daily commuter car for an off-road expedition. It technically works, but it's miserable for everyone involved.

Data warehouses solve this by creating a separate, purpose-built database optimized for analytical queries. Data flows from your production systems into the warehouse, transformed, cleaned, and reorganized for fast aggregations. The production database stays fast. The analysts get their answers in seconds.

A data warehouse differs from a production (OLTP) database in almost every dimension:

The ETL/ELT Pipeline:

Data gets into the warehouse through a process called ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform):

1. Extract. Pull data from source systems (production databases, APIs, log files, SaaS tools)
2. Transform. Clean, validate, deduplicate, and reshape the data (convert currencies, join datasets, compute derived fields)
3. Load. Bulk-insert the transformed data into the warehouse

Modern cloud warehouses favor ELT, load raw data first, then transform inside the warehouse using SQL. This is simpler and uses the warehouse's massive compute power for transformations.