Data Warehouse Schema

Overview

This document provides a comprehensive reference for tables supporting streaming analytics in the Mediabackstage data warehouse.

NOTE: All Columns are case-sensitive and must match the Column Name exactly

---

CDN_PLAY_METRICS

Purpose: Captures detailed playback metrics for media content, including session duration, delivery statistics, and user environment attributes.

Column Name	Type	Description
UTC_DATE	Date	Date of playback
SITE_ID	Varchar	Site where the media was played
MEDIA_ID	Varchar	Unique media identifier
PROFILE_ID	Varchar	Viewer profile ID
USER_ID	Varchar	User identifier
VIEWER_ID	Varchar	Viewer identifier
START_UTC_TIME	Timestamp_NTZ	Playback session start (UTC)
END_UTC_TIME	Timestamp_NTZ	Playback session end (UTC)
MEDIA_DURATION_MIN	Float	Total media duration (minutes)
MINUTES_DELIVERED	Float	Minutes delivered to the viewer
UNIQUE_MINUTES_DELIVERED	Float	Unique minutes watched
PERCENTAGE_DELIVERED	Float	Percentage of media watched
AD_IMPRESSIONS	Number	Count of ad impressions
HAS_MANIFEST	Boolean	Indicates if manifest file was used
STREAMING_TYPE	Varchar	Streaming protocol (e.g., HLS)
DEVICE_TYPE	Varchar	Device category (e.g., mobile, desktop)
OPERATING_SYSTEM	Varchar	Device operating system
BROWSER	Varchar	Browser used for playback
USER_AGENT	Varchar	Full user agent string
COUNTRY_CODE	Varchar	ISO country code
REGION	Varchar	Region, state, or province
CONTINENT_CODE	Varchar	Continent code
ACCESS	Varchar	Access type (e.g., authenticated)
REFERRER_HOST	Varchar	Referring host

---

MEDIA

Purpose: Stores core metadata describing each media asset.

Column Name	Type	Description
MEDIA_ID	Varchar	Unique media identifier
SITE_ID	Varchar	Site where the media is published
TITLE	Varchar	Media title
DESCRIPTION	Varchar	Media description
AUTHOR	Varchar	Author or uploader
DURATION	Float	Duration (minutes)
SOURCE_TYPE	Varchar	Media source type
READY	Number	Readiness state
CREATED	Timestamp_TZ	Creation timestamp
PUBLISHED_TIMESTAMP	Timestamp_TZ	Publication timestamp
EXPIRES	Timestamp_TZ	Expiration timestamp

---

MEDIA_TAGS

Purpose: Associates tags with media assets for categorization and filtering.

Column Name	Type	Description
MEDIA_ID	Varchar	Media identifier
SITE_ID	Varchar	Site identifier
TAG	Varchar	Tag assigned to the media

---

MEDIA_CUSTOM_PARAMETERS

Purpose: Holds dynamic key-value metadata pairs for media assets.

Column Name	Type	Description
MEDIA_ID	Varchar	Media identifier
SITE_ID	Varchar	Site where media is hosted
CUSTOM_PARAMETER_KEY	Varchar	Custom parameter key
CUSTOM_PARAMETER_VALUE	Varchar	Custom parameter value

---

PLAYLIST

Purpose: Holds Playlist definition.

Column Name	Type	Description
FEED_ID	Varchar	Unique identifier for the playlist
SITE_ID	Varchar	Site where the playlist is defined
ACTIVE	Number	Indicates if the playlist is active
TYPE	Varchar	Playlist type (e.g., static, dynamic)
TITLE	Varchar	Playlist title
DESCRIPTION	Varchar	Playlist description
LINK	Varchar	URL or reference link for the playlist
CREATED	Timestamp_NTZ	Creation timestamp
UPDATED	Timestamp_NTZ	Last updated timestamp
MAXLEN	Number	Maximum number of items in the playlist
RECENCY	Varchar	Recency filter applied to the playlist
SORT	Varchar	Sort order for playlist items
TAGS_MODE	Varchar	Tag inclusion mode
EXCLUDE_TAGS_MODE	Varchar	Tag exclusion mode
PIN_SLOT_1	Varchar	Media ID pinned to slot 1
PIN_SLOT_2	Varchar	Media ID pinned to slot 2

---

PLAYLIST_CUSTOM_PARAMETERS

Purpose: Stores custom key-value parameters associated with playlists.

Column Name	Type	Description
SITE_ID	Varchar	Site where the playlist is defined
FEED_ID	Varchar	Playlist identifier
NAME	Varchar	Custom parameter key
VALUE	Varchar	Custom parameter value

---

PLAYLIST_FILTER_PARAMETERS

Purpose: Defines filter parameters for playlists.

Column Name	Type	Description
SITE_ID	Varchar	Site where the playlist is defined
FEED_ID	Varchar	Playlist identifier
NAME	Varchar	Filter parameter key
VALUE	Varchar	Filter parameter value
FILTER_TYPE	Varchar	Type of filter applied

---

PLAYLIST_FILTER_TAGS

Purpose: Associates filter tags with playlists for content selection.

Column Name	Type	Description
SITE_ID	Varchar	Site where the playlist is defined
FEED_ID	Varchar	Playlist identifier
TAG	Varchar	Tag used for filtering
FILTER_TYPE	Varchar	Type of filter applied

---

PLAYLIST_MEDIA

Purpose: Maps media items to playlists and defines their order.

Column Name	Type	Description
SITE_ID	Varchar	Site where the playlist is defined
FEED_ID	Varchar	Playlist identifier
MEDIA_ID	Varchar	Media item identifier
POSITION	Number	Position of the media item in the playlist

IDP_OKTA_EVENTS

Purpose: Contains event-based log data ingested from Okta, covering authentication and user management activities such as login, signup, password resets, and failures.

Column Name	Data Type	Partition Key	Description
version	string		Event version
id	string		Unique event ID
detail-type	string		Type of event detail
source	string		Event source
account	string		AWS Account ID
time	string		Event timestamp
region	string		AWS Region
resources	array		Related AWS resources
detail	struct		Nested event details
partition_0	string	Partition (0)	Partition key
partition_1	string	Partition (1)	Partition key
partition_2	string	Partition (2)	Partition key
partition_3	string	Partition (3)	Partition key

detail Structure Example:

{
    "detail": {
        "actor": {
            "id": "string",
            "type": "string",
            "alternateId": "string",
            "displayName": "string",
            "detailEntry": "string"
        },
        "device": "string",
        "outcome": {
         "result": "string",
            "reason": "string"
        },
        "target": [
            {
                "id": "string",
                "type": "string",
                "alternateId": "string",
                "displayName": "string",
                "detailEntry": {
                    "signOnModeType": "string"
                }
            }
        ]
    }
}

Okta Event Log Format

Overview

Okta System Logs capture all user activity, administrative actions, and system events within the Okta platform. Events are emitted as structured JSON objects and can be consumed via API, AWS EventBridge, or log streaming tools.

---

outcome

Field	Type	Description
result	string	e.g., SUCCESS, FAILURE
reason	string	Description of the outcome

actor

Field	Type	Description
id	string	Unique identifier of the actor
type	string	Type of actor (e.g., User)
alternateId	string	Email or login name
displayName	string	Full name of the actor
detailEntry	string	Additional actor metadata

target[]

List of entities the event affected.

Field	Type	Description
id	string	Target object ID
type	string	Object type (e.g., user)
alternateId	string	Alternate identifier
displayName	string	Object display name
detailEntry.signOnModeType	string	Sign-on mode

---

For additional information, please consult the official documentation:

• JWPlayer Documentation

• Okta System Log API Reference

• Okta Event Types

These resources provide further details on schema definitions, event formats, and integration best practices.