The demo was flawless. Someone asked the agent about the portfolio’s default rate for the quarter and it answered in three seconds, with a chart. Weeks later, in production, a manager asked the same question and got two things nobody had planned for: a number that differed from the one on the committee dashboard, and an excerpt from a credit assessment he was not cleared to read. Nobody broke in. The agent did exactly what it was built to do — look for the answer in the vector index someone had synced from the tables, because that was the fastest way to stand up the pilot. The index is a copy. And a copy inherits no permissions.

The two routes An agent that answers questions about data has two possible routes, and they do not compete: they serve different questions. The unstructured route — vector search, what almost everyone calls RAG — answers “what does this document say”. The structured route — query generation, the familiar text-to-SQL — answers “how much was it”. Choosing between them looks like a decision about answer quality, which is why it usually gets made by whoever is in a hurry. It is not. It is a governance decision, because the two routes have different permission models, and one of them does not have the model you think it has.

What does it mean for an agent to access Lakehouse data?

It means turning a natural-language question into an operation the data engine knows how to run. There are exactly two ways to do that, and the Databricks documentation splits them across two sibling pages — the best possible endorsement for anyone who has to defend the decision in an architecture review: structured retrieval tools and unstructured retrieval tools.

On the unstructured route, text becomes vectors. Documents are chunked, turned into embeddings and written to an index — on Databricks, Databricks AI Search, which until recently was called Databricks Vector Search. The question becomes a vector too, and the system returns the nearest chunks. What the agent receives is text to read.

On the structured route, the question becomes a query. The model writes SQL against Unity Catalog tables and the engine executes it. On Databricks this is the Genie Agent, the current name for what used to be called a Genie space. What the agent receives is a query result.

The contrast that matters is not “which one answers better”. It is this: on the structured route the data never leaves its place — the query travels to it and is evaluated at execution time. On the unstructured route the data was copied somewhere else before any question existed, and that somewhere else has rules of its own.

The problem it solves

The real problem is that most pilots pick the wrong route for an innocent reason: the unstructured route is easier to build. You dump everything into an index — tables included — and the agent “works”. It answers. What it does not do is answer correctly and answer only to those who may see it.

Two failures come out of that choice, and they look like different problems until you trace them back:

1. The number that does not match. Aggregation questions — sum, average, count, share — are not a retrieval problem. You do not find “quarterly revenue” inside a paragraph; you compute it. A vector index returns the chunks most similar to the question, and “similar” is not “correct”. The agent then adds up whatever it found and presents it with its usual confidence.
2. The data that leaks. This is the point the whole article turns on, and it is not an opinion. The Databricks AI Search documentation, in its limitations section, says it literally: “Row and column level permissions are not supported. However, you can implement your own application level ACLs using the filter API.” Row- and column-level permissions are not supported on the index. What exists is the option for you to implement your own control, through filters, in the application.

The three mistakes I see most often in the field:

1. Dumping tables into the vector index. It turns into similarity search over numbers. That is the origin of “almost right”.
2. Assuming the index inherits the source table’s permissions. It does not — and the documentation is explicit.
3. Having no metric layer. Without one, “revenue” is whatever the model decides it is on that run.

How it works — step by step

1. Classify the question before choosing the tool. “What does the contract say about termination?” is retrieval. “How many contracts were terminated this quarter?” is a query. Questions that mix both need both tools, not one halfway option.
2. Send numbers to SQL, always. If the answer involves aggregation, comparison or a time window, the route is structured. No vector retrieval replaces a GROUP BY.
3. Define the metric before exposing the agent. A metric view in Unity Catalog declares the measure once, in YAML, and queries invoke it with the MEASURE() function. Without that, you are asking the model to decide what revenue is.
4. Point the Genie Agent at curated tables. The documented limit is 30 tables or views per agent — less a restriction than an invitation to curate.
5. Teach the domain with trusted assets. Example SQL queries and registered SQL functions give verified answers to the questions you already know are coming.
6. On the unstructured route, choose the index type. A Delta Sync Index tracks a Delta table and updates itself; a Direct Vector Access Index leaves the writing to you.
7. Implement the security filter in the index query. It is the only form of row-level scoping on that route, and it is your responsibility, not the catalog’s.
8. Measure with a benchmark, not with impressions. Each Genie Agent accepts up to 500 benchmark questions to evaluate answer accuracy.
9. Decide conversation retention before opening the agent. The retrieved excerpt is written into the thread — on Foundry, into the enterprise_memory database. Retention defined after the first incident already arrives late.

The unstructured route: the index is a copy with its own permissions

When you create an index in Databricks AI Search, it is registered in Unity Catalog — but as an object in its own right, not as an extension of the table that fed it. The consequence shows up in the access requirement: anyone querying the index who is not the owner needs USE CATALOG on the catalog, USE SCHEMA on the schema and SELECT on the index. Not SELECT on the source table. On the index.

Put that next to the limitations line — “Row and column level permissions are not supported” — and the design is clear: whoever has access to the index has access to the index’s content, all of it. The row filter you configured so carefully on the Delta table does not cross the boundary into the copy. It does not even know the copy exists.

The official way to scope is the filter API: you store metadata alongside each chunk — business unit, region, classification — and filter on it at query time. It works, and it is what you should do. But notice what happened to accountability: the access rule left the catalog, where it is declarative and auditable, and landed in application code, where it depends on somebody not forgetting to apply it. That has to be a conscious decision — not a side effect of having picked the easier route.

One reading trap deserves a warning, because an attentive reader will trip over it: the AI Search MCP server documentation says results are governed by Unity Catalog permissions. That does not contradict the paragraph above. It is permission on the index — the SELECT I mentioned. It is not the source table’s row filter. Both statements are true at once, and confusing one for the other is exactly the mistake that produces the leak.

For the rest of the route, what is worth knowing:

Aspect	What the documentation defines
Delta Sync Index	Syncs with the source Delta table automatically and incrementally
Direct Vector Access Index	Direct read and write; keeping it current is on you
Continuous sync	Latency in seconds, at higher cost — it provisions a cluster for the streaming pipeline
Prerequisite	On standard endpoints, the source table needs Change Data Feed enabled
Hybrid search	Runs ANN and full-text search in parallel and fuses them with Reciprocal Rank Fusion

Hybrid search earns a line of its own, because it solves a real problem: vectors alone miss on product codes, contract numbers and proper nouns — precisely the terms a user types when they already know what they are after. The lexical half uses Okapi BM25 and is switched on with query_type="hybrid".

The structured route: the SQL runs inside the engine

Unity Catalog row filters and column masks are SQL functions attached to the table with ALTER TABLE ... SET ROW FILTER and evaluated at query time: every row passes through the function, and the ones that return FALSE stay out of the result. Because the rule belongs to the table and is applied at execution, it holds for any query that reaches that table — including one a model just wrote. It is not the agent respecting the permission; it is the engine not returning the row. That is the structural difference between the two routes.

But we have to be precise about how the Genie Agent authenticates, because the simplified version (“it runs as the user”) is half wrong. The documentation describes two separate credentials:

• Compute: the generated query runs on the SQL warehouse with the credential embedded by the agent’s author. The end user needs no permission on the warehouse.
• Data access: Unity Catalog evaluates access with the end user’s identity, not the author’s. Each person sees only what they may see, and each query is attributed to the end user in the history.

In other words: the engine belongs to the author, the data belongs to the user. That is good for adoption, but it carries two operational consequences that belong in your design:

• If the author who configured the warehouse leaves the company, the embedded credentials stop working and queries fail for everyone using that agent. It is a single point of failure with a first and last name.
• The representative values used to match question text against column content are generated with the author’s data permissions, and they become part of the agent’s shared context. It is a fine detail, but it is a path by which a sample of restricted data reaches someone who should not see it.

And there is a common misunderstanding worth clearing up: Genie is not confined to the tables added to the agent. The documentation says it can query tables beyond those, and that control belongs to Unity Catalog, not to the agent. Which means the agent is not a perimeter — the catalog is. Anyone thinking of “limiting the agent to the right tables” as a security control is guarding the wrong door.

Underneath, what sustains auditing: Unity Catalog lineage is captured automatically at column level for queries run on Databricks, and Genie activity is recorded in the system table system.access.audit, under the service_name aibiGenie.

The third layer: from ontology to governed metrics

There is a step between “the agent writes SQL” and “the agent answers correctly”, and it is not a bigger model: it is definition.

This is where ontology stops being a diagram and starts executing. Ontology, in practical terms, is the agreement about what each business concept means and how it relates to the others: what a customer is, what counts as an active contract, which book belongs to which segment. While that agreement lives in an architecture document, the agent has no access to it — what reaches the model is a column name. The semantic layer is where the ontology becomes an artefact the engine reads, and that is why it belongs in this discussion: the same place that defines meaning is the place where permission is still evaluated. When the definition is copied into an index, both are lost at once — the meaning and the access rule.

Metric views are Unity Catalog’s implementation of semantics: they separate the definition of a measure from the fields it is analysed by. You declare the metric once, in YAML, with source, filter, dimensions and measures, and queries invoke it with MEASURE(). An ordinary view freezes aggregation and grouping at creation time; a metric view leaves grouping open at query time.

The gain in an agent context is direct: without that layer, “default rate” is whatever the model infers from column names, and two phrasings of the same question produce two numbers — both defensible, neither official. With it, the definition has an owner, a version and a single place where it changes. And the Genie Agent queries metric views, which closes the loop between the declared metric and the natural-language question.

A labelling note is due: metric views entered Public Preview, the current pages no longer carry the banner and I could not locate a formal general-availability announcement. Confirm the status before you write it into your architecture document.

In the Microsoft world, the equivalent role belongs to the Power BI semantic model, which carries business logic, calculated measures and curated metrics — with a warning almost nobody reads: RLS only restricts users with Viewer permission. It does not apply to workspace Admins, Members or Contributors. If your agent runs under an identity with the Contributor role, it sees everything, and the sentence “SQL inherits the permissions” stops being true — not because the mechanism failed, but because of an identity choice.

On the Microsoft side: Fabric data agent and Microsoft Foundry

Anyone on Fabric gets the same design in different packaging. The Fabric data agent is a generally available capability for building question-and-answer systems over your data, and it translates natural language into three different languages depending on the source: NL2SQL for lakehouse and warehouse, NL2DAX for semantic models and NL2KQL for KQL databases.

Two points stand out given the discussion above: it uses the credentials of whoever is asking to apply least privilege, and the documentation states that it honours all of the user’s permissions on the data, including RLS and CLS — the rule lives in the data, not in the application. And it takes up to five sources per agent, in any combination of lakehouse, warehouse, KQL database, semantic model, ontology and Microsoft Graph.

Notice what is not on that list: files. For a lakehouse you select tables, not files — a CSV or JSON has to be ingested into a table before the agent can see it. It is the opposite stance to Foundry’s file search, which builds the index from the upload. In Fabric, data enters through the catalogue or it does not enter at all.

An honest correction to the argument I made above: Azure AI Search does have ACL and RBAC ingestion, Purview labels and SharePoint ACLs — but in preview, and with documented lag between a permission change at the source and the index recognising it. That reinforces the thesis rather than dismantling it: the permission is still a second copy of the rule, one that has to be synchronised and may be stale at query time.

Where the Microsoft agent keeps what it read

Outside Fabric, the Microsoft agent runs on Microsoft Foundry — the current name for what used to be Azure AI Foundry, and the documentation’s own redirect confirms the change. In the Foundry Agent Service standard setup, agent state lives in three resources of yours, bound to the project by a capability host: Storage, Cosmos DB and Azure AI Search.

That reinforces the argument rather than changing it. The knowledge index is a resource in your own subscription, with RBAC of its own — the permission that counts there is the search service’s, not that of the table the data came from. And there is a detail almost nobody weighs: threads and messages live in the enterprise_memory database on Cosmos DB. The excerpt the agent retrieved and showed the user is written into the conversation. Fixing the index filter afterwards does not undo the answer already given — it became history, and history is data.

user question
        │
        ├─ "what does the document say?" → unstructured route
        │      ├─ Databricks AI Search  · the index is its own securable
        │      ├─ Azure AI Search       · ACL ingestion in preview, with lag
        │      ├─ Foundry · file search · the index is born from the upload
        │      │      └─ metadata scoping in the query (filter API)
        │      │             └─ the ACL is your responsibility
        │      └─ Fabric data agent     · reads no files: tables only
        │             └─ data enters through the catalogue, or not at all
        │
        ├─ "how much was it?"            → structured route
        │      ├─ metric defined?  → metric view · MEASURE()
        │      ├─ free-form query  → Genie Agent · SQL at query time
        │      │      └─ Unity Catalog row filter + column mask
        │      └─ source in Fabric → Fabric data agent · NL2SQL/NL2DAX/NL2KQL
        │             └─ RLS and CLS of whoever is asking
        │                    └─ the ACL belongs to the data
        │
        └─ after the answer              → the agent's state
               └─ thread in enterprise_memory · Cosmos DB (Foundry)
                      └─ history is data too

How the two routes become a single agent

In practice you do not pick one and discard the other: you expose both as tools and let the orchestrator decide. The design is the same across all three platforms — what changes is the name and, above all, who signs the query:

Tool	Endpoint or parameter	What governs access
Genie Agent MCP server · Databricks	/api/2.0/mcp/genie/{genie_space_id}	Unity Catalog permissions on every request
Unity Catalog functions MCP server · Databricks	/api/2.0/mcp/functions/{catalog}/{schema}/{function_name}	The EXECUTE privilege on the function
Fabric data agent · Foundry	A Microsoft Fabric project connection, with workspace_id and artifact_id	The identity of whoever is asking, through On-Behalf-Of; semantic model RLS and CLS still apply
Azure AI Search · Foundry	project_connection_id + index_name	The project’s managed identity, with Search Index Data Contributor on the search service; per-user trimming only through the filter parameter
File search · Foundry	vector_store_ids	A role on the resource: whoever reaches the vector store reads everything indexed into it

Read the right-hand column top to bottom and the entire argument of this article fits there. The first three rows ask who you are before answering. The last two only ask whether you can reach the index.

Two declared limitations of the Genie server avoid surprises: it is read-only and it keeps no conversation history. Anyone who needs continuity has to maintain it elsewhere. The Fabric data agent has its own, and it is the honest flip side of On-Behalf-Of: it does not accept a service principal. An agent running unattended, in a nightly pipeline, does not go through that door.

Registering logic as a Unity Catalog function and exposing it as a tool is the most underrated pattern on the list: it takes the business rule out of the prompt and puts it into an object with an owner and permissions. And when the function is used as a trusted asset, Genie can neither see nor alter the SQL inside it — exactly what you want for logic that should not be exposed.

To orchestrate all of it, the platform’s current name is Supervisor Agent, inside Agent Bricks, which coordinates Genie Agents, agent endpoints, Unity Catalog functions, MCP servers and custom agents. On the Microsoft side the equivalent is the Foundry Agent Service.

The trap lives in the last row of the table: it is the unstructured route at the point where it is easiest to trigger without noticing — all it takes is dragging a file in.

The errors that show up when the route is wrong

• The number changes with how the question is phrased. No metric view. The model is inventing the definition on every run.
• The agent cites a document the user should not see. Index with no metadata filter. No point looking for the hole in Unity Catalog: it has no jurisdiction there.
• The answer ignores the filter that exists on the table. Somebody indexed the table instead of querying it.
• Everything fails at once, for every user. The Genie Agent author’s embedded credential lost validity.
• The agent answers about a table nobody added. Documented behaviour: control belongs to the catalog, not to the agent.
• You fixed the filter and the leak is still in the history. The retrieved excerpt stayed in the thread. Fixing the source does not rewrite past conversations.

Production best practices

• Choose the route by the shape of the data, not by ease of assembly. Text to the index, numbers to SQL.
• Never index a transactional table to answer an aggregation question. If the answer requires a GROUP BY, the path is a query.
• Treat the vector index as a separately governed copy, with its own classification and its own lifecycle.
• Write the metadata filter into the index query from day one, even in the pilot. ACL retrofit is the kind of debt that only comes due in an audit.
• Declare the metrics before opening the agent to end users. Without a definition there is no correct answer — only a plausible one.
• Prefer a Unity Catalog function over logic in the prompt. A prompt has no owner and no permissions.
• Do not run the agent under a privileged identity. A Contributor role in Power BI and a broad service account in Databricks both cancel row-level scoping.
• Run the benchmark on every meaningful change to tables, instructions or model.
• Document who authored each Genie Agent and have a plan for the day that person leaves.
• Treat the agent’s thread store as a data repository. On Foundry it lives in your own Cosmos DB: classify it, set retention and bring it into audit scope.

What has to be in place

Foundation — authority over the data. Tables live in Unity Catalog with a defined owner, row filters and column masks applied where policy requires, and column-level lineage is captured. The vector index, if it exists, is registered as an object in its own right, with a declared classification and a named owner — and nobody confuses SELECT on the index with SELECT on the table. The agent’s thread store belongs on the same list: it is a repository, not a runtime detail.

It is in place when removing one person’s access to a table changes what they get back from the agent, on both routes.

Production with context — semantics turning into answers. The metrics the business uses are declared as metric views, with owner and version; the Genie Agent points at a curated set of tables and has trusted assets for the known questions; the benchmark exists and runs. “How much was it” has one answer, and it is the same on the dashboard and in the agent.

It is in place when two different phrasings of the same question return the same number.

Scale and efficiency — the platform as a product. New sources come in through a known path: Unity Catalog function for logic, metric view for metrics, index for documents. Orchestration is declarative, the cost of continuous sync is a conscious choice per use case, and adding a new domain does not require redesigning the agent.

It is in place when adding a new domain means registering objects in the catalog, not writing integration code.

The order is causal, not chronological. There is no point declaring metrics over ownerless data, and no point scaling an agent that does not yet return the same number twice.

Frequently asked questions (FAQ)

Is RAG suitable for answering questions about numbers?

No. Vector search returns chunks similar to the question, and similarity is not aggregation. Questions involving sums, averages, time windows or comparisons belong to the structured route.

Does the vector index inherit the source table’s row filters?

No. The Databricks AI Search documentation states that row- and column-level permissions are not supported, and points to implementing the control in the application using the filter API. The index is a separate object with permissions of its own.

So does the Genie Agent run as the user asking the question?

Only partly, and the distinction matters. The query runs on the warehouse with the credential embedded by the agent’s author; data access is evaluated by Unity Catalog with the end user’s identity, and that is how row filters and column masks keep applying.

Is adding only the right tables to the agent a security control?

No. The documentation says Genie can query tables beyond the ones added, because control belongs to Unity Catalog and not to the agent. The perimeter is the catalog.

Do I need a metric view if I already have a Power BI semantic model?

Not necessarily, but you do need one metric layer, and it has to be the same one for the dashboard and for the agent. Two definitions of the same metric in different places reproduce exactly the problem the layer exists to solve.

And in Microsoft Fabric, does the design change?

Not in essence. The Fabric data agent translates to SQL, DAX or KQL depending on the source, uses the credentials of whoever is asking and honours RLS and CLS. The unstructured route is still an indexed copy.

Where does the data sit when the agent runs on Microsoft Foundry?

In resources inside your own subscription. In the standard Foundry Agent Service setup, Storage, Cosmos DB and Azure AI Search are linked to the project by a capability host, and threads go to the enterprise_memory database. The permission that counts on the index is the search service’s RBAC, not the source table’s — and the conversation is a second place where the retrieved content comes to exist.

Conclusion

Choosing between RAG and text-to-SQL is not about which one answers better: it is about where the access rule lives. On the structured route the rule sits in the table and is evaluated on every execution — the engine simply does not return the row you cannot see. On the unstructured route the rule became a copy, and the documentation says in plain words that row- and column-level permissions are not supported on the index. Both routes are legitimate, and most serious systems use both. What is not legitimate is using the second one believing it behaves like the first.

The maturity test fits into a question you can ask your team today: if I remove one person’s access to a table, what changes in what the agent answers for them? If the answer is “it changes on both routes”, the design stands. If it is “it changes in SQL, on the index I’d have to check”, at least the risk is known. And if it is silence, what you have is not a data agent — it is an ownerless copy answering with the confidence of one that has an owner. Worth asking the same question a second time, in the past tense: and what has already been answered, where does it live?

👉 If you are taking a data agent to production — especially in a regulated environment, where the auditor’s question is not whether the agent is right but why it saw that row, this is the starting point.
Want to talk about governed data access for agents? Reach out on LinkedIn.