Agent Guide

Build semantic domain templates programmatically with complete schema reference and validation rules

This guide is for AI agents and automation scripts that generate domain templates as JSON. It provides the complete schema specification, construction rules, and validation checklist needed to produce valid templates programmatically.

Approach

Domain templates can be applied in two ways:

Template tab: Generate JSON, paste it into the Domain Editor's Template tab, and save
Public API: PUT /api/v1/domains/{domainId}/template with the template in the request body (see Domain Templates API)

Both paths accept the same template format and run the same validation. Generate templates with schemaVersion: "2.1".

Templates saved in the 2.0 shape

Domains saved before version 2.1 keep working: they read as 2.1, and they are stored as 2.1 the next time they are saved. GET on the template API always returns 2.1. The API still accepts 2.0 bodies (calculatedMetrics, calculatedDimensions, datasets with type or fields.metrics, relationships with sourceDataset) and upgrades them on save, but refuses a body that mixes 2.0 and 2.1 keys (mixed_schema_versions). The relationship endpoints accept only from/to.

Template JSON Schema

Top-Level Structure

{
  "schemaVersion": "2.1",
  "aiContext": { "instructions": "..." },
  "datasets": [],
  "relationships": [],
  "metrics": [],
  "composedDimensions": [],
  "grainMappings": []
}

Field	Type	Required	Default	Description
`schemaVersion`	`"2.1"`	Yes		Schema version
`aiContext`	`AiContext`	No		Context for the whole domain
`datasets`	`SemanticDataset[]`	No	`[]`	Dataset definitions
`relationships`	`Relationship[]`	No	`[]`	Join definitions
`metrics`	`Metric[]`	No	`[]`	Every metric: `DatasetMetric` and `ComposedMetric`
`composedDimensions`	`ComposedDimension[]`	No	`[]`	Domain-level row expressions over inputs
`grainMappings`	`GrainMapping[]`	No		Cross-dataset dimension alignment

SemanticDataset

{
  "name": "orders",
  "label": "Orders",
  "description": "Customer order transactions",
  "aiContext": "One row per order. Exclude status = 'cancelled' from revenue.",
  "analyticsRole": "fact",
  "source": {
    "kind": "table",
    "connectionId": "conn_abc123",
    "connectionType": "PostgreSQL",
    "schema": "public",
    "table": "orders"
  },
  "primaryKey": ["order_id"],
  "uniqueKeys": [["order_number"]],
  "primaryDateField": "order_date",
  "fields": {
    "identifiers": [],
    "facts": [],
    "dimensions": []
  }
}

Field	Type	Required	Constraints
`name`	string	Yes	Unique within domain
`label`	string	Yes	Display name
`description`	string	No
`aiContext`	`AiContext`	No
`analyticsRole`	enum	No	`"fact"`, `"dimension"`, `"event"`, `"snapshot"`, `"lookup"`, `"bridge"`, or `"unknown"`
`source`	`DatasetSource`	Yes	Where rows come from
`primaryKey`	string[]	No	Field names that identify one row. Defaults to `[]`. Set it for fan-out detection
`uniqueKeys`	string[][]	No	Other field-name lists that identify one row
`primaryDateField`	string	No	Name of a date or datetime dimension
`fields`	object	Yes	Contains `identifiers`, `facts`, `dimensions`

analyticsRole is semantic planning metadata. It helps governed query execution and MCP diagnostics understand whether a dataset is a fact table, dimension, snapshot, lookup, bridge, or event table. It does not create relationships or grant access by itself.

DatasetSource

{ "kind": "table", "connectionId": "conn_abc123", "connectionType": "PostgreSQL", "database": "analytics", "schema": "public", "table": "orders" }
{ "kind": "sql", "connectionId": "conn_abc123", "connectionType": "PostgreSQL", "sql": "SELECT ... FROM public.orders" }

Field	Type	Required	Constraints
`kind`	enum	Yes	`"table"` or `"sql"`
`connectionId`	string	Yes	Must match an existing connection
`connectionType`	enum	Yes	See connection types below
`database`	string	No	Database, catalog, or project (BigQuery, Snowflake)
`schema`	string	No	Database schema
`table`	string	No	Table or view name for `kind: "table"`. Defaults to the dataset name
`sql`	string	For `kind: "sql"`	The query that returns the rows. Fully qualify tables; no trailing semicolon

Connection types: "PostgreSQL", "MySQL", "MSSQL", "BigQuery", "Redshift", "Snowflake", "clickhouse", "S3", "GoogleSheets", "FileUpload", "API", "none"

Common Field Properties

Identifiers, facts, and dimensions share these properties:

Field	Type	Required	Constraints
`name`	string	Yes	Unique within the dataset, across all field lists and the dataset's metrics
`label`	string	No	Display name
`description`	string	No
`aiContext`	`AiContext`	No
`dataType`	enum	Yes	Logical type: `"string"`, `"number"`, `"boolean"`, `"date"`, `"datetime"`, `"json"`, `"geo"`, `"unknown"`
`nativeDataType`	string	No	The database's exact type, e.g. `"numeric(12,2)"` or `"timestamp with time zone"`
`sourceField`	string	See below	Physical column name
`hidden`	boolean	No
`tags`	string[]	No

Put the logical type in dataType and the database type in nativeDataType. A 2.1 template must use a logical type in dataType; a database type there is refused with a SCHEMA error. A template in the 2.0 shape, and the inline metric and dimension endpoints, still accept a database type in dataType: Semaphor stores the logical type there and moves the original value to nativeDataType.

IdentifierField

{
  "name": "order_id",
  "label": "Order ID",
  "description": "Unique order identifier",
  "dataType": "number",
  "nativeDataType": "bigint",
  "sourceField": "order_id",
  "type": "primary"
}

Field	Type	Required	Constraints
`type`	enum	Yes	`"primary"` (a column of the dataset's key) or `"foreign"`

Declare the key itself with the dataset's primaryKey. Join planning reads primaryKey, not the identifier type.

DimensionField

{
  "name": "order_date",
  "label": "Order Date",
  "dataType": "date",
  "sourceField": "order_date",
  "granularity": "month",
  "format": {
    "type": "temporal_bucket",
    "presentation": {
      "mode": "pattern",
      "pattern": "MMM YYYY"
    }
  },
  "isDisplayField": false,
  "hidden": false,
  "tags": []
}

Field	Type	Required	Constraints
`sourceField`	string	Conditional	Physical column name. Required when there is no `expression`
`expression`	string	Conditional	Row-level SQL expression. Required when there is no `sourceField`. See Inline Expressions
`granularity`	enum	No	`"second"`, `"minute"`, `"hour"`, `"day"`, `"week"`, `"month"`, `"quarter"`, or `"year"`; valid only for `date`/`datetime`, and date-only fields reject sub-day grains
`format`	object	No	Structured `temporal_bucket` presentation. Requires `granularity` and must be compatible with that grain. Omit it, or use `mode: "auto"`, to use resolved viewer preferences.
`isDisplayField`	boolean	No	Default display label for dataset

Every dimension needs sourceField or expression. A dimension expression is row-level: aggregate functions are refused (aggregate_in_row_field). It can reference the dataset's identifiers and dimensions, plus the column in its own sourceField.

format.presentation is one of:

{ "mode": "auto" }
{ "mode": "preset", "preset": "quarter_short" }
{ "mode": "pattern", "pattern": "MMM YYYY" }

Supported presets are week_of_short, week_of_long, quarter_short, quarter_long, fiscal_quarter_short, and fiscal_year. Patterns use the bounded Semaphor token vocabulary (YYYY, YY, MMMM, MMM, MM, DD, HH, mm, and ss) rather than arbitrary date-library tokens. Presets and patterns are grain-specific and invalid combinations are rejected when the domain is validated.

An explicit card format overrides the semantic format. If neither is explicit, Semaphor uses the viewer's Display formats preferences.

FactField

{
  "name": "amount",
  "label": "Revenue",
  "dataType": "number",
  "nativeDataType": "numeric(12,2)",
  "sourceField": "amount",
  "defaultAggregate": "SUM",
  "unit": "USD",
  "isDefault": false,
  "hidden": false,
  "tags": []
}

Field	Type	Required	Constraints
`sourceField`	string	Conditional	Physical column. Required when there is no `expression`
`expression`	string	Conditional	Row-level SQL expression. Required when there is no `sourceField`
`defaultAggregate`	enum	Recommended	Default rollup: `"SUM"`, `"COUNT"`, `"AVG"`, `"MIN"`, `"MAX"`, `"MEDIAN"`, or `"DISTINCT"`
`format`	string \| object	No	Display format hint
`unit`	string	No	Unit label
`isDefault`	boolean	No	Mark as the dataset's default measure
`filters`	array	No	Row conditions applied whenever the fact is aggregated. Column facts only
`comparisonPolarity`	enum	No	`"higher_is_better"`, `"lower_is_better"`, `"neutral"`

A fact is a row-level quantity. Its expression never aggregates: SUM(...) in a fact is refused (aggregate_in_row_field) and belongs in a DatasetMetric.

defaultAggregate is the semantic default used by dashboards, MCP semaphor_analyze, and other governed query paths. New generated templates should include it; a fact without one is treated as SUM.

Common choices:

Fact meaning	Aggregate
Additive amount, quantity, value, cost, revenue, margin, duration, or weight	`"SUM"`
Count or countable indicator	`"COUNT"`
Rate, ratio, percentage, score, price, or per-unit measurement	`"AVG"`
Highest or lowest observed value	`"MAX"` or `"MIN"`
Median-sensitive distribution	`"MEDIAN"`
Unique-value count	`"DISTINCT"`

Metric

Every metric lives in the top-level metrics list in one of two forms. A metric with both dataset and inputs, or neither, is refused.

DatasetMetric aggregates one dataset's rows:

{
  "name": "average_order_value",
  "dataset": "orders",
  "label": "Average Order Value",
  "description": "Revenue per order",
  "expression": "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)",
  "dataType": "number",
  "format": { "type": "currency", "decimals": 2, "currency": "USD" },
  "aiContext": { "synonyms": ["AOV"] },
  "tags": ["kpi"]
}

Field	Type	Required	Constraints
`name`	string	Yes	Unique within its dataset (across fields and metrics)
`dataset`	string	Yes	The dataset whose rows it aggregates
`expression`	string	Yes	Aggregate expression using that dataset's field and metric names
`dataType`	enum	Yes	Logical type, usually `"number"`
`nativeDataType`	string	No
`label`, `description`, `aiContext`		No
`format`, `unit`, `comparisonPolarity`, `isDefault`, `hidden`, `tags`		No

ComposedMetric combines named inputs:

{
  "name": "net_revenue",
  "label": "Net Revenue",
  "expression": "{sales} - COALESCE({returned}, 0)",
  "inputs": {
    "sales": { "metric": "revenue", "dataset": "orders" },
    "returned": { "dataset": "returns", "field": "amount", "aggregate": "SUM" }
  },
  "metricType": "derived",
  "format": { "type": "currency", "decimals": 2, "currency": "USD" }
}

Field	Type	Required	Constraints
`name`	string	Yes	Unique among composed metrics
`expression`	string	Yes	Min 1 character. Uses `{input_name}` tokens
`inputs`	object	Yes	Map of token name to an input (below)
`defaultAggregate`	enum	Conditional	Required when the expression works row by row
`metricType`	enum	No	`"base"`, `"derived"` (default), `"calculated"`
`aggregationStrategy`	enum	No	`"default"`, `"symmetric_aggregate"`, `"aggregate_then_join"`, `"weighted"`
`filters`	array	No	Each `{ "dataset", "field", "operator", "value" }`
`label`, `description`, `aiContext`, `format`, `comparisonPolarity`, `hidden`, `tags`		No

Input types:

Input Type	Shape	Use Case
Field reference	`{ "dataset": "...", "field": "...", "aggregate": "SUM" }`	A field (identifier, fact, or dimension) of a dataset
Dataset metric reference	`{ "metric": "...", "dataset": "..." }`	A `DatasetMetric`
Composed metric reference	`{ "metric": "..." }`	Another `ComposedMetric`

Aggregate values: "SUM", "COUNT", "AVG", "MIN", "MAX", "MEDIAN", "DISTINCT"

ComposedDimension

{
  "name": "order_month",
  "label": "Order Month",
  "description": "Monthly time grouping",
  "expression": "DATE_TRUNC('month', {order_date})",
  "inputs": {
    "order_date": { "dataset": "orders", "field": "order_date" }
  },
  "dataType": "date"
}

Field	Type	Required	Constraints
`name`	string	Yes	Unique among composed dimensions
`label`	string	Yes	Display name
`description`	string	No
`aiContext`	`AiContext`	No
`expression`	string	Yes	SQL expression with `{input_name}` tokens
`inputs`	object	Yes	Map of name to field reference or `{ "dimension": "..." }`
`dataType`	enum	No	`"string"`, `"number"`, `"boolean"`, `"date"`, `"datetime"`
`grainMapping`	object	No	Map of dataset name to column name

Generate new row-level groupings as dimensions with an expression on their dataset. Use composed dimensions when a grouping needs a grain mapping across datasets.

Relationship

{
  "id": "rel_001",
  "name": "orders_to_customers",
  "from": { "dataset": "orders", "fields": ["customer_id"] },
  "to": { "dataset": "customers", "fields": ["id"] },
  "cardinality": "many_to_one",
  "defaultJoinType": "LEFT",
  "isAutoJoin": true,
  "joinPriority": 1,
  "discoveredBy": "user",
  "confidence": "high",
  "isActive": true,
  "description": "Orders belong to customers"
}

Field	Type	Required	Constraints
`id`	string	No	Auto-generated if omitted
`name`	string	Yes	Unique name
`from`	`{ "dataset", "fields" }`	Yes	The many side. Dataset must exist in `datasets`; min 1 field
`to`	`{ "dataset", "fields" }`	Yes	The side whose fields identify one row. Same number of fields as `from`
`cardinality`	enum	Yes	`"many_to_one"`, `"one_to_one"`, `"many_to_many"`
`defaultJoinType`	enum	Yes	`"INNER"`, `"LEFT"`, `"RIGHT"`, `"FULL"`
`isAutoJoin`	boolean	Yes	Must be `true` for automatic join resolution
`joinPriority`	number	No	Higher = preferred when multiple paths exist
`discoveredBy`	enum	Yes	`"user"`, `"auto"`, `"fk_constraint"`, `"ai"`, `"agent_model_repair"`
`confidence`	enum	No	`"high"`, `"medium"`, `"low"`
`isActive`	boolean	Yes	Set `false` to disable
`description`	string	No
`aiContext`	`AiContext`	No

AiContext

Context for agents and natural-language questions. Allowed on the domain, datasets, fields, metrics, composed dimensions, and relationships. A plain string is shorthand for { "instructions": "..." }.

{
  "instructions": "Use for questions about recurring revenue. Excludes one-time fees.",
  "synonyms": ["MRR", "monthly recurring revenue"],
  "examples": ["What was MRR by plan last month?"]
}

Field	Type	Limit
`instructions`	string	2,000 characters
`synonyms`	string[]	50 entries, 100 characters each
`examples`	string[]	10 entries, 300 characters each

Over a limit, the save is refused with ai_context_too_long or ai_context_too_many. Empty strings are dropped and synonyms are de-duplicated (case-insensitive).

FormatSpec

{
  "type": "currency",
  "decimals": 2,
  "currency": "USD",
  "prefix": "$",
  "suffix": "",
  "thousandsSeparator": true
}

Field	Type	Required	Constraints
`type`	enum	Yes	`"number"`, `"currency"`, `"percentage"`, `"date"`, `"string"`
`decimals`	integer	No	0-10
`currency`	string	No	Currency code (e.g., `"USD"`)
`prefix`	string	No
`suffix`	string	No
`thousandsSeparator`	boolean	No	Default `true`

GrainMapping

{
  "sourceDimension": "order_month",
  "targetDataset": "returns",
  "targetColumn": "return_date"
}

Field	Type	Required	Description
`sourceDimension`	string	Yes	Name of a composed dimension
`targetDataset`	string	Yes	Dataset to map the dimension to
`targetColumn`	string	Yes	Column in the target dataset

Construction Rules

Follow these rules to produce valid templates:

Naming

Dataset name: unique within the domain, use snake_case
Field and dataset metric name: unique within its dataset, across identifiers, facts, dimensions, and that dataset's metrics
Composed metric and composed dimension name: unique within their list, use snake_case
Relationship name: descriptive (e.g., orders_to_customers)

Where Each Calculation Goes

Row-level quantity to aggregate (amount_cents / 100): a fact with expression and defaultAggregate
Row-level attribute to group by (DATE_TRUNC('month', order_date)): a dimension with expression
Aggregate over one dataset (SUM(amount) / COUNT(DISTINCT order_id)): a DatasetMetric
Combines datasets, or builds on composed metrics: a ComposedMetric

Semaphor checks this on save. A misplaced calculation is refused with aggregate_in_row_field, row_expression_in_dataset_metric, or dataset_metric_references_other_dataset.

Required Field Combinations

source.kind: "sql" datasets must have sql
Facts and dimensions must have sourceField or expression (at least one)
New facts should include defaultAggregate
Dataset metrics must have dataset, expression, and dataType
Composed metrics must have expression and inputs
Identifiers must have type set to "primary" or "foreign"
Relationships must have name, from, to, cardinality, defaultJoinType, isAutoJoin, discoveredBy, and isActive

Token Rules (composed metrics and dimensions)

Token names in expressions ({name}) must exactly match keys in the inputs map
Token names are case-sensitive
Every token in the expression must have a corresponding input
Every input should be referenced by at least one token (unused inputs produce warning E006)
No braces {} inside SQL string literals (parser limitation)
No escape sequences like {{ or }}

Aggregation Rules

A DatasetMetric expression must aggregate (SUM(amount), or another dataset metric by name)
A fact or dimension expression must not aggregate
In a composed metric, if the expression wraps a token in an aggregate (SUM({amount})), the input does not need aggregate
If a composed metric uses a bare field token ({total_revenue} / {order_count}), each field input must specify aggregate
Metric inputs ({ "metric": "..." }) never need aggregate: the referenced metric provides its own

Reference Rules

A fact expression can reference fields of its dataset, plus its own sourceField column
A dimension expression can reference identifiers and dimensions of its dataset, plus its own sourceField column
A dataset metric can reference fields and other dataset metrics of its own dataset only
Composed metrics can reference fields, dataset metrics ({ "metric", "dataset" }), and other composed metrics ({ "metric" })
Composed dimensions can reference fields and other composed dimensions; they cannot reference metrics (error E007)
Maximum nesting depth: 10 levels
No circular references (error E001)

Relationship Rules

from.dataset and to.dataset must exist in datasets, and their fields must name fields of those datasets
from.fields and to.fields must have the same length
Put the many side in from; there is no one_to_many
For many_to_one, to.fields should equal the to dataset's primaryKey or one of its uniqueKeys (otherwise: warning relationship_keys_unmatched)
Set isAutoJoin: true for fields to be auto-joinable in the explorer
Set isActive: true for the relationship to be used

Validation Checklist

Before submitting a template, verify:

Validation Error Codes

Code	Severity	Cause
`SCHEMA`	Error	The template doesn't match the schema; the message names the path
`mixed_schema_versions`	Error	The body mixes 2.0 and 2.1 keys
`duplicate_name`	Error (warning when it already existed)	A name is used twice in a dataset or list
`aggregate_in_row_field`	Error	A fact or dimension expression aggregates
`row_expression_in_dataset_metric`	Error	A dataset metric expression works row by row
`dataset_metric_references_other_dataset`	Error	A dataset metric uses another dataset's fields
`row_composed_metric_without_default_aggregate`	Error	A row-level composed metric has no `defaultAggregate`
`metric_input_not_found`	Error	A metric input names a metric that doesn't exist
`column_input_not_found`	Error	A field input names a field that doesn't exist
`unknown_dataset`, `unknown_field`	Error	A dataset metric or relationship refers to a missing dataset or field
`relationship_ends_unequal`	Error	`from` and `to` list a different number of fields
`ai_context_too_long`, `ai_context_too_many`	Error	Context is over a limit
`invalid_data_type`	Error	`dataType` isn't a logical type
`unknown_source_kind`	Error	`source.kind` isn't `table` or `sql`
`relationship_keys_unmatched`	Warning	A relationship's key-side fields don't match a key; joins may repeat rows
`native_type_needs_sync`	Warning	A field's binding changed, so its stored database type was cleared
E001	Error	Circular dependency in metric/dimension references
E003	Error	Composed dimension input references unknown field
E004	Error	Input references unknown dataset
E005	Error	Token in expression not found in inputs
E006	Warning	Input defined but not used in expression
E007	Error	Composed dimension references a metric
E009	Error	Invalid expression (braces in string literals)
E010	Error	Input requires aggregate but none specified
E011	Error	Input references unknown composed dimension
E012	Error	Unknown name in a fact or dataset metric expression
E013	Error	Invalid fact or dataset metric expression, or fact filter
E014	Error	Unknown field in a fact filter
E015	Error	Unsupported filter path (crosses unsafe relationship)
E016	Error	Unknown field in a dimension expression
E017	Error	Invalid dimension expression (`AS` aliases or syntax)
E018	Error	`primaryDateField` names an unknown dimension
E019	Error	`primaryDateField` names a dimension that isn't a date or datetime
E022	Error	A metric input wraps an aggregate metric in another aggregate

Generating Templates from Database Metadata

Here's a step-by-step algorithm for AI agents generating a domain from a database schema:

Step 1: Map Tables to Datasets

For each table in the target schema:

{
  "name": "<table_name_snake_case>",
  "label": "<Human Readable Name>",
  "analyticsRole": "<fact|dimension|event|snapshot|lookup|bridge|unknown>",
  "source": {
    "kind": "table",
    "connectionId": "<connection_id>",
    "connectionType": "<database_type>",
    "schema": "<schema_name>",
    "table": "<table_name>"
  },
  "primaryKey": ["<primary_key_column>"],
  "fields": { "identifiers": [], "facts": [], "dimensions": [] }
}

Step 2: Classify Columns into Fields

For each column in each table, set sourceField to the column name, dataType to its logical type, and nativeDataType to the database type:

Column Type	Classification	Rule
Primary key	Identifier (`type: "primary"`), and listed in `primaryKey`	Always
Foreign key	Identifier (`type: "foreign"`)	Always
Numeric, non-key	Fact	Choose `defaultAggregate`
Date/DateTime	Dimension	Set `granularity: "day"` for dates
String/Boolean	Dimension	Default classification

When choosing defaultAggregate, use SUM for additive measures, AVG for rates/prices/scores/per-unit measures, and COUNT only for explicit counts. If the automation cannot confidently classify the aggregate, prefer SUM and let a domain author adjust it later.

Step 3: Detect Relationships

For each foreign key constraint, put the table holding the foreign key in from:

{
  "name": "<fk_table>_to_<pk_table>",
  "from": { "dataset": "<fk_table>", "fields": ["<fk_column>"] },
  "to": { "dataset": "<pk_table>", "fields": ["<pk_column>"] },
  "cardinality": "many_to_one",
  "defaultJoinType": "LEFT",
  "isAutoJoin": true,
  "discoveredBy": "fk_constraint",
  "isActive": true
}

Step 4: Generate Common KPIs

Based on the facts, create dataset metrics for standard KPIs:

{
  "name": "total_<fact_name>",
  "dataset": "<dataset>",
  "label": "Total <Fact Label>",
  "expression": "SUM(<fact_name>)",
  "dataType": "number"
}

{
  "schemaVersion": "2.1",
  "aiContext": {
    "instructions": "Revenue is net of discounts. Use order_date for time questions unless the question is about returns.",
    "synonyms": ["sales", "ecommerce"]
  },
  "datasets": [
    {
      "name": "orders",
      "label": "Orders",
      "analyticsRole": "fact",
      "source": {
        "kind": "table",
        "connectionId": "conn_1",
        "connectionType": "PostgreSQL",
        "schema": "public",
        "table": "orders"
      },
      "primaryKey": ["order_id"],
      "uniqueKeys": [["order_number"]],
      "primaryDateField": "order_date",
      "fields": {
        "identifiers": [
          {"name": "order_id", "dataType": "number", "sourceField": "order_id", "type": "primary"},
          {"name": "customer_id", "dataType": "number", "sourceField": "customer_id", "type": "foreign"}
        ],
        "dimensions": [
          {
            "name": "order_date",
            "label": "Order Date",
            "dataType": "date",
            "sourceField": "order_date",
            "granularity": "day"
          },
          {
            "name": "order_number",
            "label": "Order Number",
            "dataType": "string",
            "sourceField": "order_number"
          },
          {"name": "status", "label": "Status", "dataType": "string", "sourceField": "status"},
          {
            "name": "order_quarter",
            "label": "Order Quarter",
            "dataType": "date",
            "expression": "DATE_TRUNC('quarter', order_date)"
          }
        ],
        "facts": [
          {
            "name": "amount",
            "label": "Amount",
            "dataType": "number",
            "nativeDataType": "numeric(12,2)",
            "sourceField": "amount",
            "defaultAggregate": "SUM"
          },
          {
            "name": "cost",
            "label": "Cost",
            "dataType": "number",
            "sourceField": "cost",
            "defaultAggregate": "SUM"
          }
        ]
      }
    },
    {
      "name": "customers",
      "label": "Customers",
      "analyticsRole": "dimension",
      "source": {
        "kind": "table",
        "connectionId": "conn_1",
        "connectionType": "PostgreSQL",
        "schema": "public",
        "table": "customers"
      },
      "primaryKey": ["id"],
      "fields": {
        "identifiers": [{"name": "id", "dataType": "number", "sourceField": "id", "type": "primary"}],
        "dimensions": [
          {
            "name": "segment",
            "label": "Customer Segment",
            "dataType": "string",
            "sourceField": "segment",
            "aiContext": {"synonyms": ["tier", "customer type"]}
          },
          {"name": "region", "label": "Region", "dataType": "string", "sourceField": "region"}
        ]
      }
    },
    {
      "name": "returns",
      "label": "Returns",
      "analyticsRole": "fact",
      "source": {
        "kind": "table",
        "connectionId": "conn_1",
        "connectionType": "PostgreSQL",
        "schema": "public",
        "table": "returns"
      },
      "fields": {
        "dimensions": [{"name": "return_date", "label": "Return Date", "dataType": "date", "sourceField": "return_date"}],
        "facts": [
          {
            "name": "amount",
            "label": "Return Amount",
            "dataType": "number",
            "sourceField": "amount",
            "defaultAggregate": "SUM"
          }
        ]
      }
    }
  ],
  "relationships": [
    {
      "name": "orders_to_customers",
      "from": {"dataset": "orders", "fields": ["customer_id"]},
      "to": {"dataset": "customers", "fields": ["id"]},
      "cardinality": "many_to_one",
      "defaultJoinType": "LEFT",
      "isAutoJoin": true,
      "discoveredBy": "user",
      "isActive": true
    }
  ],
  "metrics": [
    {
      "name": "revenue",
      "dataset": "orders",
      "label": "Revenue",
      "expression": "SUM(amount)",
      "dataType": "number",
      "aiContext": "Gross order amount after discounts, before returns."
    },
    {
      "name": "total_cost",
      "dataset": "orders",
      "label": "Total Cost",
      "expression": "SUM(cost)",
      "dataType": "number"
    },
    {
      "name": "profit",
      "dataset": "orders",
      "label": "Profit",
      "expression": "revenue - total_cost",
      "dataType": "number"
    },
    {
      "name": "profit_margin",
      "dataset": "orders",
      "label": "Profit Margin %",
      "expression": "profit / NULLIF(revenue, 0) * 100",
      "dataType": "number",
      "format": {"type": "percent", "decimals": 1}
    },
    {
      "name": "net_revenue",
      "label": "Net Revenue",
      "description": "Revenue minus returns",
      "expression": "{sales} - COALESCE({returned}, 0)",
      "inputs": {
        "sales": {"metric": "revenue", "dataset": "orders"},
        "returned": {"dataset": "returns", "field": "amount", "aggregate": "SUM"}
      }
    }
  ],
  "composedDimensions": [
    {
      "name": "order_month",
      "label": "Order Month",
      "expression": "DATE_TRUNC('month', {order_date})",
      "inputs": {"order_date": {"dataset": "orders", "field": "order_date"}},
      "dataType": "date"
    }
  ],
  "grainMappings": [{"sourceDimension": "order_month", "targetDataset": "returns", "targetColumn": "return_date"}]
}

Next Steps

Template Guide: Step-by-step YAML/JSON tutorial with SQL translation examples
Access Control: Control domain access via tokens