version: 2 models: - name: int_clip_comment_count description: "Intermediate model for clip comment counts aggregated by clip_id" config: group: clips tags: ["cadence=daily", "dbt"] columns: - name: clip_id description: "Unique identifier for the clip" - name: n_comments description: "Total number of non-deleted comments for the clip" - name: n_non_reply_comments description: "Number of non-deleted top-level comments (where parent_id is null)" - name: n_reply_comments description: "Number of non-deleted reply comments (where parent_id is not null)" - name: n_deleted_comments description: "Number of deleted comments" - name: updated_at description: "Timestamp of the last comment update for the clip" - name: int_clip_pin_metrics description: "Clip pin/unpin events from backend events, aggregated to the user-clip level." config: group: clips tags: ["cadence=daily", "dbt"] columns: - name: user_id description: "ID of the user who pinned/unpinned the clip" - name: clip_id description: "ID of the clip that was pinned/unpinned" - name: net_action_value description: "Net pin action: 1 for pin, -1 for unpin" - name: first_pin_timestamp description: "Timestamp of the first pin action for this user and clip" - name: has_unpin description: "Boolean indicating if the clip has ever been unpinned by this user" - name: last_action_timestamp description: "Timestamp of the last pin/unpin action for this user and clip" - name: int_agg_user_clip_plays description: "Aggregated user-clip level play statistics, including various play count and duration metrics." config: group: clips tags: ["cadence=daily", "dbt"] columns: - name: user_id description: "ID of the user who played the clip" - name: clip_id description: "ID of the clip that was played" - name: clip_creator_user_id description: "ID of the user who created the clip" - name: is_user_song_owner description: "Boolean indicating if the user playing the clip is the creator" - name: first_play_start_ts_utc description: "Timestamp of this user's first play of the clip" - name: last_play_start_ts_utc description: "Timestamp of this user's most recent play of the clip" - name: n_unique_days_listened description: "Number of unique days this user has listened to the clip" - name: play_cnt_0s description: "Total number of plays of the clip by this user" - name: play_cnt_5s description: "Number of plays by this user that lasted at least 5 seconds" - name: play_cnt_25pct description: "Number of plays by this user that covered at least 25% of the clip's unique duration" - name: play_cnt_50pct description: "Number of plays by this user that covered at least 50% of the clip's unique duration" - name: play_cnt_90pct description: "Number of plays by this user that covered at least 90% of the clip's unique duration" - name: play_duration_seconds_0s description: "Total play duration in seconds for all plays of this clip by this user" - name: play_duration_seconds_5s description: "Total play duration for plays by this user that lasted at least 5 seconds" - name: play_duration_seconds_25pct description: "Total play duration for plays by this user that covered at least 25% of the clip" - name: play_duration_seconds_50pct description: "Total play duration for plays by this user that covered at least 50% of the clip" - name: play_duration_seconds_90pct description: "Total play duration for plays by this user that covered at least 90% of the clip" - name: unique_play_contexts description: "Array of unique contexts (e.g., 'feed', 'search') where this user played the clip" - name: n_unique_play_contexts description: "Number of unique contexts where this user played the clip" - name: p_date description: "Partition date, aligned with the clip's creation date" - name: p_hour description: "Partition hour, aligned with the clip's creation hour" - name: int_clip_share_metrics description: "Clip-level aggregation of share metrics, including counts of unique users who shared each clip." config: group: clips tags: ["cadence=daily", "dbt"] columns: - name: clip_id description: "Unique identifier for the clip" - name: clip_creator_user_id description: "ID of the user who created the clip" - name: n_unique_users_shared description: "Number of unique users who have shared this clip" - name: n_unique_non_creator_users_shared description: "Number of unique users (excluding the creator) who have shared this clip" - name: n_shares description: "Total number of share actions for this clip" - name: n_creator_shares description: "Number of times the clip creator has shared their own clip" - name: n_non_creator_shares description: "Number of times users other than the creator have shared this clip" - name: n_shared_platforms description: "Number of unique platforms this clip has been shared to" - name: first_share_timestamp description: "Timestamp of the first share of this clip" - name: last_share_timestamp description: "Timestamp of the most recent share of this clip" - name: p_date description: "Partition date, aligned with the clip's creation date" - name: p_hour description: "Partition hour, aligned with the clip's creation hour" - name: int_clip_playlist_metrics description: "Clip-level aggregation of playlist engagement metrics." config: group: playlists tags: ["cadence=daily", "dbt"] columns: - name: clip_id description: "Unique identifier for the clip" - name: clip_creator_user_id description: "ID of the user who created the clip" - name: n_playlist_adds description: "Total number of times this clip has been added to playlists" - name: n_users_added_to_playlist description: "Number of unique users who have added this clip to any playlist" - name: did_owner_add_to_playlist description: "Boolean indicating whether the clip creator has added their own clip to any playlist" - name: n_non_owner_added_to_playlist description: "Number of unique users (excluding the clip creator) who have added this clip to any playlist" - name: n_playlists_added_to description: "Total number of unique playlists this clip has been added to" - name: n_playlists_added_by_owner description: "Number of unique playlists this clip was added to by the clip creator" - name: n_playlists_added_by_non_owner description: "Number of unique playlists this clip was added to by users other than the clip creator" - name: first_playlist_add_timestamp description: "Timestamp of the first time this clip was added to any playlist" - name: last_playlist_add_timestamp description: "Timestamp of the most recent time this clip was added to any playlist" - name: p_date description: "Partition date, aligned with the clip's creation date" - name: p_hour description: "Partition hour, aligned with the clip's creation hour" - name: int_clip_play_metrics description: "Clip-level aggregation of play metrics, separating stats for the creator vs. non-creators." config: group: clips tags: ["cadence=daily", "dbt"] columns: - name: clip_id description: "Unique identifier for the clip" - name: clip_creator_user_id description: "ID of the user who created the clip" - name: first_play_start_ts_utc description: "Timestamp of the very first play of this clip by any user" - name: last_play_start_ts_utc description: "Timestamp of the most recent play of this clip by any user" - name: creator_first_play_start_ts_utc description: "Timestamp of the creator's first play of their own clip" - name: creator_last_play_start_ts_utc description: "Timestamp of the creator's most recent play of their own clip" - name: n_non_creator_listeners_0s description: "Number of unique non-creator users who have played this clip" - name: n_non_creator_listeners_5s description: "Number of unique non-creator users who have played this clip for at least 5 seconds" - name: non_creator_play_cnt_0s description: "Total number of plays from all non-creator users" - name: non_creator_play_duration_seconds_0s description: "Total play duration in seconds from all non-creator users" - name: p_date description: "Partition date, aligned with the clip's creation date" - name: p_hour description: "Partition hour, aligned with the clip's creation hour" - name: int_dim_clip description: | Intermediate model that replicates the DIM_CLIP_INSERT_PROC procedure logic. It reads from S3 parquet files via the raw external table and transforms the data to match the exact structure of the production dim_clip table. This replaces the Snowflake task/procedure with dbt-dagster orchestration. Additionally, this model merges lyrics (lyrics_at_gen and lyrics_final) from the ddb_clip_meta_heavy table, replicating the DIM_CLIP_LYRICS_UPSERT_PROC procedure logic. config: group: clips meta: dagster: automation_condition: hourly_cron_with_eager_historical_backfill_condition partitions_def: type: hourly start_date: 2023-05-12 end_offset: 0 backfill_policy: max_partitions_per_run: 336 # 2 weeks worth of hourly partitions columns: - name: clip_id description: "Unique identifier for the clip (primary key)" tests: - not_null # - unique TO BE REPLACED WITH PARTITION AWARE AND/OR CORRECT WAREHOUSE - name: p_date description: "Partition date - date when the clip was created (used for table clustering)" tests: - not_null - name: p_hour description: "Partition hour - hour when the clip was created (used for table clustering)" tests: - not_null - name: created_at description: "Timestamp when the clip was created (with timezone information)" - name: user_id description: "ID of the user who created the clip" - name: request_id description: "Unique identifier for the generation request that created this clip" - name: s3_id description: "S3 object identifier for the clip's audio file" - name: status description: "Current status of the clip (e.g., completed, failed, processing)" - name: platform description: "Platform where the clip was created (web, mobile, etc.)" - name: display_index description: "Index/order of the clip within a batch generation request" - name: initial_title description: "Original title assigned to the clip at creation" - name: time_used_sec description: "Time in seconds taken to generate the clip" - name: type description: "Type of clip generation (e.g., song, instrumental, remix)" - name: task description: "Specific task or mode used for clip generation" - name: priority description: "Priority level assigned to the generation request" - name: duration_sec description: "Duration of the generated clip in seconds" - name: is_refund_credits description: "Boolean indicating if credits were refunded for this clip" - name: is_bot description: "Boolean indicating if the clip was generated by a bot user" - name: credit_cost description: "Number of credits consumed to generate this clip" - name: lyrics_at_gen description: "Lyrics text that was used during clip generation" - name: simple_prompt description: "Simplified prompt description used for generation" - name: lyrics_final description: "Final lyrics text associated with the clip" - name: is_simple description: "Boolean indicating if this was generated using simple mode" - name: is_make_instrumental description: "Boolean indicating if the clip was generated as instrumental only" - name: style_tags description: "JSON array of style tags applied to the clip" - name: negative_style_tags description: "JSON array of style tags to avoid during generation" - name: request_models description: "JSON array of AI models requested for generation" - name: model_name description: "Name of the AI model actually used for generation" - name: experiment_inf_param description: "JSON object containing experimental inference parameters" - name: experiment_version description: "Version identifier for experimental features used" - name: history description: "JSON object containing generation history and iterations" - name: concat_history description: "Concatenated string representation of generation history" - name: error_type description: "Type of error if clip generation failed" - name: error_message description: "Detailed error message if clip generation failed" - name: upload_has_vocal description: "Boolean indicating if uploaded clip contains vocals" - name: edit_session_id description: "ID of the editing session if this clip was edited" - name: edited_clip_id description: "ID of the original clip if this is an edited version" - name: task_details description: "JSON object containing detailed task-specific information" - name: creation_source description: "Source or method used to create the clip" - name: augmented_tags description: "JSON array of additional tags added to the clip" - name: free_quota_category description: "Category classification for free quota usage" - name: lyrics_language description: "Language of the lyrics in the clip" - name: simple_lyrics_model description: "Model used for simple lyrics generation" - name: created_session_id description: "ID of the user session when the clip was created" - name: clip_uid description: "Unique user-facing identifier for the clip" - name: is_audio_watermark description: "Boolean indicating if audio watermarking was requested (default: false)" - name: is_audio_watermarked description: "Boolean indicating if the clip has audio watermarking applied (default: false)" - name: user_tier description: "Subscription tier of the user who created the clip" - name: is_deleted description: "Boolean indicating if the clip has been soft deleted" - name: create_mode description: "Mode or method used to create the clip" - name: combined_from_clip_ids description: "JSON array of clip IDs that were combined to create this clip (if applicable)" - name: metadata description: "Parsed JSON object containing all clip metadata from the raw metadata_json field" - name: web_client_pathname description: "Web client pathname from metadata, indicating the page/route where the clip was created" - name: int_clip_initial_metrics description: | Intermediate model that replicates the CLIP_INITIAL_METRICS_HOURLY_UPSERT_PROC procedure logic. It processes clips created in a specific hour and collects initial interaction metrics within a 120-minute window after clip creation. This replaces the Snowflake task/procedure with dbt-dagster orchestration. Key logic: - Processes clips created in the partition hour (2 hours ago due to end_offset=-2) - Collects metrics within 120-minute window after clip creation - Aggregates play data from fact_play (owner plays only) - Aggregates backend events from bk_nonrealtime_event (ClipLike, ClipShare, etc.) - Calculates satisfaction metrics (indirect and direct) config: group: clips meta: dagster: automation_condition: hourly_cron_with_eager_historical_backfill_condition partitions_def: type: hourly start_date: 2023-05-12 end_offset: -3 partition_mappings: - asset_key: ["fact_play"] type: time_window start_offset: 0 end_offset: 3 allow_nonexistent_upstream_partitions: false - asset_key: ["bk_nonrealtime_event"] type: time_window start_offset: 0 end_offset: 3 allow_nonexistent_upstream_partitions: false backfill_policy: max_partitions_per_run: 336 columns: - name: clip_id description: "Unique identifier for the clip (primary key)" - name: user_id description: "User ID who created the clip" - name: user_uid description: "User UID who created the clip" - name: clip_created_at description: "Timestamp when the clip was created" - name: window_minutes description: "Time window in minutes for measuring initial metrics (120 minutes)" - name: window_start_time description: "Start of the measurement window (truncated to hour of clip creation)" - name: window_end_time description: "End of the measurement window (window_start_time + window_minutes)" - name: play_cnt description: "Total number of plays by the clip owner within the time window" - name: play_duration_seconds description: "Total play duration in seconds by the clip owner within the time window" - name: n_like_events description: "Number of ClipLike events by the clip owner within the time window" - name: n_undolike_events description: "Number of ClipUndoLike events by the clip owner within the time window" - name: net_like_events description: "Net like events (n_like_events - n_undolike_events)" - name: n_dislike_events description: "Number of ClipDislike events by the clip owner within the time window" - name: n_undodislike_events description: "Number of ClipUndoDislike events by the clip owner within the time window" - name: net_dislike_events description: "Net dislike events (n_dislike_events - n_undodislike_events)" - name: n_share_events description: "Number of ClipShare events by the clip owner within the time window" - name: n_download_events description: "Number of ClipDownload events by the clip owner within the time window" - name: n_makepublic_events description: "Number of ClipMakePublic events by the clip owner within the time window" - name: n_makeprivate_events description: "Number of ClipMakePrivate events by the clip owner within the time window" - name: net_public_toggle_events description: "Net public toggle events (n_makepublic_events - n_makeprivate_events)" - name: net_positive_interactions description: | Combined positive interaction score (net_like_events - net_dislike_events + share_events + download_events + net_public_toggle_events) - name: is_satisfactory_indirect description: | Indirect satisfaction indicator based on play metrics (VARIANT) (play_cnt >= 3 AND play_duration_seconds >= 60 AND no net dislikes) - name: is_satisfactory_direct description: | Direct satisfaction indicator based on explicit user actions (VARIANT) (positive interactions without net dislikes) - name: is_satisfactory description: | Overall satisfaction indicator (VARIANT) (is_satisfactory_indirect OR is_satisfactory_direct) - name: metadata description: "Additional metadata including model_name and other clip attributes (VARIANT)" - name: p_date description: "Partition date (date of clip creation)" - name: p_hour description: "Partition hour (hour of clip creation)"