Can Snowflake Auto Purge records older than X number of days?

Question

For an existing table in snowflake is there a way we can set TTL for each record ?

In other words can i ensure records updated/created more than 90 days ago is automatically purged periodically.

Answer 1

You can use a Snowflake TASK to run deletes on a routine schedule:

CREATE OR REPLACE TASK PURGE
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = '1 MINUTE'
    AS
    DELETE FROM MY_TABLE
    -- retention time
    WHERE LOAD_TIME < (CURRENT_TIMESTAMP - INTERVAL '90 DAYS');

If you are dealing with a very large table, I recommend that you cluster it on the DATE of whatever field you are using to delete from. This will increase the performance of the delete statement. Unfortunately, there is no way to set this on a table and have it remove records automatically for you.

Answer 2

Opt 1. If the table is used for analytics, you can build a view on top of it to retrieve only last 90 days data (doing this you have the history) Opt 2. you can use SQL statement on schedule which deletes the records which are > 90 days