snowflake_v_etl_logs.sql

CREATE OR REPLACE VIEW V_ETL_LOGS AS
SELECT
    TO_TIMESTAMP_NTZ(
        substr(
            "Date",
            position(',', "Date") + 1,
            position('+', "Date") + 1 - position(',', "Date") -2
        ),
        'DD MON YYYY HH24:MI:SS'
    ) datetime_log,
    trim(
        REPLACE(
            REPLACE(
                REPLACE("Subject", '[KBC] Garymanley orchestrator'),
                ' succeeded'
            ),
            ' error'
        )
    ) ETL_GROUP,
    case
        when "Subject" like '%succeeded%' then 'SUCCESSFUL'
        when "Subject" like '%error%' then 'EROOR'
        else 'Unknown'
    end as ETL_STATUS,
    TO_TIMESTAMP_NTZ(
        substr(
            "EmailText",
            position('Start time', "EmailText") + 12,
            16
        ),
        'YYYY-MM-DD HH24:MI'
    ) start_time,
    TO_TIMESTAMP_NTZ(
        substr(
            "EmailText",
            position('End time', "EmailText") + 10,
            16
        ),
        'YYYY-MM-DD HH24:MI'
    ) end_time,
    substr(
        "EmailText",
        position('succeeded in', "EmailText") + 12,
        13
    ) run_time
FROM
    KEBOOLA_7127.WORKSPACE_15661914.ETL_LOGS