Amazon Redshift Publishing

LakeXpress creates Amazon Redshift tables from exported Parquet files stored in S3, supporting both native internal tables and Spectrum external tables.

Prerequisites

1. Redshift Cluster

A Redshift Provisioned cluster or Redshift Serverless workgroup with:

• A database (default: dev)
• A user with CREATE SCHEMA and CREATE TABLE privileges
• Security group allowing inbound TCP on port 5439

2. IAM Role

An IAM role that Redshift can assume, with:

• S3 read access on the bucket containing exported Parquet files (required for both internal and external tables)
• AWS Glue catalog access (required only for external/Spectrum tables)

Example trust policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {"Service": "redshift.amazonaws.com"},
      "Action": "sts:AssumeRole"
    }
  ]
}

3. S3 Storage

Both the Redshift cluster and the S3 bucket must be in the same AWS region to avoid cross-region transfer costs.

The S3 credential and the Redshift credential must both be in your credentials.json:

{
  "s3_01": {
    "ds_type": "s3",
    "auth_mode": "profile",
    "info": {
      "directory": "s3://my-bucket/lakexpress/",
      "profile": "lakexpress"
    }
  },
  "redshift_classic": {
    "ds_type": "redshift",
    "auth_mode": "classic",
    "info": {
      "host": "my-cluster.eu-west-1.redshift.amazonaws.com",
      "port": 5439,
      "database": "dev",
      "username": "$env{LX_REDSHIFT_USER}",
      "password": "$env{LX_REDSHIFT_PASSWORD}",
      "iam_role": "arn:aws:iam::123456789012:role/MyRedshiftRole",
      "region": "eu-west-1"
    }
  }
}

Authentication Modes

Classic (Username/Password)

{
  "redshift_classic": {
    "ds_type": "redshift",
    "auth_mode": "classic",
    "info": {
      "host": "my-cluster.eu-west-1.redshift.amazonaws.com",
      "port": 5439,
      "database": "dev",
      "username": "$env{LX_REDSHIFT_USER}",
      "password": "$env{LX_REDSHIFT_PASSWORD}",
      "iam_role": "arn:aws:iam::123456789012:role/MyRedshiftRole",
      "region": "eu-west-1"
    }
  }
}

Field	Required	Description
host	Yes	Cluster endpoint
port	No	Default: 5439
database	No	Default: dev
username	Yes	Database user
password	Yes	Database password
iam_role	Yes	IAM role ARN for S3/Glue access
region	No	AWS region (default: us-east-1)

IAM

{
  "redshift_iam": {
    "ds_type": "redshift",
    "auth_mode": "iam",
    "info": {
      "host": "my-cluster.eu-west-1.redshift.amazonaws.com",
      "port": 5439,
      "database": "dev",
      "cluster_identifier": "my-cluster",
      "iam_role": "arn:aws:iam::123456789012:role/MyRedshiftRole",
      "region": "eu-west-1"
    }
  }
}

Field	Required	Description
cluster_identifier	Yes	Redshift cluster identifier
iam_role	Yes	IAM role ARN
region	No	AWS region (default: us-east-1)

Table Types

Internal Tables (--publish_method internal)

Creates native Redshift tables and loads data using COPY FROM S3 FORMAT AS PARQUET.

• Data is copied into Redshift storage -- queries are fast but storage costs apply
• The COPY command reads all Parquet files in the S3 directory (including partitioned exports)
• Decimal columns preserve source precision and scale (e.g., DECIMAL(12,2)) -- the same mapping used for external tables; DECIMAL(38,18) is used only when the source has no declared precision
• Supports --pk_constraints for informational primary key constraints

External Tables (--publish_method external)

Creates Redshift Spectrum tables backed by the AWS Glue Data Catalog.

• Data stays in S3 -- no data movement, no Redshift storage costs
• Requires an IAM role with Glue catalog permissions
• The Glue database is created automatically (CREATE EXTERNAL DATABASE IF NOT EXISTS)
• Decimal columns preserve source precision (e.g., DECIMAL(12,2)) -- same mapping used for internal tables
• PK constraints are not supported for external tables

Dynamic Naming Patterns

Token	Description
{schema}	Source schema name
{table}	Source table name
{database}	Source database name
{subpath}	Value of --sub_path
{date}	Current date (YYYYMMDD)
{timestamp}	Current timestamp (YYYYMMDD_HHMMSS)
{uuid}	UUID4 identifier (consistent per run)

Set via --publish_schema_pattern and --publish_table_pattern. Default: {schema} for --publish_method internal, EXT_{schema} for the default external method (unless set explicitly); --publish_table_pattern defaults to {table}.

Usage Examples

Ready-to-run commands for Amazon Redshift are in Examples & Recipes: Amazon Redshift.

Primary Key Constraints

When --pk_constraints is set, LakeXpress adds PRIMARY KEY constraints to internal table DDL based on the source table's primary key columns.

These constraints are informational only in Redshift -- they are not enforced but are used by the query optimizer for better execution plans.

PK constraints are not supported for external (Spectrum) tables.

Notes and Limitations

• COPY parallelism: The COPY command automatically distributes file reads across Redshift compute slices. Multi-file exports (partitioned tables) benefit from multi-node clusters.
• Decimal mapping: Internal and external (Spectrum) tables use identical decimal mapping -- both preserve the source's precision and scale (e.g. DECIMAL(12,2)). DECIMAL(38,18) is used only as a fallback when the source type reports no precision (e.g. a bare NUMBER).
• Region: The Redshift cluster and S3 bucket should be in the same AWS region to avoid cross-region data transfer costs.
• Glue database: For external tables, the CREATE EXTERNAL SCHEMA DDL automatically creates the backing Glue database if it does not exist.
• Redshift Serverless: Both Provisioned and Serverless clusters are supported. The connection uses the same host/port/user/password parameters.
• Driver: Requires redshift_connector. Install with pip install redshift_connector.

See Also

• Compatibility - Full list of supported platforms
• Storage Configuration: S3 - S3 credential setup
• Amazon Redshift Reference - Data type mappings, CLI arguments, and querying