+1 (726) 227-3497
RougeWarehouse

Connecting AI Agents to Amazon Redshift: MCP Server, Generative SQL and Guardrails

Every warehouse team is now being asked the same question: can an AI assistant answer questions directly off Redshift? In 2026 the plumbing for that exists and is boring — the Model Context Protocol (MCP) gives an agent a standard way to discover your schema and run queries, and Amazon Q generative SQL does text-to-SQL inside the Redshift query editor. The interesting work is no longer the connection. It is making sure an agent cannot melt your warehouse, read the salary table, or quietly hand a business user a wrong number.

This tutorial sets up both paths and then spends most of its time on the guardrails, because that is the part that gets skipped.

What the pieces actually are

  • Redshift MCP server — a small process that speaks MCP to a client (Claude Desktop, an IDE assistant, Bedrock Agents, your own LangGraph app) and speaks the Redshift Data API to your cluster or Serverless workgroup. It exposes tools like "list schemas", "describe table", "run query". The agent decides what to call; the server decides what is allowed.
  • Amazon Q generative SQL — managed text-to-SQL in the Redshift query editor v2. No infrastructure; it reads your schema metadata (and optionally your query history) and drafts SQL for a human to review before running.
  • Redshift Data API — the HTTP, IAM-authenticated, no-JDBC-connection way to submit SQL asynchronously. Both of the above sit on top of it, and it is what makes least-privilege easy: the identity is an IAM role, not a database password in a config file.

Use Q generative SQL for humans in the console. Use MCP when an application or agent needs the warehouse as a tool.

Step 1: create a purpose-built read-only role

Never point an agent at your ETL role. Create a database role whose blast radius is a defined set of views.

CREATE ROLE agent_readonly;

-- Only the curated layer. Not raw, not staging.
GRANT USAGE ON SCHEMA analytics TO ROLE agent_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE agent_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO ROLE agent_readonly;

-- Explicitly no write path
REVOKE CREATE ON SCHEMA analytics FROM ROLE agent_readonly;

CREATE USER agent_svc PASSWORD DISABLE;   -- IAM-only identity
GRANT ROLE agent_readonly TO agent_svc;

Attach row-level security and masking to the same objects so the agent inherits the same rules as a human in that role:

ATTACH RLS POLICY region_filter ON analytics.fct_orders TO ROLE agent_readonly;

ATTACH MASKING POLICY mask_email ON analytics.dim_customer(email)
  TO ROLE agent_readonly PRIORITY 10;

If you have already read our post on RBAC, row-level security and dynamic data masking, this is the payoff: policies written for people work unchanged for agents.

The IAM role the MCP server assumes needs only Data API calls scoped to one workgroup:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult",
        "redshift-data:ListSchemas",
        "redshift-data:ListTables",
        "redshift-data:DescribeTable"
      ],
      "Resource": "arn:aws:redshift-serverless:us-east-1:111111111111:workgroup/analytics-wg"
    },
    {
      "Effect": "Allow",
      "Action": "redshift-serverless:GetCredentials",
      "Resource": "arn:aws:redshift-serverless:us-east-1:111111111111:workgroup/analytics-wg",
      "Condition": { "StringEquals": { "redshift-serverless:DbUser": "agent_svc" } }
    }
  ]
}

Note there is no BatchExecuteStatement, no CancelStatement on other people's work, and no Secrets Manager read. Temporary credentials for one database user, nothing else.

Step 2: run the MCP server

MCP servers are configured client-side. A typical entry, using the AWS Labs Redshift MCP server via uvx:

{
  "mcpServers": {
    "redshift": {
      "command": "uvx",
      "args": ["awslabs.redshift-mcp-server@latest"],
      "env": {
        "AWS_REGION": "us-east-1",
        "AWS_PROFILE": "redshift-agent",
        "REDSHIFT_WORKGROUP": "analytics-wg",
        "REDSHIFT_DATABASE": "warehouse",
        "REDSHIFT_DB_USER": "agent_svc",
        "MCP_READONLY": "true"
      }
    }
  }
}

Smoke-test it before you connect an LLM. Ask the client to list schemas and describe one table; if the agent can see raw_ schemas you got the grants wrong, and it is far cheaper to find that out now.

If you are writing your own server (common, because most teams want to restrict the tool surface further), the whole thing is a few dozen lines around the Data API:

import boto3, time

data = boto3.client("redshift-data", region_name="us-east-1")

BANNED = ("insert", "update", "delete", "drop", "alter", "create",
          "grant", "revoke", "copy", "unload", "truncate", "call")

def run_query(sql: str, max_rows: int = 500):
    lowered = sql.strip().lower()
    if not lowered.startswith(("select", "with")) or any(b in lowered.split() for b in BANNED):
        raise ValueError("read-only tool: SELECT statements only")

    resp = data.execute_statement(
        WorkgroupName="analytics-wg", Database="warehouse", DbUser="agent_svc",
        Sql=sql, StatementName="mcp-agent",
    )
    sid = resp["Id"]
    while True:
        desc = data.describe_statement(Id=sid)
        if desc["Status"] in ("FINISHED", "FAILED", "ABORTED"):
            break
        time.sleep(0.4)
    if desc["Status"] != "FINISHED":
        raise RuntimeError(desc.get("Error", desc["Status"]))
    return data.get_statement_result(Id=sid)["Records"][:max_rows]

String matching is a backstop, not the security boundary. The grants in Step 1 are the security boundary. Treat the parser check as a way to give the model a fast, clear error instead of a permission denied it will try to work around.

Step 3: make the schema legible to a model

Text-to-SQL quality is mostly a metadata problem. Both Q generative SQL and any MCP agent read what your catalog tells them, so spend an afternoon on comments:

COMMENT ON TABLE analytics.fct_orders IS
  'One row per order line, grain = order_id + line_no. Excludes cancelled orders. Revenue is net of discounts, in USD.';
COMMENT ON COLUMN analytics.fct_orders.net_revenue IS
  'Net revenue in USD, excludes tax and shipping. Use this for revenue reporting, not gross_amount.';
COMMENT ON COLUMN analytics.dim_customer.is_active IS
  'TRUE if the customer ordered in the last 365 days.';

Other things that move accuracy more than prompt engineering:

  • Expose a narrow curated schema of wide, denormalized marts, not 400 normalized tables. Agents pick wrong join paths; remove the choice.
  • Name columns unambiguously. Two columns called amount in different tables cost you more accuracy than any model upgrade.
  • Publish a handful of canonical example queries in the agent's prompt or as MCP resources — "revenue by month", "active customers by region". Models copy patterns well.
  • For Q generative SQL, enable query history context so it can learn from SQL your analysts actually run, and remember it is an account-level admin choice with obvious privacy implications.

Step 4: cost and concurrency guardrails

An agent that retries a SELECT * on a 4 TB fact table three times is a real bill. Put the limits in the database, not in the prompt.

-- Serverless: cap what agent workloads can consume
CREATE WORKLOAD MANAGEMENT QUERY MONITORING RULE agent_guard
  FOR USER agent_svc
  WHEN query_execution_time > 120 OR scan_row_count > 2000000000
  ACTION abort;

On Serverless, also set a max RPU on the workgroup the agent uses, and give agent queries a low query priority so interactive dashboards win contention. The cleanest topology is a separate Serverless workgroup for agents, reading the same data via a datashare from your production namespace: independent scaling limits, independent cost tag, and a blast radius of zero on the production warehouse.

Budget controls worth setting on day one:

  • Serverless usage limit with an alert action at, say, 200 RPU-hours/month on the agent workgroup.
  • statement_timeout on the agent user: ALTER USER agent_svc SET statement_timeout TO 120000;
  • Row cap in the MCP tool itself — an LLM does not need 50,000 rows, and the context window will not survive them anyway.

Step 5: log everything and evaluate it

Agent SQL is a new class of workload and you should be able to audit it.

-- Everything the agent ran in the last day
SELECT q.query_id, q.start_time, q.elapsed_time / 1000000.0 AS seconds,
       q.returned_rows, q.query_text
FROM SYS_QUERY_HISTORY q
WHERE q.user_id = (SELECT usesysid FROM pg_user WHERE usename = 'agent_svc')
  AND q.start_time > DATEADD(day, -1, GETDATE())
ORDER BY q.elapsed_time DESC;

Tag every statement with the conversation or request ID (StatementName in the Data API, or a leading SQL comment) so you can trace a bad answer in a chat thread back to the exact query.

Then build a small evaluation set — 30 to 50 questions your business actually asks, each with a hand-written correct query and its result. Run it on a schedule against the agent and score on result equivalence, not SQL text similarity; there are many correct ways to write the same aggregate. This is the only honest way to answer "is it accurate?", and it catches the day a schema change silently breaks answers.

What still goes wrong

  • Semantics, not syntax. The model writes valid SQL against the wrong table. Fix with a narrower curated layer and better comments — not a bigger model.
  • Silent filters. Users ask "revenue last quarter" and the agent quietly uses gross, or includes cancelled orders. Encode business rules in the views so there is nothing to get wrong.
  • Freshness. Agents will happily report on a mart that failed to load at 03:00. Expose a last_loaded_at column or an MCP tool for pipeline status and instruct the agent to state it.
  • PII leakage through aggregates. Masking protects columns; small-group aggregates can still identify people. Add minimum-group-size logic in the views if that matters to you.

Where to start

A sensible first project is one curated schema, one read-only role, one MCP server, one Slack or IDE client, and an eval set — not "chat with the whole warehouse". The teams getting value from this in 2026 are the ones that treated it as a data modeling project with an LLM on the end.

If you want help designing the curated layer, the permission model and the guardrails before you point an agent at production, that is exactly the kind of engagement our Redshift consultants take on — see Redshift ML & Generative SQL or get in touch.