Analysing and serving · Chapter 18

SQL, Dashboards, and Sharing

A platform is judged by what people can do with it, and for most organisations that means SQL, dashboards, questions asked in plain English, and data handed to a partner without emailing a file.

Warehouses, and the settings that decide the experience #

SQL warehouses are the compute that BI tools, analysts, and applications talk to, and three choices determine both the experience and the bill.

Serverless warehouses start in seconds and shut down when idle, which makes them the right default for interactive and bursty analyst work. The start-up latency of a classic warehouse is the most common reason analysts report that the platform is slow, and it is a configuration problem rather than a performance one.

Size and scaling should be set from the shape of the workload rather than the size of the data. A warehouse serving many small concurrent dashboard queries wants more clusters of a modest size, while a warehouse serving a handful of heavy analytical queries wants a larger size and less scaling. Getting these the wrong way round produces either queueing or waste.

Auto-stop should be aggressive on anything that is not serving a scheduled workload. Ten minutes is usually right for an analyst warehouse, and tightening it is often the largest saving available in an estate that has grown without review.

Dashboards, and the discipline they need #

Dashboards multiply. Every one is a promise that a number is correct, and every one is a query that runs whether or not anybody looks at it.

We keep dashboards pointed at gold tables rather than at ad hoc SQL over silver, because a dashboard containing its own business logic is business logic with no review, no test, and no lineage. When a dashboard needs a calculation that does not exist in gold, the answer is to add it to gold rather than to hide it in a widget.

We also review dashboards for usage on a schedule and retire the ones nobody opens. An unused dashboard is not free, because it costs compute, and more importantly it costs credibility when someone eventually opens it and finds it broken.

Natural language over governed data #

Databricks provides a conversational interface over Unity Catalog data, and it works considerably better than the demos of a few years ago suggested it would. It is genuinely useful for exploratory questions from people who will never write SQL.

The quality of the answers depends almost entirely on preparation that has nothing to do with the language model. Tables need meaningful comments, columns need descriptions, relationships need to be discoverable, and the curated set of tables exposed needs to be small and well shaped. Pointed at a raw silver layer with cryptic column names it produces plausible and wrong answers, which is worse than producing none.

Sharing data outside the organisation #

OpenSharing, previously Delta Sharing, grants another organisation read access to a table without copying it, and the recipient does not need to be on Databricks. This is a far better answer than the file drop it usually replaces, because access is revocable, auditable, and always current.

The operational questions to settle before using it are which tables are shareable in principle, who approves a share, and what happens at the end of a commercial relationship. We express those as a small number of shareable-by-design gold tables per domain rather than as a case-by-case decision, since case-by-case decisions become a permanent queue.

Beyond the basics #

The capabilities below are the ones that separate an estate that merely works from one that is cheap, fast, and trusted.

Metric views. A metric view defines dimensions and measures once, in Unity Catalog, so that revenue means the same thing in a dashboard, in a natural language answer, and in an analyst's ad hoc query. This is the semantic layer that teams otherwise buy separately or, more often, reimplement three times.

Three tiers of cache, and knowing which one you hit. A repeated query can be answered from a result cache without touching data at all, a scan can be served from a local disk cache of previously read files, and only a genuine miss reaches cloud storage. Serverless warehouses keep a result cache that survives the warehouse stopping, which is why an aggressive auto-stop costs less than people fear.

Materialised views for dashboard-facing aggregates. Where a dashboard runs the same expensive aggregation all day, a materialised view computes it once on a schedule and refreshes incrementally where the query shape allows, turning a repeated cost into a single one.

Query history as tables. Query history is available as system tables, so the question of which dashboard is responsible for a third of the bill is a SQL query rather than an investigation. We build a small internal dashboard over this on every engagement, and it usually pays for itself in the first week.

Row filters and column masks. A row filter or column mask attached to a table lets one physical table serve many audiences, so a regional manager and a group executive query the same object and see different rows. This removes the usual sprawl of filtered copies, each of which is a governance liability and a stale number waiting to happen.

Alerts on query results. An alert is a query, a condition, and a schedule, which covers freshness checks, threshold breaches, and reconciliation failures without any additional monitoring tooling.

Clean rooms. Where two organisations need a joint result without either handing over raw data, a clean room runs the agreed computation in an isolated environment with neither side able to read the other's rows.

The Statement Execution API. SQL can be submitted over REST and collected asynchronously, which is the right route for an application that needs an analytical result. It is not the right route for a per-request lookup, which is the subject of Serving Data to Applications.

The other clouds do each of those jobs well and do them in separate products with separate permission models. The row we would ask a sceptic to look at is fine-grained access, because having two systems there means having two answers to a question an auditor asks once.

Consumption by people is only half of this part of the manual. The other half is consumption by software, which has a different latency budget and a different set of failure modes, and that is the next chapter.

Table of Contents