We're sorry but this app doesn't work properly without JavaScript enabled. Please enable it to continue.

This lesson's interactive features are locked, please to keep using them

Building a Data Model

Now let's bring everything together:

  1. Create dimension tables – extract unique entities
  2. Create the fact table – keep only keys, metrics, timestamps
  3. Validate grain – check for duplicates
  4. Aggregate – answer business questions by joining facts to dimensions

Click to play video

Say we have this flat dataset: everything in one table.

raw = pd.DataFrame(
    {
        "order_id": ["ORD-7K2M", "ORD-9P4X", "ORD-3D8V", "ORD-6N1R", "ORD-5C9J"],
        "customer_id": ["CUS-4821", "CUS-7316", "CUS-4821", "CUS-2059", "CUS-7316"],
        "customer_name": [
            "Alice Chen",
            "Bob Martinez",
            "Alice Chen",
            "Carol Singh",
            "Bob Martinez",
        ],
        "region": ["West", "East", "West", "West", "East"],
        "product_id": ["CHL-482", "TST-731", "OVN-205", "CHL-482", "TST-731"],
        "product_name": [
            "ChillVault Mini",
            "ToastForge Pro",
            "CrispWave Oven",
            "ChillVault Mini",
            "ToastForge Pro",
        ],
        "category": ["Refrigeration", "Toasters", "Ovens", "Refrigeration", "Toasters"],
        "order_date": [
            "2024-01-15",
            "2024-01-15",
            "2024-01-16",
            "2024-01-16",
            "2024-01-17",
        ],
        "revenue": [799.00, 129.00, 349.00, 799.00, 129.00],
    }
)

Step 1: Build Dimension Tables

 
dim_customers = raw[["customer_id", "customer_name", "region"]].drop_duplicates()
dim_products = raw[["product_id", "product_name", "category"]].drop_duplicates()

Step 2: Build the Fact Table

 
fact_orders = raw[["order_id", "customer_id", "product_id", "order_date", "revenue"]]

Step 3: Validate Grain

 
dupes = fact_orders.duplicated(subset=["order_id"])
assert dupes.sum() == 0, f"Grain violation: {dupes.sum()} duplicates"

Step 4: Aggregate

Join facts to dimensions, then aggregate:

analysis = fact_orders.merge(dim_customers, on="customer_id")

revenue_by_region = (
    analysis.groupby("region")
    .agg(
        total_revenue=("revenue", "sum"),
        order_count=("order_id", "count"),
        unique_customers=("customer_id", "nunique"),
    )
    .reset_index()
)

Assignment

SnackStack's telemetry team handed you a flat export: one row per device reading, with each device's details repeated on every reading. You want a clean device dimension: one row per device.

Complete the build_device_dimension function. It accepts a flat raw export and returns the device dimension table: one row per device with its device_id, device_name, and region.