Enable Tracking: During our last post we talked about the Query to identify whether the CHANGE_TRACKING has been enabled on the tables or not. In continuation for the same, my friend put a valid scenario to enable tracking for multiple tables without manual intervention. Consider the scenario where you have 500+ tables and as per need we require to enable tracking on all tables. You can achieve the requirement by running the below command in your environment.
alter table <<tablename>> set change_tracking = true;
But the limitation with above is that you have to do manually for all 500+ tables. Or other way around you can create your query in excel file for all tables and run in one go. But above approaches requires the user involvement and business wants to do in some automated way. As later on if they want they can turn off the Tracking on their own .
To complete the requirement in an automation way, following table and javascript procedure has been develop which will enable the tracking with no manual intervention.
SHOW TABLES;
As per the below screenshot, list of the tables where change_tracking is disabled and want to enable it via procedure.
A transient table created to hold the tables names only.
CREATE OR REPLACE TRANSIENT TABLE CHANGE_TRACK (TAB_NAME VARCHAR(30));
INSERT INTO CHANGE_TRACK(TAB_NAME)
select "name" from table(result_scan(last_query_id())) where "change_tracking" = 'OFF';
select * from CHANGE_TRACK;
Execute the Procedure:
Finally, After execution,CHANGE_TRACKING enable on all tables in my env….
Thanks for sharing this information.
Could you provide us some tips from where we can learn to write these complex store procedures for snowflake