Redshift SQL Ddl Copy

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.

COPY — load from S3 (Redshift-specific, not standard SQL)

• 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:

  jsonCopy

  "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

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)

• 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.