Reviewed by Aditya Kumar Ā· Last reviewed 2026-03-24
**Architectural Logic**: ER and dimensional serve different purposesātransactional integrity vs analytical performance. **ER**: Normalized; entities and relationships; OLTP; minimizes redundancy; supports transactional integrity. Many tables; complex joins. **Dimensional**:...
Architectural Logic: ER and dimensional serve different purposesātransactional integrity vs analytical performance. ER: Normalized; entities and relationships; OLTP; minimizes redundancy; supports transactional integrity. Many tables; complex joins. Dimensional: Star/snowflake; facts and dimensions; OLAP; optimized for analytics. Denormalized; fewer joins; intuitive for business. Why Both: ER for operational systems; dimensional for warehouses. Scalability: ER scales for writes; dimensional for reads. Cost: Dimensional reduces compute per query (fewer joins); ER reduces update anomalies. Best Practice: Model operationally with ER; transform to dimensional for analytics; conformed dimensions across marts.
Red Flag: Using ER for analytics warehouseāwrong tool. Pro-Move: State purpose (OLTP vs OLAP); reference conformed dimensions; mention transform layer (ETL) between them.
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.