# Case Study: Architecting Netflix's Event-Driven Viewing History & Analytics Engine --- ## Executive Summary & Business Challenge With over **260+ million paid subscribers** streaming billions of hours of content monthly, **Netflix** generates petabytes of playback telemetry data every single day. Every time a user clicks "Play", pauses, changes audio language, or watches 5 minutes of a show before exiting, a heartbeat telemetric event is emitted. ### Business Requirements: 1. **Instant User Feature ("Continue Watching"):** The moment a user pauses on their TV, they must be able to resume on their phone at the exact second. 2. **Personalized Recommendation Algorithm:** Processing watch completion rates to suggest trending movies. 3. **Royalty & Licensing Analytics:** Calculating payout metrics to production studios based on exact hours streamed per region. --- ## 1. High-Level Data Warehouse Architecture Netflix uses a **Lambda/Kappa Architecture Pattern** combining real-time event streams with a massive Apache Iceberg / Cloud Data Lakehouse: ``` [Smart TV / Mobile App] │ ▼ (Playback Heartbeat Event every 10s) ┌───────────────┐ │ Apache Kafka │ └───────┬───────┘ │ ├─────────────────────────────────────────┐ │ │ ▼ (Real-Time Pipeline) ▼ (Batch Storage Pipeline) ┌───────────────┐ ┌───────────────┐ │ Apache Flink │ │ Apache Spark │ └───────┬───────┘ └───────┬───────┘ │ │ ▼ ▼ ┌──────────────────┐ ┌──────────────────┐ │ Cassandra Cache │ │ Columnar DW / │ │ (Continue Watch) │ │ Apache Iceberg │ └──────────────────┘ └──────────────────┘ ``` --- ## 2. Dimensional Data Modeling: The Viewing History Star Schema To query billions of watch events efficiently, data is modeled into a **Star Schema** optimized for analytical queries (OLAP). ``` ┌─────────────────────────────────┐ │ Dim_User │ ├─────────────────────────────────┤ │ user_id (PK) │ │ subscription_tier, country_code │ └────────────────┬────────────────┘ │ │ 1:N ┌───────────────────────────┐ ▼ ┌───────────────────────────┐ │ Dim_Media │ ┌───────────────┐ │ Dim_Device │ ├───────────────────────────┤ │ FACT_STREAM │ ├───────────────────────────┤ │ media_id (PK) ├───┤ SESSIONS ├─┤ device_id (PK) │ │ title, genre, duration_min│ │ (Fact Table) │ │ device_type, os_family │ └───────────────────────────┘ └──────┬────────┘ └─────────────────────────┘ │ 1:N ▼ ┌─────────────────────────────────┐ │ Dim_Date │ ├─────────────────────────────────┤ │ date_key (PK) │ │ year, month, day, is_weekend │ └─────────────────────────────────┘ ``` ### Fact Table SQL DDL (`fact_stream_sessions`) ```sql CREATE TABLE fact_stream_sessions ( session_id BIGINT NOT NULL, user_id BIGINT NOT NULL, media_id INT NOT NULL, device_id INT NOT NULL, date_key INT NOT NULL, start_timestamp TIMESTAMP NOT NULL, end_timestamp TIMESTAMP, watch_duration_sec INT NOT NULL, completed_pct DECIMAL(5,2) NOT NULL, -- e.g., 95.50% buffer_events_count INT DEFAULT 0, PRIMARY KEY (date_key, user_id, session_id) ) PARTITION BY RANGE (date_key); ``` --- ## 3. Real-World SQL Analytics Examples ### Query 1: Binge-Watching Identification (Window Functions) Find users who watched **3 or more episodes of the same show back-to-back within 4 hours**: ```sql WITH user_episodes AS ( SELECT s.user_id, m.series_name, s.start_timestamp, LEAD(s.start_timestamp, 2) OVER ( PARTITION BY s.user_id, m.series_name ORDER BY s.start_timestamp ) AS third_episode_start FROM fact_stream_sessions s JOIN dim_media m ON s.media_id = m.media_id WHERE m.content_type = 'SERIES' ) SELECT user_id, series_name, COUNT(*) AS binge_sessions FROM user_episodes WHERE third_episode_start IS NOT NULL AND third_episode_start <= start_timestamp + INTERVAL '4 hours' GROUP BY user_id, series_name; ``` --- ### Query 2: Country-Level Churn Predictor (Completion Rates) Identify shows with high abandon rates (< 15% watch duration) in the first 10 minutes: ```sql SELECT m.title, u.country_code, COUNT(s.session_id) AS total_starts, SUM(CASE WHEN s.completed_pct < 10.0 THEN 1 ELSE 0 END) AS early_abandons, ROUND( (SUM(CASE WHEN s.completed_pct < 10.0 THEN 1 ELSE 0 END) * 100.0) / COUNT(s.session_id), 2 ) AS abandon_rate_pct FROM fact_stream_sessions s JOIN dim_media m ON s.media_id = m.media_id JOIN dim_user u ON s.user_id = u.user_id WHERE s.date_key >= 20260701 GROUP BY m.title, u.country_code HAVING COUNT(s.session_id) > 1000 ORDER BY abandon_rate_pct DESC; ``` --- ## 4. Key Performance Optimizations 1. **Partitioning by Date (`date_key`):** Queries analyzing "last week's trends" scan only relevant date partitions rather than 10 years of data. 2. **Columnar Compression (Parquet/ORC):** Storing data column-by-column achieves 80%+ compression rates and speeds up `SUM()` and `AVG()` queries. 3. **Pre-Aggregated Materialized Views:** Rolling hourly totals are computed automatically so studio executives get instant dashboard loads without scanning raw event logs.