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 looking. Which database has the income knowledge, 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 knowledge groups, and it’s why the panorama is shifting. With AI brokers getting into the analytics workflow, a rising variety of enterprise customers can now carry out advanced knowledge evaluation. They not have to file a ticket with the info engineering staff 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 a substitute 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 put up, you learn 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 greatest practices for manufacturing Amazon Redshift environments.

You need to use Kiro in two varieties: Kiro built-in growth surroundings (IDE), a full desktop growth surroundings, 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 effectively suited to the conversational analytics workflow this put up describes, as a result of you can begin querying your knowledge warehouse from a terminal session with out opening an IDE.

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

What’s the Amazon Redshift MCP server?

The Mannequin Context Protocol (MCP) is an open commonplace that gives AI brokers with safe connections to exterior knowledge 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:

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

The server interprets your pure language requests into the suitable Amazon Redshift Information API calls and SQL queries. No handbook 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 affords the AWS MCP server (a part of the Agent Toolkit for AWS), which gives 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 offers Kiro basic AWS capabilities (service resolution guides, SDK utilization steerage, troubleshooting abilities, and entry to AWS APIs). The Redshift MCP server provides a purpose-built analytics layer on high. It gives 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 clarification, native identification propagation, cluster evaluation, and UDF discovery will additional lengthen this specialised layer.

You need to 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 package deal supervisor from Astral.

On AWS:

• AWS credentials configured by the AWS Command Line Interface (AWS CLI), surroundings variables, or IAM roles.
• At the very least one Amazon Redshift provisioned cluster or serverless workgroup.
• IAM permissions for Amazon Redshift entry (see the next part).

Step 1: Set up the uv package deal supervisor

If you happen to 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 position:

{
  "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 wish to question, USAGE on schemas you wish 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. Consumer-level configuration (applies globally): ~/.kiro/settings/mcp.json.
2. Workspace-level configuration (applies to a selected undertaking): .kiro/settings/mcp.json.

For Kiro CLI:

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

The configuration format is similar 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: Substitute AWS_PROFILE and AWS_DEFAULT_REGION with values matching your surroundings. If you happen to use AWS_REGION as a substitute of AWS_DEFAULT_REGION, AWS_REGION takes larger priority.

Word: Home windows customers: use uv as a substitute 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 characteristic panel). Then open the Kiro chat and enter the next immediate:

Checklist my obtainable Redshift clusters

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

Sensible examples

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

Tip: In order for you Kiro to point out the SQL that it executes alongside each end result, inform it at the start of your session:

For each question you run towards 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 mirror this habits.

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, check in 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. prospects
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 knowledge 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 prospects 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 seek 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.prospects 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 knowledge comparability

With out Kiro: Open two SQL purchasers, check in to completely different clusters, run comparable queries, manually examine leads to a spreadsheet.

With Kiro:

Evaluate the full 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 gives a comparability, displaying each question it runs alongside the way in which.

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 knowledge comparability: Analytics vs ETL.

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

Instance 4: Schema documentation on demand

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

With Kiro:

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

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

Desk: gross sales.prospects

Desk: gross sales.orders

(continues for all tables within the schema…)

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

Instance 5: Troubleshooting knowledge 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 couple 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 report 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 (seemingly holidays).

Each question is true there within the response. You possibly can copy them into your individual 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 provides the agent context for subsequent queries.
2. Be particular about which cluster. You probably 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 staff conventions. Create a .kiro/steering/redshift.md file in your undertaking with particulars about your cluster naming conventions, necessary schemas, and customary question patterns. This provides Kiro persistent context about your surroundings.

Instance steering file:

---
inclusion: auto
---

# Redshift Surroundings Context

## Clusters

- **analytics-cluster**: Main 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 knowledge
- `advertising`: Marketing campaign efficiency and attribution knowledge

## Conventions

- All the time filter by `is_deleted = false` on soft-delete tables
- Date columns use `TIMESTAMP WITHOUT TIME ZONE` in UTC

## Question Transparency

- All the time 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 towards Redshift.

That final Question Transparency rule is a small addition with a big effect. By default, Kiro would possibly summarize outcomes with out displaying the underlying SQL. Including this steering instruction makes each question seen, which helps keep constant habits throughout your knowledge staff and helps auditing, studying, and belief.

5. Use hooks for automation. Arrange agent hooks to run widespread validation queries routinely. For instance, set off a knowledge high quality examine everytime you edit a selected SQL file.
6. Confirm necessary outcomes. All the time cross-check essential enterprise findings with stakeholders earlier than appearing on them.

Safety ideas

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

Security layers at a look

• Information stays in your account. The MCP server runs domestically, queries run inside your Amazon Redshift cluster, and no knowledge 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 choose out in Kiro settings.
• Learn-only by default. Each question is wrapped in a read-only transaction, stopping unintended writes.
• Normal IAM controls. Kiro solely will get permissions you’ve explicitly granted.
• Supervised mode. Overview every agent motion earlier than it’s utilized in manufacturing environments.

Essential caveat: The read-only enforcement solely applies to queries routed by the MCP server’s execute_query device. 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 major protection. Keep away from broad insurance policies like AdministratorAccess or AmazonRedshiftFullAccess. When IAM is correctly scoped, even a direct CLI name can not carry out writes.

Confirm Kiro’s output

Kiro is a strong assistant, nevertheless it’s not infallible. Like several AI device, it could actually misread your intent, generate incorrect SQL, or current outcomes that look believable however are mistaken. Examples embody a misplaced be a part of, a mistaken filter, or an aggregation that silently excludes rows. That is particularly necessary when working with manufacturing knowledge the place selections have actual enterprise affect.

Deal with Kiro’s output as a powerful place to begin, not a ultimate reply. Overview the SQL it generates earlier than appearing on the outcomes. Use the Question Transparency steering rule (described within the Suggestions part) so you may see the precise question behind each end result. When findings inform enterprise selections, validate them independently by operating the question your self, cross-checking with a colleague, or evaluating towards a identified 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 looking for a desk title or debugging a SQL syntax error is a minute that you simply’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 concentrate on the questions that matter to what you are promoting.

Cleansing up

If you happen to created Amazon Redshift sources particularly for this walkthrough, or in the event you not want the MCP server integration, observe these steps. They aid you keep away from ongoing costs and take away the configuration.

• Take away the MCP server configuration.
• Detach the IAM coverage.
• Delete check Amazon Redshift sources (if relevant).
• Uninstall uv (non-compulsory).

Conclusion

On this put up, you discovered how you can arrange Kiro with the Amazon Redshift MCP server to question your knowledge warehouse utilizing pure language. You explored cluster discovery, schema looking, analytical queries, cross-cluster comparisons, and knowledge 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 knowledge high quality checks or weekly schema documentation refreshes.

In regards to the writer