A video streaming analytics model separates high-volume playback heartbeats from analytical viewing sessions to balance ingestion scale with query performance. The warehouse models sessionized watch time in a primary viewing fact table, supported by a hierarchical content dimension, subscription status dimensions, and daily periodic snapshots to measure user retention.
Video playback event grain vs session grain
Video media players transmit telemetry heartbeats every ten seconds to report player state, playback position, bitrate, and buffer underruns. Storing trillions of individual raw heartbeats directly in an analytical fact table makes everyday engagement queries slow and cost-prohibitive. The proper solution uses a streaming processing pipeline (such as Apache Spark Streaming or Apache Flink) to deduplicate raw heartbeats and roll them up into a clean session grain table:
fact_viewing_sessions session_id (degenerate dimension) user_key content_key device_key subscription_tier_key session_start_date_key session_start_time_key duration_seconds_watched content_total_duration_seconds completion_percentage buffering_time_seconds bitrate_average_kbps completed_flag (TRUE if watched >= 90%)
A short overview of session boundary rules:
- A viewing session closes whenever thirty consecutive minutes of user inactivity elapse or when the subscriber selects a different title.
- Aggregating watch seconds at the session level compresses event volume by over ninety-five percent while retaining full analytical capability for viewing completion rates.
Fact viewing sessions and hierarchies
Surrounding dimensions provide the structure required for engagement reporting:
dim_content: Models video assets with a denormalized hierarchy including series title, season number, episode number, content genre, original release year, and duration. Flattening the hierarchy into one dimension allows direct drill-down from franchise to season to episode without complex recursive queries.dim_subscription: Tracks customer plan (ad-supported, standard HD, premium 4K) and billing status using SCD Type 2 to link watch time to the plan active at viewing time.dim_device: Categorizes playback clients into smart TV, mobile phone, tablet, game console, or web browser.
User retention and heartbeat deduplication
To power core executive metrics like Daily Active Users (DAU), Monthly Active Users (MAU), and cohort retention curves, build a daily periodic snapshot fact: fact_user_daily_activity. This table records one row per registered user per day with total watch minutes, unique titles watched, and activity status. When product managers calculate thirty-day cohort retention, querying the pre-aggregated daily snapshot table scans small partitions instead of re-evaluating billions of viewing session rows.