Building a Text-to-SQL Knowledge Base with AWS Bedrock

Unlock the power of natural language to query your data warehouse — no SQL expertise required. This guide walks you through how to use AWS Bedrock, Amazon Redshift, and AWS CDK to build a secure, performant Text‑to‑SQL knowledge base.

Why It Matters

• Business Accessibility: Empower non-technical users to explore data using plain English.
• Security First: Fine‑grained IAM permissions ensure safe data access.
• Governance Built-In: Execution timeouts and context control prevent runaway queries.
• Infrastructure as Code: Codify and version your entire setup with AWS CDK.

What is Text-to-SQL?

Text-to-SQL is an AI capability that translates natural language questions into executable SQL queries. Instead of writing SELECT COUNT(*) FROM employees, users can simply ask “How many employees do we have?”

Architecture Overview

This solution leverages:

• AWS Bedrock Knowledge Base: Core AI service for natural language processing

• Amazon Redshift: Data warehouse for storing and querying data

• AWS CDK: Infrastructure as Code for deployment

• IAM Roles: Secure access management

Prerequisites

Before starting, ensure you have:

• AWS CLI configured with appropriate permissions

• Node.js and AWS CDK installed

• Access to an Amazon Redshift cluster

• Basic understanding of SQL and database schemas

Step 1: Setting Up the Database Schema

First, define your database structure with clear, descriptive table and column names:

```sql
 - Example employee table
CREATE TABLE quasar.public.employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE,
department_id INT
);

# **Step 2: Creating IAM Roles and Permissions**

The Knowledge Base requires specific permissions to interact with Redshift:

const knowledgeBaseRole = new iam.Role(this, 'BedrockKnowledgeBaseRole', { assumedBy: new iam.ServicePrincipal('bedrock.amazonaws.com'), }); // Redshift Data API permissions knowledgeBaseRole.addToPolicy(new iam.PolicyStatement({ actions: [ 'redshift-data:GetStatementResult', 'redshift-data:DescribeStatement', 'redshift-data:ExecuteStatement' ], resources: [''] })); // SQL Workbench access for query generation knowledgeBaseRole.addToPolicy(new iam.PolicyStatement({ actions: [ 'sqlworkbench:GetSqlRecommendations', 'sqlworkbench:PutSqlGenerationContext' ], resources: [''] }));

# **Step 3: Defining Table Metadata**

Provide detailed descriptions for tables and columns to improve query accuracy:

const tables = [ { name: 'quasar.public.employees', inclusion: 'INCLUDE', description: 'Employee information and details', columns: [ { name: 'employee_id', description: 'Unique identifier for each employee' }, { name: 'first_name', description: 'Employee first name' }, { name: 'last_name', description: 'Employee last name' }, { name: 'email', description: 'Employee email address' }, { name: 'hire_date', description: 'Date when employee was hired (YYYY-MM-DD format)' } ] } ];

# **Step 4: Creating Curated Queries**

Provide example natural language to SQL mappings to train the model:

const curatedQueries = [ { naturalLanguage: 'How many employees do we have?', sql: "SELECT COUNT(DISTINCT employee_id) FROM quasar.public.employees;" }, { naturalLanguage: 'Show me employees hired in 2023', sql: "SELECT first_name, last_name, hire_date FROM quasar.public.employees WHERE EXTRACT(YEAR FROM hire_date) = 2023;" }, { naturalLanguage: 'What are the most common first names?', sql: "SELECT first_name, COUNT(*) as count FROM quasar.public.employees GROUP BY first_name ORDER BY count DESC LIMIT 10;" } ];

# **Step 5: Configuring the Knowledge Base**

Create the Bedrock Knowledge Base with SQL configuration:

const sqlKnowledgeBase = new bedrock.CfnKnowledgeBase(this, 'SQLKnowledgeBase', { name: 'TextToSQLKnowledgeBase', roleArn: knowledgeBaseRole.roleArn, knowledgeBaseConfiguration: { type: 'SQL', sqlKnowledgeBaseConfiguration: { type: 'REDSHIFT', redshiftConfiguration: { queryEngineConfiguration: { type: 'PROVISIONED', provisionedConfiguration: { authConfiguration: { type: 'IAM' }, clusterIdentifier: 'your-cluster-name' } }, queryGenerationConfiguration: { executionTimeoutSeconds: 200, generationContext: { curatedQueries, tables } }, storageConfigurations: [{ type: 'REDSHIFT', redshiftConfiguration: { databaseName: 'your-database-name' } }] } } }, description: 'Knowledge Base for converting natural language to SQL queries' });

# **Step 6: Setting Up Database Permissions**

CREATE USER "IAMR:${service-role}" WITH PASSWORD DISABLE; GRANT SELECT ON ${schemaName}.${tableName} TO "IAMR:${serviceRole}"; GRANT USAGE ON SCHEMA ${schemaName} TO "IAMR:${serviceRole}";

**Reference Documentation**: [AWS Bedrock Knowledge Base Prerequisites for Structured Data](https://docs.aws.amazon.com/bedrock/latest/userguide/knowledge-base-prereq-structured.html)

# **Best Practices**

When building a Text-to-SQL Knowledge Base, descriptive naming is crucial for accuracy. Use clear, business-friendly names for tables and columns such as `customer_acquisition_date` instead of abbreviated forms like `cust_acq_dt`, and `total_revenue` rather than `tot_rev`. This clarity helps the AI model better understand the data context and generate more accurate queries.

Comprehensive metadata forms the foundation of effective query generation. Provide detailed descriptions that include data types and formats, business context and meaning, relationships between tables, and common use cases. This rich metadata enables the model to understand not just what the data is, but how it’s typically used in business scenarios.

Quality curated queries serve as training examples for the AI model. Create diverse examples that cover simple aggregations like COUNT, SUM, and AVG operations, filtering and sorting scenarios, date range queries, multi-table joins, and complex business logic. These examples teach the model common patterns and help it handle similar requests from users.

Security considerations must be built into the system from the ground up. Use IAM roles with least privilege access, implement row-level security where needed to protect sensitive data, monitor query execution and results for unusual patterns, and set appropriate timeout limits to prevent resource abuse. These measures ensure that democratizing data access doesn’t compromise data security or system performance.

# Full Code

[embed]

# **Conclusion**

A well-configured Text-to-SQL Knowledge Base democratizes data access, enabling business users to get insights without SQL expertise. Success depends on thorough metadata, quality curated queries, and continuous refinement based on user needs.

The investment in setup pays dividends through increased data accessibility, faster insights, and reduced burden on technical teams. Start with a focused dataset, iterate based on feedback, and gradually expand coverage as confidence grows.