Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Fact tables**: Measures (quantity, amount); granular events; append-only; FKs to dimensions; high row count. **Dimension tables**: Descriptors (product name, customer); lower row count; used for grouping/filtering. **Why separate**: Facts grow linearly; dimensions change...
Fact tables: Measures (quantity, amount); granular events; append-only; FKs to dimensions; high row count. Dimension tables: Descriptors (product name, customer); lower row count; used for grouping/filtering. Why separate: Facts grow linearly; dimensions change slowly. Star schema optimizes analytics—filter/group by dimension, aggregate facts. Scalability: Facts partitioned by date; dimensions often fit in memory for broadcast. Cost: Denormalized facts = storage bloat; normalized dimensions = join cost.
Red Flag: Storing dimension attributes in fact table (redundancy). Pro-Move: 'We kept fact at transaction grain, conformed dimensions across 5 fact tables—enabled single dashboard over sales, returns, and inventory.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.