you’re trying to use Snowflake’s native tools to track daily changes. Since you already know why Streams/Time Travel/Clones don’t fit your “drop & recreate” pattern, here’s another approach.
Below is a stored procedure (works in practice) that replaces your CREATE OR REPLACE TABLE step, computes daily deltas (INSERT/UPDATE/DELETE), and writes a summary to a global changelog table.
Important: to identify updates, the target table must have a primary key.
If you don’t need update tracking, you can drop that requirement and approximate :
updated = (total_current_rows - total_previous_rows) - (inserted - deleted)
NOTICE- this procedure will fail without a primary key.
-- ## DEFINE SCHEMA OR USE FULL PATHS ##
-- use schema <DATABASE_NAME>.<SCHEMA_NAME>;
CREATE OR REPLACE PROCEDURE replace_table(table_path STRING, sql_code STRING)
RETURNS OBJECT
LANGUAGE SQL
COMMENT = 'This procedure replaces a table and track the changelog, according to the primary key'
as $$
BEGIN
-- ## GET THE PRIMARY KEY FROM THE TARGET TABLE ##
LET show_table string := 'SHOW PRIMARY KEYS IN TABLE '|| table_path;
EXECUTE IMMEDIATE show_table;
LET primary_key string :=
(SELECT listagg(DISTINCT $5,',') WITHIN GROUP ( ORDER BY $5) AS PK
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
);
-- ## BUILD CREATE CODE FOR THE REPLACE, AND TEMPORARY TABLES (NEW AND OLD) ##
LET code_save_old_keys STRING := 'CREATE OR REPLACE TEMPORARY TABLE old_keys_table AS SELECT ' || primary_key || ' FROM ' || :table_path;
LET code_replace_new STRING := 'CREATE OR REPLACE TABLE ' || :table_path || ' AS ' || :sql_code;
LET code_save_new_keys STRING := 'CREATE OR REPLACE TEMPORARY TABLE new_keys_table AS SELECT ' || primary_key || ' FROM ' || :table_path;
-- ## EXECUTE THE SQL CODE ##
EXECUTE IMMEDIATE code_save_old_keys;
EXECUTE IMMEDIATE code_replace_new;
EXECUTE IMMEDIATE code_save_new_keys;
-- ## RESTORE THE PRIMARY KEY IN THE TARGET TABLE AFTER REPLACE ##
LET code_restore_primary_key string:= 'ALTER TABLE ' || :table_path ||' ADD CONSTRAINT pk_' || replace(:table_path,'.','_') || ' PRIMARY KEY (' || :primary_key ||')';
EXECUTE IMMEDIATE code_restore_primary_key;
-- ## COUNT THE CHANGES OF EACH KEY ITEM ##
CREATE OR REPLACE TEMPORARY TABLE raw_changes AS
SELECT * EXCLUDE (change)
,CASE sum(change)
WHEN -1 THEN 'Deleted'
WHEN 0 THEN 'Updated'
WHEN 1 THEN 'Inserted'
END as change_type
FROM (select *,1 as change from new_keys_table
UNION ALL
select *,-1 as change from old_keys_table) U
GROUP BY ALL;
-- ## AGGREGATE THE CHANGES BY TYPE (INSERT, UPDATE, DELETE) AND STORE IN AN OBJECT VARIABLE ##
LET result_object object:=
(SELECT distinct object_agg(change_type, count(1))over()
FROM raw_changes
GROUP BY change_type);
-- ## IF NOT EXISTS, CREATE A GLOBAL CHANGELOG SUMMARY TABLE (NOT TEMPORARY) TO TRACK ALL CHANGES BY TABLE AND TIME ##
CREATE TABLE IF NOT EXISTS changelog (
table_path string
,change_time timestamp
,inserted_rows int
,updated_rows int
,deleted_rows int
);
-- ## INSERT THE CHANGES OF THE CURRENT ACTION TO THE CHANGELOG TABLE ##
let _insert int:= result_object:Inserted;
let _updated int:= result_object:Updated;
let _deleted int:= result_object:Deleted;
INSERT INTO changelog (table_path,change_time,inserted_rows,updated_rows,deleted_rows)
VALUES (:table_path,current_timestamp,:_insert,:_updated,:_deleted);
return result_object; -- RETURN THE OBJECT OF THE CURRENT CHANGES
END
$$;
I hope you will find it useful
