E-commerce Analytics Data Warehouse (Star Schema)

Overview

This project designs an analytics warehouse using a star schema centered on FactSales with dimensions DimProduct, DimCustomer, DimDate, and DimLocation. It supports monthly revenue per region, returning vs new customers, daily activity, and most profitable products.

Mermaid Diagram

erDiagram
  DimDate ||--o{ FactSales : date_key
  DimProduct ||--o{ FactSales : product_key
  DimCustomer ||--o{ FactSales : customer_key
  DimLocation ||--o{ FactSales : location_key
  DimDate {
    int date_key
    date date
    string day_name
    int week
    int month
    string month_name
    int quarter
    int year
    int iso_week
    bool is_weekend
  }
  DimProduct {
    int product_key
    string product_id
    string name
    string category
    string subcategory
    string brand
    string sku
    timestamp effective_from
    timestamp effective_to
    bool is_current
  }
  DimCustomer {
    int customer_key
    string customer_id
    string first_name
    string last_name
    string email_hash
    int signup_date_key
    string segment
    timestamp effective_from
    timestamp effective_to
    bool is_current
  }
  DimLocation {
    int location_key
    string country
    string region
    string state
    string city
    string postal_code
  }
  FactSales {
    bigint fact_sales_id
    int date_key
    int product_key
    int customer_key
    int location_key
    string order_id
    string line_item_id
    numeric quantity
    numeric unit_price
    numeric discount_amount
    numeric tax_amount
    numeric revenue
    numeric cost
    numeric profit
    numeric net_revenue
    numeric gross_margin
  }

Loading

Table Explanations

• DimDate: one row per calendar date, supports common time attributes.
• DimProduct: SCD Type 2 dimension tracking changes to product attributes over time.
• DimCustomer: SCD Type 2 dimension tracking changes to customer attributes and segment.
• DimLocation: standard geo dimension for country/region/state/city/postal_code.
• FactSales: line-item grain per order and transaction date with foreign keys to dimensions and sales measures.

Relationships

• FactSales.date_key → DimDate.date_key
• FactSales.product_key → DimProduct.product_key
• FactSales.customer_key → DimCustomer.customer_key
• FactSales.location_key → DimLocation.location_key

Setup

• PostgreSQL
  • Run schema/ddl_postgres.sql against your database.
  • Run schema/staging_postgres.sql to create staging tables.
  • Load sample data: run data/staging_seed.sql.
  • Set DW_PG_DSN and run etl/etl_example.py after loading staging tables stg_orders, stg_order_items, stg_products, stg_customers, stg_locations.
• BigQuery
  • Run schema/ddl_bigquery.sql in a dataset of your choice.
  • Use etl/etl_bigquery.sql for a MERGE-based ETL example that populates dimensions and loads FactSales with transaction_date.
  • Ensure staging tables stg_* exist and are populated.

OLAP Queries

• See queries/olap_examples.sql for 10 queries covering ROLLUP, CUBE, GROUPING SETS, window functions, ranking, contribution analysis, basket size, and time deltas.

Notes

• Dimensions use surrogate keys; FactSales includes degenerate identifiers for drill-through.
• SCD2 managed for product and customer with effective_from, effective_to, is_current.
• Requirements: see requirements.txt.