The short answer
ER / 3NF modeling focuses on capturing business entities and relationships without redundancy. It is the default for apps: customers, orders, products as separate normalized tables.
Dimensional modeling focuses on making analytical questions easy and fast: facts at a grain, dimensions for slicing. Some controlled redundancy (denormalized dims) is intentional.
ER (app):
customers -< orders -< order_lines >- products
many joins for "revenue by category last month"
Dimensional (warehouse):
fct_order_items surrounded by dim_date, dim_customer, dim_product
BI-friendly aggregates| ER / 3NF | Dimensional | |
|---|---|---|
| Goal | Write integrity, app screens | Analytics & BI |
| Redundancy | Avoided | Often accepted in dims/marts |
| Central idea | Entities & relationships | Facts, dimensions, grain |
| Typical home | OLTP DB | Warehouse / lakehouse gold |
Interview tip
> "ER for operational systems; dimensional for analytics. Same business, different optimization target."