Using Snowflake UDF to query change tracking table meta data

Viewed 41

Currently exploring different ways to identify deltas. I've got something using a change capture stream, but wanted to get something working on a table with change tracking enabled as an alternative option.

This is what I attempted to do:

create or replace table T1 (
  id number(8) not null,
  c1 varchar(255) default null
);

create or replace table T1_MERGED_OUTPUT (
  id number(8) not null,
  c1 varchar(255) default null
);

create or replace table DELTA_UPDATE_TIMES (
  id number(8) not null,
  table_name varchar(255) not null,
  last_update_datetime TIMESTAMP_TZ default current_timestamp()
);

alter table t1 set change_tracking = true;

T1 is the source table with change tracking enabled. T1_MERGED_OUTPUT will be loaded with the incremental updates. DELTA_UPDATE_TIMES holds the times of when each table was updated. To get the latest update timestamps for each table, I've defined a UDF:

CREATE OR REPLACE FUNCTION TABLE_LAST_UPDATE_DATETIME(tbl VARCHAR)
    RETURNS TIMESTAMP_TZ
    AS
    $$
        SELECT MAX(LASTEST_UPDATE_DATETIME) FROM DELTA_UPDATE_TIMES WHERE TABLE_NAME = tbl
    $$;
    

Then I tried to create another UDF to get the changes between the last update time and the current time like this:

CREATE OR REPLACE FUNCTION DATA_LAST_UPDATE_DATETIME(start_time TIMESTAMP_TZ)
    RETURNS TABLE(ID NUMBER, C1 VARCHAR, ACTION_NAME VARCHAR, IS_UPDATE BOOLEAN)
    AS
    $$
          select ID, C1, METADATA$ACTION AS ACTION_NAME, METADATA$ISUPDATE AS IS_UPDATE
          from t1
          changes(information => default)
          at(timestamp => start_time)
          end(timestamp => current_timestamp())
    $$;

But I'm getting this error: SQL compilation error: error line 2 at position 29 invalid identifier 'METADATA$ACTION'

When I run that query outside of the function, it works correct and the action and is_update is returned.

Any ideas what the issue is here and whether there is a way round it?

0 Answers
Related