Question Amazon Redshift utilizing pure language with Kiro

It’s Monday morning and your VP pings you: “Income dropped 15 p.c over the weekend. What occurred?” The clock begins. You open the AWS Administration Console, discover the fitting Amazon Redshift cluster, open the question editor, and begin searching. Which database has the income information, analytics_db or reporting_db? Is the desk referred to as orders, transactions, or sales_events? You discover it, however now you want the schema. Is the quantity column total_amount, income, or order_value? 20 minutes in, you haven’t answered the query. You’ve been navigating infrastructure.

This state of affairs performs out each day throughout information groups, and it’s why the panorama is shifting. With AI brokers coming into the analytics workflow, a rising variety of enterprise customers can now carry out advanced information evaluation. They not have to file a ticket with the info engineering workforce and wait days for a response. The bottleneck is not SQL experience. It’s the friction between having a query and getting a solution.

The Amazon Redshift MCP server paired with Kiro removes that friction. As an alternative of memorizing cluster endpoints, reverse-engineering schemas, and hand-writing SQL, you describe what you want in plain textual content and get outcomes. That Monday morning query turns into a single sentence: “Present me each day income for the previous two weeks, damaged down by area.” Kiro finds the cluster, discovers the schema, writes the question, and returns the reply in seconds, not minutes.

On this publish, you discover ways to:

1. Set up and configure Kiro with the Amazon Redshift MCP server.
2. Uncover clusters, databases, and schemas utilizing pure language.
3. Run analytical queries and cross-cluster comparisons conversationally.
4. Implement safety finest practices for manufacturing Amazon Redshift environments.

You should use Kiro in two types: Kiro built-in growth atmosphere (IDE), a full desktop growth atmosphere, and Kiro command line interface (CLI), which brings the identical AI capabilities on to your terminal. The Redshift MCP server works with each. The CLI expertise is especially nicely fitted to the conversational analytics workflow this publish describes, as a result of you can begin querying your information warehouse from a terminal session with out opening an IDE.

Vital: Earlier than utilizing this integration with manufacturing Amazon Redshift environments, learn the Safety suggestions part. This part covers important issues round AWS Identification and Entry Administration (IAM) permissions and Kiro autonomy modes.

What’s the Amazon Redshift MCP server?

The Mannequin Context Protocol (MCP) is an open normal that gives AI brokers with safe connections to exterior information sources and instruments. The Amazon Redshift MCP server is an open supply implementation that bridges the Kiro AI agent along with your Amazon Redshift infrastructure.

With the Redshift MCP server, you may:

• Routinely discover each provisioned clusters and serverless workgroups with cluster discovery.
• Browse databases, schemas, tables, and columns with metadata exploration.
• Run SQL in READ ONLY mode with built-in security protections with secure question execution.
• Work with a number of clusters and workgroups concurrently with multi-cluster help.

The server interprets your pure language requests into the suitable Amazon Redshift Information API calls and SQL queries. No guide endpoint configuration or SQL writing is required.

How the Redshift MCP server pertains to the AWS MCP server

You might need observed that AWS additionally gives the AWS MCP server (a part of the Agent Toolkit for AWS), which offers broad entry to AWS providers, together with the Redshift Information API. A standard query is: if the AWS MCP server can already attain Redshift, why add a devoted Redshift MCP server?

The 2 are complementary, not competing. The AWS MCP server provides Kiro common AWS capabilities (service resolution guides, SDK utilization steering, troubleshooting abilities, and entry to AWS APIs). The Redshift MCP server provides a purpose-built analytics layer on prime. It offers single-call question execution (in comparison with a minimal of three API requires submit, ballot, and fetch), read-only security by default, clear provisioned and serverless cluster dealing with, and devoted metadata navigation instruments. Upcoming options like question plan rationalization, native identification propagation, cluster evaluation, and UDF discovery will additional lengthen this specialised layer.

You should use each collectively, or use the Amazon Redshift MCP server by itself. There’s no either-or requirement.

Setting it up

The next sections stroll you thru the set up and configuration course of.

Stipulations

Earlier than you start, just be sure you have:

In your machine:

• Kiro IDE or Kiro CLI put in.
• Python 3.10 or newer.
• The uv bundle supervisor from Astral.

On AWS:

• AWS credentials configured by the AWS Command Line Interface (AWS CLI), atmosphere variables, or IAM roles.
• A minimum of one Amazon Redshift provisioned cluster or serverless workgroup.
• IAM permissions for Amazon Redshift entry (see the next part).

Step 1: Set up the uv bundle supervisor

Should you don’t have uv put in, run one of many following instructions.

For macOS or Linux:

curl -LsSf https://astral.sh/uv/set up.sh | sh

For Home windows PowerShell:

powershell -c "irm https://astral.sh/uv/set up.ps1 | iex"

Then set up Python 3.10 or newer if wanted:

Step 2: Configure IAM permissions

Your AWS identification wants the next permissions. Connect this coverage to your IAM consumer or function:

{
  "Model": "2012-10-17",
  "Assertion": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift:DescribeClusters",
        "redshift:GetClusterCredentialsWithIAM",
        "redshift:GetClusterCredentials",
        "redshift-serverless:ListWorkgroups",
        "redshift-serverless:GetWorkgroup",
        "redshift-serverless:GetCredentials",
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult"
      ],
      "Useful resource": "*"
    }
  ]
}

You additionally want database-level permissions: SELECT on tables you need to question, USAGE on schemas you need to discover, and connection entry to the goal databases.

Step 3: Configure the MCP server in Kiro

Open (or create) your Kiro MCP configuration file and add the Amazon Redshift server.

For Kiro IDE:

1. Person-level configuration (applies globally): ~/.kiro/settings/mcp.json.
2. Workspace-level configuration (applies to a particular venture): .kiro/settings/mcp.json.

For Kiro CLI:

1. Person-level configuration: ~/.kiro/settings/mcp.json.
2. Workspace-level configuration: .kiro/settings/mcp.json in your venture listing.

The configuration format is identical for each. Add the next:

{
  "mcpServers": {
    "awslabs.redshift-mcp-server": {
      "command": "uvx",
      "args": ["awslabs.redshift-mcp-server@latest"],
      "env": {
        "AWS_PROFILE": "default",
        "AWS_DEFAULT_REGION": "us-east-1",
        "FASTMCP_LOG_LEVEL": "ERROR"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

Tip: Exchange AWS_PROFILE and AWS_DEFAULT_REGION with values matching your atmosphere. Should you use AWS_REGION as an alternative of AWS_DEFAULT_REGION, AWS_REGION takes increased priority.

Observe: Home windows customers: use uv as an alternative of uvx with extra args. See the Amazon Redshift MCP server documentation for the Home windows-specific configuration.

Step 4: Confirm the connection

In Kiro IDE, restart Kiro (or reconnect MCP servers from the MCP Server view within the Kiro function panel). Then open the Kiro chat and enter the next immediate:

Record my obtainable Redshift clusters

If all the things is configured accurately, Kiro discovers your clusters and lists them with particulars like cluster identifiers, standing, node varieties, and endpoints.

Sensible examples

The next eventualities present how Kiro and the Amazon Redshift MCP server work collectively in real-world workflows.

Tip: If you need Kiro to indicate the SQL that it executes alongside each consequence, inform it at the start of your session:

For each question you run in opposition to Amazon Redshift, present me the precise SQL you executed together with the outcomes.

Kiro follows this instruction for the remainder of the dialog. For a everlasting setup, add this rule to a steering file (see the Suggestions part that follows) so that you don’t have to repeat it. The next examples replicate this conduct.

Instance 1: Uncover and discover your infrastructure

With out Kiro: Log in to the AWS Administration Console, navigate to Amazon Redshift, select clusters, open the question editor, register to every database, and manually browse schemas.

With Kiro:

What Amazon Redshift clusters and serverless workgroups do I've?

Kiro responds with a structured overview:

Discovered two provisioned clusters and one serverless workgroup:

• analytics-cluster (provisioned, 2x ra3.xlplus nodes, obtainable)
• reporting-cluster (provisioned, 4x ra3.4xlarge nodes, obtainable)
• etl-workgroup (serverless, obtainable)

Then drill deeper:

What databases and schemas exist in analytics-cluster?

Kiro makes use of the MCP metadata instruments to discover the cluster. No SQL is required for discovery:

Database: analytics_db (native)

• information_schema — system schema
• pg_catalog — system schema
• public — default schema
• gross sales — user-created schema

Database: dev (native)

• information_schema — system schema
• pg_catalog — system schema
• public — default schema

The one user-created schema is gross sales within the analytics_db database.

Present me the tables within the gross sales schema of the analytics database

There are 5 base tables in analytics_db.gross sales:

1. clients
2. order_items
3. orders
4. merchandise
5. returns

Describe the columns within the orders desk

Every request builds on the earlier context. Kiro remembers which cluster you’re exploring and navigates the hierarchy for you.

Instance 2: On-demand information evaluation in plain English

With out Kiro: Write SQL from scratch, guess at column names, run the question, repair syntax errors, iterate.

With Kiro:

Present me the highest 10 clients by complete buy quantity from the analytics-cluster,
together with what number of orders every buyer positioned

Kiro does the next:

1. Identifies the related cluster and database.
2. Explores the schema to search out buyer and order tables.
3. Understands the desk relationships.
4. Generates and runs the suitable SQL, displaying you the question first.
5. Presents the leads to a readable format.

The response consists of the question and outcomes:

Question executed:

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent
FROM gross sales.clients c
JOIN gross sales.orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 10;

You didn’t want to recollect desk names, column names, or be a part of situations. Kiro figured it out by exploring the metadata first. You possibly can see precisely what it ran, copy the SQL, and reuse it.

Instance 3: Cross-cluster information comparability

With out Kiro: Open two SQL shoppers, register to totally different clusters, run comparable queries, manually examine leads to a spreadsheet.

With Kiro:

Evaluate the entire gross sales income and transaction counts between
analytics-cluster and etl-workgroup for the final quarter.
Are there any discrepancies?

Kiro queries each environments, aligns the outcomes, and offers a comparability, displaying each question it runs alongside the best way.

Question executed on analytics-cluster:

SELECT
    COUNT(*) AS total_records,
    SUM(total_amount) AS total_revenue,
    COUNT(CASE WHEN order_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
        THEN 1 END) AS q4_transactions
FROM gross sales.orders;

Question executed on etl-workgroup:

SELECT
    COUNT(*) AS total_records,
    SUM(total_amount) AS total_revenue,
    COUNT(CASE WHEN order_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
        THEN 1 END) AS q4_transactions
FROM gross sales.orders;

Gross sales information comparability: Analytics vs ETL.

As a result of each queries are seen, you may confirm that Kiro in contrast the identical metrics on either side, with no hidden assumptions.

Instance 4: Schema documentation on demand

With out Kiro: Manually doc desk buildings in a wiki or spreadsheet. Documentation regularly falls outdated.

With Kiro:

Generate documentation for the tables within the gross sales schema of analytics-cluster.
Embrace column names, information varieties, and notable relationships.

Kiro explores each desk utilizing the invention instruments and produces structured documentation.

Desk: gross sales.clients

Desk: gross sales.orders

(continues for all tables within the schema…)

You possibly can paste this instantly into your workforce wiki or README, and anybody can rerun the identical discovery circulate to refresh the docs later.

Instance 5: Troubleshooting information high quality

Test the orders desk in analytics-cluster for any NULL values
within the customer_id or total_amount columns.
What number of data are affected?

Question executed:

SELECT
    COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_customer_ids,
    COUNT(*) FILTER (WHERE total_amount IS NULL) AS null_total_amounts,
    COUNT(*) AS total_rows
FROM gross sales.orders;

Out of 172,456 rows: 0 NULL customer_ids, 3 NULL total_amounts.

Discover duplicate order_ids within the gross sales.orders desk and present me
a number of examples with their timestamps

Question executed:

SELECT order_id, COUNT(*) AS occurrences, MIN(order_date) AS first_seen, MAX(order_date) AS last_seen
FROM gross sales.orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY occurrences DESC
LIMIT 5;

Discovered two duplicate order_ids: order 88412 (two occurrences), order 91003 (two occurrences).

What is the date vary of information within the occasions desk?
Are there any gaps within the each day file counts?

Question executed:

WITH daily_counts AS (
    SELECT DATE(event_date) AS day, COUNT(*) AS record_count
    FROM gross sales.occasions
    GROUP BY DATE(event_date)
),
date_range AS (
    SELECT MIN(day) AS start_date, MAX(day) AS end_date FROM daily_counts
),
all_dates AS (
    SELECT (start_date + (n || ' days')::INTERVAL)::DATE AS day
    FROM date_range, generate_series(0, (end_date - start_date)) AS n
)
SELECT a.day AS missing_date
FROM all_dates a
LEFT JOIN daily_counts d ON a.day = d.day
WHERE d.day IS NULL
ORDER BY a.day;

Date vary: 2024-01-01 to 2025-04-20. Discovered three lacking dates: 2024-03-15, 2024-07-04, 2024-12-25 (possible holidays).

Each question is correct there within the response. You possibly can copy them into your personal SQL consumer, modify them, or save them as reusable scripts.

Suggestions for getting probably the most out of Kiro and Redshift

1. Begin with discovery. Start every session by asking Kiro to listing your clusters and discover the database construction. This offers the agent context for subsequent queries.
2. Be particular about which cluster. If in case you have a number of clusters, point out the cluster title in your request to keep away from ambiguity.
3. Iterate progressively. Begin with easy questions and construct complexity. Ask for a rely earlier than asking for a full breakdown.
4. Use steering information for workforce conventions. Create a .kiro/steering/redshift.md file in your venture with particulars about your cluster naming conventions, necessary schemas, and customary question patterns. This offers Kiro persistent context about your atmosphere.

Instance steering file:

---
inclusion: auto
---

# Redshift Atmosphere Context

## Clusters

- **analytics-cluster**: Major analytics warehouse. Use database `analytics_db`.
- **etl-workgroup**: Serverless workgroup for ETL pipelines. Use database `staging_db`.

## Key Schemas

- `gross sales`: Buyer transactions, orders, and income information
- `advertising and marketing`: Marketing campaign efficiency and attribution information

## Conventions

- At all times filter by `is_deleted = false` on soft-delete tables
- Date columns use `TIMESTAMP WITHOUT TIME ZONE` in UTC

## Question Transparency

- At all times present the precise SQL question textual content being executed earlier than or alongside the outcomes.
  Customers ought to have the ability to see, confirm, and reuse each question that Kiro runs in opposition to Redshift.

That final Question Transparency rule is a small addition with a big effect. By default, Kiro may summarize outcomes with out displaying the underlying SQL. Including this steering instruction makes each question seen, which helps preserve constant conduct throughout your information workforce and helps auditing, studying, and belief.

5. Use hooks for automation. Arrange agent hooks to run frequent validation queries routinely. For instance, set off a knowledge high quality examine everytime you edit a particular SQL file.
6. Confirm necessary outcomes. At all times cross-check important enterprise findings with stakeholders earlier than performing on them.

Safety suggestions

When configured with least privilege IAM insurance policies and Supervised mode, the Redshift MCP server offers a number of layers of safety. Below the AWS shared accountability mannequin, you’re liable for configuring entry controls appropriately.

Security layers at a look

• Information stays in your account. The MCP server runs regionally, queries run inside your Amazon Redshift cluster, and no information is shipped to third-party providers.
• Content material not used for coaching. Enterprise customers are excluded from service enchancment utilization. Free or particular person tier customers can decide out in Kiro settings.
• Learn-only by default. Each question is wrapped in a read-only transaction, stopping unintended writes.
• Commonplace IAM controls. Kiro solely will get permissions you’ve explicitly granted.
• Supervised mode. Evaluate every agent motion earlier than it’s utilized in manufacturing environments.

Vital caveat: The read-only enforcement solely applies to queries routed by the MCP server’s execute_query instrument. Kiro additionally has shell entry. In case your IAM credentials have write permissions, direct CLI calls (aws redshift-data execute-statement) bypass this guardrail completely.

Takeaway: Use least privilege IAM insurance policies (scoped to learn and describe operations solely) as your main protection. Keep away from broad insurance policies like AdministratorAccess or AmazonRedshiftFullAccess. When IAM is correctly scoped, even a direct CLI name can’t carry out writes.

Confirm Kiro’s output

Kiro is a strong assistant, but it surely’s not infallible. Like several AI instrument, it might misread your intent, generate incorrect SQL, or current outcomes that look believable however are fallacious. Examples embrace a misplaced be a part of, a fallacious filter, or an aggregation that silently excludes rows. That is particularly necessary when working with manufacturing information the place choices have actual enterprise impression.

Deal with Kiro’s output as a powerful place to begin, not a last reply. Evaluate the SQL it generates earlier than performing on the outcomes. Use the Question Transparency steering rule (described within the Suggestions part) so you may see the precise question behind each consequence. When findings inform enterprise choices, validate them independently by operating the question your self, cross-checking with a colleague, or evaluating in opposition to a recognized baseline.

Abstract

Protection in depth: use these layers collectively for manufacturing environments.

What you may obtain with Kiro and Amazon Redshift

Each minute you spend trying to find a desk title or debugging a SQL syntax error is a minute that you just’re not spending on precise evaluation. You possibly can scale back that overhead by letting Kiro deal with the mechanical components (discovery, navigation, and question building) so you may deal with the questions that matter to your corporation.

Cleansing up

Should you created Amazon Redshift sources particularly for this walkthrough, or in case you not want the MCP server integration, comply with these steps. They assist you keep away from ongoing costs and take away the configuration.

• Take away the MCP server configuration.
• Detach the IAM coverage.
• Delete take a look at Amazon Redshift sources (if relevant).
• Uninstall uv (elective).

Conclusion

On this publish, you realized how you can arrange Kiro with the Amazon Redshift MCP server to question your information warehouse utilizing pure language. You explored cluster discovery, schema searching, analytical queries, cross-cluster comparisons, and information high quality checks, all with out writing SQL from scratch or switching between instruments.

To go additional:

1. New to Amazon Redshift? Get began with Amazon Redshift to create your first cluster or serverless workgroup.
2. Learn the MCP protocol specification to grasp how AI brokers work with exterior instruments.
3. Go to kiro.dev for Kiro’s full capabilities, together with specs, hooks, and steering information.

As you get snug with the fundamentals, attempt combining steering information with agent hooks to automate recurring workflows like each day information high quality checks or weekly schema documentation refreshes.

In regards to the creator