Design a dimensional model for a transactional analytics use case. 1) Specify the core fact table, including the grain, keys, and measures, and outline 4–5 dimension tables. 2) If you need to track attribute changes over time, describe how you would implement Slowly Changing Dimension Type 2 (SCD 2). 3) Describe the relationships between two example tables (one-to-many vs many-to-many) and how these cardinalities affect your schema. 4) Show how you would handle a many-to-many relationship (e.g., via a bridge table or an additional fact table) and discuss trade-offs. 5) Propose schema evolutions you would consider for new requirements (add a column, add a dimension, normalize a table, add a fact) and justify scalability impacts, aiming to keep a single authoritative fact table when possible.

This question evaluates proficiency in dimensional data modeling and scalable analytics architecture, covering fact and dimension design, historical attribute tracking, cardinality, and many-to-many relationships for high-volume transactional event streams.

How do I approach System Design interview questions?

System Design questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master system design interviews.

What difficulty level is this interview question?

This is a medium difficulty System Design question, commonly asked during Onsite rounds at Meta.

What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Meta during technical interviews.

Design a scalable dimensional model | Meta Interview Question

Design a Dimensional Model for Transactional Analytics (Concrete Example Included)

You are building a star-schema in a cloud data warehouse for near real-time transactional analytics. Assume a high-volume ads platform with three key event types: impressions (delivery), clicks, and conversions (downstream actions). Stakeholders need accurate, drillable reporting by user, device, campaign, and placement.

Tasks

Core Fact Table
- Define the core fact table for delivery events.
- Specify the grain, surrogate/natural keys, degenerate keys, and measures.
- Outline 4–5 key dimension tables (names, purpose, and example attributes).
Slowly Changing Dimensions (SCD Type 2)
- Describe how you would track attribute changes over time (e.g., a user’s country, a campaign’s status) using SCD Type 2.
Relationships and Cardinalities
- Pick two example tables and describe their relationship (one-to-many vs. many-to-many).
- Explain how these cardinalities impact schema design and query behavior.
Handling Many-to-Many
- Show how you would handle a many-to-many relationship (e.g., impression-to-conversion attribution) using a bridge table or an additional fact table.
- Discuss trade-offs in performance, storage, and correctness.
Schema Evolution
- Propose evolutions you would consider for new requirements (e.g., add a column, add a dimension, normalize a table, add a fact).
- Justify scalability impacts and how you would preserve a single authoritative fact table when possible.

Design a Dimensional Model for Transactional Analytics (Concrete Example Included)

Tasks

Core Fact Table

Define the core fact table for delivery events.
Specify the grain, surrogate/natural keys, degenerate keys, and measures.
Outline 4–5 key dimension tables (names, purpose, and example attributes).

Slowly Changing Dimensions (SCD Type 2)

Describe how you would track attribute changes over time (e.g., a user’s country, a campaign’s status) using SCD Type 2.

Relationships and Cardinalities

Pick two example tables and describe their relationship (one-to-many vs. many-to-many).
Explain how these cardinalities impact schema design and query behavior.

Handling Many-to-Many

Show how you would handle a many-to-many relationship (e.g., impression-to-conversion attribution) using a bridge table or an additional fact table.
Discuss trade-offs in performance, storage, and correctness.

Schema Evolution

Propose evolutions you would consider for new requirements (e.g., add a column, add a dimension, normalize a table, add a fact).
Justify scalability impacts and how you would preserve a single authoritative fact table when possible.

Design a scalable dimensional model

Quick Overview

Design a Dimensional Model for Transactional Analytics (Concrete Example Included)

Tasks

Solution

Comments (0)

Design a scalable dimensional model

Quick Overview

Design a Dimensional Model for Transactional Analytics (Concrete Example Included)

Tasks

Solution

Comments (0)