Sales Reporting Star Schema Design

Context

You’re joining the data platform team at a global e-commerce marketplace processing 10M+ orders/day across web and mobile. Finance and Sales Ops rely on a curated sales reporting layer for daily revenue, discounts, returns, and margin reporting by product category, region, and sales channel. The current operational schema is highly normalized and optimized for writes, but analysts are struggling with slow queries and inconsistent definitions (e.g., “net sales” vs “gross sales”).

Core Question

Design a data model (tables + keys + relationships) for a sales reporting system that supports common analytics queries such as:

• Daily/weekly/monthly revenue and units sold
• Revenue by product category, brand, and region
• Sales by channel (web/app/partner), campaign, and sales rep
• Net sales after discounts, refunds, and returns

In your answer, explain:

1. Which tables you would create (fact vs dimension) and what each table represents.
2. Primary keys and foreign keys for each table, including whether you’d use surrogate keys vs natural keys.
3. The grain of your fact table(s) (e.g., order-level vs line-item-level) and why.
4. How you would model slowly changing attributes (e.g., customer region changes, product category reclassification).
5. How you would handle returns/refunds and discount allocation (line-level vs order-level) so that finance metrics reconcile.

Scope Guidance (what a strong answer includes)

• A clear statement of the grain and how it prevents double counting in joins/aggregations.
• A star schema (or snowflake where justified) with explicit 1-to-many and many-to-1 relationships.
• Concrete key choices (surrogate integer keys, unique constraints on natural identifiers, composite keys where appropriate).
• Practical considerations: late-arriving dimensions, deduplication, and how the model supports performant joins for BI tools.

Fact vs Dimension Tables (Star Schema)

A star schema places measurable events in fact tables (e.g., sales line items) and descriptive attributes in dimension tables (e.g., product, customer, date). This reduces join complexity for analytics and improves performance in columnar warehouses.

SELECT d.calendar_date, p.category, SUM(f.net_sales_amount) AS net_sales
FROM fact_sales_line f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY 1,2;

Grain Definition and Additivity

The grain defines what a single row represents (e.g., one order line item). Correct grain ensures metrics are additive and prevents double counting when joining to dimensions like product or customer.

/* Grain example: one row per order_id + line_number */
-- fact_sales_line(order_id, line_number, product_key, quantity, net_sales_amount)

Surrogate Keys vs Natural Keys

Surrogate keys (warehouse-generated integers) provide stable joins even when source identifiers change or collide across systems. Natural keys (e.g., SKU, customer_id) should typically be enforced via unique constraints and stored for lineage, but not always used as the primary join key in the warehouse.

CREATE TABLE dim_product (
  product_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku VARCHAR(64) NOT NULL,
  ...,
  UNIQUE (sku)
);

Slowly Changing Dimensions (SCD Type 2)

SCD Type 2 preserves history by versioning dimension rows with effective start/end timestamps and a current flag. This enables historically accurate reporting (e.g., revenue by the product category at the time of sale).

SELECT f.order_id, p.category
FROM fact_sales_line f
JOIN dim_product p
  ON f.product_key = p.product_key
WHERE p.is_current = TRUE;

Modeling Returns/Refunds and Discounts

Returns and refunds can be modeled as negative facts, separate fact tables, or as adjustments linked to the original sale line. Discount allocation must be defined (order-level prorated to lines vs line-level discounts) to ensure net sales reconcile with finance systems.

SELECT order_id,
       SUM(gross_sales_amount) AS gross,
       SUM(discount_amount) AS discounts,
       SUM(refund_amount) AS refunds,
       SUM(net_sales_amount) AS net
FROM fact_sales_line
GROUP BY 1;