Data Engineering interview question · Most asked
What is a star schema, and why is it used in data warehouses?
Summary
A star schema has central fact tables (measurable events at a declared grain, such as order lines) surrounded by dimension tables (customers, products, dates) joined many-to-one. It makes queries simple and fast and prevents join fan-out errors.
Longer Answer
Facts hold numeric measures and foreign keys; dimensions hold descriptive attributes, denormalised for easy filtering and grouping. The approach comes from Ralph Kimball's dimensional modelling. Wide "one big table" models are an alternative in columnar warehouses, provided their grain is explicit.
Key Points
- Facts at a declared grain, dimensions many-to-one.
- Simple, fast, correct queries.
- Kimball dimensional modelling.
- OBT is fine if the grain is explicit.
Explanation
What interviewers look for
Correct definitions of facts, dimensions and grain.
More most-asked Data Engineering questions
- Batch or streaming: how do you decide?
- What is the difference between ETL and ELT, and when would you choose each?
- What is change data capture (CDC), and why is log-based CDC preferred over polling?
- What does it mean for a data pipeline to be idempotent, and how do you achieve it?
- How do you implement incremental extraction, and what can go wrong?
- How do you deduplicate events, and what does 'exactly-once' really mean?
- What is the grain of a table, and what is join fan-out?
- Explain slowly changing dimensions, especially Type 1 and Type 2.