HOW TO CHANGE TRACKING USING TABLE STREAMS IN SNOWFLAKE
A stream is a Snowflake object that provides change data capture (CDC) capabilities to track the changes in a table.
It records changes made to a table, including information about inserts, updates and deletes as well as metadata about each change. This process is referred to as change data capture (CDC).
Types Of Streams:
1. Standard table stream tracks all DML changes to the source table, including inserts, updates, and deletes (including table truncates).
2. Append-only table stream tracks row inserts only. Update and Delete operations (including table truncates) are not recorded. An append-only stream returns the appended rows only and therefore can be a better choice than a standard stream for extract, load, transform (ELT) and similar scenarios that depend exclusively on row inserts. Append-only table streams can be combined with Snowflake tasks for continuous ELT workflows to process recently changed table rows.
A stream becomes stale if its point of offset gets positioned at a point earlier than the table’s data retention period. In order to avoid staleness we should ensure that data from the stream is consumed regularly. Consuming stream data advances the stream offset point as a result of which it avoids stream becoming stale.
ENABLE Change Tracking On A Table:
Change tracking metadata is recorded for a table only after change tracking is explicitly enabled on the table by setting CHANGE_TRACKING parameter to TRUE or after a stream is created for the table. Once a stream is created for a table, automatically CHANGE_TRACKING parameter gets set to TRUE for that table.
DataTerrain with years of experience and reliable experts is ready to assist. We have served more than 250 customers in the US and an additional 70 customers worldwide. We are flexible in working hours and do not need any long term binding contracts.