Best Practices for Long-Term System Table Retention in Amazon Redshift with Amazon S3 Tables

Amazon Redshift system table integration with Amazon S3 Tables is a fully managed capability that automatically delivers system table log data to Amazon S3 Tables in Apache Iceberg format. This eliminates the need for custom ETL pipelines that copy system table data to persistent tables or unload it to S3 — pipelines that consume cluster resources, compete with production workloads, and break when Redshift updates system table schemas. With this feature, you can configure retention periods well beyond the 7-day limit, and your data remains open, durable, and queryable from Amazon Redshift, Amazon Athena, AWS Glue, Amazon EMR, or any Apache Iceberg-compatible engine.

Technical Prerequisites Before enabling system table integration, ensure you have an active Amazon Redshift RA3 or RG provisioned cluster, or an Amazon Redshift Serverless workgroup. You will also need an IAM role with the necessary permissions for AWS Glue Data Catalog and Amazon S3 Tables access, associated with your cluster or serverless namespace. Familiarity with creating external schemas in Redshift is helpful for the query setup.

Setting Up the Integration Navigate to the System table integrations page in the Amazon Redshift console. Select your cluster or workgroup, then choose which SYS_* views to publish from the over 25 supported views at launch (see the supported system views documentation for the full list). You can select individual views or opt in to all supported tables — choosing "all" ensures that new views added in the future are automatically included without configuration changes.

Choose your deployment model based on your operational needs. The individual model creates separate S3 tables per system table per warehouse, keeping each warehouse's data isolated. The shared model consolidates data from multiple warehouses into a common set of tables, enabling cross-warehouse fleet observability from a single query surface.

Once enabled, Redshift writes log data to S3 Tables periodically through an isolated background process, completely separate from your production workloads.

Querying Your Retained Data To query historical system table data, perform a one-time setup connecting your Redshift environment to the S3 Tables data through AWS Glue Data Catalog:

1. Create a resource link in AWS Glue Data Catalog pointing to the S3 Tables database where your logs reside.

2. Create an external schema in Redshift referencing the resource link:

CREATE EXTERNAL SCHEMA sys_tables_historical
FROM DATA CATALOG
DATABASE 'resource_link_database'
IAM_ROLE 'arn:aws:iam::<account_id>:role/<your-redshift-s3tables-role>';

1. Query your historical data using familiar two-part notation:

SELECT * FROM sys_tables_historical.sys_query_history
WHERE start_time >= DATEADD(day, -90, GETDATE());

Because access to S3 Tables is read-only, the integrity of your audit trails is inherently preserved.

Querying Options Because the data is stored in open Apache Iceberg format, you can query it with multiple engines. Use Amazon Redshift by creating an external schema pointing at the resource link. Use Amazon Athena to run serverless SQL queries against historical logs with zero infrastructure provisioning. Use AWS Glue to build automated data processing and transformation jobs on top of your operational data. Use Amazon EMR for Spark-based analytics at scale for complex cross-warehouse analysis.

Security Best Practices Follow the principle of least privilege when configuring IAM roles for S3 Tables access. Scope permissions to the specific S3 Tables bucket and Glue Data Catalog resources needed. Optionally configure AWS KMS customer managed key encryption for additional control over your data at rest — by default, data is encrypted with Amazon S3-managed key (SSE-S3) encryption.

Since the system table data in S3 Tables is read-only, your audit trails cannot be tampered with after delivery. This is particularly valuable for regulated industries that are required to maintain audit trails spanning months or years.

Common Use Cases Organizations benefit most from extended retention in several key scenarios. Performance trend analysis becomes possible with months of SYS_QUERY_HISTORY data — you can trace how individual queries perform over weeks or months and pinpoint exactly when degradation began. Before-and-after impact assessments gain measurable baselines when you change instance types, adjust WLM queues, or onboard new workloads. Seasonal capacity planning finally becomes data-driven when you can see month-end spikes, quarter-close surges, and annual peaks across extended time horizons. Compliance teams can maintain audit trails spanning months or years without building custom infrastructure.

Cost Considerations Log delivery from Amazon Redshift to Amazon S3 Tables incurs no additional cost. You pay only for Amazon S3 Tables storage, table maintenance, and querying the data with the engine of your choice. Unlike custom ETL pipelines, this feature does not consume cluster compute resources or compete with production workloads.

Conclusion Amazon Redshift system table integration with Amazon S3 Tables transforms how organizations approach operational observability, performance analysis, and compliance auditing. By eliminating custom ETL overhead, preserving data in open Apache Iceberg format, and running entirely outside your production workload, this feature delivers long-term retention with minimal operational burden. Enable it today, configure your retention policies, and unlock months or years of operational insight that was previously lost after 7 days.

References For more detailed information:

• Long-term system tables retention in Amazon Redshift with Amazon S3 Tables (AWS Blog)

• System table integration with S3 Tables (Documentation)

• Amazon Redshift System Tables and Views Reference

• Amazon S3 Tables Documentation

• Amazon Redshift Security Best Practices