sundeck-io / OpsCenter

OpsCenter for Snowflake makes it easy to understand and manage your Snowflake consumption
Other
25 stars 15 forks source link

Migrate all NTZ in task_log back to LTZ #660

Closed joshelser closed 6 months ago

joshelser commented 6 months ago

Description

The times in admin.materialization_status don't have timezones but are set to the current session timezone. This leads to confusion where downstream systems may interpret the time as UTC instead of the actual timezone.

This changes the fields in INTERNAL.TASK_LOG from TIMESTAMP_NTZ to TIMESTAMP_LTZ which implicitly carry through to ADMIN.MATERIALIZATION_STATUS.

To avoid weirdness of TIMESTAMP_LTZ values changes through a procedure call, this avoids passing start_time back when in FINISH_TASK. If you're curious:

begin
    create or replace table tztest(ts timestamp_ltz);
    WITH example AS PROCEDURE (ts timestamp_ltz)
      RETURNS STRING
    AS
    $$
    BEGIN
        insert into tztest select :ts;
        return '';
    end
    $$
    CALL example(current_timestamp());

    let rs resultset :=(select *, current_timestamp() from tztest);
    return table(rs);
end;

There is no (easy) way to correct the data in-place. We have to do this before we release the native app with the task_log publicly; else, we will have a very bad migration problem on our hands.

Checklist: