references/redshift-sql-ddl-copy.md
# Redshift DDL, COPY & UNLOAD
## CREATE TABLE — distribution + sort are the highest-impact choices
```sql
CREATE TABLE <schema, identifier, no quotes>.<table, identifier, no quotes> (
view_id BIGINT IDENTITY(1,1),
user_id INT NOT NULL,
page_url VARCHAR(2048) ENCODE ZSTD,
view_ts TIMESTAMP NOT NULL DEFAULT SYSDATE,
device_type VARCHAR(50) ENCODE BYTEDICT
)
DISTSTYLE KEY DISTKEY (user_id)
COMPOUND SORTKEY (view_ts, user_id);
```
`AUTO` is the default for both DISTSTYLE and SORTKEY, and is the documented
recommendation for most tables — omit the clauses and let Redshift choose. Specify
them deliberately (as above) when the join/filter pattern is known.
**Distribution:** `AUTO` (default — Redshift chooses and can change it as the table
grows), `KEY` (large table joined on one column), `ALL` (small dim table), `EVEN`
(no clear join key).
**Sort key:** `AUTO` (default), `COMPOUND` (range scans on leading columns, e.g.
time series), `INTERLEAVED` (equal-weight multi-column filters).
**Encoding:** `AZ64` (numeric/date), `ZSTD` (VARCHAR), `BYTEDICT` (low-cardinality
strings). First sort-key column should be `RAW` (unspecified).
- No `CREATE INDEX` — use SORTKEY. No `SERIAL` — use `IDENTITY(seed, step)`.
- `ALTER COLUMN TYPE` only supports resizing VARCHAR columns — for other type changes, recreate the table.
- `ALTER TABLE ... ALTER DISTKEY`, `ALTER DISTSTYLE`, `ALTER SORTKEY` ARE supported.
- One `ADD COLUMN` per `ALTER TABLE`.
## Late-binding views (`WITH NO SCHEMA BINDING`)
Required for views over external/Spectrum or datashare tables — otherwise CREATE
fails schema validation. Column types resolve at query time.
```sql
CREATE VIEW <schema, identifier, no quotes>.daily_events AS
SELECT event_date, COUNT(*) AS n
FROM <external_schema, identifier, no quotes>.events
GROUP BY 1
WITH NO SCHEMA BINDING;
```
## COPY — load from S3 (Redshift-specific, not standard SQL)
```sql
COPY <schema, identifier, no quotes>.<table, identifier, no quotes>
FROM 's3://<bucket, string, no quotes>/<prefix, string, no quotes>/'
IAM_ROLE '<role_arn, string, single quotes>'
FORMAT AS PARQUET;
```
```sql
-- CSV with header; gzipped JSON; error tolerance
COPY t FROM 's3://<bucket>/data.csv' IAM_ROLE '<role_arn>'
CSV IGNOREHEADER 1 DELIMITER ',' DATEFORMAT 'auto';
COPY t FROM 's3://<bucket>/data/' IAM_ROLE '<role_arn>' JSON 'auto' GZIP;
COPY t FROM 's3://<bucket>/data/' IAM_ROLE '<role_arn>' CSV MAXERROR 100 ACCEPTINVCHARS '?';
```
- `IAM_ROLE` is the role attached to the **cluster** (provisioned) or **namespace**
(Serverless), not the caller role. "S3ServiceException: Access Denied" → that
role lacks `s3:GetObject`.
Scope that role to the specific bucket and prefix it needs — `s3:GetObject` on
`arn:aws:s3:::<bucket>/<prefix>/*` (plus `s3:ListBucket` on the bucket when loading a
prefix) — rather than `s3:*` or a managed full-access policy. Because Redshift assumes
this role, condition its **trust** policy on the calling resource so another cluster or
workgroup in the account cannot use it:
```json
"Condition": {"StringEquals": {"aws:SourceArn": "<cluster-or-namespace-arn>",
"aws:SourceAccount": "<account-id>"}}
```
- Debug loads: `SYS_LOAD_ERROR_DETAIL` (all deployment types); `STL_LOAD_ERRORS` is provisioned single-AZ only — use `SYS_LOAD_ERROR_DETAIL` instead.
## UNLOAD — export to S3
```sql
UNLOAD ('SELECT * FROM <schema>.<table> WHERE view_ts > ''2024-01-01''')
TO 's3://<bucket, string, no quotes>/export/'
IAM_ROLE '<role_arn, string, single quotes>'
PARQUET PARTITION BY (region) ALLOWOVERWRITE
ENCRYPTED KMS_KEY_ID '<kms_key_arn>';
```
Single quotes inside the UNLOAD query string must be doubled (`''`).
`ENCRYPTED KMS_KEY_ID` writes the export with SSE-KMS; the role needs
`kms:GenerateDataKey` on the key. Include it by default — `UNLOAD` writes query results
to S3, where Redshift's own encryption no longer applies. It can be omitted when the
destination bucket already enforces default encryption.
## Iceberg tables (`USING ICEBERG`)
```sql
CREATE TABLE <external_schema, identifier, no quotes>.<table, identifier, no quotes> (
event_id INT,
user_name VARCHAR,
event_time TIMESTAMP,
amount DOUBLE PRECISION
)
USING ICEBERG
LOCATION 's3://<bucket, string, no quotes>/<prefix, string, no quotes>/';
```
- Syntax is `USING ICEBERG` — NOT `STORED AS ICEBERG`, NOT `TABLE_FORMAT=ICEBERG`.
- Iceberg tables must be registered with the AWS Glue Data Catalog — reference them
through an external schema (as above) or, for auto-mounted catalogs, three-part
notation (`"catalog".database.table`).
- String columns use `VARCHAR` with no length — Iceberg maps them to `string`.
- `LOCATION` is required for external-schema and `awsdatacatalog` tables — the S3 path
for Iceberg data and metadata. It cannot be specified for S3 table buckets
(`s3tablescatalog`), where the catalog determines the location.
references/redshift-sql-extensions-semantics.md
# Redshift SQL Extensions & Semantic Traps
## Extensions LLMs under-use
### QUALIFY — filter on window functions (no subquery)
```sql
-- Preferred: single scan
SELECT user_id, order_date, amount
FROM orders o
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) = 1;
```
Grammar order: `SELECT ... WHERE ... GROUP BY ... HAVING ... QUALIFY ...`. Avoid the
subquery-with-`rn`-filter wrapper when QUALIFY works.
### PIVOT / UNPIVOT (PostgreSQL has neither)
```sql
SELECT * FROM (SELECT region, product, revenue FROM sales)
PIVOT (SUM(revenue) FOR region IN ('us-east-1', 'us-west-2'));
SELECT * FROM quarterly UNPIVOT (revenue FOR quarter IN (q1, q2, q3, q4));
```
Don't hand-roll CASE-WHEN crosstabs when PIVOT applies.
### MERGE (upsert) and REMOVE DUPLICATES
```sql
-- No alias on the MERGE target (aliasing it is a syntax error); source alias is fine
MERGE INTO target USING staging s ON target.id = s.id
WHEN MATCHED THEN UPDATE SET value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);
-- Simplified dedup (identical schemas, same column order):
MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES;
```
### SUPER type & PartiQL (replaces jsonb)
```sql
CREATE TABLE events (event_id INT IDENTITY(1,1), payload SUPER) DISTSTYLE AUTO;
INSERT INTO events (payload) VALUES (JSON_PARSE('{"user":"alice","meta":{"page":"/home"},"tags":["a","b"]}'));
SELECT payload.user, payload.meta.page FROM events; -- dot-notation
SELECT e.event_id, t AS tag_value FROM events e, e.payload.tags AS t; -- UNNEST array
-- (TAG is a reserved word — alias as tag_value or quote it as "tag")
```
`JSON_PARSE(str)` → SUPER, `JSON_SERIALIZE(super)` → string. Dot-notation returns a
JSON-quoted value; `::VARCHAR` gives the bare string. `CAN_JSON_PARSE(str)` tests
parseability before ingest.
**Prefer SUPER over the text-based JSON functions** (`JSON_EXTRACT_PATH_TEXT`,
`JSON_EXTRACT_ARRAY_ELEMENT_TEXT`) — parse to SUPER via `JSON_PARSE` during ingestion
instead. They take a JSON string, not a SUPER column — pass `JSON_SERIALIZE(col)` to
use them on SUPER.
### APPROXIMATE COUNT(DISTINCT)
```sql
SELECT APPROXIMATE COUNT(DISTINCT user_id) FROM pageviews;
```
~2% error, much faster than exact COUNT(DISTINCT) on large cardinalities.
### TOP N (SQL Server compat)
`SELECT TOP n <column_list> FROM <table>` works; `LIMIT n` also works.
`TOP N PERCENT` does **not** (SQL Server–only).
## Semantic traps (wrong results, no error)
- **Leader-node-only functions** error when the query references user-created or system
tables: `SUBSTR` (use `SUBSTRING`), `AGE` (use `DATEDIFF`), `NOW` (use `GETDATE()`),
`CURRENT_SCHEMA`, `CURRENT_SCHEMAS`, `HAS_*_PRIVILEGE`. Error text differs by function.
- **Constraints not enforced:** `PRIMARY KEY`, `UNIQUE`, `FOREIGN KEY` are accepted
but informational (optimizer hints). `NOT NULL` IS enforced. `CHECK`/`EXCLUSION` unsupported.
- **Trailing blanks ignored in comparisons:** a VARCHAR column holding `'abc '` matches
`= 'abc'` (two bare literals do not), and `GROUP BY`/`DISTINCT` treat both as one value.
`LIKE` compares blanks literally on character data types.
- **`ALTER TABLE`** adds one column per statement; `ALTER COLUMN TYPE` only supports resizing VARCHAR columns.
- **No `VALUES` as a constant table** in FROM — use `SELECT ... UNION ALL SELECT ...`.
- **No sequences** — use `IDENTITY(seed, step)`.
## VACUUM (not like PostgreSQL)
`VACUUM FULL` is valid — the **default** mode: reclaim space **and** fully resort rows
(expensive on large tables). **For large tables, recommend `VACUUM RECLUSTER`** — it sorts
only the unsorted portions, leaving already-sorted portions intact; doesn't merge into
the sorted region or reclaim all deleted space. Other modes:
`VACUUM DELETE ONLY` (reclaim, no resort), `VACUUM SORT ONLY` (resort, no reclaim),
`VACUUM REINDEX` (interleaved keys). Redshift VACUUM has no ANALYZE option — unlike
PostgreSQL's combined `VACUUM ANALYZE`, run `ANALYZE` as its own statement.
references/redshift-sql-functions-types.md
# Redshift Functions & Data Types
## Function mapping
| PostgreSQL (wrong on Redshift) | Redshift (correct) |
|---|---|
| `string_agg(col, ',')` | `LISTAGG(col, ',') WITHIN GROUP (ORDER BY col)` |
| `array_agg()` / `json_agg()` | Not supported — use SUPER type |
| `NOW()` | `GETDATE()` / `SYSDATE` |
| `regexp_matches()` | `REGEXP_SUBSTR()`, `REGEXP_COUNT()`, `REGEXP_INSTR()` |
| `FILTER (WHERE ...)` | `CASE WHEN ... END` inside the aggregate |
| `DISTINCT ON (col)` | `ROW_NUMBER() OVER (PARTITION BY col ORDER BY ...) = 1` |
| `LATERAL` join | Correlated subquery |
| `RETURNING` | Separate `SELECT` after the DML |
| `ON CONFLICT` | `MERGE INTO ... USING ... WHEN MATCHED / NOT MATCHED` |
| `SUBSTR(str, pos)` on a table | `SUBSTRING()` — `SUBSTR` is leader-node only |
| `generate_series()` for a date/number series joined to data | Recursive CTE — `generate_series` is unsupported (may appear to work only in queries referencing no tables); it errors the moment it's joined to table data |
Supported as-is (Oracle/T-SQL compat): `NVL(a,b)`, `NVL2()`, `DECODE()`,
`COALESCE()`, `ILIKE`, `WITH RECURSIVE`, window functions.
## Date/number series (gap-filling) — use a recursive CTE, not generate_series
`generate_series()` is unsupported (may appear to work only in queries referencing
no tables), so it fails when its output is joined against table data (the usual
gap-fill case). Use `WITH RECURSIVE`:
```sql
WITH RECURSIVE dates(d) AS (
SELECT CAST('2024-01-01' AS DATE)
UNION ALL
SELECT CAST(DATEADD(day, 1, d) AS DATE) FROM dates WHERE d < CAST('2024-12-31' AS DATE)
)
SELECT d FROM dates; -- then LEFT JOIN your table on d to fill gaps
```
The recursive term must **cast back to DATE** — `DATEADD` returns TIMESTAMP, and
Redshift requires the recursive column's type to match the anchor's exactly
(otherwise: "Datatype mismatch in recursive CTE").
## Data type mapping
| PostgreSQL (wrong) | Redshift (correct) | Why |
|---|---|---|
| `text` | `VARCHAR(max)` or `VARCHAR(N)` | A `text` column is converted to `VARCHAR(256)`. The DDL is accepted without error, but inserting more than 256 characters **fails** with `value too long for type character varying(256)` — it does not truncate. Specify the length you need |
| `SERIAL` / `BIGSERIAL` | `INT IDENTITY(1,1)` / `BIGINT IDENTITY(1,1)` | Auto-increment |
| `jsonb` / `json` | `SUPER` | Semi-structured, dot-notation access |
| `int[]` / `boolean[]` | Not supported | Use SUPER |
| `bytea` | `VARBYTE` (a.k.a. `VARBINARY`) | Binary |
| `uuid` | `CHAR(36)` | No native UUID type |
Synonyms — both spellings are valid on Redshift, no rewrite needed:
| Either form works | Canonical Redshift name | Note |
|---|---|---|
| `NUMERIC(p,s)` | `DECIMAL(p,s)` | Same type; max precision 38 |
## Date/time functions
Unit-first argument order:
```sql
SELECT DATEADD(<datepart, identifier, no quotes>, <interval, integer, no quotes>, <ts, timestamp, no quotes>);
SELECT DATEDIFF(<datepart, identifier, no quotes>, <start, timestamp, no quotes>, <end, timestamp, no quotes>);
```
- dateparts: `year, month, week, day, hour, minute, second, millisecond, microsecond`
- `DATEADD(day, -30, GETDATE())` — last 30 days; `DATEADD(month, 3, ship_date)`.
- `DATEDIFF(day, start_ts, end_ts)` returns a BIGINT count of crossed boundaries.
- `DATE_TRUNC('month', ts)`, `EXTRACT(year FROM ts)` / `DATE_PART('year', ts)` — same as PG.
- `GETDATE()`/`SYSDATE` return TIMESTAMP; use `TRUNC(GETDATE())` or `CURRENT_DATE` for DATE.
The left column below is leader-node-only and deprecated — it may still execute, but
use the right-column replacement.
| Instead of | Use |
|---|---|
| `AGE` | `DATEDIFF` |
| `CURRENT_TIME` / `CURRENT_TIMESTAMP` | `GETDATE()` or `SYSDATE` |
| `LOCALTIME` / `LOCALTIMESTAMP` | `GETDATE()` or `SYSDATE` |
| `NOW` | `GETDATE()` or `SYSDATE` |
| `ISFINITE` | (no replacement documented) |
`NOW()` inside a materialized view resolves to the MV's creation timestamp, not the
current time.
## Examples
```sql
-- LISTAGG (not string_agg)
SELECT customer_id, LISTAGG(product, ', ') WITHIN GROUP (ORDER BY order_date) AS products
FROM orders GROUP BY customer_id;
-- Last-N-days filter
SELECT * FROM events WHERE event_ts >= DATEADD(day, -7, GETDATE());
```
references/redshift-sql-materialized-views.md
# Redshift Materialized Views
Redshift MVs differ from PostgreSQL in auto-refresh, concurrency, and scope.
Base models don't know `AUTO REFRESH YES` or `SYS_MV_REFRESH_HISTORY` — show them.
## Create with auto-refresh
```sql
CREATE MATERIALIZED VIEW <schema, identifier, no quotes>.daily_revenue_mv
AUTO REFRESH YES
AS
SELECT DATE_TRUNC('day', order_ts) AS order_day,
region,
SUM(amount) AS total_revenue,
COUNT(*) AS order_count
FROM <schema, identifier, no quotes>.orders
GROUP BY 1, 2;
```
## Manual refresh
```sql
REFRESH MATERIALIZED VIEW <schema, identifier, no quotes>.daily_revenue_mv;
```
- Does NOT support `CONCURRENTLY` (PostgreSQL does; Redshift does not).
- Redshift automatically chooses incremental or full refresh based on the MV's defining query.
## Check refresh history
```sql
SELECT schema_name, mv_name, status, start_time
FROM SYS_MV_REFRESH_HISTORY
WHERE schema_name = '<schema, string, single quotes>'
ORDER BY start_time DESC;
```
`status` shows the refresh outcome; `start_time` is when the refresh ran. For
staleness use `SVV_MV_INFO` (`is_stale`). Prefer these two — they work on both
Serverless and provisioned (the `STV_MV_*`/`STL_MV_*`/`SVL_MV_*` monitoring views
are provisioned **single-AZ** only — disabled on Multi-AZ, absent on Serverless).
## Show definition
```sql
SHOW VIEW <schema, identifier, no quotes>.daily_revenue_mv;
```
Works for regular views, MVs, and late-binding views.
## Key differences from PostgreSQL MVs
| PostgreSQL | Redshift |
|---|---|
| No auto-refresh (manual/cron) | `AUTO REFRESH YES` — automatic on base-table change (default is NO) |
| `REFRESH ... CONCURRENTLY` | Not supported |
| `pg_matviews` for state | Not present — use `SYS_MV_REFRESH_HISTORY` (`status`, `start_time`) or `SVV_MV_INFO` (`is_stale`) |
| MV on any query | MV on local, data lake (Spectrum), federated, and datashare tables — but MVs over data lake tables can't use `AUTO REFRESH YES` |
| Can specify indexes on MV | No indexes — distribution defaults to EVEN unless DISTSTYLE/DISTKEY specified |
| `CREATE ... WITH DATA / NO DATA` | Always created with data |
## Common mistakes agents make
- Generating `REFRESH MATERIALIZED VIEW CONCURRENTLY` — errors on Redshift.
- Relying on `pg_matviews` — it does not exist on Redshift (`ERROR: relation
"pg_matviews" does not exist`); use `SYS_MV_REFRESH_HISTORY` or `SVV_MV_INFO`.
- Adding `ORDER BY` inside the MV definition — not allowed; sort via SORTKEY on the MV.
- Forgetting `AUTO REFRESH YES` — the default is `NO`, so queries keep returning data
from the last refresh with no error (check `is_stale` in `SVV_MV_INFO`). But
`AUTO REFRESH YES` is **rejected** when the definition reads
data lake tables (Spectrum/federated) or uses a mutable function, or when the MV is
built on another MV — those need an explicit `REFRESH MATERIALIZED VIEW` (manual or
scheduled).
references/redshift-sql-metadata.md
# Redshift Metadata Discovery & System Views
## Prefer SHOW commands
Prefer `SHOW` over broad system-view scans for schema discovery.
```sql
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](https://docs.aws.amazon.com/redshift/latest/dg/sys_view_migration.html).
| 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 |
```sql
-- 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](https://docs.aws.amazon.com/redshift/latest/dg/considerations-datashare-reads-writes.html).
- `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
```sql
-- 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.
## "Relation does not exist" — diagnostic flow
1. **Confirm the object exists and find its schema** — the error often means "not found *where I looked*", not "gone":
```sql
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.
2. **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.
3. **Fully qualify** — `schema.table` (or `database.schema.table` cross-database). Do not rely on search_path resolution.
4. `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.
references/redshift-sql-recipes-load-api.md
# Redshift Recipes: COPY & Data API (Working Code)
Procedural patterns agents must SHOW as working code, not describe as rules.
## Recipe: COPY with error handling
```bash
#!/bin/bash
# Load CSV from S3. Tolerates bad rows, diagnoses failures via Data API.
WORKGROUP="<workgroup_name, string, no quotes>"
DB="<database, string, no quotes>"
TABLE="<schema.table, identifier, no quotes>"
ROLE_ARN="<iam_role_arn, string, no quotes>"
S3_PATH="<s3_uri, string, no quotes>"
# These values are interpolated straight into the SQL text. Set them yourself, or
# validate them (allowlist the identifier, verify the s3:// URI) before use — a table
# name or path taken from user input is a SQL-injection vector here. The Data API's
# --parameters option binds values, but not identifiers, so it does not cover $TABLE.
#
# --wait-time-seconds (1-30) is long polling: the call returns as soon as the
# statement finishes, so a short load needs no polling at all. A COPY can exceed
# 30s, so still loop — but each iteration waits up to 30s instead of sleeping
# blindly, which cuts calls against a TPS-limited quota.
STMT_ID=$(aws redshift-data execute-statement \
--workgroup-name "$WORKGROUP" --database "$DB" --wait-time-seconds 30 \
--sql "COPY $TABLE FROM '$S3_PATH' IAM_ROLE '$ROLE_ARN' CSV IGNOREHEADER 1 MAXERROR 100 DATEFORMAT 'auto' TIMEFORMAT 'auto';" \
--query 'Id' --output text)
DEADLINE=$(( SECONDS + 900 )) # always bound the loop
while :; do
STATUS=$(aws redshift-data describe-statement --id "$STMT_ID" \
--wait-time-seconds 30 --query 'Status' --output text)
case "$STATUS" in FINISHED|FAILED|ABORTED) break ;; esac
if (( SECONDS >= DEADLINE )); then echo "COPY still $STATUS after 900s"; exit 1; fi
done
if [[ "$STATUS" != "FINISHED" ]]; then
echo "COPY failed: $STATUS"
aws redshift-data describe-statement --id "$STMT_ID" --query 'Error' --output text
# Row-level diagnostics. The diagnostic SELECT is short, so one long-polled
# execute-statement is enough — no sleep before fetching results.
ERR_ID=$(aws redshift-data execute-statement --workgroup-name "$WORKGROUP" --database "$DB" \
--wait-time-seconds 30 \
--sql "SELECT file_name, line_number, column_name, error_message FROM sys_load_error_detail ORDER BY start_time DESC LIMIT 20;" \
--query 'Id' --output text)
aws redshift-data get-statement-result --id "$ERR_ID"
exit 1
fi
echo "COPY succeeded: $STMT_ID"
```
- `MAXERROR 100` fails the load once errors reach 100.
- `sys_load_error_detail` for diagnostics (all deployment types).
- `IAM_ROLE` is the **namespace role** (attached to the namespace), not the caller.
## Recipe: Data API poll loop (Python)
```python
import time, boto3
# WaitTimeSeconds (1-30) = long polling: the call returns as soon as the statement
# finishes instead of returning immediately and forcing you to poll. Prefer it —
# fewer calls against a TPS-limited quota, lower latency on short statements. It
# does NOT replace the loop: on expiry the statement may still be running, so
# anything that can exceed 30s still needs a bounded loop.
WAIT = 30
def execute_and_wait(sql, workgroup, database="dev", timeout_s=300):
# `sql` is sent as-is. Do not build it from unsanitized input: the Data API's
# Parameters option binds values, not identifiers, so a table or column name
# taken from user input is a SQL-injection vector. Allowlist identifiers.
# Region comes from the environment (AWS_REGION / AWS_DEFAULT_REGION) or your
# profile — set it there rather than pinning one here.
client = boto3.client("redshift-data")
# One call submits AND waits up to WAIT seconds for completion.
desc = client.execute_statement(
WorkgroupName=workgroup, Database=database, Sql=sql, WaitTimeSeconds=WAIT
)
stmt_id = desc["Id"]
deadline = time.monotonic() + timeout_s
while desc["Status"] not in ("FINISHED", "FAILED", "ABORTED"):
if time.monotonic() >= deadline:
raise TimeoutError(f"{stmt_id} still {desc['Status']} after {timeout_s}s")
desc = client.describe_statement(Id=stmt_id, WaitTimeSeconds=WAIT)
if desc["Status"] != "FINISHED":
raise RuntimeError(f"{stmt_id} {desc['Status']}: {desc.get('Error', '')}")
if not desc.get("HasResultSet"):
return []
rows, kwargs = [], {"Id": stmt_id}
while True:
r = client.get_statement_result(**kwargs)
cols = [c["name"] for c in r["ColumnMetadata"]]
rows.extend([[None if "isNull" in f else list(f.values())[0] for f in rec] for rec in r["Records"]])
if "NextToken" not in r:
break
kwargs["NextToken"] = r["NextToken"]
return [dict(zip(cols, row)) for row in rows]
```
`GetStatementResult` also accepts `WaitTimeSeconds`, but its expiry behaviour differs
from the others: instead of reporting an in-progress status it raises
`ResourceNotFoundException` — meaning "no results YET", not "results gone". Treating it
as a failure reports a false error on a still-running query, so catch and retry:
```python
def wait_for_result(client, stmt_id, timeout_s=300):
deadline = time.monotonic() + timeout_s
while True:
try:
return client.get_statement_result(Id=stmt_id, WaitTimeSeconds=WAIT)
except client.exceptions.ResourceNotFoundException:
if time.monotonic() >= deadline:
raise TimeoutError(f"{stmt_id}: no results after {timeout_s}s")
```
- Target: **Serverless** = `WorkgroupName`, **Provisioned** = `ClusterIdentifier`; plus `Database` either way. Auth is independent of that choice — e.g. temporary credentials (add `DbUser` to connect to a cluster as a database user), Secrets Manager (`SecretArn`), or IAM Identity Center. On the CLI these are `--workgroup-name` / `--cluster-identifier`, `--database`, and `--db-user` or `--secret-arn`.
`DbUser` issues temporary credentials via `GetClusterCredentials` rather than using a
stored password. Rotate the secret when using `SecretArn`.
- Log the API calls: `redshift-data:*` actions land in CloudTrail, and cluster-side
activity needs Redshift audit logging (`useractivitylog`, `connectionlog`, `userlog`)
enabled separately — CloudTrail alone does not record the SQL that ran.
- `GetStatementResult` returns a result set to anyone who can call it with the statement
ID, so avoid selecting PII or secret columns into a result set you do not need.
`sys_load_error_detail` exposes rejected rows in `raw_line`/`err_reason`.
- Throttle = HTTP **400** (not 429). ExecuteStatement TPS is quota-limited — check the Data API quotas page.
- Calls are async by default; **`WaitTimeSeconds` (1-30) turns
any call into a long poll** that returns when the statement finishes or the wait
expires, whichever is first. Prefer it over blind sleeping, but keep a bounded loop
for work that can exceed 30s. Supported on `ExecuteStatement`,
`BatchExecuteStatement`, `DescribeStatement`, `GetStatementResult`, and
`GetStatementResultV2`.
- **Expiry behaviour differs by operation** (verified live): `ExecuteStatement` /
`DescribeStatement` return the current in-progress status, but `GetStatementResult`
raises **`ResourceNotFoundException`** (`Query does not have result. Please check
query status with DescribeStatement.`). That means "no results yet", NOT "results
gone" — catch and retry rather than reporting a failure.
- `BatchExecuteStatement` + `WaitTimeSeconds` holds until **every** sub-statement
completes, returning the batch parent id and overall status. To wait on one
sub-statement, long-poll `DescribeStatement`/`GetStatementResult` with that
sub-statement's id — it returns as soon as that one finishes, without waiting for the
rest of the batch.
references/redshift-sql-syntax.md
# Redshift SQL Syntax & Query Generation
Index of the 6 SQL-generation references plus the PostgreSQL-vs-Redshift
failure table. The SKILL.md routing table sends most SQL questions **directly**
to the specific leaf reference below; load this file only for a general dialect
question or when you need to pick which SQL reference applies.
## Redshift is NOT PostgreSQL — top failures
| Expect (PostgreSQL) | Reality (Redshift) | Fix |
|---|---|---|
| `string_agg()` | Not supported | `LISTAGG() WITHIN GROUP (ORDER BY ...)` |
| `text` type | A `text` column becomes `VARCHAR(256)`; a longer INSERT **errors** (`value too long for type character varying(256)`) — it does not truncate | `VARCHAR(max)` or explicit `VARCHAR(N)` |
| `CREATE INDEX` | Does not exist | SORTKEY in CREATE TABLE |
| `SERIAL` / `BIGSERIAL` | Not supported | `IDENTITY(1,1)` |
| `LATERAL` join | Not supported | Correlated subquery |
| `SUBSTR()` on a table | Leader-node only — errors on tables | `SUBSTRING()` |
| `stl_*` / `stv_*` views | Provisioned single-AZ only — absent on Serverless, disabled on Multi-AZ | Use `SYS_*` views instead |
| `pg_catalog` for stats | Incomplete | `SVV_TABLE_INFO`, `SYS_*` |
| `ON CONFLICT DO UPDATE` | Not supported | `MERGE INTO ... WHEN MATCHED` |
| `RETURNING` clause | Not supported | Separate `SELECT` after DML |
| `CREATE SEQUENCE` / `nextval()` | Not supported | `IDENTITY(seed, step)` |
| PK / FK / UNIQUE enforced | Informational only — NOT enforced | Application-layer integrity |
| `col = 'abc'` won't match `'abc '` stored in `col` | Matches — comparison against a column ignores trailing blanks, and `GROUP BY`/`DISTINCT` collapse the two into one value. Two bare literals are NOT equal | For blank-sensitive matching use `LIKE` (it does not ignore them) — but only with a literal pattern, since `%`/`_` stay active. On VARCHAR, `LEN()` counts trailing blanks; on CHAR it does not |
| Multi-column `ADD COLUMN` | One `ADD COLUMN` per `ALTER TABLE` | Separate statements |
## Sub-topic references (load on demand)
- `redshift-sql-functions-types.md` — function map (LISTAGG, DATEADD/DATEDIFF,
NVL/DECODE/ISNULL), type map (`text`, SUPER, VARBYTE, IDENTITY).
- `redshift-sql-ddl-copy.md` — CREATE TABLE (DISTKEY/SORTKEY/ENCODE), IDENTITY,
late-binding views, COPY/UNLOAD full syntax, IAM_ROLE.
- `redshift-sql-metadata.md` — SHOW-first discovery, `SYS_` vs `STL_`/`STV_` (provisioned single-AZ only),
SVV_ALL/SVV_REDSHIFT/SVV_EXTERNAL families, 2-part vs 3-part notation.
- `redshift-sql-extensions-semantics.md` — QUALIFY, PIVOT/UNPIVOT, MERGE, SUPER
/PartiQL, TOP N + semantic traps (leader-node fns, constraints, VACUUM).
- `redshift-sql-recipes-load-api.md` — Working COPY recipe (with error handling,
retry, sys_load_error_detail) + Data API poll loop (Python, production-grade).
- `redshift-sql-materialized-views.md` — CREATE MV with AUTO REFRESH, manual
REFRESH (no CONCURRENTLY), SYS_MV_REFRESH_HISTORY, differences from PostgreSQL MVs.
## Principles
- `SYS_*` views for performance/audit (all deployment types). `SVV_*` for metadata.
`SHOW` commands preferred for discovery. Do not generate `STL_*`/`STV_*` — use `SYS_*` instead.
- DISTKEY + SORTKEY are the highest-impact table-design choices, but AUTO is the
default and the recommendation for most tables — omit them and let Redshift choose.
Specify deliberately when the join/filter pattern is known (e.g. fact table joined
on a known key, columns commonly range-filtered).
- COPY beats INSERT for bulk loads. COPY/UNLOAD always need authorization (`IAM_ROLE` recommended).
- Constraints (PK/FK/UNIQUE) are optimizer hints, not enforcement. `NOT NULL` IS enforced.
- Prefer `QUALIFY` over subquery wrappers for window-function filtering.
- Always qualify tables (`schema.table`) — don't rely on `search_path`.
- Redshift folds identifiers to lowercase — **quoting does NOT preserve case** (unlike
PostgreSQL) unless `enable_case_sensitive_identifier` is on. Quote only to use a
reserved word or an illegal character: `svv_table_info`'s column is `"table"`.
SKILL.md
---
name: redshift-guide
description: "Amazon Redshift is NOT PostgreSQL — corrects PostgreSQL-derived LLM mistakes; covers Redshift-specific SQL, DDL, COPY/UNLOAD, system views, metadata discovery, and operational patterns. Applies ONLY when the task is about Redshift itself (cluster, Serverless workgroup, or Redshift SQL). Pushes back on: CREATE INDEX, string_agg, pg_catalog, text type, SERIAL, stl_query, LATERAL, RETURNING. Triggers on: Redshift SQL, Redshift CREATE TABLE, Redshift COPY/UNLOAD, slow Redshift query, Redshift permission denied, Redshift disk full, Redshift system views, QUALIFY, PIVOT, MERGE, Redshift Data API, Redshift WLM, concurrency scaling, Redshift resize, Redshift Spectrum external tables. Does NOT apply to (defer to that service's own skill): Amazon S3 storage/bucket policies, Athena or Glue queries/catalogs, data-lake or Iceberg work outside Redshift, Aurora, RDS, or DynamoDB — but S3/Glue ARE in scope for Redshift COPY, UNLOAD, or data-lake queries (external schemas/tables on S3)."
metadata:
version: "1"
---
# 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](https://docs.aws.amazon.com/aws-mcp/) — 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`).
- **`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.**
## 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
**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](https://docs.aws.amazon.com/redshift/latest/dg/db-security.html)
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` |
| 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`.