-- Label Maker Projects table for storing project configurations
-- This table is populated by the Glue job rds_to_s3_bots_labelmaker_projects_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 {label_maker_projects_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique project identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the project was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Project details
    PROJECT_NAME VARCHAR(16777216),                     -- Name of the project
    QUESTION VARCHAR(16777216),                         -- Project question
    INSTRUCTIONS VARCHAR(16777216),                     -- Project instructions
    PREFERENCE_DIMENSIONS VARIANT,                      -- Preference dimensions configuration (JSON)
    TARGET_REVIEW_COUNT NUMBER(38,0),                   -- Target number of reviews
    STATSIG_GATE VARCHAR(16777216),                     -- Statsig gate identifier
    CREATED_BY VARCHAR(16777216),                       -- Creator identifier
    ENABLED BOOLEAN,                                    -- Whether project is enabled
    PRIORITY NUMBER(38,0),                              -- Project priority
    
    -- 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
);
