Datasets & Fields

Datasets

Datasets represent your data sources. A dataset's source says where its rows come from: a table or view (kind: table) or a SQL query (kind: sql).

Table Datasets

A table dataset reads a database table or view:

datasets:
  - name: orders
    label: "Orders"
    description: "Customer order transactions"
    analyticsRole: fact
    source:
      kind: table
      connectionId: "conn_abc123"
      connectionType: PostgreSQL
      schema: public
      table: orders
    primaryKey: [order_id]
    fields:
      identifiers: []
      facts: []
      dimensions: []

SQL Datasets

A SQL dataset (also called a virtual dataset) reads the rows a query returns:

datasets:
  - name: monthly_revenue
    label: "Monthly Revenue"
    analyticsRole: snapshot
    source:
      kind: sql
      connectionId: "conn_abc123"
      connectionType: PostgreSQL
      sql: |
        SELECT
          DATE_TRUNC('month', order_date) AS month,
          SUM(amount) AS revenue,
          COUNT(*) AS order_count
        FROM public.orders
        GROUP BY DATE_TRUNC('month', order_date)
    primaryKey: [month]
    fields:
      dimensions:
        - name: month
          label: "Month"
          dataType: date
          sourceField: month
      facts:
        - name: revenue
          label: "Revenue"
          dataType: number
          sourceField: revenue
          defaultAggregate: SUM
        - name: order_count
          label: "Order Count"
          dataType: number
          sourceField: order_count
          defaultAggregate: SUM

Use SQL datasets for pre-aggregated data, complex transformations, or combining tables with custom logic.

Dataset Field Reference

source properties:

Supported connectionType values: PostgreSQL, MySQL, MSSQL, BigQuery, Redshift, Snowflake, clickhouse, S3, GoogleSheets, FileUpload, API

Dataset Analytics Role

analyticsRole tells Semaphor what kind of dataset this is. It is not a permission setting and it does not create joins by itself. It helps the semantic query planner, MCP tools, and relationship diagnostics choose safer defaults.

Use fact for the dataset that owns the metric grain, and dimension or lookup for datasets joined to enrich that fact. For example, a supplier concentration analysis should usually start from fact_purchase_line and join dim_supplier, not start from every supplier and join purchases.

Keys

A key is a list of field names whose values identify one row. Semaphor uses keys to plan joins that don't double count.

• primaryKey is the dataset's main key, for example [order_id], or [order_id, line_number] for a composite key.
• uniqueKeys lists any other keys, for example a natural key like [order_number].

datasets:
  - name: order_lines
    label: "Order Lines"
    primaryKey: [order_id, line_number]
    uniqueKeys:
      - [line_uuid]

In the editor, marking an identifier as Primary key adds it to primaryKey. In a template, primaryKey is the declaration that counts, so set it on every dataset that has a key.

Primary Date Field

Each dataset can declare one date dimension as its primary date field. This is the dataset's canonical date: the one dashboard-level features use when they need a "default" date to filter on.

Today it powers the Global Date Filter: a single date range set once on a dashboard that applies to every eligible card, without per-card setup.

Declare it with primaryDateField on the dataset, referencing a dimension by name:

datasets:
  - name: orders
    label: "Orders"
    primaryDateField: order_date
    fields:
      dimensions:
        - name: order_date
          label: "Order Date"
          dataType: date
          sourceField: order_date
        - name: ship_date
          label: "Ship Date"
          dataType: datetime
          sourceField: ship_date

In the semantic domain editor UI, open a dataset's dimensions and toggle Set as primary date field on the column you want. The toggle only appears on date or datetime dimensions.

Rules

A few things to keep in mind when setting a primary date field:

• The field you reference must exist on the dataset.
• It must be a date or datetime type. Other data types aren't eligible, and the toggle won't appear on them.
• Renames are safe. If you rename the field later, the primaryDateField reference updates automatically, so no dashboard re-wiring is needed.

Each dataset has one primary date field at a time. To switch to a different field, toggle Set as primary date field on the new date dimension (the previous one clears automatically), or update primaryDateField directly in YAML.

See Global Date Filter for the dashboard-side feature this unlocks.

Fields

Every dataset has three field lists: identifiers, facts, and dimensions. All three work row by row. Aggregates live in the domain's metrics list (see Metrics below).

A field name is unique within its dataset, across all three lists and the dataset's metrics.

Data Types

Every field has a logical dataType. You can also record the database's exact type in nativeDataType.

facts:
  - name: amount
    dataType: number
    nativeDataType: "numeric(12,2)"
    sourceField: amount

dataType takes one of the logical types above; a 2.1 template with a database type there (for example timestamp with time zone) is refused when you save. Templates in the 2.0 shape still accept one: Semaphor stores the logical type in dataType and moves the original value to nativeDataType. Sync data types on a dataset reads exact types from the database again.

Identifiers

Identifiers are primary and foreign key fields used for joins:

identifiers:
  - name: order_id
    label: "Order ID"
    dataType: number
    sourceField: order_id
    type: primary

  - name: customer_id
    label: "Customer ID"
    dataType: number
    sourceField: customer_id
    type: foreign

Dimensions

Dimensions are attributes you group and filter by. They appear in GROUP BY clauses:

dimensions:
  - name: order_date
    label: "Order Date"
    dataType: date
    sourceField: order_date
    granularity: month
    format:
      type: temporal_bucket
      presentation:
        mode: pattern
        pattern: MMM YYYY

  - name: status
    label: "Status"
    dataType: string
    sourceField: status

  - name: region
    label: "Region"
    dataType: string
    sourceField: region
    isDisplayField: true

  - name: order_month
    label: "Order Month"
    dataType: date
    expression: "DATE_TRUNC('month', order_date)"

Grouped temporal formats

Temporal presentation is structured and separate from the canonical grouped value. Use one of these shapes:

# Viewer preference for this grain
format:
  type: temporal_bucket
  presentation:
    mode: auto

# Grain-specific preset
format:
  type: temporal_bucket
  presentation:
    mode: preset
    preset: quarter_short

For a bounded custom pattern:

format:
  type: temporal_bucket
  presentation:
    mode: pattern
    pattern: MMM YYYY

Preset values are week_of_short, week_of_long, quarter_short, quarter_long, fiscal_quarter_short, and fiscal_year. Pattern tokens are YYYY, YY, MMMM, MMM, MM, DD, HH, mm, and ss; Semaphor also validates that the token combination matches the declared grain. A format without granularity, a sub-day grain on a date, or a temporal format on a non-temporal dimension is rejected.

Card-level presentation wins over semantic presentation. When neither is explicit, Semaphor applies the viewer's Display formats preferences.

Facts

A fact is a row-level quantity you aggregate. It reads a column (sourceField) or computes a row-level expression, and defaultAggregate says how a card rolls it up when nothing else is chosen:

facts:
  - name: amount
    label: "Revenue"
    dataType: number
    sourceField: amount
    defaultAggregate: SUM

  - name: quantity
    label: "Quantity"
    dataType: number
    sourceField: quantity
    defaultAggregate: SUM

  - name: unit_price
    label: "Unit Price"
    dataType: number
    sourceField: unit_price
    defaultAggregate: AVG

  - name: discounted_total
    label: "Discounted Total"
    dataType: number
    expression: "amount * (1 - discount_rate)"
    defaultAggregate: SUM

Choose the aggregate that matches the business meaning:

Dashboards and MCP can still request a different aggregate for a specific analysis. For example, a fact modeled with defaultAggregate: SUM can be analyzed with an explicit AVG when the user asks for average margin instead of total margin. That choice is query-specific; it does not change the semantic model.

Metrics

Metrics aggregate rows. They live in the domain's top-level metrics list, not inside a dataset. A metric that aggregates one dataset names it with dataset:

metrics:
  - name: revenue
    dataset: orders
    label: "Revenue"
    expression: "SUM(amount)"
    dataType: number

  - name: average_order_value
    dataset: orders
    label: "Average Order Value"
    expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"
    dataType: number

A metric that combines inputs from several datasets uses inputs instead. See Calculated Metrics for both forms, and Choosing the Right Type for where a new calculation goes.

Context

Context tells agents and natural-language questions how to read an object: what it means, what people call it, and what it answers. You can add it to the domain, a dataset, a field, a metric, a composed dimension, or a relationship. In the editor it's the Context section; in a template it's aiContext.

datasets:
  - name: orders
    label: "Orders"
    aiContext:
      instructions: "One row per order. Cancelled orders have status = 'cancelled'; exclude them from revenue questions."
      synonyms: [purchases, sales orders]
      examples:
        - "How many orders did we ship last week?"
        - "Which region had the most cancelled orders?"

A plain string is shorthand for instructions:

facts:
  - name: amount
    dataType: number
    sourceField: amount
    aiContext: "Order amount in USD after discounts."

A value over a limit is refused when you save (ai_context_too_long, ai_context_too_many). Empty entries are dropped, and repeated synonyms are kept once. Context is shown to the same audiences as the object's description.

Complete Fields Example

Putting it all together for an orders dataset:

datasets:
  - name: orders
    label: "Orders"
    analyticsRole: fact
    source:
      kind: table
      connectionId: "conn_abc123"
      connectionType: PostgreSQL
      schema: public
      table: orders
    primaryKey: [order_id]
    primaryDateField: order_date
    fields:
      identifiers:
        - name: order_id
          label: "Order ID"
          dataType: number
          sourceField: order_id
          type: primary
        - name: customer_id
          label: "Customer ID"
          dataType: number
          sourceField: customer_id
          type: foreign
      dimensions:
        - name: order_date
          label: "Order Date"
          dataType: date
          sourceField: order_date
          granularity: day
        - name: status
          label: "Status"
          dataType: string
          sourceField: status
        - name: channel
          label: "Sales Channel"
          dataType: string
          sourceField: channel
        - name: order_quarter
          label: "Order Quarter"
          dataType: date
          expression: "DATE_TRUNC('quarter', order_date)"
      facts:
        - name: amount
          label: "Revenue"
          dataType: number
          nativeDataType: "numeric(12,2)"
          sourceField: amount
          defaultAggregate: SUM
        - name: quantity
          label: "Quantity"
          dataType: number
          sourceField: quantity
          defaultAggregate: SUM