Twitter (X) Data Engineer Interview

Self-join follows table on follower-followee inversion. SELECT a.follower, a.followee FROM follows a JOIN follows b ON a.follower = b.followee AND a.followee = b.follower WHERE a.follower < a.followee (dedupe pairs). Discuss why the inequality matters for pair deduplication.

JOIN tweets to user_followers_at_post_ts. Bucket follower count (0-100, 100-1K, 1K-10K, 10K-100K, 100K+). Per bucket, compute avg(impressions / followers) and avg(likes / impressions). Discuss why follower count at post-time matters more than current follower count.

Self-join follows twice. SELECT a.follower, c.followee FROM follows a JOIN follows b ON a.followee = b.follower WHERE a.follower != b.followee AND NOT EXISTS (SELECT 1 FROM follows WHERE follower = a.follower AND followee = b.followee). Discuss why this won't scale to billions of edges; mention precomputed neighbor table or graph database for production.

Per tweet per hour: engagements_this_hour vs engagements_prior_hour. CASE WHEN current > prior * 5 AND prior >= 100 THEN 1 ELSE 0 AS is_viral. Discuss the threshold tuning trade-off (low threshold = noisy, high threshold = misses early viral tweets).

Find user pairs with suspicious behavioral correlation: posting the same content within 1 minute of each other more than N times in 24 hours. Self-join tweets on near- identical content_hash, abs(ts_diff) < 60 sec, GROUP BY user pair, HAVING COUNT > threshold. Discuss the follow-up signal layer: account age, IP clustering, device fingerprint similarity.

Per user, maintain a deque of recent post timestamps. On new post: trim deque to last 24 hours, if len > daily_limit reject, else append and accept. Discuss why deque (O(1) push/pop) vs list. Mention that production uses Redis sorted sets for distributed rate limiting.

from collections import deque
from datetime import datetime, timedelta

class TweetRateLimiter:
    def __init__(self, daily_limit: int = 2400):
        self.daily_limit = daily_limit
        self.window = timedelta(days=1)
        self.user_posts: dict[str, deque[datetime]] = {}

    def can_post(self, user_id: str, now: datetime) -> bool:
        posts = self.user_posts.setdefault(user_id, deque())
        cutoff = now - self.window
        while posts and posts[0] < cutoff:
            posts.popleft()
        if len(posts) >= self.daily_limit:
            return False
        posts.append(now)
        return True

Stream of tweets, output top-K hashtags per minute. Naive hash-map grows unbounded. Count-min sketch: fixed-size 2D array, hash each hashtag with K hash functions, increment cell, take min on read. Discuss space-vs-accuracy trade-off. Mention HyperLogLog for unique-tagger counting.

Per user request: fetch candidate tweets (from cache), pull features per (user, tweet) from feature store, batch inference call to ML model, return top-K. Discuss the budget: 30ms candidate fetch, 30ms feature lookup, 30ms inference, 10ms slack. Mention the cold-start fallback (popular tweets when user has no history).

Per user, maintain a bloom filter of tweets shown in last 24 hours. On candidate generation, filter out positives from bloom. Discuss false-positive rate (1% acceptable; user occasionally misses a fresh tweet) vs false-negative rate (0% required; user never sees same tweet twice). Trade-off: bloom size vs FP rate.

Two patterns. Fanout-on-write: when user A tweets, push the tweet_id into the inbox of every follower. Pro: read is O(1). Con: write amplification (Taylor Swift posts: 100M writes). Fanout-on-read: on user's timeline request, query tweets-from-follows from a cache. Pro: write is O(1). Con: read is expensive, cache key explosion. Hybrid: fanout-on-write for normal users, fanout-on-read for celebrities (defined as >1M followers). Discuss the threshold tuning, the eventual-consistency window, and the role of pre-aggregated “Home Latest” vs ML-ranked “Home For You” pipelines.

Tweet write
   -> Kafka (tweets topic, key=user_id)
   -> Heron stream job:
        if author.follower_count < 1M (normal user):
            FANOUT-ON-WRITE: push tweet_id to inbox of every follower
            (in Manhattan / Redis, sharded by follower_id)
        else (celebrity):
            FANOUT-ON-READ: tweet stays in author's outbox only
            on follower's timeline request, fetch celebrity outboxes
            (limited set, can be cached)

Timeline request
   -> Service merges:
       (a) follower's inbox (fanout-on-write portion)
       (b) celebrity outboxes for follows >1M (fanout-on-read portion)
   -> ML ranker scores combined candidate set
   -> top-K returned

Failure modes:
1. Heron worker crash: Kafka redelivers, idempotent inbox writes.
2. Celebrity inbox-fanout drift: rare; daily reconciliation job.
3. Cache eviction during traffic spike: degrade to direct Manhattan
   read with higher latency, no functional break.

Tweets -> Kafka -> Heron stream (extract hashtags and entities) -> sliding window count per hashtag per geo over 30-min window -> rank by velocity (current rate vs trailing baseline) -> emit top-K to Druid. Cover: spam suppression (exclude hashtags from accounts < 30 days old), per-geo localization, the “always trending” problem (popular topics like #Apple are always counts-high but not trending).

Tweet stream -> Heron (multi-stage classifier: rule- based filters first, then ML scoring) -> queue for human review or auto-action based on score. Cover: feature pipeline for the ML classifier (account age, posting velocity, content embedding similarity to known spam), label feedback loop (human reviewer decisions flow back as training data), audit log for appeals.

Four edge tables in a graph: follows (follower_id, followee_id, ts), mentions (tweet_id, mentioned_user_id, mention_position), replies (reply_tweet_id, parent_tweet_id), retweets (retweet_id, original_tweet_id, retweeter_id). Plus fact_tweet (tweet_id, author_id, content, ts) and dim_user. Discuss: why edges are separate tables vs a single edges table with edge_type column (separate is faster to query for type-specific traversal).

fact_tweet_engagement_hourly: one row per (tweet_id, hour_bucket) with impressions, likes, retweets, replies, bookmarks, profile_visits. Aggregated from raw event log by hourly Spark job. Discuss: 7-day rolling rollup for recent analytics, monthly rollup for older. Trade-off: storage cost vs query latency. Most queries hit the hourly rollup; raw is kept 30 days for ad-hoc.

X's 2023+ culture explicitly rewards eliminating work. Story should cover: the project or system you killed, why it was the right call (cost vs benefit), how you handled the affected stakeholders, the outcome 6 months later. Decision postmortem essential. Stories where you proactively proposed the deprecation land better than stories where it was assigned.

Post-2023 X teams are dramatically smaller than pre-Musk Twitter. A single data engineer often owns multiple pipelines that historically had dedicated teams. Frame stories about broader scope ownership, fewer specialists, more generalist judgment.

X serves a real-time global feed. Every system design answer should acknowledge global scale (600M users), real-time SLAs (sub-second timeline updates), and the cost-vs-latency trade-offs that follow. Vague answers about throughput don't survive.

Post-2023 X interviewers ask about cost optimization unprompted. Mentioning storage class transitions, compute right-sizing, or caching strategies that reduce egress is a positive signal. Over-engineered answers that ignore cost are a downgrade signal.

X's post-2023 culture rewards questioning whether a system needs to exist. Stories about killing a project, deprecating a service, or shipping a simpler version of an elaborate proposal score well. Stories about elaborate engineering excellence in service of an unclear business need score badly.