How to create a Metadata Analyzer in Fabric Copilot

This tutorial shows how to create a Metadata Analyzer in Fabric Copilot to scan tables and generate a report with sensitive columns, inconsistent types and undocumented fields — useful for audits, governance and model preparation.

Prerequisites

• Account with access to Microsoft Fabric and permissions for the Copilot.
• A Lakehouse or Warehouse with at least one sample table.
• Basic knowledge of SQL and the Data Explorer pane in Fabric.

Step 1: Prepare the sample table

Choose a table in your Lakehouse or Warehouse that has varied columns (names, emails, dates, amounts). If needed, create a simple table for testing. The goal is to have real data for the Copilot to analyze.

-- Exemplo em SQL para criar uma tabela de teste
CREATE TABLE IF NOT EXISTS sales_demo (
  id INT,
  customer_name STRING,
  customer_email STRING,
  amount DECIMAL(10,2),
  purchase_date DATE,
  notes STRING
);
-- Inserir alguns registos
INSERT INTO sales_demo VALUES
(1,'Ana Silva','ana.silva@example.com',120.50,'2024-06-01','primeira compra'),
(2,'João Pereira','joao.p@example.com',95.00,'2024-06-03',NULL);

Step 2: Open Copilot and define the Analyzer goal

Open Copilot in Fabric and prepare a clear prompt: describe that you want a Metadata Analyzer that identifies sensitive columns (e.g., emails, NIF), inconsistent types (numbers stored as text) and fields without descriptions. Explain the desired output format (JSON or table).

Prompt exemplo:
"Analisa a tabela sales_demo e devolve um relatório JSON com:
- colunas sensíveis (tipo e motivo),
- colunas com tipos inconsistentes (exemplos),
- colunas sem documentação,
- sugestões de tipo e ação (ex.: anonymize, cast, document).
Formato: JSON com chaves: column, issue, example, suggestion."

Step 3: Provide context and samples to Copilot

For better results, provide a small data excerpt and the schema. You can paste five rows or use a SELECT LIMIT query. This helps Copilot detect real patterns.

-- Extrair amostra para colar no prompt
SELECT * FROM sales_demo LIMIT 5;

Step 4: Run the prompt and iterate

Run the prompt in Copilot. You will likely need to ask clarifications or adjust instructions (e.g., which rules to consider sensitive). Iterate until the JSON is coherent and includes real examples from the sample.

Exemplo de output esperado (JSON simplificado):
[
  {"column":"customer_email","issue":"sensitive","example":"ana.silva@example.com","suggestion":"anonymize or hash"},
  {"column":"amount","issue":"type_inconsistent","example":"'120.50' as text","suggestion":"cast to DECIMAL"},
  {"column":"notes","issue":"missing_documentation","example":null,"suggestion":"add column description"}
]

Step 5: Automate with a Prompt Template

If you will repeat this analysis across multiple tables, create a Prompt Template in Copilot with placeholders for table name, schema and sample_query. This saves time and ensures consistency.

Template exemplo:
"Analisa a tabela {{table_name}} com schema: {{schema}} e sample: {{sample}}. Produz JSON com column, issue, example, suggestion."

Step 6: Integrate the report into the Lakehouse

Save the resulting JSON in a metadata table in the Lakehouse for tracking and audit. This enables future queries and automated actions (e.g., workflows for anonymization).

-- Criar tabela para relatórios
CREATE TABLE IF NOT EXISTS metadata_analyzers_reports (
  table_name STRING,
  report JSON,
  analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Inserir relatório (exemplo simplificado)
INSERT INTO metadata_analyzers_reports (table_name, report) VALUES
('sales_demo', '{"report": [...] }');

Verify the result

Confirm that the JSON contains: all important columns from the table, real examples (from the sample), and actionable suggestions. Also check the metadata_analyzers_reports table to ensure the report was saved.

Conclusion

With this Metadata Analyzer in Fabric Copilot you can quickly identify risks and inconsistencies in your tables, speed up audits and prepare data for models. Next steps: create automated rules that apply the suggestions (e.g., casting, anonymization) or schedule periodic analyses. Tip: start with small samples and refine the prompt to reduce false positives.