Building Data Agents on Databricks: From Generated SQL to Governed Tools

A few months ago, I was building a data investigation agent. The architecture felt straightforward: build an internal application, connect it to an LLM, and give it access to Databricks through a service principal.

A user would ask a question, the agent would generate SQL, execute it, and explain the result. I used Langfuse for observability and built the surrounding agent harness to manage the flow.

Take a question like:

What were my sales for January 2026?

It sounds simple. But before the agent could answer, it had to figure out where sales data lived, which columns to use, how the tables related to each other, and what “sales” meant in that dataset.

Was it gross sales or net sales? Should cancelled orders count? Which date represented the sale?

Some of those were business questions. Others were schema questions. My implementation was making the model resolve both while writing SQL.

I frequently ran into hallucinated column names. The generated query looked reasonable, but referenced a field that didn’t exist. To address that, I started supplying more schema information, either hardcoding it into the application or passing it along whenever the agent generated a query.

That helped, but it also created more work: maintaining schema context, selecting relevant tables, handling failed queries, and checking whether a query that ran successfully actually answered the question.

I was spending more time helping the agent find the right place to look than helping it investigate the data.

There was also an access question. Queries ran with the service principal’s permissions. Unless I deliberately implemented user-specific authorization, the person asking the question and the identity reading the data were different.

Unity Catalog could still govern that service principal’s access. But the application needed to account for what that meant for everyone using the agent.

Recently, while exploring agent governance on Databricks, I came across a pattern that made me reconsider this design.

Instead of giving the agent a general SQL execution capability, expose a small set of predefined analytical functions through Unity Catalog.

The model chooses which function to call. The function contains the query logic.

Databricks supports using Unity Catalog functions as agent tools, including predefined SQL logic with parameters supplied by the agent. That creates an opportunity to separate the model’s reasoning from the details of querying the data.

For the sales example, I could expose a function such as get_sales_summary(start_date, end_date). This is an illustrative extension, rather than something implemented in the repository.

The function would define the source tables, joins, date field, and treatment of cancelled orders. The agent would supply the dates and interpret the result.

I would still need to define what sales means. But I could define it in reviewed, reusable code instead of expecting the model to reconstruct it for each question.

The repository demonstrates this approach with HR analytics. It creates a view called data_analyst_view over employee records, compensation, and performance reviews. The view leaves out names, email addresses, phone numbers, and SSNs, substitutes formatted employee identifiers, and excludes the Legal department.

Two Unity Catalog functions query that view:

Tool	What it returns
analyze_performance()	Department-level ratings, employee counts, and average tenure
analyze_operations()	Department-level compensation averages and employee counts

The agent receives these two tools through UCFunctionToolkit. It selects a tool, the application executes the registered function, and the result goes back to the model for interpretation.

There is no general-purpose SQL execution tool exposed to the model in this implementation.

The arrows show the query path. Tool results return to the agent, which uses them to compose its answer.

What I find useful here is that the model no longer needs to know the underlying column names to answer a supported question. It needs to understand the tools and their outputs.

The schema knowledge still exists, but it lives in the view and function definitions. If a source column changes, those definitions may need updating. The model doesn’t need to relearn the join.

The governance controls also become more concrete. The view defines which data is exposed. The functions define which calculations are available. Grants control access to those objects. The notebook separately applies a column mask to SSNs in the employee table.

A prompt saying “do not reveal employee information” can help guide the agent’s response. The stronger boundary is preventing the tool from returning that information in the first place.

There are a few details here that are easy to overstate.

The classification tags in the notebook are metadata. Labelling a table “Restricted” does not, by itself, restrict access. The view uses explicit SQL to select and filter data; it does not dynamically derive its behaviour from those tags.

Likewise, replacing an employee ID with something like EMP_000001 is not strong anonymization. It is a predictable identifier, and the view still contains employee-level attributes. The aggregate tool outputs reduce what reaches the model, but small departments could still create disclosure risks.

Those distinctions matter if this pattern is going to move beyond a demonstration.

The identity question also remains.

Using Unity Catalog tools does not automatically make an agent act with the permissions of the person chatting with it.

The deployment example uses a service principal workflow and declares the agent’s required resources. It does not demonstrate passing each end user’s identity through to every data access.

Databricks distinguishes between application authorization and user authorization. Application authorization uses a service principal; user authorization acts as the requesting user. The appropriate configuration depends on the deployment and the access model you need.

For a shared analytics assistant, a carefully restricted service identity might be appropriate. For an assistant where different managers should see different employees, user-specific authorization becomes part of the architecture.

Moving the agent into Databricks does not settle that decision for you.

The implementation also uses MLflow to trace model and tool calls, evaluate responses, and register the agent before deployment. That gives a useful way to inspect how the agent arrived at an answer.

But tracing and governance address different questions. A trace helps explain what happened. Access controls determine what was allowed to happen.

There are trade-offs with predefined tools, too.

The most obvious is flexibility. These two functions can answer questions about departmental performance and compensation. They cannot support every HR investigation.

For example, the deployment notebook includes the question:

Are we retaining top performers long-term?

The available tools return ratings, tenure, and compensation summaries. They do not provide the turnover history or cohort analysis needed to establish retention. A good answer should acknowledge that limitation rather than turn average tenure into a retention claim.

That is an important reminder: removing SQL generation does not remove hallucination. The model can still choose the wrong tool or draw a conclusion the returned data does not support.

The SQL itself also needs review. In this example, compensation records and performance reviews are joined through employee IDs. If both contain multiple records per employee, the join can multiply rows and distort averages. Putting that logic inside a governed function makes it easier to inspect and maintain, but does not make it correct automatically.

There is an ongoing maintenance cost as well. Someone needs to own the functions, document their meaning, test their calculations, and extend them when new questions arise. The example’s broad development permissions would also need to be separated from the narrower permissions required by a production agent.

For a bounded set of recurring business questions, I find that trade-off appealing. The agent gets a smaller, clearer set of capabilities, and the data logic becomes something an engineering team can review directly.

For open-ended investigation, I would still consider SQL generation. It offers flexibility that a small tool catalogue cannot match. But I would make that an explicit design choice, with appropriate schema context, validation, and access boundaries.

What changed for me was the starting question.

Earlier, I started with: “How do I help the model write the right SQL?”

Now, I would first ask: “Which analytical operations should this agent be able to perform, and under whose permissions?”

That gives me something concrete to design before I write the prompt.

The HR example discussed here comes from the agent-governance repository. It illustrates the architecture; it is not evidence of a production deployment or a complete security assessment.