Star & Snowflake Schema

A star schema is the standard shape for analytics/data-warehouse tables: one central fact table (every row is a measurable event — "this sale happened, for this amount, at this time"), surrounded by dimension tables it references via foreign keys (customer, product, date, store — the "who/what/when/where" you'd filter or group by).

def is_valid_star_schema(fact_fks, dimension_tables):
    return set(fact_fks).issubset(set(dimension_tables))

fact_sales_fks = ["customer", "product", "date"]
dimensions = ["customer", "product", "date", "store"]
print(is_valid_star_schema(fact_sales_fks, dimensions))   # True

A snowflake schema is the same idea, but with dimension tables further normalized (e.g. product splitting off into its own category and department tables) — more normalized, less redundant, but requires more joins to query.

Every foreign key in the fact table should point to a REAL row in its dimension — a fact row referencing a product_id that doesn't exist in the products dimension is an orphaned row, usually a sign of a broken ETL pipeline (a product got deleted from the dimension, but historical fact rows still reference it):

def find_orphaned_fact_rows(fact_rows, dimension_keys):
    orphans = []
    for i, row in enumerate(fact_rows):
        for dim, valid_keys in dimension_keys.items():
            if row.get(dim) not in valid_keys:
                orphans.append((i, dim))
    return orphans

Real data warehouses run exactly this kind of check as part of their data-quality pipeline — catching orphaned fact rows before they corrupt a dashboard or report downstream.