🎯 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:
- Product Economics: Track gross revenue, discounts, snapshotted COGS, and real margins at the SKU line-item level.
- 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.
- 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:
ecommerce_star_schema.sql: Production DDL creatingfct_order_items,dim_customer,dim_product,dim_date, anddim_order_flags.- Architecture Justification: Document your chosen grain, SCD strategy, and shipping allocation formula (either as SQL header comments or in an accompanying
architecture.md). - 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
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_idandorder_item_idstay 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 approach: allocate proportional to gross_revenue. Document the allocation rule.
- COGS snapshot: store the cost at time of order. If we joined to current
dim_product.cost, historical margin would change every time we update product cost.
Step 3 — Define the dimensions
dim_customer (Type 2)
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY,
customer_id BIGINT, -- natural
-- attributes
email VARCHAR,
full_name VARCHAR,
signup_date DATE,
country VARCHAR,
region VARCHAR,
customer_segment VARCHAR, -- enterprise, smb, consumer
is_internal_test BOOLEAN, -- flag for test accounts
-- Type 2
effective_from TIMESTAMPTZ,
effective_to TIMESTAMPTZ,
is_current BOOLEAN
);
Note: email and full_name are Type 1 within the same row (overwrite on change). country, region, segment are Type 2 (versioned). This is the per-attribute SCD strategy.
dim_product (Type 2 on category; Type 1 elsewhere)
CREATE TABLE dim_product (
product_key INTEGER PRIMARY KEY,
product_id BIGINT,
sku VARCHAR,
product_name VARCHAR,
category_name VARCHAR, -- Type 2
department_name VARCHAR, -- Type 2 (or Type 1 if categories are stable)
brand VARCHAR,
current_price NUMERIC(12, 2), -- for reference; fact uses snapshot
current_cost NUMERIC(12, 2), -- for reference; fact uses snapshot
effective_from TIMESTAMPTZ,
effective_to TIMESTAMPTZ,
is_current BOOLEAN
);
dim_date (Type 1, the calendar)
The standard analytics calendar. Pre-populated 5 years back, 2 years forward.
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY, -- YYYYMMDD as integer
full_date DATE,
year SMALLINT,
quarter SMALLINT,
month SMALLINT,
month_name VARCHAR,
week SMALLINT,
day_of_month SMALLINT,
day_of_week SMALLINT,
day_name VARCHAR,
is_weekend BOOLEAN,
is_holiday_in VARCHAR[], -- ['IN'] for Indian holiday, ['US'] for US, etc.
fiscal_year SMALLINT,
fiscal_quarter SMALLINT
);
dim_store, dim_address, dim_payment_method
Smaller dimensions, mostly Type 1. Address is interesting: do you Type 2 it (if customers change address) or treat each order's ship_to as a separate dimension key? Pragmatic answer: snapshot the ship_to address into a separate row each time it's used, with a hash key.
dim_order_flags (junk dim)
CREATE TABLE dim_order_flags (
order_flags_key INTEGER PRIMARY KEY,
is_gift BOOLEAN,
is_first_order BOOLEAN,
is_express_delivery BOOLEAN,
is_corporate BOOLEAN,
is_test BOOLEAN
);
Combines 5 small flags into one lookup table. ~50 distinct combinations expected.
Step 4 — Talk through the tradeoffs
This is what separates a junior from a senior:
You: "I went with line-item grain because it gives the most analytical flexibility — we can roll up to order-level or customer-level later, but we can't decompose a daily summary into line items. The trade-off is fact row count: ~3× the order count. Modern warehouses handle this comfortably.
Type 2 on customer country and segment because cohort analysis and retention metrics need accurate attribution at fact time. Type 1 on email because no one queries historical emails.
The shipping amount allocation rule needs documenting — I'd allocate proportional to gross revenue per line, and note the rule on the table description so analysts don't naively SUM shipping across rows and get the wrong total.
COGS is snapshotted at the time of order; storing it as a measure on the fact rather than joining to dim_product.cost. Historical margin must stay stable even when costs change.
The order_flags junk dim is a small optimization; could be skipped if the team is small and prefers explicit columns. For a real warehouse with 50+ flags accumulated over time, the junk dim pays off."
Common follow-ups
Follow-up 1: "How would you handle returns?"
Two options:
- (a) Add a
fct_returnsat line-item grain, mirroringfct_order_itemswith negative measures and a link back to the original. - (b) Add return_amount, return_date_key as additional measures and FKs on
fct_order_items. Pros: single fact, easier join. Cons: returns are usually a separate event and don't always exist; nullable columns proliferate.
The cleaner answer is usually (a) — separate fact, conformed customer/product dimensions, link via order_item_id.
Follow-up 2: "How would you serve a real-time dashboard from this?"
Materialize aggregations: hourly fct_order_items_hourly summarized to (date_key, region, category, hour). Refresh via incremental dbt model or warehouse-native streaming. Dashboards query the aggregate, not the line-item fact.
Follow-up 3: "What if products have multiple categories (multi-categorization)?"
Add a fct_order_item_categories bridge table. Each row: (order_item_key, category_key). Allows multi-attribution while keeping category in the dimension. Aggregations need care — without DISTINCT, revenue triple-counts for triple-categorized products. The cleaner approach is to pick a single canonical category per product or to allocate revenue (50% to cat A, 50% to cat B).
📥 Download Starter Assets & Mock Database
Choose your preferred environment to get started immediately:
💾 Pre-Seeded SQLite Database (Recommended — Zero Setup):
⬇ Download case4_ecommerce_funnel.sqlite
Pre-loaded with raw source tables, mock transactional records, and schema skeletons. Double-click to open in DB Browser for SQLite, DBeaver, or VS Code.📄 ANSI / PostgreSQL SQL Starter Script:
⬇ Download case4_ecommerce_funnel_starter.sql
Contains clean DDL statements, table definitions, and mock INSERT statements ready to paste into PostgreSQL, DuckDB, Snowflake, or BigQuery.
🛠️ How to Complete & Run This Case Study
You can complete this hands-on project using whichever tool matches your local setup:
- Option 1: SQLite / DB Browser / DBeaver (Easiest — Zero Installation)
Downloadcase4_ecommerce_funnel.sqliteabove. Open it in DB Browser for SQLite, DBeaver, or VS Code (with the free SQLite Viewer extension). All source ingestion tables and sample records are already populated. Write your target dimensional models and verification queries directly against the database with 0 server configuration. - Option 2: Local PostgreSQL or DuckDB
Downloadcase4_ecommerce_funnel_starter.sql. Execute the script in your local PostgreSQL database (via psql, DBeaver, or pgAdmin) or in DuckDB. - Option 3: Cloud Data Warehouse (Snowflake / BigQuery)
Copy and paste the DDL and mock seed data into a Snowflake worksheet or Google BigQuery dataset.
💻 Starter Scaffold
Use this template to begin your implementation:
-- ====================================================================
-- DELIVERABLE 1: ecommerce_star_schema.sql
-- ====================================================================
-- 1. FACT TABLE: fct_order_items (Line-Item Grain)
CREATE TABLE IF NOT EXISTS fct_order_items (
order_item_key BIGINT PRIMARY KEY, -- Surrogate fact key
order_id BIGINT NOT NULL, -- Degenerate dimension
order_item_id BIGINT NOT NULL, -- Degenerate dimension
-- Dimension Foreign Keys
order_date_key INTEGER NOT NULL, -- FK -> dim_date
customer_key INTEGER NOT NULL, -- FK -> dim_customer
product_key INTEGER NOT NULL, -- FK -> dim_product
order_flags_key INTEGER NOT NULL, -- FK -> dim_order_flags
-- Measures
quantity INTEGER NOT NULL,
unit_price NUMERIC(12, 2) NOT NULL,
discount_amount NUMERIC(12, 2) DEFAULT 0.00,
gross_revenue NUMERIC(12, 2) NOT NULL, -- quantity * unit_price
net_revenue NUMERIC(12, 2) NOT NULL, -- gross - discount
cogs NUMERIC(12, 2) NOT NULL, -- snapshotted unit cost * quantity
margin NUMERIC(12, 2) NOT NULL, -- net_revenue - cogs
allocated_shipping_cost NUMERIC(12, 2) DEFAULT 0.00,
inserted_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- 2. DIMENSION: dim_customer (SCD Type 2)
CREATE TABLE IF NOT EXISTS dim_customer (
customer_key INTEGER PRIMARY KEY,
customer_id BIGINT NOT NULL,
email VARCHAR(255) NOT NULL, -- Type 1
full_name VARCHAR(255) NOT NULL, -- Type 1
country VARCHAR(100) NOT NULL, -- Type 2
customer_segment VARCHAR(50) NOT NULL, -- Type 2
effective_from TIMESTAMPTZ NOT NULL,
effective_to TIMESTAMPTZ,
is_current BOOLEAN NOT NULL DEFAULT TRUE
);
-- 3. DIMENSION: dim_product (SCD Type 2 on Category)
CREATE TABLE IF NOT EXISTS dim_product (
product_key INTEGER PRIMARY KEY,
product_id BIGINT NOT NULL,
sku VARCHAR(100) NOT NULL,
product_name VARCHAR(255) NOT NULL,
category_name VARCHAR(100) NOT NULL, -- Type 2
department_name VARCHAR(100) NOT NULL, -- Type 2
current_retail_price NUMERIC(12, 2) NOT NULL,
effective_from TIMESTAMPTZ NOT NULL,
effective_to TIMESTAMPTZ,
is_current BOOLEAN NOT NULL DEFAULT TRUE
);
-- 4. JUNK DIMENSION: dim_order_flags
CREATE TABLE IF NOT EXISTS dim_order_flags (
order_flags_key INTEGER PRIMARY KEY,
is_gift BOOLEAN NOT NULL,
is_first_order BOOLEAN NOT NULL,
is_express_delivery BOOLEAN NOT NULL,
is_corporate BOOLEAN NOT NULL
);
-- ====================================================================
-- DELIVERABLE 2: analytics_verification.sql
-- ====================================================================
-- Query 1: Monthly Profit Margin % by Product Department
-- TODO: Write query joining fct_order_items to dim_product and dim_date.
-- Query 2: Customer Segment Cohort Revenue
-- TODO: Write query analyzing net revenue by customer_segment using Type 2 dimension keys.
📊 Evaluation Rubric (100 Points Total)
| Evaluation Criterion | Weight | Target Standard (Pass ≥ 80%) |
|---|---|---|
| 1. Grain Integrity & Measure Additivity | 25 pts | Grain is strictly atomic line-item (order_item_id); measures distinguish additive (gross_revenue, cogs, margin) from non-additive metrics; shipping cost is properly allocated. |
| 2. Key Strategy & SCD Design | 25 pts | Clean separation of natural IDs vs surrogate keys; per-attribute SCD Type 2 accurately configured on customer geography/segment and product hierarchy with timestamp intervals (effective_from, effective_to, is_current). |
| 3. Production DDL & Cloud Standards | 25 pts | Robust PostgreSQL / Snowflake / BigQuery DDL; appropriate precision types (NUMERIC(12,2)), non-null constraints, and indexing/clustering noted. |
| 4. Analytical Queryability & Verifiability | 25 pts | Included verification queries execute cleanly without fan-outs; demonstrates that historical margins stay stable even when modern product costs change. |
📤 Submission Instructions & Guidelines
- Deliverable Formats: Upload your completed
.sqlfile or a.ziparchive containing your SQL scripts and documentation (.md,.pdf, or ERD diagrams). - Dialect Compatibility: Ensure all DDL and queries execute cleanly on standard ANSI SQL, PostgreSQL, Snowflake, BigQuery, or Databricks.
- Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
- Upload: Use the drag-and-drop file upload zone below. You can optionally detail architectural trade-offs in the submission notes box.
- Confirmation: Click Submit Assignment. Your submission will be recorded, your lesson and module progress will be marked complete, and you can download your submitted work at any time.