How to build a data support Chatbot in Copilot on Fabric

This tutorial shows how to create a data support Chatbot using Copilot in Fabric to answer questions about schemas, columns and pipelines. It is useful for teams that want to provide immediate access to technical information without writing extensive documentation: instead of searching README files or opening tickets, users consult the Chatbot and get answers based on the catalog. We will explain the reason for each step and then show a practical step-by-step that can be replicated in minutes.

Prerequisites

• An account with access to Microsoft Fabric and permission to use Copilot. Ideally, a provisioned Copilot license and Owner or Contributor permissions in the Workspace.
• A Workspace with data loaded (e.g.: Dataflow Gen2, Lakehouse or Warehouse). It is recommended to start with a pilot set of 10–50 tables for testing.
• Basic Fabric knowledge: Data Explorer, Notebooks and KQL/SQL. Knowing how to run simple queries and create tables in the Warehouse or Lakehouse is sufficient.
• Read permissions for the entities that will feed the meta_catalog — Copilot needs access to the metadata table to respond without errors.

Step 1: Prepare a reference dataset

The Chatbot needs a source to look up metadata. Create a simple table with table name, column, type and description. This table serves as the index that Copilot consults first — if you have 200 rows for the 20 most used tables, you can cover ~80% of operational questions. You can use a Notebook or Dataflow Gen2 to create the table in the Lakehouse or Warehouse.

-- Exemplo SQL no Warehouse
CREATE TABLE meta_catalog (
  table_name STRING,
  column_name STRING,
  data_type STRING,
  description STRING
);

INSERT INTO meta_catalog VALUES
('clientes','id','INT','Identificador único do cliente'),
('clientes','nome','STRING','Nome completo do cliente'),
('vendas','valor','DECIMAL','Valor da transacção em EUR');

Practical tip: add additional columns if you need more context (e.g.: source, last_updated, row_count). You can also automate updating the meta_catalog with a Notebook that runs a DESCRIBE TABLE on each table and updates the rows daily.

Step 2: Index the catalog for Copilot

Copilot works better when it has fast access to metadata. Ensure the meta_catalog table is visible in the Workspace and has read permissions for Copilot. A common practice is to place the meta_catalog in a OneLake or Lakehouse connected to the Workspace and set read permissions for the Copilot service group. Verify that query response times are low (ideally < 300 ms) so the Chatbot responds smoothly.

Step 3: Create a base prompt for the Chatbot

Define a prompt that teaches Copilot to look for answers in the meta_catalog before resorting to free reasoning. A guiding prompt drastically reduces hallucinations and establishes a priority order: 1) search meta_catalog, 2) if no result, warn and suggest a query. This prompt is the assistant’s default behavior in data support contexts.

System: You are a data support assistant. Use only information from the meta_catalog table to answer questions about schema, columns and data types. If answer not found, say "Não encontrado no catálogo" and suggest a KQL/SQL query.
User: {user_question}

Step 4: Test simple questions in Copilot

Open Copilot in the Workspace and use the base prompt. Ask direct questions to confirm that the Chatbot uses the meta_catalog table. Test a set of 10–20 questions covering common scenarios (columns, types, descriptions, related tables). If Copilot returns answers that do not match the table, review permissions or the prompt.

Exemplos de perguntas para testar:
- Quais são as colunas da tabela clientes?
- O que significa a coluna valor em vendas?
- Que tipo tem clientes.id?

Step 5: Generate automatic queries when information is missing

If Copilot does not find an entry, ask it to generate a KQL/SQL query that the user can execute. This makes the Chatbot useful even without full catalog coverage — for example, in the 20% of cases where the column is not indexed, Copilot provides a ready-to-run query, reducing manual effort. Provide examples of robust queries that use LIKE and description searches to maximize chances of finding matches.

System addition: If not found, return a ready-to-run SQL query to search the warehouse, e.g.:
SELECT table_name, column_name, data_type, description
FROM meta_catalog
WHERE column_name LIKE '%{term}%' OR description LIKE '%{term}%';

Step 6: Improve with iterative prompts and validation

Add iterative prompts to clarify ambiguous questions (e.g.: “Do you mean the clientes or vendas table?”). Use automatic validation: when Copilot answers, ask it to attach the query it used to verify the source. You can also implement a small flow that automatically runs the validation query and compares the result with Copilot’s answer, flagging discrepancies. This reduces errors and increases user confidence.

User: O que é "id"?
Copilot: A que tabela se refere: clientes ou outra? (se ambíguo, peça clarificação)

Verify the result

Confirm that the Chatbot responds with data from the meta_catalog and that, when it cannot find an entry, it returns the message "Não encontrado no catálogo" followed by a valid SQL query. Run a battery of tests: 30 questions mixing ambiguous, specific and partial-term queries. Execute the suggested queries and check for consistency. Useful metrics: success rate (correct answer) > 90% for covered tables, average response time < 1 s, and a reduction in support tickets by at least 30% after initial adoption.

Conclusion

You now have a data support Chatbot in Copilot on Fabric that uses a simple catalog to answer about schemas and columns and generates queries when necessary. Next steps: automate updating the meta_catalog from Lakehouse metadata, expand coverage to the 50 most critical tables and integrate lineage explanations. Operational tip: start by covering the most used tables to reduce "Não encontrado no catálogo" responses and collect user feedback in the first weeks to adjust the prompt and the catalog.