SQL Editor, Parameters, CTEs, Snippets & Caching

The one-liner

Parameter playground — type values, watch the safe query build

▸ :param is typed & bound — not string-concatenated, so injection can't execute

How it works — deep dive

1 · The SQL editor — where queries live

Mechanism: the editor runs statements on a selected SQL Warehouse (10.1). Saved queries are first-class, permissioned (UC GRANT) objects reused by dashboards, alerts, and jobs. Trade-off: the editor is free; the warehouse under it drives cost/latency.

-- A saved query targets a UC object with the 3-level namespace.
SELECT region, count(*) AS orders
FROM prod.sales.orders            -- catalog.schema.table (Unity Catalog)
WHERE order_date >= current_date() - INTERVAL 7 DAYS
GROUP BY region ORDER BY orders DESC;

2 · Named parameter markers — :param (typed & injection-safe)

Mechanism: the mandatory : separates parameter names from columns; values are bound as typed literals, never spliced into SQL text. Why: that binding is what makes them injection-proof. Trade-off: they replace the legacy mustache {{ }} of the old editor — old queries must be migrated.

-- :named markers are typed & bound — safe + reusable across every range.
SELECT region, sum(amount) AS revenue
FROM prod.sales.orders
WHERE order_date BETWEEN :start_date AND :end_date   -- bound as typed DATEs
  AND region = :region                               -- bound as STRING
GROUP BY region;

3 · IDENTIFIER() — parameterize object names safely

Mechanism: a plain :param binds a value, not a name — you can't put :table after FROM. IDENTIFIER(:x) interprets the bound string as a catalog/schema/table/column name. Trade-off: the object must exist and UC grants still apply — it doesn't bypass permissions.

-- Dynamic, injection-safe table & column selection.
SELECT IDENTIFIER(:metric_col) AS metric          -- column name as a parameter
FROM IDENTIFIER(:catalog || '.' || :schema || '.orders')  -- table name as a parameter
WHERE order_date >= :since;

4 · CTEs — WITH for readable multi-step SQL

Mechanism: WITH name AS (subquery) names an intermediate result you reference (and chain) later. Trade-off (gotcha): a CTE is not guaranteed to materialize — referenced many times, it may be recomputed; persist a temp view/table for heavy reuse.

-- Chained CTEs read top-down; each name is a clean, reusable step.
WITH recent AS (
  SELECT * FROM prod.sales.orders
  WHERE order_date BETWEEN :start_date AND :end_date
),
by_region AS (
  SELECT region, sum(amount) AS revenue, count(*) AS orders
  FROM recent GROUP BY region
)
SELECT region, revenue, revenue / orders AS avg_order_value
FROM by_region ORDER BY revenue DESC;

5 · Query snippets — team-standard SQL fragments

Mechanism: save a reusable fragment with a trigger keyword; typing it expands the snippet (placeholders allowed). Why: everyone defines common logic ("active customer") identically. Trade-off: snippets aren't governed — for logic that must stay consistent, prefer a view or SQL UDF in UC.

-- Snippet "active_cust" expands to a standardized predicate so every
-- query defines "active" identically:
SELECT customer_id, region
FROM prod.sales.customers
WHERE status = 'ACTIVE'
  AND last_seen >= current_date() - INTERVAL 90 DAYS;

6 · Query caching — three layers that make BI instant

Mechanism: three layers, each auto-invalidated on write. Why: a re-run of the same query skips compute (result cache); the disk cache speeds different queries over the same hot data.

-- Force a cold run for benchmarking; re-enable afterward.
SET use_cached_result = false;   -- bypass the result cache for this session
SELECT region, sum(amount) FROM prod.sales.orders GROUP BY region;
SET use_cached_result = true;    -- restore caching

Cache simulator — run the same query, change the data, watch the cache

▸ identical query on unchanged data = cache hit; a write invalidates it

Why it matters

• Parameterized queries are the safe, reusable way to build dashboards — hard-coding values invites injection & copy-paste sprawl.
• Caching is why a dashboard re-opens instantly — knowing which cache served a run (and when each invalidates) is a classic "why was the second run 100× faster?" answer.

Uses, edge cases & limitations

• Uses: reusable dashboard queries (:param), env/column-agnostic queries (IDENTIFIER), readable pipelines (CTEs), standard fragments (snippets), millisecond re-opens (caching).
• Edge cases: a CTE referenced many times may be recomputed (materialize it); any cache invalidates on write (first post-load query pays full cost); the remote result cache needs a serverless, active warehouse.
• Limitation: :param/IDENTIFIER/snippets are Databricks SQL features; the query profile reflects one run's plan — re-check after data/size changes.

Common gotchas

• ❌ String-concatenating user input into SQL instead of :param → injection + no type safety.
• ❌ Putting :table after FROM — bind values with :param, names with IDENTIFIER(:table).
• ❌ Pasting legacy {{ mustache }} parameters into the new editor — migrate them to :named markers.
• ❌ Referencing a heavy CTE many times assuming it's computed once — it may be recomputed; materialize it.
• ❌ Assuming the result cache still helps after the table changed, or benchmarking with caching on (SET use_cached_result = false).