Amazon Bedrock Knowledge Bases can retrieve from structured data by translating a user’s natural-language question into SQL and executing it through an Amazon Redshift query engine. Current AWS documentation supports structured knowledge bases with Redshift Serverless or Redshift Provisioned as the query engine, querying data stored in Redshift and supported data reachable through the default AWS Glue Data Catalog. The workflow can be used through Retrieve, RetrieveAndGenerate, or GenerateQuery.
Within Generative AI on AWS, structured retrieval is different from vector RAG. Instead of embedding chunks and finding semantically similar text, Bedrock generates SQL against a governed schema and uses the query result as the factual basis for the response.
The existing Amazon Bedrock Knowledge Bases article provides the broader retrieval context.
Redshift is the query engine even when the underlying data spans other stores
A structured knowledge base requires an Amazon Redshift query engine configuration.
The Redshift engine can query native warehouse data and data accessible through supported Glue Data Catalog integration, allowing the knowledge base to reason over structured enterprise datasets.
Capacity, permissions, network access, and SQL governance on Redshift therefore become part of the generative-AI application’s SLO.
Metadata quality directly influences text-to-SQL quality
The service uses schema metadata to translate the user’s question into SQL.
Ambiguous table names, cryptic columns, duplicate business concepts, or missing descriptions make it harder to generate the intended query.
Curate schemas, comments, representative relationships, and domain terminology so the model sees a database that communicates its business meaning.
Use read-only, least-privilege database access
AWS explicitly warns that executing generated SQL can be risky and recommends controls such as restricted roles, read-only databases, and sandboxing.
The Bedrock service role/query-engine identity should not have UPDATE, DELETE, DDL, or broad admin permissions for a retrieval-only knowledge base.
Apply row/column/database security at the data layer; do not rely on the model to remember which tables a user should not query.
Retrieve returns query results directly
When structured data is used with Retrieve, the service generates/executes SQL and returns the retrieval result.
This is useful when the application wants to format or reason over the rows itself.
Log generated SQL and result metadata under appropriate security controls so incorrect answers can be traced to query generation versus later language-model synthesis.
RetrieveAndGenerate adds answer generation on top of the SQL result
RetrieveAndGenerate executes retrieval and asks a model to create a response grounded in the returned structured data.
This reduces application orchestration but adds another model step whose prompt/template and grounding behavior should be evaluated.
For financial or operational metrics, consider returning both the human answer and the underlying rows/SQL or citations needed for verification.
GenerateQuery separates SQL generation from execution
The GenerateQuery API transforms an English natural-language question into SQL for the configured structured knowledge base without immediately treating the result as a completed answer.
This is useful when the application wants to inspect, approve, transform, log, or execute the SQL through a controlled workflow.
A high-risk environment can put policy checks between generated SQL and execution rather than giving every question an automatic execution path.
English-query limitation should be reflected in product behavior
Current AWS GenerateQuery documentation notes that text-to-SQL queries must be written in English.
Multilingual products should translate/normalize questions or route unsupported languages through another mechanism and evaluate whether translation changes business meaning.
Do not advertise generic multilingual structured querying unless the tested workflow actually supports it.
Cross-Region inference is used in structured retrieval
AWS notes that structured data retrieval uses cross-Region inference within the relevant geography to select an optimal Region for the inference step.
That matters for compliance architecture even if the Redshift data itself stays in one Region.
Document where SQL executes, where metadata/data resides, and where the model processing may occur as separate boundaries.
Generated SQL should have cost and complexity limits
A safe read-only query can still be operationally harmful if it scans huge tables, creates expensive joins, or runs repeatedly under load.
Use Redshift workload management, statement timeout, row limits where appropriate, materialized/curated views, and a semantic layer that keeps natural-language queries away from raw operational schemas.
Monitor query duration, scanned data, concurrency, and failure classes by knowledge-base workload.
Evaluation should include wrong-but-valid SQL
The hardest failures are not syntax errors; they are queries that run successfully but answer a subtly different business question.
Create test cases for date windows, distinct counts, slowly changing dimensions, null semantics, joins, currency/units, fiscal calendars, and security filters.
Score both generated SQL and final answer so the team can tell whether an error came from text-to-SQL or answer synthesis.
Structured retrieval succeeds when generated SQL remains governed database work
The mature design exposes curated schemas, uses least-privilege read-only identities, limits query cost, records/generated SQL, tests business semantics, and preserves the data platform’s row/column security.
Natural-language access is valuable when it lowers the barrier to trusted data—not when it creates an unbounded query agent against the production warehouse.
Curated database views can provide a safer semantic layer than exposing raw warehouse schemas. A view can rename cryptic columns, prejoin stable dimensions, hide sensitive fields, and encode business definitions such as active customer or recognized revenue. This both improves text-to-SQL accuracy and reduces the query surface the generated SQL can reach.
Row-level access should be enforced by the database/query identity where user-specific entitlements matter. If every user shares one Bedrock service role that can read all rows, the model cannot create trustworthy tenant isolation by adding a WHERE clause on its own. Consider separate query identities, secure views, RLS, or application-mediated query generation/execution depending on the requirement.
Generated SQL should be inspected for dangerous patterns even under a read-only role. Cartesian joins, unbounded scans, recursive constructs, or functions with surprising cost can degrade the warehouse. A lightweight parser/policy step can reject disallowed schemas, missing LIMIT in exploratory workflows, or queries that exceed known complexity before execution.
Business vocabulary should be documented in metadata. If users ask for “ARR,” “active users,” or “open incidents,” the model needs to know which table/column/formula represents that concept. Curated comments and semantic views are more reliable than hoping the model infers the organization’s definition from generic column names.
Time semantics are a frequent failure source. UTC versus local dates, fiscal calendars, month-end snapshots, slowly changing dimensions, event time versus load time, and late-arriving data can all produce valid SQL with the wrong meaning. Add these cases to the evaluation set and expose clear time columns/descriptions in the schema.
RetrieveAndGenerate should avoid overclaiming beyond the returned rows. If the query result is empty, partial, or aggregated, the final model should state that limitation rather than fill in a likely answer from prior knowledge. Guardrail/grounding or application instructions can reinforce this behavior.
SQL/result logging needs privacy controls. Generated queries can reveal table names and filters; result samples can contain regulated data. Store enough metadata for debugging—query hash, duration, row count, error, knowledge base, user/tenant—but redact or restrict raw result logging according to data classification.
Warehouse workload isolation can protect ordinary analytics. Use a dedicated Redshift workgroup/queue or resource controls for text-to-SQL traffic so a burst of natural-language questions does not starve scheduled BI/ETL. The generative interface should be treated as another database workload with its own concurrency and cost profile.
Structured retrieval is strongest when the user question can be answered by declarative SQL over trusted structured data. For narrative documents, policies, and unstructured evidence, vector/document retrieval is usually more appropriate. Hybrid applications can route questions to the structured or unstructured knowledge base based on domain instead of forcing every query through one retrieval technique.
Schema evolution should trigger text-to-SQL regression. Renaming a column, splitting a table, or changing a view definition can leave generated SQL syntactically valid but semantically wrong. Version curated views and rerun the evaluation corpus before exposing structural changes to the knowledge base.
Result size should be constrained before answer generation. A natural-language question can produce thousands of rows; sending all of them to a generation step is costly and can reduce answer quality. Prefer aggregates, top-N results, or a second deterministic transformation that produces a compact evidence set.
User experience should surface when the system cannot translate a question reliably. Ambiguous business terms or unsupported questions should produce a request for clarification rather than a confidently generated SQL guess. Confidence/validation logic is especially important when the resulting answer influences financial or operational decisions.
For sensitive analytics, consider a human-review or generated-SQL preview mode for privileged questions. Showing the SQL and data scope before execution can make high-impact queries understandable to analysts and provides a control point when the model interprets an ambiguous request too broadly.
Curated views should include stable business metrics whenever possible. If every user question requires the model to rediscover a complex revenue, churn, or inventory formula from raw facts, text-to-SQL accuracy will vary. Encoding approved business logic in views reduces ambiguity and keeps the generative layer focused on selection and aggregation.
Database permissions remain part of the retrieval design. Generated queries should execute through identities with narrow access, explicit limits, and auditable statements so natural-language convenience does not create a path around row, column, tenant, or workload controls.
