Star schema vs snowflake schema

        dim_date      dim_customer
              \            /
               fact_sales
              /            \
        dim_product     dim_store

create table dim_product (
  product_key    bigint primary key,   -- surrogate key
  product_id     string,               -- natural key
  product_name   string,
  brand_name     string,               -- denormalized
  category_name  string,               -- denormalized
  department_name string               -- denormalized
);

create table fact_sales (
  sale_id        bigint,
  date_key       int    references dim_date,
  customer_key   bigint references dim_customer,
  product_key    bigint references dim_product,
  store_key      bigint references dim_store,
  quantity       int,
  net_amount     decimal(18,2)
);

Query: revenue by department — one join.

select p.department_name, sum(f.net_amount) as revenue
from fact_sales f
join dim_product p using (product_key)
group by 1;

create table dim_department (department_key int primary key, department_name string);
create table dim_category   (category_key int primary key, category_name string,
                             department_key int references dim_department);
create table dim_brand      (brand_key int primary key, brand_name string);
create table dim_product (
  product_key   bigint primary key,
  product_name  string,
  brand_key     int references dim_brand,
  category_key  int references dim_category
);

Same question — three joins:

select d.department_name, sum(f.net_amount) as revenue
from fact_sales f
join dim_product    p using (product_key)
join dim_category   c using (category_key)
join dim_department d using (department_key)
group by 1;

Multiply that by every dashboard tile and every self-service analyst who has to remember the join path. That's the real cost of snowflaking — human, not just CPU.

Default to star. Snowflake a dimension only when you have a specific reason: the sub-dimension is shared across several dimensions, the dimension is so large that duplication actually shows up on the bill, or a governance rule demands one master table. "It feels cleaner" is not a reason — dimensional models optimize for readers, not for third normal form.

• Grain. "What does one row of this fact table represent?" Answer it in one sentence or your model is wrong.
• Surrogate vs natural keys. Surrogate keys are what make SCD Type 2 possible.
• Conformed dimensions. One dim_customer shared by sales, support and billing facts.
• Fact types. Transaction, periodic snapshot, accumulating snapshot.