Redshift Guide

AWSOfficialPublished by AWS

Skill
Tool integration
Cloud and DevOps
Apache-2.0
No issues found

From the author

Amazon Redshift is NOT PostgreSQL; corrects LLM mistakes; covers SQL, metadata, connectivity/auth, storage, query performance, and operations. Applies ONLY to Redshift (provisioned or Serverless). Pushes back on: CREATE INDEX, string_agg, pg_catalog, text type, SERIAL, stl_query. Triggers on: Redshift SQL, Redshift CREATE TABLE, Redshift COPY/UNLOAD, slow or suddenly slow Redshift query, EXPLAIN plan, queue wait, stale statistics/ANALYZE, result cache, Redshift permission denied, Redshift disk full, storage growth, snapshot/backup cost, ghost rows/bloat, VACUUM, spill to disk, distribution skew, Redshift system views, QUALIFY, PIVOT, MERGE, materialized views, Redshift Data API (Python/boto3), Redshift workload management (WLM), concurrency scaling, Redshift resize, Spectrum, awsdatacatalog, connection failures (SSL/TLS, ODBC/JDBC, connection limit), IAM/SAML federation, IAM Identity Center, datashares. Does NOT apply to: S3 policies, Athena, Glue/Iceberg outside Redshift, Aurora, RDS, DynamoDB.

— the description by AWS

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

The Redshift Guide is a technical reference designed to prevent common errors when working with Amazon Redshift. Because Redshift shares a wire protocol with PostgreSQL, AI models often incorrectly apply PostgreSQL syntax and behaviors to Redshift. This guide provides the necessary corrections, covering DDL, system metadata, query performance, storage management, and authentication. It distinguishes between provisioned clusters and Serverless workgroups, ensuring users apply the correct system views and API calls for their specific environment.

What it does

Corrects common misconceptions where PostgreSQL syntax is incorrectly applied to Redshift.
Provides guidance on Redshift-specific DDL, including distribution and sort keys.
Outlines the correct system views (SYS_ vs STL_) for monitoring and metadata discovery.
Details secure authentication practices for JDBC/ODBC and the Data API.
Offers troubleshooting steps for storage issues, query performance, and COPY/UNLOAD operations.
Enforces the use of Redshift-specific commands over PostgreSQL-native alternatives.

When to use it

When you are unsure if a PostgreSQL command or function is supported in Redshift.
When troubleshooting performance issues, disk space, or query errors in Redshift.
When setting up new tables, loading data via COPY, or exporting data via UNLOAD.
When configuring IAM roles, permissions, or connectivity for Redshift clusters or workgroups.
When you need to identify the correct system views for monitoring a specific Redshift deployment.

Advantages

Prevents errors caused by assuming PostgreSQL compatibility.
Provides clear distinctions between provisioned and Serverless environments.
Focuses on high-impact performance tuning like distribution and sort keys.
Promotes secure, least-privilege access patterns for data operations.
Reduces time spent debugging by providing a structured routing table for common issues.

Usage examples

“How do I create a table in Redshift with a sort key?”: The agent will provide a CREATE TABLE statement using DISTSTYLE and SORTKEY, explaining that Redshift uses these instead of standard PostgreSQL indexes.
“Why is my query failing with a 'relation does not exist' error?”: The agent will guide you to use SHOW commands or SYS_ views to inspect the schema and verify table existence, rather than querying information_schema.
“How do I debug a failed COPY command?”: The agent will instruct you to query SYS_LOAD_ERROR_DETAIL to identify the specific row or column causing the load failure.
“Is my Redshift cluster running out of disk space?”: The agent will help you distinguish between actual data growth and temporary query spill, and guide you to use the appropriate storage utilization references.

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/aws/redshift-guide/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip redshift-guide.zip -d .agents/skills

Documentation by AWS

Amazon Redshift Guide

Redshift is NOT PostgreSQL (read first)

Redshift speaks PostgreSQL's wire protocol and shares much of its surface syntax, so LLMs assume PostgreSQL behavior carries over — it frequently does not. Divergences span system tables (pg_catalog is incomplete), DDL (no indexes, no sequences), functions (string_agg, SUBSTR on tables, leader-node-only functions), types (a text column becomes VARCHAR(256)), and comparison semantics (trailing blanks, unenforced constraints). Assume divergence and verify against the reference below — do not answer from PostgreSQL habit. Common PostgreSQL→Redshift divergences are in references/redshift-sql-syntax.md.

Works best with the AWS MCP server — it runs the AWS CLI and Redshift Data API calls below in a sandboxed, audit-logged environment. All guidance here is plain AWS CLI and SQL and works without it.

STEP 0: Serverless or Provisioned?

Establish this before answering — APIs, system tables, and capabilities differ. Take it from the question when it says which one; ask when it does not. SELECT version() does not identify it.

Serverless — identified by a workgroup (and namespace). Data API calls take --workgroup-name; the user says "workgroup"/"Serverless".
Provisioned — identified by a cluster. Data API calls take --cluster-identifier; the user says "cluster".

Target	System Views	Credentials API
Provisioned	SYS_, all SVV_ + STL_, STV_, SVL_, SVCS_ (single-AZ only — disabled on Multi-AZ)	`redshift:GetClusterCredentials`
Serverless	`SYS_` + a subset of `SVV_` ONLY (no STL/STV/SVL/SVCS)	`redshift-serverless:GetCredentials`

Critical Facts

SHOW commands are the primary metadata interface — SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES, SHOW COLUMNS, SHOW TABLE, SHOW VIEW. Do NOT default to pg_catalog or information_schema. → Load references/redshift-sql-metadata.md for metadata/discovery questions and any "relation does not exist" report — it has the diagnostic flow.
SYS_ views are the preferred system views — they work everywhere. STL_, STV_, SVL_, and SVCS_ are provisioned single-AZ only, and some SVV_ views are unsupported on Serverless. → Load references/redshift-sql-metadata.md for any system-view or monitoring question.
sys_load_error_detail for COPY debugging (not stl_load_errors, which is provisioned single-AZ only).
DATEADD/DATEDIFF — unit-first argument order: DATEADD(day, -30, GETDATE()), DATEDIFF(day, start, end).
APPROXIMATE COUNT(DISTINCT col) — Redshift-specific, ~2% error, much faster than exact COUNT(DISTINCT) on large datasets.
MERGE ... REMOVE DUPLICATES — simplified dedup when source and target have identical schemas.
COPY should use IAM_ROLE (the namespace role, not the caller role) + supports MANIFEST for explicit file lists + MAXERROR for error tolerance.
SUBSTR() is leader-node-only — works on literals but errors on table columns (SUBSTR() function is not supported (Hint: use SUBSTRING instead)). Use SUBSTRING() on columns.
UNIQUE / PRIMARY KEY / FOREIGN KEY are informational only — NOT enforced (duplicate rows are accepted with no error). Optimizer hints; enforce integrity in the application or via MERGE. NOT NULL IS enforced.
SHOW VIEW <schema.name> returns the definition of a regular view, materialized view, or late-binding view. MV freshness: SVV_MV_INFO (is_stale).
No FILTER (WHERE ...) on aggregates — PostgreSQL-only, Redshift rejects it. Write COUNT(CASE WHEN cond THEN 1 END) / SUM(CASE WHEN cond THEN x END) instead. This applies to every query you emit, including ones you derive from a reference example.
TOP N and LIMIT N both work (TOP N PERCENT does not). A text column becomes VARCHAR(256) — use VARCHAR(max) or explicit length.
Iceberg tables use CREATE TABLE ... USING ICEBERG (not STORED AS ICEBERG, not TABLE_FORMAT=ICEBERG).
Datashares support read and write operations — consumers can write once the producer grants write privileges. Treat "permission denied" on a datashare write as a missing grant, not an unsupported operation. → Load references/redshift-sql-metadata.md for requirements and limits.
Storage: attribute before you remediate, and never reflex-VACUUM. Disk-full is usually NOT data growth — rule out transient query spill (temp blocks) and per-node/slice skew first, because the cluster average hides a single full node. On RA3/Serverless a high local reading is cache, not out-of-space, and free managed storage does not prevent a node-local Disk Full. SVV_TABLE_INFO.unsorted alone is misleading — gate sorting on vacuum_sort_benefit. "Storage is growing / what is driving it" is a different question from "storage is high": it needs a trend over months (CloudWatch, not a point-in-time view), per-table attribution is always partial, and the per-table sum is never the cluster total. → Load references/redshift-storage-utilization.md to attribute space, then references/redshift-storage-remediation.md to act.

Safety Guardrails

BLOCK: DROP DATABASE, DELETE without WHERE, publicly-accessible=true, GRANT ALL ON ALL WARN then confirm: RESIZE, RESTORE, VACUUM on large tables, ALTER PASSWORD, WLM config change, TRUNCATE / DROP TABLE — TRUNCATE forces an implicit COMMIT and cannot be rolled back (it also commits other pending work in the same transaction), so name the target table and say the rows are unrecoverable before proceeding; treat it as routine only on temp/staging tables the user has already called disposable Confirm: CREATE, GRANT specific, COPY, UNLOAD

Security Considerations

Apply these defaults when generating anything that connects, loads, or exports. Details are in the reference files noted.

In transit: the Data API is HTTPS-only. For JDBC/ODBC set the require_ssl parameter and connect with sslmode=verify-full so the server certificate is checked.
At rest: keep cluster/namespace encryption enabled, and add ENCRYPTED KMS_KEY_ID '<arn>' to UNLOAD — it writes query results to S3, outside Redshift's own encryption. → references/redshift-sql-ddl-copy.md
Credentials: prefer SecretArn (Secrets Manager) or IAM Identity Center; DbUser is acceptable because it issues temporary credentials. Never place database passwords in code, environment variables, or SQL text. → references/redshift-sql-recipes-load-api.md
Least privilege: scope the namespace IAM_ROLE to the specific bucket and prefix (s3:GetObject on arn:aws:s3:::<bucket>/<prefix>/*), not s3:* or a managed full-access policy, and condition its trust policy on both aws:SourceArn (the cluster/namespace ARN) and aws:SourceAccount — SourceArn alone still allows another resource in the account to assume it. Grant per-object privileges rather than GRANT ALL ON ALL.
Audit: CloudTrail records redshift-data:* API calls but not the SQL executed; enable Redshift audit logging (useractivitylog, connectionlog, userlog) for that. Both capture query text and user activity, so encrypt every destination in use: the CloudWatch Logs group (aws logs associate-kms-key), the CloudTrail trail (SSE-KMS), and the audit-log S3 bucket (SSE-S3 — audit logging to S3 supports only S3-managed keys, not KMS). Serverless only supports sending audit logs to CloudWatch.
Network: keep PubliclyAccessible=false and connect over a VPC endpoint. Do not open port 5439 to 0.0.0.0/0 or ::/0 — scope inbound rules to specific CIDRs or to a referencing security group.
Sensitive data: Data API results persist for 24h and sys_load_error_detail can echo fragments of rejected rows, so treat statement IDs and load-error output as sensitive.
Further reading: Security in Amazon Redshift for the full guidance behind these defaults.

Routing Table

MANDATORY: When a question matches a row below, you MUST load and read the referenced file BEFORE answering.

Ask whether the target is provisioned or Serverless before giving troubleshooting steps — unless the question already says which one, in which case use that and do not re-confirm.

User Intent	Route To
"CREATE TABLE", "DISTKEY/SORTKEY", "ENCODE", "IDENTITY", "COPY", "UNLOAD", "IAM_ROLE", "Iceberg table"	`references/redshift-sql-ddl-copy.md`
"LISTAGG", "DATEADD/DATEDIFF", "NVL/DECODE", "type mapping", "text type", "VARBYTE", "recursive CTE"	`references/redshift-sql-functions-types.md`
"QUALIFY", "PIVOT/UNPIVOT", "MERGE", "TOP N", "SUBSTR error", "UNIQUE/PK not enforced", "trailing blanks", "leader-node function", "JSON", "SUPER", "PartiQL", "nested/semi-structured data"	`references/redshift-sql-extensions-semantics.md`
"system view", "SVV_/SYS_", "SHOW commands", "STL vs SYS", "list tables", "distkey/sortkey lookup", "datashare discovery", "2-part vs 3-part", "permission denied", "GRANT", "privileges", "relation/table does not exist"	`references/redshift-sql-metadata.md`
"how do I write SQL", "PostgreSQL vs Redshift", "which SQL reference", general dialect question	`references/redshift-sql-syntax.md` (index of the 6 SQL references + PostgreSQL-vs-Redshift failure table)
"COPY failed", "load error", "Data API poll", "async query", "Data API throttle"	`references/redshift-sql-recipes-load-api.md`
"materialized view", "MV refresh", "AUTO REFRESH", "stale view"	`references/redshift-sql-materialized-views.md`
"disk full" / error 1016 / 34507, "PercentageDiskSpaceUsed", "storage grew", "what's consuming space", "ghost rows", "tombstone", "spill to disk", "managed storage vs local SSD", "datashare cache", "one node full / storage skew", "Serverless storage inflation", "what is driving the storage increase" / "storage keeps climbing" / "grew N% per month" / "which tables are growing" / "is vacuum missing" — a growth-over-time question, answered by the "Is it growing, and what is driving it?" section: split it into four (is it really that full, is it vacuum debt, how much would VACUUM return, what is driving the trend), trend from CloudWatch over months, per-table attribution from `SYS_VACUUM_HISTORY`, and state plainly what cannot be attributed from customer-side views, any snapshot / backup / recovery-point billing or cost question — including "AWS Backup", "cross-region snapshot copy", "backup billed at full size", "why is snapshot storage this expensive", "incremental so it should be cheaper" (answer from the per-type × per-platform matrix in S16, never from AWS Backup's own incremental-copy documentation, which describes AWS Backup's behavior and not Redshift's manual-snapshot metering), "cluster unavailable / stuck after hitting 100% storage" (confirm the fill first — `PercentageDiskSpaceUsed` in CloudWatch, since the SQL views are unreachable while the cluster is down, and rule out the non-storage causes before assuming disk) — attribute the space first	`references/redshift-storage-utilization.md`
"VACUUM won't finish / times out", "which VACUUM mode — DELETE ONLY / SORT ONLY / FULL / RECLUSTER", "vacuum_sort_benefit", "unsorted", "reclaim space", "deep copy", "auto-vacuum CPU", "distribution key skew", "recovering a cluster stuck or unavailable at ~100% storage — headroom plus escalation", "resize failed and stuck in canceling-resize" — take action	`references/redshift-storage-remediation.md`
"slow Redshift query", "query was fast now slow", "why is my query slow", "query performance", "time decomposition", "queue vs execution time", "planning/compile time", "result cache hit / false fast", "EXPLAIN plan", "DS_BCAST_INNER / broadcast / nested loop / merge join", "stale statistics / ANALYZE / bad row estimate", "function on join key / plan flip", "distribution skew / skew_rows / one slice", "did the query change / query hash", "row multiplication / join fan-out / DISTINCT slow", "sys_query_history / sys_query_detail"	`references/redshift-sql-query-performance.md`
"ready-to-run performance query", "which SQL to run", "diagnostic queries", "execution step / skew / spill", "plan diff between runs", "svv_table_info table health", "auto-analyze / auto-vacuum history", "lock wait / transaction history"	`references/redshift-sql-query-performance-queries.md`
"connection timed out/refused", "SSO/federated login fails", "Response signature invalid", "assumeRoleWithSAML", "RedshiftDbUser principal tag", "sts:TagSession AccessDenied", "IAM Identity Center region", "connection limit exceeded", "ODBC/JDBC driver", "ODBC 1.x EOL/CVE", "SSL error/certificate verify failed", "permission denied to query database created from datashare", "SYSLOG ACCESS/system-table row visibility", "GetClusterCredentials AccessDenied"	`references/redshift-auth-authorization.md`
General Redshift question not matching above	Answer directly from general knowledge
Aurora, RDS, DynamoDB, Athena (non-Redshift)	REFUSE. State this skill is for Amazon Redshift only. Do not provide guidance for other database services.

Data API Quick Reference

→ Load references/redshift-sql-recipes-load-api.md before answering ANY Data API, COPY-error, or async-query question. It carries the bounded poll loop, the HasResultSet and ResourceNotFoundException handling, the per-target parameters, and the auth options.

Data API calls are async by default — use long polling (--wait-time-seconds, 1–30) rather than blind sleeps, and keep a bounded loop for work that can exceed 30s. Serverless takes --workgroup-name, provisioned takes --cluster-identifier.

Files

ATTRIBUTION.mdAdded by AutoGineer908 B
LICENSELicence file added by AutoGineer9.9 KiB
NOTICELicence file added by AutoGineer67 B
SKILL.md14.7 KiB
references/redshift-auth-authorization.md24.1 KiB
references/redshift-sql-ddl-copy.md4.9 KiB
references/redshift-sql-extensions-semantics.md4.0 KiB
references/redshift-sql-functions-types.md4.2 KiB
references/redshift-sql-materialized-views.md2.9 KiB
references/redshift-sql-metadata.md10.3 KiB
references/redshift-sql-query-performance-queries.md22.8 KiB
references/redshift-sql-query-performance.md34.6 KiB
references/redshift-sql-recipes-load-api.md7.7 KiB
references/redshift-sql-syntax.md4.0 KiB
references/redshift-storage-remediation.md37.8 KiB
references/redshift-storage-utilization.md34.0 KiB

references/redshift-sql-metadata.md

Redshift Metadata Discovery & System Views

Prefer SHOW commands

Prefer SHOW over broad system-view scans for schema discovery.

SHOW DATABASES;
SHOW SCHEMAS FROM DATABASE <db, identifier, no quotes>;
SHOW TABLES FROM SCHEMA <db, identifier, no quotes>.<schema, identifier, no quotes>;
SHOW COLUMNS FROM TABLE <db>.<schema>.<table, identifier, no quotes>;
SHOW TABLE <schema>.<table>;   -- full CREATE TABLE DDL
SHOW VIEW <schema>.<view, identifier, no quotes>;   -- view definition

10,000-row limit on the list commands (SHOW DATABASES / SCHEMAS / TABLES / COLUMNS) — filter with SHOW TABLES FROM SCHEMA ... LIKE '%<pat, string, no quotes>%' or LIMIT. SHOW TABLE / SHOW VIEW return a single definition and have no such limit.
Fall back to SVV_* for what SHOW can't give: row counts/size/skew/stats staleness (SVV_TABLE_INFO — stats_off), OID lookups (pg_class).

Use `SYS_` views — `STL_`/`STV_` are provisioned single-AZ only

SYS_* views work everywhere — Serverless, provisioned single-AZ, and provisioned Multi-AZ. Always recommend them.

View family	Provisioned single-AZ	Provisioned Multi-AZ	Serverless
`SYS_*`	✅	✅	✅
`SVV_*`	✅ all	✅ all	⚠️ subset only
`STL_` / `STV_` / `SVL_` / `SVCS_`	✅	❌	❌

The legacy families are single-AZ only. STL_*/STV_*/SVL_*/SVCS_* are disabled on Multi-AZ as well as absent on Serverless, so any monitoring query built on them breaks on a single-AZ → Multi-AZ move, not just on a Serverless migration.
On Multi-AZ, use compute_type in SYS_QUERY_HISTORY (primary / secondary) to see which AZ ran a query.
A subset of the SVV_* views on Serverless are queryable, so some provisioned-only views error there — including SVV_QUERY_STATE (→ SYS_QUERY_DETAIL), SVV_VACUUM_PROGRESS and SVV_VACUUM_SUMMARY (→ SYS_VACUUM_HISTORY), SVV_DISKUSAGE (no equivalent — storage is managed), and SVV_SCHEMA_QUOTA_STATE (→ SVV_REDSHIFT_SCHEMA_QUOTA). Unlike the legacy families above, this is per view, not per family — most SVV_* views do work on Serverless, so check the individual view's reference page, which carries the note when it is provisioned-only (e.g. SVV_DISKUSAGE: "This view is only available when querying provisioned clusters"). For the full legacy → SYS_ mapping, grouped by the replacement SYS_ view, see System view mapping for migrating to SYS monitoring views.

Provisioned-only (use SYS_ instead)	Use instead
`stl_query` / `svl_qlog`	`sys_query_history` (query-level)
step/operator detail (e.g. `svl_query_summary`)	`sys_query_detail` (per-step metrics)
`stl_querytext`	`sys_query_text`
`stl_load_errors`	`sys_load_error_detail`
`stv_inflight` / `stv_recents`	`sys_query_history WHERE status='running'`
`stv_sessions`	`sys_session_history`
`stl_connection_log`	`sys_connection_log`

For query analysis, sys_query_history gives per-query rows and sys_query_detail gives the per-step (operator-level) breakdown. Use these SYS_* views — they are the documented, customer-facing interface for query monitoring.

Common column-name mistakes on sys_query_history: use query_text (not query), start_time (not starttime), elapsed_time (not duration). On svv_table_info the table column is quoted: "table"; row count is tbl_rows.

SVV_ families — pick the right scope

Family	Scope	Use when
`SVV_REDSHIFT_*`	Local + datashare tables	Redshift-native objects only
`SVV_EXTERNAL_*`	Any external schema (data catalog, Hive metastore, federated PostgreSQL/MySQL, remote Redshift, streaming, …) — see `eskind` in `SVV_EXTERNAL_SCHEMAS`	Data-lake schemas: partitions, location, file format. Federated/streaming schemas: schema and table/column listing only
`SVV_ALL_*`	Union of the two	Everything in one query
`SVV_TABLE_INFO`	Local design details	diststyle, sortkey, size, skew, unsorted

-- SVV_TABLE_INFO is visible only to superusers. A regular user gets
-- "permission denied for relation svv_table_info" until a superuser runs
-- GRANT SELECT ON svv_table_info TO <user>. SHOW TABLES lists a table only when
-- the current user is a superuser, owns the table, or has USAGE on the parent
-- schema plus SELECT on the table (or on any column of it). SVV_ALL_TABLES is
-- visible to all users, but regular users see only their own data.
SELECT "table", schema, diststyle, sortkey1, tbl_rows, size
FROM svv_table_info WHERE schema = '<schema, string, single quotes>';

-- Datashare discovery
SELECT share_name, share_type, source_database FROM svv_datashares;
SELECT object_type, object_name FROM svv_datashare_objects WHERE share_name = '<share, string, single quotes>';

Datashare writes (consumer side)

Datashares support read and write operations — consumers can INSERT / UPDATE / DELETE / MERGE / COPY / TRUNCATE / CTAS into shared tables once the producer grants write privileges. So treat "permission denied" on a datashare write as a missing grant — the fix is for the producer to grant the privilege, not to tell the user writes are unsupported.

Reference shared objects with 3-part notation (database.schema.table) or connect to the shared database — other notations are not supported for writes.
Requirements: producer database uses snapshot isolation. Full list in the datashare read/write considerations.
error: Your consumer size is not supported for multi-warehouse write queries. For more details, please refer to Amazon multi-warehouse write documentation. — this is a producer/consumer sizing mismatch, not an unsupported node type. Send the user to the documentation above for sizing guidance; do not guess at a slice count or a supported node-type list.
Not writable: views/MVs on datashare databases, interleaved-sort-key tables; multi-statement writes must be wrapped in explicit BEGIN...END. COPY is supported only without COMPUPDATE.

Permission troubleshooting

-- Check what privileges current user has on a table
SELECT HAS_TABLE_PRIVILEGE('<user, string, single quotes>', '<schema.table, string, single quotes>', 'SELECT');

-- Table grants EXPLICITLY granted to a user/role/group in the CURRENT database
-- (grants inherited via nested roles or group membership are not listed here)
SELECT * FROM svv_relation_privileges
WHERE identity_name = '<user_or_role, string, single quotes>';

-- Roles granted directly to a user (explicit grants only — roles nested inside
-- those roles are not listed here; use svv_role_grants for role-to-role grants)
SELECT * FROM svv_user_grants WHERE user_name = '<user, string, single quotes>';

-- Grant SELECT on all tables in a schema (a bare name is a username; to target
-- a role or group the keyword is required: TO ROLE role_name / TO GROUP group_name)
GRANT SELECT ON ALL TABLES IN SCHEMA <schema, identifier, no quotes> TO <user, identifier, no quotes>;

-- Grant usage on schema (required before table grants take effect)
GRANT USAGE ON SCHEMA <schema, identifier, no quotes> TO <user, identifier, no quotes>;

"permission denied for relation" → check svv_relation_privileges + GRANT USAGE on schema + GRANT SELECT on tables.
SVV_DEFAULT_PRIVILEGES shows what new objects will inherit.

`permission denied for database awsdatacatalog`

Queries against the auto-mounted AWS Glue Data Catalog fail with ERROR: permission denied for database awsdatacatalog. Root cause: the connecting role/user lacks database-level USAGE on awsdatacatalog (the Redshift database that exposes the Glue Data Catalog) — the same database-level USAGE grant that datashare-created databases require on the consumer side. Fix:

GRANT USAGE ON DATABASE awsdatacatalog TO ROLE "<database_role, identifier, double-quoted>";
-- e.g. GRANT USAGE ON DATABASE awsdatacatalog TO ROLE "AWSIDC:hr";

where the role is the one mapped to the connecting identity — e.g. an IAM Identity Center role (awsidc:<permission-set-or-group>) or an IAM-mapped identity (IAMR:<role-name> / IAM:<user-name>). Double-quote the identity — these names contain a colon (e.g. "IAMR:Admin"), so they must be quoted. Grant to the user instead (GRANT USAGE ON DATABASE awsdatacatalog TO "<database_user, identifier, double-quoted>";) if roles aren't in use. Verify with svv_database_privileges.

"Relation does not exist" — diagnostic flow

Confirm the object exists and find its schema — the error often means "not found where I looked", not "gone":
```
SHOW TABLES FROM SCHEMA <db, identifier, no quotes>.<schema, identifier, no quotes> LIKE '%<name, string, no quotes>%';
SELECT schema_name, table_name FROM svv_all_tables WHERE table_name = '<table, string, single quotes>';
```
A LIKE condition returns candidates, not an exact match — _ is a metacharacter, so '%user_data%' also matches userXdata. Confirm identity with the = query above.
Check the search path — SHOW search_path;. search_path does not work at all for external schemas/tables or datashare schemas (datashares behave as external data) — these must always be explicitly qualified, not added to search_path.
Fully qualify — schema.table (or database.schema.table cross-database). Do not rely on search_path resolution.
STL_*/STV_*/SVL_*/SVCS_* don't exist on Serverless and are disabled on provisioned Multi-AZ; use the SYS_* equivalent. Some SVV_* are also unavailable on Serverless.

Object notation: 2-part vs 3-part

2-part schema.table — current database.
3-part database.schema.table — cross-database / datashare.
External schemas alias a remote db+schema to a local 2-part name (required for Spectrum — all external tables must be created in an external schema; optional for datashares, where they enable granular per-schema permissions).
Don't rely on search_path — qualify explicitly. Debug "relation does not exist" with SHOW search_path;. External and datashare schemas can't be put in search_path at all — qualification is the only option for them.

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Needs: Needs an MCP server, Needs Node.js

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by

AWS

Publisher

AWS (Official)

Source

aws/agent-toolkit-for-aws · plugins/aws-data-analytics/skills/redshift-guide · commit 0a218f8

Licence

Apache License 2.0 (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
NOTICE (added by AutoGineer from NOTICE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-08

Also distributed in

aws/agent-toolkit-for-aws

AutoGineer is not affiliated with or endorsed by AWS.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download redshift-guide.zip