Databricks SQL & the Semantic Layer

Why this module punches above its weight for you

Databricks SQL (DBSQL) is where the Data Intelligence Platform meets the business: SQL warehouses serve BI tools, AI/BI Dashboards replace standalone reporting stacks, and Genie turns curated data into natural-language answers. Most data engineers treat this layer as someone else's problem. You can't afford to — and you shouldn't want to, because this is the module where your Power BI, SSAS/AAS, Dremio, and production Genie experience converts directly into interview answers nobody else in the room can give.

SQL warehouses: serverless vs pro vs classic

A SQL warehouse is the compute that runs DBSQL queries. It is still Spark under the hood — with Photon, the C++ vectorized engine, doing the heavy lifting — but presented as a T-shirt-sized, autoscaling endpoint instead of a cluster you configure node by node.

Three sizing levers, and they answer different problems:

• Size (2X-Small → 4X-Large) scales up: each step roughly doubles compute. Pick a bigger size when individual queries are slow — large scans, heavy joins, disk spill in the query profile.
• Min/max clusters scales out: the warehouse adds clusters of the same size and load-balances queries across them. Raise max clusters when queries are individually fast but queue under concurrency (rule of thumb: plan for roughly ten concurrent queries per cluster).
• Auto-stop controls idle cost. Serverless starts in seconds, so an aggressive auto-stop (a few minutes) is nearly free in user experience; pro and classic take minutes to warm up, so cutting auto-stop too low just trades compute cost for angry dashboard users at 9am.

Queries, alerts, and AI/BI Dashboards

The DBSQL surface area beyond warehouses:

• Queries — saved, parameterized SQL with scheduled refresh; the building block for alerts and legacy dashboards.
• Alerts — a query evaluated on a schedule, firing a notification (email, webhook, Slack/Teams via destinations) when a condition is met. Cheap operational monitoring: row-count drops, freshness SLAs, KPI thresholds.
• AI/BI Dashboards — the current name (they were briefly "Lakeview"; that name now survives only in the API namespace, and the older DBSQL dashboards are "legacy dashboards"). Built on datasets defined in the dashboard, with cross-filtering, publishing with embedded credentials, and Genie integration so consumers can ask follow-up questions of a curated dashboard.

Genie spaces: NL-to-SQL you can actually govern

AI/BI Genie (GA since June 2025) lets business users ask natural-language questions against a curated set of Unity Catalog tables. The word that matters is curated. A Genie space is not "point an LLM at the catalog" — it is a scoped, instructed, evaluated semantic surface:

• Scope: you pick a small set of tables/views. UC column comments, descriptions, and primary/foreign key metadata become the model's grounding.
• Instructions: plain-text business rules — "headcount means active employees only", "fiscal year starts in July", which filters to apply by default.
• Example queries: question→SQL pairs that teach the space your join paths and metric definitions.
• Trusted assets: parameterized queries or UC SQL functions you mark as trusted. When a user's question matches one, Genie executes your vetted logic instead of free-generating SQL — deterministic answers for the questions that matter most.
• Evaluation: benchmark question sets and user feedback (thumbs up/down) let you measure accuracy before and after you widen the audience.

And the design decision that builds trust: Genie shows the generated SQL with every answer. An analyst can expand the query, sanity-check the joins and filters, and escalate when something looks wrong. That turns a black-box chatbot into an auditable analytics tool.

Materialized views and streaming tables in DBSQL

DBSQL is no longer read-only serving — you can build incremental transformation logic directly in SQL:

• Materialized views (MVs) precompute a query's result and refresh it incrementally where possible. Ideal for the Gold aggregates dashboards hit hardest: consumers query the MV, not a five-table join, and the cost of computing the join is paid once per refresh instead of per query.
• Streaming tables ingest from streaming sources incrementally with exactly-once processing — CREATE STREAMING TABLE with a STREAM or read_files source gives you Auto-Loader-style incremental ingestion in pure SQL.

-- Gold aggregate for the P&L dashboard, refreshed on a schedule
CREATE MATERIALIZED VIEW finance_gold.pl_by_entity_month
  SCHEDULE EVERY 1 HOUR
AS
SELECT entity, fiscal_period,
       SUM(amount_usd)            AS actuals,
       SUM(budget_amount_usd)     AS budget,
       SUM(amount_usd) - SUM(budget_amount_usd) AS variance
FROM finance_silver.gl_postings g
JOIN finance_silver.dim_entity e ON g.entity_key = e.entity_key
GROUP BY entity, fiscal_period;

Both require Unity Catalog and a pro or serverless warehouse to create, and their refreshes actually execute on serverless Lakeflow Spark Declarative Pipelines infrastructure behind the scenes — the SQL surface is sugar over the same engine that runs declarative pipelines. Be straight in interviews: your production aggregations at ADM are built as Delta tables in orchestrated jobs (ADF + Lakeflow Jobs + RunMyJobs); MVs and streaming tables are lab knowledge for you, and you can articulate exactly which of your Gold tables you would convert and why (high-fanout dashboard joins with predictable refresh cadence first).

Query profile: tuning warehouse queries

Every DBSQL statement lands in Query History, and the query profile is your flame graph. Read it in this order:

• Time breakdown — scheduling/queuing vs execution vs result fetch. Heavy queuing means a concurrency problem (scale out), not a query problem.
• Pruning — files/partitions read vs pruned. Poor pruning on a large Delta table points to missing or wrong liquid clustering keys (remember: liquid clustering, ideally CLUSTER BY AUTO, is the current recommendation for new tables — partitioning + Z-ORDER is legacy guidance).
• Operator graph — exploding row counts after a join reveal fan-out mistakes; large shuffle and disk spill say the warehouse is undersized for the query shape.
• Cache and engine flags — whether the result came from cache and whether Photon executed the plan.

Result caching: know all the layers

"Why was it fast the second time?" has at least four answers, and naming them precisely is an easy credibility win:

• Query result cache (local) — exact-match results reused on the same running warehouse while the underlying data is unchanged.
• Remote result cache (serverless) — persisted result cache that survives warehouse stop/restart, so the Monday-morning dashboard storm doesn't recompute Friday's results.
• Disk cache — Parquet data files cached on the warehouse nodes' local SSDs; accelerates repeated scans of the same tables even when results differ.
• BI-tool caches — a Power BI import-mode semantic model is itself a giant cache, and dashboard tiles cache on top of that. Diagnose freshness complaints by walking the layers from the screen back to Delta.

Power BI on Databricks

The native Databricks connector in Power BI (Desktop and Service) speaks to SQL warehouses with Microsoft Entra ID SSO or OAuth — no gateway needed for cloud-to-cloud, and user-level identity flows through so Unity Catalog row filters and column masks apply per consumer. The architectural decision is storage mode:

Performance patterns that make DirectQuery viable: star schemas (narrow facts, conformed dimensions — DirectQuery over a wide flat table is how you DDoS your own warehouse), Power BI aggregation tables backed by DBSQL materialized views or pre-built Gold aggregates so common visuals never touch the base fact, dual-mode dimensions so slicers resolve in memory, and a dedicated warehouse for the BI workload sized for its concurrency profile.

When does an external semantic layer (Dremio/Denodo) still make sense?

• Choose native (DBSQL + UC + AI/BI) when Databricks is the center of gravity: one engine, one governance model, Genie and dashboards get the UC metadata for free, and Unity Catalog metric views (now GA) give you governed, reusable metric definitions in the catalog itself — Databricks' direct answer to the standalone semantic-layer pitch.
• Choose Dremio/Denodo when you must federate across systems that are not moving to the lakehouse soon (mainframe, multiple warehouses, operational databases), when many BI tools need one metric definition and the organization won't standardize on UC, or when query virtualization is a deliberate strategy to defer migration. Cost: another hop, another security model to reconcile with UC, another vendor.

Workload isolation: separate warehouses per team

One warehouse for everything is a noisy-neighbor machine. The standard pattern is isolation by workload and team:

finance-bi-prod       Serverless M, min 1 / max 4   auto-stop 10m  (dashboards, exec consumers)
finance-adhoc         Serverless S, min 1 / max 2   auto-stop 5m   (analyst exploration)
finance-genie         Serverless S, min 1 / max 2   auto-stop 5m   (Genie space traffic)
finance-refresh-etl   Pro L,        min 1 / max 1   auto-stop 10m  (Power BI refresh, alerts)

Why it works: a runaway ad-hoc query can no longer queue the CFO's dashboard; each warehouse gets a size and auto-stop tuned to its traffic shape; statement timeouts can be strict on ad-hoc and looser on refresh; and per-warehouse tags flow into system.billing.usage so chargeback per team is a query, not an argument. Connect BI-tool service principals to their own warehouse so refresh storms are visible and attributable.

Self-check before you move on

• Queuing vs slow queries: which sizing lever fixes which?
• Name the three serverless-only advantages over pro (startup, intelligent workload management + rapid scaling, remote result cache).
• List the four Genie curation mechanisms and explain why showing generated SQL matters.
• Defend composite mode as the Power BI default for lakehouse-scale facts.
• Give one situation each where native UC metric views beat Dremio/Denodo — and one where they don't.