How To Integrate Amazon Bedrock’s Claude 3 Sonnet for SQL generation and Amazon Titan Embeddings with Bedrock Knowledge Bases for contextual grounding

Introduction

Converting natural language questions into precise SQL queries remains a significant challenge in building intuitive data exploration tools. Traditional approaches often rely on rigid rule-based systems or complex semantic parsing, which struggle with the inherent variability and ambiguity of human language. The advent of large language models (LLMs) has opened new avenues for this problem, offering unprecedented capabilities in understanding context and generating code. However, even the most advanced LLMs can hallucinate or produce incorrect SQL queries if they lack specific knowledge about the underlying database schema, relationships, or specific data values.

This article introduces a robust solution that combines the generative power of LLMs with the precision of retrieval-augmented generation (RAG). By integrating Amazon Bedrock’s Claude 3 Sonnet for SQL generation and Amazon Titan Embeddings with Bedrock Knowledge Bases for contextual grounding, we can build a highly accurate and reliable text-to-SQL application. This approach ensures that the LLM is always informed by the most relevant and up-to-date database schema and examples, significantly enhancing the accuracy and relevance of generated SQL queries.

Solution Overview

Our solution leverages a RAG architecture to provide Claude 3 Sonnet with the necessary context to generate accurate SQL queries. The process begins with a user’s natural language query. This query is then transformed into a vector embedding using Amazon Titan Embeddings. This embedding is used to search a pre-indexed Knowledge Base, which contains essential information about the database schema, sample queries, and relevant documentation. The retrieved context, combined with the user’s original query, is then fed into Claude 3 Sonnet via a carefully crafted prompt template. Claude 3 Sonnet, with its advanced reasoning capabilities, generates the corresponding SQL query, which can then be executed against the database.

The following diagram illustrates the high-level architecture and flow of queries:

Prerequisites

To implement this solution, you will need access to the following AWS services and resources:

• Amazon Bedrock: For accessing Claude 3 Sonnet as a Foundation Model and Amazon Titan Embeddings.
• Amazon S3: To store your database schema, sample queries, and documentation files that will be indexed by the Knowledge Base.
• Amazon Bedrock Knowledge Bases: To create, index, and retrieve relevant context for the LLM.
• AWS Identity and Access Management (IAM): For managing permissions to AWS services.
• Optionally, a relational database: Such as Amazon RDS or Amazon Aurora, to connect to and execute the generated SQL queries for testing.

IAM Role Setup and Policy Requirements:

You will need an IAM role that grants your application permission to interact with Amazon Bedrock. The role should have the following minimum permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "bedrock:InvokeModel",
                "bedrock:Retrieve",
                "bedrock:ListFoundationModels",
                "bedrock:ListKnowledgeBases",
                "bedrock:GetKnowledgeBase",
                "bedrock:CreateKnowledgeBase",
                "bedrock:CreateDataSource",
                "bedrock:StartIngestionJob"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::your-knowledge-base-bucket/*",
                "arn:aws:s3:::your-knowledge-base-bucket"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "iam:PassRole"
            ],
            "Resource": "arn:aws:iam::YOUR_AWS_ACCOUNT_ID:role/BedrockKnowledgeBaseServiceRole"
        }
    ]
}

Note: Replace your-knowledge-base-bucket and YOUR_AWS_ACCOUNT_ID with your actual S3 bucket name and AWS account ID.

Set Up Your Amazon Bedrock Knowledge Base

The Amazon Bedrock Knowledge Base is central to our RAG architecture. It stores and indexes your database schema and other relevant information, enabling efficient retrieval during the query process.

1. Prepare Your Data: Create text files containing your database schema (e.g., DDL statements), sample SQL queries, and any relevant documentation that can help the LLM understand your data model. For instance:

schema.sql

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    registration_date DATE
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

CREATE TABLE OrderItems (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

sample_queries.txt

-- Example: Get all customers registered in 2023
SELECT * FROM Customers WHERE registration_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Example: Calculate total sales for each product category
SELECT p.category, SUM(oi.quantity * oi.unit_price) AS total_sales
FROM OrderItems oi
JOIN Products p ON oi.product_id = p.product_id
GROUP BY p.category;

-- Example: Find customers who placed more than 5 orders
SELECT c.first_name, c.last_name, COUNT(o.order_id) AS total_orders
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) > 5;

2. Upload to S3: Upload these files to an S3 bucket that will serve as the data source for your Knowledge Base.

3. Create Knowledge Base and Data Source: Use the boto3 library to create and configure your Amazon Bedrock Knowledge Base.

import boto3
import time

bedrock_agent_client = boto3.client('bedrock-agent')

# Configuration
KB_NAME = "TextToSQLKnowledgeBase"
S3_BUCKET_NAME = "your-text-to-sql-kb-bucket" # Replace with your S3 bucket
KB_DESCRIPTION = "Knowledge base for SQL schema and sample queries for text-to-SQL conversion."
KB_ROLE_ARN = "arn:aws:iam::YOUR_AWS_ACCOUNT_ID:role/BedrockKnowledgeBaseServiceRole" # Replace with your IAM role ARN

# 1. Create Knowledge Base
def create_knowledge_base():
    try:
        response = bedrock_agent_client.create_knowledge_base(
            name=KB_NAME,
            description=KB_DESCRIPTION,
            roleArn=KB_ROLE_ARN,
            knowledgeBaseConfiguration={
                'type': 'VECTOR_DATABASE',
                'vectorKnowledgeBaseConfiguration': {
                    'embeddingModelArn': 'arn:aws:bedrock:us-east-1::foundation-model/amazon.titan-embed-text-v1'
                }
            }
        )
        kb_id = response['knowledgeBase']['knowledgeBaseId']
        print(f"Knowledge Base '{KB_NAME}' created with ID: {kb_id}")
        return kb_id
    except Exception as e:
        print(f"Error creating Knowledge Base: {e}")
        return None

# 2. Create Data Source
def create_data_source(kb_id):
    try:
        response = bedrock_agent_client.create_data_source(
            knowledgeBaseId=kb_id,
            name="SQLSchemaDataSource",
            dataSourceConfiguration={
                'type': 'S3',
                's3Configuration': {
                    'bucketArn': f'arn:aws:s3:::{S3_BUCKET_NAME}'
                }
            },
            vectorIngestionConfiguration={
                'chunkingConfiguration': {
                    'chunkingStrategy': 'FIXED_SIZE',
                    'fixedSizeChunkingConfiguration': {
                        'maxTokens': 500,
                        'overlapPercentage': 20
                    }
                }
            }
        )
        ds_id = response['dataSource']['dataSourceId']
        print(f"Data Source 'SQLSchemaDataSource' created with ID: {ds_id}")
        return ds_id
    except Exception as e:
        print(f"Error creating Data Source: {e}")
        return None

# 3. Start Ingestion Job
def start_ingestion_job(kb_id, ds_id):
    try:
        response = bedrock_agent_client.start_ingestion_job(
            knowledgeBaseId=kb_id,
            dataSourceId=ds_id
        )
        job_id = response['ingestionJob']['ingestionJobId']
        status = response['ingestionJob']['status']
        print(f"Ingestion job started with ID: {job_id}, Status: {status}")

        # Poll for job completion
        while status not in ['COMPLETE', 'FAILED']:
            time.sleep(30) # Wait for 30 seconds
            job_status_response = bedrock_agent_client.get_ingestion_job(
                knowledgeBaseId=kb_id,
                dataSourceId=ds_id,
                ingestionJobId=job_id
            )
            status = job_status_response['ingestionJob']['status']
            print(f"Ingestion job status: {status}")
        
        if status == 'COMPLETE':
            print("Ingestion job completed successfully.")
        else:
            print("Ingestion job failed.")
        return status
    except Exception as e:
        print(f"Error starting or monitoring ingestion job: {e}")
        return None

if __name__ == "__main__":
    kb_id = create_knowledge_base()
    if kb_id:
        ds_id = create_data_source(kb_id)
        if ds_id:
            start_ingestion_job(kb_id, ds_id)

This script automates the creation of your Knowledge Base and its data source, and initiates the ingestion process, indexing your S3 documents with Amazon Titan Embeddings.

Designing the Prompt Template for Claude 3 Sonnet

The prompt template is crucial for guiding Claude 3 Sonnet to generate accurate SQL. It should include the natural language question, the retrieved context (database schema and examples), and clear instructions for SQL generation.

Here’s a structured approach to a robust prompt:

You are an expert SQL query generator. Your task is to convert natural language questions into valid SQL queries based on the provided database schema and examples.

<schema_information>
{schema_context}
</schema_information>

<sample_queries>
{sample_queries_context}
</sample_queries>

<rules>
- Use only the tables and columns provided in the schema.
- Do NOT make up table or column names.
- If a column name is ambiguous (e.g., 'name'), try to infer from the question or ask for clarification.
- If the question cannot be answered from the provided schema, state that explicitly.
- Ensure all queries are syntactically correct SQL.
- For aggregate functions like SUM, AVG, COUNT, always provide an alias.
- If the question asks for top N results, use LIMIT.
- Use JOINs when necessary to link tables.
- Return only the SQL query, without any additional text or explanations.
</rules>

Question: {natural_language_question}

SQL Query:

Explanation of prompt components:

• Role Definition: “You are an expert SQL query generator…” sets the context for the LLM.
• <schema_information>: This tag will be populated with the DDL statements retrieved from the Knowledge Base.
• <sample_queries>: This tag will contain relevant sample SQL queries and their natural language counterparts, retrieved from the Knowledge Base. This provides few-shot examples for the LLM.
• <rules>: A set of explicit instructions and constraints for generating valid SQL, helping prevent common errors and hallucinations.
• Question: {natural_language_question}: The user’s input.
• SQL Query:: A clear instruction for the LLM to output only the SQL query.

Example Prompt and Claude 3 Completion Output:

Let’s assume our Knowledge Base has retrieved the Customers and Orders table DDL and a sample query.

Retrieved schema_context (simplified for brevity):

CREATE TABLE Customers (customer_id INT PRIMARY KEY, first_name VARCHAR(50));
CREATE TABLE Orders (order_id INT PRIMARY KEY, customer_id INT, total_amount DECIMAL(10, 2));

Retrieved sample_queries_context (simplified):

-- Example: Get total orders for customer 1
SELECT COUNT(order_id) FROM Orders WHERE customer_id = 1;

Natural Language Question: “Show me the total amount of all orders.”

Full Prompt:

You are an expert SQL query generator. Your task is to convert natural language questions into valid SQL queries based on the provided database schema and examples.

<schema_information>
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    registration_date DATE
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
</schema_information>

<sample_queries>
-- Example: Get all customers registered in 2023
SELECT * FROM Customers WHERE registration_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Example: Calculate total sales for each product category
SELECT p.category, SUM(oi.quantity * oi.unit_price) AS total_sales
FROM OrderItems oi
JOIN Products p ON oi.product_id = p.product_id
GROUP BY p.category;

-- Example: Find customers who placed more than 5 orders
SELECT c.first_name, c.last_name, COUNT(o.order_id) AS total_orders
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) > 5;
</sample_queries>

<rules>
- Use only the tables and columns provided in the schema.
- Do NOT make up table or column names.
- If a column name is ambiguous (e.g., 'name'), try to infer from the question or ask for clarification.
- If the question cannot be answered from the provided schema, state that explicitly.
- Ensure all queries are syntactically correct SQL.
- For aggregate functions like SUM, AVG, COUNT, always provide an alias.
- If the question asks for top N results, use LIMIT.
- Use JOINs when necessary to link tables.
- Return only the SQL query, without any additional text or explanations.
</rules>

Question: Show me the total amount of all orders.

SQL Query:

Claude 3 Sonnet Completion Output:

SELECT SUM(total_amount) AS total_orders_amount FROM Orders;

Building the Application Pipeline

Now, let’s assemble the Python application using the boto3 SDK to orchestrate the RAG flow.

import boto3
import json

# AWS Bedrock clients
bedrock_runtime_client = boto3.client('bedrock-runtime')
bedrock_agent_runtime_client = boto3.client('bedrock-agent-runtime')

# Configuration
KNOWLEDGE_BASE_ID = "YOUR_KNOWLEDGE_BASE_ID" # Replace with your KB ID

def get_bedrock_response(model_id, prompt, max_tokens=2048, temperature=0.0):
    """Invokes a Bedrock model with the given prompt."""
    body = {
        "anthropic_version": "bedrock-2023-05-31",
        "messages": [
            {
                "role": "user",
                "content": prompt
            }
        ],
        "max_tokens": max_tokens,
        "temperature": temperature
    }
    
    response = bedrock_runtime_client.invoke_model(
        modelId=model_id,
        contentType="application/json",
        accept="application/json",
        body=json.dumps(body)
    )
    
    response_body = json.loads(response['body'].read())
    return response_body['content'][0]['text']

def retrieve_context_from_kb(query, kb_id, top_k=5):
    """Retrieves relevant context from Bedrock Knowledge Base."""
    response = bedrock_agent_runtime_client.retrieve(
        knowledgeBaseId=kb_id,
        retrievalQuery={
            'text': query
        },
        retrievalConfiguration={
            'vectorSearchConfiguration': {
                'numberOfResults': top_k
            }
        }
    )
    
    contexts = [result['content']['text'] for result in response['retrievalResults']]
    return "\n\n".join(contexts)

def build_prompt(question, schema_context, sample_queries_context):
    """Constructs the prompt for Claude 3 Sonnet."""
    prompt_template = f"""
You are an expert SQL query generator. Your task is to convert natural language questions into valid SQL queries based on the provided database schema and examples.

<schema_information>
{schema_context}
</schema_information>

<sample_queries>
{sample_queries_context}
</sample_queries>

<rules>
- Use only the tables and columns provided in the schema.
- Do NOT make up table or column names.
- If a column name is ambiguous (e.g., 'name'), try to infer from the question or ask for clarification.
- If the question cannot be answered from the provided schema, state that explicitly.
- Ensure all queries are syntactically correct SQL.
- For aggregate functions like SUM, AVG, COUNT, always provide an alias.
- If the question asks for top N results, use LIMIT.
- Use JOINs when necessary to link tables.
- Return only the SQL query, without any additional text or explanations.
</rules>

Question: {question}

SQL Query:
"""
    return prompt_template

def text_to_sql_pipeline(natural_language_query):
    """
    End-to-end pipeline for converting natural language to SQL.
    """
    print(f"User Query: {natural_language_query}")

    # 1. Retrieve context from Knowledge Base
    retrieved_context = retrieve_context_from_kb(natural_language_query, KNOWLEDGE_BASE_ID, top_k=5)
    
    # Separate schema and sample queries if possible (based on your document structure)
    # For simplicity, we assume retrieved_context contains both for now.
    # In a more advanced scenario, you might categorize and retrieve different types of content.
    schema_context = retrieved_context # Or apply logic to extract only schema
    sample_queries_context = retrieved_context # Or apply logic to extract only sample queries

    print("\n--- Retrieved Context ---")
    print(retrieved_context)
    print("-------------------------")

    # 2. Build the prompt
    full_prompt = build_prompt(natural_language_query, schema_context, sample_queries_context)

    # 3. Invoke Claude 3 Sonnet
    print("\n--- Invoking Claude 3 Sonnet ---")
    generated_sql = get_bedrock_response(
        model_id="anthropic.claude-3-sonnet-20240229-v1:0",
        prompt=full_prompt,
        temperature=0.0 # Keep low for deterministic SQL generation
    )
    
    # 4. Post-process response (remove potential backticks or extra text)
    # Claude 3 often encloses code in markdown blocks.
    if generated_sql.startswith('```sql') and generated_sql.endswith('```'):
        generated_sql = generated_sql[7:-3].strip() # Remove ```sql and ```
    elif generated_sql.startswith('```') and generated_sql.endswith('```'):
        generated_sql = generated_sql[3:-3].strip() # Remove general ```
    
    print("\n--- Generated SQL ---")
    print(generated_sql)
    print("---------------------")
    
    return generated_sql

if __name__ == "__main__":
    # Example usage
    query1 = "What were the top 5 products sold last quarter?"
    sql1 = text_to_sql_pipeline(query1)

    query2 = "Show me total revenue grouped by region"
    sql2 = text_to_sql_pipeline(query2)

    query3 = "How many customers registered in the last year?"
    sql3 = text_to_sql_pipeline(query3)

Note: Remember to replace YOUR_KNOWLEDGE_BASE_ID with the actual ID of the Knowledge Base you created. The retrieve_context_from_kb function simply retrieves all relevant text. In a more sophisticated setup, you might tag documents in S3 (e.g., type:schema, type:example) and retrieve specific types of context.

Handling SQL Execution (Optional)

After generating the SQL query, you’ll typically want to execute it against your relational database. This step involves connecting to the database, validating the SQL, and fetching the results.

import psycopg2 # Example for PostgreSQL. Use appropriate driver for your DB.

# Database connection details (replace with your actual credentials)
DB_HOST = "your-rds-endpoint.aws.com"
DB_NAME = "your_database_name"
DB_USER = "your_db_username"
DB_PASSWORD = "your_db_password"
DB_PORT = 5432 # Default for PostgreSQL

def execute_sql_query(sql_query):
    """
    Connects to the database and executes the given SQL query.
    Returns the results.
    """
    conn = None
    try:
        conn = psycopg2.connect(
            host=DB_HOST,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            port=DB_PORT
        )
        cur = conn.cursor()
        
        # Validate SQL query (basic check: ensure it's a SELECT statement for safety)
        # More robust validation might involve parsing the SQL or using database-specific functions
        if not sql_query.strip().upper().startswith('SELECT'):
            raise ValueError("Only SELECT queries are allowed for security.")
            
        cur.execute(sql_query)
        
        # Fetch results
        results = cur.fetchall()
        column_names = [desc[0] for desc in cur.description]
        
        cur.close()
        return column_names, results

    except ValueError as ve:
        print(f"SQL Validation Error: {ve}")
        return None, None
    except psycopg2.Error as e:
        print(f"Database Error: {e}")
        return None, None
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    # Example of integrating SQL execution with the pipeline
    natural_language_query = "What is the average total amount of orders?"
    generated_sql = text_to_sql_pipeline(natural_language_query)

    if generated_sql and generated_sql.strip():
        print(f"\nAttempting to execute: {generated_sql}")
        columns, data = execute_sql_query(generated_sql)
        if columns and data:
            print("\n--- Query Results ---")
            print(columns)
            for row in data:
                print(row)
            print("---------------------")

Important Security Note: When executing generated SQL, always implement strict validation and sanitization. Never directly execute arbitrary SQL generated by an LLM without proper scrutiny, especially in production environments, to prevent SQL injection or unintended data modifications. Consider using a whitelist of allowed query types or a dedicated SQL parsing library for robust validation.

Use Case: Natural Language Query on Sales Data

Let’s illustrate with our sample sales data schema.

Example Table Schema: (As provided in schema.sql previously)

• Customers (customer_id, first_name, last_name, email, registration_date)
• Orders (order_id, customer_id, order_date, total_amount, status)
• Products (product_id, product_name, category, price)
• OrderItems (order_item_id, order_id, product_id, quantity, unit_price)

Example Queries and Outputs:

1. Natural Language Query: “What were the top 5 products sold last quarter?”

• RAG Process:
  • Query embedded by Titan Embeddings.
  • Knowledge Base retrieves Products and OrderItems schema, and potentially examples of TOP N queries.
  • Prompt constructed with context.
  • Claude 3 Sonnet receives prompt.
• Claude 3-Generated SQL:

SELECT p.product_name, SUM(oi.quantity) AS total_quantity_sold FROM OrderItems oi JOIN Products p ON oi.product_id = p.product_id JOIN Orders o ON oi.order_id = o.order_id WHERE o.order_date >= DATE(‘now’, ‘-3 months’) — Assuming ‘last quarter’ means last 3 months relative to current date GROUP BY p.product_name ORDER BY total_quantity_sold DESC LIMIT 5; “`

• Execution and Result (example output): ['product_name', 'total_quantity_sold'] ('Laptop Pro', 150) ('Wireless Mouse', 120) ('Mechanical Keyboard', 100) ('USB-C Hub', 90) ('External SSD', 80)

2. Natural Language Query: “Show me total revenue grouped by region”

• RAG Process:
  • Query embedded by Titan Embeddings.
  • Knowledge Base retrieves Orders schema. (Note: If ‘region’ is not in the schema, the LLM will indicate it or make an assumption based on other docs if available. For a precise answer, ‘region’ would need to be in a table like Customers or a separate Regions table, and included in the Knowledge Base.)
  • Prompt constructed.
  • Claude 3 Sonnet generates SQL.
• Claude 3-Generated SQL (assuming Customers table has a region column added to schema.sql and KB): SQLSELECT c.region, SUM(o.total_amount) AS total_revenue FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id GROUP BY c.region ORDER BY total_revenue DESC;
• Execution and Result (example output): ['region', 'total_revenue'] ('North', 150000.00) ('South', 120000.50) ('East', 95000.75) ('West', 80000.25) (If ‘region’ is not in schema): I cannot answer this question as the database schema does not contain information about 'region'.

Evaluation and Refinement

Evaluating the accuracy of generated SQL queries is crucial for improving the system.

• Accuracy Metrics:
  • Exact Match: Does the generated SQL exactly match a correct SQL query?
  • Execution Accuracy: Does the generated SQL run successfully and produce the correct results when executed against the database? This is the most practical metric.
  • Schema Adherence: Does the query only use valid table and column names from the provided schema?
• Tuning Retrieval Configuration:
  • Chunk Size & Overlap: Experiment with different maxTokens and overlapPercentage in your Knowledge Base data source configuration. Smaller chunks might retrieve more precise context for very specific questions, while larger chunks provide more surrounding information.
  • Top-K: Adjust the numberOfResults (top-K) in the retrieve call. Retrieving more chunks might provide richer context but also introduces noise. Start with a small number (e.g., 3-5) and increase if necessary.
• Prompt Strategy:
  • Instruction Clarity: Refine the <rules> section in your prompt. Be very explicit about desired output format, error handling, and constraints.
  • Example Quantity and Quality: Add more diverse and complex sample_queries_context to your Knowledge Base. These few-shot examples are highly effective for teaching the LLM how to translate certain patterns.
• Iterative Refinement:
  • Continuously add more schema elements, sample queries (especially for common or tricky patterns), and documentation to your Knowledge Base.
  • Monitor cases where the LLM produces incorrect SQL. Analyze why it failed (e.g., missing schema detail, ambiguous phrasing, incorrect prompt instruction) and update your Knowledge Base or prompt accordingly.

Best Practices

• Clear Table/Column Names: Use descriptive and unambiguous names for your tables and columns in your database schema. This directly translates to better LLM understanding and SQL generation. Avoid abbreviations where possible.
• Minimal and Structured Prompt Templates: While detailed, keep your prompt templates as concise and structured as possible. Use XML-like tags (e.g., <schema_information>) to clearly delineate different sections of context.
• Monitor Token Usage and Latency: Be mindful of the number of tokens in your prompts, especially when retrieving large amounts of context. Longer prompts consume more tokens and can increase latency and cost.
• Secure Access:
  • Implement IAM least privilege for Bedrock API calls and S3 bucket access.
  • Use VPC endpoints for Bedrock and S3 to keep traffic within your AWS network.
  • For database connections, use AWS Secrets Manager to securely store credentials and IAM roles for database access where possible.
  • Never expose your database directly to the internet.
• Version Control: Store your schema files, sample queries, and prompt templates in a version control system (e.g., Git) to track changes and facilitate collaboration.
• Logging and Monitoring: Implement logging for all stages of the pipeline: user queries, retrieved context, generated SQL, and execution results. Use Amazon CloudWatch for monitoring Bedrock invocation metrics and potential errors.

Conclusion

Building a reliable text-to-SQL application powered by generative AI transforms how users interact with structured data. By adopting a Retrieval-Augmented Generation (RAG) approach with Amazon Bedrock, leveraging Claude 3 Sonnet for its advanced reasoning and Amazon Titan Embeddings for efficient context retrieval via Bedrock Knowledge Bases, we can overcome the inherent limitations of LLMs when dealing with specific domain knowledge.

This modular architecture provides a robust, scalable, and secure way to create natural language interfaces for your databases. The power of Bedrock’s components allows for flexible integration and continuous improvement through iterative refinement of your Knowledge Base and prompt strategies. We encourage readers to extend this system by incorporating real-time database schema updates, implementing advanced query logging for analytics, and exploring schema auto-discovery mechanisms to further enhance the user experience and application intelligence.