<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Gunther Hagleitner on Medium]]></title>
        <description><![CDATA[Stories by Gunther Hagleitner on Medium]]></description>
        <link>https://medium.com/@gunther.hagleitner?source=rss-dc9bb5ebe29e------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*aPpxnjrThNrz--As</url>
            <title>Stories by Gunther Hagleitner on Medium</title>
            <link>https://medium.com/@gunther.hagleitner?source=rss-dc9bb5ebe29e------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 23:50:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@gunther.hagleitner/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[SQL Agents Need Compilers]]></title>
            <link>https://blog.waii.ai/sql-agents-need-compilers-7d52cc284408?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/7d52cc284408</guid>
            <category><![CDATA[compilers]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[llm]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Wed, 11 Jun 2025 21:03:21 GMT</pubDate>
            <atom:updated>2025-06-11T21:03:21.834Z</atom:updated>
            <content:encoded><![CDATA[<p>by <a href="https://www.linkedin.com/in/swaraj-nayegandhi-09a446196/">Swaraj Nayegandhi</a>, <a href="https://in.linkedin.com/in/rajeshbalamohan">Rajesh Balamohan</a>, and <a href="https://www.linkedin.com/in/hagleitn">Gunther Hagleitner</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EDQB0bdaxTKiYe43UNBEDQ.png" /></figure><p>SQL agents represent one of the most compelling applications of Large Language Models (LLMs) in enterprise software. The promise is transformative: democratize data access by letting anyone query complex databases using natural language. No more waiting for the data team, no more learning SQL syntax, just ask questions and get answers.</p><p>The challenge is that getting SQL agents right is extraordinarily difficult. Unlike code generation for general programming, SQL operates in a constrained, high-stakes environment where small mistakes have big consequences. A single malformed query can lock tables, expose sensitive data, or return subtly incorrect results that mislead business decisions.</p><p>The gap between demo-quality and production-ready is vast. Simple queries work well enough to generate excitement, but real-world usage quickly reveals fundamental problems with accuracy, performance, and security. Most organizations that pilot SQL agents end up disappointed by the results.</p><p>But this doesn’t mean SQL agents are fundamentally flawed. The issue is that we’ve been treating SQL generation as a pure language modeling problem when it’s actually a compiler problem. With the right architectural approach, SQL agents can be both powerful and reliable.</p><h3>The Syntax Problem</h3><p>Let’s start with the basics: LLMs generate syntactically broken SQL all the time. As context grows or queries get complex, the error rate shoots up fast.</p><p><strong>Dialect Confusion</strong>: SQL is SQL until it isn’t. LLMs predictably struggle where SQL dialects diverge most. Date/time constructs, semistructured data handling (JSON, XML), nested queries, and specialized features like geospatial or streaming functions vary wildly between engines. Sparse training data in many of these areas compounds the problem. The result is broken queries that mix incompatible dialect features. Here is a common example:</p><pre>-- What the LLM generates (invalid)<br>SELECT <br>  customer_id,<br>  user_data-&gt;&gt;&#39;email&#39; as email                     -- PostgreSQL JSON syntax<br>FROM customer_profiles,<br>LATERAL FLATTEN(input =&gt; user_data:tags) as f      -- Snowflake FLATTEN syntax<br>WHERE f.value = &#39;premium&#39;</pre><p><strong>User-defined Function (UDF) Hallucinations</strong>: LLMs use functions that don’t exist on your target engine. They’ve learned function names from documentation across different platforms but struggle to maintain engine consistency. This is particularly common with date functions, where every engine seems to have invented its own syntax for basic operations. Your agent might use BigQuery’s DATE_SUB() when you&#39;re running on SQL Server (which uses DATEADD()), or reach for MySQL&#39;s NOW() on a PostgreSQL system that expects CURRENT_TIMESTAMP.</p><p><strong>Made-up Tables</strong>: The less obvious the mapping from natural language concepts to your actual database tables becomes, the more likely LLMs are to synthesize non-existent table names. Complex queries requiring multi-way joins, large schemas, or tables with names weakly linked to their semantic meaning all increase hallucination rates.</p><p>Ask about “quarterly performance across multiple dimensions” and your agent might generate:</p><pre>-- What the LLM generates (invalid)<br>SELECT quarter, region, product_category, SUM(revenue)<br>FROM quarterly_sales_summary  -- This table doesn&#39;t exist<br>JOIN regional_performance     -- Neither does this one<br>  ON quarterly_sales_summary.region_id = regional_performance.region_id<br>WHERE quarter &gt;= &#39;2024-Q1&#39;<br>GROUP BY quarter, region, product_category</pre><p>Your actual schema might have tables like order_line_items, customer_addresses, and product_catalog - but when faced with a semantically complex request, LLMs default to reasonable-sounding table names from their training data rather than mapping concepts to your actual database objects.</p><h3>The Semantic Problem</h3><p>Even when the SQL is syntactically correct, it’s often semantically wrong. This is the more challenging problem because semantic correctness isn’t just about valid syntax — it’s about whether the query actually makes sense in the domain.</p><p>Unlike syntax errors, semantic issues are rarely captured in database schemas. Semantics are often more ambiguous and less clear-cut than syntax rules, and subtle mistakes can lead to severely wrong answers. These queries run without error but produce meaningless or misleading results.</p><p>At first glance, semantic correctness doesn’t seem like a compiler problem. Semantics are contextual, domain-specific, and often subjective. But here’s the key insight: many semantic rules can be modeled as constraints on the allowed SQL space. You choose how much of the semantics to cover — creating logical guardrails for the generated SQL.</p><p>This means you can use a compiler to test and verify semantics, not just syntax. An appropriate constraint language can encode domain rules, data relationships, and analytical best practices — dramatically improving the quality of queries LLMs produce.</p><p>Take currency mixing. Your LLM might generate:</p><pre>SELECT SUM(revenue) FROM sales  -- Mixing USD and EUR!</pre><p>But you can catch this with a semantic constraint:</p><pre>- id: revenue_currency_partition<br>  description: Revenue must be grouped by currency<br>  match:<br>    agg_fn:<br>      function: &quot;SUM&quot;<br>      column: &quot;revenue&quot;<br>  require:<br>    group_by: [&quot;currency&quot;]<br>  severity: error</pre><p>Or consider geographic hierarchies. This query looks reasonable but is actually meaningless:</p><pre>SELECT city, SUM(sales)<br>FROM transactions<br>GROUP BY city  -- Which &quot;Springfield, they are only unique per state.&quot;?</pre><p>A semantic constraint can enforce the hierarchy:</p><pre>- id: geo_hierarchy_city_state<br>  description: Must include state when grouping by city<br>  match:<br>    group_by_includes: [&quot;city&quot;]<br>  require:<br>    group_by: [&quot;state&quot;]<br>  severity: error</pre><p>You can also catch statistical nonsense like averaging ZIP codes or summing inventory across time periods (inventory is a point-in-time measure, not additive).</p><p>Creating these semantic constraints might sound like significant manual work, but we’ve automated the process by generating what we call semantic statements from database, data, documentation, files, and other enterprise metadata systems.</p><p>These semantic statements are natural language descriptions about the domain that contain optional semantic constraints in our constraint language. The LLM is really adept at writing these constraints automatically.</p><p>The combination is powerful: the natural language helps generate the query, while the constraints validate it. LLMs can leverage the semantic statements to understand domain context while the compiler enforces the rules. There’s typically no need for manual intervention.</p><h3>Performance and Security Constraints</h3><p>Performance issues are another class of problems that constraints can solve. Consider a historical sales table with billions of rows spanning decades. An LLM might try to aggregate the entire dataset without any date filters.</p><p>With a performance constraint requiring date filters on historical tables, the compiler catches this violation and returns an error to the LLM: “Historical tables must include date range filters.” If no default date range is configured, the system can prompt the user: “This query would scan the entire historical dataset. Please specify a date range.”</p><p>In systems like BigQuery that charge by data scanned, these constraints become cost guardrails-preventing accidentally expensive queries that could blow through budgets. These cost overrun guarantees are considered a must-have by many of our customers.</p><h3>Security Constraints</h3><p>Security constraints work differently than performance or semantic constraints. Instead of flagging errors, they’re transparently injected by the compiler before execution. This maintains the illusion that users have access to the full database while silently restricting them to only data they’re authorized to see.</p><pre>- id: us_analysts_region_filter<br>  description: US analysts can only see US data<br>  match:<br>    from_table: &quot;global_sales&quot;<br>  require:<br>    where_filter:<br>      column: &quot;region&quot;<br>      allowed_values: [&quot;US&quot;]<br>  severity: error</pre><p>When a US analyst queries the global sales table, they think they’re looking at all sales data, but the system automatically injects WHERE region = &#39;US&#39; into their query. The same principle applies to tables they don&#39;t have access to—the compiler can transparently redirect queries or filter results without the user knowing restrictions exist.</p><p>Security constraints cannot be overridden by LLMs or users. These are hard stops that operate at the compiler level.</p><h3>The Compiler Solution</h3><p>As we’ve seen, the answer to all these problems is a SQL compiler framework designed for LLM-generated queries.</p><p>Here’s how it works:</p><ol><li><strong>Parse</strong>: Compile the query into an Abstract Syntax Tree (AST)</li><li><strong>Resolve</strong>: Use your catalog to resolve all objects (tables, columns, functions)</li><li><strong>Validate</strong>: Apply all relevant constraints to the resolved tree</li><li><strong>Decide</strong>: For each violation, can the compiler fix it automatically, or does the LLM need to rewrite?</li></ol><p><strong>Auto-fixes</strong> are great for simple dialect translations. If someone uses PostgreSQL’s EXTRACT(MONTH FROM date) but you&#39;re on Snowflake, just translate it to MONTH(date). Missing projections in Common Table Expressions (CTEs)? Add them automatically.</p><p><strong>LLM rewrites</strong> are needed for more complex issues. The compiler identifies the minimum spanning subquery that needs fixing, provides a clear error message, and suggests fixes. The LLM rewrites just that piece and plugs it back in.</p><p>This loop continues until all constraints are satisfied or you give up and surface the error to the user.</p><pre>┌─────────────────┐    ┌──────────────┐    ┌─────────────────┐<br>│   LLM Query     │───▶│   Compiler   │───▶│   Constraints   │<br>│   Generation    │    │   (Parse &amp;   │    │   Validation    │<br>└─────────────────┘    │   Resolve)   │    └─────────────────┘<br>         ▲             └──────────────┘             │<br>         │                                          ▼<br>         │              ┌──────────────┐    ┌─────────────────┐<br>         │              │ Auto-fix or  │◀───│ Violation Found │<br>         └──────────────│ LLM Rewrite  │    └─────────────────┘<br>                        └──────────────┘</pre><h3>Results That Matter</h3><p>We’ve tested this approach across several dimensions:</p><p><strong>Syntactic Correctness</strong>: LLMs generated syntactically valid SQL only 77% of the time on our test set. With compiler intervention, this improved to 99%. This was tested over a large set of queries focusing purely on whether the SQL would compile, without considering semantic correctness. We see these low error rates confirmed in production — SQL syntax issues occur in much fewer than 1% of all queries in real-world workloads and are basically a solved problem.</p><p><strong>Semantic Correctness</strong>: On multiple large warehouses for a Fortune 10 customer with complex business context, semantically valid queries improved from 60% to 96%. Simpler databases might see higher baseline accuracy. We used a user intent-based metric over a ground truth set. Note: This increase can mean the difference between successful production and failure.</p><p><strong>Performance &amp; Security</strong>: The compiler achieved 100% compliance with performance and security constraints, while LLMs alone handled these accurately only 87% of the time. Performance and security considerations are often straightforward for LLMs to follow, but misses occur especially as overall query complexity and context grow. No queries that pass the compiler violate performance or security constraints — these are hard stops. We tested performance and security constraints across multiple databases.</p><h3>But Won’t LLMs Just Get Better?</h3><p>This is the question everyone asks. Won’t OpenAI’s GPT-(n+1) or Anthropic’s Claude-(n+1) just solve all these problems?</p><p>Maybe eventually. But we’ve seen no indication that even syntax errors will disappear anytime soon. The complexity of real-world SQL — with its dialects, extensions, and domain-specific constraints — creates a massive surface area for errors.</p><p>More importantly, even human developers use syntax checking and linting. It’s cheap insurance. These constraints can be enforced without any chance of failure.</p><p>For security and performance constraints, you need perfection every time. A 99% success rate means 1 in 100 queries might leak sensitive data or bring down your database. That’s not acceptable. In these cases we see no alternative than using a compiler.</p><h3>Why Not Just Use the Database’s Compiler?</h3><p>Fair question. Database engines already have sophisticated SQL compilers. Why build another one?</p><p>Several reasons:</p><p><strong>LLM-Optimized Errors</strong>: Database error messages are written for humans. “Syntax error near ‘)’” doesn’t help an LLM much. We can provide structured error messages that LLMs understand and can act on.</p><p><strong>Batch Processing</strong>: Handle multiple errors in one pass instead of the traditional fix-one-error-at-a-time approach.</p><p><strong>Extended Constraints</strong>: Enforce semantic, performance, and security rules that go beyond standard SQL validation.</p><p><strong>Load Management</strong>: Keep broken SQL generation attempts away from your production database compiler.</p><p>The typical “eval loop” that sends every generated query to the database creates unnecessary load and exposes your system to potentially expensive operations during the debugging process.</p><h3>A Path Forward</h3><p>SQL agents don’t have to be unreliable. With compiler-based validation, you can move from demo-quality to production-ready systems that users actually trust.</p><p>We see this compiler approach as essential for successful real-world deployments, yet most SQL and analytics agent systems have yet to incorporate these validation layers. Many of our customers have tried and failed without it — running into accuracy issues that erode user confidence, expose security vulnerabilities, or end in unexpected cost overruns. The gap between what’s possible in demos and what works in production remains large across the industry.</p><p>Want to see this approach in action? We’d love to show you how other organizations are using compiler technology to solve real SQL generation challenges. Whether you’re dealing with complex schemas, strict security requirements, or cost-sensitive environments, there’s likely a path forward.</p><p><a href="https://www.waii.ai/">Reach out through our website</a> to learn more about real-world implementations or to discuss how this approach might work for your specific use case.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7d52cc284408" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/sql-agents-need-compilers-7d52cc284408">SQL Agents Need Compilers</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building Trust in Text-to-SQL: Making AI Analytics More Reliable]]></title>
            <link>https://blog.waii.ai/building-trust-in-text-to-sql-making-ai-analytics-more-reliable-4b3c94b4960d?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/4b3c94b4960d</guid>
            <category><![CDATA[conversational-analytics]]></category>
            <category><![CDATA[text-2-sql]]></category>
            <category><![CDATA[llm]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Fri, 16 May 2025 00:44:34 GMT</pubDate>
            <atom:updated>2025-05-16T00:44:34.823Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xu-yqtwWFjNWePSrKQSyMg.png" /></figure><h3>Why Trust Matters (And Why It’s Complicated)</h3><p>Let’s be honest: trust in AI-generated SQL isn’t just about hallucinations. That’s the obvious problem everyone talks about. The real challenge is much more nuanced.</p><p>Think about it this way — when you ask a human analyst “What were our Q3 sales?”, they make assumptions. They assume you mean the current year, not 2019. They assume you want net revenue, not gross. They assume you want all channels, not just online. These assumptions are usually reasonable, but sometimes they’re wrong.</p><p>AI has the same challenge, just amplified. The difference? Humans can read the room, understand context from your tone, and clarify when something feels off. AI can’t (yet) detect that slight furrow in your brow when the number seems too high.</p><p>Here’s where it gets interesting: these problems aren’t unique to AI. They’re inherent to the domain of data analysis itself. Language is ambiguous. Business contexts are complex. Even the most experienced analyst occasionally misinterprets a request.</p><p>The traditional approach has been to make AI more accurate — better models, more training data, etc. That’s important, but it misses the point. Perfect accuracy isn’t the goal. Trust is. And trust isn’t about being right 100% of the time. It’s about being predictable, transparent, and recoverable when things go wrong.</p><h3>Our Trust Framework: Know, Set, Source, Learn</h3><p>We’ve identified four principles that make AI analytics trustworthy:</p><p><strong>1. Know thy user</strong> — A CFO asking for revenue numbers has different needs than a sales rep checking their quota. The CFO might want precision and multiple cuts of the data. The sales rep probably just wants a quick answer. Understanding this context means making appropriate assumptions and choosing the right level of detail.</p><p><strong>2. Set expectations</strong> — Good judgment means knowing when to add context. If the question is ambiguous, clarify. If the result is surprising, explain why. But don’t overwhelm users with unnecessary details. A simple question like “How many customers do we have?” shouldn’t trigger a dissertation on data lineage.</p><p><strong>3. Have your reasons ready</strong> — When someone questions a result, you should be able to explain exactly how you got there. Not just “the AI said so,” but the actual logic, assumptions, and data sources. Think of it as showing your work, like in math class.</p><p><strong>4. Learn from interactions</strong> — Pay attention to when users accept results versus when they dig deeper. When do they ask for clarification? Use this feedback to calibrate future responses for similar questions and users.</p><h3>How We Built It: Six Features for Trust</h3><p>Working with our partners, we’ve implemented features that embody these principles:</p><h3>1. Assumption Tracking</h3><p>We capture every assumption made during query planning and execution. For instance, if you ask for “sales” but don’t specify a time period, we might assume you want year-to-date data.</p><p>But here’s the key: we distinguish between assumptions and established definitions. Things we know from your knowledge graph — like metric definitions learned from your PowerBI model — aren’t flagged as assumptions to avoid noise.</p><p>Assumptions go well beyond metric definitions. They include choices like which table to use when multiple similar ones exist, assumptions about column cardinality (is customer_id unique?), how to handle null values, or filtering decisions when criteria are ambiguous.</p><p>We rank each assumption by how likely or outlandish it is. Assuming “sales” means YTD when no timeframe is given? Pretty reasonable. Assuming you want data from 5 years ago? That’s more questionable and gets flagged for user attention.</p><h3>2. Source Attribution</h3><p>Our knowledge graph isn’t just a schema — it’s a rich web of understanding about your data world. It contains information about tables, columns, metrics, measures, dimensions, domain knowledge from documents, semantic layer information, and insights learned from parsing historical queries and studying data patterns.</p><p>For example, we understand that sales figures are stored in the ‘revenue’ column as decimals in hundreds of millions USD. When you ask for “total sales this quarter,” the query gets attributed back to this knowledge. You can see exactly why we chose that specific column, how we handled the units (multiplying by 100M), what currency conversion we applied, and which tables we joined to get the timeframe.</p><h3>3. Confidence Scoring</h3><p>We have a separate system that uses query complexity, alignment with our knowledge graph, historical accuracy, and other factors to predict confidence in our answer.</p><p>Think of it this way: the simpler the question or the more familiar it is given our knowledge corpus, the higher our confidence. “How many customers do we have?” scores high if we’ve seen similar queries and have clear table definitions. “Show me revenue correlation with customer satisfaction by region for non-standard product categories” scores much lower.</p><p>This confidence score serves both humans and machines in making further decisions. Low confidence might trigger the system to ask clarifying questions or provide additional context automatically. Users can see the score and decide whether to dig deeper, accept the result, or refine their question.</p><h3>4. User Profiling</h3><p>We build profiles capturing technical knowledge, risk tolerance, and communication preferences. But more importantly, we allow configuration of assumption behavior.</p><p>Many customers prefer questions over assumptions. For example, if there are two or more supported ways to do something in the database — like multiple valid date columns or similar tables — you can configure the system to never use an internal tie-breaker. Instead, it asks clarifying questions.</p><p>This configuration can be set at the user, team, or organization level. A conservative finance team might want clarification on every ambiguous query, while a data engineering team might prefer reasonable defaults with detailed context.</p><p>Beyond explicit configuration, we learn preferences over time. If someone consistently rejects certain types of assumptions, we adjust their profile to ask for clarification in similar situations.</p><h3>5. Adaptive Communication</h3><p>Using the confidence score, user profile, and assumption ranking, we decide whether to just answer, ask follow-ups, or provide additional context.</p><p>High confidence + user prefers minimal details: “Total revenue is $45.2M.”</p><p>Medium confidence + user wants transparency: “Total revenue is $45.2M. I used the ‘net_revenue’ table and assumed you wanted Q3 YTD since no timeframe was specified.”</p><p>Low confidence + risk-averse user: “I see two potential revenue columns — ‘gross_sales’ and ‘net_revenue’. The gross includes refunds. Which would you prefer for your Q3 analysis?”</p><h3>6. Feedback Integration</h3><p>Users can provide feedback on the output, and we parse that feedback to update our knowledge graph or user preferences. If you correct an assumption — “Actually, I meant gross revenue, not net” — that clarification gets incorporated. If you request more detail consistently, we adjust your profile accordingly.</p><p>This means subsequent interactions benefit from these corrections. The same question asked later might produce a different response based on what we’ve learned from your feedback.</p><h3>Example: A Trust-Building Session in Action</h3><p>Let’s see how these features work together in a real conversation:</p><p><strong>User:</strong> “What were our sales last quarter?”</p><p><strong>Waii (</strong><em>Confidence: 75%):</em> “Q3 sales were $2.3M. I used the primary sales table (vs. backup table used 8% of the time) and assumed ‘last quarter’ meant Q3 2024.”</p><p><strong>Sources:</strong> Primary.sales_data table, PowerBI “Net Revenue” definition <strong>Assumptions:</strong> Time period = Q3 2024 (medium confidence), Table choice = primary vs backup (high confidence)</p><p><strong>User:</strong> “That seems low. Show me the breakdown by region.”</p><p><strong>Waii (</strong><em>Confidence: 65%):</em> “I notice some unusual patterns that might explain the low number. Here’s the breakdown:</p><ul><li>West: $1.8M</li><li>East: $500K</li><li>International: $0</li></ul><p>The international sales are zero because I found no data in the international_sales table for Q3. This could be a data processing issue or actual zero sales.”</p><p><strong>Sources:</strong> Regional mappings from sales_territories table <br><strong>Assumptions:</strong> Region definitions from standard geographic groupings (high confidence)</p><p><strong>User:</strong> “Actually, when I say sales, I always mean gross revenue including refunds. Can you update that?”</p><p><strong>Waii:</strong> “Got it. I’ve updated your preference. Recalculating with gross revenue including refunds:</p><p>Q3 sales were $2.8M (+$500K from the previous number). The regional breakdown is:</p><ul><li>West: $2.1M</li><li>East: $700K</li><li>International: $0</li></ul><p>I’ve also noted that for you, ‘sales’ = gross revenue. This will apply to future queries.”</p><p><strong>Feedback Integration:</strong> User preference updated in profile: “sales” = gross revenue for this user</p><p>This interaction shows all six trust features working together: assumption tracking with confidence levels, source attribution, adaptive communication based on confidence, and feedback integration that improves future interactions.</p><h3>Built into the Waii API: Trust by Default</h3><p>Here’s the best part: all of these trust mechanisms are baked directly into Waii API responses. You don’t need to build them yourself.</p><p>Every query response includes:</p><ul><li>The assumptions made and their confidence rankings</li><li>Source attribution mapping back to your knowledge graph</li><li>Confidence scores for the overall result</li><li>Contextual explanations tailored to the user profile</li><li>Feedback mechanisms to improve future interactions</li></ul><p>Any application built on top of Waii gets these trust capabilities for free. Whether you’re building a dashboard, a chatbot, or integrating into your existing analytics stack — the trust infrastructure comes along for the ride.</p><p>This means developers can focus on their user experience rather than rebuilding trust mechanisms from scratch. More importantly, it means every Waii-powered application starts with a foundation of transparency and reliability.</p><h3>The Result: Trustworthy AI Analytics</h3><p>The goal isn’t perfection. It’s building a system that users actually trust to use for important decisions. One that says “I don’t know” when appropriate, explains its reasoning clearly, and learns from mistakes.</p><p>We’re seeing encouraging results: users are more willing to run complex queries without human validation, they catch edge cases faster when they do occur, and they’re generally more confident in AI-generated insights.</p><p>Trust in AI isn’t about making the technology perfect. It’s about making it predictable, transparent, and honest about its limitations. Just like the best human analysts.</p><p><em>Want to see this in action? We’re always looking for partners to help us refine these trust mechanisms. Drop us a line if you’re interested in being part of the conversation.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4b3c94b4960d" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/building-trust-in-text-to-sql-making-ai-analytics-more-reliable-4b3c94b4960d">Building Trust in Text-to-SQL: Making AI Analytics More Reliable</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Kaiser Schmarrn: A Traditional Austrian Delight]]></title>
            <link>https://medium.com/@gunther.hagleitner/kaiser-schmarrn-a-traditional-austrian-delight-27210912ce77?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/27210912ce77</guid>
            <category><![CDATA[recipe]]></category>
            <category><![CDATA[austria]]></category>
            <category><![CDATA[food]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Sat, 26 Apr 2025 16:50:57 GMT</pubDate>
            <atom:updated>2025-04-27T01:05:26.978Z</atom:updated>
            <content:encoded><![CDATA[<p>by Livia Auer</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*a8wMSae9ho-MQ2yuO87zEQ.png" /></figure><p>There’s something magical about traditional European desserts that warm both heart and soul. Today, I’m excited to share my grandmother’s recipe for Kaiser Schmarrn (also known as Emperor’s Mess), a beloved Austrian pancake dish that’s been bringing smiles to my family’s table for generations.</p><h3>What is Kaiser Schmarrn?</h3><p>Kaiser Schmarrn is a fluffy, shredded pancake that originated in Austria. Legend has it that it was first prepared for Emperor Franz Joseph I, hence the name “Kaiser” (Emperor). This delightful dish straddles the line between breakfast and dessert, making it perfect for any time of day when you need a little comfort food magic.</p><h3>Ingredients</h3><ul><li>½ cup all-purpose flour</li><li>½ cup milk (about 125 ml)</li><li>2 eggs</li><li>About 20g sugar (approximately 1½ tablespoons)</li><li>Pinch of salt</li><li>Butter for cooking</li><li>Raisins (optional)</li><li>Rum for soaking raisins (optional)</li></ul><h3>Instructions</h3><ol><li><strong>Prepare the batter</strong>: In a large bowl, mix the flour and milk until well combined.</li><li><strong>Separate the eggs</strong>: Carefully separate the egg whites from the yolks. Beat the egg whites until they form stiff peaks.</li><li><strong>Complete the batter</strong>: Stir the egg yolks, sugar, and salt into the flour and milk mixture until smooth.</li><li><strong>Fold in egg whites</strong>: Gently fold the stiff egg whites into the batter. Don’t stir vigorously — this folding technique helps maintain the fluffiness!</li><li><strong>Optional step</strong>: If you’d like to include raisins, soak them in a small amount of rum for extra flavor, then fold them into the batter.</li><li><strong>Cook the pancake</strong>:<br>- Heat a pan with butter until hot.<br>- Pour in the batter about 1–2 cm (about ¾ inch) high.<br>- Cover with a lid and cook until the bottom turns golden brown.</li><li><strong>Cut and flip</strong>: Once the bottom is golden, cut the pancake into quarters (like a pizza) and flip each piece. Continue cooking until the other side is golden brown as well.</li><li><strong>Shred and caramelize</strong>:<br>- When both sides are golden, tear or cut the pancake into bite-sized pieces.<br>- Push the pieces to the sides of the pan to create a space in the middle.<br>- Add butter and sugar to the center and let it caramelize.<br>- Mix everything together to coat the pancake pieces with the caramelized sugar.</li><li><strong>Serve</strong>: Transfer to plates and serve while warm!</li></ol><h3>Serving Suggestions</h3><p>Kaiser Schmarrn is traditionally served with powdered sugar sprinkled on top and a side of fruit compote, applesauce, or fresh berries. For an extra indulgent treat, a dollop of whipped cream never hurts!</p><h3>Recipe Notes</h3><ul><li>The key to perfect Schmarrn is in the egg whites — make sure they’re beaten until properly stiff for the fluffiest results.</li><li>The rum-soaked raisins are optional but add a wonderful depth of flavor. For a family-friendly version, you can skip the rum or substitute with vanilla extract.</li><li>Don’t rush the caramelization process — this is what gives Kaiser Schmarrn its characteristic sweet, nutty flavor.</li></ul><p>Enjoy this little taste of Austria in your own home! This recipe has been passed down through generations in my family, and I hope it brings as much joy to your table as it has to ours.</p><p><em>Guten Appetit!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=27210912ce77" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Large Latency Models: How We Cut SQL Response Times in Half]]></title>
            <link>https://blog.waii.ai/large-latency-models-how-we-cut-sql-response-times-in-half-ea8073689e09?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea8073689e09</guid>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[conversational-analytics]]></category>
            <category><![CDATA[text-to-sql]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Thu, 24 Apr 2025 23:14:09 GMT</pubDate>
            <atom:updated>2025-04-24T23:14:09.720Z</atom:updated>
            <content:encoded><![CDATA[<blockquote>written by <a href="https://www.linkedin.com/in/hagleitn/">Gunther Hagleitner</a> and <a href="https://www.linkedin.com/in/rajeshbalamohan/">Rajesh Balamohan</a></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6gDIm9F_GweTlEp8dESiXQ.png" /></figure><p>After spending a combined 5 decades building and tuning database systems, we’ve learned one thing: impressive technology alone doesn’t solve real problems. It’s all about implementation.</p><p>At Waii, we’ve been obsessed with a single goal: making conversational analytics not just accurate, but fast enough that people actually want to use it. Let me share what we’ve discovered about achieving low latency in text-to-SQL systems.</p><h3>Right-Sizing Your Models (Or: It’s Complicated)</h3><p>It’s well understood that you don’t always need the biggest model to get top results. A Ferrari is unlikely to get your kids to school faster than a minivan in traffic. Our tests confirmed this — for specific database operations, GPT-4.1-mini performed almost identically to GPT-4.1 in human evaluations, while being dramatically faster and cheaper.</p><p>But here’s the twist: sometimes larger models are actually faster end-to-end. How? The more capable models can:</p><ol><li>Use index references instead of repeating entire objects</li><li>Work effectively with less context upfront</li><li>Generate precise changes rather than rebuilding everything from scratch</li></ol><p>This means in complex workflows, the “slower” model might process fewer tokens overall, resulting in better system performance. The key is decomposing your workflow and assigning the right model to each component:</p><ul><li>Use smaller, specialized models for understanding questions (3–4x faster)</li><li>Reserve larger, reasoning-heavy models for complex query generation</li><li>Pick mid-tier models for result explanation that balance coherence and speed</li></ul><p>Even within the same model family, we often see a 30% reduction in runtime for individual tasks just by choosing the right model.</p><h3>Token Economics: Every Token Counts</h3><p>LLMs can be painfully slow — with throughput as low as 5 tokens per second under load. Even when you choose smaller models or reserve dedicated GPU capacity (instead of paying by the token), LLM requests remain the biggest contributor to overall system latency.</p><p>Here’s what’s widely understood about token performance:</p><ul><li>Latency scales linearly with output tokens</li><li>Input tokens barely impact performance</li></ul><p>This creates a clear priority: focus ruthlessly on output token optimization first. Input tokens still matter, but primarily for cost and improving the model’s attention.</p><p>We implemented schema reference compression that transforms verbose SQL:</p><pre>-- Before<br>SELECT public.customer_data.customers.first_name, public.customer_data.customers.last_name<br>FROM public.customer_data.customers<br><br>-- After<br>SELECT c.first_name, c.last_name<br>FROM customers c</pre><p>You could go even further with single-token variables (effectively generating invalid SQL and fixing it later), but we found that maintaining readability was worth the small tradeoff. Our measurements showed that simpler schema references alone saved 12.1% of time in generating SQL queries.</p><p>For input tokens, pre-selecting relevant schema objects before context injection reduced token count by 71–79% in complex environments.</p><p>We also instructed models to limit explanation verbosity with simple directives like: “Provide only the essential reasoning steps needed to construct this query.”</p><p>This produced a 28% reduction in reasoning token usage without sacrificing accuracy.</p><h3>Two Hard Things</h3><p>There are only two hard problems in computer science: cache invalidation and naming things.</p><p>Let me introduce you to Enterprise SQL Cache 2000, standard edition.</p><p>Our analysis of conversational analytics workloads revealed some eye-opening opportunities:</p><ul><li>Depending on the workload, 33–54% of chat messages can be answered by simple transformations of previous answers in the same conversation</li><li>On average, 56% of questions are exact duplicates, and 71% are semantic duplicates (reworded versions of the same question)</li><li>SQL fragments like join graphs and complex expressions often repeat even in otherwise different queries</li></ul><p>With these patterns identified, we implemented several specialized caching strategies:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*w5CAgeS4kgX9n70mt_iKLQ.png" /></figure><p><strong>1. Conversational State Caching</strong> We maintain artifacts (queries, datasets, visualizations) for reuse within a conversation. When someone says “show that as a pie chart,” we can transform the visualization without regenerating the query or dataset, saving over 90% of execution time.</p><p><strong>3. Similarity-Based Retrieval</strong> When paired with speculative execution, this approach can provide near-instantaneous answers for questions we’ve seen before, even when phrased differently.</p><p><strong>4. SQL Mutation Caching</strong> We built a specialized agent that can perform common mutations to existing SQL queries (adding filters, changing date ranges, adding fields). This approach often saves more than 90% of execution time compared to query regeneration.</p><p><strong>5. Fragment Caching</strong> By storing and reusing common SQL patterns like complex join structures and window functions, we reduce the cost of generation by 10–15%.</p><p>The combined impact of these caching strategies is dramatic — for workloads with high cache hit rates, overall latency drops by more than half while costs decrease substantially.</p><h3>Everything, Everywhere, All at Once</h3><p>Agentic SQL generation isn’t a linear process — it’s a complex web of interconnected tasks. Entity extraction, knowledge graph lookups, table selection, column filtering, context compression, join generation… each step has its own dependencies.</p><p>We identified two major parallelization opportunities:</p><p><strong>Simple Parallelization</strong> Many tasks can run concurrently without any special consideration. For example, you can format a query and check for correctness at the same time. These are the low-hanging fruit of optimization.</p><p><strong>Speculative Execution</strong> Here’s where it gets interesting. Some steps have binary outcomes but take time to resolve. Instead of waiting, we can speculatively execute both possible paths and abandon the wrong branch once we know the result. A perfect example: looking up a similar previous question versus generating a new answer — we can start both processes and use whichever finishes first.</p><p>For medium complexity questions without retries or other complications, parallelization alone saves about 21% of execution time. The benefits scale with query complexity — the more steps involved, the greater the payoff from concurrent execution.</p><p>When combined with speculative execution, the time savings can be even more dramatic, especially for complex analytical queries with many decision points. Note that speculative execution increases the query cost (either in inference or token usage) — we’re explicitly trading latency for cost.</p><h3>The Illusion of Speed</h3><p>When generating SQL, we face a challenge: unlike direct model outputs, we can’t stream token-by-token because we need to ensure everything is working, optimal, secure, and reasonable. This creates blocking steps in our execution graph that can make the system feel slow.</p><p>That’s nearly 6 seconds end-to-end for a medium-complex query — an eternity in user experience terms.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*ULCa6Ax27viQmbrffX5Kcw.png" /></figure><p>The solution? Stream meaningful intermediate artifacts instead of waiting for the entire process to complete:</p><ol><li>As soon as we identify entities, we show them (400ms)</li><li>When we select tables and columns, we visualize the selection (900ms)</li><li>We display assumptions and parameters as they’re determined (1200ms)</li><li>We show draft SQL before validation (4500ms)</li><li>Finally, we present the validated, optimized query (5700ms)</li></ol><p>This approach means users see something meaningful within 400ms — a psychological game-changer that makes the system feel dramatically more responsive, even though the total execution time remains unchanged.</p><h3>Why Bother?</h3><p>“Won’t LLMs just get faster over time, making all this optimization irrelevant?”</p><p>I hear this question often, and it’s worth addressing. Yes, models will absolutely get faster — we’ve already seen dramatic improvements. But there are two compelling reasons why optimization remains crucial:</p><ol><li><strong>The value of today</strong>: Even if perfect performance arrives tomorrow (it won’t), there’s immense value in making technology more usable today. We’ve seen transformational impact from these speedups — teams that wouldn’t touch AI-powered analytics because of frustrating delays are now building it into their daily workflows.</li><li><strong>The performance/cost equation persists</strong>: History shows us that as technology gets faster, our demands grow to match. We simply expect to do more, process more data, or handle more complex questions. The specific techniques might shift, but the fundamental challenge of balancing performance and cost will remain relevant.</li></ol><p>When models get 10x faster, we’ll use that budget to tackle 10x more complex problems — and we’ll still need to optimize to cut response times in half again.</p><p>See these optimizations in action with your own database. Start a conversation at <a href="http://www.waii.ai/">www.waii.ai</a> and cut your SQL response times in half.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea8073689e09" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/large-latency-models-how-we-cut-sql-response-times-in-half-ea8073689e09">Large Latency Models: How We Cut SQL Response Times in Half</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unlocking the Power of Text-to-SQL: How Metadata Makes the Difference]]></title>
            <link>https://blog.waii.ai/unlocking-the-power-of-text-to-sql-how-metadata-makes-the-difference-2f42aaba4e84?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f42aaba4e84</guid>
            <category><![CDATA[generative-ai-tools]]></category>
            <category><![CDATA[sql-agent]]></category>
            <category><![CDATA[text-to-sql]]></category>
            <category><![CDATA[generative-bi]]></category>
            <category><![CDATA[metadata]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Wed, 05 Mar 2025 19:04:31 GMT</pubDate>
            <atom:updated>2025-03-05T19:04:31.048Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z3TMdWusXYbr6gmrr9vGEQ.jpeg" /></figure><p>In the evolving landscape of data analytics, the ability to simply ask questions of your data and receive accurate SQL queries in return is no longer science fiction — it’s a competitive advantage. Waii stands at the forefront of this revolution, offering a text-to-SQL solution that truly understands your data’s context. Let’s explore how <a href="https://www.waii.ai/">Waii’s</a> multi-faceted approach to metadata transforms conversational analytics from frustrating to fantastic.</p><h3>The Secret Ingredient: Metadata Magic</h3><p>What separates good text-to-SQL from great text-to-SQL? In a word: metadata. Without proper context, even the most sophisticated AI will struggle to translate natural language into accurate queries. Waii’s approach tackles this challenge head-on with a comprehensive strategy that can quickly achieve 95%+ accuracy.</p><h3>Automatic Semantic Context Generation: The Foundation</h3><p>Imagine setting up a new analytics tool and discovering it already understands your database intimately. That’s the reality with Waii’s automatic semantic context generation. By examining your database structure, Waii can:</p><ul><li>Predict joins between tables</li><li>Categorize columns as measures, metrics, dimensions, or hierarchies</li><li>Analyze data patterns for similarity search</li><li>Identify substructures and schema relationships</li><li>Pinpoint high-value content</li><li>Infer data lineage</li><li>Predict high-value metrics and KPIs</li><li>Formulate business context for each object</li></ul><p>This isn’t just AI — it’s AI combined with sophisticated compiler technology that understands database structures at a fundamental level. For well-modeled databases with clear documentation, this first step alone can deliver surprising accuracy.</p><h3>Leveraging Your Existing Documentation</h3><p>Why reinvent the wheel? Waii automatically ingests the documentation and metadata you’ve already created:</p><ul><li>Technical documentation (e.g.: text, excel, websites, pdf, confluence)</li><li>Semantic layers (e.g.: LookerML, dbt)</li><li>Business glossaries (e.g.: Collibra, Alation)</li><li>Data catalogs (e.g.: OpenMetadata.org, DataHub)</li></ul><p>Waii doesn’t just store this information — it intelligently links each concept to the right nodes in its knowledge graph, creating a rich understanding of your specific domain.</p><p>The agents used to build your SQL query dynamically retrieve these linked concepts and will prioritize them over automatically generated context — when you spend the time to model a concept, Waii makes sure to follow the instructions.</p><p>Need to refine something quickly? Simply add incidental documentation to clarify concepts, and watch as Waii’s understanding evolves.</p><h3>360° Evaluation: Testing for Excellence</h3><p>Great systems aren’t built in a vacuum — they’re tested rigorously. Waii has partnered with Microsoft to implement Archerfish (<a href="https://github.com/archerfish-bench">https://github.com/archerfish-bench</a>), an evaluation framework that measures performance based on user-intent metrics rather than just syntactic accuracy.</p><p>To ensure comprehensive coverage, Waii generates question/answer pairs that reflect real-world usage patterns:</p><ul><li>Automatically created from your environment</li><li>Informed by your query history</li><li>Based on existing dashboards and reports</li></ul><p>After review, these datasets train and tune the system, eliminating remaining shortcomings and ensuring balanced performance across all use cases.</p><h3>Continuous Learning: Getting Better Every Day</h3><p>The magic doesn’t stop at deployment. Waii’s live feedback system captures user interactions — correct responses, incorrect ones, and explicit feedback — to continuously refine its understanding.</p><p>Here’s how it works: When feedback is received, Waii evaluates it against its knowledge graph, identifying precisely what needs to be adjusted. Then, when a similar question arises, Waii applies these learnings as a delta to ensure the feedback is respected in future responses.</p><p>Importantly, you maintain full control over how this feedback propagates through your organization. Administrators can approve which feedback gets distributed to which users or teams, ensuring that improvements benefit the right people while maintaining consistent experiences across different groups when needed.</p><p>This creates a virtuous cycle where every interaction makes the system smarter — but in a controlled, intentional way that respects your organization’s structure and needs.</p><h3>Organization That Makes Sense</h3><p>How does Waii manage all this metadata? Through a sophisticated labeling system that tracks:</p><ul><li>Information origin</li><li>Date of changes</li><li>Modification history</li><li>Multi-tenant capabilities</li></ul><p>Different teams can maintain different understandings of concepts, and administrators can implement approval processes to control access and changes to semantic context.</p><h3>Version Control Ready: Store Your Metadata as Code</h3><p>Waii takes a modern approach to metadata management with its YAML-based language that allows you to store all semantic information as code in GitHub. This brings powerful advantages:</p><ul><li>Track changes with familiar git workflows</li><li>Review metadata modifications through pull requests</li><li>Roll back to previous versions when needed</li><li>Collaborate across teams with branching strategies</li><li>Automate deployment through CI/CD pipelines</li></ul><p>Here’s what this looks like in practice:</p><pre>tables:<br>  - name: orders<br>    description: &quot;Tracks all customer orders with payment and fulfillment status&quot;<br>    pk: order_id<br>    semantic_context:<br>      - &quot;Queries against this table need to filter by load_id for proper SCD&quot;<br>    joins:<br>      - description: customers<br>        type: N:1<br>        fk: customer_id<br>        constraints: &quot;orders.customer_id = customers.id&quot;<br>    columns:<br>      - name: product_category<br>        type: categorical<br>        vector_search: product_embeddings.vectors<br>    measures:<br>      - name: revenue<br>        expr: SUM(amount)<br>        description: &quot;Total order value in USD&quot;</pre><p>This code-first approach to metadata means your semantic layer becomes a first-class asset in your development ecosystem — versioned, reviewed, and deployed with the same rigor as your application code.</p><h3><strong>From Zero to Insights in Record Time</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ei-K74gUS10wJCRinCf3GQ.png" /></figure><p>The true testament to Waii’s approach isn’t just its technical sophistication — it’s the speed of implementation. Typically, customers go from nothing to a working system in less than 24 hours.</p><p>Although varying with business requirements and how much of a custom UX is desired, it’s common to start onboarding users just a couple of weeks after first setup.</p><p>This rapid time-to-value means you’re not waiting months for benefits. Your team can start asking questions in natural language and receiving accurate SQL fast, creating immediate impact while the system continues to refine its understanding through usage.</p><h3>The Result: A System That Speaks Your Language</h3><p>When these approaches combine, something magical happens: your analytics tool suddenly speaks your language. It understands your business definitions, recognizes your domain terminology, and translates natural language into precisely the SQL you would have written yourself.</p><h3>See It In Action</h3><p><strong>LookML Metric Definition:</strong></p><pre>measure: customer_retention_rate {<br>  type: number<br>  sql: ${retained_customers} / NULLIF(${previous_customers}, 0) ;;<br>  value_format_name: percent_2<br>  description: &quot;Percentage of customers who made a purchase in previous period and current period&quot;<br>}</pre><p><strong>User Question:</strong> “What was our customer retention for Q1 by region?”</p><p><strong>Waii Generated SQL:</strong></p><pre>SELECT<br>  region,<br>  COUNT(CASE WHEN purchased_prev_quarter = 1 AND purchased_this_quarter = 1 THEN customer_id END) /<br>  NULLIF(COUNT(CASE WHEN purchased_prev_quarter = 1 THEN customer_id END), 0) AS customer_retention_rate<br>FROM customer_activity<br>WHERE quarter = &#39;2025-Q1&#39;<br>GROUP BY region<br>ORDER BY customer_retention_rate DESC</pre><p><strong>User Feedback:</strong> “This is good but should include the rate and the actual counts”</p><p><strong>Waii Learning:</strong> <br>✓ Associate “customer retention” with the specific LookML measure <br>✓ Remember to include component counts when this user asks for rates</p><p><strong>Next Time Query:</strong></p><pre>SELECT<br>  region,<br>  COUNT(CASE WHEN purchased_prev_quarter = 1 THEN customer_id END) AS previous_customers,<br>  COUNT(CASE WHEN purchased_prev_quarter = 1 AND purchased_this_quarter = 1 THEN customer_id END) AS retained_customers,<br>  COUNT(CASE WHEN purchased_prev_quarter = 1 AND purchased_this_quarter = 1 THEN customer_id END) /<br>  NULLIF(COUNT(CASE WHEN purchased_prev_quarter = 1 THEN customer_id END), 0) AS customer_retention_rate<br>FROM customer_activity<br>WHERE quarter = &#39;2025-Q1&#39;<br>GROUP BY region<br>ORDER BY customer_retention_rate DESC</pre><p>In a world where data drives decisions, the ability to simply ask questions of your data — and get accurate answers immediately — isn’t just convenient. It’s transformative. And with Waii’s metadata-driven approach to text-to-SQL, that transformation is within reach.</p><p>Ready to experience the difference metadata makes? Discover how <a href="https://www.waii.ai/">Waii</a> can bring conversational analytics to your organization today.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f42aaba4e84" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/unlocking-the-power-of-text-to-sql-how-metadata-makes-the-difference-2f42aaba4e84">Unlocking the Power of Text-to-SQL: How Metadata Makes the Difference</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Complex SQL Joins with LangGraph and Waii]]></title>
            <link>https://blog.waii.ai/complex-sql-joins-with-langgraph-and-waii-9e3b093b2942?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/9e3b093b2942</guid>
            <category><![CDATA[langgraph]]></category>
            <category><![CDATA[conversational-bi]]></category>
            <category><![CDATA[llm-agent]]></category>
            <category><![CDATA[text-to-sql]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Fri, 11 Oct 2024 05:09:51 GMT</pubDate>
            <atom:updated>2025-03-04T23:04:14.739Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6FpPJy42rSn_Hy7SvAQ5jw.png" /></figure><p>In the rapidly evolving landscape of data analytics, the ability to interact with data through natural language is becoming increasingly valuable. Conversational analytics aims to make complex data structures more accessible to users without specialized technical skills.</p><p>LangGraph is a framework for building stateful, multi-agent applications using language models. <a href="https://www.waii.ai/">Waii</a> provides text-to-SQL and text-to-chart capabilities, enabling natural language interactions with databases and data visualization.</p><p>This post explores how <a href="https://www.waii.ai/">Waii’s</a> capabilities can enhance LangGraph applications for conversational analytics. We’ll focus on <a href="https://www.waii.ai/">Waii’s</a> approach to handling complex joins in databases, a critical aspect of generating accurate SQL from natural language queries.</p><h3>Waii’s Text-to-SQL Capabilities</h3><p>At the core of conversational analytics is the ability to translate natural language into database operations. <a href="http://www.waii.ai/">Waii</a> offers a comprehensive text-to-SQL solution that stands out in several key areas:</p><ul><li>High-accuracy joins across complex schemas</li><li>Scalable table selection for large databases</li><li>Bespoke compiler for syntactic correctness and query optimization</li><li>Specialized agentic flows for filters, sort order, common metrics, etc</li></ul><p>In the following section, we’ll take a deep dive into how <a href="https://www.waii.ai/">Waii</a> tackles complex joins. We focus on this because it’s a table-stakes capability for conversational analytics that many of today’s solutions struggle with. We will examine an example, see how the joins are constructed, and explain how you can easily integrate Waii into your existing LangGraph application to achieve these gains.</p><h3>Deep Dive: Join Handling</h3><h3>An example</h3><p>Imagine a streaming platform’s data team is tasked with creating a comprehensive director performance dashboard. They need to analyze what makes directors successful by combining data from movies, TV series, genres, keywords, awards, and actor collaborations.</p><h3>The Instructions</h3><p>Create a view that provides the following information for the top 5 directors (by the highest number of titles):</p><ul><li>Director’s name</li><li>Total number of titles</li><li>Most frequent genre</li><li>Most frequent keyword</li><li>Number of awards won</li><li>Total revenue from movies</li><li>List of actors they’ve worked with</li></ul><h3>The Query</h3><p>The full query generated by <a href="https://www.waii.ai/">Waii</a> from these instructions can be found in Appendix A. Here’s a small fragment, showing some of the joins:</p><pre>...<br>FROM ranked_directors AS rd<br>INNER JOIN movie_db.movies_and_tv.people AS p<br>    ON rd.people_id = p.people_id<br>LEFT JOIN combined_director_genres AS cdg<br>    ON rd.people_id = cdg.people_id AND cdg.genre_rank = 1<br>LEFT JOIN combined_director_keywords AS cdk<br>    ON rd.people_id = cdk.people_id AND cdk.keyword_rank = 1<br>LEFT JOIN director_awards AS da<br>    ON rd.people_id = da.people_id<br>LEFT JOIN director_revenue AS dr<br>    ON rd.people_id = dr.people_id<br>LEFT JOIN director_actors AS d_actors<br>    ON rd.people_id = d_actors.people_id<br>...</pre><h3>Query analysis</h3><p>This query showcases a number of complex join capabilities:</p><ol><li><strong>Complex Join Graph:</strong> 14 tables used in the query with different qualifiers, arity and semantics.</li><li><strong>Bridge Table Joins:</strong> Used to connect entities in many-to-many relationships (e.g., directors to movies, TV series, and actors).</li><li><strong>Dimension Table Joins:</strong> Utilized to enrich the data with descriptive information from genres and keywords tables.</li><li><strong>Complex Join Chains:</strong> Implemented to connect distant entities, such as linking directors to actors through their collaborative work.</li><li><strong>Full Outer Joins:</strong> Employed to combine a director’s work across both movies and TV series, ensuring comprehensive coverage.</li><li><strong>Left Joins for Optional Data:</strong> Applied when including data that might not exist for all directors (e.g., awards, revenue).</li></ol><p>(This list is not exhaustive, there are many other considerations for accurate join handling, such as: Dfference between on and where clause, join order, non-equi joins, lateral joins for semi-structured data, etc)</p><p>The key to Waii approach to understanding and representing database relationships. Here’s how it works:</p><h3>Knowledge Graph Construction</h3><p>Waii automatically builds a comprehensive knowledge graph of the database objects. This graph incorporates information from multiple sources:</p><ul><li>Schema information</li><li>Constraints (e.g., primary keys / foreign keys)</li><li>Predictions based on analyzing column names and data patterns</li><li>Join graphs extracted and ranked from query history</li><li>Database documentation</li><li>Relationships defined in data catalogs</li><li>Feedback from system usage over time</li></ul><p>This graph is continuously updated and refined. Every schema change, new query, and new piece of feedback is analyzed and integrated into the graph.</p><h3>Agentic Flows for Query Construction</h3><p>With the knowledge graph in place, Waii employs a sequence of agentic flows to construct the optimal query:</p><p><strong>1. Table Selection: </strong>Analyzing the user’s request to identify the most relevant tables. Common join relationships and understanding of the semantics of the relationships are used to find tables and paths that might not be directly semantically linked to the user input.</p><p><strong>2. Join Graph Analysis: </strong>Proposing and evaluating potential join paths between selected tables. This includes scoring how well the join graph is aligned with previously seen joins and the semantic understanding of the relationships.</p><p><strong>3. Evaluation/refinement of the join conditions: </strong>Separate check to make sure outer joins and join conditions are applied correctly. This is where we also look at “on” vs “where” clause conditions for outer joins.</p><p><strong>3. Query Construction:</strong> Building the SQL query based on the chosen join graph, and conditions.</p><p><strong>4. Compilation and Optimization:</strong> Ensuring the joins are syntactically correct and optimized for performance. We also enforce operational constraints placed by the user on the query (e.g., max output rows, max input partitions).</p><p>The result is a SQL query that not only answers the user’s question accurately but does so in a way that’s optimized for the specific database structure and query engine.</p><h3>Building a Conversational Analytics Application</h3><p>Now that we understand how <a href="https://www.waii.ai/">Waii</a> handles joins and text-to-SQL, let’s explore how we can leverage this capability in combination with LangGraph to build a sophisticated conversational analytics application.</p><p>LangGraph is the de facto framework for building agentic systems. For any LangGraph application needing precise, thoughtful database access, Waii is a great addition. Integrating Waii with LangGraph allows developers to create systems that execute complex queries while maintaining context across interactions, boosting the application’s overall intelligence.</p><h3>Implementation Details</h3><p>Implementing this system involves several key components:</p><p><strong>1. LangGraph Framework:</strong> Provides the overall structure for the multi-agent system, managing state and agent interactions.</p><p><strong>2. Waii API Integration:</strong> The SQL Generation and Visualization Agents would make calls to Waii’s APIs to leverage its text-to-SQL and text-to-chart capabilities.</p><p><strong>3. Natural Language Processing:</strong> For understanding user inputs and generating human-readable responses.</p><p><strong>4. Waii Execution API:</strong> To execute the generated SQL queries against the actual database. Code is injected to enforce user-level security policies such as limiting row / column access.</p><p><strong>5. State Management:</strong> To maintain context across multiple user interactions, allowing for follow-up questions and iterative analysis.</p><p>The flow of a typical interaction might look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1004/1*i0r1JDT9zfKC1BDxQVAs3A.png" /></figure><ol><li>User inputs a question.</li><li>An LangGraph Question Classifier decides if the request is best answered from memory or from database</li><li>[optional] The Waii SQL Generator creates an optimized SQL query.</li><li>[optional] The Waii SQL Executor injects security constraints, executes the query, and retrieves results.</li><li>[optional] A Result classifier decides if the output should be data or visualization.</li><li>[optional] The Waii Chart Generator creates relevant charts from the data and metadata.</li><li>A LangGraph Insight Generation Agent synthesizes the final results for the user</li><li>The loop repeats.</li></ol><p>(Optional / not shown in image: On error or exception, LangGraph loops, rewrites input and regenerates the required objects.)</p><p>Throughout this process, the Conversation Management Agent maintains state, allowing for contextual follow-up questions and a more natural, flowing interaction.</p><p>The full implementation of the example is given in Appendix B.</p><h3>Benefits and Use Cases</h3><p>This integration of LangGraph and Waii for database access offers several key benefits:</p><ol><li><strong>Accessibility:</strong> Complex data analysis becomes accessible to non-technical users through natural language interaction.</li><li><strong>Depth of Analysis:</strong> The system can handle intricate queries that would be challenging to formulate manually.</li><li><strong>Contextual Understanding:</strong> Maintained state allows for more natural, context-aware conversations about data.</li><li><strong>Visual Insights:</strong> Automatic generation of relevant visualizations enhances understanding of the data.</li><li><strong>Scalability:</strong> The system can adapt to large, complex databases without a proportional increase in complexity for the end-user.</li></ol><p>Potential use cases span various industries:</p><ul><li><strong>Business Intelligence:</strong> Executives can query complex business data without needing to learn SQL or BI tools.</li><li><strong>Healthcare:</strong> Researchers can explore large medical databases, correlating diverse factors in patient outcomes.</li><li><strong>Finance:</strong> Analysts can quickly investigate market trends and company performance across multiple dimensions.</li><li><strong>E-commerce:</strong> Marketing teams can analyze customer behavior patterns to inform strategy.</li><li><strong>Education:</strong> Administrators can gain insights into student performance and resource allocation.</li></ul><h3>Conclusion</h3><p>The combination of LangGraph’s multi-agent capabilities and <a href="https://www.waii.ai/">Waii’s</a> advanced text-to-SQL and visualization features opens up new opportunities in analytics and data processing. By making complex data analysis accessible through natural language, this approach dramatically lowers the barrier to high-quality insights from data.</p><p>We’d love to hear from you: How are you addressing these challenges today? What applications are you building with these capabilities?</p><h3>Appendix A: Query</h3><p>The full SQL query looks like this:</p><pre>WITH director_movie_count AS (<br>    SELECT<br>        mdb.people_id,<br>        COUNT(m.movie_id) AS movie_count<br>    FROM movie_db.movies_and_tv.movies_directors_bridge AS mdb<br>    INNER JOIN movie_db.movies_and_tv.movies AS m<br>        ON mdb.movie_id = m.movie_id<br>    GROUP BY<br>        mdb.people_id<br>),<br><br>director_tv_count AS (<br>    SELECT<br>        tsdb.people_id,<br>        COUNT(ts.tv_series_id) AS tv_count<br>    FROM movie_db.movies_and_tv.tv_series_directors_bridge AS tsdb<br>    INNER JOIN movie_db.movies_and_tv.tv_series AS ts<br>        ON tsdb.tv_series_id = ts.tv_series_id<br>    GROUP BY<br>        tsdb.people_id<br>),<br><br>combined_counts AS (<br>    SELECT<br>        COALESCE(dmc.people_id, dtc.people_id) AS people_id,<br>        COALESCE(dmc.movie_count, 0) + COALESCE(dtc.tv_count, 0) AS total_count<br>    FROM director_movie_count AS dmc<br>    FULL OUTER JOIN director_tv_count AS dtc<br>        ON dmc.people_id = dtc.people_id<br>),<br><br>ranked_directors AS (<br>    SELECT<br>        combined_counts.people_id,<br>        combined_counts.total_count,<br>        RANK() OVER (ORDER BY combined_counts.total_count DESC NULLS LAST) AS rank<br>    FROM combined_counts<br>),<br><br>director_genres AS (<br>    SELECT<br>        rd.people_id,<br>        g.name AS genre_name,<br>        COUNT(*) AS genre_count<br>    FROM ranked_directors AS rd<br>    LEFT JOIN movie_db.movies_and_tv.movies_directors_bridge AS mdb<br>        ON rd.people_id = mdb.people_id<br>    LEFT JOIN movie_db.movies_and_tv.movies_genres_bridge AS mgb<br>        ON mdb.movie_id = mgb.movie_id<br>    LEFT JOIN movie_db.movies_and_tv.genres AS g<br>        ON mgb.id = g.id<br>    GROUP BY<br>        rd.people_id,<br>        g.name<br>    UNION ALL<br>    SELECT<br>        rd.people_id,<br>        g.name AS genre_name,<br>        COUNT(*) AS genre_count<br>    FROM ranked_directors AS rd<br>    LEFT JOIN movie_db.movies_and_tv.tv_series_directors_bridge AS tsdb<br>        ON rd.people_id = tsdb.people_id<br>    LEFT JOIN movie_db.movies_and_tv.tv_series_genres_bridge AS tsgb<br>        ON tsdb.tv_series_id = tsgb.tv_series_id<br>    LEFT JOIN movie_db.movies_and_tv.genres AS g<br>        ON tsgb.id = g.id<br>    GROUP BY<br>        rd.people_id,<br>        g.name<br>),<br><br>combined_director_genres AS (<br>    SELECT<br>        director_genres.people_id,<br>        director_genres.genre_name,<br>        SUM(director_genres.genre_count) AS total_genre_count,<br>        RANK()<br>            OVER (PARTITION BY director_genres.people_id ORDER BY SUM(director_genres.genre_count) DESC NULLS LAST)<br>            AS genre_rank<br>    FROM director_genres<br>    GROUP BY<br>        director_genres.people_id,<br>        director_genres.genre_name<br>),<br><br>director_keywords AS (<br>    SELECT<br>        rd.people_id,<br>        k.name AS keyword_name,<br>        COUNT(*) AS keyword_count<br>    FROM ranked_directors AS rd<br>    LEFT JOIN movie_db.movies_and_tv.movies_directors_bridge AS mdb<br>        ON rd.people_id = mdb.people_id<br>    LEFT JOIN movie_db.movies_and_tv.movies_keywords_bridge AS mkb<br>        ON mdb.movie_id = mkb.movie_id<br>    LEFT JOIN movie_db.movies_and_tv.keywords AS k<br>        ON mkb.id = k.id<br>    GROUP BY<br>        rd.people_id,<br>        k.name<br>),<br><br>combined_director_keywords AS (<br>    SELECT<br>        director_keywords.people_id,<br>        director_keywords.keyword_name,<br>        SUM(director_keywords.keyword_count) AS total_keyword_count,<br>        RANK()<br>            OVER (<br>                PARTITION BY director_keywords.people_id ORDER BY SUM(director_keywords.keyword_count) DESC NULLS LAST<br>            )<br>            AS keyword_rank<br>    FROM director_keywords<br>    GROUP BY<br>        director_keywords.people_id,<br>        director_keywords.keyword_name<br>),<br><br>director_awards AS (<br>    SELECT<br>        pab.people_id,<br>        COUNT(*) AS award_count<br>    FROM movie_db.movies_and_tv.people_awards_bridge AS pab<br>    INNER JOIN movie_db.movies_and_tv.awards AS a<br>        ON pab.award_id = a.award_id<br>    WHERE<br>        a.iswinner = &#39;True&#39;<br>    GROUP BY<br>        pab.people_id<br>),<br><br>director_revenue AS (<br>    SELECT<br>        mdb.people_id,<br>        SUM(m.revenue) AS total_revenue<br>    FROM movie_db.movies_and_tv.movies_directors_bridge AS mdb<br>    INNER JOIN movie_db.movies_and_tv.movies AS m<br>        ON mdb.movie_id = m.movie_id<br>    GROUP BY<br>        mdb.people_id<br>),<br><br>director_actors AS (<br>    SELECT DISTINCT<br>        rd.people_id,<br>        p.name AS actor_name<br>    FROM ranked_directors AS rd<br>    LEFT JOIN movie_db.movies_and_tv.movies_directors_bridge AS mdb<br>        ON rd.people_id = mdb.people_id<br>    LEFT JOIN movie_db.movies_and_tv.movies_actors_bridge AS mab<br>        ON mdb.movie_id = mab.movie_id<br>    LEFT JOIN movie_db.movies_and_tv.people AS p<br>        ON mab.people_id = p.people_id<br>    UNION<br>    SELECT DISTINCT<br>        rd.people_id,<br>        p.name AS actor_name<br>    FROM ranked_directors AS rd<br>    LEFT JOIN movie_db.movies_and_tv.tv_series_directors_bridge AS tsdb<br>        ON rd.people_id = tsdb.people_id<br>    LEFT JOIN movie_db.movies_and_tv.tv_series_actors_bridge AS tsab<br>        ON tsdb.tv_series_id = tsab.tv_series_id<br>    LEFT JOIN movie_db.movies_and_tv.people AS p<br>        ON tsab.people_id = p.people_id<br>)<br><br>SELECT<br>    p.name,<br>    rd.total_count AS number_of_titles,<br>    ARRAY_AGG(DISTINCT cdg.genre_name) AS most_frequent_genres,<br>    ARRAY_AGG(DISTINCT cdk.keyword_name) AS most_frequent_keywords,<br>    COALESCE(da.award_count, 0) AS award_count,<br>    COALESCE(dr.total_revenue, 0) AS total_revenue,<br>    ARRAY_AGG(DISTINCT d_actors.actor_name) AS actors_worked_with<br>FROM ranked_directors AS rd<br>INNER JOIN movie_db.movies_and_tv.people AS p<br>    ON rd.people_id = p.people_id<br>LEFT JOIN combined_director_genres AS cdg<br>    ON rd.people_id = cdg.people_id AND cdg.genre_rank = 1<br>LEFT JOIN combined_director_keywords AS cdk<br>    ON rd.people_id = cdk.people_id AND cdk.keyword_rank = 1<br>LEFT JOIN director_awards AS da<br>    ON rd.people_id = da.people_id<br>LEFT JOIN director_revenue AS dr<br>    ON rd.people_id = dr.people_id<br>LEFT JOIN director_actors AS d_actors<br>    ON rd.people_id = d_actors.people_id<br>WHERE<br>    rd.rank &lt;= 5<br>GROUP BY<br>    p.name,<br>    rd.total_count,<br>    da.award_count,<br>    dr.total_revenue<br>ORDER BY<br>    rd.total_count DESC NULLS LAST,<br>    p.name ASC</pre><h3>Appendix B: LangGraph application</h3><p>Here’s the full LangGraph application (also on <a href="https://github.com/waii-abi/langgraph-waii">github</a>):</p><pre>import os<br>import sys<br>from typing import List, Optional, Dict, Any<br><br>import pandas as pd<br>import plotly<br>from pydantic import BaseModel<br>from langgraph.graph import StateGraph<br>from langchain_openai import ChatOpenAI<br>from langchain.prompts import ChatPromptTemplate<br>from langchain.schema import StrOutputParser<br>from waii_sdk_py import WAII<br>from waii_sdk_py.query import QueryGenerationRequest, RunQueryRequest<br><br>class State(BaseModel):<br>    database_description: str = &#39;&#39;<br>    query: str = &#39;&#39;<br>    sql: str = &#39;&#39;<br>    data: List[Dict[str, Any]] = []<br>    chart: Any = &#39;&#39;<br>    insight: str = &#39;&#39;<br>    response: str = &#39;&#39;<br>    error: Optional[str] = None<br>    path_decision: str = &quot;&quot;<br><br>class LanggraphWorkflowManager:<br><br>    def init_waii(self):<br>        WAII.initialize(url=os.getenv(&quot;WAII_URL&quot;), api_key=os.getenv(&quot;WAII_API_KEY&quot;))<br>        WAII.Database.activate_connection(os.getenv(&quot;DB_CONNECTION&quot;))<br><br>    def create_workflow(self) -&gt; StateGraph:<br>        workflow = StateGraph(State)<br><br>        workflow.add_node(&quot;Question Classifier&quot;, self.question_classifier)<br>        workflow.add_node(&quot;Result Classifier&quot;, self.result_classifier)<br>        workflow.add_node(&quot;SQL Generator&quot;, self.sql_generator)<br>        workflow.add_node(&quot;SQL Executor&quot;, self.sql_executor)<br>        workflow.add_node(&quot;Chart Generator&quot;, self.chart_gen)<br>        workflow.add_node(&quot;Insight Generator&quot;, self.insight_generator)<br>        workflow.add_node(&quot;Result Synthesizer&quot;, self.result_synthesizer)<br><br>        workflow.set_entry_point(&quot;Question Classifier&quot;)<br>        workflow.add_conditional_edges(<br>            &quot;Question Classifier&quot;,<br>            lambda state: state.path_decision,<br>            {<br>                &quot;database&quot;: &quot;SQL Generator&quot;,<br>                &quot;visualization&quot;: &quot;Chart Generator&quot;,<br>                &quot;general&quot;: &quot;Insight Generator&quot;<br>            }<br>        )<br><br>        workflow.add_edge(&quot;SQL Generator&quot;, &quot;SQL Executor&quot;)<br>        workflow.add_edge(&quot;SQL Executor&quot;, &quot;Result Classifier&quot;)<br>        workflow.add_conditional_edges(<br>            &quot;Result Classifier&quot;,<br>            lambda state: state.path_decision,<br>            {<br>                &quot;visualization&quot;: &quot;Chart Generator&quot;,<br>                &quot;data&quot;: &quot;Result Synthesizer&quot;<br>            }<br>        )<br>        workflow.add_edge(&quot;Chart Generator&quot;, &quot;Result Synthesizer&quot;)<br>        workflow.add_edge(&quot;Insight Generator&quot;, &quot;Result Synthesizer&quot;)<br>        workflow.add_edge(&quot;Result Synthesizer&quot;, &quot;Question Classifier&quot;)<br><br>        return workflow<br><br>    def question_classifier(self, state: State) -&gt; State:<br>        state.database_description = self.format_catalog_info(WAII.Database.get_catalogs())<br>        state.query = input(&quot;Question: &quot;)<br><br>        prompt = ChatPromptTemplate.from_messages([<br>            (&quot;human&quot;,<br>             &quot;Database info: \n---\n{database_description}\n---\n&quot;<br>             &quot;Answer &#39;database&#39; if this question is likely related to information in the database. Otherwise answer &#39;general&#39;? Question: &#39;{query}&#39;. &quot;<br>             &quot;Consider the information you have about the database, when in doubt answer &#39;database&#39;&quot;)<br>        ])<br>        chain = prompt | ChatOpenAI() | StrOutputParser()<br>        classification = chain.invoke({&quot;query&quot;: state.query, &quot;database_description&quot;: state.database_description}).strip().lower()<br>        return state.model_copy(update={&quot;path_decision&quot;: classification, &quot;error&quot;: None})<br><br>    def sql_generator(self, state: State) -&gt; State:<br>        sql = WAII.Query.generate(QueryGenerationRequest(ask=state.query)).query<br>        return state.model_copy(update={&quot;sql&quot;: sql, &quot;insight&quot;:&quot;&quot;})<br><br>    def sql_executor(self, state: State) -&gt; State:<br>        data = WAII.Query.run(RunQueryRequest(query=state.sql)).rows<br>        return state.model_copy(update={&quot;data&quot;: data}, deep=True)<br><br>    def chart_gen(self, state: State) -&gt; State:<br>        df_data = pd.DataFrame(state.data)<br>        chart = WAII.Chart.generate_chart(df=df_data)<br>        return state.model_copy(update={&quot;chart&quot;: chart.chart_spec, &quot;error&quot;: None}, deep=True)<br><br>    def result_classifier(self, state: State) -&gt; State:<br>        state.chart = &#39;&#39;<br>        prompt = ChatPromptTemplate.from_messages([<br>            (&quot;human&quot;,<br>             &quot;Is the following question best answered by &#39;data&#39; or a &#39;visualization&#39;? Question: &#39;{query}&#39;. &quot;<br>             &quot;Output: Strictly respond with either &#39;data&#39;, or &#39;visualization&#39;. No additional text.&quot;)<br>        ])<br>        chain = prompt | ChatOpenAI() | StrOutputParser()<br>        classification = chain.invoke({&quot;query&quot;: state.query}).strip().lower()<br>        return state.model_copy(update={&quot;path_decision&quot;: classification, &quot;error&quot;: None})<br><br>    def insight_generator(self, state: State) -&gt; dict:<br>        prompt = ChatPromptTemplate.from_messages([(&quot;human&quot;, &quot;{query}&quot;)])<br>        chain = prompt | ChatOpenAI() | StrOutputParser()<br>        insight = chain.invoke({&quot;query&quot;: state.query})<br>        return state.model_copy(update={&quot;insight&quot;: insight, &quot;sql&quot;: &quot;&quot;, &quot;data&quot;: [], &quot;error&quot;: None}, deep=True)<br><br>    def result_synthesizer(self, state: State) -&gt; State:<br>        model = ChatOpenAI()<br>        prompt = ChatPromptTemplate.from_messages([<br>            (&quot;system&quot;, &quot;You are an expert assistant in analyzing data&quot;),<br>            (&quot;human&quot;, &quot;\n User Question: &#39;{query}&#39;. &quot;<br>                             &quot;\n Results of query (if any): &#39;{data}&#39;.&quot;<br>                             &quot;\n LLM results (if any): &#39;{insight}&#39;.&quot;<br>                             &quot;\n\n Instructions: Answer the user with this information.&quot;)<br>        ])<br>        chain = prompt | model | StrOutputParser()<br>        data = &quot;\n&quot;.join(&quot; | &quot;.join(f&quot;{key}: {value}&quot; for key, value in row.items()) for row in state.data)<br>        output = chain.invoke({&quot;query&quot;: state.query, &quot;data&quot;: data, &quot;insight&quot;: state.insight}).strip().lower()<br>        if state.chart:<br>            df = pd.DataFrame(state.data)<br>            exec(state.chart.plot)<br>        print(&#39;Answer: &#39;+output)<br>        return state.model_copy(update={&quot;response&quot;: output}, deep=True)<br><br>    def __init__(self):<br>        self.workflow = self.create_workflow()<br>        self.app = self.workflow.compile()<br>        self.init_waii()<br>        print(self.app.get_graph().draw_ascii())<br><br>    def format_catalog_info(self, catalogs):<br>        return &quot;\n&quot;.join([<br>            f&quot;Database: {catalog.name}\n&quot; +<br>            &quot;\n&quot;.join([<br>                f&quot;  Schema: {schema.name.schema_name}\n    Description: {schema.description}&quot;<br>                for schema in catalog.schemas<br>            ]) + &quot;\n&quot;<br>            for catalog in catalogs.catalogs<br>        ])<br><br>    def run_workflow(self):<br>        while True:<br>            try:<br>                initial_state = State()<br>                app_response = self.app.invoke(initial_state)<br>            except Exception as e:<br>                print(f&quot;Error in workflow: {e}. Will restart.&quot;)<br><br>LanggraphWorkflowManager().run_workflow()</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9e3b093b2942" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/complex-sql-joins-with-langgraph-and-waii-9e3b093b2942">Complex SQL Joins with LangGraph and Waii</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tame High-Cardinality Categorical Data in Agentic SQL Generation with Vector DBs]]></title>
            <link>https://blog.waii.ai/tame-high-cardinality-categorical-data-in-agentic-sql-generation-with-vector-dbs-067b5f4f5eed?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/067b5f4f5eed</guid>
            <category><![CDATA[vector-search]]></category>
            <category><![CDATA[genai]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[text-to-sql]]></category>
            <category><![CDATA[vector-database]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Mon, 16 Sep 2024 20:06:05 GMT</pubDate>
            <atom:updated>2024-09-16T20:06:05.057Z</atom:updated>
            <content:encoded><![CDATA[<p>By <a href="https://zilliz.com/authors/Jiang_Chen">Jiang Chen</a> and <a href="https://www.linkedin.com/in/hagleitn/">Gunther Hagleitner</a></p><p>Agentic workflows and Large Language Models (LLMs) have fundamentally changed how we approach text-to-SQL systems, and are enabling users to query databases using natural language like never before. As these systems evolve, we encounter various novel challenges in translating human intent to accurate SQL queries.</p><p>One significant problem is handling high-cardinality categorical data. This article explores how integrating vector databases with agentic text-to-SQL systems can address this challenge. Specifically, we will show how the combination of <a href="http://www.waii.ai/">Waii</a> and <a href="https://zilliz.com/">Zilliz Cloud</a> (fully-managed <a href="https://milvus.io/">Milvus</a> vector database) automatically handles this issue.</p><h3>The Challenge of High-Cardinality Categorical Data</h3><p>Categorical data is ubiquitous in databases — think product categories, customer segments, or transaction types. In many cases, the number of unique values (cardinality) in these categories is manageable. For instance, a column for U.S. states will have at most 50 unique values (plus territories). These low-cardinality columns are relatively easy for text-to-SQL systems to handle, as the gap between natural language queries and database values is small.</p><p>The challenge arises when we encounter high-cardinality categorical data. Over 60% of databases we have worked with have examples of this situation: Imagine a product catalog with millions of unique product IDs, or a financial database with hundreds of thousands of company identifiers.</p><p>Databases require precise, rigid lookups for these categories, but natural language queries are often fluid and imprecise. A user might ask for “popular smartphones” or “tech companies that did well recently,” but translating these vague terms into specific database values is where traditional text-to-SQL approaches fall short, leaving a wealth of data out of reach.</p><h3>Why Existing Methods Don’t Work</h3><p>Often text-to-SQL systems employ one of two methods when translating natural language queries involving high-cardinality categorical data:</p><ol><li><strong>Preprocessed Database Techniques:</strong> This approach relies on traditional database capabilities like text search and regular expressions, combined with preprocessing of the input. While it can handle simple matches, it’s often too inflexible to accurately represent the user’s intent, especially with complex or nuanced queries. The rigid nature of these techniques struggles to bridge the gap between natural language variability and precise database values.</li><li><strong>LLM-Based Translation:</strong> In this method, the system uses an LLM to predict the correct categorical values based on training data or few-shot examples. While LLMs excel at understanding context and natural language, they often produce incorrect results when dealing with high-cardinality data. This is because the LLM lacks knowledge of the complete (and often private) dataset, and struggles with accurately recalling millions of unique identifiers that weren’t part of its training data.</li></ol><p>For high-cardinality data, both methods fall short. The preprocessed database techniques are too inflexible and literal to handle the nuances of natural language queries effectively. LLMs, on the other hand, may understand the query intent but can’t reliably map it to the correct values in a large, specific dataset they haven’t been trained on. This leaves us with a significant gap in translating natural language queries to accurate SQL for high-cardinality categorical data.</p><h3>Enter Vector Databases</h3><p>This is where vector databases come into play. Modern Information Retrieval uses vector embeddings to search by semantic, rather than key-word match Vector databases are designed to store and efficiently query high-dimensional vector representations of data. In our context, we can leverage them to create a bridge between natural language queries and high-cardinality categorical data.</p><p>Here’s how it works:</p><ol><li><strong>Create Embeddings:</strong> For each unique value in your high-cardinality column, create a vector embedding. This embedding captures the semantic meaning of the value by mapping it to a vector in a high-dimensional space.</li><li><strong>Index in Vector DB:</strong> Store these embeddings in a vector database, which is optimized for fast similarity searches in high-dimensional spaces.</li><li><strong>Query Processing:</strong> When a user submits a natural language query, use an LLM to understand the intent and generate a preliminary SQL query. For high-cardinality columns, instead of trying to generate specific values, the LLM generates a description or characteristics of the desired values.</li><li><strong>Vector Search: </strong>Use embedding of this description to perform a similarity search for the embedding of a massive amount of unique values in the vector database, retrieving the most relevant categorical values.</li><li><strong>Refinement:</strong> The LLM can then refine these results, filtering out any irrelevant matches based on the full context of the user’s query.</li><li><strong>SQL Generation:</strong> Generate the final SQL query by using the refined list of categorical values to build the appropriate filters and aggregates.</li></ol><h3>Benefits of This Approach</h3><p>This vector DB-enhanced approach offers several advantages:</p><ol><li><strong>Scalability:</strong> It can handle categories with millions of unique values without significant performance degradation.</li><li><strong>Accuracy:</strong> By combining the semantic understanding of LLMs with the precise recall of vector databases, we can more accurately translate user intent to specific database values.</li><li><strong>Flexibility:</strong> This method can adapt to changes in the database without requiring retraining of the entire system.</li></ol><h3>Implementation Considerations</h3><p>It is naturally possible to build the flows required to implement the described approach from scratch, but we believe that combining <a href="http://www.waii.ai/">Waii</a> and <a href="https://zilliz.com/">Zilliz Cloud</a> offers significant advantages.</p><p>Waii stands out as the world’s first text-to-SQL API built on agentic workflows. It combines compiler technology with an automatically generated knowledge graph for the most accurate query generation.</p><p>Complementing Waii’s capabilities, Zilliz Cloud is <em>the</em> vector database underpinning AI workflows. Its ability to scale, deliver accurate results and low latency make it the natural choice in the AI stack.</p><p>The combination gives you a scalable solution out of the box:</p><p><strong>Waii for Intelligent Text-to-SQL</strong></p><ul><li><strong>Automatic Detection:</strong> Waii automatically identifies these high-cardinality columns, eliminating the need for manual configuration.</li><li><strong>Smart Embedding Generation:</strong> It employs tailored workflows to build and update embeddings for different types of columns, optimizing for both accuracy and resource efficiency.</li><li><strong>Adaptive Techniques:</strong> Waii automatically selects appropriate techniques for various column types, ensuring optimal performance without requiring deep expertise from the user.</li></ul><p><strong>Zilliz Cloud for Vector Database</strong></p><ul><li><strong>Scalability:</strong> Zilliz Cloud can handle billions of vectors with ease, making it ideal for high-cardinality data scenarios.</li><li><strong>Lightning-Fast Queries:</strong> Its optimized indexing ensures rapid similarity searches, critical for maintaining low latency in production environments.</li><li><strong>Security and Access Control</strong>: Zilliz Cloud provides enterprise-grade data security and privacy compliance through secure networking options and encryption protocols. This ensures data safety during both transfer and when at rest. Additionally, Zilliz Cloud offers sophisticated identity control and access management, including Role-Based Access Control (RBAC) and OAuth 2.0 for secure, centralized Single Sign-On (SSO) capabilities.</li></ul><p><strong>Integration</strong></p><ul><li><strong>Seamless Workflow:</strong> The combination of Waii’s automatic preprocessing and Zilliz’s powerful vector storage creates a smooth, end-to-end solution for handling high-cardinality data in text-to-SQL systems.</li><li><strong>Production-Ready:</strong> This pairing is designed for real-world, large-scale challenges, making it suitable for production deployments across various industries.</li></ul><p>By leveraging Waii’s automatic detection and embedding generation with Zilliz you can implement a robust solution for handling high-cardinality categorical data in your text-to-SQL system, without custom code for metadata management or embedding generation.</p><h3>A Practical Example</h3><p>Let’s consider a global event management platform with millions of events. The database schema includes:</p><pre>CREATE TABLE events (<br>  event_id INT PRIMARY KEY,<br>  event_name VARCHAR(255),<br>  event_date DATE,<br>  category VARCHAR(100),<br>  total_spent DECIMAL(10, 2)<br>);</pre><p>The <em>event_name</em> is a high-cardinality categorical column, with millions of unique, descriptive names like “Global AI Ethics Summit 2024”, “Sustainable Living Expo: Greening Our Future”, or “5th Annual Quantum Computing Breakthrough Conference”.</p><p>An analyst might ask: “How much money was spent on AI events last month?”</p><p>Here’s how different approaches might handle this:</p><h4>Traditional Keyword Matching</h4><p>In this approach we use the term provided by the user and plug it into the query as a search term. We use case insensitive full text search here, but generating equality filters is also common in this scenario.</p><pre>SELECT SUM(total_spent)<br>FROM events<br>WHERE event_name ILIKE &#39;%AI%&#39;<br>  AND event_date &gt;=<br>    DATE_TRUNC(&#39;month&#39;, CURRENT_DATE - INTERVAL &#39;1 month&#39;)<br>  AND event_date &lt;<br>    DATE_TRUNC(&#39;month&#39;, CURRENT_DATE);</pre><p>Problem: This misses events like “Machine Learning Symposium” or “Neural Network Workshop” which are AI-related but don’t contain “AI” in the name. It might also incorrectly include events like “HAIR styling convention”.</p><h4>Naive LLM Approach</h4><p>In this approach the LLM generates multiple terms that might be relevant for search and constructs a filter out of it. This can optionally be done with sample values from the column and with additional context about the database. It would typically produce something like:</p><pre>SELECT SUM(total_spent)<br>FROM events<br>WHERE (event_name ILIKE &#39;%AI%&#39;<br>        OR event_name ILIKE &#39;%Artificial Intelligence%&#39;<br>        OR event_name ILIKE &#39;%Machine Learning%&#39;<br>        OR event_name ILIKE &#39;%Neural Network%&#39;)<br>    AND event_date &gt;=<br>        DATE_TRUNC(&#39;month&#39;, CURRENT_DATE - INTERVAL &#39;1 month&#39;)<br>    AND event_date &lt;<br>        DATE_TRUNC(&#39;month&#39;, CURRENT_DATE);</pre><p>Problem: While this catches more AI-related events, it’s still limited to a predefined list of terms and might miss events with more specific or novel AI-related names. The problem of finding unrelated events also still exists.</p><h4>Vector DB + LLM Approach</h4><p>The following diagram depicts the overall flow. The left side is the preprocessing that’s done on the events table, the right side shows the flow of generating the query.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3woMi8RhButhGqNf" /></figure><p>The query generation flow is:</p><p>a. Waii interprets the query and rewrites a description: “Events related to artificial intelligence, machine learning, neural networks, and other AI technologies.”</p><p>b. This description is used to query Zilliz Cloud, returning a list of relevant event IDs based on semantic similarity.</p><p>c. Waii refines this list, filtering out any non-AI events that might have been inadvertently included.</p><p>d. Waii uses an agentic workflow incorporates these event IDs into the final SQL query:</p><pre>SELECT SUM(total_spent)<br>FROM events<br>WHERE event_id IN (1234, 5678, 9101, 1121, 3141, 5161, 7181, 9202, 1222, 3242)<br>    AND event_date &gt;=<br>        DATE_TRUNC(&#39;month&#39;, CURRENT_DATE - INTERVAL &#39;1 month&#39;)<br>    AND event_date &lt;<br>        DATE_TRUNC(&#39;month&#39;, CURRENT_DATE);</pre><p>This approach can accurately identify AI-related events, even if they use specialized terminology or don’t contain obvious keywords. It can capture events like “3rd Symposium on Generative Adversarial Networks” or “Workshop on Ethical Considerations in Reinforcement Learning”, which the other approaches might miss.</p><p>By leveraging vector similarity search, we can more accurately interpret the user’s intent and match it to the high-cardinality event names in our database, providing more comprehensive and accurate results.</p><h3>Conclusion</h3><p>As data volumes continue to grow and user expectations for intuitive data interactions increase, handling high-cardinality categorical data in text-to-SQL systems will only become more commonplace. By leveraging the power of Zilliz Cloud in conjunction with Waii, we can create more robust, scalable, and accurate systems.</p><p>We’ve seen promising results with this approach, and we believe it is beneficial for others facing similar challenges. Please reach out and let us know if you have tried this or similar approaches and share your insights.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=067b5f4f5eed" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/tame-high-cardinality-categorical-data-in-agentic-sql-generation-with-vector-dbs-067b5f4f5eed">Tame High-Cardinality Categorical Data in Agentic SQL Generation with Vector DBs</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tell me Waii: Jupyter and gen AI]]></title>
            <link>https://blog.waii.ai/tell-me-waii-jupyter-and-gen-ai-e96d763cb0e8?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/e96d763cb0e8</guid>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Thu, 23 Nov 2023 00:50:09 GMT</pubDate>
            <atom:updated>2023-11-23T00:50:09.939Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Robot looking at a number of charts and visualizations." src="https://cdn-images-1.medium.com/max/1024/1*inKN7Vu4XGNckV-a-hilgA.png" /></figure><p>Well, the ‘Waii’ is clear actually. The ‘how’ is what I’m going to get into here.</p><p>Waii (<a href="http://www.waii.ai/">waii.ai</a>) is an easy to use text-to-SQL API. It solves some of the more challenging issues in the space: Large databases (100s of tables), achieving high accuracy and customization to your business context.</p><p>Let’s start with a problem statement: You have a warehouse that captures sales and customer data and you’re trying to find out which US state has the most customer growth.</p><p>Since you want to use Waii, there are a few lines of setup code you need.</p><p>(Side note: I’ve run this on <a href="https://colab.google/">colab.google</a>, but any Jupyter deployment will work.)</p><figure><img alt="Code that installs and initializes the waii sdk." src="https://cdn-images-1.medium.com/max/1024/1*NC9U_nTGzcnTp-lpS-rprw.png" /></figure><p>The first statement will install the sdk. The other statements will simply initialize the API. The key is stored using google’s user data api.</p><figure><img alt="Code to generate a query from instructions expressed in natural language." src="https://cdn-images-1.medium.com/max/1024/1*aOH5kJedHpce1RjZNX4zlQ.png" /></figure><p>This is really it. A single call to generate the query. And the data you want is stated as an instruction in plain English.</p><p>If you’re wondering, yes, this assumes that you have already connected Waii to your own warehouse. If you hadn’t, you could either do this thru another API call, or use the UI / CLI to do it. Everything else Waii does automatically behind the scenes.</p><p>The sql object that is returned has a wealth of information. Let’s take a look at the query first.</p><figure><img alt="SQL query corresponding to the natural language statement." src="https://cdn-images-1.medium.com/max/1024/1*NYiCB_fO2RldOGCh8N1scg.png" /></figure><p>The query is compiled, optimized, formatted, and ready to go. If you don’t want to read through all of it, here’s a step-by-step description of the query.</p><figure><img alt="Description of the query in plain english." src="https://cdn-images-1.medium.com/max/1024/1*9Y8nUYJWrqeLZaUZa9FKEw.png" /></figure><p>This sounds sensible and matches the above query nicely. Here’s another thing you can do: Get any semantic context that was considered when generating the query.</p><figure><img alt="Semantic context of the query." src="https://cdn-images-1.medium.com/max/1024/1*Hts_OHstjZGhCOegBmWWsQ.png" /></figure><p>There are multiple month related columns in the data set. Someone put additional information about D_MOY into the system. That helps the LLM pick the right column for the query. There’s also a rule about how Item revenue is defined. (Useful, but that’s not being applied in this query.)</p><p>You can also use Waii to run the query. Here’s how. The output is put into a pandas data frame for further exploration.</p><figure><img alt="Result set of the query. Shows states and percentages." src="https://cdn-images-1.medium.com/max/1024/1*94LzxjXpqsWR7EfkaNv5Lg.png" /></figure><p>Alright, that answers the original question and didn’t take too much effort. Let’s see if we can visualize this result and display the information on a US map.</p><figure><img alt="Code that asks for a plot of the data." src="https://cdn-images-1.medium.com/max/1024/1*Bws4uhZUgsZtQmEmMaHZSA.png" /></figure><figure><img alt="US map with the percent increase information visualized through a color scheme." src="https://cdn-images-1.medium.com/max/1024/1*ES86K_SKQqqOA_tAJdZttw.png" /></figure><p>Flashy, but there’s really no additional information that’s revealed by putting the data on a map. It doesn’t look like there is a broader geographic context behind the numbers. Maybe not the best visualization. Let’s have Waii choose the chart type.</p><figure><img alt="Code that asks for a chart of the data." src="https://cdn-images-1.medium.com/max/1024/1*d8JhkUSzX2hvRYZAu3ouMw.png" /></figure><figure><img alt="Bar chart representing the data." src="https://cdn-images-1.medium.com/max/1024/1*fwAJ-cjKYWMeT0gi-pzALg.png" /></figure><p>Much better. That gives a pretty clear indication of what the customer growth looks like!</p><p>And that’s it for a quick tour. There’s a lot more — you can get descriptions of the warehouse, tune the performance of the query, translate between database engines, ... But I hope this gives you a quick overview of what’s possible.</p><p>If you’re interested and want to try Waii for yourself: Request access at <a href="https://www.waii.ai/">waii.ai</a>. We’ll set up with an api key, playground, sample data and full docs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e96d763cb0e8" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/tell-me-waii-jupyter-and-gen-ai-e96d763cb0e8">Tell me Waii: Jupyter and gen AI</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Simplify Tableau data modeling with GPT-4 based SQL generation]]></title>
            <link>https://blog.waii.ai/simplify-tableau-data-modeling-with-gpt-4-based-sql-generation-ecb3cf6bfaa5?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/ecb3cf6bfaa5</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[artificalintelligence]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[tableau]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Tue, 09 May 2023 02:49:53 GMT</pubDate>
            <atom:updated>2023-11-22T20:08:04.430Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DVC6HC2Dp52zpg21TTlygQ.png" /></figure><p>In this blog we take a look at how to leverage new advances ushered in by LLMs and GPT-4 with Tableau. In particular we will show how you can simplify the modeling step and thus provide insights faster than ever before.</p><p>We will do this by using GPT-4 to generate SQL that can then be used as the base model to build visualizations on. Since GPT-4 by itself doesn’t know how to connect your warehouse information to the questions you’re asking, we use a tool we’ve built for this job. We call it, <strong><em>TweakIt</em></strong>.</p><p>We’ve looked in detail at the techniques required to get high-accuracy SQL-generation capabilities from LLMs. Those techniques are implemented by TweakIt, and are detailed <a href="https://medium.com/querymind/gpt-4s-sql-mastery-2cd1f3dea543">here</a> and <a href="https://medium.com/querymind/gpt-4-in-winter-sql-land-tackling-snowflake-schemas-f1fc4626262a">here</a>.</p><p>Let’s start with the problem statement. Let’s say you have sales and customer information about your US retail stores in a Snowflake database. A business analyst comes to you and says they would like to generate a heat map of which states are growing the fastest by sales — this heat map is needed tomorrow for an executive briefing.</p><p>After logging into TweakIt and connecting it to your Snowflake instance, you type the following question:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OokyZ1-VyGcc4ICHFMl92Q.png" /></figure><p>Note, there is no other modeling involved. No need to specify tables, joins, relationships, etc. Just point TweakIt at the database and ask the question.</p><p>It comes back with:</p><pre>WITH sales_per_year AS<br>  (SELECT s.S_STORE_NAME AS store_name,<br>          s.S_STATE AS state,<br>          d.D_YEAR AS YEAR,<br>          SUM(ss.SS_NET_PAID) AS total_sales<br>   FROM store_sales ss<br>   JOIN store s ON ss.SS_STORE_SK = s.S_STORE_SK<br>   JOIN date_dim d ON ss.SS_SOLD_DATE_SK = d.D_DATE_SK<br>   GROUP BY s.S_STORE_NAME,<br>            s.S_STATE,<br>            d.D_YEAR),<br>     sales_growth AS<br>  (SELECT store_name,<br>          state,<br>          YEAR,<br>          total_sales,<br>          (total_sales - LAG(total_sales)<br>                          OVER (PARTITION BY store_name, state <br>                                ORDER BY YEAR))<br>                       / NULLIF(LAG(total_sales)<br>                          OVER (PARTITION BY store_name, state <br>                                ORDER BY YEAR), 0)<br>  AS sales_growth<br>   FROM sales_per_year)<br>SELECT *<br>FROM sales_growth<br>ORDER BY store_name, state, YEAR;</pre><p>Looks reasonable, but how do you trust it? Let’s take a look at the explanation that was produced with it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*uFQxJYixQqaz0oD0eGesYQ.png" /></figure><p>That seems correct. Except it should not use NET PAID, but a different computation. You tweak the original question with:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XH3hoomY-7AgtvD6Jgy59w.png" /></figure><p>That will change the query to use the requested definition for total sales. After that you execute the query and take a look at the result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MiYpQFwRCcHaNyfj4eLgGw.png" /></figure><p>And that looks good as well. Here is what the whole page looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nE5jrSpwTjV0VpXySR2vag.png" /></figure><p>It’s time to take the query to the BI tool. In Tableau you create a new data source (or edit an existing one if you’re already connected to the database you want.)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3K7QD42gdxNiFaFAPYZS6w.png" /></figure><p>Then once you are connected you pick, “New Custom SQL”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/632/1*IyG09eXSoxvmcqorx9htOA.png" /></figure><p>And enter the query you have generated. Like so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K6TJglkne0loJBqTfeKfZA.png" /></figure><p>Once that is done you can use the generated model to build the visualization like you are used to. In this case we are filtering by year, using the state names as location information and encode the median of the year-over-year growth as the color of the heat-map. The result is this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eMQqp_15d1WxiL2Sjd6kcA.png" /></figure><p>Voila. Ship it.</p><p>To recap: Without knowing the database in any detail, we were able to build a model with confidence and in just a few seconds. From there we were able to use Tableau as to and deliver the requested visuals and insights. Life will never be the same.</p><p>If you’re interested in seeing this in action for yourself, please join our Slack channel: <a href="https://join.slack.com/t/waiicommunity/shared_invite/zt-1xib44mr5-LBa7ub9t_vGvo66QtbUUpg">Wai’i community</a>, or request access at <a href="https://www.waii.ai/">waii.ai</a></p><p><em>This post was written by </em><a href="https://medium.com/u/bb392ae11be4"><em>Wangda Tan</em></a><em> and </em><a href="https://medium.com/u/dc9bb5ebe29e"><em>Gunther Hagleitner</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ecb3cf6bfaa5" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/simplify-tableau-data-modeling-with-gpt-4-based-sql-generation-ecb3cf6bfaa5">Simplify Tableau data modeling with GPT-4 based SQL generation</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GPT-4 In Winter SQL-land: Tackling Snowflake Schemas!]]></title>
            <link>https://blog.waii.ai/gpt-4-in-winter-sql-land-tackling-snowflake-schemas-f1fc4626262a?source=rss-dc9bb5ebe29e------2</link>
            <guid isPermaLink="false">https://medium.com/p/f1fc4626262a</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[mls]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-warehousing]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <dc:creator><![CDATA[Gunther Hagleitner]]></dc:creator>
            <pubDate>Tue, 25 Apr 2023 02:05:03 GMT</pubDate>
            <atom:updated>2023-11-22T20:07:19.853Z</atom:updated>
            <content:encoded><![CDATA[<p>‘This query is so much better than anything I could have written’ — Something I won’t admit to myself.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rfvZSDda54kUxcUQ6906Tw.png" /></figure><p>In the <a href="https://medium.com/querymind/gpt-4s-sql-mastery-2cd1f3dea543">last blog</a> we concluded that the <a href="https://arxiv.org/abs/1809.08887">Spider benchmark</a> for natural language to SQL conversion is basically solved, but that there are still open questions. The most pressing ones for us were: (a) What about more realistic wider databases/tables, (b) what about more complex analytical queries, and (c) what about real-world datasets (vs. synthetic/public benchmark data)?</p><p>In this blog we look at (a) + (b). And we decided to call upon an old friend to do so: <a href="https://www.tpc.org/tpcds/">TPC-DS</a></p><h3>TPC-DS is a performance benchmark, mate.</h3><p>Well, yes. TPC-DS has been created to measure the performance of database systems on analytical workloads. It does so by defining a typical star/snowflake schema for a fictitious retailer.</p><p>There are fact tables such as store- or web sales, and dimension tables such as customer, or household_demographics. It defines a data generator to fill these tables with however many rows you like. Finally, it defines a set of 100 complex analytical queries to measure your warehouse performance.</p><p>So, how is this useful for benchmarking natural-language-to-SQL capabilities? In the addendum the benchmark defines the business questions that these 100 queries embody. This was probably provided to justify why these complex queries are useful benchmarks for a warehousing system.</p><p>We decided to flip this around: Use the business queries and see if we can generate the corresponding SQL. As a bonus we can compare the generated queries versus the benchmark queries on performance as well.</p><p>An example will help visualize. This is the business question that underlies query #11:</p><pre>Find customers whose increase in spending was larger on the web than <br>in stores for the year 2000, when compared to previous year.</pre><p>You can tell it was a simpler time when the benchmark was created. People were wondering if web is real and if it adds anything meaningful to brick-and-mortar stores. No spoilers, please.</p><p>However, the question templates are still relevant. These are similar questions to what a data scientist might have to answer to today: “Which customers are adopting our new sales channels the quickest?” is still relevant.</p><p>We’re using the same optimized combination of techniques as defined in the <a href="https://medium.com/querymind/gpt-4s-sql-mastery-2cd1f3dea543">last blog</a> (schema, compilation, chain-of-thought, multi-step, etc), the same backend that gave us the Spider results and we are using GPT-4 as the LLM for this evaluation. We’ll refer to this system as QueryMind (QM) going forward to simplify talking about it.</p><p>Here’s what QM will produce:</p><pre>WITH web_sales_filtered AS<br>  (SELECT ws.WS_BILL_CUSTOMER_SK,<br>          SUM(ws.WS_NET_PAID) AS total_spending,<br>          dd.D_YEAR<br>   FROM snowflake_sample_data.tpcds_sf10tcl.web_sales ws<br>   JOIN snowflake_sample_data.tpcds_sf10tcl.date_dim dd <br>          ON ws.WS_SOLD_DATE_SK = dd.D_DATE_SK<br>   WHERE dd.D_YEAR IN (1999, 2000)<br>   GROUP BY ws.WS_BILL_CUSTOMER_SK,<br>            dd.D_YEAR),<br>     store_sales_filtered AS<br>  (SELECT ss.SS_CUSTOMER_SK,<br>          SUM(ss.SS_NET_PAID) AS total_spending,<br>          dd.D_YEAR<br>   FROM snowflake_sample_data.tpcds_sf10tcl.store_sales ss<br>   JOIN snowflake_sample_data.tpcds_sf10tcl.date_dim dd <br>          ON ss.SS_SOLD_DATE_SK = dd.D_DATE_SK<br>   WHERE dd.D_YEAR IN (1999, 2000)<br>   GROUP BY ss.SS_CUSTOMER_SK,<br>            dd.D_YEAR),<br>     web_increase AS<br>  (SELECT ws1999.WS_BILL_CUSTOMER_SK,<br>          (ws2000.total_spending - ws1999.total_spending) AS increase<br>   FROM web_sales_filtered ws1999<br>   JOIN web_sales_filtered ws2000 <br>          ON ws1999.WS_BILL_CUSTOMER_SK = ws2000.WS_BILL_CUSTOMER_SK<br>   WHERE ws1999.D_YEAR = 1999<br>     AND ws2000.D_YEAR = 2000),<br>     store_increase AS<br>  (SELECT ss1999.SS_CUSTOMER_SK,<br>          (ss2000.total_spending - ss1999.total_spending) AS increase<br>   FROM store_sales_filtered ss1999<br>   JOIN store_sales_filtered ss2000 <br>          ON ss1999.SS_CUSTOMER_SK = ss2000.SS_CUSTOMER_SK<br>   WHERE ss1999.D_YEAR = 1999<br>     AND ss2000.D_YEAR = 2000)<br>SELECT c.C_CUSTOMER_ID,<br>       c.C_FIRST_NAME,<br>       c.C_LAST_NAME,<br>       wi.increase AS WEB_INCREASE,<br>       si.increase AS STORE_INCREASE<br>FROM snowflake_sample_data.tpcds_sf10tcl.customer c<br>JOIN web_increase wi ON c.C_CUSTOMER_SK = wi.WS_BILL_CUSTOMER_SK<br>JOIN store_increase si ON c.C_CUSTOMER_SK = si.SS_CUSTOMER_SK<br>WHERE wi.increase &gt; si.increase;</pre><p>It’s worthwhile pointing out that this is a remarkable result. The query is relatively complex and uses advanced concepts to get to the result. There are 6 joins, 2 group by’s and 4 common table expressions. Also QM made confident decisions on how to compute spending increases or how to represent a customer. The query definitely satisfies the business question, but does it produce the same output as the benchmark?</p><p>Well, no.</p><p>For a reason that data scientists are very familiar with: The benchmark assumes different definitions of certain concepts: Instead of adding up the NET_PAID amounts, the benchmark defines “spend” as “price minus discount”. There are almost always different ways to interpret things and making this consistent within an organization is hard. How this can be handled with LLMs is the topic of another blog. For now we just refine the result by adding these prompts to QM interactively (look at tweaks 2, 3 and 4):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/614/1*isyf_yhWYEgJ-eFgDXOjtw.png" /><figcaption>2–4 show additional inputs to adjust the business definitions</figcaption></figure><p>The result is a query, which does yield the same results as the benchmark defines! Here are the updates that QM made to the query in response to the additional information. We show it as inline differences to give a sense of how interactive feedback evolves the SQL:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OBUx2bzTmb74doLKr-SC1g.png" /></figure><h3>GPT-4 gets promoted to Principal SQL engineer</h3><p>We would also like to point out that the query produced here is much better than the one you will find for instance in the Snowflake workbook that comes with every Snowflake account (i.e.: the Snowflake TPC-DS query itself). It is easier to read and digest because of the way it is structured. You can top-down step-by-step follow thru with what is being achieved. Something like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/649/1*cOAUS5VVEfb190aWtYTEVQ.png" /></figure><p>Here are the two queries side-by-side. On the left is the QM-generated query, on the right is the TPC-DS query. See for yourself which one you would prefer to read or maintain (open the image in a new tab if you want to see the details).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mc5UITvELS0GxOEfVNCVcg.png" /><figcaption>Side-by-side: Left — Generated, Right — TPC-DS</figcaption></figure><p>(Snowflake — you know where to find us, if you want help cleaning this up.)</p><h3>The fine print</h3><p>Because of the different ways to interpret the queries and myriad ways of writing the queries themselves, it was necessary to manually inspect the results of each of these queries. We have put the queries and analysis <a href="https://gist.github.com/hagleitn/ba165129ed3ff30f34a006367bea0254">here</a> for folks that want to try it themselves.</p><p>We also only picked 10 queries at random from the 100 available queries. It’s a small sample, but gives an indication of what to expect.</p><p>Aaaand that’s not entirely true. We picked 9 queries at random. Query 27 was selected as the first query because of a long history we have with it (Shout-out: <a href="https://www.linkedin.com/in/gopal-v-76ab548/">Gopal</a>, <a href="https://www.linkedin.com/in/nita-dembla-833b132/">Nita</a>, <a href="https://www.linkedin.com/in/ashutoshchauhan/">Ashutosh</a>, <a href="https://www.linkedin.com/in/jesuscamachorodriguez/">Jesus</a>, <a href="https://www.linkedin.com/in/thejasnair/">Thejas</a> &amp; <a href="https://www.linkedin.com/in/acmurthy/">Arun</a>).</p><p>We captured the times it took for QM to generate the queries using GPT-4 and also the time it took to run the queries on a medium warehouse (with the 10TB TPC-DS dataset).</p><h3>The results</h3><p>Without further ado, here are the results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4bgUyBvhKS-4V1buVTJEiw.png" /><figcaption>Results of the experiment</figcaption></figure><p>Here’s what the columns mean in detail:</p><ul><li><strong>Succeeded w/o Human guidance:</strong> Means that QM generated a query that answers the business question. Without any additional human / user input besides the question.</li><li><strong>Human guidance required: No</strong> — means that the query produced the right output w/o any additional human/user input. <strong>No*</strong> means the same as <strong>No</strong>, but that additional input was required to achieve the same output as the TPCDS query. That’s due to the different possible interpretations of the question — like the examples given above for query 11. <strong>Yes</strong> — means that the query didn’t answer the business question and required additional input. <strong>Direct SQL edit</strong> means that we had to resort to actually editing the SQL by hand to “solve” the question.</li><li><strong>Query Generation Time: </strong>That’s the time QM took to generate the initial query in seconds.</li><li><strong>Execution Time Speedup Factor:</strong> That’s the ratio of the runtime of the TPC-DS query divided by the runtime of the query that QM produced.</li></ul><h3><strong>What does it all mean then?</strong></h3><p>The results are very different from what we’ve seen with the Spider benchmark. The queries are more complex and so is the data model. As a result, we were not able to achieve the same accuracy: We had to provide a lot more human input and in one case had to even resort to editing the SQL query by hand. Overall it seems this benchmark can still teach us something.</p><p>Advanced techniques like multi-step and query compilation were used much more frequently here, than with the simpler queries. Without them few (if any) queries would have succeeded.</p><p>Selecting the right information for the LLM is even more critical here than it was in the Spider benchmark. The complexity of the model can easily overwhelm the LLM.</p><p>Query complexity (i.e.: number of operations, especially joins, set operations, window functions, aggregates) is still the best indicator for success. Query 64 for instance is by far the most complex query — it requires 13 tables and a wide range of set operations / joins / etc. QM got close, but we couldn’t get the query quite right.</p><p>A very interesting result is that <strong>9/10 queries actually performed on</strong> <strong>par or faster</strong> than the expert-tuned TPC-DS queries. That’s important: We want non-experts to use these techniques to write queries w/o breaking the warehouse by writing inefficient queries. It’s also an interesting angle for query optimization that we will explore further in future.</p><p>In conclusion: Our techniques work really well in a highly complex environment. The potential time savings and efficiency gains that these methods can bring to data practitioners is enormous. Yet, the value can only be unlocked by a combination of the described (advanced) techniques and an appropriate user experience.</p><h3><strong>Future work</strong></h3><p>The next step for us is to move away from these synthetic workloads and to look how well this performs in the real world. We are in the process of testing on warehouses that have been used in anger for years to see if these findings will hold in those environments.</p><p>If you’re interested in seeing this in action for yourself, please join our Slack channel: <a href="https://join.slack.com/t/waiicommunity/shared_invite/zt-1xib44mr5-LBa7ub9t_vGvo66QtbUUpg">Wai’i community</a>, or request access at <a href="https://www.waii.ai/">waii.ai</a></p><p><em>This post was written by </em><a href="https://medium.com/u/bb392ae11be4"><em>Wangda Tan</em></a><em> and </em><a href="https://medium.com/u/dc9bb5ebe29e"><em>Gunther Hagleitner</em></a><em>.</em></p><h3>That’s all, folks!</h3><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f1fc4626262a" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/gpt-4-in-winter-sql-land-tackling-snowflake-schemas-f1fc4626262a">GPT-4 In Winter SQL-land: Tackling Snowflake Schemas!</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>