+1 (726) 227-3497
RougeWarehouse

Data Retention on Amazon Redshift: UNLOAD, Cold-Data Tiering and Automated Archiving

Every Redshift warehouse eventually accumulates history nobody queries. The fact table that started at 400 GB is 9 TB, four years deep, and 95% of the scans touch the last 90 days. On RA3 and Serverless you are billed for managed storage by the terabyte-month, so that history is a recurring line item — and it slows down VACUUM, snapshot restores, table rebuilds and every unqualified query someone runs by accident.

This guide covers the lifecycle mechanics: deciding what to keep hot, unloading cold partitions to S3 in a queryable format, wiring the archive back in so history is still reachable, and automating the whole thing so it does not become a quarterly fire drill.

Step 1: Find out what is actually cold

Do not guess the retention boundary. Redshift records the predicates your workload uses; read them before you delete anything.

-- Largest tables and what they cost you in managed storage
SELECT "table", size / 1024.0 AS size_gb, tbl_rows,
       sortkey1, unsorted, diststyle
FROM SVV_TABLE_INFO
ORDER BY size DESC
LIMIT 25;

Then check how far back queries reach. SYS_QUERY_HISTORY joined to SYS_QUERY_DETAIL shows which tables are scanned and how often:

SELECT d.table_name,
       COUNT(DISTINCT d.query_id) AS queries,
       MIN(h.start_time)          AS first_seen
FROM SYS_QUERY_DETAIL d
JOIN SYS_QUERY_HISTORY h USING (query_id)
WHERE h.start_time > DATEADD(day, -30, GETDATE())
  AND d.step_name = 'scan'
GROUP BY 1
ORDER BY queries DESC;

For the date boundary itself, the cheapest signal is your own BI layer: dashboards almost always carry a fixed lookback. In practice we end up with three tiers:

  • Hot — last 90 days to 13 months, in Redshift managed storage, sorted on the event timestamp.
  • Warm — 1 to 3 years, in S3 as Parquet or Iceberg, queried through Spectrum or the lakehouse catalog when someone asks a year-over-year question.
  • Cold — beyond that, in S3 with a Glacier Instant Retrieval or Deep Archive lifecycle rule, restorable for audits and nothing else.

Write the tiers down as a policy per table. "Retention" arguments are much shorter when finance, legal and analytics have all signed the same page.

Step 2: UNLOAD the cold slice correctly

UNLOAD writes query results straight from the compute nodes to S3 in parallel. The options matter more than the syntax.

UNLOAD ('
  SELECT *, DATE_PART(year, event_ts) AS event_year,
            DATE_PART(month, event_ts) AS event_month
  FROM analytics.fact_events
  WHERE event_ts >= ''2024-01-01'' AND event_ts < ''2024-02-01''
')
TO 's3://acme-warehouse-archive/fact_events/'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftArchive'
FORMAT AS PARQUET
PARTITION BY (event_year, event_month)
MAXFILESIZE 256 MB
CLEANPATH;
  • FORMAT AS PARQUET is not optional for an archive you intend to query again. Columnar, compressed, typed, and readable by Spectrum, Athena, EMR and Glue without a schema guess. Parquet unloads are typically far smaller than gzipped CSV and much faster to scan.
  • PARTITION BY writes Hive-style event_year=2024/event_month=1/ prefixes. Include the partition columns in the SELECT list; by default they are written into the folder path and dropped from the file, which is what you want.
  • MAXFILESIZE between 128 MB and 512 MB. Thousands of tiny files make Spectrum queries slow and expensive; one enormous file cannot be read in parallel.
  • CLEANPATH deletes existing files at the destination prefix before writing. Use it for idempotent re-runs; leave it off — and use a fresh prefix per run — if you cannot afford an accidental wipe.
  • ALLOWOVERWRITE is the blunter version of the same idea. Prefer CLEANPATH with a partition-scoped prefix.
  • Add ENCRYPTED with a KMS key ID if the archive bucket is not already enforcing SSE-KMS by default.

Verify before you delete. STL_UNLOAD_LOG records every file written:

SELECT path, line_count, transfer_size
FROM STL_UNLOAD_LOG
WHERE query = PG_LAST_QUERY_ID();

Compare SUM(line_count) against the row count of the source predicate. If they do not match exactly, stop and investigate — an UNLOAD that hit a permissions error mid-flight can leave a partial prefix behind.

Step 3: Make the archive queryable

An archive nobody can query becomes an archive nobody trusts, and then nobody lets you delete anything. Register the unloaded data as an external table so the history stays one SQL statement away.

The classic path is Spectrum with an external schema over the Glue Data Catalog:

CREATE EXTERNAL SCHEMA archive
FROM DATA CATALOG
DATABASE 'warehouse_archive'
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftArchive'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

CREATE EXTERNAL TABLE archive.fact_events (
  event_id    BIGINT,
  event_ts    TIMESTAMP,
  account_id  BIGINT,
  event_type  VARCHAR(32),
  amount      DECIMAL(18,2)
)
PARTITIONED BY (event_year INT, event_month INT)
STORED AS PARQUET
LOCATION 's3://acme-warehouse-archive/fact_events/';

-- Register each unloaded partition
ALTER TABLE archive.fact_events
ADD IF NOT EXISTS PARTITION (event_year = 2024, event_month = 1)
LOCATION 's3://acme-warehouse-archive/fact_events/event_year=2024/event_month=1/';

Forgetting the ADD PARTITION step is the single most common archive bug: the files are there, the table exists, and every query returns zero rows. Either script it into the same job as the UNLOAD, or run a Glue crawler on the prefix.

If you are already on the lakehouse path, write the archive as Apache Iceberg through Glue or EMR instead of raw Parquet. You get schema evolution, row-level deletes for erasure requests, and compaction — see our tutorial on querying Iceberg and S3 Tables from Redshift for the read side.

Then give analysts one object that spans both tiers:

CREATE OR REPLACE VIEW analytics.fact_events_all AS
SELECT event_id, event_ts, account_id, event_type, amount
FROM analytics.fact_events
UNION ALL
SELECT event_id, event_ts, account_id, event_type, amount
FROM archive.fact_events
WITH NO SCHEMA BINDING;

WITH NO SCHEMA BINDING is required for any view referencing an external table. Point dashboards at the hot table and leave fact_events_all for the occasional deep query — the union view cannot be incrementally materialized and will always be the slower path.

Step 4: Delete from Redshift, cheaply

Now remove the hot copy. How you do it depends on how the table is built.

Partitioned by month into separate tables (the pattern that makes retention trivial):

DROP TABLE analytics.fact_events_2024_01;

Instant, no vacuum, no ghost rows. If you own the schema design, monthly or quarterly child tables behind a union-all view are still the cheapest retention mechanism Redshift has.

One big table:

DELETE FROM analytics.fact_events
WHERE event_ts < '2024-02-01';

DELETE only marks rows; the space comes back when auto-vacuum-delete runs, which on a busy cluster can take hours. For a very large slice, a rebuild is usually faster and leaves the table perfectly sorted:

BEGIN;
CREATE TABLE analytics.fact_events_new (LIKE analytics.fact_events);
INSERT INTO analytics.fact_events_new
SELECT * FROM analytics.fact_events WHERE event_ts >= '2024-02-01';
ALTER TABLE analytics.fact_events     RENAME TO fact_events_old;
ALTER TABLE analytics.fact_events_new RENAME TO fact_events;
COMMIT;

DROP TABLE analytics.fact_events_old;

Keep the renames inside the transaction so readers never see a missing table. Drop the old table only after you have confirmed the counts, and only after the UNLOAD verification in step 2 passed. Note that LIKE does not carry over constraints or grants — re-apply them, or use CREATE TABLE ... (LIKE ... INCLUDING DEFAULTS) and a scripted GRANT block.

Either way, check that storage actually dropped:

SELECT "table", size / 1024.0 AS size_gb, unsorted, tbl_rows
FROM SVV_TABLE_INFO
WHERE "table" = 'fact_events';

Remember that RA3 snapshots still hold the deleted blocks until those snapshots age out of your retention window, so the bill falls with a lag.

Step 5: Automate it

Retention that depends on someone remembering is retention that stops after two quarters. The lightweight version is a stored procedure plus a scheduled invocation:

CREATE OR REPLACE PROCEDURE analytics.sp_archive_month(p_month DATE)
AS $$
DECLARE
  v_rows BIGINT;
  v_sql  VARCHAR(MAX);
BEGIN
  SELECT COUNT(*) INTO v_rows
  FROM analytics.fact_events
  WHERE event_ts >= p_month
    AND event_ts <  ADD_MONTHS(p_month, 1);

  IF v_rows = 0 THEN
    RAISE INFO 'nothing to archive for %', p_month;
    RETURN;
  END IF;

  v_sql := 'UNLOAD (''SELECT *, DATE_PART(year, event_ts) AS event_year, '
        || 'DATE_PART(month, event_ts) AS event_month FROM analytics.fact_events '
        || 'WHERE event_ts >= ''''' || p_month || ''''' '
        || 'AND event_ts < ''''' || ADD_MONTHS(p_month, 1) || ''''''') '
        || 'TO ''s3://acme-warehouse-archive/fact_events/'' '
        || 'IAM_ROLE ''arn:aws:iam::111111111111:role/RedshiftArchive'' '
        || 'FORMAT AS PARQUET PARTITION BY (event_year, event_month) MAXFILESIZE 256 MB';

  EXECUTE v_sql;

  INSERT INTO analytics.archive_log(table_name, archived_month, row_count, archived_at)
  VALUES ('fact_events', p_month, v_rows, GETDATE());
END;
$$ LANGUAGE plpgsql;

Run it from EventBridge Scheduler through the Redshift Data API, or as a Step Functions state machine when you want the unload, the partition registration, the row-count assertion and the delete to be separate, retryable, individually alarmed states — the pattern in our Data API orchestration tutorial. Never put the DELETE in the same step as the UNLOAD.

Finish the lifecycle in S3 rather than in SQL. An S3 Lifecycle configuration on the archive prefix moves objects to Glacier Instant Retrieval after a year and Deep Archive after three, and expires them at your legal boundary:

{
  "Rules": [{
    "ID": "fact-events-archive-tiering",
    "Filter": { "Prefix": "fact_events/" },
    "Status": "Enabled",
    "Transitions": [
      { "Days": 365,  "StorageClass": "GLACIER_IR" },
      { "Days": 1095, "StorageClass": "DEEP_ARCHIVE" }
    ],
    "Expiration": { "Days": 2555 }
  }]
}

Objects in Deep Archive are not readable by Spectrum until restored, so make sure the boundary between "warm and queryable" and "cold and restorable" matches what you told the business.

Things that bite

  • Erasure requests. A per-user delete under GDPR or CCPA has to reach the archive too. Raw Parquet forces you to rewrite whole partitions; Iceberg gives you row-level deletes. If you have erasure obligations, archive to Iceberg from day one.
  • Schema drift. The hot table gains a column; last year's Parquet files do not have it. Spectrum returns NULL for columns missing from a Parquet file — but only if you have added the column to the external table definition. Version your external DDL alongside the table DDL.
  • Timezones at the boundary. If event_ts is UTC and the business defines months in local time, your partitions will be off by a few hours' worth of rows at each edge. Pick one convention and document it.
  • Snapshots are not an archive. They restore whole clusters, not tables, and they expire. Compliance retention belongs in S3 with Object Lock, not in the snapshot schedule — see backup and disaster recovery for what snapshots are actually for.
  • A dashboard pointed at the union view can scan years of S3 by accident. Cap it with a WLM query monitoring rule on scan size, or keep fact_events_all out of the BI tool's default schema entirely.
  • Datashare consumers. If another account reads the table through a datashare, dropping history changes what they see. Announce the boundary before you enforce it.

The payoff

A 9 TB fact table trimmed to 13 months of hot data is typically 1.5–2 TB. On RA3 managed storage that is a direct monthly saving, and the second-order effects are bigger: faster restores, faster table rebuilds, shorter vacuum windows, and query plans that no longer scan four years of blocks because someone left off a date filter.

If you want the tiering policy, the archive format and the automation designed once and handed over as running code, that is core to our Redshift Performance Optimization and Redshift Administration engagements. Get in touch with your largest table and its retention requirement and we will size the work.