Empowering Non-Technical Users with Text-to-SQL for Data-Driven Decision Making

Bridging the Gap: Real-Time Insights Without Technical Expertise

Evaluating Generative AI Options for Accurate Data Queries

The Advantages of Text-to-SQL in Deterministic Data Retrieval

Implementing Amazon Nova for Enhanced Query Generation

Key Features of a Robust Text-to-SQL Solution

Step-by-Step Setup: From Prerequisites to Execution

Example Queries: Harnessing Text-to-SQL for Practical Insights

Understanding the Code Flow: Dynamic Schema Retrieval and Query Generation

Best Practices for Formatting Query Results

Cleaning Up: Ensuring Resource Efficiency in AWS

Conclusion: Embracing a Scalable Future with Text-to-SQL using Amazon Bedrock

Meet the Authors: Experts in Generative AI and Cloud Solutions

Empowering Non-Technical Users with Text-to-SQL: A Bridge to Real-Time Insights

In today’s data-driven world, businesses increasingly rely on accurate, real-time insights for critical decision-making. However, enabling non-technical users to access proprietary or organizational data without the requisite technical expertise remains a formidable challenge. This is where Text-to-SQL can make a significant impact, creating opportunities for faster decision-making and fostering a truly data-driven culture.

The Challenge of Deterministic Answers

The primary challenge faced by organizations involves generating deterministic answers from their data—precise and consistent results necessary for tasks like generating exact counts or detailed reports. Although generative AI presents numerous methods for querying data, selecting the right approach is crucial for ensuring accuracy and reliability.

In this post, we’ll evaluate key options for querying data using generative AI, emphasize the advantages of Text-to-SQL for deterministic schema-specific tasks, and illustrate how to use Amazon Nova—available in Amazon Bedrock—to derive precise insights from your organizational data.

Options for Querying Data

Organizations have a variety of options for querying data. The effectiveness of each approach often depends on the data’s nature and the desired outcomes. Let’s take a closer look at three prominent methods:

1. Retrieval Augmented Generation (RAG)

• Use Case: Ideal for extracting insights from unstructured or semi-structured sources like documents or articles.
• Strengths: Handles diverse data formats and generates narrative-style responses.
• Limitations: Provides probabilistic answers that can vary, making it unsuitable for deterministic queries, such as retrieving exact counts.
• Example: “Summarize feedback from product reviews.”

2. Generative Business Intelligence (BI)

• Use Case: Suitable for high-level insights and summary generation from structured and unstructured data.
• Strengths: Delivers narrative insights that aid decision-making and trend identification.
• Limitations: Lacks the precision required for schema-specific or operational queries; results often vary in phrasing and focus.
• Example: “What were the key drivers of sales growth last quarter?”

3. Text-to-SQL

• Use Case: Excels in querying structured organizational data directly from relational schemas.
• Strengths: Offers deterministic, reproducible results for specific, schema-dependent queries—ideal for operations like filtering, counting, or aggregating data.
• Limitations: Requires structured data and predefined schemas.
• Example: “How many patients diagnosed with diabetes visited clinics in New York City last month?”

In scenarios where precision and consistency are paramount, Text-to-SQL clearly outshines RAG and generative BI by delivering accurate, schema-driven results, making it the optimal choice for operational and structured data queries.

Solution Overview

The solution we propose leverages the capabilities of Amazon Nova Lite and Amazon Nova Pro large language models (LLMs) to simplify querying proprietary data in natural language, empowering non-technical users. Amazon Bedrock serves as a fully managed service that streamlines building and scaling generative AI applications by providing access to leading FMs through a single API.

Key Features

1. Dynamic Schema Context: Retrieves database schema dynamically for precise query generation.
2. SQL Query Generation: Converts natural language into SQL queries using Amazon Nova Pro.
3. Query Execution: Executes queries on organizational databases and retrieves results.
4. Formatted Responses: Processes raw query results into user-friendly formats using Amazon Nova Lite.

Architectural Flow

The architectural flow involves using Amazon Nova Pro and Amazon Nova Lite to harness their respective strengths efficiently:

• Dynamic Schema Retrieval and SQL Query Generation: Amazon Nova Pro translates natural language inputs into SQL queries, accurately interpreting user intents and generating precise statements.
• Formatted Response Generation: After executing SQL queries, the raw results are processed by Amazon Nova Lite to produce user-friendly outputs, making insights readily accessible.

Implementation Steps

Prerequisites

Before starting, make sure to:

1. Install the AWS Command Line Interface (CLI).
2. Enable Amazon Bedrock in your AWS account.
3. Obtain access to Amazon Nova Lite and Amazon Nova Pro.
4. Install Python 3.9 or later, along with other required libraries.
5. Create a Microsoft SQL Server database with credentials and configure AWS Secrets Manager.

Example Queries

To illustrate, consider these example queries:

1. “Who are the customers who bought smartphones?”

   • Generated SQL:

     SELECT DISTINCT CustomerName, ProductName, SUM(Quantity) AS TotalSoldQuantity 
     FROM vw_sales 
     WHERE ProductName LIKE '%smartphone%' 
     GROUP BY CustomerName, ProductName, OrderDate;

   • Formatted Response: Alice Johnson, who bought 1 smartphone on October 14th, 2023. Ivy Martinez, who bought 2 smartphones on October 15th, 2023.
2. “How many smartphones are in stock?”
   • Generated SQL:

     SELECT DISTINCT ProductName, StockQuantity AS AvailableQuantity 
     FROM vw_sales 
     WHERE ProductName LIKE '%smartphone%';

   • Response: There are 100 smartphones currently in stock.

Conclusion

Text-to-SQL, powered by Amazon Bedrock and its Nova LLMs, provides a scalable solution for deterministic, schema-based querying. By delivering consistent and precise results, this approach empowers organizations to make informed decisions, improve operational efficiency, and reduce reliance on technical resources.

To dive deeper, explore the GitHub repository for a comprehensive example. Get started with Text-to-SQL use cases today by leveraging Amazon Bedrock’s capabilities.

About the Authors

Mansi Sharma is a Solutions Architect for Amazon Web Services, specializing in generative AI application development and serverless technologies.

Marie Yap is a Principal Solutions Architect for Amazon Web Services, focusing on analytics and modern data architectures.

This post highlights how adopting Text-to-SQL can make your organizational data accessible and utilizable, paving the way for a more data-driven decision-making process. Start your journey toward data empowerment today!