Meta·Software Engineer·Onsite - System Design / Architecture
- Design a dimensional model for a transactional analytics use case. Define the core fact table including its grain, keys, and measures, and outline four or five dimension tables.
- If you need to track how dimension attributes change over time, how would you implement SCD Type 2?
- Describe the relationship between two example tables, specifically contrasting one-to-many and many-to-many cardinalities, and explain how each affects your schema design.
- How would you handle a many-to-many relationship in your schema, for example using a bridge table or an additional fact table, and what are the trade-offs between those approaches?
- How would you evolve your schema to accommodate new requirements such as adding a column, introducing a new dimension, normalizing a table, or adding a new fact, while keeping a single authoritative fact table and maintaining scalability?
“Started okay, picked an e-commerce transaction as my example and defined grain at the order-line level.”