SUBMISSION: Case 4 — E-Commerce Order Funnel Star Schema

# SUBMISSION: Case 4 — E-Commerce Order Funnel Star Schema > 🎯 **Hands-on Portfolio Deliverable:** In this project, you will act as the Lead Analytics Engineer designing the production analytics schema for a high-volume direct-to-consumer e-commerce brand. You will define the dimensional star schema, handle Slowly Changing Dimensions (SCDs), snapshot time-sensitive financial measures, and submit your production DDL and verification queries. ## 🏢 Business Scenario & Executive Prompt You are hired by **Aura Retail**, a high-growth D2C e-commerce platform processing over 15,000 orders per day across North America and Europe. The executive team (CFO, Head of Product, VP of Growth) relies on Looker dashboards, but queries directly hitting their production PostgreSQL database are causing severe connection timeouts. Your challenge is to design a high-performance **Kimball dimensional star schema** for the analytics warehouse that satisfies three core business demands: 1. **Product Economics:** Track gross revenue, discounts, snapshotted COGS, and real margins at the SKU line-item level. 2. **Historical Attribution (SCD Type 2):** When a customer relocates to a new region or changes segments, historical sales must remain attributed to their location *at the time of purchase*, while new orders reflect their current profile. 3. **Operational Optimization:** Group multi-flag order metadata (first order, gift, express shipping) cleanly without polluting fact rows with dozens of low-cardinality boolean columns. --- ## 🎯 Required Deliverables You must upload a single `.sql` or `.zip` file containing: 1. **`ecommerce_star_schema.sql`**: Production DDL creating `fct_order_items`, `dim_customer`, `dim_product`, `dim_date`, and `dim_order_flags`. 2. **Architecture Justification**: Document your chosen grain, SCD strategy, and shipping allocation formula (either as SQL header comments or in an accompanying `architecture.md`). 3. **Verification Queries (`analytics_verification.sql`)**: 2 runnable analytical SQL queries demonstrating how the schema answers (a) Monthly margin by product category, and (b) Customer cohort revenue by segment. --- > *"You have these OLTP tables: `users`, `products`, `categories`, `orders`, `order_items`, `payments`, `addresses`. Design the analytics star schema for the orders fact. Specify the grain, fact columns, and the dimensions you'd build."* This is a portfolio-defining design conversation. The interviewer wants to hear you reason about grain, conformed dimensions, and SCD types — not just produce a diagram. --- ## Step 1 — Clarify Always start with grain questions: - *"One row per order, or one row per line item?"* — line item lets you analyze SKU-level economics; order lets you analyze checkout-level. Pick line item unless explicitly told otherwise; you can always aggregate up. - *"Do you need historical attributes (e.g., customer country at time of order)?"* — yes → Type 2 SCD on the relevant dimensions. No → Type 1 is simpler. - *"What's the analytics use case driving this?"* — common ones: revenue by product/region, conversion funnel, customer LTV, marketing attribution. Each affects what dimensions you'd prioritize. For this case, assume: - Line-item grain (most flexible). - History matters for customer and product attributes (Type 2). - Primary use cases: revenue by region/category, customer cohort retention, product-level margin. ## Step 2 — Define the fact ```sql CREATE TABLE fct_order_items ( -- surrogate fact key (avoid composite keys) order_item_key BIGINT PRIMARY KEY, -- degenerate dimensions (kept on the fact for traceability) order_id BIGINT, -- source order ID order_item_id BIGINT, -- source line item ID -- foreign keys to dimensions order_date_key INTEGER, customer_key INTEGER, product_key INTEGER, store_key INTEGER, ship_to_address_key INTEGER, payment_method_key INTEGER, order_flags_key INTEGER, -- junk dim -- measures (additive) quantity INTEGER, unit_price NUMERIC(12, 2), discount_amount NUMERIC(12, 2), tax_amount NUMERIC(12, 2), shipping_amount NUMERIC(12, 2), -- allocated to line item (with grain caveat) gross_revenue NUMERIC(12, 2), -- quantity × unit_price net_revenue NUMERIC(12, 2), -- gross - discount cogs NUMERIC(12, 2), -- product cost × quantity (snapshot at time of order) margin NUMERIC(12, 2), -- net_revenue - cogs -- audit inserted_at TIMESTAMPTZ ); ``` Reasoning out loud: - **Grain**: one row per order line item. - **Degenerate dimensions**: `order_id` and `order_item_id` stay on the fact (no other attributes to put in a separate table). - **Shipping amount allocation**: at line-item grain, shipping needs to be allocated per line item. Standard approac