Keeping Redshift System Table History Past 7 Days with S3 Tables

Redshift keeps its SYS_* monitoring views for seven days in cluster, which is fine until an auditor or an incident review asks for something older. Until now the usual fix was a custom export job to S3 and a Glue crawler to keep it queryable. AWS just added a native system table integration with S3 Tables that skips that pipeline entirely: Redshift can write system table data straight into Amazon S3 Tables, in Apache Iceberg format, with no ETL to maintain.

What it runs on

This applies to Redshift Provisioned on RA3 and RG instance types, and to Redshift Serverless. Older provisioned instance families are not supported. AWS launched it across a wide set of commercial regions rather than a single preview region, but region coverage is the kind of detail that shifts, so confirm your region against the current availability list before you enable it.

Permissions you need

The principal that enables the feature needs:

• redshift:EnableLogging for a provisioned cluster, or redshift-serverless:UpdateNamespace for Serverless
• s3tables:CreateTableBucket, s3tables:PutTableBucketEncryption, and s3tables:PutTableBucketPolicy, to stand up the aws-redshift table bucket

That is the whole permission set. Once the bucket exists, Redshift creates namespaces and tables inside it through a service trust relationship with S3 Tables, so nobody needs standing permission to create namespaces or tables themselves.

1. Decide what you are retaining and how it should be organized

You choose which SYS_* views to publish, from SYS_QUERY_HISTORY and SYS_QUERY_TEXT to SYS_CONNECTION_LOG and SYS_VACUUM_HISTORY, or all of them at once. You also pick a deployment model. Per-warehouse keeps each cluster's data in its own set of tables, which matters if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES could contain sensitive literal values. Consolidated writes every warehouse in the account and Region into shared tables, distinguished by a warehouse_name column, which is better for cross-warehouse observability.

Supported system tables

You can select any of these SYS_* views.

2. Enable delivery on the cluster

For a provisioned cluster, this reuses the existing logging API with a new destination type. The principal running this needs redshift:EnableLogging plus permission to create and configure an S3 table bucket named aws-redshift in the account.

-- publish selected system tables to S3 Tables, consolidated across the account
aws redshift enable-logging \
    --cluster-identifier my-redshift-cluster \
    --log-destination-type s3table \
    --log-exports sys_query_history sys_query_text sys_connection_log \
    --s3-table-granularity account
 
-- Redshift Serverless uses update-namespace instead
aws redshift-serverless update-namespace \
    --namespace-name my-namespace \
    --log-destination-type s3table \
    --s3-table-action Enable \
    --s3-table-names all \
    --s3-table-granularity namespace
  

3. Confirm data is actually flowing

Delivery runs in batches at a fixed frequency and only includes completed activity, so a query still running will not show up until it finishes. Check the last ingestion time per view with describe-logging-status on a provisioned cluster or get-namespace on Serverless.

aws redshift describe-logging-status \
    --cluster-identifier my-redshift-cluster
  

4. Register the table bucket with Glue Data Catalog

Querying the retained data from Redshift, Athena, or any other Iceberg-compatible engine requires the aws-redshift S3 table bucket to be integrated with AWS Glue Data Catalog first. This is a one-time step per account and Region, not something you repeat per cluster.

5. Query the history from Redshift

Once the catalog integration is in place, point an external schema at it and query the historical view like any other table. The rows carry warehouse_name, warehouse_namespace_arn, and s3_tables_ingestion_time alongside the original SYS_QUERY_HISTORY columns, which is what lets a consolidated deployment separate one cluster's activity from another's.

-- replace <glue_database> with the database created by the S3 Tables / Glue integration
CREATE EXTERNAL SCHEMA redshift_history
FROM DATA CATALOG
DATABASE '<glue_database>'
IAM_ROLE 'arn:aws:iam::111122223333:role/RedshiftHistoryReadRole';
 
-- queries older than the 7-day in-cluster window
SELECT warehouse_name, query_id, start_time, elapsed_time
FROM redshift_history.sys_query_history
WHERE start_time < dateadd(day, -7, getdate())
ORDER BY elapsed_time DESC
LIMIT 20;
  

What it costs

Writing the data out of Redshift into S3 Tables is free. What you pay for is standard S3 Tables storage and maintenance, meaning compaction and snapshot upkeep, on whatever you retain, plus normal usage pricing for the engine you query it with, whether that is Redshift Spectrum, Athena, or something else. Since data without an expiration policy is kept forever, an unset retention policy is really a storage cost decision, not just a compliance one.

Gotchas worth knowing before you enable this

• Disabling and re-enabling, or switching between per-warehouse and consolidated, never backfills. Whatever happened during the gap is gone for good.
• Delivered rows are immutable. You cannot update or delete individual rows through Redshift, only through S3 Tables record expiration.
• Dropping the S3 Tables permanently deletes everything retained in them and Redshift does not recreate them automatically. Re-enabling starts a fresh table with no history.
• A customer managed KMS key has to be set the first time you enable the feature. Changing it later means dropping the tables, and the retained data, and starting over.
• The views marked with an asterisk above need patch P203 or later. On an older patch the tables get created but stay empty.
• Everything is scoped to a single account and a single region. Cross-account or cross-region analysis means combining results at query time, not a single unified table.
• Only completed activity is delivered. A query that is still running will not show up until it finishes, aborts, or is canceled.

Before you run this in production

Set a record expiration policy directly in S3 Tables once delivery is running, since without one the data is kept indefinitely and keeps accruing storage cost. Start with the per-warehouse model if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES might carry sensitive literal values, and only move to consolidated once you have confirmed what those views actually capture in your environment. 


If you are pulling SYS_QUERY_EXPLAIN history into this pipeline for performance work, PlanTrace is a free tool for turning those query plans into something you can actually read.