I ran into this unexpectedly on a client engagement. We were helping their data analysts use LLMs to get better visibility into Databricks usage and costs. The goal was to speed up the analytics to spot inefficiencies: idle compute clusters burning budget, pipelines running for hours and wasting resources, and services nobody was actually using. Given a small analyst team and a wide adoption in the organisation with thousands of employees, we needed a reliable way to analyse records daily/weekly and act on them quickly. When I connected the LLM to the Databricks system tables, it failed immediately. The model invented tables that did not exist, guessed at column values, and produced wrong results. For a small team already stretched in its capacity, that is not a minor inconvenience but time wasted debugging AI output instead of delivering smarter with it.
The root cause was not a model limitation. The LLM simply did not know how the data was structured, so it tried to guess. In fact, I was surprised because the Databricks system table documentation is accessible online for everyone. Now, most probably you are an organisation with your own unique data, column conventions, or business logic. Every time the model guesses wrong, you pay for it: in analyst time, in misleading output your team might act on, and in compliance risk when an AI tool is reasoning about data it was never meant to see. Thus, the more complex and unique your data environment is, the harder it is for a general-purpose LLM model to navigate correctly.
The question was: what is the minimum structured context needed to fix this, and how should it be built? After reading this article, you will get all the answers.
This article has 2 parts: Part 1 explains how to do it and why to use the Context Layer; Part 2 shows the technical details and our methodology to make these results reproducible.
The diagram below shows what a full conversational analytics system looks like. A user asks a natural language question; the system generates a SQL query, runs it against your data, and returns a formatted answer. Follow-up questions deepen the analysis in a continuous loop.
Diagram 1: Conversational analytics architecture: text-to-SQL complete loop with context layer
Text-to-SQL sits at the heart of this loop. Get it right, and the rest of the system works. Get it wrong, and every answer the system produces is unreliable. Three reasons make it the right place to start if you need to improve your conversational analytics.
That last point is where most implementations break down, and that is exactly where the context layer comes in.
Before analysts could use a BI tool reliably, someone had to prepare the data behind it: document the tables, define the relationships, establish what each column means in business terms. That preparation is what made BI reliable. The same preparation is what makes LLM-generated SQL reliable.
The context layer is a structured package of knowledge about your data that the LLM receives alongside every question it is asked. We found out these 4 key steps:
Now, we can test whether this packaged context layer produces any value compared to the baseline situation without it.
We tested whether this context layer actually makes a difference by running the same three business questions through two versions of the same LLM:
Diagram 2: LLM without context(left) vs LLM with semantic layer for text-to-SQL that creates an enriched context layer.
To see the full produced code, see Part 2: Technical Deep Dive.
We took three questions that product owners typically ask about their Databricks account and ran those questions via two versions of the same LLM. Both ran against live Databricks system tables in Xomnia's own workspace, which meant results were verifiable against real data. We evaluated each output on three criteria: accuracy, hallucination rate, and actionability.
Business question: List all the workspaces that are currently running and have never been used
Prompt: Build a table that returns all the workspaces that are running and have never been used
Analysis: The image below demonstrates some of the fixes that an analyst has to make in the very first SQL CTE to fix the wrongly generated logic.
| Criteria | LLM without Context | LLM with Context |
|---|---|---|
| Accuracy | Critical failure Conflated event-based usage data with static workspace status. Even after manual fixes, identified only 30% of correct workspaces. | Success Correctly used DBU consumption to identify zero-cost workspaces. Ran with one minor adjustment. Also surfaced active clusters with no user activity, enabling additional analysis beyond the original question. |
| Hallucinations | High frequency Invented system.workflows (does not exist), columns workspace_status and created_on (do not exist), and filtered on "ENABLED" instead of the correct value "RUNNING". | Zero Correct schema names, column names, and accepted values throughout, without guessing. |
| Actionability | Negative ROI Required significant time to debug. Furthermore, the final output lacked the metadata needed to take any action. | High ROI Included workspace_name and direct URL for Databricks UI drill-down, plus a full activity breakdown across queries, clusters, and warehouses, enabling immediate investigation. |
Conclusion: LLM with Context completely outperformed the one without Context. The baseline output consumed analyst time and still returned wrong answers. The Context output saved time and delivered something immediately usable.
Winner: LLM with Context
Business Question: List the costs for each type of service used in Databricks across the entire organisation.
Prompt: Create a list that shows the costs for each type of product/service used in Databricks across the entire organisation. Some columns I want to see: cost, product_name, number of distinct users
Analysis:
| Criteria | LLM without Context | LLM with Context |
|---|---|---|
| Accuracy | Partial High-level summary was correct, but the user counting attempt failed: over-complicated the "User Count" by attempting to correlate services to users via unnecessary joins, rather than using the simple existing table. | Solid Correct service grouping and user counts. The solution was slightly over-engineered due to joining two tables where a single table would be enough. Crucially, the logic was valid, and the output was accurate. |
| Hallucinations | Logic hallucinations It didn't just invent a table, but it hallucinated a relationship (join) between tables that wasn't necessary. The model guessed the wrong data structure for counting users, leading to errors. It made a good attempt, given a limited understanding of the table columns and a good "rule of thumb," but it doesn't always work. | Zero The model stuck strictly to the available schema. Furthermore, the model read and found the current struct with all the user attributes, and it produced a correct count, unlike the LLM without context. |
| Actionability | Low Flawed user counts made the output unreliable for business decisions to be correctly made based on this output. | Medium Correct service classification made the output interpretable. Did not automatically distinguish between All-purpose, SQL, and Jobs compute sub-costs, but the data was accurate. |
Conclusion: The LLM with Context did a significantly better job of understanding the exact columns and fields across the tables, how to join them, and how to derive good, high-level metrics as requested. Furthermore, we see that context doesn't always equal "perfect insight." While the LLM with Context prevented errors and hallucinations (solving the accuracy problem), it didn't automatically make the results actionable.
Winner: LLM with Context
Business Question: Make a list of all the resources and their settings for auto- termination time. Furthermore, list the resources that have no auto-termination at all
Prompt: Make a list of all my resources and their settings for auto stop/auto termination, so I can prevent idle costs by looking over the settings. Finally, list only those resources that have no auto termination / have it set up for more than 45 minutes
Analysis:
| Criteria | LLM without Context | LLM with Context |
|---|---|---|
| Accuracy | Superficial High-level cluster classification was correct, but the model missed delete_time, treating already-deleted clusters as currently active and flagging them as risks. | Context-aware Correctly identified delete_time from the schema, distinguishing historical records from running resources. The output reflected the actual current state. |
| Hallucinations | Column hallucinations Tried to select start_date (does not exist); could not resolve between create_time and change_time, guessing incorrectly. | Zero Exact column names throughout. |
| Actionability | Misleading The results looked actionable: listed resources with risky settings, named owners, and provided termination times. However, those resources had already been deleted. An analyst acting on this would investigate clusters that no longer existed and would waste their time. | High Surfacing delete_time allowed immediate verification that the flagged resources were already gone, changing the entire interpretation of the output and preventing the waste of time for an analyst. |
Conclusion: This example shows the most dangerous failure mode: an output that looks correct but produces misleading results. The model without Context returned a correct list, but it had no way to know those entries were historical without schema context. By simply knowing the schema definition for deletion_time, the LLM with Context revealed that the "risky" clusters were already dead. One column in the documentation changed the entire output.
Winner: LLM with Context
The results across all three questions point to the same root cause for teams building conversational analytics: the bottleneck for LLM-based analytics is not model intelligence; it is data context. Modern LLMs can write excellent SQL. What they cannot do is infer your specific schema, understand your business naming conventions, or know which columns hold which values in your environment. A context layer addresses this directly.
The practical return is real. A model that produces runnable, accurate SQL from the first attempt saves analysts hours of debugging every week. It also eliminates a harder-to-catch class of errors: queries that execute but return wrong answers, or analyses that look actionable but send teams in the wrong direction, as we saw in questions 2 and 3. For organisations in regulated environments, there is a governance benefit too: with a context layer in place, the LLM's reasoning is traceable. You can show exactly what data it was given and why it answered the way it did.
The good news is that most teams are closer to a working context layer than they realise. If you already maintain any of the following, you have most of the raw material:
The additional work is structuring this for an LLM: YAML metadata files, column descriptions, relationship mapping. In a well-documented environment, it takes days, not months.
If your data already lives on Databricks or Snowflake, you may have a head start. Both platforms offer native conversational analytics tooling (Databricks Genie and Snowflake Cortex Analyst), giving your team a starting point rather than a blank page.
Data modeling for AI follows the same principle as data modeling for BI. The format is different, and the consumer is an LLM rather than an analyst, but the underlying logic is unchanged: give the tool a clean, documented, relationship-aware context of the data, and it will produce reliable output. Skip that step, and you will spend most of your time recovering from the consequences.
If you are currently building any kind of AI-powered analytics, the most useful question to ask is whether the LLM behind it has a trusted context layer or whether it is guessing at your schema from general knowledge. That question predicts the quality of your results more reliably than the model you chose or the platform you deployed it on.
This blog covered just the foundations of building the context layer. For more advanced teams and use cases, the following components will improve how well the LLM understands your data and your users' questions. We will cover these in depth in a follow-up article.
For data engineers, analytics engineers, or other readers who want to understand or replicate the experiment, below you can find everything you need to evaluate the approach and apply it to your own environment. If you are a business reader, above is everything you need.
We chose Databricks system tables as the data source for this experiment because they are standardised across organisations and the lineage documentation is publicly available. This makes the experiment reproducible: you can run the same queries against your own Databricks workspace and compare results.
Source: Databricks
The lineage graph shows relationships between system tables across compute, billing, access, and query schemas. This is the starting point for building the context layer: understanding which tables exist and how they connect before documenting them.
The three business questions were chosen to represent real analyst needs around Databricks cost management, each requiring different table combinations and join logic. Correct answers to all three are immediately actionable for a data platform team.
Preparing the recipe components for the LLM to build the right context layer
This article focuses primarily on steps 5-6: which is where we evaluate our context layer and show its benefits.
Diagram: visual summary of the value-creation end-to-end process.
For each of the three business questions, we structured the evaluation as follows.
Business question 1. The SQL result of the LLM with the recipe
The full SQL generated by the enriched LLM for "list all workspaces that are currently running and never used" is shown below. The model generated all CTEs correctly. One minor manual adjustment was made to the date range filter after initial review.
WITH workspace_activity AS ( -- Get all active workspaces SELECT DISTINCT w.workspace_id, w.workspace_name, w.workspace_url, w.status FROM system.access.workspaces_latest w WHERE w.status = 'RUNNING' ), -- Check for billing usage (any DBU consumption) workspace_billing AS ( SELECT DISTINCT workspace_id FROM system.billing.usage WHERE usage_start_time >= '2025-09-01' AND usage_start_time < '2025-10-01' ), -- Check for query activity workspace_queries AS ( SELECT DISTINCT workspace_id FROM system.query.history WHERE date(start_time) >= '2025-09-01' AND date(start_time) < '2025-10-01' ), -- Final query: workspaces with no activity final_cte AS ( SELECT wa.workspace_id, wa.workspace_name, wa.workspace_url, wa.status, ew.total_dbu_used, CASE WHEN wb.workspace_id IS NOT NULL THEN 'Has Billing' ELSE 'No Billing' END AS billing_status FROM workspace_activity wa LEFT JOIN workspace_billing wb ON wa.workspace_id = wb.workspace_id LEFT JOIN workspace_queries wq ON wa.workspace_id = wq.workspace_id WHERE wb.workspace_id IS NULL AND wq.workspace_id IS NULL ORDER BY wa.workspace_name )
Business question 2. The SQL result of the LLM with the recipe
The full SQL for "costs per service type across the organisation, including distinct user counts" is below. The model used both billing and query history tables for user identification, which was technically valid but slightly over-engineered. The cost calculations were correct.
WITH billing_with_pricing AS ( -- Join usage data with pricing to calculate costs SELECT u.billing_origin_product, u.sku_name, u.usage_quantity, u.usage_unit, u.usage_date, u.workspace_id, u.identity_metadata, u.usage_metadata, -- Calculate cost using effective list price u.usage_quantity * COALESCE( p.pricing.promotional.default, p.pricing.effective_list.default, p.pricing.default ) as calculated_cost FROM system.billing.usage u LEFT JOIN system.billing.list_prices p ON u.sku_name = p.sku_name AND u.cloud = p.cloud AND u.usage_date >= DATE(p.price_start_time) AND u.usage_date < COALESCE(DATE(p.price_end_time), '2099-12-31') WHERE u.usage_date >= '2025-09-01' AND u.usage_date <= '2025-10-31' AND u.record_type = 'ORIGINAL' ), user_identification AS ( -- Extract user identities from various sources SELECT DISTINCT billing_origin_product, sku_name, workspace_id, usage_date, calculated_cost, COALESCE( identity_metadata.run_as, identity_metadata.owned_by, identity_metadata.created_by ) as user_identity FROM billing_with_pricing WHERE COALESCE( identity_metadata.run_as, identity_metadata.owned_by, identity_metadata.created_by ) IS NOT NULL ), -- Get additional users from query history for SQL-related usage query_users AS ( SELECT DISTINCT 'SQL' as billing_origin_product, DATE(qh.start_time) as usage_date, qh.workspace_id, qh.executed_by as user_identity FROM system.query.history qh WHERE DATE(qh.start_time) >= '2025-09-01' AND DATE(qh.start_time) <= '2025-10-31' AND qh.executed_by IS NOT NULL ), -- Get users from audit logs for broader coverage audit_users AS ( SELECT DISTINCT CASE WHEN a.service_name IN ('jobs', 'jobsService') THEN 'JOBS' WHEN a.service_name IN ('sql', 'sqlAnalytics') THEN 'SQL' WHEN a.service_name IN ('notebook', 'notebooks') THEN 'INTERACTIVE' WHEN a.service_name IN ('mlflow', 'modelServing') THEN 'MODEL_SERVING' WHEN a.service_name IN ('unityCatalog', 'catalog') THEN 'MANAGED_STORAGE' ELSE 'OTHER' END as billing_origin_product, a.event_date as usage_date, a.workspace_id, COALESCE( a.user_identity.email, a.identity_metadata.run_as, a.identity_metadata.run_by ) as user_identity FROM system.access.audit a WHERE a.event_date >= '2025-09-01' AND a.event_date <= '2025-10-31' AND COALESCE( a.user_identity.email, a.identity_metadata.run_as, a.identity_metadata.run_by ) IS NOT NULL ), -- Combine all user sources all_users AS ( SELECT billing_origin_product, usage_date, workspace_id, user_identity FROM user_identification UNION ALL SELECT billing_origin_product, usage_date, workspace_id, user_identity FROM query_users UNION ALL SELECT billing_origin_product, usage_date, workspace_id, user_identity FROM audit_users ), -- Aggregate costs by product product_costs AS ( SELECT billing_origin_product, SUM(calculated_cost) as total_cost FROM billing_with_pricing GROUP BY billing_origin_product ), -- Count distinct users by product product_users AS ( SELECT billing_origin_product, COUNT(DISTINCT user_identity) as distinct_users FROM all_users GROUP BY billing_origin_product ) -- Final result combining costs and user counts SELECT COALESCE(pc.billing_origin_product, pu.billing_origin_product) as product_name, ROUND(COALESCE(pc.total_cost, 0), 2) as cost, COALESCE(pu.distinct_users, 0) as number_of_distinct_users, CASE WHEN COALESCE(pu.distinct_users, 0) > 0 THEN ROUND(COALESCE(pc.total_cost, 0) / pu.distinct_users, 2) ELSE 0 END as cost_per_user, CASE WHEN COALESCE(pc.billing_origin_product, pu.billing_origin_product) IN ('JOBS', 'DLT') THEN 'Data Processing' WHEN COALESCE(pc.billing_origin_product, pu.billing_origin_product) IN ('SQL', 'INTERACTIVE', 'ALL_PURPOSE') THEN 'Analytics & Development' WHEN COALESCE(pc.billing_origin_product, pu.billing_origin_product) IN ('MODEL_SERVING', 'VECTOR_SEARCH', 'LAKEHOUSE_MONITORING') THEN 'ML & AI Services' WHEN COALESCE(pc.billing_origin_product, pu.billing_origin_product) IN ('MANAGED_STORAGE', 'FINE_GRAINED_ACCESS_CONTROL') THEN 'Data Management' WHEN COALESCE(pc.billing_origin_product, pu.billing_origin_product) IN ('NETWORKING', 'SHARED_SERVERLESS_COMPUTE') THEN 'Infrastructure' WHEN COALESCE(pc.billing_origin_product, pu.billing_origin_product) IN ('APPS', 'BASE_ENVIRONMENTS') THEN 'Application Platform' ELSE 'Other Services' END as product_category FROM product_costs pc FULL OUTER JOIN product_users pu ON pc.billing_origin_product = pu.billing_origin_product ORDER BY cost DESC, number_of_distinct_users DESC;
The full SQL for "all compute resources with their auto-termination settings, filtering for those with no auto-stop or settings above 45 minutes" is below. The key contribution of The Context here was the inclusion of delete_time, which changed the actionability of the entire output.
Business question 3. The SQL result of the LLM with the recipe
-- Complete inventory of all Databricks compute resources with auto-termination settings -- Covers SQL Warehouses and Clusters for September-October 2025 -- Improved version using CTEs and better SQL practices WITH date_filter AS ( SELECT DATE('2025-09-01') as start_date, DATE('2025-10-30') as end_date ), warehouse_settings AS ( SELECT 'SQL_WAREHOUSE' as resource_type, warehouse_id as resource_id, warehouse_name as resource_name, workspace_id, warehouse_type as resource_subtype, warehouse_size as size_configuration, auto_stop_minutes, CASE WHEN auto_stop_minutes IS NULL THEN 'NO_AUTO_STOP' WHEN auto_stop_minutes > 45 THEN 'LONG_AUTO_STOP' ELSE 'ACCEPTABLE_AUTO_STOP' END as auto_stop_status, CONCAT('Min: ', COALESCE(min_clusters, 0), ', Max: ', COALESCE(max_clusters, 0)) as scaling_config, 'System' as owner, change_time as last_modified, delete_time FROM system.compute.warehouses w CROSS JOIN date_filter df WHERE w.change_time >= df.start_date AND (w.delete_time IS NULL OR w.delete_time >= df.start_date) ), cluster_settings AS ( SELECT CASE WHEN cluster_source = 'JOB' THEN 'JOB_CLUSTER' WHEN cluster_source IN ('PIPELINE', 'PIPELINE_MAINTENANCE') THEN 'PIPELINE_CLUSTER' ELSE 'ALL_PURPOSE_CLUSTER' END as resource_type, cluster_id as resource_id, COALESCE(cluster_name, 'Unnamed Cluster') as resource_name, workspace_id, cluster_source as resource_subtype, CONCAT( COALESCE(driver_node_type, 'Unknown'), ' (driver), ', COALESCE(worker_node_type, 'Unknown'), ' (worker)' ) as size_configuration, auto_termination_minutes as auto_stop_minutes, CASE WHEN auto_termination_minutes IS NULL THEN 'NO_AUTO_TERMINATION' WHEN auto_termination_minutes > 45 THEN 'LONG_AUTO_TERMINATION' ELSE 'ACCEPTABLE_AUTO_TERMINATION' END as auto_stop_status, CASE WHEN worker_count IS NOT NULL THEN CONCAT('Fixed: ', worker_count, ' workers') WHEN min_autoscale_workers IS NOT NULL AND max_autoscale_workers IS NOT NULL THEN CONCAT('Auto: ', min_autoscale_workers, '-', max_autoscale_workers, ' workers') ELSE 'Single node' END as scaling_config, COALESCE(owned_by, 'Unknown') as owner, change_time as last_modified, delete_time FROM system.compute.clusters c CROSS JOIN date_filter df WHERE c.change_time >= df.start_date AND (c.delete_time IS NULL OR c.delete_time >= df.start_date) ), combined_resources AS ( SELECT * FROM warehouse_settings UNION ALL SELECT * FROM cluster_settings ) SELECT resource_type, resource_id, resource_name, workspace_id, resource_subtype, size_configuration, auto_stop_minutes, auto_stop_status, scaling_config, owner, last_modified, delete_time, -- Additional computed fields for analysis CASE WHEN delete_time IS NOT NULL THEN 'DELETED' WHEN auto_stop_minutes IS NULL THEN 'NEEDS_CONFIGURATION' WHEN auto_stop_minutes > 45 THEN 'NEEDS_OPTIMIZATION' ELSE 'PROPERLY_CONFIGURED' END as configuration_status FROM combined_resources ORDER BY resource_type, workspace_id, CASE WHEN auto_stop_minutes IS NULL THEN 0 ELSE auto_stop_minutes END DESC, resource_name;
System prompt for all 3 Business questions, used for both the LLM with and without the recipe
Goal: Shape analysis of the usage of Databricks internally
How: produce analytics queries in SQL for the data platform observability team
Context: Generate queries that can be used by the team to better understand the usage of Databricks internally
Source: official databricks documentation page of system tables: https://docs.databricks.com/aws/en/admin/system-tables/ *
Data source: system tables in databricks
Data timeline: September 2025
---
META DESCRIPTION (155 chars):
We ran the same text-to-SQL queries through an LLM with and without a semantic layer on Databricks system tables. Here is what happened and what it means for your analytics team.
PULL QUOTE:
"Context does not guarantee perfect analytical depth. It prevents errors. And you cannot iterate on a query that will not run."
Analytics Engineer at Xomnia.
AI analytics runs on the same principle that made BI reliable: give the tool the right context about your data.
The gap between a compelling GenAI demo and a working production system is wider than most organisations expect. And the reason is almost never the technology.
So you centralise. The right call. One team manages agentic AI. They create rules, stop...
This blog is about getting full control of your data. And it is geared towards data platforms. However, a lot of the more generic remarks will hold up for other types of system environments also.
