How to Query Facts Directly

This guide shows how to query facts as row-level columns with the FACTS clause of SEMANTIC_VIEW(…) (the facts parameter of semantic_view()). Unlike metrics, fact queries return individual rows without aggregation.

Prerequisites:

Query Facts

List fact names after FACTS to retrieve them as row-level columns:

SELECT * FROM SEMANTIC_VIEW(analytics
    FACTS net_price, tax_amount
);

Each row in the result contains the computed fact values – no aggregation is applied. This is equivalent to a SELECT with the fact expressions inlined.

Combine Facts with Dimensions

Changed in version 0.18.0: Facts and dimensions no longer go in separate clauses of one query. List them all in FACTS.

To read dimensions alongside facts, list them in FACTS too. A dimension listed in FACTS is read row by row, like a fact, and the columns come back in the order you list them:

SELECT * FROM SEMANTIC_VIEW(analytics
    FACTS region, net_price
);

This returns one row per source row, with region and net_price columns.

A query that lists facts in FACTS and dimensions in DIMENSIONS is refused, as it is in Snowflake:

semantic view 'analytics': querying FACTS with DIMENSIONS is not supported. List the dimensions in FACTS
for one row per source row, or the facts in DIMENSIONS for one row per distinct value.

The other way round, a fact listed in DIMENSIONS is grouped on, like a dimension: the query returns one row per distinct value, and metrics can be computed per value:

SELECT * FROM SEMANTIC_VIEW(analytics
    DIMENSIONS region, net_price
    METRICS order_count
);

Compute a Row-Level Expression

Added in version 0.16.0.

An item in FACTS can be an expression over the facts and dimensions of one table, named with AS. It is computed per row, like a declared fact:

SELECT * FROM SEMANTIC_VIEW(analytics
    FACTS net_price, net_price + tax_amount AS gross_price
);

The expression lasts for this query only. Declare it in the FACTS clause when other queries or metrics should use it too. See Expression Items for what an expression may use.

Mutual Exclusion with Metrics

Warning

Facts and metrics cannot be combined in the same query.

-- This fails:
SELECT * FROM SEMANTIC_VIEW(analytics
    METRICS total_revenue
    FACTS net_price
);

semantic view 'analytics': cannot combine facts and metrics in the same query.
Use FACTS or METRICS (facts := [...] or metrics := [...]), not both.

To get both row-level facts and aggregated metrics, run two separate queries.

Wildcard Selection for Facts

Use alias.* to select all public facts for a table alias:

SELECT * FROM SEMANTIC_VIEW(analytics
    FACTS li.*
);

Private facts are excluded from wildcard expansion. See How to Use Wildcard Selection for details.

Verify with explain_semantic_view()

Use explain_semantic_view() to see the expanded SQL and query plan for a fact query. It takes the FACTS clause as its facts parameter:

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

You can also use DESCRIBE SEMANTIC VIEW to inspect FACT entries and their expressions.

Troubleshooting

Unknown fact name

The fact name must match a fact declared in the FACTS clause. The error message lists available facts and suggests close matches.

Querying FACTS with DIMENSIONS is not supported

A query lists facts in FACTS and dimensions in DIMENSIONS. Move the dimensions into FACTS for one row per source row, or the facts into DIMENSIONS for one row per distinct value. See Combine Facts with Dimensions.

Private fact cannot be queried

Facts marked PRIVATE cannot be queried directly. They can only be referenced in metric expressions and other facts. Remove the PRIVATE keyword to make a fact queryable.

Incompatible table paths

A fact query returns rows as they are, so the tables it references must be joinable without multiplying them: one of them must reach every other without traversing a one-to-many relationship against its direction. If none does – typically two tables that both reference a third – the extension returns fact query references objects from incompatible table paths. A chain of many-to-one relationships is fine however long it is, and so are two tables that a third one the query reads both references (a fact on orders with dimensions on customers and products).