Data Engineer Interview Question : Designing a High‑Level OLAP Data Model for Social Media Emoji Reactions

Imagine sitting in a tough data‑engineering interview and the interviewer says:

“You’re building a social media platform. Users post photos, others react with emojis… how would you design a data model that supports trend analysis, historical changes, and complex OLAP queries?”

If your mind jumps straight to “let’s just add a likes column on the post table,” you’re already on the wrong side of the screen. 😬

In this article, I’ll walk you through a clean, OLAP‑friendly data model that not only tracks emoji reactions per post, but also lets you answer questions like:

“What were the top 3 emojis used by users from California on influencer posts over the last 7 days?”

By the end, you’ll be able to whiteboard this confidently in any interview and sound like you’ve shipped this at scale. 🚀

2. 🎯 Problem Statement

Social media platforms need more than just “likes”; they need analytics over:

• Who reacted (user context: city, region, follower count)
• What they reacted to (posts, creators, content type)
• When they reacted (timestamps, day‑level trends)
• What changed over time (user relocates, influencer status changes, emojis evolve per region)

If you naively model this in a transactional fashion (e.g., posts.likes_count, posts.love_count), you’ll quickly hit roadblocks when someone asks:

• “Show me the top emojis by region every day.”
• “What if a user moves from New York to California?”
• “How many distinct posts did California users react to with ❤️ yesterday?”

That’s why we need a dimensional, OLAP‑ready data warehouse schema.

3. 💡 Concept Explanation: OLAP‑Friendly Data Model

3.1 Star Schema mindset

We’ll design a star schema centered around a fact table of “emoji reactions”.

• Fact table = “something that happened” (a user reacting with an emoji).
• Dimension tables = “who, what, where, when, why” (user, post, emoji, time, geography).

This is exactly how big data warehouses like Snowflake, Redshift, or BigQuery model engagement metrics.

3.2 Core entities

From the problem, we can extract:

• Users — who reacts and who posts.
• Posts — photos and content.
• Reactions — emoji‑based actions (like, love, haha, angry, etc.).
• Time — for daily trends.
• Geography — especially for “users from California”.
• Influencer / power status — to capture “users with >180k followers”.

3.3 Granularity of the fact table

For OLAP flexibility, our granularity is one reaction event per user‑per‑emoji‑per‑post‑per‑timestamp.

In other words:

• Each row = one emoji reaction by one user on one post at a specific time.
• This is a transaction‑fact table, ideal for counting, trending, and slicing by dimensions.

If the same user changes their reaction (e.g., switches from 😂 to ❤️), we treat that as two separate events with timestamps:

• first row: reaction = 😂, created_at = t1
• second row: reaction = ❤️, created_at = t2

This way, we can:

• Track temporal changes (reaction history)
• Support trend analysis (e.g., “from 2024 to 2025, 😂 usage dropped 30%”)

3.4 Handling historical changes

We also need to handle:

• User location changes (e.g., user moves from New York → California)
• User status changes (e.g., follower count crosses 180k → influencer)

For dimensions like dim_user, we can use Slowly Changing Dimension (SCD) Type 2 (optional depending on complexity):

• Each “state” of a user gets a separate row with valid_from and valid_to.
• When a user moves city or gains followers, we close the old row and open a new one.

This lets us correctly attribute a reaction made in June 2025 to the user’s location and status at that time, not today.

4. 🧪 Practical Example: High‑Level Data Model

Let’s design the schema in a star‑like style.

4.1 Dimension tables

dim_user

Context: who reacted, who posted, where they are, and how influential they are.

Analogy: Think of dim_user as a user profile card that evolves over time. Each row is a “snapshot” of who the user was at that point.

dim_post

Context: what the reaction is about.

Storing influencer status here lets us filter “influencer posts” without joining to user every time.

dim_emoji

Context: the type of reaction.

dim_time

Context: when the reaction happened.

This lets us slice “last 7 days”, “Monday vs Sunday”, etc.

4.2 Fact table:

fact_emoji_reaction

This is the OLAP heart of our model.

Key points:

• One row = one emoji reaction event.
• If a user updates their reaction (😂 → ❤️), we may log:
• first row: reaction_type = 'new'
• second row: reaction_type = 'update'
• If the user deletes the reaction, we might either:
• add a is_deleted = 1 row, or
• soft‑delete in a transactional layer and only load active reactions into this fact table.

4.3 Relationships (foreign keys)

• fact_emoji_reaction.user_sk → FK to dim_user.user_sk
• fact_emoji_reaction.post_sk → FK to dim_post.post_sk
• fact_emoji_reaction.emoji_sk → FK to dim_emoji.emoji_sk
• fact_emoji_reaction.time_sk → FK to dim_time.time_sk

In diagram‑terms:

                         fact_emoji_reaction
                       ┌──────────────────────┐
                       │ user_sk   ──→ dim_user
                       │ post_sk   ──→ dim_post
                       │ emoji_sk  ──→ dim_emoji
                       │ time_sk   ──→ dim_time
                       └──────────────────────┘

That’s it for the star schema. Every row in the fact table rolls up to its dimension via surrogate keys.

🧮 SQL for the interview query

Question:

“For each day in the last 7 days, list the top 3 emojis used by users from California on posts created by influencers (users with >180k followers). Show emoji, total reactions and distinct post count.”

WITH ranked_emojis AS (
    SELECT
        t.date,
        e.emoji_name,
        -- total reactions in this group
        COUNT(*) AS total_reactions,
        -- distinct posts reacted to
        COUNT(DISTINCT f.post_sk) AS distinct_post_count,
        -- rank per day
        ROW_NUMBER() OVER (
            PARTITION BY t.date
            ORDER BY COUNT(*) DESC
        ) AS rank_in_day
    FROM
        fact_emoji_reaction f
        JOIN dim_user u ON f.user_sk = u.user_sk
        JOIN dim_post p ON f.post_sk = p.post_sk
        JOIN dim_emoji e ON f.emoji_sk = e.emoji_sk
        JOIN dim_time t ON f.time_sk = t.time_sk
    WHERE
        -- 1. Users from California
        u.state = 'California'
        -- 2. Posts created by influencers (follower_count > 180000)
        AND p.is_influencer_post = TRUE
        -- 3. Last 7 days
        AND t.date BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE
    GROUP BY
        t.date,
        e.emoji_name
)
SELECT
    date,
    emoji_name,
    total_reactions,
    distinct_post_count
FROM
    ranked_emojis
WHERE
    rank_in_day <= 3
ORDER BY
    date DESC,
    rank_in_day;

What this SQL does step‑by‑step

1. JOIN all relevant dimensions

• fact_emoji_reaction → dim_user, dim_post, dim_emoji, dim_time.

2. Filter by business logic

• u.state = 'California' → only CA users.
• p.is_influencer_post = TRUE → only influencer‑posted content.
• t.date BETWEEN ... → last 7 days.

3. GROUP BY day + emoji

• Count total reactions and distinct posts per (date, emoji).

4. Rank top 3 per day

• ROW_NUMBER() over date ordered by COUNT(*) DESC.

5. Filter top 3 and sort cleanly.

This is exactly the kind of OLAP‑style query you’d whiteboard in an interview and then follow up with, “How would we optimize this for large tables?” (answer: partition by date, cluster by emoji_sk, materialized view, etc.).

4.4 How this supports analytical queries

Given this schema, analytics become natural:

• “Reactions per post” → GROUP BY post_sk
• “Top emojis by region” → JOIN dim_user.state + GROUP BY emoji_sk, state
• “Daily activity” → JOIN dim_time.date + GROUP BY date

This is exactly how real‑world data warehouses model engagement metrics using star schemas.

5. ⚠️ Common Mistakes / Misconceptions

5.1 Mistaking OLAP for OLTP

Mistake: “Let’s just add columns like like_count, love_count on posts table.”

This works for read‑centric front‑end counts, but it’s terrible for analysis:

• You can’t track who reacted
• You can’t analyze trends by region
• You can’t handle historical changes cleanly

Fix:

• Keep transactional schema simple.
• Build a separate OLAP schema for analytics.

5.2 Ignoring granularity

Mistake: Granularity = one row per user‑post (aggregated counts).

That prevents answering:

• “How did emoji distribution change over time?”
• “What if a user changed their reaction?”

Fix:

• Use transaction‑fact granularity: one row per reaction event.

5.3 Not accounting for SCD

Mistake: Treat user location and influence as static.

If a user moves from NY to CA, older reactions will be misattributed to “today’s location” if you don’t track history.

Fix:

• Use SCD Type 2 in dim_user (new row when city/followers change).
• Join fact_emoji_reaction.created_at with dim_user.valid_from / valid_to to get correct context at time of reaction.kops.

6. 🚀 Pro Tips / Best Practices

6.1 Keep the fact table “thin”

• Only store surrogate keys + additive metrics in fact_emoji_reaction.
• Put all descriptive attributes (city, state, influencer status, emoji name) in dimensions.

This keeps the fact table fast for aggregations.

6.2 Use a proper ETL / CDC pipeline

In production:

• A CDC pipeline (e.g., Kafka → Spark → warehouse) can:
• Capture every reaction event
• Enrich with user, post, emoji, time context
• Upsert dimensions (SCD‑2)
• Append to the fact table

This mirrors how large platforms like Instagram / Twitter analyze engagement at scale.

6.3 Precalculate common aggregates

For performance:

• Create aggregated materialized views like:
• daily_reactions_by_emoji_city
• post_engagement_metrics
• Use them for dashboards, but keep the granular fact table for exploratory queries.

7. 🔄 Real‑World Use Cases

7.1 Interview readiness

In a data‑engineering interview, you can confidently:

• Define fact vs dimensions
• Explain granularity and why transaction‑fact is chosen
• Mention SCD‑2 for user location and influencer status
• Walk through a query like:
• “Top 3 emojis by region per day for influencer posts”

And then write the SQL to match.

7.2 Production data‑warehousing

Big social‑media platforms:

• Track every like, reaction, view, share as granular events
• Load them into star schemas (users, posts, time, geo, etc.)
• Power BI / Tableau dashboards answering exactly the kind of query you outlined.

8. 📌 Summary (Quick Recap)

• Granularity: One row per emoji reaction event (user × post × emoji × time).
• Fact table: fact_emoji_reaction with surrogate keys to dim_user, dim_post, dim_emoji, dim_time.
• Dimensions: dim_user, dim_post, dim_emoji, dim_time (with SCD‑2 for changing user attributes).
• Analytics: JOIN fact + dimensions to slice by region, time, emoji, influencer status.
• Anti‑patterns to avoid:
• Storing counts instead of events
• Ignoring historical changes in user state

9. 📣 Call to Action (CTA)

If you found this breakdown useful:

Share this article with your friends preparing for data‑engineering or SDE interviews — it’s exactly the kind of “OLAP‑friendly social‑media schema” you’ll be asked to whiteboard.

🚀 Level Up Your Career — Don’t Wait, Start NOW!

If you’re serious about growing in tech and staying ahead of the curve, this is your moment. No shortcuts — just real skills that actually make a difference.

🌐 Let’s Connect & Grow Together

Follow me for practical insights, real-world learning, and career tips:

🐦 Twitter: https://x.com/SriwWorld 📺 YouTube: https://www.youtube.com/@sriwworldofcoding?sub_confirmation=1 ✍️ Medium: https://medium.com/@sriwworldofcoding 🧵 Threads: https://www.threads.com/@sriwworldofcoding 📸 Instagram: https://www.instagram.com/sriwworldofcoding/ 📘 Facebook: https://www.facebook.com/profile.php?id=61576419014220 🌌 Bluesky: https://bsky.app/profile/sriwworldofcoding.bsky.social

🎯 Want Real Skills? Start With These Hands-On Courses

⚙️ Apache Airflow Bootcamp (Workflow Automation)

👉 https://www.udemy.com/course/apache-airflow-bootcamp-hands-on-workflow-automation/ 💡 Go from beginner to advanced — master DAGs, scheduling, operators, sensors, and build real production workflows.

🔥 PySpark for Data Engineers (Architecture + Interviews)

👉 https://www.udemy.com/course/pyspark-for-data-engineers-architecture-interviews/ 💡 Deep dive into Spark architecture, optimization, and performance tuning — plus crack interviews with confidence.

☁️ Crack Azure Data Engineer Interviews: The Ultimate Q&A Guide

👉 https://www.udemy.com/course/crack-azure-data-engineer-interviews-the-ultimate-qa-guide/ 💡 Get interview-ready with real-world questions on ADF, Synapse, Databricks, Event Hubs, Data Lake, Azure Functions & more.

💥 The difference between where you are and where you want to be? ACTION. Start learning today — your future self will thank you.