-- Survey Questions table for storing survey question data from RDS
-- This table is populated by the Glue job rds_to_s3_bots_surveyquestions_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 {survey_questions_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique question identifier
    
    -- Question content
    PROMPT STRING,                                      -- Question text/prompt
    TYPE VARCHAR(255),                                  -- Question type (e.g., multiple choice, text)
    OPTIONS VARCHAR(16777216),                          -- Available options for the question (JSON)
    
    -- Question organization
    GROUP_ID VARCHAR(255),                              -- Group/category ID for this question
    ORDER_NUMBER INT,                                   -- Display order within the group
    
    -- Status flags
    IS_ACTIVE BOOLEAN,                                  -- Whether the question is currently active
    IS_REQUIRED BOOLEAN,                                -- Whether the question requires an answer
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the question was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on updated_at
    P_HOUR INT                                          -- Partition hour (0-23) based on updated_at
);

