How to Build a Redshift AI Agent

Redshift is the warehouse at the centre of many AWS data estates, feeding dashboards, pipelines, and reporting that the business depends on daily. An agent issuing queries into it shares infrastructure with all of that, which makes workload isolation the design concern specific to Redshift alongside the security and grounding concerns common to every warehouse agent. This guide covers building one that answers well without degrading everything else, drawing on FISTA Solutions' AI agents delivery on AWS data platforms. It complements how to build a text to sql agent and how to build a bigquery ai agent.

How should the agent connect?

Through the Data API. It executes SQL asynchronously over HTTPS with IAM credentials, needs no connection pool or driver management, and fits a serverless or containerised agent naturally. The executing IAM identity maps to a database user or role, which is where security attaches.

Approach	Identity	Connection handling	Fits
Data API with user identity	Per requesting user	None to manage	Interactive agents
Data API with scoped role	Service role with limited grants	None to manage	Background analysis
Driver with shared credentials	One superuser	Pool to manage	Not for agents

Executing as the requesting identity is what makes row-level security, column-level grants, and masking apply. A shared superuser sees everything and attributes nothing.

How is security enforced?

Through the database's own mechanisms, applied to the executing identity. Row-level security policies attached to tables filter rows by the querying user or role. Column-level grants restrict which columns may be selected. Dynamic data masking transforms sensitive columns for identities without clearance. IAM authentication mapped to database roles ties all of it to the organisation's identity provider.

The agent's job is to execute as the right identity and to return results as they come, including masked values, rather than to reimplement any of it. The permission test, running as two identities with different grants and confirming results differ, precedes launch.

How is workload managed?

With a dedicated WLM queue for the agent. Redshift schedules queries through queues with defined concurrency and memory allocation, and on provisioned clusters an agent's exploratory queries compete with production dashboards and ETL if they share a queue. A dedicated queue with its own concurrency limit, memory share, and statement timeout contains the agent's impact.

On Redshift Serverless the concern shifts to compute consumption and cost, and the equivalent controls are query limits, timeouts, and monitoring of RPU usage attributable to the agent. Either way, the agent is a workload with a budget.

What grounds SQL generation?

System catalogue metadata and curated views. The catalogue exposes tables, columns, types, comments, and distribution and sort key configuration. Comments decide whether the agent picks the right table; sort key awareness decides whether the query it writes performs, since a filter on the sort key column is the difference between a fast scan and a full one.

Curated views encode the organisation's definitions, and the agent should query through them where they exist. Reconstructing an active-customer definition from base tables produces a number that contradicts every report using the view. Building curated views for the domain in scope is part of the project where none exist.

How are questions translated?

Into SQL over curated views and documented tables, using metadata to choose, with clarification on ambiguity. The agent submits through the Data API, polls for completion, and returns the result with the SQL, the query identifier, the tables used, and their last load time.

Redshift's asynchronous execution suits agents well: the agent submits, the query runs in its queue, and the agent retrieves the result without holding a connection open, which also means a slow query can be cancelled by statement identifier rather than left running.

What should be returned?

The result and its provenance: exact SQL, the query identifier that administrators can trace in system tables for execution plan and resource use, the tables and their freshness, and a note of any masking applied. Freshness matters because Redshift tables are loaded on schedules, and a correct number from a table last loaded two days ago should say so.

How is it evaluated?

Against real questions with verified SQL and answers. Measure table and view selection, filter correctness, result match, and execution time relative to an expert query, which catches correct-but-slow SQL that ignores sort keys. Test as identities with different row-level policies. Monitor queue wait times to confirm the agent is not affecting other workloads.

What about Amazon's own capabilities?

AWS ships natural-language query capability for Redshift and surrounding services, and where it fits the need it should be used. A custom agent earns its place where the organisation needs its own presentation and integration, combined data from systems outside Redshift, evaluation against its own reference questions, or queue and ambiguity handling the platform feature does not control.

What does the build sequence look like?

Two weeks on metadata comments and curated views for the domain in scope. One week on Data API access, identity mapping, the dedicated queue, and the permission test. Two weeks on question translation with the data team testing. One week on presentation with provenance. Then expansion with queue monitoring watched throughout.

What goes wrong?

Shared superuser credentials. Agent queries in the production queue. Sparse comments. SQL against base tables that contradicts curated views. Generated queries that ignore sort keys and run for minutes. Silent disambiguation. And freshness unstated, so yesterday's load is presented as now.

How does Redshift Spectrum and data sharing change this?

By widening what the agent can reach and therefore what must be governed. Spectrum queries external data in S3 through the Glue catalogue, and data sharing exposes other clusters' datasets; both appear to the agent as tables it might select. Permissions on external schemas and shared databases must be scoped with the same care as local tables, and the agent's metadata grounding should include external table comments so it does not prefer an undocumented external copy over a curated local view. Cost awareness matters too, since Spectrum scans are billed by bytes and behave more like BigQuery than like a provisioned cluster.

How FISTA Solutions helps

FISTA Solutions builds Redshift agents on the Data API with per-user identity so security policies hold, grounded in enriched metadata and curated views, isolated in a dedicated workload queue, and returning SQL and query identifiers with every answer, through AI enablement, AI agents, and forward deployed engineers working with data platform teams. The record behind the approach is 150+ projects for 50+ companies with 99.9% uptime.

To make Redshift conversational without degrading production workloads, message FISTA on WhatsApp, or read how to build a text to sql agent.