Snowflake能否自动清理超X天记录？存量表可配置单条记录TTL吗？

1. Does Snowflake support automatic cleanup of records older than X days? #

Snowflake doesn’t have a native "auto-clean old active records" feature out of the box, but you can build this easily using Snowflake Tasks—the platform’s scheduled job tool.

Here’s how it works:

• First, write a DELETE query targeting records where your timestamp column (like created_at or last_updated) is older than X days. For example:

  DELETE FROM your_target_table 
  WHERE created_at < DATEADD(day, -30, CURRENT_TIMESTAMP());
  

• Wrap this query in a Task that runs on your preferred schedule (daily, hourly, etc.). Snowflake will execute it automatically at the set interval.

A quick note: Snowflake’s native Time Travel and Fail-safe features handle historical data retention for recovery, but these don’t touch your table’s active data. You can adjust Time Travel retention periods (up to 90 days for standard editions) to control how long historical versions are kept, but that’s separate from deleting old active records.

2. Can we set TTL (Time-to-Live) for existing Snowflake tables to auto-clean records older than 90 days? #

Absolutely—you can implement this for existing tables using the same Task-based approach, with a few extra steps if needed:

1. Verify you have a timestamp column (to track record creation/update time). If your table doesn’t have one, add it with:

   ALTER TABLE your_existing_table ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP();
   

   (Optional: Create a trigger to update this column whenever a record is modified, so TTL reflects the latest change time.)

2. Create a scheduled Task for the cleanup. For a daily task that deletes records older than 90 days:

   CREATE OR REPLACE TTL_cleanup_task
   WAREHOUSE = your_warehouse_name
   SCHEDULE = 'USING CRON 0 0 * * * UTC' -- Runs daily at midnight UTC
   AS
   DELETE FROM your_existing_table
   WHERE last_updated < DATEADD(day, -90, CURRENT_TIMESTAMP());
   

3. Enable the task to start the automatic cleanup:

   ALTER TASK TTL_cleanup_task RESUME;
   

For large tables, you can pair this Task with a Stream to track records incrementally—this avoids scanning the entire table every time and makes the cleanup more efficient.

内容的提问来源于stack exchange，提问作者Vinod Jayachandran