A social media analytics model combines an event-level post table, an engagement transaction fact for interactions like reactions and comments, and a factless edge table to record follow relationships. High-cardinality social graphs and feed metrics are partitioned by date, with pre-aggregated creator summaries built to avoid expensive multi-hop self-joins.
Social graph and engagement grain
User interactions separate into publishing events and engagement reactions:
fact_posts (grain: one row per post created) post_id (degenerate dimension) author_user_key post_date_key post_time_key post_type (text, image, video, reel) character_count media_duration_seconds fact_engagement_events (grain: one row per interaction event) engagement_id post_id actor_user_key interaction_type (like, comment, share, bookmark, click) event_date_key event_time_key dwell_time_milliseconds
Key modeling mechanics for engagement:
- Unifying likes, shares, comments, and clicks into
fact_engagement_eventssimplifies funnel calculations and cross-format engagement comparisons. - Comments link to the parent post via
post_id, while comment text bodies reside in an external document store to keep analytical warehouse rows compact and fast to scan.
Modeling follow edges and graph traversals
The follower network is modeled using a factless relationship edge table: fact_user_follows, containing follower_user_key, followed_user_key, followed_date_key, and unfollowed_date_key. When a user unfollows an account, the pipeline updates unfollowed_date_key rather than deleting the row, preserving relationship history. Graph queries in a relational warehouse have clear architectural limits:
- First-degree questions (such as total follower count for user A) require simple counts on
fact_user_followsand perform exceptionally well. - Second-degree queries (such as followers of followers or mutual friend recommendations) require self-joining the edge table, which causes severe data shuffles and cartesian blowups on large social graphs.
- High-fanout profiles like celebrity accounts with tens of millions of followers create extreme data skew during joins.
- Relationship attributes like mute or block status should be modeled directly on the edge table as status columns rather than creating duplicate relationship matrices.
- Real-time graph traversals belong in specialized graph databases or key-value caches, reserving the relational warehouse for historical trend analytics.
Creator aggregations and scaling
Because creator analytics dashboards query lifetime impressions, viral reach, and daily engagement curves, querying raw event tables is computationally expensive. Build a daily aggregate table: agg_creator_daily_metrics (grain: one row per creator per day) that pre-computes total likes, comments, impressions, and net follower growth. Partition all underlying event tables by transaction date to prune storage scans during ingestion and rollup transformations. This separation guarantees fast dashboard loading while preserving granular event history for ad-hoc algorithm analysis.