balajij8
Esteemed Contributor II

4 weeks ago

@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