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:

โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ” โ”‚ STAR SCHEMA โ”‚ โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜ dim_dates (time context) โ†‘ โ”‚ dim_sellers โ† fact_orders โ†’ dim_customers (seller โ”‚ (events) (customer context) โ”‚ context) โ”‚ dim_products (product context) FACT TABLE: fact_orders โ”œโ”€ order_id (primary key) โ”œโ”€ customer_id (FK โ†’ dim_customers) โ”œโ”€ product_id (FK โ†’ dim_products) โ”œโ”€ seller_id (FK โ†’ dim_sellers) โ”œโ”€ date_id (FK โ†’ dim_dates) โ”œโ”€ qty_ordered, unit_price, discount, total_amount โ”œโ”€ payment_method, shipping_cost, order_status โ””โ”€ date_returned DIMENSIONS: โ”œโ”€ dim_customers: customer_id, first_name, last_name, email, city, signup_date โ”œโ”€ dim_products: product_id, product_name, category, is_discontinued โ”œโ”€ dim_sellers: seller_id, seller_name, seller_city, seller_country, segment โ””โ”€ dim_dates: date_id, calendar_date, year, month, quarter, day_of_week, week_number

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

sql
CREATE TABLE

Q2: Create dim_products

sql
CREATE TABLE

Q3: Create dim_sellers

sql
CREATE TABLE

Q4: Create dim_dates

sql
CREATE TABLE

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

sql
CREATE TABLE

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 > 0 and unit_price > 0 ensure valid business data.
  • payment_method is restricted to three options for consistency (used in later queries).
  • date_returned is 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:

Q6: Create Indexes on fact_orders

sql
CREATE INDEX

Q7: Create Indexes on Dimensions

sql
CREATE INDEX

ETL: Extract, Transform, Load Data

ETL is the process of moving data from source systems into the warehouse. For ShopStream, we'll simulate loading data from a transactional database into our star schema.

Q8: Load Dimensions โ€” dim_customers

sql
INSERT 0 8

Q9: Load Dimensions โ€” dim_products

sql
INSERT 0 8

Q10: Load Dimensions โ€” dim_sellers

sql
INSERT 0 6

Q11: Load Dimensions โ€” dim_dates (Generate 24 Months)

sql
INSERT 0 731

Q12: Load Fact Table โ€” fact_orders

sql
INSERT 0 20 (+ 737 more rows)

Note: The full ETL script loads 757 orders across Jan 2024โ€“May 2025. For this tutorial, we show representative samples. Check the downloadable file for the complete dataset.

Business Analytics Queries (15+)

Now that the warehouse is populated, let's run queries to answer real business questions. These are the questions executives ask.

Q13: Revenue by Month (Trend)

sql
year | month | order_count | total_revenue | avg_order_value 2024 | 1 | 43 | 7827.46 | 181.91 2024 | 2 | 38 | 6542.10 | 172.16 2024 | 3 | 45 | 8234.55 | 182.99 ... 2025 | 4 | 50 | 9667.03 | 193.34 2025 | 5 | 48 | 9214.27 | 191.96

Q14: Revenue by Product Category (Top 3)

sql
category | order_count | total_revenue | pct_of_total Electronics | 182 | 51256.99 | 32.51 Home & Kitchen | 145 | 21144.47 | 13.42 Clothing | 168 | 17337.47 | 11.01 Sports | 142 | 15823.64 | 10.05

Q15: Top 10 Sellers by Revenue

sql
seller_name | seller_tier | order_count | total_revenue | pct_of_total FashionForward Co | platinum | 87 | 11123.90 | 7.05 SoundWave Audio | platinum | 85 | 11048.22 | 7.00 TechNova | platinum | 92 | 10987.45 | 6.97 CozyNest Home | premium | 78 | 9876.54 | 6.27

Q16: Customer Cohort Analysis (Signup Month Cohorts)

sql
signup_year | signup_month | customer_count | cohort_revenue | avg_spend_per_customer 2024 | 1 | 1 | 8543.21 | 8543.21 2024 | 2 | 1 | 7234.56 | 7234.56 ... 2025 | 3 | 5 | 13018.80 | 2603.76

Q17: Customer Lifetime Value (Top 20 Customers)

sql
customer_id | customer_name | city | total_purchases | lifetime_value | avg_order_value 6 | Alexander White | Chicago | 22 | 4793.25 | 217.88 1 | Alice Johnson | Mumbai | 19 | 4234.56 | 222.87 7 | Bella Garcia | Miami | 18 | 3987.43 | 221.52

Q18: Payment Method Effectiveness (Total & Percentage)

sql
payment_method | order_count | total_revenue | pct_of_total | avg_order_value standard | 389 | 74063.95 | 46.93 | 190.32 express | 168 | 32205.09 | 20.41 | 191.70 same_day | 50 | 9614.25 | 6.09 | 192.29

Q19: Return Rate by Seller

sql
seller_name | total_orders | returned_orders | return_rate_pct CozyNest Home | 90 | 10 | 11.11 TechNova | 100 | 10 | 10.00 SoundWave Audio | 95 | 8 | 8.42 FashionForward Co | 98 | 7 | 7.14

Q20: Geographic Analysis โ€” Revenue by Customer City

sql
city | country | unique_customers | order_count | total_revenue | avg_order_value FL | USA | 1 | 134 | 25785.39 | 192.43 CO | USA | 1 | 132 | 25512.97 | 193.28 IL | USA | 1 | 99 | 18996.03 | 191.88 Mumbai | India | 1 | 87 | 16732.45 | 192.33

Q21: Best Performing Days (Day of Week)

sql
day_name | order_count | total_revenue | avg_order_value Thursday | 115 | 22145.32 | 192.57 Wednesday | 108 | 20876.54 | 193.30 Tuesday | 105 | 20145.67 | 191.87

Query Performance & Optimization

Star schema queries are naturally fast, but let's verify with EXPLAIN ANALYZE and explore materialized views for pre-computed results.

Q22: EXPLAIN ANALYZE โ€” Understand Execution

sql
Limit (cost=1234.56..1234.78 rows=10 width=50) (actual time=0.45..0.52 rows=10 loops=1) -> Sort (cost=1234.56..1245.67 rows=89 width=50) (actual time=0.42..0.48 rows=10 loops=1) Sort Key: (sum(fo.total_amount)) DESC Sort Space Used: 25 kB -> HashAggregate (cost=1010.23..1020.34 rows=89 width=50) (actual time=0.32..0.38 rows=6 loops=1) Group Key: ds.seller_id, ds.seller_name -> Hash Join (cost=45.23..987.65 rows=400 width=50) (actual time=0.12..0.28 rows=400 loops=1) Hash Cond: (fo.seller_id = ds.seller_id) -> Seq Scan on fact_orders fo (cost=0.00..750.00 rows=757 width=20) (actual time=0.05..0.18 rows=757 loops=1) Filter: (order_status = 'delivered') -> Hash (cost=30.00..30.00 rows=6 width=30) (actual time=0.02..0.02 rows=6 loops=1) -> Seq Scan on dim_sellers ds (cost=0.00..30.00 rows=6 width=30) (actual time=0.00..0.01 rows=6 loops=1) Planning Time: 0.123 ms Execution Time: 0.52 ms

What this tells us:

  • The join uses a Hash Join (very efficient for this data size).
  • Actual execution time is 0.52 ms โ€” extremely fast!
  • The fact table scan filters on order_status = 'delivered' โ€” about 400 rows out of 757.
  • The small dimension table (6 sellers) is hashed into memory, then joined against facts.

Q23: Create a Materialized View โ€” Pre-computed Aggregations

For queries that run frequently (like the top 10 sellers dashboard), create a materialized view to cache results:

sql
CREATE MATERIALIZED VIEW

Q24: Query the Materialized View (Instant Results)

sql
seller_id | seller_name | year | month | order_count | monthly_revenue 2 | FashionForward Co | 2025 | 5 | 12 | 2345.67 4 | SoundWave Audio | 2025 | 5 | 11 | 2198.43 1 | TechNova | 2025 | 5 | 10 | 2087.54

Q25: Refresh Materialized View (When New Data Arrives)

sql
REFRESH MATERIALIZED VIEW

Q26: Analyze Table Statistics (Optimize Query Plans)

sql
ANALYZE

Test Your Knowledge

Question 1 of 5
What is a star schema, and why is it better for analytics than normalized OLTP?
Question 2 of 5
Name 3 business questions the ShopStream data warehouse can answer.
Question 3 of 5
What is a slowly changing dimension?
Question 4 of 5
How do materialized views help optimize warehouse performance?
Question 5 of 5
What is the relationship between fact and dimension tables?
0
/5
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

You've built a production data warehouse. Go deeper.

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’