-- Clip Caption table for storing clip caption data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_clipcaption_hourly_upsert
-- Clustered by partition date and hour for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {clip_caption_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique clip caption identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the caption was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Caption details
    CLIP_ID VARCHAR(255),                               -- Associated clip identifier
    CAPTION STRING,                                     -- Caption text
    RAW_MENTIONS VARIANT,                               -- Raw mentions data (JSON)
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on created_at
    P_HOUR NUMBER(38,0)                                 -- Partition hour based on created_at
);

