YAML Definition Format¶

Specification of the YAML schema accepted by CREATE SEMANTIC VIEW ... FROM YAML. Every semantic view definition – whether created from inline YAML, a YAML file, or exported via READ_YAML_FROM_SEMANTIC_VIEW() – follows this format.

Added in version 0.7.0.

YAML keys differ from the SQL clause names in some cases:

SQL Clause	YAML Key	Notes
`TABLES`	`tables`
`RELATIONSHIPS`	`joins`	Different name – YAML uses `joins`
`VARIABLES`	`variables`	A list of `{name, data_type, default, comment}` entries. See Variable
`FACTS`	`facts`
`DIMENSIONS`	`dimensions`
`METRICS`	`metrics`
`MATERIALIZATIONS`	`materializations`
`COMMENT`	`comment`	View-level comment
`[WITH] TAG`	`tags`	A list of `{name, value}` entries in Snowflake’s shape. See Tag

Complete Example¶

A YAML definition covering all supported features:

tables:
  - alias: o
    table: orders
    pk_columns:
      - id
    comment: Order transactions
    synonyms:
      - order_facts
  - alias: paid
    definition: |
      SELECT * FROM orders WHERE amount > 0
    pk_columns:
      - id
  - alias: c
    table: customers
    pk_columns:
      - id

joins:
  - table: c
    from_alias: o
    fk_columns:
      - customer_id
    name: order_customer
    cardinality: ManyToOne

variables:
  - name: min_amount
    data_type: DECIMAL(10,2)
    default: '100'
    comment: Smallest order counted as large

facts:
  - name: net_price
    expr: o.extended_price * (1 - o.discount)
    source_table: o
    comment: Price after discount
    synonyms:
      - discounted_price

dimensions:
  - name: region
    expr: o.region
    source_table: o
    comment: Sales territory
    tags:
      - name: {tag: S_LAYER_KIND}
        value: dimension
  - name: customer_name
    expr: c.name
    source_table: c
  - name: report_date
    expr: o.report_date
    source_table: o

metrics:
  - name: revenue
    expr: SUM(o.amount)
    source_table: o
    comment: Total revenue
    synonyms:
      - total_revenue
  - name: order_count
    expr: COUNT(*)
    source_table: o
  - name: avg_order
    expr: revenue / order_count
  - name: balance
    expr: SUM(o.amount)
    source_table: o
    non_additive_by:
      - dimension: report_date
        order: Desc
        nulls: First

materializations:
  - name: region_agg
    table: revenue_by_region
    dimensions:
      - region
    metrics:
      - revenue
      - order_count

comment: Revenue analytics view
tags:
  - name: {database: analytics, schema: governance, tag: owner}
    value: finance

Minimal Definition¶

A minimal definition has tables, dimensions, and metrics. Both the dimensions and metrics keys must be present, and at least one of them must be non-empty. Write [] for the one you do not need:

tables:
  - alias: o
    table: orders
    pk_columns:
      - id
dimensions:
  - name: region
    expr: o.region
    source_table: o
metrics:
  - name: revenue
    expr: SUM(o.amount)
    source_table: o

A definition with metrics only:

tables:
  - alias: o
    table: orders
    pk_columns:
      - id
dimensions: []
metrics:
  - name: revenue
    expr: SUM(o.amount)
    source_table: o

Note

tables must list at least one table. A definition that omits tables, or sets it to [], is rejected by CREATE SEMANTIC VIEW ... FROM YAML.

Changed in version 0.16.0: Earlier versions accepted such a definition, and the view failed at its first query.

Top-Level Keys¶

Key	Type	Required	Description
`tables`	list of Table	Yes	Physical tables available to the view. Must list at least one table: a definition without `tables`, or with `tables: []`, is rejected. See Minimal Definition.
`dimensions`	list of Dimension	Yes ^*	Named grouping expressions.
`metrics`	list of Metric	Yes ^*	Named aggregation or derived expressions.
`joins`	list of Join	No	FK/PK relationships between tables. Maps to the SQL `RELATIONSHIPS` clause.
`facts`	list of Fact	No	Named row-level expressions (no aggregates).
`variables`	list of Variable	No	View variables that member expressions and queries read. Maps to the SQL `VARIABLES` clause. Added in version 0.18.0.
`materializations`	list of Materialization	No	Pre-aggregated table mappings for query routing.
`comment`	string	No	View-level human-readable description.
`tags`	list of Tag	No	View-level tags.
`max_staleness`	integer or string	No	`MAX_STALENESS`: whole seconds (`3600`) or an interval (`'1 hour'`), at least 120 seconds. See Cortex Analyst metadata.
`module_custom_instructions`	mapping	No	Cortex Analyst instructions: `sql_generation` and `question_categorization`, both optional strings. The YAML form of `AI_SQL_GENERATION` and `AI_QUESTION_CATEGORIZATION`.
`verified_queries`	list of VerifiedQuery	No	The YAML form of `AI_VERIFIED_QUERIES`.

^* Both keys must be present – a definition without a metrics key is rejected with a “missing field” error – and at least one of the two must be non-empty. Use [] for the empty one.

Table¶

Each entry in the tables list declares a physical table, or a table defined by a query, with an alias. The first table is the base table (root of the relationship graph).

Field	Type	Required	Default	Description
`alias`	string	Yes		Short name used to reference this table in all other sections.
`table`	string	One of these two		Physical table name. Supports catalog-qualified names (`catalog.schema.table`).
`definition`	string	One of these two		The query of a table defined by a query, without its parentheses. It must be one query: its brackets nest, nothing in it is left open, and it holds no `;` and no prepared-statement parameter. Added in version 0.18.0.
`pk_columns`	list of string	No	`[]`	Primary key column names. Used for JOIN synthesis and cardinality inference.
`unique_constraints`	list of list of string	No	`[]`	UNIQUE constraint column lists. Each inner list is one constraint. Used for cardinality inference.
`comment`	string	No	null	Human-readable description.
`synonyms`	list of string	No	`[]`	Alternative names for discoverability.
`tags`	list of Tag	No	`[]`	Name/value metadata tags.

tables:
  - alias: o
    table: orders
    pk_columns:
      - id
    unique_constraints:
      - - email
    comment: Order transactions
    synonyms:
      - order_facts

Dimension¶

Each entry in the dimensions list declares a named grouping expression.

Field	Type	Required	Default	Description
`name`	string	Yes		Dimension name.
`expr`	string	Yes		SQL expression. Can be a column reference (`o.region`) or computed (`date_trunc('month', o.ordered_at)`).
`source_table`	string	No	null	Table alias this dimension comes from. Used for join dependency resolution. Must be an alias declared in `tables` (any case), also in a single-table view. Write it unquoted: a quoted alias such as `'"O"'` is accepted only in a single-table view (a known limitation).
`output_type`	string	No	null	No longer accepted. Write the cast into the expression instead – `expr: CAST(o.ordered_at AS DATE)`. No DDL clause can express a member type, so GET_DDL dropped this field and a restored view silently lost the cast; a cast written into `expr` survives the round trip. A type name here is rejected at `CREATE` time. `output_type: null` is accepted and ignored: READ_YAML_FROM_SEMANTIC_VIEW() writes it on every dimension, metric, and fact, so exported YAML re-imports unchanged.
`comment`	string	No	null	Human-readable description.
`synonyms`	list of string	No	`[]`	Alternative names for discoverability.
`tags`	list of Tag	No	`[]`	Name/value metadata tags.
`is_filter`	boolean	No	`false`	`true` declares the dimension a named filter, the YAML form of `LABELS = (FILTER)`. The expression should be boolean.
`sample_values`	list of string	No	`[]`	The YAML form of `SAMPLE_VALUES`. See Cortex Analyst metadata.
`is_enum`	boolean	No	`false`	The YAML form of `IS_ENUM`.
`cortex_search_service`	mapping	No	null	The YAML form of `WITH CORTEX SEARCH SERVICE`: `service` (required), with optional `schema`, `database` and `literal_column` (the `USING` column). A `database` needs a `schema`, and no part may be empty.

dimensions:
  - name: region
    expr: o.region
    source_table: o
    comment: Sales territory
  - name: order_month
    expr: CAST(date_trunc('month', o.ordered_at) AS DATE)
    source_table: o
  - name: is_eu
    expr: o.region = 'EU'
    source_table: o
    is_filter: true
  - name: warehouse
    expr: o.warehouse
    source_table: o
    sample_values: [SMALL, LARGE]
    is_enum: true
    cortex_search_service:
      service: warehouses
      schema: search
      literal_column: warehouse_name

Metric¶

Each entry in the metrics list declares a named aggregation, derived metric, semi-additive metric, or window metric.

Field	Type	Required	Default	Description
`name`	string	Yes		Metric name.
`expr`	string	Yes		For base metrics: aggregate expression (`SUM(o.amount)`). For derived metrics: arithmetic over other metric names (`revenue - cost`).
`source_table`	string	No	null	Table alias. Present for base metrics, absent for derived metrics. Must be an alias declared in `tables` (any case), also in a single-table view. Write it unquoted: a quoted alias such as `'"O"'` is accepted only in a single-table view (a known limitation).
`output_type`	string	No	null	No longer accepted. Write the cast into the expression instead (see the dimension table above).
`using_relationships`	list of string	No	`[]`	Named relationships this metric traverses (for role-playing dimensions).
`comment`	string	No	null	Human-readable description.
`synonyms`	list of string	No	`[]`	Alternative names for discoverability.
`tags`	list of Tag	No	`[]`	Name/value metadata tags.
`access`	string	No	`Public`	Access modifier: `Public` (queryable) or `Private` (usable only in derived expressions).
`non_additive_by`	list of NonAdditiveDim	No	`[]`	Semi-additive dimensions for snapshot selection. Mutually exclusive with `window_spec`.
`window_spec`	WindowSpec	No	null	Window function specification. Mutually exclusive with `non_additive_by`.

Base metric (with source_table and aggregate expression):

metrics:
  - name: revenue
    expr: SUM(o.amount)
    source_table: o

Derived metric (no source_table, references other metrics):

metrics:
  - name: profit
    expr: revenue - cost

Private metric:

metrics:
  - name: raw_total
    expr: SUM(o.amount)
    source_table: o
    access: Private

Semi-additive metric:

metrics:
  - name: total_balance
    expr: SUM(a.balance)
    source_table: a
    non_additive_by:
      - dimension: report_date
        order: Desc
        nulls: First

Window metric:

metrics:
  - name: rolling_avg
    expr: AVG(total_qty)
    source_table: s
    window_spec:
      window_function: AVG
      inner_metric: total_qty
      excluding_dims:
        - date
      order_by:
        - expr: date
          order: Asc
          nulls: Last
      frame_clause: "RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW"

Fact¶

Each entry in the facts list declares a named row-level expression. Facts can reference other facts (resolved in topological order). Aggregate functions are not allowed.

Field	Type	Required	Default	Description
`name`	string	Yes		Fact name.
`expr`	string	Yes		Row-level SQL expression. Must not contain aggregate functions.
`source_table`	string	No	null	Table alias this fact is scoped to.
`output_type`	string	No	null	No longer accepted. Write the cast into the expression instead (see the dimension table above). SHOW SEMANTIC FACTS reports the expression’s actual type without it.
`comment`	string	No	null	Human-readable description.
`synonyms`	list of string	No	`[]`	Alternative names for discoverability.
`tags`	list of Tag	No	`[]`	Name/value metadata tags.
`access`	string	No	`Public`	Access modifier: `Public` (queryable via `facts := [...]`) or `Private` (usable only in metric expressions).
`is_filter`	boolean	No	`false`	`true` declares the fact a named filter, the YAML form of `LABELS = (FILTER)`. The expression should be boolean.
`sample_values`	list of string	No	`[]`	The YAML form of `SAMPLE_VALUES`. See Cortex Analyst metadata.

facts:
  - name: net_price
    expr: o.extended_price * (1 - o.discount)
    source_table: o
    comment: Price after discount
    synonyms:
      - discounted_price
  - name: tax_amount
    expr: o.net_price * o.tax_rate
    source_table: o

Join¶

Each entry in the joins list declares a FK/PK relationship between tables. This maps to the SQL RELATIONSHIPS clause.

Field	Type	Required	Default	Description
`table`	string	Yes		Target table alias (the table being joined to).
`from_alias`	string	Yes		Source table alias containing the FK columns.
`fk_columns`	list of string	Yes		FK column names on the source table. At least one is required; `CREATE` rejects a relationship without any.
`ref_columns`	list of string	No	`[]`	Referenced columns on the target table. Defaults to the target’s primary key if omitted.
`name`	string	Yes		Relationship name, referenced by `using_relationships` for role-playing dimensions. Must be unique among the view’s relationships, compared the way DuckDB compares identifiers (`r`, `R` and `"r"` are one name).
`cardinality`	string	No	`ManyToOne`	`ManyToOne` or `OneToOne`. Used for fan trap detection.

joins:
  - table: c
    from_alias: o
    fk_columns:
      - customer_id
    name: order_customer
    cardinality: ManyToOne
  - table: a
    from_alias: f
    fk_columns:
      - departure_code
    ref_columns:
      - airport_code
    name: dep_airport

Variable¶

Added in version 0.18.0.

Each entry in the variables list declares a view variable. This maps to the SQL VARIABLES clause; see VARIABLES for what a variable does and the rules it follows.

Field	Type	Required	Default	Description
`name`	string	Yes		Variable name: one identifier. It cannot be the name of a fact, dimension, metric or other variable.
`data_type`	string	No	null	A DuckDB type that each value is cast to (`INTEGER`, `DECIMAL(10,2)`).
`default`	string	No	null	The default expression, used when a query does not set the variable (`'1000'`, `current_date`). Each query evaluates it.
`comment`	string	No	null	Human-readable description.

variables:
  - name: min_amount
    data_type: INTEGER
    default: '1000'
  - name: multiplier
    data_type: DOUBLE
    default: |
      1.0
  - name: target_region
    data_type: VARCHAR
    comment: the region to report on

data_type and default are stored trimmed, so a | block scalar, which ends in a line break, stores 1.0. Because an export has to re-create the same view through GET_DDL, CREATE SEMANTIC VIEW ... FROM YAML also refuses an entry that the SQL form could not write back:

a data_type or default holding a SQL comment marker (-- or /*), which CREATE would drop: default: 2 -- a note is refused;
a data_type or default holding a DEFAULT, := or COMMENT of its own outside brackets, which would read back as a different variable.

Materialization¶

Each entry in the materializations list maps a pre-aggregated table to the dimensions and metrics it covers.

Field	Type	Required	Default	Description
`name`	string	Yes		Materialization name (unique within the view).
`table`	string	Yes		Pre-aggregated table name. Supports catalog-qualified names. Not validated for existence at define time.
`dimensions`	list of string	No	`[]`	Dimension names covered. Must match declared dimension names.
`metrics`	list of string	No	`[]`	Metric names covered. Must match declared metric names.

At least one of dimensions or metrics must be specified.

materializations:
  - name: region_agg
    table: revenue_by_region
    dimensions:
      - region
    metrics:
      - revenue
      - order_count
  - name: global_agg
    table: global_totals
    metrics:
      - revenue

NonAdditiveDim¶

Used within a metric’s non_additive_by list to specify snapshot selection behavior.

Field	Type	Required	Default	Description
`dimension`	string	Yes		Dimension name for snapshot selection.
`order`	string	No	`Asc`	Sort direction: `Asc` or `Desc`.
`nulls`	string	No	`Last`	NULLS placement: `Last` or `First`.

non_additive_by:
  - dimension: report_date
    order: Desc
    nulls: First

WindowSpec¶

Used within a metric’s window_spec field to declare a window function metric.

Field	Type	Required	Default	Description
`window_function`	string	Yes		Window function name (`AVG`, `LAG`, `SUM`, `RANK`, etc.).
`inner_metric`	string	Yes		Name of the metric to wrap in the window function.
`extra_args`	list of string	No	`[]`	Additional function arguments (e.g., `["30"]` for `LAG(metric, 30)`).
`excluding_dims`	list of string	No	`[]`	Dimensions to exclude from the partition set (`PARTITION BY EXCLUDING` semantics). Mutually exclusive with `partition_dims`.
`partition_dims`	list of string	No	`[]`	Explicit partition dimensions (`PARTITION BY` semantics). Mutually exclusive with `excluding_dims`.
`order_by`	list of WindowOrderBy	No	`[]`	ORDER BY entries for the window frame.
`frame_clause`	string	No	null	Raw SQL frame clause (e.g., `RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW`).

window_spec:
  window_function: AVG
  inner_metric: total_qty
  excluding_dims:
    - date
  order_by:
    - expr: date
      order: Asc
      nulls: Last
  frame_clause: "RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW"

WindowOrderBy¶

Used within a window spec’s order_by list.

Field	Type	Required	Default	Description
`expr`	string	Yes		Dimension name or expression to order by.
`order`	string	No	`Asc`	Sort direction: `Asc` or `Desc`.
`nulls`	string	No	`Last`	NULLS placement: `Last` or `First`.

Tag¶

Added in version 0.17.0.

Used within the tags list of the view, a table, a fact, a dimension or a metric. The shape follows Snowflake’s semantic-view YAML: name is a mapping with the tag’s name parts.

Field	Type	Required	Default	Description
`name`	mapping	Yes		The tag name: `tag` (required), with optional `schema` and `database`. A `database` needs a `schema`. Parts are stored as written and compared case-insensitively.
`value`	string	Yes		The tag value, at most 256 characters.

tags:
  - name: {tag: S_LAYER_KIND}
    value: metric
  - name: {schema: governance, tag: pii}
    value: none

A tag list may not name the same tag twice. Tags the SQL form could not write (an empty name part, a database without a schema, a value over 256 characters, a duplicate) are rejected by CREATE SEMANTIC VIEW ... FROM YAML.

VerifiedQuery¶