CertSafari
    Snowflake SnowPro Advanced: Security Engineer (SEA-C01)· Lessons

    Domain 2 · Lesson 8/21

    Data retention strategy and storage lifecycle policies

    Establish and manage data retention and data lifecycle management.

    10 min read
    4.29% of exam
    5 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Use storage metrics, access history and retention settings to decide which tables should keep long history, short history, or age out
    • Write and attach a storage lifecycle policy that archives or expires rows based on age
    • Choose between the COOL and COLD archive tiers and account for retrieval, tier lock-in, transient tables, cloning and replication

    1.Retention strategy for structured and semi-structured data

    A retention strategy answers two questions for each table. How long must the change history stay recoverable? And how long should the rows exist at all? Time Travel covers the first question, and table type sets its upper limit. Long-lived tables such as fact tables should be permanent so Fail-safe protects them. Short-lived data that you can rebuild, such as ETL work tables that live less than a day, fits transient tables, which carry no Fail-safe cost.

    Weigh downtime as well as storage. If reloading lost data would be slow or disruptive, a permanent table can be the better overall choice even with its Fail-safe cost. Transient tables suit only data you can replicate or reproduce outside Snowflake.

    Choose with metadata, not guesswork. The TABLE_STORAGE_METRICS view breaks each table's bill into ACTIVE_BYTES, TIME_TRAVEL_BYTES, FAILSAFE_BYTES and RETAINED_FOR_CLONE_BYTES. A table whose Fail-safe bytes dwarf its active bytes is churning history you may not need. Its IS_TRANSIENT column shows which tables already skip Fail-safe. Access signals complete the picture. The ACCESS_HISTORY view records the objects each query read and wrote, in base_objects_accessed, direct_objects_accessed and objects_modified. A large table that nothing has read for months is a candidate for archiving.

    Finding schemas where Time Travel is turned offsql
    SHOW SCHEMAS
      ->> SELECT "name", "retention_time"
            FROM $1
            WHERE "retention_time" = 0;

    Ageing rows out needs something that measures their age. Structured tables usually have a timestamp column that a lifecycle policy can test. Semi-structured or raw landing data often lacks a reliable column you maintain. Turn on ROW_TIMESTAMP = TRUE and attach the policy to the METADATA$ROW_LAST_COMMIT_TIME metadata column instead. Rows then archive or expire by when they were last committed, whatever their shape.

    Checkpoint 1 of 4· Fill the gap

    A governance review needs to list every schema where Time Travel is turned off. Which value completes the filter?

    SHOW SCHEMAS
      ->> SELECT "name", "retention_time"
            FROM $1
            WHERE "retention_time" =  ? ;

    Checkpoint 2 of 4· Check yourself

    A large, long-lived sales fact table is fed by slow upstream systems. Reloading it after a loss would take days of downtime. Finance wants to cut its storage bill. Which table design fits the retention strategy?

    Sources1234

    2.Automating archive and expiry with storage lifecycle policies

    A storage lifecycle policy is a schema-level object that ages table rows out automatically. Its body is a Boolean expression over arguments that map to table columns. Each day, on shared compute, Snowflake checks every row against the expression. Matching rows are archived to a cheaper tier, or expired, which deletes them permanently. The first run usually happens within minutes of attaching the policy. That suits compliance rules directly: archive data for a set time before it expires, or expire it with no archive.

    A policy that archives 60-day-old rows of closed accounts to COOL for 90 dayssql
    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;

    You attach a policy with WITH STORAGE LIFECYCLE POLICY ... ON (col) in CREATE TABLE, or ADD STORAGE LIFECYCLE POLICY in ALTER TABLE. Each table can have only one policy. The ON columns must match the policy's arguments in number and type. Attaching a policy also turns on change tracking for the table. Compare against dates rather than raw timestamps, for example TO_DATE(DATEADD(...)). That way the time of day the policy runs does not change which rows qualify. Cluster on the attachment columns as well, so each daily run scans fewer micro-partitions.

    Runs have side effects. While a policy runs on a table, UPDATE, DELETE and MERGE are locked, though INSERT and COPY continue. Large backlogs are processed in chunks and may take several daily runs. Every run costs money. For a one-time purge, attach the policy, confirm the work finished in INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY, then remove it with ALTER TABLE my_table DROP STORAGE LIFECYCLE POLICY.

    Policies work on standard, transient and dynamic tables, and on interactive tables that do not auto-refresh. They do not work on shared tables (provider or consumer side), WORM snapshots or Native Apps. To evaluate the expression, Snowflake temporarily bypasses governance policies on the table internally. A masking or row access policy therefore does not hide rows from the lifecycle check.

    Checkpoint 3 of 4· Check yourself

    Your EVENTS table has a storage lifecycle policy, and today's run is still working through a large backlog. During the run, a COPY INTO load job and a MERGE upsert job both target EVENTS. What happens to them?

    Sources45

    3.Archive tiers, retrieval and what archiving does not protect

    COOL and COLD archive tiers compared
    FactorCOOLCOLD
    Minimum archival period90 days180 days
    Storage costHigherOne-fourth of COOL
    Retrieval timeFast, readily availableUp to 48 hours
    Retrieval costHigherLower
    Cloud availabilityAWS, Microsoft Azure, Google CloudAWS and Google Cloud

    Pick COOL when auditors or analysts may need archived rows back quickly. Pick COLD for data you must keep but expect never to read. Archiving costs the same once for both tiers. COLD is also limited to 1 million files per restore operation. Deleting archived data before ARCHIVE_FOR_DAYS has passed still incurs the minimum storage charge.

    The tier choice is permanent for the table. Once a table has an archival policy, it stays on that tier for its whole life. Dropping the policy and attaching one with the other tier is not allowed. Changing the tier means asking Snowflake Support to delete the existing archive. You also cannot query archived rows in place. CREATE TABLE ... FROM ARCHIVE OF with a WHERE clause copies only the rows you need into a new table. SYSTEM$GET_TABLE_ARCHIVE_METADATA shows row counts and column min/max values without paying for retrieval. Removing a policy or truncating the table leaves the archive in place. UNDROP TABLE within Time Travel restores the table's archived data too.

    Three more limits affect a retention design.

    • Clones. A policy applies only to the table it is attached to, never to that table's clones, so archiving can leave you paying for the same data in both standard and archive storage.
    • Replication. Snowflake replicates policies and their table attachments, but never archived COOL or COLD data. After failover, the archive is not available in the target account, and secondary policies do not run on secondary tables. See replication and failover operations.
    • Encryption keys. Archived data can be protected with Tri-Secret Secure, but Snowflake does not rekey it. If you suspect a key has been compromised, retrieve the archive into a new table, which has its own key, archive again from there, and drop the old archive.

    Checkpoint 4 of 4· Check yourself

    For six months, a compliance table's storage lifecycle policy has been archiving rows to the COOL tier. Finance now wants the archive in COLD, which costs a quarter as much to store. What does the team have to do to move this table's archive to COLD?

    Sources4

    Practise it for real

    Attach an archival storage lifecycle policy to a table, confirm it ran, and remove it after a one-time cleanup.

    1. 1.Create the policy with CREATE STORAGE LIFECYCLE POLICY my_slp, using an expression on an event timestamp column, ARCHIVE_TIER = COOL and ARCHIVE_FOR_DAYS = 90.

      Why: The expression decides which rows age out. The tier and period decide where they go and how long they stay before expiry.

      You should see: The policy exists as an object in the current schema.

    2. 2.Attach it with ALTER TABLE my_table ADD STORAGE LIFECYCLE POLICY my_slp ON (col1), where col1 matches the policy argument's type.

      Why: The ON columns supply the values the expression evaluates, and a table can have only one policy.

      You should see: The attachment succeeds, and change tracking is turned on for the table.

    3. 3.Check INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY after a few minutes, and call SYSTEM$GET_TABLE_ARCHIVE_METADATA on the table.

      Why: The first run normally happens within minutes, and the metadata function shows what was archived without paying for retrieval.

      You should see: A completed execution, and archive metadata showing row counts and column min/max values.

    4. 4.For a one-time cleanup, run ALTER TABLE my_table DROP STORAGE LIFECYCLE POLICY.

      Why: Each policy run costs money. Removing the policy stops future runs and leaves the archived data retrievable.

      You should see: No further daily runs. CREATE TABLE ... FROM ARCHIVE OF can still retrieve the archived rows.

    Stuck? Get a nudge

    If the attach statement fails, check that the number and types of the ON columns match the policy's AS (...) signature.

    Sources

    (5)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.”
      ↩︎ Retention strategy for structured and semi-structured data
      “You can define short-lived tables as transient to eliminate Fail-safe costs.”
      ↩︎ Retention strategy for structured and semi-structured data
      “If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, might offer”
      ↩︎ Checkpoint
    2. 2.
      “Bytes owned by (and billed to) this table that are in the Fail-safe state for the table.”
      ↩︎ Retention strategy for structured and semi-structured data
    3. 3.
      “base_table in the base_objects_accessed and direct_objects_accessed columns because the table was accessed directly”
      ↩︎ Retention strategy for structured and semi-structured data
    4. 4.
      “This lets you expire or archive rows based on when they were last committed instead of on a column that you maintain.”
      ↩︎ Retention strategy for structured and semi-structured data
      “either archives the data to COOL or COLD storage or expires the data, which deletes it permanently”
      ↩︎ Automating archive and expiry with storage lifecycle policies
      “You can archive data for a specific time before expiration, or expire it directly without archiving.”
      ↩︎ Automating archive and expiry with storage lifecycle policies
      “The minimum archival period is 180 days.”
      ↩︎ Archive tiers, retrieval and what archiving does not protect
      “When you remove a policy from a table, the archived data remains in archive storage and can still be retrieved.”
      ↩︎ Archive tiers, retrieval and what archiving does not protect
      “Snowflake never automatically runs secondary storage lifecycle policies on secondary tables, even after failover.”
      ↩︎ Archive tiers, retrieval and what archiving does not protect
      “the table is permanently assigned to the specified archive tier for its lifetime.”
      ↩︎ Exam trap 1
      “When a storage lifecycle policy is running on a table, Snowflake locks UPDATE, DELETE, and MERGE operations. You can still perform INSERT and COPY operations”
      ↩︎ Checkpoint
      “After the specified archive period elapses and the data expires, or after you drop the archive, the data is permanently deleted”
      ↩︎ Prediction
      “To alter the archive tier for a table, contact Snowflake Support to request deletion of the currently archived data.”
      ↩︎ Checkpoint

    Spotted a mistake, or was something unclear? Tell us.