What is the optimal methodology for building enterprise data models (Star Schema vs Snowflake Schema)?

Use a star schema for most enterprise analytics workloads, reserving snowflake schemas for highly normalized reference data or when query performance is dominated by dimension joins.

Decision checklist
1. Identify grain of the fact table. If the grain is transaction‑level and you need fast aggregations, choose star.
2. Count dimension attributes. > 20 attributes per dimension → consider snowflake to avoid sparse wide tables.
3. Estimate join cost. If estimated join rows > 10 million per query (based on DBMS EXPLAIN), star usually wins.
4. Assess maintenance overhead. Snowflake adds 1–2 extra ETL steps per dimension for hierarchy loading.

Comparison table

| Aspect | Star Schema | Snowflake Schema |
|-----------------------|------------------------------------------|------------------------------------------|
| Query latency | Low (single join to fact) | Higher (multiple joins) |
| Storage efficiency | Redundant dimension data (≈10‑20 % more) | Normalized, ~30 % less space |
| ETL complexity | Simple – one load per dimension | Moderate – load hierarchy tables first |
| Flexibility for hierarchies | Limited (flat) | Strong (multi‑level hierarchies) |

SQL snippets

-- Star fact table
CREATE TABLE dbo.SalesFact (
    SalesKey BIGINT PRIMARY KEY,
    DateKey INT NOT NULL,
    ProductKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    SalesAmount DECIMAL(18,2) NOT NULL,
    Quantity INT NOT NULL
);

-- Snowflake dimension hierarchy
CREATE TABLE dbo.ProductCategory (
    CategoryKey INT PRIMARY KEY,
    CategoryName NVARCHAR(100) NOT NULL
);
CREATE TABLE dbo.ProductSubCategory (
    SubCategoryKey INT PRIMARY KEY,
    CategoryKey INT NOT NULL REFERENCES dbo.ProductCategory(CategoryKey),
    SubCategoryName NVARCHAR(100) NOT NULL
);

Gotcha: When the fact table grain is too fine (e.g., per‑click events), the star can explode to billions of rows; pre‑aggregate to a daily grain or switch to a snowflake to keep join depth manageable.