A Fabric Data Agent can produce valid T-SQL that answers the wrong business question. It may select a similar status, join at the wrong grain, use order date instead of invoice date, count events instead of customers, include cancelled records, or miss a tenant filter. More prompting at the agent level rarely fixes a source that exposes ambiguous objects and undocumented business logic.
For SQL sources, Microsoft describes three core inputs to NL2SQL: the selected schema, data-source instructions, and example queries. The tool retrieves similar examples, generates a read-only query, validates it against the approved schema, and executes it through the source's SQL analytics endpoint. Accuracy work should improve those inputs and prove the output query, not merely polish the final explanation.
Can a new analyst write the approved query using only the exposed schema and instructions? If not, the Data Agent is being asked to infer undocumented business logic.
Define a business-question accuracy contract
Select one decision domain, accountable data owner, supported users, approved source, question classes, metrics, time logic, sensitivity, and quality threshold. For each question, specify expected interpretation, source objects, joins, filters, grain, calculation, output fields, deterministic result, and acceptable clarification. Define prohibited questions and cases that require an analyst.
| Input layer | Required decision | Evidence | Failure signal |
|---|---|---|---|
| Domain scope | Users, decisions, supported questions, prohibited use, owner, freshness, and acceptance threshold. | Versioned question catalogue and accountable approver. | Generic agent expected to answer every question over a broad platform. |
| SQL source | Lakehouse, Warehouse, SQL Database, or Mirrored Database; authoritative endpoint and environment. | Source ID, workspace, refresh, lineage, permission, and production binding. | Duplicate or development source answers the same business term differently. |
| Selected schema | Minimum tables, views, columns, names, descriptions, keys, and relationships required. | Approved object inventory and rationale for each exposed object. | Ambiguous duplicates, technical fields, excessive scope, or missing join key. |
| Instructions | Purpose, grain, business rules, joins, dates, values, filters, nulls, and exclusions. | Analyst review showing instructions support correct query construction. | Long prose conflicts, security in prompts, or missing operational rules. |
| Example queries | Representative question-SQL pairs for complex and high-value patterns. | Schema validation plus clarity, relatedness, mapping, and result review. | Wrong SQL becomes a highly relevant few-shot and amplifies error. |
| Evaluation | Generated query, deterministic result, answer, identity, latency, and regression thresholds. | Repeatable scorecard on fixed data and production-shaped personas. | Success judged from fluent prose or user satisfaction alone. |
Reduce the schema to the decision surface
Expose only objects needed for the supported questions. Microsoft recommends minimizing scope and gives a practical tip to use 25 or fewer tables for a source for optimal results. That is a ceiling, not a target. A leadership sales agent may need six curated tables or views, while attaching the entire enterprise lakehouse introduces synonyms, duplicate dates, helper tables, historical structures, and technical fields that compete during query generation.
Prefer business-readable names and descriptions. Replace or hide objects such as F_SLS, TR_AMT, and multiple generic status columns with governed views or metadata that expresses meaning. Define fact grain, dimension keys, role-playing dates, slowly changing dimensions, active-row logic, currency, timezone, fiscal calendar, soft deletion, and tenant boundaries. Do not expect the tool to inspect arbitrary row values before generating a query; document categorical formats such as CA versus California.
Use SQL views when deterministic logic should not be regenerated on every question. A governed view can encode approved joins, row filters, status mapping, current-record selection, or metric components while preserving read-only access. Test performance and lineage, and avoid hiding contradictory definitions behind similarly named views. The Data Agent should choose among clear analytical surfaces, not reverse-engineer an operational schema.
Write source instructions and few-shots as executable knowledge
Agent-level instructions should describe cross-source routing and answer behavior. Put SQL-specific join paths, grain, filters, date rules, value formats, and query constraints in data-source instructions where the SQL tool receives them. Structure instructions into short sections: purpose, supported questions, entities, joins, dates, metrics, required filters, exclusions, and output expectations. Remove narrative that cannot be tested.
Example queries demonstrate approved patterns. Pair one clear natural-language question with SQL that validates against the selected schema and returns the intended result. Cover complex joins, fiscal periods, cohort logic, status combinations, exclusions, ranking, distinct entities, and null behavior. Use realistic variation without creating near-duplicate examples that compete for retrieval. Microsoft notes that the tool typically retrieves the most similar examples, so relevance matters as much as volume.
Evaluate the example library before using it as context. Microsoft exposes SDK evaluation for clarity, relatedness, and mapping between question literals and SQL. Add deterministic review for business correctness, performance, security, grain, and result. An example can pass syntax validation and still teach an incorrect metric. Version the library with the source schema and rerun validation after column, view, or business-rule changes.
The preview Build agent with AI can explore schema, inspect query-history patterns when permitted, propose instructions and few-shots, and execute read-only validation queries. Use it as a drafting assistant. It does not select the source or schema for you, and accepted suggestions can replace current configuration blocks. Review diffs, validate generated SQL and results, and preserve the prior configuration before applying changes.
Score the generated SQL before the narrative answer
| Dimension | Test | Pass condition | Typical remediation |
|---|---|---|---|
| Source and objects | Check selected source, tables, views, columns, and approved schema boundary. | Only authoritative and permitted objects are referenced. | Reduce schema, improve names, or clarify source routing. |
| Join and grain | Inspect keys, join type, cardinality, fan-out, deduplication, and result grain. | No duplicate inflation, dropped entities, or mixed grains. | Curated view, explicit join instruction, or representative few-shot. |
| Filters and dates | Verify tenant, status, active row, timezone, fiscal period, exclusions, and implied values. | Every material question constraint appears correctly in SQL. | Document values, add examples, clarify ambiguity, or use Advanced NL2SQL evaluation. |
| Metric logic | Compare aggregation, distinct count, denominator, null, currency, and approved business formula. | Query reproduces deterministic ground truth. | Governed view, explicit metric surface, or corrected example query. |
| Security | Run allowed and denied personas, cross-tenant prompts, sensitive columns, and injection attempts. | Application and source permissions fail closed with no inferred leakage. | Permission correction, narrower source, RLS/CLS test, or separate agent. |
| Operations | Measure query cost, latency, timeouts, result limits, capacity, and repeat behavior. | Correct answers meet the defined SLO and consumption threshold. | Optimize views and SQL, reduce scope, index/source tuning, or route large extracts elsewhere. |
Classify failures by durable layer: source data, schema design, selected scope, instruction, example retrieval, generated SQL, permissions, runtime, result formatting, or user ambiguity. Fix the lowest responsible layer. Do not add a verbose prompt workaround for a broken join or encode access policy in instructions. Rerun the complete suite after each change because improving one pattern can alter example retrieval or routing for another.
Compare the standard NL2SQL runtime first. If unresolved cases specifically involve example adherence, implied filters, or ambiguity, test the preview Advanced NL2SQL runtime through the separate runtime evaluation and migration assessment. Keep source configuration constant so runtime behavior is the only variable.
Run a three-to-five-week NL2SQL accuracy assessment
- Select one SQL decision domain, production source, accountable owner, target users, business questions, prohibited uses, and quality threshold.
- Inventory the source, schema, object names, relationships, grain, dates, values, permissions, refresh, lineage, performance, and current failures.
- Reduce the selected schema, create governed views where needed, and document the purpose of every exposed table and column.
- Write structured data-source instructions and a diverse library of validated question-SQL examples from approved business logic.
- Build deterministic ground truth and run generated-query, result, security, ambiguity, unsupported, latency, and consumption tests.
- Classify failures, remediate the correct layer, evaluate standard and preview runtime only where justified, and repeat regression.
- Deliver the source contract, schema map, instructions, example library, evaluation scorecard, release package, operating checks, and prioritized backlog.
Frequently asked questions
How does a Fabric Data Agent generate SQL?
For a supported SQL source, the Data Agent reads the approved schema, applies data-source instructions, retrieves the most similar example queries, generates a read-only T-SQL query, validates it against the selected schema, executes it through the SQL analytics endpoint, and formats the result. The standard runtime uses NL2SQL; the preview runtime can use Advanced NL2SQL.
How many tables should a Fabric Data Agent SQL source include?
Microsoft recommends minimizing scope and provides a tip to use 25 or fewer tables for a given source for optimal results. The correct number is the smallest approved schema that can answer the target questions. Exclude unrelated, duplicate, technical, or ambiguous objects and test every selected table's purpose.
What should Fabric Data Agent data-source instructions contain?
Data-source instructions should explain the source's purpose, supported question types, business terminology, grain, required filters, join paths, date rules, status values, exclusions, null behavior, and columns required in results. They should help a competent analyst write the correct query without trying to replace deterministic business logic or security controls.
How do example queries improve Fabric Data Agent NL2SQL accuracy?
Example queries pair a clear natural-language question with validated SQL that demonstrates approved joins, filters, calculations, and grain. The Data Agent retrieves the most similar examples for a question and uses them as context. Examples must be unique, correct, schema-valid, representative, and evaluated for clarity, relatedness, and literal mapping.
How long does a Fabric Data Agent NL2SQL accuracy assessment take?
A focused assessment commonly takes three to five weeks for one SQL domain, one Data Agent, and a representative question set. The work includes source and schema review, instructions, example-query design, deterministic ground truth, NL2SQL evaluation, security tests, iteration, release evidence, and an improvement backlog.
Official implementation references
- SQL sources, NL2SQL, and Advanced NL2SQL
- Add and configure Fabric Data Agent SQL sources
- Fabric Data Agent configuration best practices
- Example-query design and SDK evaluation
- Build agent with AI configuration workflow
- Fabric Data Agent source and few-shot definition format
Start with ten questions whose approved SQL is already known. Datrick can turn those examples into a focused source contract, measurable NL2SQL improvements, and a production regression suite.