How to add Creation time and last update of a record in a snowflake table

Question

I am kinda new to snowflake and trying to add two columns to an existing table

Below are the two columns

1. Creation Time
2. Last update Time

Could someone please help me in achieving this to an existing SNOWFLAKE table.

Thanks in advance.

Answer 1

You can create these two columns but Snowflake will not be able to update data into them by himself.

You will have to update these technical fields while inserting / updating data into the given table.

You should have a look on the MERGE feature. You can for exemple :

• WHEN NOT MATCHED : insert new row with creation_time and last_update_time at current timestamp.
• WHEN MATCHED : update last_update_time with current timestamp.

Here is a nice exemple of what could be achieved usiong MERGE + STREAM + TASKS to build ome scd type 2 tables : Building a Type 2 Slowly Changing Dimension in Snowflake Using Streams and Tasks