How to Use Materializations

This guide shows how to declare materializations in a semantic view so that queries whose dimensions and metrics exactly match a materialization are routed to a pre-aggregated table instead of expanding raw sources with JOINs and GROUP BY. Materializations can improve query performance for common access patterns without changing the query interface.

Added in version 0.7.0.

Prerequisites:

  • A working semantic view with TABLES, DIMENSIONS, and METRICS (see Multi-Table Semantic Views)

  • A pre-aggregated table containing the pre-computed results you want to route to

How Materializations Work

A materialization maps a set of dimensions and metrics to a physical table that already contains the pre-aggregated results for that exact combination. When a query requests that exact set of dimensions and metrics, the extension reads directly from the pre-aggregated table instead of scanning raw tables, building JOINs, and computing GROUP BY.

The query interface (semantic_view()) does not change. Routing is transparent – the caller does not specify which table to use. The extension decides based on the materialization declarations.

Declare a Materialization

Add a MATERIALIZATIONS clause after METRICS in the CREATE SEMANTIC VIEW DDL. Each materialization entry names a pre-aggregated table and lists the dimensions and metrics it covers.

First, create the pre-aggregated table:

CREATE TABLE daily_revenue_by_region AS
SELECT region, SUM(amount) AS revenue, COUNT(*) AS order_count
FROM orders
GROUP BY region;

Then declare a semantic view with a materialization pointing to that table:

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

The MATERIALIZATIONS clause must appear after all other clauses. The clause order is: TABLES, RELATIONSHIPS, FACTS, DIMENSIONS, METRICS, MATERIALIZATIONS.

Tip

The pre-aggregated table must have columns named after the dimensions and metrics it covers. The extension selects columns by name from the materialization table.

How Routing Works

Routing uses exact match logic. A materialization matches a query when both of these conditions are true:

  1. The materialization’s dimension set exactly equals the requested dimension set.

  2. The materialization’s metric set exactly equals the requested metric set.

Matching is case-insensitive. If a query requests region and revenue, a materialization declaring Region and Revenue still matches.

When a match is found, the extension generates a simple SELECT ... FROM <materialization_table> instead of the usual JOINs and GROUP BY. When no match is found, the extension falls back to standard expansion against the raw tables.

-- This query matches the materialization (exact dims + metrics)
-- Routes to daily_revenue_by_region
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue', 'order_count']
);

-- This query does NOT match (different metric set)
-- Falls back to standard expansion
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue']
);

Warning

Superset matching is not supported in v0.7.0. If a materialization covers [region] + [revenue, order_count] and a query requests only [region] + [revenue], the materialization does not match. The query falls back to standard expansion.

Multiple Materializations

A semantic view can declare multiple materializations for different access patterns. The extension scans materializations in definition order and uses the first match.

CREATE SEMANTIC VIEW order_metrics AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
    o.region AS o.region,
    o.status AS o.status
)
METRICS (
    o.revenue     AS SUM(o.amount),
    o.order_count AS COUNT(*)
)
MATERIALIZATIONS (
    region_agg AS (
        TABLE revenue_by_region,
        DIMENSIONS (region),
        METRICS (revenue, order_count)
    ),
    region_status_agg AS (
        TABLE revenue_by_region_status,
        DIMENSIONS (region, status),
        METRICS (revenue)
    )
)

Queries match whichever materialization covers their exact dimension and metric set. If two materializations cover the same set, the first one declared wins.

Routing Exclusions

Two kinds of metrics are always excluded from materialization routing, even when a materialization declaration covers them:

  • Semi-additive metrics (declared with NON ADDITIVE BY): These require snapshot selection logic (ROW_NUMBER CTE) that cannot be pre-computed in a materialization table.

  • Window metrics (declared with OVER): These require CTE-based window expansion that depends on the queried dimensions.

When a query includes any semi-additive or window metric, the extension skips all materializations and falls back to standard expansion, regardless of whether a matching materialization exists.

CREATE SEMANTIC VIEW account_metrics AS
TABLES (
    a AS accounts PRIMARY KEY (id)
)
DIMENSIONS (
    a.customer_id  AS a.customer_id,
    a.report_date  AS a.report_date
)
METRICS (
    a.total_balance NON ADDITIVE BY (report_date DESC) AS SUM(a.balance)
)
MATERIALIZATIONS (
    balance_agg AS (
        TABLE balance_by_customer,
        DIMENSIONS (customer_id),
        METRICS (total_balance)
    )
)

-- This query falls back to standard expansion (semi-additive metric)
-- even though a matching materialization exists
SELECT * FROM semantic_view('account_metrics',
    dimensions := ['customer_id'],
    metrics := ['total_balance']
);

Verify Routing with explain_semantic_view()

Use explain_semantic_view() to check whether a query routes to a materialization. The output includes a -- Materialization: header line that shows either the materialization name or none.

SELECT * FROM explain_semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue', 'order_count']
);

Sample output when routing matches:

-- Semantic View: order_metrics
-- Dimensions: region
-- Metrics: revenue, order_count
-- Materialization: region_agg

-- Expanded SQL:
SELECT
    "region",
    "revenue",
    "order_count"
FROM "daily_revenue_by_region"

When no materialization matches, the output shows -- Materialization: none and the expanded SQL contains the standard JOINs and GROUP BY.

Inspect with SHOW and DESCRIBE

Use SHOW SEMANTIC MATERIALIZATIONS to list all materializations declared in a view or across all views:

-- List materializations for a specific view
SHOW SEMANTIC MATERIALIZATIONS IN order_metrics;

-- List materializations across all views
SHOW SEMANTIC MATERIALIZATIONS;

Use DESCRIBE SEMANTIC VIEW to see materialization details alongside all other view objects:

SELECT object_kind, object_name, property, property_value
FROM (DESCRIBE SEMANTIC VIEW order_metrics)
WHERE object_kind = 'MATERIALIZATION';

Troubleshooting

Query not routing to materialization when expected

  • Use explain_semantic_view() to check the -- Materialization: line.

  • Verify the dimension and metric sets in the query exactly match the materialization declaration (supersets and subsets do not match).

  • Check whether any metric in the query is semi-additive (NON ADDITIVE BY) or a window metric (OVER). These are always excluded from routing.

Materialization table has wrong column names

The materialization table must have columns named to match the dimension and metric names declared in the semantic view. If the pre-aggregated table uses different column names, the extension cannot select from it correctly.

DDL error: dimension or metric not found

Materialization declarations reference dimensions and metrics by name. The names must match dimensions and metrics declared earlier in the same CREATE SEMANTIC VIEW statement. The error message suggests close matches.

DDL error: MATERIALIZATIONS clause out of order

The MATERIALIZATIONS clause must appear after METRICS. Move it to the end of the DDL body.

DDL error: must specify at least one of DIMENSIONS or METRICS

Each materialization entry must declare at least one dimension or one metric. A materialization with only a TABLE and neither DIMENSIONS nor METRICS is rejected.

See Error Messages for the full list of materialization validation errors.