-- Hook Manual Quality Label table for storing manual quality labels from RDS
-- This table is populated by the Glue job rds_to_s3_hook_manual_quality_label_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by partition date and hour for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {hook_manual_quality_label_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(16777216),                               -- Unique hook manual quality label identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the label was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    TIMESTAMP TIMESTAMP_TZ(9),                          -- Timestamp of when the label was applied
    
    -- Label details
    HOOK_ID VARCHAR(255),                               -- Hook being labeled
    LABEL VARCHAR(255),                                 -- Quality label applied (e.g., "good", "bad", "spam")
    USER_ID_LABELED_BY NUMBER(38,0),                    -- User ID of the person who applied the label
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on created_at
    P_HOUR NUMBER(38,0)                                 -- Partition hour (0-23) based on created_at
);

