I am looking for a way to design a snowflake stream that ONLY emits columns that have changed in a record. If I use the stream, it emits the entire record even if only one of the column values has changed in that record.
Source table is as following:
create or replace table web_users (user_id int, name string, state string);
insert into web_users values (1, 'saqib', 'CA');
insert into web_users values (2, 'ali', 'AZ');
update web_users set state = 'MN' where user_id = 1; -- sample update
One approach is that I unpivot (transpose) this data into Key Values as following and build a stream on it:
create or replace view users_pivoted as (
select *
from web_users
unpivot include nulls (kv for k in (name, state))
);
create or replace stream users_pivoted_stream on view users_pivoted;
But I get the following error:
Change tracking is not supported on queries with 'UNPIVOT_CLAUSE'.
However if I use UNION ALL instead of UNPIVOT then I can define the stream on it:
create or replace view users_pivoted as (
select user_id, 'name' as k, name as kv from web_users
union all
select user_id, 'state' as k, state as kv from web_users
);
Any other/better way I can emit changes at the column level?