Explain a star schema and why it is used in data warehouses.

What the interviewer wants to hear

• Central fact table with measures and foreign keys
• Surrounding dimension tables with descriptive attributes
• Grain of the fact table must be clear
• Simple joins and fast aggregations for BI

Model answer

A star schema has one central fact table connected to several dimension tables, which looks like a star. The fact table stores events at a defined grain, for example one row per order line, with numeric measures like quantity and amount, plus foreign keys to dimensions. Dimension tables, such as customer, product, store and date, hold descriptive attributes used for filtering and grouping. Dimensions are usually denormalised, so product category and brand sit in the same table instead of separate lookup tables. It is popular because it is easy for analysts to understand, queries need only simple joins from fact to dimensions, and BI tools work well with it. The most important design step is choosing the grain of the fact table, because mixing grains causes double counting.

More easy Data Engineer questions

• Describe a data pipeline you built or would build, from source to dashboard.
• What is the difference between an OLTP database and an OLAP data warehouse?
• What data quality checks would you add to a pipeline?
• Why use an orchestration tool like Airflow instead of cron jobs?
• What is a slowly changing dimension, and how do SCD Types 1, 2 and 3 differ?
• Why would you store analytics data as Parquet rather than CSV or JSON?
• What is change data capture (CDC), and why do data teams use it?
• Explain Kafka topics, partitions, offsets and consumer groups to a new teammate.
• What does dbt do in a modern data stack, and what are its main features?