Data Lifecycle Management: Transient Tables, Aging Signals and Storage Lifecycle Policies

1.Transient and temporary tables: retention by design

The cheapest way to manage a lifecycle is to set it when the object is created. Snowflake supports two non-permanent table types for transitory data.

A temporary table exists only in the session that created it and is not visible to other users. That makes it the built-in auto-drop: when the session ends, the data is purged, and neither the user nor Snowflake can recover it. A procedure-scoped temporary table, created inside a Snowflake Scripting stored procedure, has an even shorter life. Temporary tables add to storage charges for as long as they exist, so Snowflake recommends dropping them explicitly and logging out of inactive sessions. One security detail matters here: in that session, a temporary table takes precedence over a permanent table with the same name in the same schema. Keep this in mind before dropping a table and restoring it with Time Travel.

A transient table stays until someone drops it and is available to any role with the right privileges. Like temporary tables, it has no Fail-safe, so it never incurs Fail-safe costs. You can also create transient databases and schemas. Every table created inside them is transient. Neither type can be converted to another table type after creation, so the choice is final. To move permanent data to transient, you use CREATE TABLE … AS SELECT, re-apply grants, drop the original and optionally rename. If a transient table is cloned from a permanent one, the micro-partitions they share only enter Fail-safe once the transient clone is dropped as well.

CREATE  ?  TABLE mytranstable (id NUMBER, creation_date DATE);

Sources12

2.Using metadata and access patterns to decide what ages out

Before you shorten retention or archive anything, collect evidence on two things: where storage is going and whether anyone still reads the data.

For storage, the TABLE_STORAGE_METRICS view breaks each table's billed bytes into ACTIVE_BYTES, TIME_TRAVEL_BYTES, FAILSAFE_BYTES and RETAINED_FOR_CLONE_BYTES, and also shows TABLE_CREATED and TABLE_DROPPED. A table with high TIME_TRAVEL_BYTES or FAILSAFE_BYTES compared with its active data is paying a lot to keep history, which makes it a candidate for a shorter retention period or a transient redesign. Its IS_TRANSIENT column tells you which tables already have no Fail-safe.

For current retention settings, use the retention_time column of the SHOW commands. Adding HISTORY includes objects that have already been dropped:

SHOW DATABASES HISTORY
  ->> SELECT "name", "retention_time", "dropped_on"
        FROM $1
        WHERE "retention_time" > 1;

For access patterns, the ACCESS_HISTORY view records reads and writes. Tables a query reads directly appear in base_objects_accessed and direct_objects_accessed, and tables it writes appear in objects_modified. A table that keeps growing but never shows up as accessed is a strong candidate for archival.

Sources34

3.Automating archival and purging with storage lifecycle policies

Once you know which rows should age out, a storage lifecycle policy enforces it. The policy is a schema-level object that applies to standard, transient, dynamic, and interactive tables that don't auto-refresh. Its expression picks out rows by age or any other condition. Each matching row is then either archived to a cheaper tier or expired, which deletes it permanently. If you specify an archive period, the rows sit in the archive for that many days and are then expired. Attaching a policy also turns on change tracking for the table.

CREATE STORAGE LIFECYCLE POLICY my_slp
  AS (event_ts TIMESTAMP, account_id NUMBER)
  RETURNS BOOLEAN ->
    event_ts < DATEADD(DAY, -60, CURRENT_TIMESTAMP())
    AND EXISTS (
      SELECT 1 FROM closed_accounts
      WHERE id = account_id
    )
  ARCHIVE_TIER = COOL
  ARCHIVE_FOR_DAYS = 90;

Some operating constraints to plan for. The tier choice is permanent: once a table has an archival policy attached, it stays on that tier for its whole life. Archived rows cannot be queried in place. You retrieve them into a new table with CREATE TABLE … FROM ARCHIVE OF, and SYSTEM$GET_TABLE_ARCHIVE_METADATA shows row counts and min/max values without retrieval costs. UPDATE, DELETE and MERGE are locked while a policy runs, but INSERT and COPY still work. Large backlogs can take several daily runs to clear. Policies do not follow clones automatically. They also bypass governance policies internally while they evaluate rows. The interaction with recovery is important too: UNDROP within Time Travel restores a table's archived data as well, but archived or expired data from a transient table can never be recovered through Fail-safe.

Sources56

4.Aligning retention with compliance requirements

Turning a regulation into Snowflake settings comes down to the controls covered above. Use MIN_DATA_RETENTION_TIME_IN_DAYS for a recovery floor that table owners cannot lower. Use DATA_RETENTION_TIME_IN_DAYS in DDL to set retention per database, schema or table. Use table type to cap how long history can exist at all. Use storage lifecycle policies to archive data for a set period and then delete it. Snowflake describes lifecycle policies as a compliance tool: you can archive for a fixed time and then expire, or expire directly without archiving.

The sources for this lesson stop there. They do not map these controls to specific regulations such as GDPR or HIPAA. They do not describe different retention strategies for structured and semi-structured data, and they do not describe a tag-based way to apply retention settings. If you need those mappings, take them from your organisation's legal requirements and the tagging documentation, not from this page. One documented fact does matter for deletion-driven requirements: a transient table has no Fail-safe, so once its archive expires, the data cannot be recovered by anyone.

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Data archived or expired from a transient table can still be recovered by Snowflake Support through Fail-safe.Why is that wrong?

   Transient tables have no Fail-safe period, so once the archive expires or is dropped, the data is gone for good.

   Covered in Automating archival and purging with storage lifecycle policies

2. 2.You can start a table on COOL archival and move it to COLD later by swapping policies.Why is that wrong?

   A table keeps the archive tier of the first archival policy attached to it for its whole lifetime. Changing tiers requires Snowflake Support to delete the existing archived data.

   Covered in Automating archival and purging with storage lifecycle policies