{"id":4648,"date":"2025-04-15T09:59:12","date_gmt":"2025-04-15T16:59:12","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/semantic-kernel\/?p=4648"},"modified":"2025-04-23T09:52:02","modified_gmt":"2025-04-23T16:52:02","slug":"guest-blog-bridging-business-and-technology-transforming-natural-language-queries-into-sql-with-semantic-kernel-part-2","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/agent-framework\/guest-blog-bridging-business-and-technology-transforming-natural-language-queries-into-sql-with-semantic-kernel-part-2\/","title":{"rendered":"Guest Blog: Bridging Business and Technology: Transforming Natural Language Queries into SQL with Semantic Kernel Part 2"},"content":{"rendered":"

Today we’d like to welcome back a team of internal Microsoft employees for part 2 of their guest blog series focused on Bridging Business and Technology: Transforming Natural Language Queries into SQL with Semantic Kernel. We’ll turn it over to our authors – Samer El Housseini, Riccardo Chiodaroli, Daniel Labbe, Fabrizio Ruocco and Angel Sevillano Cabrera to dive in.<\/p>\n

Introduction<\/h2>\n

In today’s data-driven business landscape, access to information is critical for decision-making. However, a persistent challenge has been the technical barrier between business users who need data insights and the complex database systems that house this information. The solution? Natural Language to SQL (NL2SQL) technology – a transformative approach that allows users to query databases using everyday language rather than complex SQL syntax.<\/p>\n

In this article, we’ll explore how Microsoft’s Semantic Kernel framework provides a powerful foundation for building an innovative NL2SQL system. We’ll examine both the business value this brings to organizations and the technical implementation details that make it possible.<\/p>\n

Part 1: The Business Case for Natural Language to SQL<\/h2>\n

Breaking Down Technical Barriers<\/h3>\n

Consider this common scenario: A business analyst needs to understand “What is the average wellness index for patients with chronic conditions?” Traditionally, this would require either:<\/p>\n

    \n
  1. Learning SQL and database structure<\/li>\n
  2. Submitting a request to the IT department and waiting<\/li>\n
  3. Using limited pre-built reporting tools<\/li>\n<\/ol>\n

    None of these options is ideal. The first requires significant time investment, the second creates bottlenecks, and the third lacks flexibility. NL2SQL eliminates these barriers by allowing business users to simply ask questions in plain English.<\/p>\n

    Measurable Business Impact<\/h3>\n

    The NL2SQL approach delivers several quantifiable benefits:<\/p>\n

      \n
    1. Accelerated Decision-Making<\/strong>: By removing the technical intermediary, insights can be obtained in minutes rather than days.<\/li>\n
    2. Resource Optimization<\/strong>: IT teams can focus on higher-value activities rather than servicing routine data requests.<\/li>\n
    3. Democratized Data Access<\/strong>: More employees can leverage data, creating a more informed organization.<\/li>\n
    4. Improved Data Governance<\/strong>: Business rules are systematically enforced in the system at query time, ensuring consistently compliant queries.<\/li>\n<\/ol>\n

      Real-World Applications<\/h3>\n

      Healthcare organizations can use this technology to quickly analyze patient outcomes, insurance providers can assess coverage patterns, and pharmaceutical companies can evaluate medication adherence – all without specialized database knowledge. The application is particularly powerful in regulated industries where both speed and compliance are essential. These examples can be extended to any industry for any database-related use case.<\/p>\n

      Part 2: The Technical Architecture: Building a State Machine for NL2SQL with Semantic Kernel<\/h2>\n

      Now, let’s dive into the technical implementation of this powerful system. The architecture leverages Microsoft’s Semantic Kernel Process Framework to create a sophisticated state machine that handles the complex task of translating natural language to SQL.<\/p>\n

      The Semantic Kernel Process Framework<\/h3>\n

      Semantic Kernel provides a structured way to build AI-powered applications with a process-oriented approach. This project leverages Semantic Kernel’s Process builder to create a state machine where:<\/p>\n

        \n
      1. Each step in the NL2SQL conversion is a distinct process step<\/li>\n
      2. Events trigger transitions between steps<\/li>\n
      3. The entire workflow manages state and handles failures gracefully<\/li>\n<\/ol>\n

        The result is a robust system that can iteratively refine queries until they meet both syntactic and semantic requirements. Dive into the repo here: semantic-kernel-advanced-usage\/templates\/natural_language_to_SQL at main \u00b7 Azure-Samples\/semantic-kernel-advanced-usage<\/a>.<\/span><\/p>\n

        Innovative Multi-Stage Processing<\/h3>\n

        What makes this NL2SQL implementation particularly innovative is its multi-stage approach:<\/p>\n

          \n
        1. Table Identification<\/strong>: First determining which database tables are relevant to the query<\/li>\n
        2. Column Extraction<\/strong>: Then identifying specific columns needed from those tables identifid in the previous step<\/li>\n
        3. SQL Generation<\/strong>: Creating the initial SQL statement<\/li>\n
        4. Syntactic Validation<\/strong>: Verifying SQL correctness<\/li>\n
        5. Business Rule Validation<\/strong>: Ensuring compliance with business semantics<\/li>\n
        6. Execution<\/strong>: Running the validated query<\/li>\n<\/ol>\n

          This approach solves a key challenge in NL2SQL systems – balancing the need to understand what data is available (tables\/columns) with how to properly query it (syntax\/semantics).<\/p>\n

          \"Image<\/a><\/p>\n

          The State Machine Implementation<\/h3>\n

          Let’s examine the core components of the implementation:<\/p>\n

          def get_sql_process(self) -> KernelProcess:\r\n    \"\"\"Build and configure the SQL generation process with all steps and their transitions.\"\"\"\r\n    process = ProcessBuilder(name=\"SQLGenerationProcess\")\r\n\r\n    # Add steps to the process\r\n    table_step = process.add_step(TableNameStep)\r\n    column_step = process.add_step(ColumnNameStep)\r\n    sql_generation_step = process.add_step(SQLGenerationStep)\r\n    business_rules_step = process.add_step(BusinessRulesStep)\r\n    validation_step = process.add_step(ValidationStep)\r\n    execution_step = process.add_step(ExecutionStep)\r\n\r\n    # Define the process flow\r\n    process.on_input_event(event_id=SQLEvents.StartProcess).send_event_to(target=table_step, parameter_name=\"data\")\r\n    \r\n    table_step.on_event(event_id=SQLEvents.TableNameStepDone).send_event_to(\r\n        target=column_step, parameter_name=\"data\"\r\n    )\r\n    \r\n    column_step.on_event(event_id=SQLEvents.ColumnNameStepDone).send_event_to(\r\n        target=sql_generation_step, parameter_name=\"data\"\r\n    )\r\n    \r\n    # ...additional event flow definitions...\r\n\r\n    return process.build()\r\n<\/code><\/pre>\n
          This elegant process definition creates a workflow where:<\/p>\n
            \n
          1. The process starts by identifying tables<\/li>\n
          2. On successful table identification, it moves to column identification<\/li>\n
          3. With tables and columns identified, it generates SQL<\/li>\n
          4. The SQL undergoes business rule validation<\/li>\n
          5. Then syntax validation<\/li>\n
          6. Finally, execution if all validations pass<\/li>\n<\/ol>\n
            Most importantly, the system includes feedback loops. If validation fails at any stage, the process can return to an earlier step with information about what went wrong, enabling iterative refinement.<\/p>\n
            Prompting Excellence: The Secret to Effective NL2SQL<\/h3>\n
            At the heart of this system are carefully designed prompts that guide the language model through each step. Through iterative and repeated refinements, these prompts have been drafted and were evolved to make sure that the system\u00a0converges<\/strong>\u00a0on a solution to generate a proper SQL statement. Let’s examine one of these prompts:<\/p>\n
            get_table_names_prompt_template = \"\"\"\r\n## Instructions:\r\nYou are an advanced SQL query generator. Your task is to extract the relevant table names using the following **natural language question** from a list of business tables. \r\n\r\nYou **MUST** take a conservative approach: if in doubt whether a table is relevant or not, then you need to include it in the list. It is better to have it and not need it than to need it and not have it. Make sure to review the Business Rules when deciding on the table names.\r\n\r\nMake sure to include all foreign keys and relationships that are relevant to the user's question that are necessary to join the tables. If the tables do not have direct relationships, please analyze the situation and include any intermediary tables that can join the tables, and might be necessary to answer the user's question.\r\n\r\n## **Business Rules**\r\n{rules}\r\n\r\n## User Query\r\n{user_query}\r\n\r\n## START OF LIST OF TABLES\r\n{table_list}\r\n## END OF LIST OF TABLES\r\n\r\n# **Inputs from previous generation rounds**\r\n...\r\n\"\"\"\r\n<\/code><\/pre>\n
            This prompt is structured to: 1. Precisely define the task (extract table names) 2. Provide context (business rules, available tables) 3. Include previous generation attempts for refinement 4. Specify the expected output format<\/p>\n
            Similar specialized prompts exist for each step in the process, ensuring the language model understands exactly what’s needed at each stage.<\/p>\n
            Handling Errors and Refining Results<\/h3>\n
            A particularly powerful aspect of this system is its ability to learn from failures through the state machine’s feedback loops:<\/p>\n
            business_rules_step.on_event(event_id=SQLEvents.BusinessRulesFailed).send_event_to(\r\n    target=sql_generation_step, parameter_name=\"data\"\r\n)\r\n\r\nvalidation_step.on_event(event_id=SQLEvents.ValidationFailed).send_event_to(\r\n    target=sql_generation_step, parameter_name=\"data\"\r\n)\r\n\r\nexecution_step.on_event(event_id=SQLEvents.ExecutionError).send_event_to(\r\n    target=table_step, parameter_name=\"data\"\r\n)\r\n<\/code><\/pre>\n
            When a validation or execution fails, the system captures error details and feeds them back into earlier steps. This creates an iterative refinement process that can solve complex queries that might fail on the first attempt.<\/p>\n
            For example, if business rule validation fails, the system doesn’t simply report an error – it returns to SQL generation with notes about what rules were violated, allowing for targeted improvements.<\/p>\n
            Part 3: Data Structures – The Foundation of Intelligent SQL Generation<\/h2>\n
            At the core of this NL2SQL system are several carefully designed data structures that power the various stages of query processing. These structures provide the essential context needed for the system to understand database schema, enforce business rules, and produce accurate SQL queries.<\/p>\n
            Table Descriptions: The First Layer of Understanding<\/h3>\n
            The system begins its process with a comprehensive set of table descriptions that provide high-level information about each database entity. This structure is critical for the initial table identification step, allowing the system to determine which tables might be relevant to a user’s query.<\/p>\n
            Each table description includes: – The table name (e.g., “HC_Patient_Daily_Summary_v3”) – A detailed narrative description of the table’s purpose and contents – Information about relationships with other tables through foreign keys<\/p>\n
            For example, when a user asks about “medication adherence for patients with chronic conditions,” the system can quickly identify that both the medication summary and patient summary tables are likely relevant based on these descriptions.<\/p>\n
            Database Schema Model: Detailed Column-Level Knowledge<\/h3>\n
            Once relevant tables are identified, the system leverages a more detailed schema model that includes comprehensive information about each column within those tables. This model provides:<\/p>\n