The Challenge
ShopStream is a fast-growing marketplace with millions of orders. The operational database (OLTP) is optimized for transactional speed: adding orders, updating inventory, processing payments. But executives ask questions the OLTP can't answer efficiently:
- What was our revenue last month by product category?
- Which sellers drive the most value?
- What's the cohort retention for customers acquired in March 2025?
- What's the lifetime value of our top 100 customers?
- How does payment method affect return rates?
These questions require aggregating and joining millions of rows, scanning across time periods, and complex window functions. The OLTP database would slow to a crawl. That's why ShopStream needs an OLAP data warehouse โ a separate, read-optimized database designed for analytics.
In this capstone, you'll build that warehouse using the star schema pattern. You'll learn to:
- Design dimension tables (customers, products, sellers, dates)
- Create a fact table (orders) with foreign keys and CHECK constraints
- Load data via ETL (extract, transform, load)
- Write 15+ business intelligence queries
- Optimize queries with indexes and materialized views
- Understand performance with EXPLAIN ANALYZE
Star Schema Design
A star schema has a central fact table surrounded by dimension tables. The fact table stores business events (orders), and dimension tables store the context (who, what, when, where, how). Foreign keys connect fact rows to dimension rows, creating a "star" shape when visualized:
Why star schema?
- Simple joins โ Fact table is at the center; each dimension is joined directly. No complex multi-level normalization.
- Fast aggregations โ Queries group and sum across dimensions (e.g., revenue by category, by month, by seller).
- Query optimization โ The database can pre-compute common joins (with materialized views), cache results, and use bitmap indexes.
- Scalable โ Fact tables grow large, but dimensions stay small. A billion-row fact table joins fast with a 1-million-row dimension.
Fact vs. Dimension: A fact table stores metrics (quantities, amounts, counts). A dimension table stores descriptors (names, dates, categories). Think: a fact is "1000 units sold" and a dimension is "when = Jan 2025, who = Nike, where = India".
Dimension Tables
Dimensions describe the context of your facts. Let's create four dimension tables for ShopStream:
Q1: Create dim_customers
Q2: Create dim_products
Q3: Create dim_sellers
Q4: Create dim_dates
Fact Table with Foreign Keys
The fact table is the heart of the warehouse. Each row is an order. It stores measures (qty, amount, discount) and foreign keys to every dimension (customer, product, seller, date).
Q5: Create fact_orders
Key design decisions:
- Every dimension has a foreign key constraint. This ensures data integrity โ you can't order a product that doesn't exist.
qty_ordered > 0andunit_price > 0ensure valid business data.payment_methodis restricted to three options for consistency (used in later queries).date_returnedis nullable โ if an order wasn't returned, this is NULL. Used to calculate return rates.
Indexes for Query Performance
The fact table will have hundreds of thousands of rows. Indexes make queries fast by avoiding full table scans. Here are the key indexes: