Star Schema and Data Warehouse Design

Data-warehouse questions begin with one distinction interviewers expect you to nail: OLTP vs OLAP.

• OLTP (transactional): many small reads/writes, highly normalized for integrity. Powers the app.
• OLAP (analytical): few large aggregating reads over history, deliberately denormalized for speed. Powers reporting and dashboards.

Star schemas are an OLAP design. The whole point is fast analytical queries, accepting redundancy in exchange.

A star schema splits data into two kinds of tables:

• Fact table: the measurable events or transactions (a sale, a click). Holds numeric measures and foreign keys to dimensions.
• Dimension tables: the descriptive context you slice by (date, product, customer, store).

The fact sits in the center; dimensions surround it like points of a star, hence the name.