Power BI, JDBC and ODBC fail only on large Databricks query results: Cloud Fetch and the workspace storage firewall

A Power BI refresh, a JDBC tool or a Python script runs a query against a Databricks SQL warehouse. Short lookups return fine. Anything that returns a real result set hangs at "fetching rows" or fails with a download, connection or timeout error. The query history in Databricks shows the statement as Finished, so the warehouse did its job. The client couldn't download the result.

That pattern nearly always means Cloud Fetch is blocked between the client and the workspace storage account.

#How Cloud Fetch works and why it breaks

For results larger than about 1 MB, the ODBC and JDBC drivers don't stream rows through the Databricks control plane. Databricks writes the result as Arrow files (up to about 20 MB each) to the workspace storage account, which is the DBFS root account in the managed resource group. It then hands the driver short-lived SAS URLs, and the driver downloads the files directly from Azure Blob Storage.

So the client machine needs a second network path, besides the one to adb-<id>.<n>.azuredatabricks.net. It also has to reach <workspace-storage>.blob.core.windows.net on 443. Results under the threshold come back inline, which explains why small queries keep working.

#Prove it in five minutes

#1. Confirm the query finished on the Databricks side

SELECT statement_id, executed_by, execution_status, total_duration_ms,
       produced_rows, read_rows, client_application
FROM system.query.history
WHERE start_time > now() - INTERVAL 2 HOURS
  AND executed_by = 'svc-powerbi@contoso.com'
ORDER BY start_time DESC
LIMIT 20;

If execution_status is FINISHED and produced_rows is large while the client reports failure, the problem is the download. Column names vary between system table releases, so check DESCRIBE system.query.history if one is missing.

#2. Find the workspace storage account

The account lives in the managed resource group and has a generated name, usually starting with dbstorage.

MRG=$(az databricks workspace show -g rg-dbx-prod -n dbx-prod \
  --query managedResourceGroupId -o tsv | awk -F/ '{print $NF}')

az storage account list -g "$MRG" \
  --query "[].{name:name, publicAccess:publicNetworkAccess, defaultAction:networkRuleSet.defaultAction}" -o table

If defaultAction is Deny or public access is Disabled, the workspace storage firewall is on. Clients now need a private endpoint to fetch large results.

#3. Test from the client machine, not from your laptop

Run this on the Power BI gateway host, the JDBC application server or the VDI the analysts use:

$sa = "dbstorageabc123xyz"   # name from step 2
Resolve-DnsName "$sa.blob.core.windows.net"
Test-NetConnection "$sa.blob.core.windows.net" -Port 443

What the result tells you:

• It resolves to a public IP while the firewall is on, so you're missing a private endpoint or private DNS for this network.
• It resolves to a private IP but the TCP test fails, so you're dealing with a route, NSG or on-prem firewall problem.
• The TCP test succeeds but downloads still fail. Look at the proxy, TLS inspection or an egress allowlist that only permits *.azuredatabricks.net.

#4. Turn Cloud Fetch off for one test connection

If the same query succeeds with Cloud Fetch disabled, you've confirmed the cause.

ODBC DSN or connection string:

Driver=Simba Spark ODBC Driver;Host=adb-1234567890123456.7.azuredatabricks.net;Port=443;
HTTPPath=/sql/1.0/warehouses/abcdef1234567890;SSL=1;ThriftTransport=2;AuthMech=11;
EnableQueryResultDownload=0

JDBC URL:

jdbc:databricks://adb-1234567890123456.7.azuredatabricks.net:443;httpPath=/sql/1.0/warehouses/abcdef1234567890;AuthMech=11;EnableQueryResultDownload=0

Python:

from databricks import sql

with sql.connect(
    server_hostname="adb-1234567890123456.7.azuredatabricks.net",
    http_path="/sql/1.0/warehouses/abcdef1234567890",
    access_token=token,
    use_cloud_fetch=False,   # test only; check your connector version supports it
) as conn, conn.cursor() as cur:
    cur.execute("SELECT * FROM main.sales.orders LIMIT 500000")
    print(len(cur.fetchall()))

The driver name and the AuthMech value depend on the driver you use (Simba-based or the newer Databricks driver) and on its version. Keep your existing values and only add the Cloud Fetch flag.

#Fix it properly: private endpoints to the workspace storage account

When the workspace storage firewall is enabled, Microsoft's documented design is that anything outside Databricks reaching that account has to use Private Link. That includes Cloud Fetch and the SQL Statement Execution API with EXTERNAL_LINKS. Create two private endpoints per source network, one for blob and one for dfs:

SA_ID=$(az storage account show -g "$MRG" -n dbstorageabc123xyz --query id -o tsv)

for sub in blob dfs; do
  az network private-endpoint create \
    -g rg-network-hub -n pe-dbstorage-$sub \
    --vnet-name vnet-hub --subnet snet-private-endpoints \
    --private-connection-resource-id "$SA_ID" \
    --group-id $sub --connection-name pec-dbstorage-$sub
done

Then link the endpoints to privatelink.blob.core.windows.net and privatelink.dfs.core.windows.net private DNS zones that the client networks use. For on-prem clients, that means your DNS forwarders have to send these zones to Azure DNS (or a Private DNS Resolver inbound endpoint). Re-run the Resolve-DnsName test and confirm it returns the private IP.

Warning

The managed resource group carries a deny assignment, and you'll normally get an error if you try to change the storage account's firewall directly. Use the workspace's storage firewall setting (and an access connector) to enable it, and add private endpoints for clients. Don't remove the deny assignment or open the account to all networks as a quick fix.

Caution

If you enable the workspace storage firewall in production before the client private endpoints and DNS exist, every BI refresh that returns more than about 1 MB starts failing at once. Build and test the endpoints from each client network first, then enable the firewall in a change window.

#Power BI specifics

• Power BI Desktop on a corporate network follows the same rules as any ODBC client. It needs DNS and a route to the storage private endpoint.
• Power BI service / Fabric can't use your private endpoints unless the connection goes through an on-premises data gateway or a VNet data gateway that sits in a network with access. If you can't use a gateway, Microsoft's guidance is to disable Cloud Fetch for that connection.
• After changing the gateway or network, test with a dataset that returns more than a few MB. Refreshing a small dimension table proves nothing.

#Statement Execution API callers

Scripts that call /api/2.0/sql/statements with "disposition": "EXTERNAL_LINKS" download from the same storage account. For moderate results, switch to INLINE. Inline results are capped (25 MiB at the time of writing, so check the current limit):

databricks api post /api/2.0/sql/statements --json '{
  "warehouse_id": "abcdef1234567890",
  "statement": "SELECT * FROM main.sales.daily_totals",
  "disposition": "INLINE",
  "format": "JSON_ARRAY",
  "wait_timeout": "30s"
}'

#When disabling Cloud Fetch is acceptable

Turning Cloud Fetch off is a valid permanent choice for clients that only pull small or medium results, such as reports with aggregated queries or operational lookups. It's a bad choice for large extracts and import-mode models with millions of rows. Without Cloud Fetch, results stream through the warehouse endpoint. Throughput is lower, and long fetches are more exposed to connection timeouts. If a client really needs bulk data, fix the network path or have the job write the data to a governed table or volume and let the consumer read it from there.

#Prevent it next time

• Put "client private endpoints for blob and dfs on the workspace storage account" on the checklist for any workspace storage firewall or Private Link change.
• Keep a canary query that returns about 50 MB, and run it from the gateway host and the main application subnet after every network change.
• Write the workspace storage account FQDN into your proxy and egress allowlists explicitly, next to the azuredatabricks.net entries.
• Alert on BI service principals whose queries finish in system.query.history while the client-side refresh fails. That mismatch is the signature of this problem.
• Record which clients run with EnableQueryResultDownload=0 and why, so nobody "optimizes" it back on without fixing the network.