Materializing dimensions and metrics in semantic views¶

Preparing a semantic view to support materialization¶

Before you can materialize the dimensions and metrics in a semantic view, you must

• Set the MAX_STALENESS property of the semantic view.

• Verify that you are using a role that has been granted the privileges to materialize the dimensions and metrics.

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <name>
  ...
  [ AI_QUESTION_CATEGORIZATION '<instructions_for_question_categorization>' ]
  [ MAX_STALENESS = '<num> { seconds | minutes | hours | days }' ]
  [ COPY GRANTS ]

ALTER SEMANTIC VIEW <name> SET MAX_STALENESS = '<num> { seconds | minutes | hours | days }'

CREATE OR REPLACE SEMANTIC VIEW revenue_analysis
  TABLES (
    orders AS ORDERS PRIMARY KEY (o_orderkey),
    customers AS CUSTOMER PRIMARY KEY (c_custkey)
  )
  RELATIONSHIPS (
    orders_to_customers AS orders (o_custkey) REFERENCES customers
  )
  DIMENSIONS (
    customers.customer_name AS c_name,
    orders.order_date AS o_orderdate,
    orders.order_year AS YEAR(o_orderdate)
  )
  METRICS (
    orders.total_revenue AS SUM(o_totalprice),
    orders.avg_revenue AS AVG(o_totalprice),
    orders.order_count AS COUNT(o_orderkey)
  )
  MAX_STALENESS = '1 hour';

Materializing dimensions and metrics¶

To materialize dimensions and metrics in a semantic view, run ALTER SEMANTIC VIEW … ADD MATERIALIZATION:

ALTER SEMANTIC VIEW <name> ADD MATERIALIZATION <materialization_name>
  WAREHOUSE = <warehouse_name>
  [ IMMUTABLE WHERE ( <condition> ) ]
  AS
    DIMENSIONS <dimension_name> [ , ... ]
    METRICS <metric_name> [ , ... ];

where:

• name: Specifies the name of the semantic view.

• materialization_name: Specifies the name of the materialization to add.

• WAREHOUSE = warehouse_name: Specifies the warehouse to use when materializing the dimensions and metrics.

• IMMUTABLE WHERE ( condition ): Specifies a condition that must be true for the dimensions and metrics to be materialized.

  You specify this to improve performance by limiting the scope of the refresh.

  Important

  Snowflake strongly recommends specifying this clause to reduce the cost and time of materialization refreshes. For more information, see Improving performance by incrementally refreshing materializations.

• DIMENSIONS dimension_name [ , ... ]: Specifies the dimensions to materialize.

• METRICS metric_name [ , ... ]: Specifies the metrics that you want to pre-aggregate.

For example:

ALTER SEMANTIC VIEW revenue_analysis ADD MATERIALIZATION revenue_by_customer
  WAREHOUSE = my_wh
  AS
    DIMENSIONS customers.customer_name
    METRICS orders.total_revenue;

You can add multiple materializations to a semantic view. Each materialization specifies a subset of dimensions and metrics to pre-aggregate.

You can also use this command to update the definition of an existing materialization.

Snowflake automatically refreshes the materialization regularly to fulfill the desired MAX_STALENESS. If the refreshes take too long and the materializations become more stale than the limit, they are not used for rewrites. In those cases, check the refresh history and consider increasing the MAX_STALENESS on the semantic view:

ALTER SEMANTIC VIEW revenue_analysis SET MAX_STALENESS = '2 hours';

Improving performance by incrementally refreshing materializations¶

If the semantic view contains non-additive metrics (for example, COUNT(DISTINCT ... )) or complex queries spanning multiple entities, Snowflake recomputes all of the data that is materialized. This is referred to as a full refresh.

To improve performance, you can limit the scope of the refresh to only the data that has changed since the last refresh. This is referred to as an incremental refresh.

To limit the scope of the refresh, specify the IMMUTABLE WHERE clause with a condition when you add or modify the materialization. Only the rows that match the condition are recomputed during the next refresh.

For example, the following statement adds a materialization that limits the scope of the refresh to only the data that has changed since January 1, 2020:

ALTER SEMANTIC VIEW revenue_analysis ADD MATERIALIZATION historical_revenue
    WAREHOUSE = my_wh
    IMMUTABLE WHERE (order_date < '2020-01-01')
AS
    DIMENSIONS orders.order_date
    METRICS orders.total_revenue;

Refreshing a materialization manually¶

If you want to refresh the materialization of a set of dimensions and metrics, run ALTER SEMANTIC VIEW … REFRESH MATERIALIZATION:

ALTER SEMANTIC VIEW <name> REFRESH MATERIALIZATION <materialization_name>;

For example, the following statement refreshes the revenue_by_customer materialization for the revenue_analysis semantic view:

ALTER SEMANTIC VIEW revenue_analysis REFRESH MATERIALIZATION revenue_by_customer;

When you run this command, the manual refresh uses the current warehouse of the session. Background refreshes occur automatically based on the MAX_STALENESS property using the warehouse specified when the materialization was created.

Removing a materialization¶

To remove a materialization from a semantic view, run ALTER SEMANTIC VIEW … DROP MATERIALIZATION:

ALTER SEMANTIC VIEW <name> DROP MATERIALIZATION <materialization_name>;

For example, the following statement removes the revenue_by_customer materialization from the revenue_analysis semantic view:

ALTER SEMANTIC VIEW revenue_analysis DROP MATERIALIZATION revenue_by_customer;

Listing the materializations for a semantic view¶

To list the materializations for a semantic view, run the SHOW MATERIALIZATIONS command:

SHOW MATERIALIZATIONS IN SEMANTIC VIEW <name>;

For example, to list the materializations for the revenue_analysis semantic view:

SHOW MATERIALIZATIONS IN SEMANTIC VIEW revenue_analysis;

The command returns tabular output in the following columns:

Viewing the history of refreshes¶

To view the history of refreshes for a materialization, call the SEMANTIC_VIEW_MATERIALIZATION_REFRESH_HISTORY table function in the INFORMATION_SCHEMA schema:

SEMANTIC_VIEW_MATERIALIZATION_REFRESH_HISTORY(
    NAME => '<materialization_name>'
)

For example, to view the history of refreshes for the revenue_by_customer materialization:

SELECT * FROM TABLE(INFORMATION_SCHEMA.SEMANTIC_VIEW_MATERIALIZATION_REFRESH_HISTORY(
    NAME => 'revenue_by_customer'
));

The function returns tabular output in the following columns:

How materializations are used when processing a query¶

When you query a semantic view, Snowflake uses the materializations to fulfill the query. The planner selects the lowest-cost materialization that covers the requested dimensions and metrics. The next sections explain how materializations are used when Snowflake processes a query.

SELECT * FROM SEMANTIC_VIEW(
    revenue_analysis
    DIMENSIONS customers.customer_name
    METRICS orders.total_revenue
    WHERE orders.order_year = 2024
);