社交媒体星型模型设计咨询：以Twitter为例实现DAU计算

Hey Saurabh! Great to see you’ve put in the work on Kimball’s fundamentals—let’s turn that theory into a concrete dimension model for Twitter’s web-only DAU. I’ll walk you through the whole setup, from schema design to actionable SQL you can use to calculate the metric.

Before diving into modeling, let’s formalize the DAU rule to avoid ambiguity:

Daily Active Users (Web-only) = Count of unique users who successfully logged into Twitter via a desktop web browser and completed at least one access event (e.g., loading a timeline, viewing a tweet) on a given calendar day.

This excludes mobile web, app users, and users who visited public tweets without logging in.

We’ll use a star schema with a transactional fact table (since we’re tracking discrete user actions) and 4 core dimension tables.

2.1 Core Fact Table: Fact_User_Web_Access #

This table captures every valid logged-in web access event. It’s the "heart" of the model:

• fact_user_web_access_id (UUID/auto-increment): Surrogate primary key
• Foreign Keys (dimension links):
  • date_dim_id: Links to date dimension
  • user_dim_id: Links to user dimension
  • device_browser_dim_id: Links to device/browser dimension
  • session_dim_id: Links to user session dimension
• Metrics:
  • access_count: Static value of 1 (each row = 1 valid access event)
  • is_logged_in: Boolean (1 = confirmed logged-in user, to filter out non-authenticated traffic)

2.2 Dimension Tables #

2.2.1 Dim_Date (Standard Kimball Date Dimension) #

A reusable date table for all time-based aggregations:

• date_dim_id (integer): Surrogate primary key
• full_date (YYYY-MM-DD): Human-readable date
• Time attributes: year, month, day_of_month, day_of_week, is_weekend, quarter
• No SCD needed here—dates don’t change.

2.2.2 Dim_User (SCD Type 2) #

Tracks user attributes over time (since user profiles evolve):

• user_dim_id (integer): Surrogate primary key
• user_id (string): Twitter’s native user ID (business key)
• User attributes: username, full_name, country_code, registration_date
• SCD Type 2 fields: effective_start_date, effective_end_date, is_current (flags the latest valid user profile version)

2.2.3 Dim_Device_Browser #

Filters for desktop web traffic specifically:

• device_browser_dim_id (integer): Surrogate primary key
• browser_name (string): Chrome, Safari, Firefox, etc.
• device_type (string): Desktop, Mobile, Tablet
• is_desktop_web (boolean): 1 = desktop web browser (our target segment)
• Optional: browser_version, os_name

2.2.4 Dim_Session #

Tracks user sessions to avoid overcounting repeated accesses from the same user in one day:

• session_dim_id (integer): Surrogate primary key
• session_uuid (string): Twitter’s native session ID
• user_id (string): Links to user business key
• session_start_time, session_end_time: Timestamps for the session window
• is_web_session (boolean): 1 = web-based session

Here’s how you’d query the model to get web-only DAU for a specific date:

SELECT
  d.full_date AS activity_date,
  COUNT(DISTINCT u.user_id) AS daily_active_users
FROM
  Fact_User_Web_Access f
JOIN
  Dim_Date d ON f.date_dim_id = d.date_dim_id
JOIN
  Dim_User u ON f.user_dim_id = u.user_dim_id
JOIN
  Dim_Device_Browser db ON f.device_browser_dim_id = db.device_browser_dim_id
WHERE
  db.is_desktop_web = 1 -- Filter to only desktop web traffic
  AND f.is_logged_in = 1 -- Ensure we count only logged-in users
  AND d.full_date = '2024-05-20' -- Replace with your target date
GROUP BY
  d.full_date;

• Data Collection Check: Make sure your raw event data captures user_id, login_status, device_type, browser_name, and timestamps—without these, the model won’t work.
• SCD2 Maintenance: Set up a daily job to update Dim_User whenever user profiles change (e.g., country updates, username changes) to preserve historical accuracy.
• Performance Optimization: If you’re dealing with massive volumes, create a pre-aggregated summary fact table (e.g., Fact_Daily_Web_User_Summary) that stores daily unique user counts—this speeds up ad-hoc DAU queries.
• Edge Case Handling: Exclude test accounts, bots, and suspended users by adding a user_status field to Dim_User and filtering it in your queries.

内容的提问来源于stack exchange，提问作者Saurabh Agrawal