How to Use FACTS for Reusable Row-Level Logic

This guide shows how to use the FACTS clause to define reusable row-level expressions that metrics can reference. FACTS eliminate duplicated calculations across metrics and support chaining (one fact referencing another). Facts can also be queried directly as row-level columns and annotated with comments, synonyms, and access modifiers.

Prerequisites:

Define a Basic Fact

A fact is a named row-level expression scoped to a table alias. Unlike metrics, facts do not contain aggregate functions. They compute a value for each row.

CREATE SEMANTIC VIEW sales AS
TABLES (
    li AS line_items PRIMARY KEY (id)
)
FACTS (
    li.net_price AS li.extended_price * (1 - li.discount)
)
DIMENSIONS (
    li.region AS li.region
)
METRICS (
    li.total_net AS SUM(li.net_price)
);

The metric total_net references the fact net_price. At expansion time, the extension inlines the fact expression into the metric: SUM(li.extended_price * (1 - li.discount)).

Chain Facts Together

Facts can reference other facts. The extension resolves them in dependency order (topological sort) and inlines them recursively.

CREATE SEMANTIC VIEW sales AS
TABLES (
    li AS line_items PRIMARY KEY (id)
)
FACTS (
    li.net_price  AS li.extended_price * (1 - li.discount),
    li.tax_amount AS li.net_price * li.tax_rate
)
DIMENSIONS (
    li.region AS li.region
)
METRICS (
    li.total_net AS SUM(li.net_price),
    li.total_tax AS SUM(li.tax_amount)
);

Here tax_amount references net_price. The extension resolves the chain:

  1. net_price = li.extended_price * (1 - li.discount)

  2. tax_amount = (li.extended_price * (1 - li.discount)) * li.tax_rate

Both metrics receive the fully inlined expressions.

Use Facts in Multi-Table Views

Facts are scoped to their table alias. In a multi-table view, each fact references columns from its own table.

CREATE SEMANTIC VIEW analytics AS
TABLES (
    li AS line_items PRIMARY KEY (id),
    o  AS orders      PRIMARY KEY (id),
    c  AS customers   PRIMARY KEY (id)
)
RELATIONSHIPS (
    li_to_order       AS li(order_id)    REFERENCES o,
    order_to_customer AS o(customer_id)  REFERENCES c
)
FACTS (
    li.net_price  AS li.extended_price * (1 - li.discount),
    li.tax_amount AS li.net_price * li.tax_rate
)
DIMENSIONS (
    o.region  AS o.region,
    c.country AS c.country
)
METRICS (
    li.total_net AS SUM(li.net_price),
    li.total_tax AS SUM(li.tax_amount)
);

The facts are still scoped to li (line_items), but the dimensions come from o (orders) and c (customers). The extension joins all necessary tables based on what the query requests.

Warning

An expression may only reference columns of its own table. A fact, dimension, or metric expression that names a raw column of another logical table – li.margin AS li.extended_price - c.discount – is rejected at CREATE:

semantic view: metric 'margin' references 'c.discount', a column of table
'c', but a metric expression may only reference columns of its own table
('li'). To use a value from another table, define a FACT on that table and
reference the fact by name (e.g. 'c.<fact_name>').

Snowflake applies the same rule (“Expressions cannot refer to base table columns from other tables”) and rejects at definition time as well.

Changed in version 0.12.0: Previously the CREATE succeeded and the reference surfaced at query time as a DuckDB unknown-alias error.

Referencing a named fact – on its own table or, given a relationship, on another – is fully supported for facts, dimensions and metrics alike; see Reference a Fact on Another Table below. Composing metrics across tables (m AS t1.metric_a + t2.metric_b) is likewise supported. It is only the raw foreign column that is rejected.

Reference a Fact on Another Table

Added in version 0.12.0.

A fact, dimension or metric expression may reference a declared fact – on its own table, or on another one given a relationship. This is Snowflake’s documented way to cross tables, and the only one: define the fact where its columns live, then refer to it from the connected table.

Changed in version 0.12.0: Dimension expressions reference facts on the same footing as metrics. Before, facts were inlined only into metric and fact expressions, so a fact named in a dimension was emitted verbatim and failed on the unknown column – including when the fact was declared on the dimension’s own table.

CREATE SEMANTIC VIEW order_margin AS
  TABLES (
    o AS orders PRIMARY KEY (id),
    c AS customers PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    o_to_c AS o(customer_id) REFERENCES c(id)
  )
  FACTS (
    c.cust_discount AS c.discount
  )
  METRICS (
    o.net_total AS SUM(o.amount - c.cust_discount)
  );

The fact’s expression is inlined at the reference site and its table is joined for you, so net_total computes over orders LEFT JOIN customers. The reference must be to the fact by name – bare (cust_discount) or qualified by the fact’s own table (c.cust_discount). A qualifier naming any other relation is read as a raw column of that relation, which is the unsupported form in the warning above.

Warning

The fact’s table must not fan the member’s. Reaching a fact requires joining its table, and if that join multiplies the member’s rows – a fact on a child table, reached across a one-to-many edge – the aggregate would count each row once per child. There is no correct number to return, so the query is rejected:

semantic view 'v': fan trap detected -- 'bad_margin' (table 'o')
references the fact 'item_cost' on table 'li', and relationship 'li_to_o'
fans out on the way there, so joining it would multiply 'bad_margin's
rows. Reference a fact on a table reachable without fanning out, or
define the fact on 'o'.

Facts on the parent side (many-to-one, one row per member row) join safely and are the usual case – a customer’s discount, a product’s list price, a region’s tax rate.

Query Facts Directly

Added in version 0.6.0.

Facts can be queried as row-level columns using the facts parameter in semantic_view(). Unlike metric queries, fact queries return individual rows without aggregation.

SELECT * FROM semantic_view('analytics',
    facts := ['net_price', 'tax_amount']
);

Each row in the result contains the computed fact values. Dimensions can be included alongside facts – they appear as columns but do not trigger GROUP BY:

SELECT * FROM semantic_view('analytics',
    dimensions := ['region'],
    facts := ['net_price']
);

Warning

Facts and metrics cannot be combined in the same query. Use facts := [...] OR metrics := [...], not both. Attempting to mix them produces an error.

For a complete guide to fact queries, including wildcard selection and troubleshooting, see How to Query Facts Directly.

Annotate Facts with Metadata

Added in version 0.6.0.

Facts accept four annotations: COMMENT, WITH SYNONYMS, the PRIVATE / PUBLIC access modifiers, and LABELS = (FILTER). They may appear in any order after the expression.

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',
    PRIVATE li.raw_margin AS li.price - li.cost WITH SYNONYMS = ('margin', 'gross_margin')
)
DIMENSIONS (
    li.region AS li.region
)
METRICS (
    li.total_net    AS SUM(li.net_price),
    li.total_margin AS SUM(li.raw_margin),
    profit_margin   AS total_margin / total_net * 100
);

  • COMMENT adds a human-readable description, visible in DESCRIBE SEMANTIC VIEW and SHOW SEMANTIC FACTS output.

  • WITH SYNONYMS adds informational alternative names for discoverability.

  • PRIVATE prevents a fact from being queried directly via facts := [...], while still allowing it to be referenced in metric expressions – here the base metric total_margin aggregates the private fact raw_margin. Private facts are also excluded from wildcard expansion (alias.*).

  • LABELS = (FILTER) marks a boolean fact as a reusable named filter for where_clause predicates. See Mark a Named Filter.

Note that profit_margin is a derived metric: it has no table alias and combines two base metrics by name. A derived metric may not contain an aggregate of its own, so the aggregation over raw_margin has to live in the base metric li.total_margin rather than inline in profit_margin.

For more on metadata annotations, see How to Use Metadata Annotations.

Verify the Inlined SQL

Use explain_semantic_view() to confirm that fact expressions are inlined correctly:

SELECT * FROM explain_semantic_view('analytics',
    dimensions := ['region'],
    metrics := ['total_net']
);

The expanded SQL shows the fully inlined expression in the SELECT clause, with no reference to the fact name.

Troubleshooting

Circular fact references

Facts that reference each other in a cycle cause a define-time error. The extension detects cycles during CREATE SEMANTIC VIEW and reports which facts are involved.

Aggregate functions in facts

Facts must be row-level expressions. Using an aggregate function like SUM() or COUNT() in a fact expression causes a define-time error. Aggregation belongs in the METRICS clause.

Fact name not found

If a metric references a fact name that does not exist, the extension treats it as a regular column reference. If the column also does not exist, the query fails with a DuckDB column-not-found error. Double-check fact names match exactly.

Private fact cannot be queried

Facts marked PRIVATE cannot be queried via facts := [...]. They return an error: fact '<name>' is private and cannot be queried directly. Remove the PRIVATE keyword to make a fact queryable.