Untitled

USE SCHEMA {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }};

CREATE OR REPLACE PROCEDURE create_tasks_for_iceberg_refresh(database_name STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    tbl_name STRING;
    task_name STRING;
    task_create_command STRING;
    task_resume_command STRING;
    res RESULTSET;
BEGIN
    res := (
        SELECT table_name
        FROM {{ SNOWFLAKE_DATABASE_NAME }}.information_schema.tables
        WHERE table_schema = '{{ EXTERNAL_TABLES_SCHEMA }}'
        AND is_iceberg = 'YES'
    );

    FOR tbl IN res DO
        task_name := 'refresh_iceberg_metadata_' || tbl.table_name;

        task_create_command := '
            CREATE OR REPLACE TASK ' || task_name || '
            WAREHOUSE = LOAD_WH
            SCHEDULE = ''USING CRON */5 * * * * UTC''
            AS
            BEGIN
                ALTER ICEBERG TABLE {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}.' || tbl.table_name || ' REFRESH;
            END;';

        task_resume_command := '
            ALTER TASK {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}.' || task_name || ' RESUME;';

        EXECUTE IMMEDIATE task_create_command;
        EXECUTE IMMEDIATE task_resume_command;
END FOR;

    RETURN 'Tasks created successfully for all tables in {{ SNOWFLAKE_DATABASE_NAME }}.';
END;
$$;

CALL create_tasks_for_iceberg_refresh('{{ SNOWFLAKE_DATABASE_NAME }}');