Adding last change timestamp to a table in snowflake

Question

I have a lots of tables in Snowflake that I am updating them ( basically re-creating them) every day with a python script. I can see the timestamp of the last time those tables have been changed in information schema of my database but how can I add the column or that information to one of our tables?

Assume that I have a table customer and I want to be able to see when was the last time that each row of that table has been changed. I can see this timestamp here:

SELECT CONVERT_TIMEZONE('Etc/GMT+9','UTC',last_altered) AS last_changed
FROM "XXXX"."INFORMATION_SCHEMA"."TABLES"
WHERE table_name='CUSTOMERS';

how to add this information to customer table?

Answer 1

If you would like to see that information your python program should add that information as additional columns in each row. We used to call these columns as 'WHO COLUMNS', below are the WHO COLUMNS that we added to each table in the final schema

1. Last Updated TimeStamp
2. Last Updated User
3. Creation Timestamp

Answer 2

The best option would be to add an additional audit column to the customer table with a default value as current_timestamp

Example:

CREATE TABLE CUSTOMER (column1 varchar, insert_date timestamp default current_timestamp())

In this example you can use insert_date to track when that record is inserted. The column would be auto-populated whenever you are inserting a row like this.

INSERT INTO CUSTOMER(column1) VALUES ('test')