Agent outside databricks communication with databricks delta table

[Kushal_2612]

Hello community,

I have following use case in my project:

User[ Ask any query in simple english related to data] -> AI Agent -> Databricks unity catalog -> Delta table.

Currently required data for project is in volume of workspace. Then we apply medallion architecture on that handling crash recovery also of data and then stored final data as delta data as a schema in my workspace. Now end product is that when a user ask a question related to data to AI Agent it will use SQL query to answer that question. My question is that here which method I should use to connect my AI agent with databricks?. I have used databricks API method but it is taking so much time for getting response. 

[data_pulse]

@Kushal_2612 

Since you are already using Databricks API and seeing high latency, Check the below once as it needs to be validated for API/ Compute Latency.

• Is the SQL warehouse already running, or is each request waiting for compute startup?
• How long does the generated SQL query itself take when run directly in Databricks?
• Is the API call asynchronous and spending time polling for completion?
• Is the AI agent itself taking most of the time to generate SQL / process the result?

So first benchmark the same query directly against a Serverless SQL Warehouse. If that is fast but the current API flow is slow, then the bottleneck is likely the API/agent orchestration rather than the Delta table itself.

If your agent already generates SQL, a Serverless SQL Warehouse + Databricks SQL Connector is worth testing instead of repeatedly using REST/polling. Can check the reference.

If the intent is really natural language based analytics, then Genie Agent is a better fit because Databricks handles the NL to SQL layer seamlessly.

[Kushal_2612]

I have found that AI Agent was taking so much time to run in my local system as compared to query run in SQL Editor of Databricks. So main problem is that data contains 1581 total columns and 3 lakh around rows, for every SQL query AI Agent check all 1581 columns that is the main issue which is taking so much time to run. How to counter that problem?

[DoTA]

A few connection options, roughly fastest to slowest for an external agent that already emits SQL:

1. Databricks SQL Connector for Python against a Serverless SQL Warehouse kept warm (auto-stop 5-10 min). Persistent connection, no per-call compute spin-up, no polling - usually the lowest-latency path for "agent generates SQL -> run it -> return rows."

2. Statement Execution API, called correctly: set wait_timeout=30s with on_wait_timeout=CONTINUE so short queries return inline instead of you polling GET /statements/{id} in a loop. Use disposition=EXTERNAL_LINKS + format=ARROW_STREAM above a few MB. Most "the API is slow" cases are warehouse cold start or a fixed-sleep poll loop.

3. Genie Conversation API if you want Databricks to own NL->SQL. Less control over the generated SQL, but you skip building that layer; latency is higher because there is an LLM call in the path.

4. Databricks MCP server (UC functions / Genie space) if your agent framework speaks MCP - same underlying latency as the above.

Latency checklist before changing anything: use Serverless (not classic - 3-5 min cold start); confirm the warehouse is running when the agent fires; time the generated SQL directly in the SQL editor to separate query time from transport; check whether the time is actually in your agent's LLM call. Biggest single win for us was dropping a poll loop for wait_timeout + a warm serverless warehouse.

[kartikchoudhary]

If the main issue is response time, have you checked how much of the latency is coming from the API call itself versus SQL generation and query execution?

For an interactive use case like this, I’d also be curious whether you’re keeping the SQL Warehouse warm and reusing the connection rather than creating a new connection for each question.

[balajij8]

@Kushal_2612 The slowness you are seeing is generally when an external service submits queries against standard workspace compute or interactive clusters, which carry heavy execution overhead. For querying Unity Catalog Delta tables from an external agent, you can point the agent at a Databricks SQL Warehouse using either the SQL Statement Execution API (/api/2.0/sql/statements) or drivers. SQL Warehouses are purpose-built for low-latency BI and transactional application queries, providing Photon-accelerated execution, serverless auto-scaling in seconds and intelligent query result caching. For a text-to-SQL pipeline where the agent translates user input into SQL and fetches the data, the Statement Execution API handles async execution, result pagination and caching out of the box making it far more efficient than generic workspace API calls.

To minimize latency further, you can verify a few specific warehouse and table configurations.

• Serverless SQL Warehouses - It removes cluster spin up delays keeping startup latency to a few seconds if idle.
• Query Result Caching - Ensure result caching is active on your warehouse so identical or similar queries return instantly from memory.
• Enable Predictive I/O - Turn on Predictive I/O for the frequently accessed curated Delta tables to accelerate table scans and filter operations.

If you have architectural flexibility regarding where the agent runs, an alternative to consider is deploying the agent directly onto Databricks via a Model Serving endpoint. It eliminates outbound network hops and enables native SQL functions like ai_query() for in-database natural language workflows