Skip to content
OWOX Data Marts

AWS Redshift

Use this guide to configure AWS Redshift as a storage in OWOX Data Marts. The steps below walk you through creating a storage record, completing authentication, and finishing validation.

1. Go to the Storages Page and choose Storage Type

Section titled “1. Go to the Storages Page and choose Storage Type”

In the OWOX Data Marts web application, navigate to Storages from the main navigation pane and click + New Storage. Click AWS Redshift to create a new Storage configuration.

Upon selecting the + New Storage button and specifying the desired storage type, a Storage entry is created. You can create Data Mart entities and model a data structure for your project prior to configuring the Storage. Note that Data Mart cannot be validated or published until the associated Storage is fully configured.

OWOX Data Marts interface showing the Storages menu option highlighted with a red arrow in the left sidebar, and the New Storage dialog displaying available storage types including Google BigQuery, AWS Athena, Snowflake, AWS Redshift, and Databricks, with AWS Redshift circled in red and a New Storage button indicated by a red arrow in the top right

2. Add title and AWS region

Section titled “2. Add title and AWS region”

Give the storage configuration a clear title, for example: AWS Redshift Dev Database.

Choose the AWS region where your Redshift cluster or workgroup is located:

  • Examples: us-east-1, eu-west-1, ap-southeast-1
  • You can find your region in the AWS Redshift Console in the top right corner or url.

AWS Redshift Serverless dashboard showing the region selector dropdown menu open in the top right corner. The dropdown displays available regions organized by geographic location including Europe (Stockholm, Frankfurt, Ireland, London, Paris), Asia Pacific (Sydney, Tokyo, ap-southeast-2, ap-northeast-1), Canada (Central, ca-central-1), and South America (São Paulo, sa-east-1) with Europe (Stockholm) currently selected and highlighted with a red circle and arrow indicating the region selection location.

OWOX Data Marts Configure Storage Provider dialog showing General and Connection Settings sections. The General section contains fields for Title (AWS Redshift Dev Database) and Storage Type (AWS Redshift). The Connection Settings section displays a Region field with eu-north-1 entered. Red arrows point to the Title field and Region field. The dialog includes an info link asking What is AWS Redshift and What is the AWS region. A blue Save button and Cancel button are visible at the bottom of the dialog against a dark background.

Choose Connection Type

Section titled “Choose Connection Type”

AWS Redshift supports two deployment types (one is required). It’s based on your use case and pricing model.

Option 1: Serverless

Section titled “Option 1: Serverless”

Workgroup Name:

AWS Redshift Serverless console showing the Workgroup configuration page. The left sidebar displays menu options including Serverless dashboard, Query editor v2, Amazon SageMaker Unified Studio, and Workgroup configuration highlighted with a red arrow. The main content area shows a Workgroups table with one workgroup named owox circled in red, displaying its status as Available and creation date in December. The interface includes a Create workgroup button and search functionality for finding workgroups.

Option 2: Provisioned

Section titled “Option 2: Provisioned”

Cluster Identifier:

AWS Redshift Provisioned Clusters console displaying the clusters list page. The interface shows a Clusters (1) section with a table containing one cluster entry. The cluster named redshift-cluster-1 is circled in red and highlighted, showing details including its status as Available, cluster namespace, and region eu-north-1. The top navigation shows Amazon Redshift > Clusters with In my account and From other accounts tabs. Action buttons including Create cluster, Query data, and Actions are visible in the top right. A search field labeled Find clusters is present for filtering clusters.

Enter Database Name

Section titled “Enter Database Name”
  • This is the name of the database within your Redshift cluster
  • Default Redshift database is typically dev or defaultdb
  • You can find this in the AWS Redshift Query Editor

AWS Redshift Query Editor v2 interface displaying the database selection panel on the left sidebar. The panel shows a hierarchical structure with Serverless: owox and redshift-cluster-1 deployments, followed by a native databases section containing the dev database highlighted in blue and sample_data_dev database below it. External databases section is collapsed at the bottom. The main editor area on the right shows an empty query window with Run, Limit 100, Explain, and Isolated session options visible.

Authentication

Section titled “Authentication”

OWOX Data Marts uses AWS IAM credentials to authenticate with Redshift Data API.

Access Key ID and Secret Access Key

Section titled “Access Key ID and Secret Access Key”

How to create IAM credentials:

  1. Go to AWS IAM Console
  2. Navigate to Users → Select your user or create a new one
  3. Click Create access key
  4. Choose Application running outside AWS
  5. Copy the Access key and Secret access key or download .csv file with access keys.

Security Best Practice:

  • Never share your secret access key
  • Store it securely (use AWS Secrets Manager or similar)
  • Rotate keys regularly
  • Use IAM policies to grant minimum required permissions

AWS IAM console showing the owox-user identity summary with ARN, creation date, and console access status. The Security credentials tab is open displaying the Console sign-in section. On the right side, under Access key 1, a blue Create access key link is visible with a red arrow indicating the location to click for generating new credentials. The interface shows a professional AWS management console layout with navigation menu on the left.

AWS Create Access Key dialog showing Step 1: Access key best practices and alternatives. The page explains to avoid long-term credentials and presents use case options including Command Line Interface, Local code, Application running on AWS compute service, Third-party service, and Application running outside AWS, which is selected with a blue highlight and red arrow. The interface has a professional layout with step navigation on the left side and informational content on the right.

AWS IAM Access keys retrieval page displaying the newly created access key credentials, showing the Access Key ID and Secret Access Key fields with copy icons. The page indicates this is Step 3 of the access key creation process with options to view, download, or manage the credentials in a secure manner.

Required IAM Permissions

Section titled “Required IAM Permissions”

Your IAM user or role needs the following permissions:

For Serverless:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult",
        "redshift-serverless:GetCredentials"
      ],
      "Resource": "*"
    }
  ]
}

For Provisioned Cluster:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult",
        "redshift:GetClusterCredentials"
      ],
      "Resource": "*"
    }
  ]
}

Tip: You can attach the AWS managed policy AmazonRedshiftDataFullAccess for quick setup, but consider using a custom policy with minimal permissions for production. You can set permissions to certain database and tables for more security. Example: "Resource": "arn:aws:redshift:us-east-1:123456789012"

AWS IAM policy creation interface showing Step 1: Specify permissions with a policy editor displaying JSON policy syntax. The policy editor shows Version 2012-10-17 and a Statement array with Allow Effect and Action array containing redshift-data permissions: ExecuteStatement, DescribeStatement, GetStatementResult, and redshift-serverless. The Resource field is set to asterisk. The top navigation shows IAM > Policies > Create policy breadcrumb. Step 2 Review and create is visible in the left sidebar. The interface has a professional AWS management console layout with Visual and JSON tabs, and Actions dropdown buttons in the top right corner.

3. Grand Permissions to create schemas in database

Section titled “3. Grand Permissions to create schemas in database”

If you want to create schemas in your database (to upload data from a connector-based data mart), you need to grant permissions to the IAM user who will upload the data.

GRANT CREATE ON DATABASE dev TO "IAM:<USERNAME_IN_IAM>";

Tip: You can find your username in IAM in the AWS IAM Console in the Users tab.

4. Finalize Setup

Section titled “4. Finalize Setup”

Review your entries and click Save to add the Storage configuration, or Cancel to exit without saving.

Once saved, OWOX Data Marts will validate the connection to ensure all credentials are correct.

OWOX Data Marts Configure Storage Provider dialog displaying the final configuration form. The form shows the region field set to eu-north-1, Workgroup Name field containing owox with Serverless and Provisioned tabs, Database field showing dev, and Authentication section with Access Key ID and Secret Access Key fields. A prominent blue Save button with a red arrow pointing to it is centered at the bottom of the dialog, alongside a Cancel button. The dialog has a dark background with professional layout and informational help icons throughout.

Next Steps

Section titled “Next Steps”

After configuring your AWS Redshift storage:

  1. Create a Data Mart that uses this storage
  2. Define your data structure with Redshift-specific field types
  3. Configure a Connector to load data into Redshift
  4. Run reports and export data from your Redshift tables

Troubleshooting

Section titled “Troubleshooting”

Connection Failed

Section titled “Connection Failed”
  • Verify your AWS region is correct
  • Ensure the database name exists in your Redshift cluster/workgroup
  • Check that workgroup name or cluster identifier is spelled correctly
  • Verify your Access Key ID and Secret Access Key are correct

Permission Denied

Section titled “Permission Denied”

Make sure your IAM user has the required permissions listed above. You can test permissions by running:

Terminal window
aws redshift-data execute-statement \
  --region us-east-1 \
  --database dev \
  --workgroup-name my-workgroup \
  --sql "SELECT 1"

“Schema does not exist” Error

Section titled ““Schema does not exist” Error”
  • Schema is now configured in the Connector setup (Step 5), not in Storage
  • Make sure you’ve entered a schema name when creating the connector
  • The schema will be automatically created if it doesn’t exist

Query Timeout

Section titled “Query Timeout”
  • Redshift Data API has a 5-minute query timeout
  • For large data loads, consider breaking them into smaller batches
  • Check your warehouse/cluster size and scaling settings

Access Denied to Database

Section titled “Access Denied to Database”

Ensure your Redshift database allows access from the IAM credentials:

For Serverless:

GRANT ALL ON DATABASE dev TO "IAM:<USERNAME_IN_IAM>";

For Provisioned:

GRANT ALL ON DATABASE dev TO "IAM:<USERNAME_IN_IAM>";

Filter values and standard_conforming_strings

Section titled “Filter values and standard_conforming_strings”

OWOX Data Marts safely escapes report filter values for Redshift by relying on the default standard_conforming_strings = on setting (a backslash is treated as an ordinary character). This is the default for both Redshift Serverless and provisioned clusters. Do not set standard_conforming_strings = off for the database OWOX connects to.

Additional Resources

Section titled “Additional Resources”