Analytics plugin

Enables SQL query execution against Databricks SQL Warehouses.

Key features:

• File-based SQL queries with automatic type generation
• Parameterized queries with type-safe SQL helpers
• JSON and Arrow format support
• Built-in caching and retry logic
• Server-Sent Events (SSE) streaming

Basic usage

import { analytics, createApp, server } from "@databricks/appkit";

await createApp({
  plugins: [server(), analytics({})],
});

Query files

• Put .sql files in config/queries/
• Query key is the filename without .sql (e.g. spend_summary.sql → "spend_summary")

Execution context

• queryKey.sql executes as service principal (shared cache)
• queryKey.obo.sql executes as user (OBO = on-behalf-of, per-user cache)

The execution context is determined by the SQL file name, not by the hook call.

SQL parameters

Use :paramName placeholders and optionally annotate parameter types using SQL comments:

-- @param startDate DATE
-- @param endDate DATE
-- @param limit INT
SELECT ...
WHERE usage_date BETWEEN :startDate AND :endDate
LIMIT :limit

LIMIT / OFFSET require Spark IntegerType specifically — BIGINT (LongType) is rejected with INVALID_LIMIT_LIKE_EXPRESSION.DATA_TYPE. Annotate with INT, or use sql.number() (auto-infers INT for values in [-2^31, 2^31-1], falling back to BIGINT for wider values) / sql.int() at the call site.

Supported -- @param types (case-insensitive):

• STRING, BOOLEAN, DATE, TIMESTAMP, BINARY
• INT, BIGINT, TINYINT, SMALLINT — bind via sql.int() / sql.bigint()
• FLOAT, DOUBLE — bind via sql.float() / sql.double()
• NUMERIC, DECIMAL — bind via sql.numeric() (pass strings for precision)

Sample values for type generation

Some queries only have a valid shape once a parameter has a concrete value — most commonly a dynamic table name built with IDENTIFIER(). During type generation AppKit runs DESCRIBE QUERY with placeholder defaults, so an unresolved parameter collapses to an empty string and produces invalid SQL (IDENTIFIER('' || '.schema.table') → PARSE_SYNTAX_ERROR).

Append = value to a -- @param annotation to give type generation a sample value. It is used only while describing the query; at runtime the real parameter is still bound, so the query stays portable across environments:

-- @param target_catalog STRING = main
SELECT *
FROM IDENTIFIER(:target_catalog || '.sales.nation')

Type generation describes main.sales.nation to infer the result columns, while the deployed app binds whatever catalog the caller passes. String, DATE, and TIMESTAMP values are quoted automatically (= main → 'main'), and an already-quoted literal is kept as-is (= '2024-01-01'). Numeric, BOOLEAN, and BINARY values are validated against a strict literal shape (= 100, = true, = X'00'); a value that doesn't match — anything that could otherwise inject SQL into the describe statement — is ignored and the parameter falls back to its type-based placeholder, so a sample value can never break out of the DESCRIBE QUERY.

Server-injected parameters

:workspaceId is injected by the server and must not be annotated:

WHERE workspace_id = :workspaceId

HTTP endpoints

The analytics plugin exposes these endpoints (mounted under /api/analytics):

• POST /api/analytics/query/:query_key
• GET /api/analytics/arrow-result/:jobId
• POST /api/analytics/metric/:key — measure a Unity Catalog Metric View (see Metric views)

Format options

• format: "JSON" (default) returns JSON rows
• format: "ARROW" returns an Arrow "statement_id" payload over SSE, then the client fetches binary Arrow from /api/analytics/arrow-result/:jobId

Metric views

POST /api/analytics/metric/:key measures a Unity Catalog Metric View that you declared in config/metric-views/definitions.json. Instead of writing SQL, the caller sends a structured request — which measures to aggregate, which dimensions to group by, and an optional filter — and the plugin builds and runs the SELECT MEASURE(...) ... GROUP BY ALL for you against the view.

The route is dormant until config/metric-views/definitions.json exists: with no config file, every metric key returns 404. Declaring the file (and generating types) is covered in Metric-view types; this section documents the runtime endpoint that config activates.

Request body

:key is a metric key from definitions.json. The body fields:

Measures and dimensions must be unique across both lists — a name cannot repeat, nor appear as both a measure and a dimension.

How the request becomes SQL

Given a view registered as catalog.schema.revenue_metrics, the request above produces (measures and dimensions are sorted for a deterministic SELECT list):

SELECT MEASURE(`arr`) AS `arr`, MEASURE(`revenue`) AS `revenue`,
       date_trunc('month', `order_date`) AS `order_date`, `region`
FROM `catalog`.`schema`.`revenue_metrics`
WHERE `region` IN (:f_0, :f_1)
GROUP BY ALL
ORDER BY `revenue` DESC, `order_date`, `region`
LIMIT 100

The metric view's FQN and every measure/dimension identifier are backtick-quoted; filter values are bound as parameters (:f_0, :f_1, …), never interpolated into the SQL string.

Deterministic results with limit

When limit is set, the route automatically appends all grouped dimensions to the ORDER BY clause as tie-breakers (unless they are already named in orderBy). Under GROUP BY ALL, the full dimension tuple is unique per row, so ordering by all dimensions produces a TOTAL order — every run returns the same rows, not an arbitrary sample.

This matters because LIMIT without ORDER BY is a row sample, not "the top n": Spark returns whichever rows it produced first, which varies with partitioning, parallelism and cache state. A card built on such a request can show a different number run to run with nothing erroring. The tie-breakers close that gap — over unchanged data, the same request now returns the same rows.

If you want top-N by a measure, order that measure explicitly and provide limit:

{ "orderBy": [{ "field": "revenue", "direction": "DESC" }], "limit": 100 }

The route adds the remaining dimensions (order_date, region in the example above) after your explicit entry, so the result is stable across runs.

Important: order measures by their SELECT alias. Spark rejects ORDER BY MEASURE(\revenue`)withMETRIC_VIEW_INVALID_MEASURE_FUNCTION_INPUT. The generated SQL aliases every measure (e.g. MEASURE(`revenue`) AS `revenue`), so always reference the alias — in this case, just "revenue"`.

Filters

filter is a recursive tree. A leaf is a single predicate:

{ "member": "region", "operator": "equals", "values": ["EMEA"] }

Predicates combine with and / or groups, which can nest:

{
  "and": [
    { "member": "region", "operator": "in", "values": ["EMEA", "APAC"] },
    {
      "or": [
        { "member": "segment", "operator": "equals", "values": ["Enterprise"] },
        { "member": "deal_size", "operator": "gt", "values": [50000] }
      ]
    }
  ]
}

The operator vocabulary:

For contains / notContains, the %…% wildcards are applied to the bound parameter value (%value%), not written into the SQL text — so the value is never interpolated, consistent with every other operator.

Empty groups of either kind ({ "or": [] }, { "and": [] }) are rejected with 400 — every and / or group must contain at least one predicate. To send no filter, omit the filter field entirely rather than passing an empty group.

Filters are bounded to keep hostile input from exhausting the server: nesting depth ≤ 8, ≤ 100 children per and / or group, and ≤ 1000 values per predicate. A request that exceeds a cap is rejected with 400.

Executors (cache scope)

Each entry in definitions.json names the executor the query runs as, which also sets the cache scope. This is fixed by config, not the request:

This mirrors the <key>.sql vs <key>.obo.sql distinction for file-based queries.

Response

The response is the same SSE stream as POST /api/analytics/query/:query_key. If the SQL warehouse is cold it first emits warehouse_status events (see Warehouse readiness), then a single result event with the rows as objects:

{
  "type": "result",
  "data": [
    {
      "region": "EMEA",
      "order_date": "2025-01-01",
      "arr": 1200000,
      "revenue": 340000
    }
  ]
}

On failure it emits an error event instead.

Errors and behavior

Editing definitions.json is picked up on the next request — no server restart is needed. A previously malformed file that you fix likewise starts working on the next request.

Frontend usage

useAnalyticsQuery

React hook that subscribes to an analytics query over SSE and returns its latest result.

import { useAnalyticsQuery } from "@databricks/appkit-ui/react";

const { data, loading, error } = useAnalyticsQuery(
  queryKey,
  parameters,
  options,
);

Return type:

{
  data: T | null; // query result (typed array for JSON, TypedArrowTable for ARROW)
  loading: boolean; // true while the query is executing
  error: string | null; // error message, or null on success
  warehouseStatus: WarehouseStatus | null; // see "Warehouse readiness" below
}

Options:

Warehouse readiness

If the configured SQL warehouse is STOPPED or STARTING when a query is requested, the analytics plugin will:

1. Auto-start the warehouse (when STOPPED).
2. Poll the warehouse state and stream warehouse_status events over SSE until it reaches RUNNING.
3. Execute the SQL statement.

This means a cold start no longer freezes the UI on a stalled spinner. Both useAnalyticsQuery and useMetricView expose the latest status for their current request through warehouseStatus; render it to give users feedback:

import { useAnalyticsQuery } from "@databricks/appkit-ui/react";

function SpendTable() {
  const { data, loading, error, warehouseStatus } = useAnalyticsQuery(
    "spend_summary",
    params,
  );

  if (warehouseStatus && warehouseStatus.state !== "RUNNING") {
    return <div>Warehouse is {warehouseStatus.state.toLowerCase()}…</div>;
  }
  if (loading) return <div>Loading…</div>;
  if (error) return <div>{error}</div>;
  return <table>{/* render data */}</table>;
}

For both hooks, warehouseStatus resets to null when a request starts and remains there until the first status event arrives. After the server has observed the warehouse RUNNING once, subsequent requests within ~30s skip the readiness check entirely and warehouseStatus stays null, so the steady-state hot path isn't taxed any extra round-trips.

If the warehouse is DELETED/DELETING or fails to reach RUNNING within the configured timeout, the route emits an error event (surfaced via the error field).

Global readiness indicator

For dashboards with many charts a per-component spinner isn't enough — wiring the same "warehouse warming up" UI into every skeleton is repetitive. AppKit ships a small generic context (ResourceStatusProvider) + drop-in indicator (ResourceStatusIndicator) that any plugin can publish into; analytics warehouses are wired up automatically.

The indicator surfaces the worst pending status as a sonner toast, so it inherits sonner's animations, theming, and stacking. The component mounts its own <Toaster /> (top-right by default) and forwards its props (position, theme, richColors, …):

import {
  ResourceStatusIndicator,
  ResourceStatusProvider,
} from "@databricks/appkit-ui/react";

export function AppShell({ children }) {
  return (
    <ResourceStatusProvider>
      <ResourceStatusIndicator />
      {children}
    </ResourceStatusProvider>
  );
}

useAnalyticsQuery and useMetricView register themselves with the nearest provider, so no per-chart wiring is needed. The indicator renders only the <Toaster /> mount point while every resource is healthy; it pops a single sticky toast — toast.loading for cold starts, toast.error for unrecoverable states — keyed by the worst kind, and dismisses it when they all settle. Because the same provider is shared across resource kinds (warehouse, lakebase, model serving, …), a single indicator covers every plugin.

If you already render your own <Toaster /> for unrelated app toasts, drop the indicator and call useResourceStatusToaster() instead so resource-status toasts share that single Toaster:

import { useResourceStatusToaster, Toaster } from "@databricks/appkit-ui/react";

function App() {
  useResourceStatusToaster();
  return (
    <>
      <Toaster position="top-right" />
      <Routes />
    </>
  );
}

For a fully custom toast body, pass render (rendered through toast.custom):

<ResourceStatusIndicator
  render={(agg) => (
    <div className="rounded-lg border bg-background p-3 shadow">
      {agg.worst?.kind} {agg.worst?.state.toLowerCase()} ({agg.activeCount}{" "}
      waiting)
    </div>
  )}
/>

To override copy for a specific kind without rewriting the whole UI, pass renderers:

<ResourceStatusIndicator
  renderers={{
    warehouse: {
      title: () => "Spinning up your data",
      description: (_s, agg) => `${agg.affectedLabels.length} chart(s) waiting`,
    },
  }}
/>

Or build your own UI from the aggregate with useResourceStatus():

import { useResourceStatus } from "@databricks/appkit-ui/react";

// Worst across all kinds
const aggregate = useResourceStatus();
// Just warehouses
const warehouseOnly = useResourceStatus({ kind: "warehouse" });
// { worst, byKind, affectedLabels, activeCount, elapsedMs }

The provider is optional. Apps that don't mount it still get the per-hook warehouseStatus field and the hook works exactly as before.

Publishing your own resource status

Plugins (or your own code) can hook into the same provider for non-analytics resources — e.g. a Lakebase Postgres connection warming up, a model-serving endpoint cold-starting:

import { useResourceStatusPublisher } from "@databricks/appkit-ui/react";
import { useEffect, useId } from "react";

function useLakebaseReadiness() {
  const id = useId();
  const { publish, unpublish } = useResourceStatusPublisher(id, "lakebase", {
    kindHint: "lakebase",
  });

  useEffect(() => {
    publish({
      kind: "lakebase",
      state: "STARTING",
      severity: "pending",
      startedAt: Date.now(),
    });
    return () => unpublish();
  }, [publish, unpublish]);
}

Server config (in analytics({...})):

Example with loading/error/empty handling:

import { useAnalyticsQuery } from "@databricks/appkit-ui/react";
import { sql } from "@databricks/appkit-ui/js";
import { Skeleton } from "@databricks/appkit-ui";

function SpendTable() {
  const params = useMemo(
    () => ({
      startDate: sql.date("2025-01-01"),
      endDate: sql.date("2025-12-31"),
    }),
    [],
  );

  const { data, loading, error } = useAnalyticsQuery("spend_summary", params);

  if (loading) return <Skeleton className="h-32 w-full" />;
  if (error) return <div className="text-destructive">{error}</div>;
  if (!data?.length)
    return <div className="text-muted-foreground">No results</div>;

  return (
    <ul>
      {data.map((row) => (
        <li key={row.id}>
          {row.name}: ${row.cost_usd}
        </li>
      ))}
    </ul>
  );
}

Type-safe queries

Augment the QueryRegistry interface to get full type inference on parameters and results:

// shared/appkit-types/analytics.d.ts
declare module "@databricks/appkit-ui/react" {
  interface QueryRegistry {
    spend_summary: {
      name: "spend_summary";
      parameters: { startDate: string; endDate: string };
      result: Array<{ id: string; name: string; cost_usd: number }>;
    };
  }
}

See Type generation for automatic generation from SQL files.

Memoization

Always wrap parameters in useMemo to avoid refetch loops. The hook re-executes whenever the parameters reference changes:

// Good
const params = useMemo(() => ({ status: sql.string("active") }), []);
const { data } = useAnalyticsQuery("users", params);

// Bad - creates a new object every render, causing infinite refetches
const { data } = useAnalyticsQuery("users", { status: sql.string("active") });

useMetricView

React hook that measures a metric view over SSE — the client twin of POST /api/analytics/metric/:key. Instead of writing SQL, you pass the measures, dimensions, and filter as a structured request; the hook streams back rows with typed column names plus per-column display metadata.

import { useMetricView } from "@databricks/appkit-ui/react";

const { data, loading, error, errorCode, metadata, warehouseStatus } =
  useMetricView("revenue", {
    measures: ["arr", "mrr"],
    dimensions: ["created_at"],
    timeGrain: "month",
    timeDimension: "created_at",
    orderBy: [{ field: "created_at", direction: "ASC" }],
  });

When "revenue" is a key in the generated MetricRegistry (see Metric-view types), the measure/dimension names, the allowed timeGrain values, and the selected row keys are all inferred — passing an unknown measure is a type error. JSON_ARRAY preserves SQL scalar cells as strings and allows SQL NULL for every column, so data is typed as Array<{ arr: string | null; mrr: string | null; created_at: string | null }> | null; use metadata[col].type when intentionally parsing a value.

Time-series queries should explicitly order their selected time dimension ascending, as above. SQL result order is otherwise unspecified; chart helpers may normalize chronological data defensively, but consumers should not rely on that for query ordering.

Options:

Return type:

{
  data: T | null; // selected row keys with JSON_ARRAY string | null values
  loading: boolean; // true while the metric query is executing
  error: string | null; // sanitized human-readable message, or null on success
  errorCode: string | null; // stable upstream code (branch on this, not the message)
  metadata: Record<string, MetricViewColumnDisplay> | undefined; // per-column display metadata (see below)
  warehouseStatus: WarehouseStatus | null; // latest readiness status for the current request
}

Like useAnalyticsQuery, the option object is serialized (JSON.stringify) internally, so object/array literals passed fresh each render do not trigger a refetch as long as they serialize to the same string — you do not need to useMemo the options. (This is same-serialization, not deep structural equality: reordering keys within filter changes the string and does re-query. Hoisting measures/dimensions to module scope or memoizing is still fine, and keeps the arrays type-narrowed to their literal tuple.)

metadata is the per-column display metadata for only the columns you queried, scoped and carried in the SSE result payload. It is undefined when the server resolved no metadata (the metric key is unknown, or types have not been generated) — so always treat it as optional.

Metadata

The metric route stamps per-column display metadata (display_name, format, type, description) onto each result message. This metadata is build-generated by the metric-view type generator, which writes it to config/metric-views/metadata.generated.json beside your hand-authored definitions.json.

No wiring required. The plugin discovers the bundle the same way it discovers definitions.json, so analytics({}) is enough:

// server/index.ts
import { analytics, createApp, server } from "@databricks/appkit";

createApp({
  plugins: [
    server(),
    analytics({}),
    // …
  ],
});

Commit metadata.generated.json alongside your generated types — it is the runtime half of the same generation pass, and the route reads it from disk at request time.

This is pure response decoration: the metadata never enters the cache key and never changes the SQL. Every metric result message carries a metadata field scoped to the requested columns; when no bundle is present the message is byte-identical to a plain /query result and the hook's metadata is undefined. A missing or malformed bundle degrades to unlabeled columns and logs a warning — it never fails the query. Because the metadata rides on the payload, the client never has to import the generated file or hardcode a format string — it is payload-carried and client-agnostic.

To bypass the file entirely — an app that builds its metadata some other way, or pins it deliberately — pass analytics({ metricViewsMetadata }). An explicit value always wins over the discovered bundle.

Format utilities

@databricks/appkit-ui/js ships small, pure, tree-shakeable formatters that turn raw values + the metadata above into display strings. They take the format spec (or MetricViewColumnDisplay) as arguments — no React, no chart-library coupling — so they work in tables, tooltips, and chart configs alike.

The golden rule: source the format from metadata, never hand-type it. When metadata is undefined, metadata?.[col]?.format is undefined and formatValue degrades gracefully to a default:

import { formatLabel, formatValue } from "@databricks/appkit-ui/js";
import { useMetricView } from "@databricks/appkit-ui/react";

function RevenueTable() {
  const { data, metadata } = useMetricView("revenue", {
    measures: ["arr", "mrr"],
    dimensions: ["created_at"],
    timeGrain: "month",
    timeDimension: "created_at",
    orderBy: [{ field: "created_at", direction: "ASC" }],
  });
  const columns = ["created_at", "arr", "mrr"] as const;

  return (
    <table>
      <thead>
        <tr>
          {columns.map((col) => (
            // Header text from display_name (or a humanized fallback).
            <th key={col}>{formatLabel(col, metadata?.[col])}</th>
          ))}
        </tr>
      </thead>
      <tbody>
        {data?.map((row, i) => (
          <tr key={i}>
            {columns.map((col) => (
              // Format string comes from metadata, never hand-typed.
              <td key={col}>{formatValue(row[col], metadata?.[col]?.format)}</td>
            ))}
          </tr>
        ))}
      </tbody>
    </table>
  );
}

Feeding the format into charts

Because metadata[col].format is just a string on the payload, the same spec drives axis ticks and tooltips in any chart library.

AppKit charts — pass a valueFormatter to the built-in chart. The second argument is the measure field, so one callback can select the catalog format for each series. The chart applies it to its built-in value axis and per-series tooltips without replacing the internal ECharts yAxis or tooltip defaults:

import { formatValue } from "@databricks/appkit-ui/js";
import { LineChart, useMetricView } from "@databricks/appkit-ui/react";

function RevenueChart() {
  const { data, metadata } = useMetricView("revenue", {
    measures: ["arr", "mrr"],
    dimensions: ["created_at"],
    timeGrain: "month",
    timeDimension: "created_at",
    orderBy: [{ field: "created_at", direction: "ASC" }],
  });

  if (!data) return null;

  return (
    <LineChart
      data={data}
      xKey="created_at"
      yKey={["arr", "mrr"]}
      valueFormatter={(value, field) =>
        formatValue(value, metadata?.[field]?.format)
      }
    />
  );
}

When multiple series share one value axis, its ticks use the first yKey; each tooltip uses the matching series field.

The selected prop adds declarative emphasis only to bar, pie, and donut charts. Line, area, scatter, heatmap, and radar charts ignore it because category-selection semantics are not defined for those chart types.

Plotly — pass the numeric specifier as tickformat and the literal currency symbol as tickprefix. Keeping them separate is necessary because d3's $ marker is locale-driven and cannot represent arbitrary symbols:

import Plot from "react-plotly.js";
import { toD3Format } from "@databricks/appkit-ui/js";
import { useMetricView } from "@databricks/appkit-ui/react";

function RevenuePlot() {
  const { data, metadata } = useMetricView("revenue", {
    measures: ["arr"],
    dimensions: ["created_at"],
    timeGrain: "month",
    timeDimension: "created_at",
    orderBy: [{ field: "created_at", direction: "ASC" }],
  });
  const arrFormat = toD3Format(metadata?.arr?.format);
  // "€#,##0.00" → { specifier: ",.2f", prefix: "€" }

  return (
    <Plot
      data={[
        {
          type: "scatter",
          mode: "lines+markers",
          x: data?.map((r) => r.created_at) ?? [],
          y: data?.map((r) => r.arr) ?? [],
          name: metadata?.arr?.display_name ?? "arr",
        },
      ]}
      layout={{
        yaxis: {
          tickformat: arrFormat?.specifier,
          tickprefix: arrFormat?.prefix,
        },
        hoverlabel: { namelength: -1 },
      }}
    />
  );
}

ECharts — use the format spec inside axisLabel.formatter / tooltip.formatter via formatValue:

import ReactECharts from "echarts-for-react";
import { formatLabel, formatValue } from "@databricks/appkit-ui/js";
import { useMetricView } from "@databricks/appkit-ui/react";

function RevenueECharts() {
  const { data, metadata } = useMetricView("revenue", {
    measures: ["arr"],
    dimensions: ["created_at"],
    timeGrain: "month",
    timeDimension: "created_at",
    orderBy: [{ field: "created_at", direction: "ASC" }],
  });
  const arrFormat = metadata?.arr?.format;

  const option = {
    xAxis: { type: "category", data: data?.map((r) => r.created_at) ?? [] },
    yAxis: {
      type: "value",
      axisLabel: { formatter: (v: number) => formatValue(v, arrFormat) },
    },
    tooltip: {
      trigger: "axis",
      valueFormatter: (v: number) => formatValue(v, arrFormat),
    },
    series: [
      {
        name: formatLabel("arr", metadata?.arr),
        type: "line",
        data: data?.map((r) => r.arr) ?? [],
      },
    ],
  };

  return <ReactECharts option={option} />;
}

In both cases the format string originates from the server-injected metadata and is never written into the component — swapping the YAML format attribute on the metric view re-flows every axis, tooltip, and table cell without a client change.