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:

A working semantic view with TABLES, DIMENSIONS, and METRICS (see Multi-Table Semantic Views, or Building a Complete Model for a guided introduction to facts)
Understanding of aggregate vs. row-level expressions in SQL

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:

net_price = li.extended_price * (1 - li.discount)
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.

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 support the same metadata annotations as dimensions and metrics: COMMENT, WITH SYNONYMS, and PRIVATE/PUBLIC access modifiers.

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),
    profit_margin AS total_net - SUM(li.raw_margin)
);

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. Private facts are also excluded from wildcard expansion (alias.*).

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.