Use storage lifecycle policies with dynamic tables

You can attach storage lifecycle policies to dynamic tables to delete or archive rows that match a predicate, on a schedule that runs independently of dynamic table refresh.

To attach a storage lifecycle policy directly to a dynamic table, use the WITH STORAGE LIFECYCLE POLICY clause in the CREATE DYNAMIC TABLE statement or the ADD STORAGE LIFECYCLE POLICY clause in the ALTER DYNAMIC TABLE statement.

How storage lifecycle policies work with dynamic tables

Rows that match the policy predicate form the dynamic table’s expired region. The expired region behaves like the IMMUTABLE WHERE region: refresh treats those rows as frozen and never recomputes them. Storage lifecycle policy execution runs asynchronously on its own schedule (see Storage lifecycle policies), independent of dynamic table refresh.

A row can fall in both the IMMUTABLE WHERE region and the expired region. IMMUTABLE WHERE protects rows from being recomputed by refresh, not from being expired by the policy: when the policy runs, it still deletes or archives those rows.

Limitations

The following limitations apply when you attach a storage lifecycle policy to a dynamic table:

  • If a dynamic table is created using the BACKFILL FROM clause, storage lifecycle policies on the backfill table aren’t copied to the new dynamic table. Attach a policy to the new dynamic table separately.
  • The policy predicate is subject to the same restrictions as an IMMUTABLE WHERE predicate. For the full list, see IMMUTABLE WHERE usage notes.

Reinitialization triggers

Changing or removing a storage lifecycle policy can trigger reinitialization on the dynamic table’s next refresh, depending on how the change affects the expired region. The following table lists the changes on a dynamic table and whether they trigger reinitialization:

ChangeTriggers reinitialization?
Shortening retention (expired region grows; for example, INTERVAL '2 weeks' to INTERVAL '1 week')No. The next policy run deletes the newly expired rows.
Lengthening retention (expired region shrinks; for example, INTERVAL '1 week' to INTERVAL '2 weeks')Yes. The dynamic table must recompute rows that were previously in the expired region.
Removing the policy, changing its predicate, or applying it to different columnsYes.

When a storage lifecycle policy change triggers reinitialization, the refresh-history REINIT_REASON column contains a value identifying the storage lifecycle policy change. To query refresh history, see DYNAMIC_TABLE_REFRESH_HISTORY.

For the canonical list of all reinitialization triggers, see What triggers reinitialization. For dynamic table cost guidance, see Understanding compute cost.

Example: Limited retention dynamic table

The following dynamic table contains the most recent week of orders from the base table. The attached policy expires rows older than one week, so the dynamic table retains only the most recent week:

CREATE STORAGE LIFECYCLE POLICY expire_after_1w
  AS (ts TIMESTAMP) RETURNS BOOLEAN -> ts < CURRENT_TIMESTAMP() - INTERVAL '1 week';

CREATE OR REPLACE DYNAMIC TABLE dt_orders
  TARGET_LAG = '1 minute'
  WAREHOUSE = transform_wh
  WITH STORAGE LIFECYCLE POLICY expire_after_1w ON (order_date)
  AS
    SELECT order_id, customer_id, order_date, product_name, quantity, unit_price, order_status
    FROM raw_orders;

Example: Unlimited retention dynamic table and base table with limited retention

The following example reuses the expire_after_1w policy defined in the previous example. It attaches the policy to the raw_orders base table, then creates a dynamic table that aggregates those orders into daily summaries that are retained indefinitely. The dynamic table uses an IMMUTABLE WHERE clause so that once a day is closed, its aggregate row is no longer recomputed from the base table. This makes it safe for the storage lifecycle policy to expire the older rows in the base table:

ALTER TABLE raw_orders ADD STORAGE LIFECYCLE POLICY expire_after_1w ON (order_date);

CREATE OR REPLACE DYNAMIC TABLE dt_orders_daily (order_day DATE, region VARCHAR, order_count NUMBER, daily_revenue NUMBER)
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  IMMUTABLE WHERE (order_day < DATEADD('day', -2, CURRENT_DATE))
  AS
    SELECT DATE_TRUNC('DAY', o.order_date) AS order_day, c.region,
           COUNT(*) AS order_count, SUM(o.quantity * o.unit_price) AS daily_revenue
    FROM raw_orders o
    JOIN dim_customers c ON o.customer_id = c.customer_id
    GROUP BY 1, 2;

The storage lifecycle policy retention (one week) must be longer than the immutability lag so that every day’s aggregate is finalized before its corresponding base-table rows expire.

What’s next