Designing an Efficient E-commerce Data Warehouse

The proliferation of online sales necessitates robust analytical capabilities to understand customer behavior, track product performance, and optimize marketing efforts. This challenge requires you to design a foundational data warehouse schema for an e-commerce platform, focusing on efficiency for common analytical queries.

Problem Description

You are tasked with designing a star schema for an e-commerce data warehouse. The schema should efficiently store and retrieve information about customer orders, products, and customer demographics. The primary goal is to support analytical queries that aggregate sales data, analyze product popularity, and understand customer purchasing patterns.

Key Requirements:

• Fact Table: A central fact table to store transactional data (orders).
• Dimension Tables: Dimension tables to store descriptive attributes of entities involved in transactions (products, customers, dates).
• Denormalization: Strategically denormalize dimensions to improve query performance, balancing redundancy with read efficiency.
• Data Types: Choose appropriate data types for optimal storage and performance.
• Relationships: Define clear relationships between fact and dimension tables.

Expected Behavior:

The schema should allow for efficient execution of queries such as:

• Total sales amount per product category per month.
• Number of unique customers who purchased a specific product.
• Average order value per customer demographic group.
• Top 10 best-selling products by quantity sold in the last quarter.

Edge Cases to Consider:

• Orders with no associated customer (e.g., guest checkouts).
• Products that have been discontinued but still appear in historical orders.
• Customers who have no recorded orders.
• Handling of potential data anomalies or missing values in dimensions.

Examples

Example 1:

Input (Conceptual):

A set of raw transaction logs, product catalog information, and customer registration data.

• Transactions: (order_id, customer_id, product_id, quantity, price, order_date)
• Products: (product_id, product_name, category, supplier)
• Customers: (customer_id, first_name, last_name, email, city, state, registration_date)

Output (Conceptual Schema Design):

A set of SQL CREATE TABLE statements defining the data warehouse tables:

• dim_product: Stores product details.
• dim_customer: Stores customer details.
• dim_date: Stores date-related attributes.
• fact_orders: Stores order transaction details, linking to dimensions.

Explanation:

The output would be the SQL DDL (Data Definition Language) to create the tables and define their columns, data types, primary keys, and foreign keys.

Example 2:

Input (Conceptual Data):

• Transaction 1: (101, 501, 201, 2, 15.99, '2023-10-26')

• Transaction 2: (102, 502, 202, 1, 25.50, '2023-10-26')

• Transaction 3: (103, 501, 202, 3, 25.50, '2023-10-27')

• Product 201: ('Laptop Sleeve', 'Accessories', 'TechCo')

• Product 202: ('Wireless Mouse', 'Electronics', 'ElectroGear')

• Customer 501: ('Alice', 'Smith', 'alice@example.com', 'New York', 'NY', '2022-01-15')

• Customer 502: ('Bob', 'Johnson', 'bob@example.com', 'Los Angeles', 'CA', '2022-05-20')

Output (Conceptual Data Insertion & Query Result):

If fact_orders contained records derived from the above, and we ran a query like "Total sales amount for 'Electronics' on '2023-10-26'":

SELECT SUM(fo.quantity * fo.price)
FROM fact_orders fo
JOIN dim_product dp ON fo.product_key = dp.product_key
WHERE dp.category = 'Electronics'
  AND fo.order_date_key = (SELECT date_key FROM dim_date WHERE sale_date = '2023-10-26');

Output of the Query: 25.50

Explanation:

The query filters for orders on the specified date, joins with the product dimension to identify 'Electronics' products, and then sums the total sales amount. In this example, only Transaction 2 matches the criteria.

Constraints

• The data warehouse should support at least 10 million order records per year.
• Dimension tables will contain up to 1 million unique customers and 100,000 unique products.
• Query response times for common analytical aggregations should be under 5 seconds.
• The schema should be designed using standard SQL data types.
• You will be provided with pseudocode for table structures, but you must define the optimal schema.

Notes

• Consider how to handle historical changes in dimension attributes (e.g., a product changing its category). This is often referred to as Slowly Changing Dimensions (SCDs). For this challenge, you can focus on a simpler approach like Type 1 (overwriting) or Type 2 (adding new rows for changes) for the dim_product and dim_customer tables.
• Think about surrogate keys for efficient joins.
• The dim_date table is a crucial component of any data warehouse for time-based analysis. Consider what attributes would be useful (e.g., year, month, day of week, quarter).
• Focus on designing the tables and their relationships first. The loading process (ETL/ELT) is outside the scope of this challenge.