-- RDS_STUDIO_PROJECT table for storing studio project data from RDS
-- This table is populated by the Glue job 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 {studio_project_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique studio project identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the project was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Project metadata
    TITLE VARCHAR(255),                                 -- Project title/name
    USER_ID NUMBER(38,0),                               -- User who owns the project
    ARCHIVED BOOLEAN,                                   -- Whether the project is archived
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on updated_at
    P_HOUR INT                                          -- Partition hour (0-23) based on updated_at
);
