Asurion Cuts Query Times 50-Fold With Scoped Text-to-SQL Engine

Research to Reality: Robust NLP to Analytics with LLMs at Scale With Asurion — Ai4

Ai4

Enterprise text-to-SQL reliability requires dedicating 80% of engineering effort to metadata curation and scoping queries to curated domain views rather than expanding LLM context windows.

AI-assisted summary of Research to Reality: Robust NLP to Analytics with LLMs at Scale With Asurion — original source. Check the original for full context.

Source published:

Detailed notes

## Background Published by Ai4 on YouTube on August 26, 2025, this presentation features Karthik Raparthi, Lead ML/Data Engineer at Asurion. According to the page description, the talk covers how Asurion transformed NLP research into an enterprise-grade text-to-SQL system that cut query development time by 50x while addressing metadata sparsity, deterministic generation, and user trust.

## TL;DR Karthik Raparthi details Asurion's multi-phase evolution from failed zero-shot and brittle few-shot prompting to an enterprise retrieval-augmented generation (RAG) platform that translates plain-English business prompts into SQL queries, tabular data, and visual charts. To overcome severe enterprise challenges, Asurion automated metadata enrichment across thousands of tables using historical query logs and decomposed requests into modular units—such as explicit KPIs, metrics, filters, and dimensions—ensuring deterministic query generation. The production system incorporates an automated self-healing retry loop, OpenSearch hybrid search, strict read-only guardrails, and role-based domain scoping to narrow table candidates to roughly 20 curated enterprise views. In the closing Q&A, Raparthi elaborates on managing multi-turn session memory with DynamoDB and powering dynamic visualizations via LLM-generated Python scripts and PyGWalker.

## Atmosphere & style The presentation carries the pragmatic, battle-tested tone of a senior engineer recounting real-world lessons from the trenches rather than hyping unproven AI promises. Raparthi delivers a clear, systematically structured walkthrough that balances frank admissions of early failures with tangible architectural blueprints. The atmosphere feels like an intimate industry breakout session, marked by focused technical depth, practical problem-solving, and thoughtful, candid interactions during the audience Q&A.

## Key takeaways - [00:04](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=4s) Asurion supports over 300 million global subscribers across 15 countries, operating at a massive scale that requires efficient data access for operational decisions. - [01:09](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=69s) Business users face data access bottlenecks due to lack of SQL fluency, forcing them to rely on data engineers and wait hours, days, or weeks for analytical queries. - [02:14](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=134s) Zero-shot prompting completely failed for enterprise text-to-SQL generation, while few-shot prompting failed to scale across diverse domain requirements. - [02:14](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=134s) Standard RAG improved schema awareness, but LLMs still struggled with analytical queries until business metrics, KPIs, and filter logic were explicitly modeled. - [03:47](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=227s) The production platform provides an automated retry mechanism that feeds database error messages back into the LLM up to three times to self-heal failed SQL queries. - [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) Phase 1 architecture incorporated dual-intent routing to separate knowledge documentation requests from SQL data queries, along with read-only security guardrails. - [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) Implementing the text-to-SQL solution delivered a 50x acceleration in query turnaround time across the enterprise. - [07:51](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=471s) Data curation and metadata modeling account for 80% of the total effort in building reliable NLP-to-analytics systems, compared to only 20% for LLM execution and UI. - [07:51](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=471s) Asurion used LLMs to reverse-engineer historical SQL logs to fill missing metadata, pruning table candidates from over 1,000 down to roughly 20 domain views. - [07:51](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=471s) To achieve deterministic reporting, complex user queries are decomposed into atomic business units (KPIs, metrics, filters, and dimensions) tied to pre-defined formulas. - [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) Accuracy was drastically improved by restricting the retrieval search scope to federated domain boundaries and curated enterprise views based on user authentication. - [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) To prevent context dilution during multi-turn conversations, DynamoDB session history is strictly capped to the last five conversational turns. - [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) Visualizations are generated dynamically using either LLM-generated Python rendering code or the embedded PyGWalker drag-and-drop tool for user exploration.

## Detailed walkthrough ### [00:04](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=4s) Introduction and Company Background

Karthik Raparthi, Lead ML/Data Engineer at Asurion, introduces the presentation on taking natural language processing (NLP) to analytics from "research to reality" at enterprise scale. Asurion operates as a global tech-care company supporting over 300 million subscribers across approximately 15 countries. The company handles services spanning mobile repairs, major appliances, and connected home ecosystems, supported by roughly 9,000 experts and 24/7 live chat operations.

```mermaid flowchart LR accTitle: Asurion Scale Overview A["Asurion Tech Care"] --> B["300M+ Global Subscribers"] A --> C["15 Countries"] A --> D["9,000 Experts & 24/7 Support"] A --> E["Mobile, Appliances & Connected Home"] ```

The presentation focuses on the operational challenges and multi-phase architecture behind building an enterprise-grade text-to-SQL platform that bridges the gap between non-technical business stakeholders and complex enterprise data warehouses.

---

[01:09](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=69s) The Business Problem: Technical Bottlenecks in Data Access

Business users routinely need answers to data-driven and analytical questions to make critical operational decisions. However, they frequently run into technical barriers. While business users possess deep domain expertise, they often lack SQL fluency and must rely on data engineers or analysts to write queries for them.

This reliance creates severe bottlenecks. Business stakeholders often wait days, weeks, or even hours for routine data requests, introducing friction and delays into decision-making cycles. Asurion set out to build an AI assistant capable of translating plain English business prompts into accurate SQL queries, summarized insights, and visual analytics within minutes or seconds.

---

[02:14](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=134s) The Prompting Evolution: Zero-Shot, Few-Shot, and RAG

Asurion's journey began with standard prompting techniques before arriving at a robust retrieval-augmented generation (RAG) architecture:

```mermaid flowchart TD accTitle: NLP to Analytics Prompting Evolution A["Phase 0: Zero-Shot Prompting<br/>(No context provided)"] -->|Complete failure / Irrelevant SQL| B["Phase 0.5: Few-Shot Prompting<br/>(Hardcoded SQL examples)"] B -->|Fails to scale across domain variety| C["Phase 1: Standard RAG<br/>(Table/column descriptions & terminology)"] C -->|Lacks metric & filter awareness| D["Phase 2: KPI-Driven RAG + Feedback Loop<br/>(Deterministic modular units)"] ```

- **Zero-Shot Prompting:** The initial experiment provided plain prompts to the LLM without database context. This failed entirely, producing inaccurate or completely irrelevant SQL queries. - **Few-Shot Prompting:** In-context SQL examples were added to guide the model. While effective for narrow test cases, few-shot prompting broke down at scale because manually curating and fitting hundreds of domain-specific SQL examples into prompt windows was unsustainable. - **Standard Table/Column RAG:** Retrieval-Augmented Generation was introduced to dynamically retrieve table descriptions, column definitions, and business terminology. This significantly improved relevance, but the LLM still struggled with complex analytical queries because it lacked explicit awareness of business metrics, key performance indicators (KPIs), and filtering logic.

---

[03:47](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=227s) User Interface and Self-Healing Query Execution

The production user interface allows users to enter a plain-English question (such as *"What are the insights for my claims?"*) and returns an executive summary, supporting tabular data, and dynamic chart visualizations.

Under the hood, the system exposes full context and query execution mechanics: - **Chain-of-Thought & Context Attribution:** Users and analysts can inspect the generated SQL query, see the underlying tables used, and review the retrieval relevancy scores returned by the RAG vector store. - **Self-Healing Retry Mechanism:** If the database engine returns an error upon executing the generated SQL, the system enters an automated retry loop. It feeds the error message back to the LLM to debug and fix the query in real time, capped at a limit of three retries. - **Continuous User Feedback:** Feedback collection widgets (thumbs up/down and text notes) allow business users and analysts to flag incorrect intent, wrong schemas, or bad outputs, continuously enriching the downstream semantic retrieval cache.

---

[04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) Phase 1 Architecture: Intent Routing, Hybrid Search, and Guardrails

The Phase 1 architecture integrates query routing, hybrid vector retrieval, and database execution:

```mermaid flowchart TD accTitle: Phase 1 System Architecture User["Business User Query (Plain English)"] --> Intent["Intent Classification & Security Guardrails"] Intent -->|"Document Intent (Definitions, Churn rate, TAT)"| DocAgent["Knowledge Document Agent"] Intent -->|"Data Intent (Analytics / Metrics)"| ContextAgent["Context Gathering Agent"]

subgraph UKF["Unified Knowledge Foundation (OpenSearch Vector Store)"] Tables["Table & Column Descriptions"] Terms["Business Terminology & Synonyms"] Examples["Few-Shot Query Examples"] KPIs["KPI Definitions & Documentation"] end

UKF <-->|"Hybrid Search"| ContextAgent ContextAgent --> SQLGen["SQL Generation Agent"] SQLGen --> DBEngine["Database Execution Engine"] DBEngine -->|"Execution Error"| Retry{"Retry Loop (Max 3)"} Retry -->|Fix Query| SQLGen DBEngine -->|"Success"| Insights["Insights, Tabular Data & Visualizations"] Insights --> Feedback["Continuous Feedback Loop"] Feedback --> UKF ```

- **Dual-Intent Routing:** The orchestrator determines whether an incoming prompt is a *documentation question* (e.g., explaining business definitions such as *"What is churn rate?"* or *"What is turnaround time?"*) or a *data query* requiring SQL generation. - **Security Guardrails:** Hard constraints prevent prompt injection attacks and restrict database operations strictly to read-only `SELECT` statements. - **Hybrid Search Context Retrieval:** OpenSearch runs hybrid semantic and keyword search across table descriptions, column schemas, few-shot examples, and business glossaries. - **Measurable Business Impact:** The deployment delivered an estimated 50x acceleration in query turnaround time, shrinking data delivery cycles from days or weeks down to immediate answers.

---

[07:51](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=471s) The Phase 2 Shift: Solving Metadata Sparsity and Non-Determinism

Despite the success of Phase 1, Asurion identified core bottlenecks preventing enterprise reliability:

``` Total Effort Breakdown in NLP-to-Analytics: [████████████████░░░░] 80% Data Curation & Metadata Modeling [░░░░░░░░░░░░░░░░████] 20% LLM Execution, Semantic Search & UI ```

1. **Metadata Sparsity (80% of the effort):** Enterprise data environments suffer from missing or inconsistent documentation. The term "customer" might be represented across dozens of disconnected tables with varying business definitions. To solve this, Asurion used LLMs to reverse-engineer historical SQL logs and generate standardized table descriptions, column-level annotations, and domain synonyms. This contextual pruning narrowed search candidate spaces from over 1,000 tables down to roughly 20 domain-specific tables. 2. **Deterministic Query Generation:** LLMs are inherently non-deterministic, posing risks when analytical queries must return identical underlying numbers. Asurion addressed this by modularizing queries into atomic business units: decomposing requests into explicit **KPIs**, **metrics**, **filters**, and **dimensions**. Rather than relying on the LLM to guess business logic, pre-defined KPI calculations stored within the vector knowledge store guide deterministic SQL synthesis.

```mermaid flowchart TD accTitle: Phase 2 KPI-Driven Architecture A["Plain English User Prompt"] --> B["Atomic Parsing:<br/>KPIs, Metrics, Filters, Dimensions"] B --> C["Pre-Defined Domain Vector Store<br/>(Curated Enterprise Views & KPI Formulas)"] C --> D["Deterministic SQL Synthesis Engine"] D --> E["Identical Business Results Across Query Variations"] ```

---

[11:05](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=665s) Key Takeaways and Architectural Best Practices

- **Metadata as a Mandatory Foundation:** Metadata cannot be treated as a "nice-to-have." Organizations must establish mandatory data modeling pipelines and automated quality checks whenever a new data mart or domain is introduced. - **Modular Query Decomposition:** Complex business queries must be broken down into discrete components (KPI formulas, dimension slices, and predicate filters) before prompt assembly to guarantee consistency. - **Enterprise Data Democratization:** Standardizing data access empowers non-technical business users to self-serve insights while freeing data engineering resources from ad-hoc query backlogs.

---

[12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) Audience Q&A: Accuracy, Multi-Turn Context, and Visualization Tools

#### Query Accuracy and Enterprise Scoping An audience member noted that a 90% accuracy rate still implies a 10% error margin, questioning whether that is sufficient for deterministic reporting. Karthik agreed that "90% is good but not good enough," explaining that initial naive implementations hovered around an unacceptable 40–50% accuracy.

To improve reliability, Asurion scoped query generation to federated domain boundaries (such as HR, Finance, or Sales) rather than searching across the entire enterprise warehouse. When an authenticated user logs in, the engine restricts its search scope strictly to the ~20 curated enterprise views relevant to their department.

```mermaid flowchart LR accTitle: Federated Domain Access Scoping User["Authenticated User"] --> Role{"Federated Domain Check"} Role -->|"Finance"| FinTables["Finance Data Mart (~20 Views)"] Role -->|"HR"| HRTables["HR Data Mart (~20 Views)"] Role -->|"Sales"| SalesTables["Sales Data Mart (~20 Views)"] ```

#### Table Selection Across Fragmented Views When asked how the system selects canonical tables among hundreds of user-generated views and aggregated variants, Karthik explained that the text-to-SQL bot is deliberately pointed at curated **Enterprise Views** and pre-aggregated data marts containing formal KPI definitions.

#### Multi-Turn Memory Management Addressing multi-turn conversational follow-ups, Karthik explained that the platform uses DynamoDB to store conversational history. To prevent context dilution—where prolonged conversations degrade LLM attention and retrieval relevance—the context window is strictly capped to the last five conversational turns within an active session.

#### Dynamic Data Visualization When asked whether chart generation is dynamic or pre-scripted, Karthik clarified that two methods are supported: - **LLM-Generated Python Code:** The LLM dynamically produces backend Python code to render charts based on query results. - **Interactive UI with PyGWalker:** The interface embeds PyGWalker, an open-source visual exploration tool that lets users drag and drop fields to dynamically build custom charts.

## Notable quotes > [03:47](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=227s) What are the insights for my claims?

> [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) What is churn rate?

> [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) What is turnaround time?

> [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) 90% is good but not good enough — Karthik Raparthi

> [07:51](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=471s) customer

> [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) SELECT

> [00:04](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=4s) research to reality — Karthik Raparthi

> [11:05](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=665s) nice-to-have

## Mentioned Karthik Raparthi, Asurion, Ai4, OpenSearch, DynamoDB, PyGWalker, Python, SQL

## Video description Karthik Raparthi, Lead ML/Data Engineer, Asurion

Abstracts

In this talk, I will reveal how we transformed natural language processing from promising research into a production-grade text-to-SQL system that cut query development time by 50x. I will share the journey of scaling our NLP solution beyond simple demos to handle thousands of tables and complex business terminologies across a global enterprise. You'll learn our practical approach to solving the core challenges of metadata sparsity, deterministic query generation, and building user trust in AI-generated SQL. Join me to discover proven methods for democratizing data access that will help you avoid common pitfalls and accelerate your own text-to-SQL implementation.

[Source](https://www.youtube.com/watch?v=iPiyDfpyJAA)

中文简报

Asurion借助范围限定的Text-to-SQL引擎将查询时间缩短50倍 企业级Text-to-SQL的可靠性要求将80%的工程精力投入到元数据整理以及将查询限定在精选领域视图中，而非扩展LLM上下文窗口。 Karthik Raparthi详细介绍了Asurion的多阶段演进历程：从失败的零样本和脆弱的少样本提示工程，演变为能够将纯英文业务提示转化为SQL查询、表格数据和可视化图表的企业级检索增强生成（RAG）平台。为克服严峻的企业级挑战，Asurion利用历史查询日志实现了跨数千张表的自动化元数据丰富，并将请求拆解为显式KPI、指标、筛选条件和维度等模块化单元，以确保确定性的查询生成。该生产系统集成了自动化自愈重试循环、OpenSearch混合搜索、严格的只读防护栏，以及基于角色的领域范围限定（将候选表缩小至大约20个精选企业视图）。在最后的问答环节中，Raparthi进一步阐述了利用DynamoDB管理多轮会话记忆，以及通过LLM生成的Python脚本和PyGWalker实现动态可视化的方案。 Asurion 针对业务人员因缺乏 SQL 技能而面临的取数瓶颈，打造了企业级 Text-to-SQL 系统，将分析查询交付速度提升了 50 倍。 (1:09) 提示工程演进表明 Zero-shot 完全失效且 Few-shot 难以扩展，生产系统最终引入指标感知的 RAG 架构与自愈重试机制，在数据库报错时最多允许 LLM 自动纠错重试 3 次。 (2:14) 团队总结指出，在构建端到端 NLP 分析系统中，80% 的工作量都在于数据管理与元数据建模，LLM 执行与前端开发仅占 20%。 (7:51) 为解决大模型的非确定性与元数据稀疏问题，系统按业务域将 1000 多张候选表收敛至约 20 个标准化企业视图，并把复杂查询拆解为原子级 KPI 与过滤维度进行精准生成。 (9:20)

Reading summary

To eliminate multi-day analytical backlogs for business stakeholders, Asurion developed an enterprise text-to-SQL architecture that combines hybrid retrieval, automated query healing, and atomic KPI modeling. Lead ML/Data Engineer Karthik Raparthi outlines how the organization evolved beyond naive prompting to deliver deterministic, self-service data insights at scale. Moving Beyond Zero-Shot and Few-Shot Prompting (2:14) Asurion supports more than 300 million subscribers across roughly 15 countries, but internal decision-makers frequently faced bottlenecks waiting days or weeks for data teams to run routine SQL queries. Initial efforts to translate plain-English requests into database queries using zero-shot prompting failed completely, generating irrelevant and syntactically broken SQL. Adding few-shot in-context examples improved localized benchmarks, but curating hundreds of query variations manually proved unsustainable across enterprise domains. Introducing standard Retrieval-Augmented Generation (RAG) over database schemas improved table and column identification. However, standard RAG still struggled with complex analytical questions because the LLM lacked explicit knowledge of underlying business logic, metric calculations, and filtering rules. These limitations prompted Asurion to design a multi-tiered architecture that integrates deep context retrieval directly with database execution. Intent Routing, Safety Guardrails, and Query Recovery (4:56) Asurion's Phase 1 architecture routes user prompts through an intent classification layer before database generation. The classifier separates documentation queries—such as definitions for churn rate or turnaround time—from analytical data requests. Hard security guardrails block prompt injection attempts and restrict all generated database operations strictly to read-only SELECT statements. For analytical requests, a context-gathering agent performs hybrid semantic and keyword search across OpenSearch to retrieve relevant table definitions, business glossaries, and few-shot samples. If the database engine throws an execution error, an automated self-healing retry loop returns the error message to the LLM to revise the query, allowing up to three correction attempts. Raparthi reports that this system cut query turnaround times roughly 50-fold, shifting response times from days to seconds while exposing query context and relevancy scores directly to end users. Resolving Metadata Sparsity Through Atomic KPI Modeling (7:51) In production, Asurion found that 80% of total engineering effort centered on data curation and metadata modeling, while model execution, search, and user interface design accounted for only 20%. Enterprise warehouses suffered from severe metadata sparsity, with core entities like 'customer' scattered across dozens of tables without consistent documentation. To bridge this gap, Asurion used LLMs to reverse-engineer historical SQL query logs, automatically generating standardized annotations and business synonyms that pruned search candidate spaces from over 1,000 tables to approximately 20 domain-specific views. To overcome LLM non-determinism—where identical questions could produce differing SQL calculations—Asurion shifted to an atomic business query model. The system parses requests into modular components: KPIs, metrics, filters, and dimensions. Rather than letting the LLM invent calculation formulas, deterministic SQL synthesis is guided by pre-defined, standardized KPI calculations stored within the vector knowledge repository. Federated Domain Scoping and Conversational Context Control (12:11) Addressing accuracy requirements, Raparthi emphasized that initial naive implementations achieved only 40% to 50% accuracy, and that a 90% success rate remains insufficient for enterprise financial and operational reporting. To constrain generation errors, Asurion scopes query synthesis to federated departmental boundaries. When authenticated users log in, retrieval is restricted strictly to curated enterprise views and pre-aggregated data marts aligned with their specific domain, such as HR, Finance, or Sales. For conversational analytics, Asurion manages multi-turn interactions using Amazon DynamoDB. To prevent context dilution—where prolonged conversational history degrades LLM attention and retrieval relevance—the active context window is capped at the five most recent conversational turns. Analytical outputs are subsequently rendered as executive summaries, tabular views, and dynamic charts generated either through LLM-synthesized Python code or embedded interactive tools such as PyGWalker. Asurion's deployment demonstrates that operationalizing natural language analytics requires treating metadata curation and KPI modeling as foundational prerequisites rather than afterthoughts. While federated domain scoping and self-healing execution loops significantly improve SQL reliability, sustaining deterministic accuracy across enterprise environments continues to depend on strict data modeling discipline over raw LLM capabilities.

精炼阅读摘要

Asurion 机器学习与数据工程主管 Karthik Raparthi 阐述了企业级自然语言转数据分析（Text-to-SQL）平台的构建路径，指出将非技术业务人员的分析周期从数天缩短至秒级的关键，在于通过领域边界隔离与指标模块化分解来解决元数据稀疏和模型非确定性问题。 业务访问瓶颈与提示工程的迭代演进 (1:09) Asurion 作为一家在约 15 个国家拥有超过 3 亿用户的技术支持服务公司，其业务人员在制定关键运营决策时高度依赖数据分析，但由于缺乏 SQL 编写能力，数据请求往往需要依赖数据工程师或分析师，导致交付周期长达数小时、数天甚至数周。为了打破这一瓶颈，团队尝试利用大语言模型（LLM）将自然语言转换为 SQL 查询，但在技术路线上经历了一系列迭代。 起初采用的 Zero-Shot（零样本提示）由于缺少数据库上下文，生成了完全不相关或错误的 SQL，以彻底失败告终；随后尝试的 Few-Shot（少样本提示）虽在窄场景有效，但在面临复杂多样的业务领域时，人工维护数以百计的 SQL 样例并塞入上下文窗口的做法无法持续扩展。随后引入的表级和列级 RAG（检索增强生成）虽然提升了上下文关联度，但由于缺乏对核心业务指标（KPI）与特定过滤逻辑的感知，模型在处理复杂分析查询时依然表现受限。 Phase 1 架构：意图路由、混合检索与自愈执行 (4:56) 为了实现生产级可用性，Asurion 构建了 Phase 1 架构。系统首先通过意图分类器进行双路由分发：纯业务名词或概念解释（如用户流失率、周转时间定义）被导流至知识文档代理，涉及数据提取与计算的请求则导流至上下文收集代理。在安全控制上，系统设有硬性约束，不仅防范提示注入攻击，还严格将数据库操作权限限制为只读的 SELECT 语句。上下文收集环节依托 OpenSearch，对表描述、字段信息、同义词词典及少量样例执行语义与关键字混合检索，进而驱动 SQL 生成代理。 在前台交互与执行保障方面，系统界面展示思维链（Chain-of-Thought）执行逻辑、引用的底层数据表以及向量检索相关性得分。若数据库执行引擎报错，系统会自动触发自愈重试循环，将错误信息回传给 LLM 进行实时诊断与修正，重试上限设定为 3 次。此外，用户可通过点赞、点踩和文本反馈持续纠正意图识别或输出偏差。该架构落地后，使原本耗时数天或数周的数据交付流程缩短至即时响应，查询周转效率提升约 50 倍。 Phase 2 演化：攻坚元数据稀疏性与确定性指标 (7:51) 在 Phase 1 投入运行后，团队发现自然语言转分析的核心瓶颈并非模型推理，项目约 80% 的工作量集中于数据清洗与元数据建模，仅有 20% 涉及 LLM 执行、语义搜索与用户界面。企业级环境中普遍存在元数据缺失或不一致的问题，例如“客户”一词可能散落在数十张定义迥异的表结构中。为此，Asurion 利用 LLM 反向解析历史 SQL 查询日志，自动生成标准化的表描述、字段注释和领域同义词，从而将检索候选空间从 1,000 多张表大幅剪枝至约 20 张核心领域表。 针对大语言模型输出的天然非确定性与业务报表对数据绝对精确的要求之间的矛盾，Asurion 拒绝让 LLM 自行猜测商业计算逻辑，转而推行指标原子化解析机制。系统将用户的自然语言请求拆解为 KPI、具体指标、过滤条件和分析维度等离散业务单元，并结合向量库中预定义的标准 KPI 计算公式，引导引擎进行确定性 SQL 合成，确保相同口径的问题在不同问法下均能返回一致的底层数据结果。 领域边界隔离、会话上下文管理与动态可视化 (12:11) 面对企业报表对准确率的严苛要求，Raparthi 指出，最初简单实现的系统准确率仅在 40% 至 50% 左右，而即便是 90% 的准确率也意味着 10% 的商业决策风险。为提高可靠性，Asurion 采取了联邦领域边界隔离策略：系统不面向整个企业数据仓库检索，而是根据登录用户的身份验证权限（如 HR、财务、销售），将其限定在由数据团队维护的约 20 张权威“企业视图”（Enterprise Views）和预聚合数据集市中，从源头上杜绝了模型在碎片化用户自建视图中迷失。 在交互延续性与展现形式上，系统利用 DynamoDB 存储多轮会话历史，并将上下文窗口严格截断为最近的 5 轮对话，以防止长会话稀释模型注意力和干扰检索相关性。在最终呈现上，除了生成执行摘要和结构化数据表格外，系统支持通过 LLM 动态编写后端 Python 代码渲染图表，或直接嵌入开源可视化工具 PyGWalker，让业务人员可以通过拖拽字段实现自助式多维图表探索。 Asurion 的实践表明，在大模型落地数据分析的过程中，决定成败的关键在于底层的企业级元数据治理与确定性业务建模，而非单纯依赖提示词或模型参数。通过限定联邦领域访问范围、原子化解构 KPI 以及利用历史日志补齐元数据，平台有效抑制了模型幻觉与非确定性风险；但在跨部门交叉分析以及动态演进的数据环境中，如何低成本维持高质量的权威元数据资产仍是企业需要持续解决的长期课题。

Detailed notes — zh

## Background 该视频由行业会议频道 Ai4 于 2025 年 8 月 26 日在 YouTube 平台发布。内容由 Asurion 的主任机器学习与数据工程师 Karthik Raparthi 主讲，根据简介，演讲分享了 Asurion 如何将前沿的 NLP 研究转化为生产级 Text-to-SQL 系统，将查询开发效率提升 50 倍，并攻克跨数千张表的元数据稀疏性、确定性查询生成以及建立用户信任等企业级核心挑战。 ## TL;DR Asurion 团队为了解决非技术业务人员依赖数据工程师排期导致决策延误的痛点，打造了面向企业级数据分析的智能 Text-to-SQL 助手。技术路径经历了从 Zero-shot、Few-shot 到表级 RAG 的演进，并在 Phase 1 实现了包含意图识别（区分数据查询与定义问答）、OpenSearch 混合检索、思维链执行与自我纠错重试机制的闭环架构。针对大模型非确定性及企业内部严重的“元数据稀疏”挑战（占工程难度的 80%），团队在 Phase 2 采用反向解析存量 SQL 补全元数据，并引入 KPI 驱动的模块化拆解与领域隔离策略，将端到端准确率从初期的 40%-50% 跃升至 90%，真正实现了分钟级交付的高确定性数据民主化。 ## Atmosphere & style 整个分享呈现出极其务实、硬核且坦诚的技术布道风格，没有宽泛浮夸的概念包装，而是直面工业界落地的真实泥潭。主讲人以清晰严密的工程逻辑拆解架构演进，在谈及早期版本仅有 40% 准确率以及现场面对观众对 90% 准确率的尖锐质询时，表现得极为坦率与从容。观众能清晰感受到从最初踩坑、架构反思到最终攻克复杂指标确定性的完整工程心路，兼具实战落地的方法论厚度与直面现实挑战的技术清醒感。 ## Key takeaways - [01:09](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=69s) Asurion 面临业务人员因不具备 SQL 技能而严重依赖数据工程师的问题，查询需求常需耗费数天甚至数周，亟需通过自然语言交互实现数据民主化。 - [02:14](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=134s) 纯 Prompting 方案存在明显局限：Zero-shot 无法生成有效洞察，Few-shot 遭遇样本扩展瓶颈，而初版表级 RAG 则缺失了关键业务指标口径与过滤规则。 - [03:47](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=227s) 系统在交互层集成了自我纠错重试机制（上限 3 次）与用户反馈闭环，在 SQL 报错时自动反思修复，并在界面透明展示 SQL 与相关度得分。 - [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) Phase 1 架构引入了意图识别以区分 SQL 查询与文档定义问答，并设置只读等安全护栏，使端到端查询交付效率实现了超 50 倍（50x）的飞跃。 - [08:46](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=526s) Text-to-SQL 落地呈现 80/20 法则：LLM 编排与开发仅占 20% 工作量，而解决企业内部 80% 的元数据稀疏、缺失与术语命名混乱才是真正的核心工程挑战。 - [08:46](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=526s) 团队利用大模型逆向解析存量生产 SQL 脚本，批量补全表与字段描述并沉淀同义词库，将上千张表的庞大搜索空间精准收窄至约 20 张领域核心表。 - [10:22](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=622s) 针对 LLM 的非确定性问题，Phase 2 构建了 KPI 驱动层，预先固化权威 KPI 计算逻辑与过滤标准，确保生成的 SQL 虽写法可略有差异但计算数值高度一致。 - [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) 通过引入 KPI 驱动与领域隔离（Domain-specific）策略，查询准确率从一期早期的 40%-50% 大幅跃升至 90% 的工业可用水平。 - [13:23](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=803s) 针对个人临时视图混乱的问题，系统强制将查询限制在官方认证的企业级统一视图（Enterprise Views）之上，确保数据源质量。 - [13:23](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=803s) 在多轮对话管理中，系统采用 DynamoDB 存储历史并将记忆严格限制在最近 5 轮以内，有效防止上下文膨胀导致信息稀释。 - [13:23](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=803s) 数据可视化支持双轨制：既可通过 LLM 生成后台 Python 脚本，也可集成开源工具 PyGWalker 为业务用户提供类似 Tableau 的前端拖拽探索能力。 ## Detailed walkthrough ### [00:04](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=4s) 演讲开场与 Asurion 业务背景 来自 Asurion 的主任机器学习与数据工程师（Lead ML/Data Engineer）Karthik Raparthi 开启了本次分享。他开门见山地指出，今天的核心议题是如何将自然语言处理（NLP）从前沿的研究成果落地为真实的生产力，在企业级规模下实现从自然语言到数据分析（NLP to Analytics at Scale）的稳健跨越。在深入技术细节前，Karthik 简要介绍了 Asurion 的企业概况与业务体量。Asurion 是一家全球领先的科技服务与保障公司（Tech Care Company），在全球 15 个国家拥有超过 3 亿付费订阅用户，专注于为用户解决手机维修、大家电保障以及智能互联家居设备相关的全方位技术难题。为了支撑庞大的服务网络，公司配备了约 9,000 名一线专业技术专家，并提供 7x24 小时全天候在线支持。 面对如此庞大的全球化业务，背后的数据体系与分析需求极其错综复杂。Karthik 明确了本次演讲的整体路线图：首先回顾从最初探索到成熟落地的演进历程，随后深入剖析一期和二期的分阶段架构演化，并重点分享在攻克元数据稀疏性、确定性查询生成以及提升企业级数据查询效率中沉淀下来的实战经验与踩坑心得。 ### [01:09](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=69s) 痛点拆解：业务人员与数据之间的高墙 在大型企业内部，非技术背景的业务人员（Business Users）日常需要高频面对各种与 SQL 和数据指标密切相关的业务提问。然而，现实情况中存在着难以逾越的“技术壁垒”：业务人员自身拥有深厚的业务领域专识（Domain Expertise），清楚知道自己需要什么商业洞察，但他们不会写复杂的 SQL 语句，不得不将需求提交给数据工程师或数据分析师排期处理。这种依赖导致了严重的流程阻塞，业务人员往往需要苦等数天甚至数周才能拿到查询结果，极大地延误了商业决策的最佳窗口期。 为了彻底打破这一瓶颈，Asurion 团队确立的目标是构建一个能够理解普通自然语言（Plain English）的智能助理或机器人，让业务决策者能在几分钟或一小时内自主获取所需答案。用户只需用自然语言描述分析诉求并附带领域背景，系统即可自动转换为底层 SQL，不仅迅速跑出数据，还能直接呈现关键洞察总结与配套的可视化图表，实现真正意义上的数据民主化。 ### [02:14](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=134s) 技术路线探索：从 Zero-shot、Few-shot 到初版 RAG 为了实现 Text-to-SQL，团队在技术路径上经历了层层试错与迭代。最初尝试的是零样本提示（Zero-shot Prompting），在不给大模型提供任何数据上下文的情况下，仅输入一句朴素的 Prompt 要求它去写 SQL，结果可想而知，“它显然彻底失败了，虽然生成了 SQL 语法，但给出的根本不是相关数据或有效洞察”。随后团队转向少样本提示（Few-shot Prompting），在 Prompt 中补充具体业务场景示例（例如理赔分析相关的问题与示范 SQL），模型准确率有所起色，但很快撞上了可扩展性瓶颈（Scaling Issues）：为了覆盖多变的业务场景，必须人工编写并喂入海量的 SQL 示例，这在企业级应用中完全不可维系。 基于此，团队转向检索增强生成（RAG）方案，将数据库中的表描述、字段注释以及核心业务术语录入知识库进行召回。RAG 结合少样本示例让相关性与准确度大幅跃升，但很快又暴露了新的深层问题：模型常常无法准确理解特定业务“指标（Metric）”的真实统计口径，也不清楚该套用哪些强制过滤条件（Filters），因为它所获取的仅仅是表结构的片段碎片信息。这推动了后续与 KPI 语义深度绑定的架构重塑。 ```mermaid flowchart LR accTitle: Text-to-SQL 技术演进路径 subgraph S1["阶段一：原生 Prompting"] A["零样本 (Zero-shot)"] -->|"缺乏上下文失败"| B["少样本 (Few-shot)"] end subgraph S2["阶段二：知识增强检索"] B -->|"样本扩展瓶颈"| C["表/字段级 RAG"] C -->|"缺乏指标与过滤规则"| D["KPI 驱动 RAG + 反馈闭环"] end ``` ### [03:47](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=227s) 系统交互界面与多重重试机制 Karthik 展示了面向终端用户的系统 UI 设计。当业务用户提出诸如“关于理赔（Claims）有哪些关键洞察？”的问题时，系统会迅速返回多层次的响应：顶部是基于查询结果自动生成的文字总结洞察，下方直接展示对应的数据表格，并支持一键生成交互式可视化图表。 在答案下方，系统清晰罗列了支撑这套结论的上下文细节，包括本次由大模型生成的底层 SQL 语句。尤为关键的是其内置的自我纠错重试机制（Self-correction Retry Mechanism）：如果首次生成的 SQL 执行报错，系统会自动捕获错误信息并触发重试模式，结合执行报错信息进行自我反思与修复，直到成功跑通。目前系统将重试上限严格限制为 3 次，并在界面透明展示修复前后的 SQL 及具体报错记录。此外，界面底部始终保留了连续反馈闭环（Continuous Feedback Loop），业务用户与分析师可直接对查询结果给出好评或踩评，系统同步展示了本次 RAG 检索召回的表列表以及各表的相关度得分（Relevancy Score）。 ### [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) Phase 1 架构详解：意图识别、混合检索与执行链路 一期架构完整贯穿了从自然语言输入到可视化输出的全链路。当用户输入普通自然语言后，系统第一步是在最左侧进行意图识别（Intent Classification）。该系统实际上具备双重路由能力：不仅能处理基于 SQL 的数据查询，还能处理文档知识问答（Document-based QA）。例如，当新入职员工询问“我们团队定义的流失率（Churn Rate）是怎么计算的？”或“周转时间（Turnaround Time）的定义是什么？”等纯定义类问题时，系统会精准识别意图并路由至文档检索库，而非强行生成 SQL。与此同时，安全护栏（Guardrails）机制在此刻介入，严格限制用户权限（例如系统仅允许执行只读的 `SELECT` 语句），严防 Prompt 注入攻击或非法修改数据的企图。 确认意图并通过防护后，请求进入中台上下文组装层。该层维护着海量表结构、字段描述、业务术语以及少样本示例库，借助 OpenSearch 进行向量与文本的混合搜索（Hybrid Search）。随后由上下文代理（Context Agent）生成 SQL，并传递至执行洞察模块。在此处，系统通过思维链（Chain of Thought）完成最繁重的工作：将生成的 SQL 提交到底层数据库引擎执行，若遇语法或逻辑错误则进入重试循环，成功后直接交付给上层进行图表可视化。而在底层，统一知识底座（Unified Knowledge Foundation）作为向量存储库统一沉淀文档、KPI 规则及元数据，并在每个阶段嵌入持续学习机制，若用户反馈意图分类有误，语义检索库将在下一次运行时自动纠偏。 ```mermaid flowchart TD accTitle: Phase 1 系统端到端架构流程 subgraph Ingest["1. 输入与治理"] User["用户自然语言查询"] --> Guard["护栏拦截与意图分类"] end subgraph Retrieval["2. 上下文与知识检索"] Guard -->|"定义/知识"| DocStore["文档知识库"] Guard -->|"数据统计"| HybridSearch["OpenSearch 混合检索"] UKF[("统一知识底座 Vector Store<br/>表/字段/KPI/文档")] -.-> HybridSearch end subgraph Execution["3. 生成与执行闭环"] HybridSearch --> ContextAgent["上下文代理 (生成 SQL)"] ContextAgent --> Engine["数据库引擎执行"] Engine -->|"报错 (限 3 次)"| Retry["自我纠错重试"] Retry --> ContextAgent Engine -->|"执行成功"| Output["洞察总结与图表可视化"] end Output -.-> Feedback["用户持续反馈闭环"] Feedback -.-> UKF ``` - **效能提升指标**：系统上线后，业务人员获取文档定义及 SQL 数据查询结果的等待周期从过去的数天、数周骤降至分钟级，端到端查询交付效率实现了超 50 倍（50x）的飞跃。 ### [07:51](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=471s) 一期痛点反思：拆解复杂 SQL 的原子构成 尽管一期架构将交付效率提升了 50 倍，但在复杂查询场景中依然暴露出致命缺陷：由于缺乏对指标内部逻辑和过滤条件的精细把控，大模型在生成复合查询时经常遗漏关键约束。面对复杂的分析需求，资深数据工程师或人类分析师在写 SQL 时，本质上是将问题拆解为多个核心业务单元：确定核心 KPI、定位统计指标（Metric）、识别必加的过滤条件（Filter）以及划分聚合维度（Dimension）。 因此，团队深刻意识到不能把整条复杂 SQL 的生成全盘寄托于大模型的单次推导，而必须效仿人类工程师的思维路径，在底层构建结构化的拆解与组装逻辑。第二阶段的核心攻坚目标正是围绕如何实现高度一致、确定性的查询结果（Deterministic Query Results）展开。 ### [08:46](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=526s) 核心技术挑战：80/20 法则与元数据稀疏性 回顾一期向二期的演进历程，Karthik 总结道，实际落地中的工程难度呈现出惊人的 80/20 分布。很多人误以为最难的工程在于大模型应用开发本身，但在实际场景中，语义检索、上下文拼装、调用大模型推理以及前端渲染展示这些执行层的工作仅仅占了 20% 的工作量，且属于最容易实现的部分；真正的硬骨头是剩下的 80%——企业内部普遍存在的“元数据稀疏与缺失”（Metadata Sparsity & Incompleteness）。 在真实的大型企业数据仓库中，数据分散在各处，缺乏统一的业务定义，术语命名极其混乱，“例如同一个‘客户’实体在不同业务线甚至同一张表的不同列里有完全不同的称谓”，元数据普遍严重缺损。为了攻克这一瓶颈，团队反向利用大模型去解析现有的存量生产 SQL 脚本，批量逆向推导并补全表级描述、字段含义，并梳理出表同义词库（Table Synonyms）和字段同义词库（Column Synonyms）。这种强大的元数据沉淀，使系统在处理查询时能够精准收敛上下文范围，成功将原本上千张待选物理表的庞大搜索空间，迅速收窄至特定领域内的 20 张左右核心表，检索召回精度实现质的飞跃。 ### [10:22](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=622s) Phase 2 演进：KPI 驱动的确定性查询生成 除了元数据稀疏，另一个天然挑战是大模型固有的“非确定性”（Non-deterministic Nature）。对于严谨的企业财务与商业分析而言，同一个问题如果每次生成的底层口径都不一样，将直接摧毁用户对系统的信任。为此，团队在二期架构的中枢环节新增了关键的“KPI 与过滤规则解析层”（KPI and Filter-based Layer）。 团队将核心业务 KPI 的权威 SQL 逻辑与过滤标准直接预先固化在向量数据库中。当业务用户发起涉及具体 KPI 或指标的提问时，系统不再放任模型自由发挥拼凑 SQL，而是优先命中对应的 KPI 规则定义，明确锁定当前业务领域内必须调用的底表与过滤约束。Karthik 强调：“大模型写出的 SQL 语法在写法风格上可以略有不同，但计算出来的业务数值结果必须始终高度一致。” - **元数据战略升级**：在企业数据治理观念上，彻底摒弃以往将元数据视为“可有可无的附加品（Nice to Have）”的态度，将其提升为数据工程的“核心基石（Foundational）”。 - **标准化数据流水线**：新搭建的数据集市（Data Mart）或新业务域在接入前，必须在工程流水线中强制定义元数据标准与数据质量校验规则。 - **模块化拼接方法论**：坚持拆解业务单元，将 KPI 指标、过滤条件及维度组合成确定性逻辑块，再由 Agent 组装生成确定性 SQL。 ### [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) 现场问答一：从 40% 到 90% 的准确率突破与领域隔离 进入 Q&A 环节，现场观众针对查询准确率提出了尖锐质询：“你提到基础问题的准确率达到了约 90%，但对于企业要求确定性结果的场景而言，通常期望达到 99% 甚至 100%，90% 虽然不错，但还远远不够。” Karthik 对此坦诚回应，鉴于当前底层 LLM 的概率生成本质，达到绝对的 100% 极其困难，系统正随着用户反馈数据的积累持续进化。但在演进初期，一期系统的实际准确率其实只有惨淡的 40% 到 50%，当时的结果非常糟糕。二期通过引入 KPI 驱动与原子化拆解后，准确率才大幅提升至 90% 的工业可用水平。此外，系统并没有试图在一个无所不包的全局企业级范畴下一次性解决所有问题，而是采取了领域细分（Domain-specific）隔离策略。用户登录时自带联邦访问控制权限（Federated Access），系统能识别其属于财务、HR 还是销售团队；一旦发起查询，系统仅在经过严格权限校验与元数据对齐的 20 张领域核心表内做检索匹配，而坚决避免在全公司的成百上千张杂乱大表中大海捞针。 ### [13:23](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=803s) 现场问答二：多源个人视图、多轮对话与动态可视化 针对现场观众提出的后续技术细节，Karthik 逐一进行了深度解答： - **如何筛选最佳底表与个人视图（Views）**：针对业务人员和市场部门自行创建的碎片化视图及冗余表问题，Karthik 说明团队专门构建了“企业级统一视图（Enterprise Views）”系统，将各数据集市中的半聚合与聚合数据统一汇聚。Chatbot 仅在此类经过官方认证、定义明确的企业级视图之上启用，从而绕过混乱的个人临时表。 - **多轮对话（Multi-turn Conversations）与上下文防稀释**：关于多轮追问中的上下文管理，系统结合 LLM 与底层记忆组件（采用 DynamoDB 存储对话历史）。为了避免上下文膨胀导致信息被过度稀释（Diluting the Information），系统将多轮追问的记忆严格限制在当前会话的最近 5 轮（Last 5 conversations）之内。 - **动态可视化呈现（Dynamic Visualization）**：面对图表生成的实现原理，Karthik 透露系统支持双轨方案：一种是由大模型在后台实时生成驱动可视化的 Python 脚本；另一种则是集成了开源的交互式工具 PyGWalker，生成类似 Tableau 的可视化界面，允许业务用户在前端直接通过拖拽字段（Drag and Drop）自由探索与调整图表。 ## Notable quotes > [02:14](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=134s) 它显然彻底失败了，虽然生成了 SQL 语法，但给出的根本不是相关数据或有效洞察 — Karthik Raparthi > [10:22](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=622s) 大模型写出的 SQL 语法在写法风格上可以略有不同，但计算出来的业务数值结果必须始终高度一致。 — Karthik Raparthi > [10:22](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=622s) 可有可无的附加品（Nice to Have） — Karthik Raparthi > [10:22](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=622s) 核心基石（Foundational） — Karthik Raparthi > [08:46](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=526s) 例如同一个‘客户’实体在不同业务线甚至同一张表的不同列里有完全不同的称谓 — Karthik Raparthi > [12:11](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=731s) 你提到基础问题的准确率达到了约 90%，但对于企业要求确定性结果的场景而言，通常期望达到 99% 甚至 100%，90% 虽然不错，但还远远不够。 > [03:47](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=227s) 关于理赔（Claims）有哪些关键洞察？ > [04:56](https://www.youtube.com/watch?v=iPiyDfpyJAA&t=296s) 我们团队定义的流失率（Churn Rate）是怎么计算的？ ## Mentioned Asurion, Karthik Raparthi, OpenSearch, DynamoDB, PyGWalker, Tableau, Text-to-SQL, RAG, Chain of Thought, Data Mart ## Video description Karthik Raparthi, Lead ML/Data Engineer, Asurion Abstracts In this talk, I will reveal how we transformed natural language processing from promising research into a production-grade text-to-SQL system that cut query development time by 50x. I will share the journey of scaling our NLP solution beyond simple demos to handle thousands of tables and complex business terminologies across a global enterprise. You'll learn our practical approach to solving the core challenges of metadata sparsity, deterministic query generation, and building user trust in AI-generated SQL. Join me to discover proven methods for democratizing data access that will help you avoid common pitfalls and accelerate your own text-to-SQL implementation. [Source](https://www.youtube.com/watch?v=iPiyDfpyJAA)

Source quotes

[2:14] Karthik Raparthi: 它显然彻底失败了，虽然生成了 SQL 语法，但给出的根本不是相关数据或有效洞察 [10:22] Karthik Raparthi: 大模型写出的 SQL 语法在写法风格上可以略有不同，但计算出来的业务数值结果必须始终高度一致。 [10:22] Karthik Raparthi: 可有可无的附加品（Nice to Have） [10:22] Karthik Raparthi: 核心基石（Foundational） [8:46] Karthik Raparthi: 例如同一个‘客户’实体在不同业务线甚至同一张表的不同列里有完全不同的称谓 [12:11] 你提到基础问题的准确率达到了约 90%，但对于企业要求确定性结果的场景而言，通常期望达到 99% 甚至 100%，90% 虽然不错，但还远远不够。 [3:47] 关于理赔（Claims）有哪些关键洞察？ [4:56] 我们团队定义的流失率（Churn Rate）是怎么计算的？

Source quotes

[3:47] What are the insights for my claims? [4:56] What is churn rate? [4:56] What is turnaround time? [12:11] Karthik Raparthi: 90% is good but not good enough [7:51] customer [4:56] SELECT [0:04] Karthik Raparthi: research to reality [11:05] nice-to-have

Original transcript

[0:04] My name is Karthik Raarti. Uh I'm with [0:06] Ashuran. Uh today I'm going to talk [0:08] about Ashur and how we are uh solved [0:11] research to reality on the NLP2 [0:14] analytics at scale. [0:18] So before getting started um I want to [0:21] just talk about Auran what we do. We are [0:22] a techare company. Uh we have 300 [0:25] million uh subscribers over the world [0:27] with around 15 countries. Uh we take [0:31] care of the uh any anything related to [0:33] the mobile uh repairs uh major [0:36] appliances and the connected home [0:42] and we have around 9 9,000 experts. We [0:45] have a live uh 24x7 chat and so on. So [0:51] let's get into the agenda. So I'm going [0:53] to talk about like how we solved uh NLP [0:56] to analytics uh at scale. I'm going to [0:59] start with how we started with an [1:00] evolution of uh uh the process and then [1:04] we'll go about how what's a phasebyphase [1:06] architecture with what we learned so [1:08] far. [1:09] So this is uh what we are trying to [1:11] solve here. So lot of business users [1:14] tries to solve with uh uh try to ask lot [1:17] of uh SQL and data related questions [1:20] quite often to the engineers or analyst. [1:22] So what we're trying to see is like how [1:23] can they get the data faster. So there's [1:26] a technical barrier uh right now for the [1:29] business users because they know the [1:30] domain expertise but the problem there [1:32] is like they have to wait for a days [1:35] uh weeks for the data to come back and [1:37] get the answers and so that they that [1:39] there'll be a delay for the decisions to [1:41] make it. So the solution was uh asking [1:45] in a plain English language to see if [1:48] they can answer those uh those questions [1:50] like sooner like in minutes or in an [1:53] hour. So we are introducing a bot where [1:56] you can solve those things where you you [1:59] will have a plain English language [2:02] submitting an uh with with a domain [2:05] expertise and it comes back with a SQL [2:08] saying that these are your insights and [2:10] this is your visualization for the end [2:12] answer. [2:14] So how we started this? So we started [2:17] with a few short prompting uh for zero [2:19] short prompting first. We didn't give [2:20] any context to the model. uh we just [2:23] provided uh with this plain prompt [2:25] saying that okay go and solve this [2:26] question then what we've seen is like [2:29] obviously it failed because it created a [2:32] SQL but it didn't even answer any uh any [2:34] any relevant uh SQL generation or an [2:37] insights then we went with a few short [2:40] prompting with some examples saying that [2:42] okay you have this context uh how many [2:45] customer what are the insights for my [2:46] claims let's say uh then it have we have [2:50] to give lot of examples with within in [2:52] that domain uh to answer that. So the [2:55] few short prompting we faced was the [2:57] scaling issues. Uh it couldn't it [2:59] couldn't scale. We have to provide so [3:01] much so much of SQL examples. Then it [3:04] didn't it didn't worked out for us. Then [3:06] we started with the rag approach where [3:09] uh where we uh went back with a [3:12] description of the tables columns and [3:15] then some business terminologologist [3:16] with that. uh that helped out so much [3:19] because it was it was giving pretty [3:22] relevant information with the rag with a [3:24] few short prompting. Uh but the issues [3:27] we are facing was it's not giving again [3:29] there was an issue where it doesn't know [3:31] what is a metric or what are the filters [3:33] it has to apply because it only has a [3:36] little bit of table information to that. [3:38] So I'll touch base with the last topic [3:40] like how we solved it rag with the KPI [3:43] driven with the feedback loop uh in the [3:46] later slides. [3:47] So this is a sample UI how it looks like [3:50] for us right now. Uh once you ask like [3:52] example what are the insights for the [3:54] claims. So it comes back with an [3:56] insights uh with summarization the [3:59] supporting insights was this data. So it [4:03] come back it went ahead submitted a SQL [4:05] come back with an insights with a table [4:07] results and then visualization you can [4:09] do [4:10] and these are the supporting context it [4:13] used like a SQL generated uh and then it [4:16] has a mechanism I'll go in depth of the [4:18] architecture uh where it can retry so it [4:22] can learn from the mistakes so if let's [4:24] say first time there was an issue with [4:26] the SQL it goes retry mode and then get [4:29] comes back until it got fixed so we have [4:31] an uh three three retry uh we limited to [4:34] that for now. Uh but it shows what it [4:36] has been fixed and what was the error [4:38] and if you see in the back like there is [4:40] a constant feedback loop in the bottom. [4:42] So we can't capture those from business [4:44] users or the analyst constantly [4:49] and this is the context like what tables [4:50] it used and what is the reency score [4:52] from the each from the rag. [4:56] So this is the architecture the phase [4:58] one architecture for that. So I'll start [5:00] from the left side. uh query insights uh [5:03] once a user submits the question in the [5:06] plain English the what we try to do is [5:08] we try to get the uh intent of the [5:11] question so this system does two ways [5:13] actually which I didn't uh specified in [5:15] the first slide where it can answer a [5:18] document based or a SQL based so let's [5:21] say if somebody joined new uh to the [5:23] team and if wants to know what is a [5:25] churn rate how do we calculate in that [5:27] domain or what is a turnaround time [5:29] let's say so those definitions questions [5:31] uh it can answer like it can get the [5:33] document related information uh and then [5:36] it can know the data related stuff. So [5:37] it finds to tries to find the intent it [5:40] puts the guardrails like nobody can do [5:43] like uh the user has a restrictions it [5:45] it can only do the select. So if they [5:47] ask the problem injection questions or [5:49] anything the guardrails will kick in and [5:51] then based on that it goes to the data [5:53] or knowledge from there if you see the [5:55] middle part that's where the whole [5:57] context will gather. So we have bunch of [6:00] uh table descriptions uh uh with a few [6:03] short prompting examples and then the [6:05] column descriptions and the business [6:06] terminology. From there we do the hybrid [6:09] uh search uh we use the open we are [6:11] using the open search uh and then from [6:14] there the context agent will derive [6:17] based on the intent if it is a SQL it [6:19] generates a SQL sends back to the third [6:22] one which is an actual insights. It [6:24] gives you that chain of thought where [6:26] that's where the uh heavy lifting is [6:28] happening where the SQL was submitted to [6:30] the database whatever the engine you [6:32] guys were using and then it tries to [6:34] retries like if it's successful for the [6:36] first time it just moves forward with [6:38] the insights otherwise it goes into the [6:40] loop and eventually the user can do the [6:42] visualization as top of it and the [6:46] bottom that's where the mo the whole [6:48] scaling part like the metadata driven [6:50] part comes into play the unified [6:51] knowledge foundation so that holds the [6:54] whole vector store with all the [6:56] information like the documents, KPIs, uh [6:58] the tables, everything and then the [7:00] continuous feedback loop goes into into [7:03] place and each stage if you see there [7:06] there are continuous learning and the [7:08] feedback. So user can give the feedback. [7:10] So let's say if they see the intent was [7:13] wrong, they can give the feedback and [7:14] next time when it's as a similar type of [7:16] question, the semantic kicks in and then [7:19] uh gets back the proper results uh in [7:21] the successor RAM. [7:24] So what metrics we saw we have seen [7:27] significant metric uh improve actually [7:29] uh we have seen almost like uh 50x uh [7:32] maybe more than that uh especially with [7:35] the documentation or in the SQL space. [7:38] So rather than the business users rather [7:39] than waiting on like days, weeks or [7:43] hours for some of the questions they [7:46] they are getting the results way faster [7:48] than what they expected. [7:51] So what in the third the in the one of [7:54] the slide the evolution I've said about [7:56] like uh rag versus uh the rag has a few [8:00] short prompting. So we saw some issues [8:02] over the time the fa in the phase one uh [8:05] where it doesn't know it doesn't [8:07] understand the matrix or a filter [8:08] definitions actually. So what we went [8:11] was we took a SQL which is pretty [8:13] complex and divided into multiple units [8:16] like example an uh a KPI a metric a [8:19] filter and a dimension and as an [8:21] engineer or an uh an human we try to uh [8:24] dissect that question into multiple [8:26] pieces and then comes back with an uh [8:29] the SQL whatever you're seeing in the [8:30] bottom. So we're trying to solve that in [8:33] the second phase to see how we can uh [8:36] how we can solve how we can succeed like [8:39] deterministic query results uh [8:41] consistently. [8:46] So what lessons we learned from the [8:48] phase one to the phase two. So I'll go [8:49] in the phase two in the next slide. The [8:52] lessons what we learned was one is the [8:54] metadata sparity and the completeness [8:57] because we have seen cases where uh the [9:01] data was not is everywhere like customer [9:03] can be called in in different ways [9:05] there's no def business definitions the [9:07] terminology was wrong uh missing the [9:10] metadata was not there but if you on the [9:12] right side the execution part the 20% [9:15] was easy like gathering the semantic [9:17] search uh doing that and then the [9:18] context assembly lm reference and then [9:20] the uh presentation that's the easiest [9:23] part but the hardest part what we faced [9:25] was the 80% which is missing the [9:27] metadata so that's where we try to solve [9:30] that like but going back with the [9:32] current SQL using the LLMs as a those [9:36] SQL to get back with the descriptions [9:38] the table uh table level descriptions [9:40] column level descriptions we try to [9:42] define those uh business terminologies [9:44] for the table synonyms column synonyms [9:47] so those really help for the context and [9:49] we'll drill down the accuracy uh from [9:52] thousand tables to around 20 tables and [9:55] the next piece was the deterministic [9:56] query generation where uh obviously the [10:00] LLMs are nondeterminist nature so how do [10:02] we solve that so that's where we try to [10:04] solve by the KPI driven so we say that [10:07] okay if the user business user ask about [10:09] a KPI a metric we already have the [10:11] definition like we have the def [10:13] definition of that KPI sitting in the [10:15] vector store and we know what to hit in [10:18] that specific domain [10:22] So this is the second phase of the [10:23] architecture uh where if you see that [10:26] red box in the middle that's where we [10:27] try to introduce uh that's extra layer [10:31] uh KPI and then filter based so now we [10:36] have more comprehensive [10:39] uh analysis on that where when you are [10:41] when you submit any KPI related or [10:43] metric related stuff it knows now what [10:46] to pick and then gives the determined [10:48] results every time and we want to make [10:50] sure when you're trying to solve this [10:52] like we want to have maybe LLM can [10:54] return in different ways the same SQL [10:56] but the results has to be constantly [10:58] same like we don't want to see the [11:00] different results for the SQL [11:05] so what are the key takeaways we we have [11:08] seen so one is the metadata has to be [11:11] foundational so previously uh over the [11:14] years what we used to have is metadata [11:16] is nice to have so we said like it has [11:18] to be foundational. So whenever there's [11:20] a new data mod or a new domain comes [11:22] into play. So we have a process pipeline [11:26] where you have um the metadata defined [11:29] and the quality defined on top of it. Uh [11:31] quality checks are there and then uh to [11:35] uh to get the data faster. That's what [11:37] we want to de democratize the uh access [11:39] to the business users engineers analyst [11:42] and um [11:45] and then uh and define the [11:50] sorry [11:52] so then uh the core methodology was [11:55] breaking the things into multiple pieces [11:57] where you have c KPIdriven metrics [11:59] business small business units and then [12:02] uh take those uh small units and then [12:05] form a SQL and then get the results in a [12:08] deterministic way. [12:11] With that you have any questions please. [12:14] So the basic questions we were able to [12:18] find out like almost 90% we were able to [12:21] see good results which are [12:23] >> 10% right like like that's right when [12:25] you're talking about deterministic [12:26] you're looking at a 99 or 100% right [12:29] like that's right [12:30] >> 90% is good but not good enough. [12:32] >> Yes it's not good enough because that's [12:33] the nature of the LLM. So we're trying [12:35] to evolve as it uh as it we get more and [12:37] more data into that the user feedbacks [12:39] but what we have seen initially was 40% [12:42] or 50%. It was very bad actually uh to [12:45] see that results and then the phase two [12:48] whatever we introduced with the KPIs and [12:50] stuff it it was little better because of [12:53] you're trying to target what metrics [12:55] you're trying to solve because and we [12:58] are not trying to solve at the [12:59] enterprise enterprise level we're trying [13:01] to solve at each domain specific level [13:04] because when the user logs into the [13:06] system they have a federated access what [13:08] access what part of the domain they are [13:10] belongs to and if it is part of H [13:12] finance or HR or sales that 20 tables [13:15] only we'll be hitting for that question [13:18] rather than hitting like 100 tables or [13:20] thousand tables. [13:23] >> Uh just to add on to that point so there [13:25] are many tables created views created by [13:29] individuals marketers etc. But then um [13:32] how do you decide on like the best table [13:34] to uh pick because um some tables have [13:38] lot of aggregated information some of [13:40] them um you know shed across um h how [13:45] are you taking all of those [13:46] considerations into account? So we have [13:49] a system like it's called an enterprise [13:51] views uh the the all the data ms we're [13:54] sitting in that so we enabled this uh [13:57] chatbot on those actually so that is [14:00] more on uh aggregated data [14:02] semi-aggregated data you can say so end [14:04] up into there and then we have [14:06] definitions defined on that like KPIs [14:08] because the more use case like what we [14:10] have seen the use case more was on the [14:13] business user side like how can they [14:14] dissect and get the results faster [14:16] day-to-day life because So that's [14:17] delaying them lot of uh insights and [14:20] decision making. So that's where uh we [14:22] try to target at the enterprise level. [14:26] >> How did you manage the context for a [14:28] multi-turn conversations where you have [14:31] follow-up questions? uh we have a system [14:34] for that like uh where we used uh follow [14:36] up with uh again LLM is one and then the [14:41] uh memory we have uh dyno we used to to [14:45] store that uh memory uh to store the [14:47] previous conversations but we only take [14:50] within the session like for last five uh [14:52] conversations because that dilutes the [14:54] information as it goes like the more the [14:56] conversation it diluting the information [14:58] eventually so we want to limit that at [15:00] that level. Thank you. [15:03] >> Hello. Uh you also showed visualization. [15:06] So was it like dynamic or is it [15:09] something you already assumed uh like [15:11] LLM will generate? Uh actually there are [15:14] two two things to that actually. One is [15:15] the LLM generates that with an Python [15:18] program be behind the scenes actually. [15:21] Uh but there is another option where you [15:23] can use an open-source it's called Pyg [15:26] Walker where it can like a drag and drop [15:29] you can play around with that [15:30] visualization. So users can play around [15:32] whichever they want to use.