How to Run Your First Amazon Redshift Query (Step-by-Step)

Amazon Redshift querying in plain English

Here is what actually happens when you query Redshift:

• You connect to a Redshift database the same way you would connect to PostgreSQL
• Tables live inside schemas inside that database
• You write standard SQL: SELECT, JOIN, GROUP BY, and so on
• Redshift executes the query across all compute nodes in parallel and returns the combined result

One thing to understand early: Redshift is built for analytics, not for the traffic that backs an app. It is not the right database for “look up this one user’s profile” or “insert this order as the checkout happens.” It is optimised for “give me total revenue by region for the last 12 months across 200 million rows.”

A normal beginner workflow looks like this:

1. Connect to the database
2. Run a tiny test query to confirm the connection is working
3. Create a table with the right column types and distribution keys
4. Load data from S3 using the COPY command
5. Write analytical SQL to answer real questions
6. Use EXPLAIN to inspect and tune slow queries

Before you start

You need the following before any of this works:

• A provisioned Redshift cluster or a Redshift Serverless workgroup in the Available state
• Your database name, username, and password (or an IAM identity with database access configured)
• Access to Query Editor v2 in the AWS console, or a local SQL client such as psql or DBeaver
• If using psql or another direct client: a security group rule that allows inbound TCP on port 5439 from your IP address
• If loading data from S3: an IAM role attached to the cluster that has S3 read permissions, and the role ARN copied somewhere handy

60-second quick start: run your first successful query

Before creating tables or loading data, confirm the connection is working with a tiny query. This is the fastest possible win and it rules out networking and credential problems early.

Open Query Editor v2

1. Go to the AWS console and open the Amazon Redshift service
2. Click Query Editor v2 in the left sidebar
3. Click Connect in the top right corner
4. Select your cluster or serverless workgroup from the dropdown
5. Enter your database name, username, and password, then click Connect to database

You now have a SQL editor in your browser with nothing to install. The left panel shows your databases, schemas, and tables. Write SQL in the editor pane and press Ctrl+Enter or click Run to execute.

Run these three test queries

Copy and run these one at a time. If all three return a result, your connection is working and you are ready to move on.

SELECT current_date;

SELECT current_user;

SELECT version();

If any of these fail, the problem is with the connection itself (credentials, network, or security group) rather than anything to do with your tables or data. Fix the connection before moving on.

Connecting with psql (optional)

If you prefer a terminal-based client or need to script queries, psql works with Redshift because Redshift uses a PostgreSQL-compatible wire protocol.

Install psql via your package manager, then connect with:

psql \
  -h your-cluster.abc123.us-east-1.redshift.amazonaws.com \
  -U admin \
  -d dev \
  -p 5439

Two things to get right: the port is 5439 (not the PostgreSQL default of 5432), and your security group must allow inbound TCP on 5439 from your IP. If the connection hangs or is refused, the security group is almost always the cause.

Creating your first table

Redshift uses standard SQL DDL. Here is a realistic example: a table for e-commerce orders.

CREATE TABLE orders (
  order_id     BIGINT        NOT NULL,
  customer_id  BIGINT        NOT NULL,
  order_date   DATE          NOT NULL,
  region       VARCHAR(50),
  product_id   BIGINT        NOT NULL,
  quantity     INTEGER       NOT NULL,
  unit_price   DECIMAL(10,2) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (order_date);

The two Redshift-specific clauses are worth understanding now rather than later:

• DISTKEY (customer_id) tells Redshift to distribute rows across compute nodes based on the customer ID column. Rows with the same customer ID land on the same node. When you later join this table against a customers table that is also distributed by customer_id, the matching rows are already co-located, which avoids expensive data movement.

• SORTKEY (order_date) means rows are stored sorted by date on disk. When a query filters by a date range, Redshift can skip entire disk blocks outside the range instead of scanning the whole table.

See the Redshift Architecture guide for a full explanation of how distribution and sort keys work across nodes and slices.

Loading data from S3 with COPY

Do not use individual INSERT statements to load data into Redshift. The COPY command reads files from S3 in parallel across all compute nodes and is dramatically faster for any non-trivial dataset.

Here is a complete COPY command for loading a CSV file:

COPY orders
FROM 's3://my-data-bucket/orders/2025/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3ReadRole'
FORMAT AS CSV
IGNOREHEADER 1
DATEFORMAT 'YYYY-MM-DD'
REGION 'us-east-1';

What each option does:

• FROM ‘s3://…’: a prefix loads every file under that path; a full object key loads one specific file
• IAM_ROLE: the ARN of the role attached to your cluster that grants S3 read access
• FORMAT AS CSV: the file format. Other valid options are JSON, PARQUET, and ORC
• IGNOREHEADER 1: skips the first line of each CSV file (the header row)
• DATEFORMAT: the date format used in your files. Use DATEFORMAT ‘auto’ if you are unsure

If COPY fails, query the STL_LOAD_ERRORS system table to see exactly which rows failed and the reason. The Loading Data into Redshift guide covers IAM setup, Parquet and JSON formats, manifest files, and error diagnosis in full.

Running analytical SQL queries

Redshift supports standard SQL with PostgreSQL-compatible extensions. Here are realistic examples using the orders table created above.

Basic count and filter

SELECT COUNT(*) AS total_orders
FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01';

Aggregation by group

SELECT
  region,
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*)                        AS order_count,
  SUM(total_amount)               AS total_revenue,
  AVG(total_amount)               AS avg_order_value
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY region, DATE_TRUNC('month', order_date)
ORDER BY month, total_revenue DESC;

Window function: running total per customer

SELECT
  customer_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

Join across two tables

SELECT
  c.customer_name,
  c.country,
  COUNT(o.order_id)   AS total_orders,
  SUM(o.total_amount) AS lifetime_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_name, c.country
HAVING SUM(o.total_amount) > 10000
ORDER BY lifetime_value DESC
LIMIT 50;

Reading an EXPLAIN plan

The EXPLAIN keyword shows the query execution plan without running the query. This is how you diagnose slow queries. Prepend it to any SELECT statement:

EXPLAIN
SELECT region, SUM(total_amount)
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY region;

The output looks like this (simplified):

XN HashAggregate  (cost=16523.84..16523.96 rows=50 width=42)
  -> XN Seq Scan on orders  (cost=0.00..15223.00 rows=260000 width=42)
        Filter: ((order_date >= '2025-01-01') AND (order_date <= '2025-12-31'))

Key things to look for in the output:

• Seq Scan: A full scan of the table. Normal, but check the estimated row count against the total table size. If the sort key is helping prune blocks, the row estimate should be much lower than the full table.
• DS_DIST_ALL_NONE or DS_DIST_NONE: No data redistribution needed across nodes. This is good.
• DS_BCAST_INNER: The inner table is being broadcast to all nodes. Normal for small dimension tables joining to large fact tables.
• DS_DIST_BOTH: Both tables are being redistributed during the join. This is expensive and usually means the distribution keys on the two tables do not match.
• Hash Join or Merge Join: The join algorithm. Hash joins are the most common in Redshift.

For a detailed guide on using EXPLAIN to fix distribution mismatches and tune WLM queues, see Redshift Performance Optimisation.

Useful AWS CLI commands

You can manage Redshift clusters from the terminal without opening the console:

# List all clusters in the current region
aws redshift describe-clusters \
  --query 'Clusters[*].{ID:ClusterIdentifier,Status:ClusterStatus,Nodes:NumberOfNodes}'

# Pause a provisioned cluster (stops billing for compute)
aws redshift pause-cluster \
  --cluster-identifier my-cluster

# Resume a paused cluster
aws redshift resume-cluster \
  --cluster-identifier my-cluster

# Get the endpoint for a cluster
aws redshift describe-clusters \
  --cluster-identifier my-cluster \
  --query 'Clusters[0].Endpoint'