The Celebrity Problem

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5
Asked in
Tyro

Problem

We are building a social platform where users can post content, follow other users, and see a personalized timeline of posts from people they follow. The data science team needs to analyze engagement patterns, and the product team wants to support timeline queries at scale. Design the data model.

One post. A million notifications. Something has to give.

Worked solution and explanation

Why this problem exists in real interviews

The celebrity problem probes whether a candidate understands skew: one user with 100M followers breaks every naive fanout-on-write design. The signal is whether the candidate proposes a hybrid delivery strategy (push for regular users, pull for celebrities) and models the decision as data rather than application logic. This is an art-over-science design because the cutoff is a tunable threshold, not a mathematical optimum.

Break down the requirements

Step 1: Tag users as celebrities

dim_users.is_celebrity is a boolean derived from follower_count. The threshold is a policy decision; storing it as data lets the pipeline flip users as they grow.

Step 2: Self-referential follows

fact_follows with (follower_sk, followed_sk) as the composite key is the graph. Queries like "pull the latest posts from the accounts I follow" live here.

Step 3: Timeline events are delivery records

fact_timeline_events is one row per (viewer, post) delivery, with delivery_path recording whether the post was pushed at write time or pulled at read time. This is what makes the hybrid observable.

Step 4: Engagement is a separate fact

fact_engagements records likes, reposts, and replies. Keeping engagement separate from timeline delivery lets engagement rates be computed per delivery path.

Step 5: Keep fact_posts atomic

One row per post. The author is the only dimension FK because all other analytics (delivery, engagement) happen through separate facts.

The solution

Below is one defensible model. The is_celebrity flag and the delivery_path column are the anchor: together they make the hybrid fanout strategy a first-class, measurable property of the system.

`is_celebrity` drives the fanout decision; `delivery_path` on `fact_timeline_events` captures which path was taken per post
  • dim_users: user_sk BIGINT PK, user_id TEXT, handle TEXT, follower_count BIGINT, is_celebrity BOOLEAN
  • fact_posts: post_id BIGINT PK, author_sk BIGINT FK, created_at TIMESTAMP, body TEXT
  • fact_follows: follower_sk BIGINT FK, followed_sk BIGINT FK, followed_at TIMESTAMP
  • fact_timeline_events: timeline_event_id BIGINT PK, user_sk BIGINT FK, post_id BIGINT FK, delivered_at TIMESTAMP, delivery_path TEXT
  • fact_engagements: engagement_id BIGINT PK, user_sk BIGINT FK, post_id BIGINT FK, engagement_type TEXT, engaged_at TIMESTAMP
  • dim_users references fact_posts
  • dim_users references fact_follows
  • dim_users references fact_timeline_events
  • dim_users references fact_engagements
  • fact_posts references fact_timeline_events
  • fact_posts references fact_engagements

The analysis pattern

Delivery path engagement lift
SELECT
    te.delivery_path,
    COUNT(*) AS deliveries,
    COUNT(DISTINCT te.user_sk) AS unique_viewers,
    COUNT(e.engagement_id) AS engagements,
    COUNT(e.engagement_id)::NUMERIC / NULLIF(COUNT(*), 0) AS engagement_rate
FROM fact_timeline_events te
LEFT JOIN fact_engagements e
    ON e.post_id = te.post_id
   AND e.user_sk = te.user_sk
   AND e.engaged_at BETWEEN te.delivered_at AND te.delivered_at + INTERVAL '24 hours'
WHERE te.delivered_at >= NOW() - INTERVAL '7 days'
GROUP BY te.delivery_path
ORDER BY engagement_rate DESC

Trade-offs and alternatives

Hybrid push-pull with `is_celebrity` flag
Handles skew, measurable per-path engagement, tunable threshold. Cost: dual delivery paths in the write pipeline and a reconciliation job to heal timeline gaps.
Pure fanout-on-read for everyone
Single simple write path, no reconciliation. Cost: every timeline load pays the join cost, and hot feeds become the bottleneck regardless of user size.

Common follow-up questions