Fabric data agents: setup, limits and why they give wrong answers

A Fabric data agent is a chat front end for your OneLake data: someone asks "what were returns by region last quarter?", and the agent picks a source, writes a read-only SQL, DAX or KQL query, runs it as that person and answers with a sentence and a small table. It is generally available, needs an F2 or larger capacity, and is set up in minutes. Getting it to answer correctly takes longer, and a handful of hard limits decide which questions it can take at all.

What happens to a question

Microsoft's own description of the flow is worth knowing, because each step is a place to fix a wrong answer:

1. Routing. The agent, built on Azure OpenAI's Assistants API, reads the schemas the user is allowed to see and your descriptions and instructions, and chooses a source: a lakehouse, warehouse, Power BI semantic model, KQL database, ontology or Microsoft Graph.
2. Query writing. A tool for that source writes the query: NL2SQL for lakehouses, warehouses and mirrored databases (T-SQL through the SQL analytics endpoint), NL2DAX for semantic models, NL2KQL for KQL databases. It is given the question, the selected schema, your data source instructions and the most similar of your example queries.
3. Checks. The query is validated against the schema and Microsoft's policies. Anything that writes is refused.
4. Running as the user. The query runs with the asker's own identity, so row-level and column-level security and Purview policies apply. Sharing an agent never shares the data behind it.
5. The answer. The result is summarised in words, often with a table, and the run steps show which source, query and examples were used.

This is Text-to-SQLText-to-SQLA model turning a question in plain words into a database query, so people can ask the data directly. It is only as right as its knowledge of the tables and their meaning. with guard rails, and the same rules apply: it is only as good as the context it is given.

Before you start

You need a paid F2 or larger Fabric capacity, or Power BI Premium per capacity P1 or larger with Fabric turned on. A tenant admin has to enable Users can use Copilot and other features powered by Azure OpenAI and allow capacities to be designated as Fabric Copilot capacities. If your capacity is outside the US and the EU data boundary, the two cross-geo settings (processing and storing data outside your region) are required too, which is a conversation with your compliance team rather than a checkbox. Tenant settings can take up to an hour to apply.

Then create the agent from + New item in a workspace, add sources (up to 5, in any mix) and tick only the tables it should use. For lakehouses that means tables: files in the lakehouse are invisible to it.

Where it fails

A few documented limits stop many questions outright. Describe a question below and see which one stops it.

Try itWill a Fabric data agent answer this?

The data is in

·Lakehouse tables·Files (CSV, PDF)·Warehouse·Semantic model·KQL database

The answer needs

A figure or top 10Hundreds of rowsA change to data

Sources the agent needs

2

158

Asked in English

YesNo

Source in the agent's region

YesNo

Asker can read the source

YesNo

Runtime

StandardPreview

• ✓Data it can read: passesA lakehouse tables source is queried in place with T-SQL.
• ✓Rows returned: passesA short answer fits in 25 rows by 25 columns.
• ✓Language: passesQuestions, instructions and examples in English.
• ✓Capacity region: passesSource and agent capacities are in the same region.
• ✓Sources: passes2 of the 5 sources an agent can hold.
• ✓Asker's access: passesIt runs as the person asking, with their row and column security.
• ✓Teaching it: passesAdd example queries (up to 100 per source); the most similar are sent with each question.

The ones that surprise people most:

• 25 rows and 25 columns. On the standard runtime every reply is cut there, and follow-up questions may be answered from the cut answer. Ask "show all orders this year", get 25, then ask "which of those were late?" and the agent may answer from those 25. Microsoft's advice is to start a new chat. The preview runtime raises the cap to 1,000 rows, but the agent is still for answers, not exports.
• Same region. If the lakehouse sits on a North Europe capacity and the agent's workspace on France Central, its queries fail. Keep an agent on the same capacity region as its data.
• English only. Questions, instructions and example queries should all be in English for now.
• No files, no writes. It does not read PDFs, Word files or loose CSVs, and never inserts, updates or starts a job. Documents can come in through an Azure AI Search index (in preview); changes belong in a pipeline or notebook.
• You cannot pick the model. Microsoft upgrades it for every agent. An answer that was right last month can change, which is one more reason to keep an evaluation set.

Making its answers right

A wrong answer often comes from missing context. Microsoft's best-practice page maps each kind of context to one setting, and it is the clearest way to think about it:

Agent instructions (up to 15,000 characters) say which source answers what and define your terms. Microsoft suggests 5 headings: objective, data sources, key terminology, response guidelines and handling common topics. In data source instructions, write rules as instructions, not warnings: "Join OrderLines to Orders on OrderID" works better than "be careful with joins".

Example queries teach patterns, up to 100 per source, for lakehouses, warehouses and KQL databases (semantic models and ontologies do not take them). Each is checked against the schema, and one that fails is silently dropped, so re-check them when the schema changes. Comments in the query tell the agent what to swap in:

-- Question: Which 5 products had the most returns last fiscal quarter?
-- Fiscal quarters come from dim_date.fiscal_quarter; substitute the quarter asked for.
SELECT TOP 5 p.product_name, COUNT(*) AS returns
FROM fact_returns AS r
JOIN dim_product AS p ON p.product_id = r.product_id
JOIN dim_date AS d ON d.date_id = r.return_date_id
WHERE d.fiscal_quarter = 'FY2026-Q1'   -- substitute the quarter here
GROUP BY p.product_name
ORDER BY returns DESC;

Then measure, rather than trusting a few good demos. The fabric-data-agent-sdk package (in preview) runs a set of questions with expected answers against the agent published to the stage you name, production (the default) or sandbox (change the code comment to: # the stage to evaluate; "production" is the default) and writes the results, with every step, to a table:

import pandas as pd
from fabric.dataagent.evaluation import evaluate_data_agent

ground_truth = pd.DataFrame(
    columns=["question", "expected_answer"],
    data=[
        ["How many orders shipped late in September 2026?", "1,284"],
        ["Which region had the highest return rate in FY2026-Q1?", "North, 6.2%"],
    ],
)

evaluation_id = evaluate_data_agent(
    ground_truth,
    "SalesAgent",
    table_name="sales_agent_eval",
    data_agent_stage="sandbox",  # the draft; "production" is the published version
)

Keep that set from the start and rerun it after every change and every month; it is the most reliable way to notice that a model upgrade moved an answer. Our guide to building an eval set covers how many questions you need and how to pick them.

Standard or preview runtime

Every agent runs on a runtime, the orchestration and query tools around the model. The standard runtime is the default and changes rarely. The preview runtime gets improvements first: Advanced NL2SQL, which plans in several steps, follows your examples more closely, fills in implied filter values and asks a clarifying question when a question is ambiguous; schema object descriptions; topics (up to 1 million characters of instructions, sent only when relevant); and replies of up to 1,000 rows.

Try the preview runtime on the draft against your evaluation set, and publish on it only if the scores hold. A published agent stays on the runtime it was published with until you republish.

Using it outside Fabric

Publishing makes a read-only version that others query while you keep editing the draft. People can chat with it in Fabric, and it can be reached from Microsoft 365 Copilot, Copilot Studio, Microsoft Foundry, the Python SDK and, in preview, as an MCP server.

The MCP route turns the agent into one tool any MCP client can call over streamable HTTP with a Fabric token:

https://api.fabric.microsoft.com/v1/mcp/workspaces/{WorkspaceId}/dataagents/{DataAgentId}/agent

The description you write when you publish becomes the tool's description, which is what Claude, Copilot or any other orchestrator reads to decide when to call it. So describe what the agent knows and the questions it answers, not just its name. It does not support dynamic client registration, so the client must get its own token (user or service principal) for the https://api.fabric.microsoft.com/.default scope. If your client supports the MCP tasks extension, long questions run as tasks, so the client is not left holding a connection open; other clients get the answer on the same connection. How this compares with Databricks', Snowflake's and BigQuery's MCP servers is in MCP servers for data platforms.

One warning from Microsoft applies to every route: once an answer goes to Foundry, Copilot Studio, Microsoft 365 Copilot or an MCP client, it can leave Fabric's compliance boundary and region and is handled under that service's terms.

What it costs

Data agents run on your Fabric capacity (F2 or larger, or P1 or larger). Each question uses capacity as an AI query (shown under the LlmPlugin item in the Capacity Metrics app), at the Copilot rates: 100 CU seconds per 1,000 input tokens, 10 per 1,000 cached input tokens and 400 per 1,000 output tokens. The SQL, DAX or KQL it runs uses capacity on top, like any other query.

Microsoft does not publish how many tokens a data agent question takes; the schema, instructions and examples go in with every question, so it is more than a short chat. As a worked example, if a question used 6,000 input tokens and 500 output tokens:

• 6 × 100 + 0.5 × 400 = 800 CU seconds for the model.
• At $0.18 per CU-hour (pay-as-you-go, East US), that is about $0.04.
• An F2 has 2 CUs, or 172,800 CU seconds a day, so about 216 such questions would use a whole F2 day with nothing else running.

AI queries count as background work, smoothed over 24 hours, so a busy hour does not throttle the capacity at once. But on a small capacity shared with pipelines and reports, a popular agent adds up. Read your real figure in the Capacity Metrics app during a pilot before you roll it out to a department. If you are still choosing between platforms for this kind of work, Microsoft Fabric vs Databricks compares them, and moving from Synapse to Fabric covers getting the data there.

Is it the right tool?

A data agent fits when the data is already modelled in OneLake, the questions are about numbers and short lists, and the people asking would otherwise wait for an analyst. A clean Semantic LayerSemantic LayerOne shared definition of business terms and metrics, such as revenue or active customer, that reports, dashboards and AI assistants all read instead of writing their own. (a semantic model with good measures and descriptions) gives it clearer context than raw tables. It is the wrong tool for exports, for writing data back, for documents (unless you add a search index) and for anything where a confidently wrong number is worse than no number, until your evaluation set says otherwise.