Question Amazon Redshift utilizing pure language with Kiro

It’s Monday morning and your VP pings you: “Income dropped 15 % over the weekend. What occurred?” The clock begins. You open the AWS Administration Console, discover the appropriate 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 day by day throughout information 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 complicated information evaluation. They not have to file a ticket with the information engineering crew 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 day by 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 submit, 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 greatest practices for manufacturing Amazon Redshift environments.

You should use Kiro in two varieties: Kiro built-in improvement setting (IDE), a full desktop improvement setting, 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 submit describes, as a result of you can begin querying your information warehouse from a terminal session with out opening an IDE.

Necessary: Earlier than utilizing this integration with manufacturing Amazon Redshift environments, learn the Safety suggestions part. This part covers vital concerns 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 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:

• Robotically 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 may need seen that AWS additionally presents 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 provides Kiro basic AWS capabilities (service determination guides, SDK utilization steering, troubleshooting expertise, and entry to AWS APIs). The Redshift MCP server provides a purpose-built analytics layer on prime. 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 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.

Conditions

Earlier than you start, just remember to 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), setting variables, or IAM roles.
• Not less than 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

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 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 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 selected 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 setting. Should you use AWS_REGION as an alternative of AWS_DEFAULT_REGION, AWS_REGION takes greater priority.

Notice: Home windows customers: use uv as an alternative of uvx with further 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:

Record my accessible Redshift clusters

If every thing is configured appropriately, 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 would like Kiro to point out the SQL that it executes alongside each end result, inform it originally 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 mirror 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, accessible)
• reporting-cluster (provisioned, 4x ra3.4xlarge nodes, accessible)
• etl-workgroup (serverless, accessible)

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 whole 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 contains 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 evaluate leads to a spreadsheet.

With Kiro:

Evaluate the whole 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 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 steadily 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 immediately into your crew wiki or README, and anybody can rerun the identical discovery move to refresh the docs later.

Instance 5: Troubleshooting information high quality

Verify 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 knowledge within the occasions desk?
Are there any gaps within the day by 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 (probably holidays).

Each question is true there within the response. You possibly can copy them into your individual SQL shopper, modify them, or save them as reusable scripts.

Suggestions for getting essentially the most out of Kiro and Redshift

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

Instance steering file:

---
inclusion: auto
---

# Redshift Setting 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 be capable 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 would possibly summarize outcomes with out displaying the underlying SQL. Including this steering instruction makes each question seen, which helps keep constant conduct throughout your information crew and helps auditing, studying, and belief.

5. Use hooks for automation. Arrange agent hooks to run widespread validation queries mechanically. For instance, set off an information high quality verify everytime you edit a selected SQL file.
6. Confirm vital outcomes. At all times cross-check vital enterprise findings with stakeholders earlier than appearing on them.

Safety suggestions

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 duty mannequin, you might be answerable 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 distributed 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.
• Customary IAM controls. Kiro solely will get permissions you’ve explicitly granted.
• Supervised mode. Assessment every agent motion earlier than it’s utilized in manufacturing environments.

Necessary 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 solely.

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, nevertheless it’s not infallible. Like several AI instrument, it might probably misread your intent, generate incorrect SQL, or current outcomes that look believable however are fallacious. Examples embody a misplaced be a part of, a fallacious filter, or an aggregation that silently excludes rows. That is particularly vital when working with manufacturing information the place choices have actual enterprise impression.

Deal with Kiro’s output as a powerful start line, not a remaining reply. Assessment 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 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 simply’re not spending on precise evaluation. You possibly can cut back that overhead by letting Kiro deal with the mechanical elements (discovery, navigation, and question building) so you may concentrate on the questions that matter to your corporation.

Cleansing up

Should you created Amazon Redshift assets particularly for this walkthrough, or in case 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 assets (if relevant).
• Uninstall uv (non-compulsory).

Conclusion

On this submit, you realized how one can arrange Kiro with the Amazon Redshift MCP server to question your information warehouse utilizing pure language. You explored cluster discovery, schema shopping, 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 recordsdata.

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

Concerning the writer