Media & Streaming Analytics data model
Kimball warehouse for media streaming: transaction-grain playback events (quality and lifecycle telemetry), a monthly subscription periodic snapshot for MRR and churn, and a daily engagement snapshot over conformed viewer/content/device/plan/geo dimensions. Viewer PII is classified and masked.
Streaming media analytics covering playback telemetry, monthly subscription snapshots, and daily content engagement over conformed viewer, content, device, plan, and geo dimensions — a Kimball star schema with SCD2 viewer history and viewer PII masked under GDPR. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 105
- columns
- 15
- relationships
- 4
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Playback events as a transaction-grain fact
Playback telemetry (start, buffer, error, complete) is high-volume event data; a transaction grain preserves quality-of-experience signals like rebuffering and startup time.
Monthly subscription snapshot
MRR, churn, and tenure are semi-additive subscription metrics reported monthly, decoupled from playback volume.
Daily engagement snapshot separate from raw events
Watch minutes and completion rate are pre-aggregated to viewer/content/day to keep recommendation and retention analysis performant without scanning raw event volume.
SCD2 on dim_viewer and masking on PII
Acquisition channel and profile attributes change over time, so viewer history is preserved with SCD2; GDPR/CCPA require email hashing and masking of personal identifiers.
Tables in the Media & Streaming Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_playback_event
Grain: one row per playback event (transaction)
Transaction-grain playback telemetry — one row per event.
| Column | Type | Keys |
|---|---|---|
| playback_event_key | bigint | PK |
| date_key | bigint | |
| viewer_key | bigint | |
| content_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| event_type_key | bigint | |
| session_id | varchar | |
| event_id | varchar | |
| event_timestamp | timestamp | |
| playback_position_seconds | integer | |
| bitrate_kbps | integer | |
| startup_time_ms | integer | |
| rebuffer_count | smallint | |
| rebuffer_ms | integer | |
| is_error | boolean | |
| error_code | varchar |
fct_subscription_snapshot
Grain: one row per account per plan per month (periodic snapshot)
Monthly subscription snapshot — MRR is semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| viewer_key | bigint | |
| plan_key | bigint | |
| geo_key | bigint | |
| account_id | varchar | |
| subscription_status | varchar | |
| mrr | decimal | |
| discount_amount | decimal | |
| tenure_months | integer | |
| is_new | boolean | |
| is_churned | boolean | |
| is_reactivated | boolean | |
| is_trial | boolean |
fct_engagement
Grain: one row per viewer per content per day (periodic snapshot)
Daily engagement snapshot for watch-time and completion analysis.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| viewer_key | bigint | |
| content_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| view_count | integer | |
| watch_minutes | decimal | |
| completion_rate | decimal | |
| unique_titles_watched | integer | |
| is_completed | boolean | |
| rating_given | smallint |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | varchar | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean |
dim_viewer
Grain: one row per viewer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| viewer_key | bigint | PK |
| viewer_id | varchar | BK |
| account_id | varchar | |
| varchar | PII | |
| display_name | varchar | PII |
| date_of_birth | date | PII |
| age_band | varchar | |
| gender | varchar | PII |
| profile_type | varchar | |
| acquisition_channel | varchar | |
| signup_date | date | |
| is_primary_profile | boolean |
dim_content
Grain: one row per content title
| Column | Type | Keys |
|---|---|---|
| content_key | bigint | PK |
| content_id | varchar | BK |
| title | varchar | |
| content_type | varchar | |
| series_title | varchar | |
| season_number | smallint | |
| episode_number | smallint | |
| genre | varchar | |
| subgenre | varchar | |
| maturity_rating | varchar | |
| language | varchar | |
| release_year | smallint | |
| runtime_minutes | integer | |
| is_original | boolean | |
| studio | varchar |
dim_device
Grain: one row per device type
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| device_id | varchar | BK |
| device_type | varchar | |
| device_os | varchar | |
| device_brand | varchar | |
| app_version | varchar | |
| max_resolution | varchar |
dim_plan
Grain: one row per subscription plan
| Column | Type | Keys |
|---|---|---|
| plan_key | bigint | PK |
| plan_id | varchar | BK |
| plan_name | varchar | |
| plan_tier | varchar | |
| billing_cycle | varchar | |
| monthly_price | decimal | |
| max_streams | smallint | |
| max_resolution | varchar | |
| has_ads | boolean | |
| includes_downloads | boolean |
dim_geo
Grain: one row per geographic territory
| Column | Type | Keys |
|---|---|---|
| geo_key | bigint | PK |
| geo_id | varchar | BK |
| country | varchar | |
| country_code | varchar | |
| region | varchar | |
| market | varchar | |
| timezone | varchar | |
| is_licensed_market | boolean |
Lookup tables (1)
dim_event_type
Grain: one row per playback event type
| Column | Type | Keys |
|---|---|---|
| event_type_key | bigint | PK |
| event_type_code | varchar | BK |
| event_type_name | varchar | |
| event_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_playback_event.date_key → dim_date (many-to-one)
- fct_playback_event.viewer_key → dim_viewer (many-to-one)
- fct_playback_event.content_key → dim_content (many-to-one)
- fct_playback_event.device_key → dim_device (many-to-one)
- fct_playback_event.geo_key → dim_geo (many-to-one)
- fct_playback_event.event_type_key → dim_event_type (many-to-one)
- fct_subscription_snapshot.date_key → dim_date (many-to-one)
- fct_subscription_snapshot.viewer_key → dim_viewer (many-to-one)
- fct_subscription_snapshot.plan_key → dim_plan (many-to-one)
- fct_subscription_snapshot.geo_key → dim_geo (many-to-one)
- fct_engagement.date_key → dim_date (many-to-one)
- fct_engagement.viewer_key → dim_viewer (many-to-one)
- fct_engagement.content_key → dim_content (many-to-one)
- fct_engagement.device_key → dim_device (many-to-one)
- fct_engagement.geo_key → dim_geo (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.