How to Use Metadata Annotations

This guide shows how to annotate dimensions, metrics, facts, and tables with comments, synonyms, access modifiers, named-filter labels and tags in a semantic view definition.

Prerequisites:

Add Comments

Comments are human-readable descriptions attached to any entry in the view definition. They appear in DESCRIBE SEMANTIC VIEW output and in the comment column of SHOW commands.

View-Level Comment

Set a comment on the semantic view itself using ALTER:

ALTER SEMANTIC VIEW sales SET COMMENT = 'Revenue and order analytics for the North America region';

Remove a view-level comment:

ALTER SEMANTIC VIEW sales UNSET COMMENT;

Tip

View-level comments appear in the comment column of SHOW SEMANTIC VIEWS and as a SEMANTIC_VIEW object kind row in DESCRIBE SEMANTIC VIEW.

Table-Level Comment

Add a COMMENT clause after the table declaration:

CREATE SEMANTIC VIEW sales AS
TABLES (
    o AS orders    PRIMARY KEY (id) COMMENT = 'Core order transactions',
    c AS customers PRIMARY KEY (id) COMMENT = 'Customer master data'
)
RELATIONSHIPS (order_customer AS o(customer_id) REFERENCES c)
DIMENSIONS (o.region AS o.region)
METRICS (o.revenue AS SUM(o.amount));

Comments on Dimensions, Metrics, and Facts

Add COMMENT after the expression on any entry:

CREATE SEMANTIC VIEW sales AS
TABLES (
    li AS line_items PRIMARY KEY (id)
)
FACTS (
    li.net_price AS li.extended_price * (1 - li.discount) COMMENT = 'Price after discount'
)
DIMENSIONS (
    li.region AS li.region COMMENT = 'Sales region from shipping address'
)
METRICS (
    li.total_net AS SUM(li.net_price) COMMENT = 'Net revenue after discounts'
);

Add Synonyms

Synonyms are alternative names for an entry. They are informational metadata – they do not affect query resolution, but they appear in DESCRIBE and SHOW output for discoverability.

Add WITH SYNONYMS after the expression (or after the COMMENT clause if both are present):

CREATE SEMANTIC VIEW sales AS
TABLES (
    o AS orders PRIMARY KEY (id) COMMENT = 'Order data' WITH SYNONYMS = ('transactions', 'purchases')
)
DIMENSIONS (
    o.region AS o.region WITH SYNONYMS = ('sales_region', 'territory')
)
METRICS (
    o.revenue AS SUM(o.amount) COMMENT = 'Total sales' WITH SYNONYMS = ('total_sales', 'gmv')
);

Synonyms appear as a JSON array in DESCRIBE SEMANTIC VIEW output (e.g., ["sales_region","territory"]) and in the synonyms column of SHOW SEMANTIC DIMENSIONS.

Set Access Modifiers (PRIVATE / PUBLIC)

Metrics and facts support PRIVATE and PUBLIC access modifiers. PUBLIC is the default. PRIVATE items cannot be queried directly. A private metric can only be referenced by derived metrics. A private fact can only be referenced by metric expressions and by other facts.

CREATE SEMANTIC VIEW sales AS
TABLES (
    li AS line_items PRIMARY KEY (id)
)
FACTS (
    PRIVATE li.raw_margin AS li.price - li.cost
)
METRICS (
    li.total_revenue AS SUM(li.price),
    PRIVATE li.total_cost AS SUM(li.cost),
    li.total_margin AS SUM(li.raw_margin),
    profit AS total_revenue - total_cost
);

In this example:

  • raw_margin is a private fact – it can be referenced by metrics (like total_margin) and by other facts, but cannot be queried (FACTS raw_margin), used in a query’s expression items, or named in its WHERE.

  • total_cost is a private metric – it can be referenced by derived metrics (like profit) but cannot be queried (METRICS total_cost).

  • total_margin and profit are public (default) and use the private items to compute their values.

Warning

PRIVATE is placed before the table alias (PRIVATE li.total_cost), not after the expression. Dimensions accept PUBLIC (the default) but not PRIVATE: a private dimension is rejected with PRIVATE is not supported on dimensions.

Mark a Named Filter

A named filter is a boolean-valued fact or dimension meant to be reused in a query’s pre-aggregation predicate instead of selected as output. Declare one by adding LABELS = (FILTER) between the member’s name and its AS, where Snowflake writes it:

CREATE SEMANTIC VIEW sales AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
FACTS (
    o.amount AS o.amount,
    o.is_large LABELS = (FILTER) AS o.amount > 100
)
DIMENSIONS (
    o.is_domestic LABELS = (FILTER) AS o.country = 'US'
)
METRICS (
    o.revenue AS SUM(o.amount)
);

You can also write LABELS = (FILTER) after the expression, among the other annotations: o.is_large AS o.amount > 100 LABELS = (FILTER) declares the same filter. Write it in one place or the other, not both. GET_DDL writes it before AS.

Added in version 0.18.0: LABELS before AS. Earlier versions accepted it only after the expression.

Reference the filter by name in the query’s WHERE, which applies it before the metrics aggregate:

SELECT * FROM SEMANTIC_VIEW(sales METRICS revenue WHERE is_domestic AND is_large);

The label is declarative metadata, not an access restriction or a resolution rule:

  • The WHERE inside SEMANTIC_VIEW(...) (where_clause in semantic_view()) already substitutes any declared fact or dimension name, labeled or not. The label records that the member exists to be filtered on, and surfaces it in DESCRIBE and GET_DDL for discoverability.

  • A filter is still a queryable member. DIMENSIONS is_domestic returns its boolean values like any other dimension. There is no way to hide a filter while keeping it usable: PRIVATE applies only to facts and metrics, and a private fact is refused in WHERE as well as in FACTS.

Note

The BOOLEAN requirement is checked by DuckDB’s binder at query time, not at CREATE. CREATE binds the filter like any other member, which catches an unknown column or function, but does not check its type, so a filter over a non-boolean expression is created successfully and raises DuckDB’s own type error the first time it is used in a predicate.

FILTER is the only supported label. Any other value is rejected at CREATE rather than silently dropped, so a definition cannot round-trip having quietly lost a label you wrote.

LABELS is likewise valid only on a fact or a dimension. Writing it on a table, on a metric, or as a view-level annotation is rejected for the same reason: those entries carry no filter flag, so accepting it there would mean discarding it on the way to storage.

Add Tags

Added in version 0.17.0.

Tags are name/value pairs for tools that read your view’s metadata, such as a governance catalog or a BI layer that needs to know what each member is. Like comments, they never change what a query returns. Add WITH TAG (...) to any table, fact, dimension or metric entry, or after the last clause to tag the view itself:

CREATE SEMANTIC VIEW sales AS
TABLES (
    o AS orders PRIMARY KEY (id) WITH TAG (governance.pii = 'none')
)
DIMENSIONS (
    o.region AS o.region COMMENT = 'Sales region'
        WITH TAG (s_layer_kind = 'dimension', s_layer_label = 'Region')
)
METRICS (
    o.revenue AS SUM(o.amount) WITH TAG (s_layer_kind = 'metric')
)
WITH TAG (owner = 'analytics');

The WITH is optional, as in Snowflake. A tag name can be qualified (governance.pii, db.governance.pii) and is stored as written: there is no CREATE TAG object to create first. Values are single-quoted strings of up to 256 characters.

Read the tags back from the TAGS row in DESCRIBE, which holds a JSON object:

SELECT object_name, json_extract_string(property_value, '$.s_layer_kind') AS kind
FROM describe_semantic_view('sales')
WHERE property = 'TAGS' AND object_kind IN ('DIMENSION', 'METRIC');

GET_DDL and READ_YAML_FROM_SEMANTIC_VIEW() write the tags back out, so they survive a dump and replay. SHOW commands don’t list them, which matches Snowflake. See Tags for the full rules.

To change the view’s own tags without re-creating it, use ALTER SEMANTIC VIEW … SET TAG / UNSET TAG:

ALTER SEMANTIC VIEW sales SET TAG owner = 'finance', tier = 'gold';
ALTER SEMANTIC VIEW sales UNSET TAG tier;

ALTER changes view-level tags only, as in Snowflake. To change a member’s tags, re-create the view.

Keep Cortex Analyst Metadata

Added in version 0.18.0.

If you port views from Snowflake, their DDL may carry metadata for Cortex Analyst: sample values, enum flags, a Cortex Search service, custom instructions and verified queries. Paste it as it is. The extension stores these clauses and writes them back out, so a view you export to Snowflake keeps them, but they don’t change any query here:

CREATE SEMANTIC VIEW sales
TABLES (o AS orders PRIMARY KEY (id))
DIMENSIONS (
    o.region AS o.region SAMPLE_VALUES ('East', 'West', 'North', 'South') IS_ENUM,
    o.warehouse AS o.warehouse
        WITH CORTEX SEARCH SERVICE search.warehouses USING warehouse_name
)
METRICS (o.revenue AS SUM(o.amount))
AI_SQL_GENERATION 'Round all numeric columns to 2 decimal places.'
MAX_STALENESS = '1 hour';

They show in DESCRIBE (SAMPLE_VALUES, IS_ENUM and CORTEX_SEARCH_SERVICE_* rows on the dimension, a CUSTOM_INSTRUCTIONS row and a view-level MAX_STALENESS row), and GET_DDL and the YAML export write them back. See Cortex Analyst metadata for every clause and its rules.

Inspect Annotations

Via DESCRIBE

DESCRIBE SEMANTIC VIEW shows annotation properties as additional rows:

DESCRIBE SEMANTIC VIEW sales;

Look for these property rows:

  • COMMENT – the comment text (conditional, only when set)

  • SYNONYMS – JSON array of synonyms (conditional, only when set)

  • LABELS – ["FILTER"] for a named filter (conditional, only on labeled facts and dimensions)

  • TAGS – JSON object of tags (conditional, only when set)

  • ACCESS_MODIFIER – PUBLIC or PRIVATE (always emitted for facts and metrics)

  • NON_ADDITIVE_BY – non-additive dimension list (conditional, only for semi-additive metrics)

  • WINDOW_SPEC – reconstructed OVER clause (conditional, only for window metrics)

Via SHOW Commands

The SHOW SEMANTIC DIMENSIONS, SHOW SEMANTIC METRICS, and SHOW SEMANTIC FACTS commands include synonyms and comment columns in their output. SHOW SEMANTIC DIMENSIONS and SHOW SEMANTIC FACTS also have a labels column, ["FILTER"] for a named filter and [] otherwise:

SHOW SEMANTIC DIMENSIONS IN sales;

┌───────────────┬─────────────┬────────────────────┬────────────┬────────┬───────────┬──────────────────────────────┬─────────┬────────┐
│ database_name │ schema_name │ semantic_view_name │ table_name │ name   │ data_type │ synonyms                     │ comment │ labels │
├───────────────┼─────────────┼────────────────────┼────────────┼────────┼───────────┼──────────────────────────────┼─────────┼────────┤
│ memory        │ main        │ sales              │ orders     │ region │ VARCHAR   │ ["sales_region","territory"] │         │ []     │
└───────────────┴─────────────┴────────────────────┴────────────┴────────┴───────────┴──────────────────────────────┴─────────┴────────┘

This output is for the sales view from Add Synonyms. Its region dimension has synonyms but no comment.

Tip

Private items are excluded from SHOW COLUMNS IN SEMANTIC VIEW and from wildcard expansion (alias.*). They still appear in DESCRIBE SEMANTIC VIEW (with ACCESS_MODIFIER set to PRIVATE), SHOW SEMANTIC METRICS, and SHOW SEMANTIC FACTS.

Troubleshooting

Comment not appearing in SHOW SEMANTIC VIEWS

Only view-level comments appear in SHOW SEMANTIC VIEWS. Table/dimension/metric/fact comments appear in DESCRIBE SEMANTIC VIEW and in the comment column of SHOW SEMANTIC DIMENSIONS, SHOW SEMANTIC METRICS, and SHOW SEMANTIC FACTS.

Cannot query a private metric or fact

Private items return an error when queried directly, for example metric 'total_cost' is private and cannot be queried directly. Reference a private metric from a derived metric, and a private fact from a metric or another fact. To make an item queryable again, recreate the view without the PRIVATE keyword.

Synonyms not affecting query resolution

Synonyms are informational metadata only. They do not expand the set of names recognized by SEMANTIC_VIEW(…) and semantic_view() or explain_semantic_view(). Use the declared name to query an item.

COMMENT, WITH SYNONYMS, LABELS and TAG order

The annotations on one entry may appear in any order – o.region AS o.region WITH SYNONYMS = ('territory') COMMENT = 'c' parses the same as the reverse. What the parser does require is that the annotation region be tiled by recognized clauses: once the first annotation keyword is seen, everything after it must be a valid COMMENT / WITH SYNONYMS / LABELS / [WITH] TAG clause separated by whitespace. Leftover text (COMMENT = 'a' banana) or a repeated clause is an error rather than being silently dropped.

LABELS alone may also sit between the name and AS (o.is_large LABELS = (FILTER) AS ...). COMMENT, WITH SYNONYMS and TAG there raise Only LABELS may come between the name and ‘AS’. Writing LABELS both before AS and after the expression raises Duplicate LABELS annotation.

Unsupported label

FILTER is the only value accepted in LABELS = (...). Other label values are rejected at CREATE – deliberately, so a definition cannot round-trip having quietly lost a label. Remove the unsupported value to create the view.

LABELS rejected on a table, metric, or the view itself

LABELS applies only to facts and dimensions – they are the entries that carry the filter flag. On a TABLES or METRICS entry, or in the trailing view-level annotation position, it raises LABELS is not valid on a …. Move the annotation to the fact or dimension you meant to mark.

A named filter still shows up in query output

Expected. LABELS = (FILTER) declares intent and drives introspection; it does not hide the member. PRIVATE is not a way round this: it applies only to facts and metrics, and a private fact cannot be used in WHERE either.