I started with users and posts as the obvious fact/dimension split, but then spent way too long debating whether to normalize engagement events or just denormalize everything into wide tables.
Start by clarifying the business goals and key metrics (e.g., user engagement, content performance) to determine the grain and dimensions. Then propose a star schema with fact tables for core interactions (posts, likes, comments, follows) and dimension tables for users, content, and time, while discussing trade-offs between normalization and query performance.
Pro tip: Emphasize scalability and real-time analytics: Instagram's massive scale requires partitioning, columnar storage, and possibly a lambda architecture for batch and stream processing. Mention how you'd handle slowly changing dimensions (e.g., user profile updates) to show depth.
Ask about the primary use cases (e.g., ad-hoc analysis, dashboards, ML features) and key performance indicators (DAU, engagement rate, content virality). This shapes the schema design.
Determine the core fact tables (e.g., user_engagement, content_creation) and their grain (e.g., one row per user-post interaction). Identify conformed dimensions like user, content, time, and device.
Propose a star schema with fact tables linked to dimension tables via foreign keys. For example, a fact_post_engagement table with user_key, content_key, date_key, and measures like likes, comments, shares.
Discuss partitioning (e.g., by date), distribution keys, and storage formats (Parquet, ORC). Compare star vs. snowflake schema, and consider real-time vs. batch processing needs.
Explain how to manage changes in dimensions (e.g., user profile updates) using SCD Type 2 to preserve history, and discuss impact on ETL and query performance.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.