Queries

Data App queries are server-side analytics specs. They describe the question you want answered; Semaphor compiles and executes the query through governed analytics.

Query Types

Prefer semantic query types first. Use SQL only when the request cannot be represented by the governed builders or the user explicitly asks for SQL.

Metric Query

Use metric for KPIs.

const revenueKpi = semaphor.metric({
  id: 'revenue_kpi',
  label: 'Revenue',
  source: orders,
  measures: [revenue],
  primaryMeasure: revenue,
  dateField: orderDate,
  comparison: {
    kind: 'previous_period',
  },
});

function RevenueKpi() {
  const result = useSemaphorQuery(revenueKpi);

  if (result.isLoading) return <div>Loading...</div>;
  if (result.error) return <div>{result.error.message}</div>;

  return (
    <div>
      <div>{result.primaryValue}</div>
      <div>{result.deltaPercent}% vs previous period</div>
    </div>
  );
}

Use primaryMeasure when a query includes more than one measure and the UI needs to know which one is the hero value.

Metric comparison fields such as comparisonValue, delta, and deltaPercent describe the query's primaryMeasure. If a dashboard shows several headline KPI cards and each card needs its own period-over-period comparison, use separate single-measure semaphor.metric queries rather than one multi-measure query.

A Data App query sets its own comparison and doesn't pick up a viewer's Global Date selection. Everything else works the same as on a dashboard: how the date window and baseline are chosen, and how zeros, missing history, and favorable direction are handled, all follow Metric Comparisons.

Records Query

Use records for chart series, grouped breakdowns, and tables.

const revenueByCampaign = semaphor.records({
  id: 'revenue_by_campaign',
  label: 'Revenue by Campaign',
  source: orders,
  fields: [campaignName, revenue],
  orderBy: {
    field: revenue,
    direction: 'desc',
  },
  limit: 10,
});

This produces a grouped query similar to:

select
  dim_campaign.campaign_name,
  sum(fact_orders.revenue) as revenue
from fact_orders
left join dim_campaign
  on fact_orders.campaign_id = dim_campaign.campaign_id
group by dim_campaign.campaign_name
order by revenue desc
limit 10

Your app did not write this SQL. The source-bearing fields and semantic relationship model let Semaphor compile it.

Trend Query

Use a date field and timeGrain for trends.

const monthlyRevenue = semaphor.records({
  id: 'monthly_revenue',
  label: 'Monthly Revenue',
  source: orders,
  dateField: orderDate,
  timeGrain: 'month',
  fields: [orderDate, revenue],
  orderBy: {
    field: orderDate,
    direction: 'asc',
  },
  limit: 24,
});

The monthly row value remains the canonical month bucket. Its result column carries temporalBucket metadata, including the month grain and governed Calendar. A presentation surface can format that value using applicable authored or viewer policy, but the structured Data App result stays canonical; order, filters, and interactions must use the value and columns[].key.

Matrix Query

Use matrix for pivot tables. For example, campaign channel by month.

const revenuePivot = semaphor.matrix({
  id: 'revenue_by_channel_month',
  label: 'Revenue by Channel and Month',
  source: orders,
  rows: [campaignChannel],
  columns: [
    {
      field: orderDate,
      grain: 'month',
      format: {
        type: 'temporal_bucket',
        presentation: { mode: 'pattern', pattern: 'MMM YYYY' },
      },
    },
  ],
  values: [
    {
      field: revenue,
      aggregate: 'SUM',
    },
  ],
  totals: {
    rows: true,
    columns: true,
  },
});

Matrix axes can use related dimensions and dates when the semantic model proves the relationship. A temporal axis requires an explicit supported grain. Its optional structured format controls only display; omit it to use semantic and viewer-preference fallback.

Matrix results use schemaVersion: 2. Canonical member paths—not rendered headers—own hierarchy identity, sorting, expansion, and totals. A genuine null member remains distinct from subtotal and grand-total roles. Data Apps receive canonical structured output; they should not localize values before using them as identity.

SQL Query Ownership

semaphor.sql is a first-class explicit SQL path, but its result is code-owned rather than governed by a retained semantic/card configuration. Semaphor does not infer grouped temporal, pivot, or Matrix identity from SQL result strings. If the author needs a governed grain, hierarchy, interaction, or semantic presentation contract, use the semantic builders above.

This is separate from export presentation: a dashboard-card CSV/PDF is a formatted customer-facing artifact, while an explicitly raw SQL-editor export stays raw. See Display formats.

Analysis Query

Use analysis when the app needs explanation-style analytics, such as period-change drivers.

const revenueDrivers = semaphor.analysis({
  id: 'revenue_drivers',
  label: 'Revenue Drivers',
  source: orders,
  measures: [revenue],
  primaryMeasure: revenue,
  dimensions: [campaignName],
  dateField: orderDate,
  analysis: {
    kind: 'period_change',
  },
  driverMode: 'positive_and_negative',
});

The result can include records, drivers, changes, summaries, and execution metadata. Row-shaped analysis payloads are normalized into result.resultSets, for example result.resultSets?.changes?.records or result.resultSets?.drivers?.records. Treat result.executionResult as the authoritative status, validation, coverage, and lineage path.

Input Options Query

Use inputOptions for dropdowns and searchable filters.

const campaignOptions = semaphor.inputOptions({
  id: 'campaign_options',
  inputId: 'campaign',
  label: 'Campaign options',
  source: campaign,
  labelField: campaignName,
  valueField: campaignId,
  searchField: campaignName,
  limit: 100,
});

Option queries are also governed server-side queries. They should not load every possible value into the browser.

SQL Fallback Query

Use sql only as an explicit escape hatch.

const customWindow = semaphor.sql({
  id: 'custom_window',
  label: 'Custom Window Analysis',
  source: semaphor.source.sql({
    connectionId: 'conn_123',
    dialect: 'postgres',
    label: 'Analytics warehouse',
  }),
  sql: `
    select
      month,
      revenue,
      revenue - lag(revenue) over (order by month) as revenue_delta
    from monthly_revenue
    order by month
  `,
  rationale:
    'Uses a custom window function that is not represented by semantic records yet.',
  limit: 100,
});

SQL should be bounded, permissioned, and documented. Normal KPIs, tables, trends, filters, joins, and pivots should use the semantic builders when possible.

Loading And Error States

Every visual should handle loading and errors.

function Insight() {
  const result = useSemaphorQuery(revenueByCampaign);

  if (result.isLoading && !result.isStale) return <Skeleton />;
  if (result.error) return <ErrorMessage error={result.error} />;
  if (result.records.length === 0) return <EmptyState />;

  return (
    <>
      {result.isStale ? <RefreshingBadge /> : null}
      {result.isPartial ? <PartialResultWarning result={result} /> : null}
      <Chart rows={result.records} columns={result.columns} />
    </>
  );
}

For generated Data Apps, prefer the generated view accessors:

const rows = rowsForView.revenueByCampaign(result);
const metrics = metricValuesForView.revenueKpi(metricResult);

Common Mistakes