Build a Smart Copilot Studio Agent with Databricks Genie

AI agents are becoming a common way for teams to interact with business data. With Copilot Studio, you can quickly build agents that answer questions and automate everyday tasks. But what happens when users need answers based on thousands of CRM records instead of a handful of documents?

That’s the challenge we set out to solve.

In this blog, we’ll share how we integrated Databricks Genie with Copilot Studio to build a Sales Performance Agent that can query large datasets, provide accurate responses, and generate downloadable reports, all through a simple chat interface.

1. Knowledge Source: Not Reliable for Calculations

Copilot Studio’s Knowledge Source feature works well for static, text-based Q&A (like retrieving company policies), but it sometimes needs additional handling when dealing with enterprise data.

Additionally, Knowledge Source has strict data processing limits per interaction. It gets difficult to feed large datasets into a Prompt Tool to generate Excel or PDF reports.

2. Dataverse MCP Server Tool: Has a 20-Record Limit

Initially, we architected this request with the Dataverse MCP (Model Context Protocol) approach to connect the agent directly to Dataverse so it could query live CRM data.

However, we noticed that there are some known limitations of the Dataverse tool, such as it can only return a maximum of 20 records per query. If you have thousands of accounts, hundreds of sales orders, and a team of 50 reps, the agent is only ever seeing a tiny slice of your data. It’s like trying to analyze your entire sales pipeline by looking at just one page of a 500-page spreadsheet.

After hitting these walls, we completely rethought our approach. Instead of forcing Copilot Studio to query CRM directly, we shifted the entire data layer to Databricks.

Databricks is a powerful cloud-based data platform that gave us everything the native tools couldn’t:

• No record limits: We can query tens of thousands of records simultaneously.
• Improved mathematical accuracy: Calculations are done in SQL, not by an AI formula guessing. When we ask for total revenue, we get the exact number.
• Jobs & Pipelines: We created automated data pipelines that sync data from Dataverse (CRM) to Databricks.

Now, within Databricks, we leverage a built-in AI agent called Genie. Genie acts as the intelligence layer between the user’s natural language and the database. By providing Genie with detailed instructions containing table schemas, JOIN relationships, and business rules, it automatically converts user questions into precise, accurate SQL queries behind the scenes.

The quality of this process depends entirely on the instruction file we provide. Genie understands complex business scenario logic and returns improved response data with a large volume of data.

To bridge Copilot Studio and Databricks Genie, we use a Power Automate Flow. Since Genie exposes a REST API, we wrapped all four API calls into a Custom Connector in Power Platform, which makes them available as clean, reusable actions inside our flows. The first action is Start Conversation, where we pass in the space_id (which identifies the specific Genie space configured with our tables and instructions) and the content (the user’s natural language question received from Copilot Studio). Genie receives this question, interprets it using the instruction file, and begins generating the appropriate SQL query... Read More