Picture this: your company sits on tens of petabytes of data. To put that into perspective, if I had a penny for each byte and stacked them up, I’d have enough to reach Pluto and back, with some change left over. That\u2019s the reality we face at Rocket Mortgage, and it’s probably not too different from what your organization is facing.\ud83d\udca1All that data represents a goldmine of insights, but the challenge has always been making it accessible to the people who need it most (executives, analysts, and decision-makers who understand the business but don\u2019t necessarily code SQL in their sleep.)That\u2019s why we built Rocket Analytics, and today I want to take you behind the scenes of how we created a text-to-SQL application using agentic RAG (Retrieval-Augmented Generation). This tool fundamentally changes how our teams interact with data, letting them focus on what they do best: asking strategic and thoughtful questions, while the system handles the technical heavy lifting.What Rocket Analytics actually doesHere\u2019s how it works in practice: a user asks a natural language question, such as:\u201cGive me the count of loans for the past six months.\u201dBehind the scenes, the system:Converts the question into a SQL queryExecutes it against the relevant databaseReturns the results in a clean, understandable format\ud83d\udca1But the real magic comes when users ask follow-up questions. They can request insights based on the initial results, or generate dashboards highlighting actionable patterns and trends.During a recent demo, someone went from raw loan counts to a comprehensive dashboard showing:Total loans closedAverage daily loansMaximum daily loan datesTrend analysis\u2014all within seconds. For executives and stakeholders in the mortgage industry, where speed of decision-making is crucial, this capability is transformative.The architecture behind the magicRocket Analytics relies on several key components, each playing a critical role in delivering accurate results.Query transformation: Making questions understandableThe journey starts when a user inputs their question. Large language models, while powerful, don\u2019t inherently understand our specific business context or data structures. Ask ChatGPT, for example:\u201cCan you give me the top ten days for loan production?\u201dIt won\u2019t know exactly what you mean.Our query transformation module converts natural language into explicit, actionable instructions. That same question becomes:\u201cWrite a SQL query to get the top ten days based on loan closing.\u201dThis step is crucial because we never pass actual data to the model\u2014only metadata about our database structures.Building and managing the knowledge baseBefore queries can be answered, we need a comprehensive knowledge base. We:Convert database metadata into embeddings using Amazon TitanStore them in a FAISS (Facebook AI Similarity Search) vector storeInclude information about all tables, schemas, relationships, and business contextThis knowledge base is the foundation that allows the system to understand which tables might be relevant for any given question.Intelligent retrieval: Finding the right data sourcesWhen a question comes in, we perform a hybrid search:Semantic similarity search identifies potentially relevant tablesKeyword triggers ensure critical tables are included even if semantic search alone misses themFor example, a knowledge base with 15 tables might be narrowed down to 4\u20135 most relevant. This ensures the LLM is not overwhelmed and reduces errors.Re-ranking for precisionOnce candidate tables are retrieved, we use Cohere\u2019s re-ranker model (BART-based) to refine selection. This classification system evaluates the likelihood of each document being relevant to the user\u2019s question.This step is essential for preventing hallucinations, ensuring the LLM generates accurate SQL queries based only on relevant metadata.Prompt engineering: The art and scienceCrafting prompts involves multiple layers:Standard guidelines: Always answer within context; use exact table and column namesAdaptive guidelines: Few-shot examples for tricky question typesDomain-specific context: Helps the model understand mortgage-industry terminologyA typical prompt looks like:\ud83d\udca1\u201cGiven the below two tables and their detailed metadata information, please answer the question at the end of the prompt, keeping the below guidelines in mind\u2026\u201dThis carefully crafted prompt ensures accuracy while guiding the model through complex queries.Execution and post-processingOnce the LLM generates a SQL query:Execute it against the appropriate databasePost-process results to make them user-friendlyUsers can consume results as-is or ask follow-up questionsFor instance, a user reviewing six months of loan data can then ask:\u201cCan you show me some insights?\u201dThe system will automatically generate visualizations and highlight actionable trends, all within seconds.The agentic framework: Orchestrating complexitySo far, we\u2019ve described a single agent handling queries in one domain. But organizations often need cross-domain insights.Our agentic framework introduces:A main orchestrator agentSub-agents specialized in domains like sales, marketing, and operationsEach sub-agent has its own knowledge base and optimized promptsCurrently, each agent operates within domain constraints. The next step is multi-agent coordination, enabling cross-domain queries without manual integration.Performance optimization: Speed and cost matterTwo strategies have made a big difference:Semantic cachingAvoids redundant processing by reusing tables and prompts for semantically similar questionsRequires careful tuning to handle subtleties like \u201cpast six months\u201d vs. \u201cpast ten days\u201dPrompt cachingCaches static prompt componentsReduces computation and improves latency and cost efficiencyThese optimizations help Rocket Analytics scale across teams without slowing down queries.Measuring success: How we know it\u2019s workingWe evaluate the system with several key metrics:Relevance: Are retrieved tables truly relevant? Checked using LLM-as-a-judgeHallucination detection: Compares outputs against a golden datasetToxicity screening: Ensures all responses are professionalMultiple iterations per query give a reliable performance overview and help catch edge cases.Real-world impact: Who benefits and how?Rocket Analytics helps teams across the organization:Sales & MarketingAnalyze campaign effectivenessIdentify seasonal trendsCompare strategies across segmentsOperationsMonitor performance metricsOptimize processesFinanceGenerate reports instantlyAnalyze trendsPreviously, these insights took days to produce via analysts; now they\u2019re available in seconds.The human element: Trust but verifyAI accelerates insight, but human oversight is critical:Human-in-the-loop checkpoints for high-stakes queriesRegular audits to catch drift or emerging errorsEnsures speed and accuracy without compromising trustLooking ahead: The evolution continuesOur roadmap includes:Multi-agent coordination for cross-domain insightsContinuous performance improvementsExpanded domain coverage for more teamsBy democratizing data access, Rocket Analytics enables any team member to make data-driven decisions, without SQL expertise.Final thoughtsBuilding a text-to-SQL system with agentic RAG has taught us:AI can augment human intelligence, not replace itSpeed, accuracy, and accessibility matter more than sheer complexityThoughtful implementation and user-centered design are keyBy removing technical barriers, Rocket Analytics empowers more people to explore, analyze, and act on data, creating a competitive advantage.The goal isn\u2019t to build the most sophisticated AI but to build the most useful AI.Arjun Barli, Staff Data Scientist at Rocket Mortgage, gave this presentation at our Agentic AI Summit, New York, 2025. <\/p>\n","protected":false},"excerpt":{"rendered":"