How to Filter Semantic View Queries¶

This guide shows how to filter a SEMANTIC_VIEW(…) query, and how to pick the right filter for the job. There are two of them and they run on opposite sides of the aggregation, so for anything except a filter on a dimension you are already grouping by, they return different numbers:

A WHERE inside SEMANTIC_VIEW(...), written as in Snowflake, filters the rows before the metrics aggregate them. With the semantic_view() table function, the same filter is the where_clause parameter.
An ordinary WHERE outside, on the query around it, filters the rows the query returns.

Tip

In a standard-SQL query over the view by name, WHERE is the filter inside the construct and HAVING is the filter on the result: SELECT region, AGG(revenue) FROM order_metrics WHERE ordered_at >= DATE '2024-01-01' GROUP BY region HAVING AGG(revenue) > 200. Everything below about choosing between them applies to that form too.

Prerequisites:

A working semantic view you can query (see Getting Started)
Familiarity with the query modes (see Query Modes)

Which Filter to Use¶

What you want to filter on	Use	Runs
A dimension that is in the query’s output	Either	Same result
A dimension or fact that is not in the output	`WHERE` inside `SEMANTIC_VIEW(...)`	Before aggregation
A date or segment scope for the numbers themselves	`WHERE` inside `SEMANTIC_VIEW(...)`	Before aggregation
A metric value (`revenue > 200`)	Outer `WHERE`	After aggregation

The rule behind the table: the WHERE inside the construct decides which rows the metrics aggregate over, and an outer WHERE decides which of the resulting rows you keep.

Set Up an Example View¶

Every example below runs against this view. It has two dimensions – region, which the queries group by, and ordered_at, which they mostly do not – and two metrics.

INSTALL semantic_views FROM community;
LOAD semantic_views;

CREATE TABLE orders (
    id INTEGER,
    region VARCHAR,
    amount DECIMAL(10,2),
    ordered_at DATE
);

INSERT INTO orders VALUES
    (1, 'East', 100.00, DATE '2023-11-02'),
    (2, 'East', 250.00, DATE '2024-01-15'),
    (3, 'East',  50.00, DATE '2024-02-20'),
    (4, 'West', 400.00, DATE '2023-12-10'),
    (5, 'West', 150.00, DATE '2024-03-05');

CREATE SEMANTIC VIEW order_metrics AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
    o.region     AS o.region,
    o.ordered_at AS o.ordered_at
)
METRICS (
    o.revenue     AS SUM(o.amount),
    o.order_count AS COUNT(*)
);

Unfiltered, revenue by region covers all five orders:

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue, order_count
) ORDER BY region;

┌────────┬─────────┬─────────────┐
│ region │ revenue │ order_count │
├────────┼─────────┼─────────────┤
│ East   │  400.00 │           3 │
│ West   │  550.00 │           2 │
└────────┴─────────┴─────────────┘

Filter the Result Rows with an Outer WHERE¶

Add an ordinary SQL WHERE after the construct. The predicate may name any column in the result – that is, any dimension or metric you asked for.

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue
) WHERE region = 'East';

┌────────┬─────────┐
│ region │ revenue │
├────────┼─────────┤
│ East   │  400.00 │
└────────┴─────────┘

region is one of the grouped dimensions, so removing the West group after aggregation and removing the West rows before aggregation come to the same thing. DuckDB’s optimizer pushes such predicates down into the generated query anyway. This is also the only place a filter on a metric can go, because a metric has no value until it has been aggregated:

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue
) WHERE revenue > 500;

Filter the Rows Behind a Metric with an Inner WHERE¶

Write WHERE <predicate> inside SEMANTIC_VIEW(...), after the lists, to apply the predicate before the metrics are aggregated. The predicate names declared dimensions and facts by their logical names, whether or not they appear in the output.

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue, order_count
    WHERE ordered_at >= DATE '2024-01-01'
) ORDER BY region;

┌────────┬─────────┬─────────────┐
│ region │ revenue │ order_count │
├────────┼─────────┼─────────────┤
│ East   │  300.00 │           2 │
│ West   │  150.00 │           1 │
└────────┴─────────┴─────────────┘

Each region’s revenue has been recomputed over its 2024 orders only, and the counts follow. The result still has one row per region – filtering by ordered_at did not add it to the output.

With semantic_view(), pass the same predicate as where_clause:

SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue', 'order_count'],
    where_clause := 'ordered_at >= DATE ''2024-01-01'''
) ORDER BY region;

Note

The parameter is spelled where_clause rather than where because DuckDB reserves where in named-parameter position: where := '…' fails to parse before the extension is ever consulted. Inside the SQL string literal, single quotes are doubled – hence DATE ''2024-01-01''.

Where the Two Filters Diverge¶

The 2024 result above is not reachable with an outer WHERE. Two attempts show why.

Attempt 1: filter on a column that is not in the output.

-- Fails: `ordered_at` is not a column of the result
SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue
) WHERE ordered_at >= DATE '2024-01-01';

The query returned region and revenue, so DuckDB raises a binder error for the unknown column. This failure is loud, which makes it the harmless case.

Attempt 2: add the column to the output so the filter binds.

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region, ordered_at
    METRICS revenue
) WHERE ordered_at >= DATE '2024-01-01'
ORDER BY region, ordered_at;

┌────────┬────────────┬─────────┐
│ region │ ordered_at │ revenue │
├────────┼────────────┼─────────┤
│ East   │ 2024-01-15 │  250.00 │
│ East   │ 2024-02-20 │   50.00 │
│ West   │ 2024-03-05 │  150.00 │
└────────┴────────────┴─────────┘

This runs, but it answers a different question. Adding ordered_at to the dimensions changed the grouping, so revenue is now per region per day. To get 2024 revenue per region you would have to re-aggregate the result yourself – and any metric that is not a plain sum (an average, a distinct count, a semi-additive snapshot) cannot be recovered that way at all.

Warning

This is the failure mode the inner WHERE exists to prevent. Attempt 2 produces numbers, and they are wrong for the question asked. Whenever a filter scopes what the metric measures – a date range, a customer segment, a status – it belongs inside SEMANTIC_VIEW(...).

Reuse a Predicate as a Named Filter¶

Added in version 0.12.0.

Rather than repeating a predicate in every query, declare it once as a member annotated LABELS = (FILTER). The label records that the member exists to be filtered on rather than selected:

CREATE OR REPLACE SEMANTIC VIEW order_metrics AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
    o.region     AS o.region,
    o.ordered_at AS o.ordered_at,
    o.is_2024 LABELS = (FILTER) AS o.ordered_at >= DATE '2024-01-01'
)
METRICS (
    o.revenue     AS SUM(o.amount),
    o.order_count AS COUNT(*)
);

Queries then name the filter instead of restating the predicate:

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue
    WHERE is_2024
) ORDER BY region;

Named filters compose with ordinary SQL operators – WHERE is_2024 AND region = 'East'. Each member’s expression is substituted parenthesized, so a filter defined as an OR keeps its grouping when you AND it with something else. See Mark a Named Filter for the annotation itself.

Use Both Filters in One Query¶

The two filters compose, each doing its own job: the inner WHERE scopes the rows the metrics see, and the outer WHERE trims the assembled result.

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue
    WHERE ordered_at >= DATE '2024-01-01'
) WHERE revenue > 200
ORDER BY revenue DESC;

┌────────┬─────────┐
│ region │ revenue │
├────────┼─────────┤
│ East   │  300.00 │
└────────┴─────────┘

West’s 2024 revenue of 150.00 is computed and then discarded by the outer predicate. Reversing the two filters would give a different answer: revenue > 200 applied to unfiltered totals keeps both regions.

Verify Which Rows Were Aggregated¶

explain_semantic_view() takes the inner WHERE as its where_clause parameter, so you can inspect the exact statement an application will issue before shipping it:

SELECT * FROM explain_semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue'],
    where_clause := 'ordered_at >= DATE ''2024-01-01'''
);

Look for the predicate ahead of the GROUP BY. It is placed before aggregation on every emission path – including inside each grain’s aggregate in a multi-grain query, inside the snapshot step of a semi-additive metric so that filtering changes which row wins, and inside the aggregate step of a window metric so that a filtered window number is recomputed rather than trimmed afterwards.

An omitted, empty, or whitespace-only where_clause is treated as absent, so where_clause := '' returns the unfiltered numbers.

Pass a Request Value from Application Code¶

When the filter value comes from outside your code – a date range in an HTTP request, a segment picked in a dashboard – keep that input out of the predicate’s SQL text. Write the predicate yourself and bind each value as a parameter. Each value goes into the query as a value of its own type, never as SQL, so nothing in it can change what the predicate does.

Danger

A predicate built by pasting request input into the SQL, whether into the construct’s WHERE or into a where_clause string, with an f-string or with '...' || ? in SQL, is open to SQL injection. The predicate runs with every right the connection has. Sent as the date in ordered_at >= DATE '...', the value 2024-01-01' OR true -- removes the date scope, and a subquery such as region IN (SELECT ... FROM other_table) or EXISTS (SELECT 1 FROM read_text('...')) reads any table or file the connection can reach. Binding the whole predicate with where_clause := ? does not help: the bound text is still SQL.

Two patterns keep request input out of the SQL text.

Bind the value in the construct’s WHERE. A prepared-statement parameter (?, $1 or $name) inside SEMANTIC_VIEW(...)’s WHERE is a parameter of your statement:

import datetime

import duckdb

con = duckdb.connect("analytics.duckdb")  # the database holding order_metrics
con.execute("LOAD semantic_views")


def revenue_by_region(since):
    return con.execute(
        """
        SELECT * FROM SEMANTIC_VIEW(order_metrics
            DIMENSIONS region
            METRICS revenue, order_count
            WHERE ordered_at >= ?
        ) ORDER BY region
        """,
        [since],
    ).fetchall()


revenue_by_region(datetime.date(2024, 1, 1))
# [('East', Decimal('300.00'), 2), ('West', Decimal('150.00'), 1)]

With semantic_view(), refer to each value in the where_clause text as $1, $2, …, and bind the values inside params. This is what the construct does for you:

con.execute(
    """
    SELECT * FROM semantic_view('order_metrics',
        dimensions := ['region'],
        metrics := ['revenue', 'order_count'],
        where_clause := 'ordered_at >= $1',
        params := row(?)
    ) ORDER BY region
    """,
    [datetime.date(2024, 1, 1)],
).fetchall()
# [('East', Decimal('300.00'), 2), ('West', Decimal('150.00'), 1)]

Map request choices to named filters. When the choices are a fixed set, declare each one as a named filter and look the request value up in an allowlist. The predicate is then always one of your own member names. This pattern uses semantic_view(), because the predicate itself is the bound value:

# Using con and the order_metrics view with the is_2024 named filter from above
NAMED_FILTERS = {"2024": "is_2024"}  # request value -> named filter


def revenue_for_period(period):
    predicate = NAMED_FILTERS[period]  # KeyError for anything not listed
    return con.execute(
        """
        SELECT * FROM semantic_view('order_metrics',
            dimensions := ['region'],
            metrics := ['revenue'],
            where_clause := ?
        ) ORDER BY region
        """,
        [predicate],
    ).fetchall()


revenue_for_period("2024")
# [('East', Decimal('300.00')), ('West', Decimal('150.00'))]

The dimensions and metrics lists are different: you can bind them directly, for example dimensions := ? with [["region"]]. The extension matches every name against the members the view declares and rejects anything else (unknown dimension 'amount'. Available: [region, ordered_at]), so a list value never reaches the generated SQL as text.

Troubleshooting¶

A predicate on a metric is rejected: WHERE revenue > 200 inside the construct, or where_clause := 'revenue > 200', is refused because the filter runs before aggregation, where an aggregate has no value yet. Move that predicate to the outer WHERE.
A binder error names a column that is not in the result: The filter is on the outer query but names a member the query did not request. Move it inside SEMANTIC_VIEW(...), where any declared dimension or fact resolves whether or not it is in the output.
A fan trap error names a member you only filtered on: Tables the predicate reaches are joined in and checked exactly as a queried dimension’s are, so a filter can trip the fan trap check on its own. See How to Diagnose and Fix Fan Traps for the fixes, and Metric Grain and How Queries Are Assembled for why the check exists.
A filter member on a role-played table is rejected: When a table is reachable through two named relationships, a predicate has no way to say which role it means, so the query errors rather than binding to whichever relationship was declared first. Filter on a member of a table that is reached one way only. See How to Model Role-Playing Dimensions.
The predicate fails on a type error the first time it is used: A named filter’s BOOLEAN requirement is enforced by DuckDB’s binder when the filter is first used, not at CREATE. Check that the member’s expression really evaluates to a boolean.
A parameter inside where_clause is refused: A ? or $name inside the where_clause string would be one of the calling statement’s own parameters, so it is refused. Write $1, $2, … and pass the values in params, or use the construct, whose WHERE takes ? directly, as shown in Pass a Request Value from Application Code.
Quoting looks wrong: where_clause is a SQL string literal, so every single quote inside it is doubled: where_clause := 'region = ''East'''. Double-quoted identifiers inside the predicate need no escaping. In the construct the predicate is plain SQL, so it is written as usual: WHERE region = 'East'.