DatabricksCDN by Airbrx

Airbrx / Databricks CDN / Result cache vs CDN

Databricks query result cache vs a CDN for Databricks SQL

The result cache makes a running warehouse faster. It can't let the warehouse sleep, and a sleeping warehouse isn't billing you.

Every query, matched to a rulewarehouse: asleep
  1. SELECT region, SUM(total) FROM orders GROUP BY region dashboard-reads HIT cached 1 h
  2. SELECT * FROM main.sales.orders no-agent-full-scans BLOCKED 42501
  3. SELECT * FROM orders WHERE region = 'EU' LIMIT 500 route-agents ROUTED to agents wh
  4. SELECT COUNT(*) FROM orders WHERE day = CURRENT_DATE todays-numbers HIT until midnight
  5. INSERT INTO orders VALUES (...) orders-writes STALE dashboard-reads
  6. SELECT * FROM returns WHERE reason = 'damaged' no rule PASS to Databricks

How Databricks' own caches compareCheck your own hit rate

The short version

Databricks result cache vs CDN: three caches, three different jobs.

Query result cache

Makes a running warehouse faster.

  • A hit still needs the warehouse up
  • Keeps results up to 24 hours
  • Any write to the table clears it

AI/BI dashboard cache

Lets the warehouse sleep, for AI/BI dashboards only.

  • Best effort, up to 24 hours
  • Data changes do not clear it
  • Tableau, Power BI and agents get nothing

Airbrx Gateway

Databricks CDN answers before Databricks is involved.

  • A hit never wakes the warehouse
  • Any tool you point at it
  • Freshness is a rule you write
  • Each query runs on the warehouse you chose

Keep Databricks' caches either way. Whatever reaches the warehouse still benefits from them.

Rules matched to actions

Rule-based caching for Databricks SQL: rules you wrote, not guesses.

Databricks' result cache decides for you, and the only rule it knows is "a table changed, throw everything away." The gateway reads every statement before Databricks sees it, matches it to your rules, and does exactly what the matching rule says: answer it from cache, refuse it, send it to the warehouse you chose for it, or let it through. Writes can expire exactly the cached answers they affect.

  1. Deny rules first, all of them

    Every enabled deny rule is checked on every statement, in its own pass, so no other rule can shadow a block. A match is refused as a native Databricks SQL error with your message in it.

  2. Then which warehouse

    Warehouse rules pick where a query runs if it has to run: agents on a small warehouse, finance on its own, writes on the ETL one. First match wins, and each choice is logged with its cost center.

  3. Then the first cache rule that matches

    The first matching cache rule decides how long the answer lives and what makes it unique, such as per user. A write can expire the answers of the rules it names, and nothing else.

  4. No match, no change

    Anything no rule claims goes straight to your warehouse and is logged. Until you write a rule, that is everything.

In detail

Five real caching rules, and what they buy you.

These are the rules in the animation above, in the shape the gateway reads them. Each one solves something Databricks' result cache cannot.

dashboard-readscache
When
A SELECT reads orders.
Then
Answer from cache for an hour, separately for each user. After that, serve the last answer instantly while a fresh one is fetched in the background.
Instead of
Waking the warehouse every time someone opens the sales dashboard.
{
  "id": "dashboard-reads",
  "priority": 50,
  "mode": "all",
  "conditions": {
    "statementType": { "equals": "SELECT" },
    "tables": { "includes": "orders" }
  },
  "actions": {
    "cache": {
      "ttlSeconds": 3600,
      "staleWhileRevalidate": {
        "enabled": true,
        "windowSeconds": 86400
      }
    },
    "cacheKeyElements": ["userId", "standardizedSql"]
  }
}
todays-numberscache
When
The query calls CURRENT_DATE.
Then
Cache it until local midnight: the exact moment its answer can change, and not a second later.
Instead of
Treating every "today" query as uncacheable and recomputing it all day.
{
  "id": "todays-numbers",
  "priority": 40,
  "mode": "all",
  "conditions": {
    "nonDeterministic": {
      "types": { "includes": "date" }
    }
  },
  "actions": {
    "cache": {
      "ttlStrategy": "until-midnight-tz:America/Los_Angeles"
    },
    "cacheKeyElements": ["userId", "standardizedSql"]
  }
}
no-agent-full-scansdeny
When
An agent's service account asks for all of orders with no filter.
Then
Refuse it before anything runs. The agent gets a normal SQL permissions error with your message, and the block is logged.
Instead of
Finding the runaway loop on next month's invoice.
{
  "id": "no-agent-full-scans",
  "priority": 10,
  "mode": "all",
  "conditions": {
    "userId": { "matches": "^svc-agent" },
    "standardizedSql": {
      "matches": "^SELECT \\* FROM main\\.sales\\.orders\\s*;?$"
    }
  },
  "actions": {
    "deny": {
      "enabled": true,
      "message":
        "No full scans of orders for agents. Add a WHERE clause."
    }
  }
}
orders-writesinvalidate
When
An INSERT, UPDATE or DELETE lands on orders.
Then
Every answer cached by dashboard-reads goes stale at once. Nothing else is touched.
Instead of
One unrelated row emptying every cached answer on the table. From a Lakeflow job, the last task can call the markers API instead.
{
  "id": "orders-writes",
  "priority": 20,
  "mode": "all",
  "conditions": {
    "statementType": {
      "in": ["INSERT", "UPDATE", "DELETE"]
    },
    "tables": { "includes": "orders" }
  },
  "actions": { "cache": { "ttlSeconds": 0 } },
  "invalidateRules": ["dashboard-reads"]
}
route-agentsroute
When
The query comes from an agent's service account.
Then
Run it on agents, a small warehouse from your catalog, billed to its own cost center. Deny rules still apply first and cache rules after, and one cached answer serves everyone, whichever warehouse would have run it.
Instead of
A chatty agent keeping the large BI warehouse awake all night, and nobody able to say which team the DBUs belong to.
{
  "id": "route-agents",
  "priority": 15,
  "conditions": {
    "userId": { "matches": "^svc-agent" }
  },
  "actions": { "warehouse": "agents" }
}
warehousescatalog
Name them
List your SQL warehouses once, by name, with a cost center. Rules refer to the name, so swapping the warehouse behind it is one edit.
Default
Anything no warehouse rule claims runs on your default warehouse if you name one, otherwise where the client asked. With no catalog and no warehouse rules, nothing changes at all.
Logged
Every query records the warehouse it ran on, the rule that chose it and its cost center, so DBUs land on the team that spent them. A cache hit is logged as never having run.
{
  "warehouses": {
    "bi": {
      "warehouse_id": "1a2b3c4d5e6f7a8b",
      "costCenter": "shared-bi"
    },
    "agents": {
      "warehouse_id": "9f8e7d6c5b4a3f2e",
      "costCenter": "ai-agents"
    }
  }
}

Warehouse routing stays inside one Databricks workspace. A warehouse a rule names is used as named: if it refuses the query, the client sees Databricks' own error rather than a silent reroute. Over JDBC and ODBC a session stays on the warehouse it first ran on, so route by who is asking (userId, a header) rather than by query shape.

On Databricks, key per-user results by userId: that is what keeps Unity Catalog row filters and column masks from being shared between people who should see different rows. Full rule schema Setup is in how it works, and security questions are in the FAQ.

What Databricks gives you, and where it stops

Databricks' caching layers: good caches, built for a warehouse that is already running.

Follow one Tableau dashboard through a Tuesday. At 8:00 it runs its twelve queries, Databricks computes them, and the query result cache keeps the answers. At 8:15 it refreshes, and the result cache answers all twelve without recomputing anything. That is exactly what it is for.

But the warehouse had to be running to serve those hits, so the Auto Stop clock started over. At 9:02 a few new rows land in orders, and every cached answer on that table is cleared, last quarter's included. By evening the warehouse has been awake all day for a dashboard whose data changed twice.

Every Databricks cache did its job that day. None of them is built to answer from outside the warehouse, and that is the gap the gateway fills.

Databricks documents four caching layers for SQL warehouses.1 Keep all four. Here is what each does well, where it stops, and what the gateway adds in front of it.

  1. Query result cache

    Skips the work when an identical query comes back. On serverless, a remote result cache persists in workspace storage and survives the warehouse stopping. It serves ODBC and JDBC clients and the SQL Statement Execution API.

    Keeps results
    Up to 24 hours
    Cleared by
    Any update to an underlying table

    Where it stops

    A hit still needs a running warehouse. Databricks says so explicitly, even for the remote cache.1 The hit saves the execution, not the uptime, and uptime is what you are billed for. And one write clears every answer on the table, because the cache cannot tell which ones the write affected.

    Where Airbrx picks up

    The repeat is answered before Databricks sees it, so the warehouse can stay stopped. A write expires only the cached answers of the rules you name, and you can clear one answer, one rule or everything yourself, or restore an earlier answer.

    See orders-writes

  2. AI/BI dashboard cache

    AI/BI dashboards keep their own best-effort result cache, and serving from it does not start the warehouse.2 It is the one Databricks cache that lets the warehouse sleep.

    Keeps results
    Up to 24 hours
    Cleared by
    Not by data changes

    Where it stops

    It only covers AI/BI dashboards. Tableau, Power BI, Excel, dbt tests, internal apps and AI agents connect to the warehouse directly and get none of it. And because data changes do not clear it, it can serve answers up to a day old.

    Where Airbrx picks up

    The same sleep for any tool pointed at the gateway, with freshness set per rule: an hour, until midnight, or until a write you name.

    See todays-numbers

  3. Disk cache

    Keeps copies of data files on the warehouse's local SSDs, so scans read faster.

    Cleared by
    The warehouse stopping or restarting

    Where it stops

    It makes the work faster rather than avoiding it, and it starts cold every time the warehouse wakes.

    Where Airbrx picks up

    Repeats never reach the warehouse at all. New questions still do, and they still get the disk cache's speed.

  4. Databricks SQL UI cache

    Per-user results in the SQL editor and Databricks SQL dashboards, so reopening a result is instant.

    Keeps results
    Up to 7 days

    Where it stops

    It lives inside the Databricks UI. External tools never see it.

    Where Airbrx picks up

    Per-user caching for every external tool too, keyed by userId, so row filters and masks still hold.

    See dashboard-reads

Whatever the gateway passes through still gets every one of these caches, exactly as it does today.

Side by side

Databricks result cache, dashboard cache and CDN, compared

Query result cacheAI/BI dashboard cacheAirbrx Gateway
A hit needs a running warehouseYesNoNo
Covers external BI tools, apps and agentsYesNoYes, any tool pointed at the gateway
How long a result livesUp to 24 hoursUp to 24 hoursSet per rule: a TTL, a clock boundary, or until a write or marker
What clears a resultAny update to an underlying tableNot data changesYou choose: one answer by its cache key through the API, one rule's answers when a write or marker names it, or the whole cache at once
Bring back an earlier answerNoNoYes, cached answers are versioned: restore a previous one from the admin UI
Per-user resultsManaged by DatabricksManaged by DatabricksExplicit: key each rule by userId
Picks the warehouse a query runs onNoNoYes, warehouse rules, logged per cost center
Can refuse a queryNoNoYes, deny rules, as a native SQL error
ConfigurationOn by defaultOn by defaultNothing cached until you write a rule

Honestly

When the built-in cache is enough

Check this before you put anything in the path. If one of these is you, Databricks' own caching may be all you need.

  • Your warehouse is never idle. If it runs flat out on genuinely new work all day, an edge cache saves executions, not uptime. The scan will tell you which.
  • All your BI is AI/BI dashboards. Their own cache already keeps the warehouse asleep for repeat views.
  • Every reader needs the latest row, always. If the data changes constantly, there is little settled data for any cache to serve.

Measure it

Check your own hit rate

system.query.history records whether each statement was served from Databricks' result cache, in from_result_cache.3 This read-only query lists the statements that ran more than once in the last 30 days, and how often Databricks already caught the repeat:

SELECT
  statement_text,
  COUNT(*)                                            AS runs,
  COUNT(DISTINCT executed_by)                         AS people,
  SUM(CASE WHEN from_result_cache THEN 1 ELSE 0 END)  AS native_cache_hits,
  ROUND(SUM(total_duration_ms) / 3600000.0, 2)        AS execution_hours
FROM system.query.history
WHERE compute.type = 'WAREHOUSE'
  AND statement_type = 'SELECT'
  AND execution_status = 'FINISHED'
  AND start_time >= current_timestamp() - INTERVAL 30 DAYS
GROUP BY statement_text
HAVING COUNT(*) > 1
ORDER BY runs DESC
LIMIT 50;

Two caveats. It groups by exact text, so it undercounts: the gateway standardizes SQL and catches repeats that differ only in whitespace or case. And execution time is not uptime, which is what you pay for. Here is how to look at uptime, or let the scan do both.

Sources
  1. Databricks documentation, Query caching.
  2. Databricks documentation, Dashboard caching.
  3. Databricks documentation, Query history system table reference.

Free forever

Put the dashboard that keeps your warehouse awake behind it today.

Sign in with Google or Microsoft, get your own gateway hostname, point one Databricks connection at it. No card, no sales call, no trial clock. Usage caps may apply.

Try it free, forever Scan my query history first