AI Data Analysis: Why Governed Metrics Beat Raw SQL Generation

A developer argues that AI data analysis tools like Julius AI, ChatGPT Data Analyst, and Databricks AI/BI fail to produce trustworthy, consistent results at scale because they lack a governed semantic layer. The post explains that without shared metric definitions, different tools generate conflicting numbers for the same question, such as 'revenue'. The solution proposed is a semantic layer that defines metrics once in a version-controlled system, ensuring accuracy, governance, and auditability across all AI queries.

AI data analysis tools are everywhere. Upload a spreadsheet to Julius AI and get a chart. Ask ChatGPT's Data Analyst to find trends. Connect Databricks AI/BI to your warehouse and let it generate queries. The pitch: anyone can analyze data without writing SQL. The pitch works for one-off exploration. It breaks when you need trustworthy numbers at scale: consistent results across tools, governed access per customer, and an audit trail for every answer. That's where most AI analytics tools stop and where a semantic layer starts. AI data analysis uses large language models and machine learning to automate parts of the data analysis workflow: generating queries, identifying patterns, creating visualizations, and summarizing findings in natural language. Instead of writing SQL by hand, you describe what you want and the AI produces it. The tools fall into three categories: Spreadsheet AI. Upload a CSV and ask questions. Julius AI, Formula Bot, ChatGPT Data Analyst. Good for ad-hoc exploration of small datasets. No governance, no multi-tenancy, no audit trail. Your data leaves your infrastructure. BI tool AI. Built into an existing BI platform. Tableau's AI analytics, Power BI Copilot, Domo AI, ThoughtSpot's natural language search. Better than spreadsheet tools because they query your warehouse. Still limited by the BI tool's ecosystem and licensing model. Text-to-SQL agents. An LLM generates SQL against your warehouse schema. DataGPT, custom GPT agents, Databricks AI/BI. Most flexible, most dangerous. The agent writes arbitrary SQL. No guardrails on what it queries or how it calculates metrics. All three share the same fundamental problem: the AI interprets your data on every query. There's no shared definition of what "revenue" means. Different tools, different phrasings, different numbers. AI data analysis demos are impressive. The tool generates a chart from a natural language question in seconds. But run the same question through two different tools and compare the numbers. They don't match. Why? Because each tool independently interprets your schema. "Revenue" might be SUM amount in one tool and SUM total price WHERE status = 'completed' in another. Both are plausible. Neither knows that your finance team excludes refunds and trial conversions. This isn't a model quality issue. GPT-4, Claude, and Gemini all exhibit this behavior. The model does what it's designed to do: generate plausible SQL from schema context. But "plausible" and "correct" are different things. The accuracy problem compounds at scale: Text-to-SQL https://bonnard.dev/glossary/text-to-sql gives you speed. It doesn't give you trust. For production analytics, you need both. The gap between a demo and a production system is five things. Most AI data analysis tools have one or two. None of the spreadsheet or text-to-SQL tools have all five. Every metric has one definition. "Revenue" is SUM amount WHERE status = 'refunded' AND type = 'trial' . This definition is fixed, versioned in Git, and referenced by every consumer. The AI doesn't interpret what revenue means. It queries the governed definition. cubes: - name: orders sql table: public.orders measures: - name: total revenue sql: "CASE WHEN status = 'refunded' AND type = 'trial' THEN amount ELSE 0 END" type: sum description: "Total revenue excluding refunds and trials" - name: order count type: count - name: average order value sql: amount type: avg dimensions: - name: status sql: status type: string - name: category sql: category type: string - name: created at sql: created at type: time This is a semantic layer https://bonnard.dev/glossary/semantic-layer : a metadata layer between your warehouse and every consumer. The AI agent calls explore schema to discover available metrics, then query to fetch governed data. It never generates SQL from scratch. The same metric definition serves dashboards, React components in your product, REST APIs, AI agents, and markdown reports. Ask for "revenue" from any surface and you get the same number. Not because the AI happened to generate the same SQL, but because the definition is fixed. This matters for AI specifically because AI tools generate different SQL on every call. Even the same tool, same question, different session can produce different queries. Governed definitions eliminate this variance. For B2B products, Customer A's AI agent should never see Customer B's data. This can't be a prompt instruction "only query data for customer A" . It needs to be structural: enforced on every query regardless of what the AI does. security context: - name: tenant filter sql: "{SECURITY CONTEXT.tenant id} = customer id" Every query through a tenant's key automatically includes this filter. The AI agent can't bypass it. See RBAC https://bonnard.dev/glossary/rbac for more on how this works. AI agents make more queries than humans. A human asks one question. An agent might make 10 queries to answer it: exploring the schema, trying different dimensions, following up on anomalies. Multiply by hundreds of customers' agents querying simultaneously. Pre-aggregation https://bonnard.dev/glossary/pre-aggregation caching pre-computes common rollups. Hot queries return in single-digit milliseconds. Without it, AI-scale query volumes overwhelm most warehouses. When the number is wrong, you need to trace it. With a semantic layer, every result traces back to a versioned metric definition in Git. You can point to the exact commit that defined the calculation, see when it changed, and who approved the change. With raw text-to-SQL, the trail is "an LLM generated some SQL" with no reproducibility. | Tool | Approach | Governed metrics | Multi-tenant | Audit trail | Best for | |---|---|---|---|---|---| Julius AI | Spreadsheet upload + LLM | No | No | No | Ad-hoc exploration of CSVs | ChatGPT Data Analyst | File upload + code interpreter | No | No | No | One-off analysis, prototyping | Databricks AI/BI | Text-to-SQL + Unity Catalog | Partial table-level | Partial | Partial | Teams already on Databricks | Power BI Copilot | Natural language in Power BI | DAX measures locked to PBI | Complex setup | Within PBI | Microsoft ecosystem | Tableau AI | Natural language in Tableau | Tableau semantic model | Enterprise | Within Tableau | Salesforce ecosystem | ThoughtSpot | Natural language BI | Proprietary semantic layer | Enterprise | Yes | Enterprise AI-powered BI | DataGPT | Conversational analytics agent | No | No | No | Autonomous data analysis | Domo AI | BI platform with AI features | Within Domo | Within Domo | Within Domo | End-to-end data platform | Charts in agent Bonnard | visualize tool on your MCP server | N/A your runSql | N/A your DB | N/A | Interactive charts inside Claude or ChatGPT | The tools in the top half are AI-first but governance-second. The ones in the middle enterprise BI have governance but lock it to their ecosystem. The bottom row is a different kind of option: @bonnard/mcp-charts does not generate SQL or define metrics. It renders interactive charts from the rows your runSql returns, inside the agent. Use spreadsheet AI Julius, ChatGPT when: Use BI tool AI Power BI Copilot, Tableau AI when: Use a semantic layer when: Governing the numbers is one half of AI data analysis. The other half is what the customer actually sees. When someone asks a question in Claude or ChatGPT, they often want a chart, not a table of rows. That's a separate surface from the governance layer, and it's where @bonnard/mcp-charts fits. @bonnard/mcp-charts is not a semantic layer, a BI tool, or a text-to-SQL agent. It is a visualize tool you add to your MCP server that renders interactive charts from your query results, inside the agent. js import { addCharts } from "@bonnard/mcp-charts"; addCharts server, { runSql: async sql = db.query sql , } ; addCharts registers a visualize tool and a visualize read me companion . The flow: visualize with a query. runSql returns rows. ui:// widget in Claude or ChatGPT.Chart types: line, bar, area, pie, scatter, funnel, waterfall, and table. Axes, formatting, and gap-fill are determined automatically, so the same typed result produces the same chart. Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql . Bonnard never touches your database. The tool is built for the agent: compact summaries, row caps with completeness flags, a typed schema, and instructive errors with a next step . For the design principles, see how Bonnard builds agent-friendly MCPs https://bonnard.dev/blog/how-bonnard-builds-agent-friendly-mcps . The first generation of AI analytics gave agents raw warehouse access and hoped for the best. It worked in demos. It failed in production because the agent interprets business logic on every query. The next generation separates the concerns. Data teams define governed metrics in a semantic layer https://bonnard.dev/glossary/semantic-layer . AI agents query those definitions instead of raw tables. The data team's expertise is encoded in the schema. The AI's role shifts from "guess what revenue means" to "pick the right governed metric and present the result." This is a better division of labor. The data team defines what's true. The AI handles the interface. Trustworthy numbers with a natural language frontend. If you want charts rendered inside an AI agent from your own query results, install the package and register the tool on your MCP server: npm install @bonnard/mcp-charts js import { addCharts } from "@bonnard/mcp-charts"; addCharts server, { runSql: async sql = db.query sql , } ; That registers the visualize tool and visualize read me . Connect your server in Claude or ChatGPT, and the agent can render charts from your query results. Native adapters cover Postgres, BigQuery, Snowflake, Databricks, and DuckDB, or pass your own runSql . Repo: github.com/bonnard-data/mcp-charts https://github.com/bonnard-data/mcp-charts . Full walkthrough: MCP charts https://bonnard.dev/blog/mcp-charts . For background: What Is a Semantic Layer? https://bonnard.dev/blog/what-is-a-semantic-layer . AI data analysis uses large language models and machine learning to automate data querying, pattern detection, and insight generation. Instead of writing SQL manually, you ask questions in natural language and the AI produces queries, charts, and summaries. Tools range from spreadsheet analyzers Julius AI to enterprise BI platforms with AI features Power BI Copilot, Tableau AI . For ad-hoc exploration, accuracy is "good enough." For production analytics where customers see the numbers, accuracy varies per query because the AI generates SQL from scratch each time. Two tools generating SQL for "revenue" may produce different numbers because they interpret the calculation differently. A semantic layer solves this by defining each metric once and letting AI query the definition instead of generating its own. It depends on the use case. For one-off CSV exploration: Julius AI or ChatGPT Data Analyst. For enterprise BI: Power BI Copilot or Tableau AI. For governed metrics served to AI agents and B2B customers: a semantic layer approach. See the comparison table above. For a SaaS product, two things matter: trustworthy numbers and where the customer sees the result. Govern the numbers with a semantic layer so the AI queries fixed definitions instead of guessing. Then decide the surface. If your customers work in Claude or ChatGPT, @bonnard/mcp-charts adds a visualize tool to your MCP server that renders interactive charts from your query results, inside the agent. Your runSql runs the query, so Bonnard never touches your database. Install with npm install @bonnard/mcp-charts . Traditional BI requires analysts to write queries and build dashboards. AI analytics automates the query generation. The risk: AI-generated queries are inconsistent. Traditional BI with a semantic layer gives you consistent definitions. AI analytics with a semantic layer gives you both: consistent definitions with a natural language interface. No. AI can generate queries and create visualizations faster than humans. It can't define what metrics should mean, decide which questions to ask, or judge whether an insight is actionable. Data analysts bring domain knowledge and judgment. AI handles the mechanical parts. A semantic layer encodes the analyst's expertise so AI can query it reliably. Text-to-SQL https://bonnard.dev/glossary/text-to-sql is a technique where an LLM generates SQL from natural language. AI analytics is broader: it includes text-to-SQL plus pattern detection, anomaly identification, forecasting, and visualization generation. Both share the same governance gap: the AI interprets your data on every query. A semantic layer addresses this for both. Most AI analytics tools work with common warehouses: Snowflake https://bonnard.dev/integrations/snowflake , BigQuery https://bonnard.dev/integrations/bigquery , Databricks https://bonnard.dev/integrations/databricks , PostgreSQL https://bonnard.dev/integrations/postgres including Supabase, Neon, and RDS , Redshift https://bonnard.dev/integrations/redshift , and DuckDB https://bonnard.dev/integrations/duckdb including MotherDuck . The semantic layer generates the appropriate SQL dialect for each warehouse.