How to perform a star schema join in Python

Denormalize mock fact and dimension tables by building lookup dicts and enriching each sales fact with customer, product, and date attributes.

Medium Python 3.9+ Aug 9, 2026 Data pipelines & processing 12 views 0 copies

star-schema data-joins dimensional-modeling lookup-tables

Python code

56 lines
Python 3.9+
Run it 
from datetime import date

# Mock dimension tables
customers = [
    {"customer_id": 1, "name": "Alice", "city": "New York"},
    {"customer_id": 2, "name": "Bob", "city": "Los Angeles"},
    {"customer_id": 3, "name": "Carol", "city": "Chicago"},
]

products = [
    {"product_id": 101, "name": "Laptop", "category": "Electronics"},
    {"product_id": 102, "name": "Mouse", "category": "Electronics"},
    {"product_id": 103, "name": "Desk", "category": "Furniture"},
]

dates = [
    {"date_id": 20240101, "date": date(2024, 1, 1), "year": 2024, "month": 1},
    {"date_id": 20240102, "date": date(2024, 1, 2), "year": 2024, "month": 1},
    {"date_id": 20240103, "date": date(2024, 1, 3), "year": 2024, "month": 1},
]

# Mock fact table with foreign keys
sales_facts = [
    {"sale_id": 1, "customer_id": 1, "product_id": 101, "date_id": 20240101, "amount": 1200.00},
    {"sale_id": 2, "customer_id": 2, "product_id": 102, "date_id": 20240101, "amount": 25.50},
    {"sale_id": 3, "customer_id": 1, "product_id": 103, "date_id": 20240102, "amount": 350.00},
    {"sale_id": 4, "customer_id": 3, "product_id": 101, "date_id": 20240102, "amount": 1200.00},
    {"sale_id": 5, "customer_id": 2, "product_id": 102, "date_id": 20240103, "amount": 25.50},
]

# Helper to build lookup dicts from dimension tables
def build_lookup(dimension, key_field):
    return {row[key_field]: row for row in dimension}

customer_lookup = build_lookup(customers, "customer_id")
product_lookup = build_lookup(products, "product_id")
date_lookup = build_lookup(dates, "date_id")

# Join fact table with all dimensions
enriched_facts = []
for fact in sales_facts:
    enriched = dict(fact)
    enriched["customer_name"] = customer_lookup[fact["customer_id"]]["name"]
    enriched["customer_city"] = customer_lookup[fact["customer_id"]]["city"]
    enriched["product_name"] = product_lookup[fact["product_id"]]["name"]
    enriched["product_category"] = product_lookup[fact["product_id"]]["category"]
    enriched["sale_date"] = date_lookup[fact["date_id"]]["date"].isoformat()
    enriched["sale_year"] = date_lookup[fact["date_id"]]["year"]
    enriched_facts.append(enriched)

# Display the star schema join result
if __name__ == "__main__":
    for row in enriched_facts:
        print(f"Sale {row['sale_id']}: {row['customer_name']} ({row['customer_city']}) "
              f"bought {row['product_name']} [{row['product_category']}] "
              f"on {row['sale_date']} for ${row['amount']:.2f}")

Output

stdout 
Sale 1: Alice (New York) bought Laptop [Electronics] on 2024-01-01 for $1200.00
Sale 2: Bob (Los Angeles) bought Mouse [Electronics] on 2024-01-01 for $25.50
Sale 3: Alice (New York) bought Desk [Furniture] on 2024-01-02 for $350.00
Sale 4: Carol (Chicago) bought Laptop [Electronics] on 2024-01-02 for $1200.00
Sale 5: Bob (Los Angeles) bought Mouse [Electronics] on 2024-01-03 for $25.50

How it works

The code models a classic star schema with fact and dimension tables as lists of dictionaries. A build_lookup helper converts each dimension list into a dictionary keyed by its primary key, enabling O(1) lookups. Each fact row is copied and enriched by pulling attribute values from the lookup dicts, simulating a SQL JOIN without a database. The date dimension is stored as date objects and converted to ISO strings for clean output. This pattern is a lightweight way to prototype analytics queries before moving to a real warehouse.

Common mistakes

  • Forgetting to copy the fact dict with `dict(fact)` before enriching, which mutates the original fact table.
  • Assuming every foreign key exists in the dimension lookup, which raises a KeyError for orphaned rows.
  • Storing dates as strings instead of `date` objects, making date arithmetic and filtering harder.
  • Building lookup dicts inside the loop, turning O(1) lookups into O(n²) performance.

Variations

  1. Use `pandas.merge` with `how='left'` to perform the same join in a single call on DataFrame columns.
  2. Wrap the join in a generator expression to stream enriched facts lazily for large fact tables.

Real-world use cases

  • Prototyping an analytics dashboard that aggregates sales by region and category before building production SQL.
  • Enriching event logs from a message queue with user and product metadata for a recommendation engine.
  • Creating training features for an ML pipeline by joining fact tables with customer and item dimensions.

Sponsored

Run this sample

Open the browser IDE to tweak the example and see results without installing anything.

Open editor

More from Data pipelines & processing

Keep learning

Related tutorials and quizzes for this topic.

Tutorials

Quizzes