querying-aws-redshift
Query AWS Redshift System Tables
Overview
Works best with the AWS MCP server for sandboxed execution and audit logging. All commands below use the AWS CLI and work in any environment with configured AWS credentials. Use IAM roles or temporary credentials; avoid long-lived access keys.
Redshift can publish system tables — the SYS_* monitoring data such as sys_query_history, sys_query_detail, and sys_connection_log — to S3 Tables as continuously-updated Apache Iceberg tables.
Terminology used throughout: system table refers to a SYS_* dataset generally, and each one maps 1:1 to a published Iceberg table. Where this skill says SYS_ view, it means specifically the live in-cluster object you query on the cluster itself — that is a view, and it is a different thing from the published S3 Tables copy. This applies to both Provisioned clusters and Serverless namespaces. It is an opt-in extension of the existing logging APIs. Published tables are read-only, stored in the AWS-managed aws-redshift table bucket, and queryable via any Iceberg-compatible engine including Amazon Athena and Amazon Redshift itself.
Querying the S3 Tables copy is preferred over the live in-cluster SYS_ views when analyzing historical or high-volume system-table data because:
- The in-cluster
SYS_views have a limited retention window; S3 Tables retains history well beyond it. - Querying S3 Tables adds no load to the running Redshift cluster.
- The logs are Iceberg tables, so they can be queried at scale from any Iceberg-compatible engine and joined with other lake data.