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?