How do you choose a data model?

Direct answer

What a strong answer covers

How it works

The model is chosen by who reads it and how. Analysts running ad hoc queries and BI tools want a star schema: wide dimension tables and narrow fact tables at a stated grain, one row per order line or per session. Machine learning features want denormalised, point-in-time correct tables. Operational lookups want a key-value shape. All of them derive from one modelled source of truth where each fact is stored once with its history, so a definition change propagates instead of diverging. Update patterns decide slowly changing dimension handling, and query latency decides how much is precomputed.

Example

A retail warehouse keeps fct_order_lines at one row per line with keys to dim_customer, dim_product, and dim_date; dim_customer is a type 2 dimension so a report for last year shows the segment the customer had at the time. The recommendation team reads a denormalised customer_features table rebuilt nightly from those same models, and both agree on revenue because they share the fact table.

Common mistakes

• Mixing grains in one fact table so sums double count.
• Overwriting dimension attributes and rewriting history in every past report.
• Building a separate model per dashboard until no two numbers match.

A useful answer structure

Answer from your own work.

Do not memorise a script: use only examples you can explain honestly in follow-up questions.