Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Fact table + dimensions = star schema; optimizes analytical query patterns. **Fact Table**: Measures (amount, quantity); FKs to dimensions; large; grain defined by dimensions. Additive or semi-additive. **Star Schema**: Fact surrounded by dimension...
Architectural Logic: Fact table + dimensions = star schema; optimizes analytical query patterns. Fact Table: Measures (amount, quantity); FKs to dimensions; large; grain defined by dimensions. Additive or semi-additive. Star Schema: Fact surrounded by dimension tables; denormalized dimensions; simple structure. Example: fact_sales (sale_id, date_key, product_key, store_key, amount, qty); dim_date, dim_product, dim_store. Why: Aligns with business thinking (measures by dimensions); fewer joins; BI-friendly. Scalability: Partition fact by date; surrogate keys for dimensions. Best Practice: Define grain explicitly; design for incremental loads.
Red Flag: Fact table without defined grain—ambiguous. Pro-Move: State grain (e.g., one row per order line); reference additive vs semi-additive; surrogate keys.
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.