Materializing dimensions and metrics in semantic views¶

To improve the query performance of a semantic view, you can materialize selected dimensions and metrics in the view, similar to how you materialize data in a materialized view.

When you query the semantic view, Snowflake reads from the materialized dimensions and metrics, rather than scanning the base table and computing the information.

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.

Setting the maximum staleness of the materialized dimensions and metrics¶

The MAX_STALENESS property determines the maximum amount of time that the materialized dimensions and metrics can be stale (out of date) before they are refreshed. For example, if you don’t want the materialized dimensions and metrics to be out of date by more than 1 hour, set the MAX_STALENESS property to ’1 hour’.

You can set this property when running the CREATE SEMANTIC VIEW command to create a new semantic view:

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 ]

You can also set this property on an existing semantic view by running ALTER SEMANTIC VIEW … SET MAX_STALENESS:

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

The following example creates a semantic view with a maximum staleness of 1 hour:

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';

The MAX_STALENESS property defines the maximum acceptable staleness for materialized data. The system uses this value to determine how frequently materializations are refreshed. Queries are only rewritten to use a materialization when the materialization is not older than the value of the MAX_STALENESS property.

Granting the privileges to materialize the dimensions and metrics¶

To materialize the dimensions and metrics in a semantic view and manage those materializations, you must use a role that has been granted the following privileges:

• the OWNERSHIP privilege on the semantic view
• the ADD SEMANTIC VIEW MATERIALIZATION privilege on the schema that contains the semantic view

To grant this privilege to a role, run the GRANT <privileges> … TO ROLE command. For more information about granting privileges, see Access control privileges.

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>
  [ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
  [ IMMUTABLE WHERE ( <immutable_condition> ) ]
AS
  DIMENSIONS <dimension_name> [ , ... ]
  METRICS <metric_name> [ , ... ]
  [ WHERE ( <filter_condition> ) ];

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.

• REFRESH_MODE = { AUTO | FULL | INCREMENTAL }: Specifies how the materialization is refreshed.

  • AUTO (default): Snowflake automatically determines the best refresh strategy. For materializations that span multiple entities, AUTO typically resolves to FULL.
  • FULL: Forces a full recomputation of the materialization on every refresh.
  • INCREMENTAL: Forces incremental refresh, processing only the changed data. Use this to incrementalize queries that AUTO mode would otherwise process as full refreshes. Not all query shapes support incremental refresh (for example, COUNT(DISTINCT ...) doesn’t support it).

• IMMUTABLE WHERE ( immutable_condition ): Specifies a condition that identifies rows that don’t change.

  You specify this to improve performance by limiting the scope of both incremental and full refreshes.

  Tip

  Use unqualified column names in the IMMUTABLE WHERE condition (for example, order_date rather than orders.order_date). The condition is evaluated on the result of the semantic view query, which uses unqualified names.

  Important

  Snowflake strongly recommends specifying this clause to reduce the cost and time of materialization refreshes. For more information, see Improving refresh performance with IMMUTABLE WHERE.

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

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

• WHERE ( filter_condition ): Specifies a filter that limits which rows the materialization stores. This is different from IMMUTABLE WHERE: WHERE controls which rows are included in the materialization and determines when queries can be rewritten to use it. IMMUTABLE WHERE is a refresh optimization hint that identifies rows that don’t need to be recomputed.

  Use unqualified column names (for example, order_year rather than orders.order_year). The condition is evaluated on the result of the semantic view query, which uses unqualified names.

  For more information, see Materializing a filtered subset of data.

For example:

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

To force incremental refresh for a multi-entity materialization:

ALTER SEMANTIC VIEW revenue_analysis ADD MATERIALIZATION revenue_by_customer_year
  WAREHOUSE = my_wh
  REFRESH_MODE = INCREMENTAL
  AS
    DIMENSIONS customers.customer_name, orders.order_year
    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. If you run ADD MATERIALIZATION with the same name:

• Same definition: The operation is a no-op.
• Different definition: The existing materialization is dropped and replaced with the new definition.

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 aren’t 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';

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.

• Reaggregation of additive metrics
• How dimensions in a WHERE clause are handled
• When materializations are not used

Reaggregation of additive metrics¶

When a materialization contains more dimensions than the query requests, Snowflake can reaggregate additive metrics. For example, a materialization on (customer_name, order_year) with SUM(total_revenue) can serve a query requesting only customer_name by summing across years.

If the metrics use the following functions, Snowflake can reaggregate the metrics:

• SUM
• COUNT
• MIN
• MAX

If metrics use the following functions, Snowflake can’t reaggregate the metrics:

• COUNT(DISTINCT … )
• APPROX_COUNT_DISTINCT
• MEDIAN
• PERCENTILE_CONT and PERCENTILE_DISC
• AVG
• Any DISTINCT aggregation
• Derived metrics referencing non-additive metrics

How dimensions in a WHERE clause are handled¶

Dimensions used in a WHERE clause count as covered dimensions. For example, a materialization on (customer_name, order_year) can be used when the following query is processed:

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

Expressions on dimensions in the WHERE clause (for example, MOD(orders.order_year, 2) = 0) are also supported, as long as the underlying dimension is covered by the materialization. The same applies to expressions on top of additive metrics and dimensions.

When materializations are not used¶

Snowflake falls back to scanning the base tables in the following situations:

• No materialization covers the requested dimensions or metrics.
• The materialization exceeds the value of the MAX_STALENESS property.
• A masking policy or row access policy exists on a column that is referenced by the materialization.
• Non-additive metrics require reaggregation.
• The materialization is in SUSPENDED state.
• The query’s WHERE clause is less restrictive than the materialization’s WHERE filter, or the query filters on a column that isn’t a materialized dimension. See Materializing a filtered subset of data.

Materializing a filtered subset of data¶

You can use a WHERE clause when adding a materialization to limit which rows are stored. This is useful for materializing only the most recent or most frequently queried data (for example, orders from the last two years).

ALTER SEMANTIC VIEW revenue_analysis ADD MATERIALIZATION recent_revenue
  WAREHOUSE = my_wh
AS
  DIMENSIONS orders.order_year, orders.category
  METRICS orders.total_revenue
  WHERE (order_year > 2020);

Snowflake rewrites a query to use this materialization when the query’s WHERE clause is compatible with the materialization’s filter:

Improving refresh performance with IMMUTABLE WHERE¶

If the semantic view contains non-additive metrics (for example, COUNT(DISTINCT ... )) or complex queries spanning multiple entities, REFRESH_MODE = INCREMENTAL is not supported and Snowflake performs a full recomputation on every refresh.

You can still improve the performance of these full refreshes by specifying the IMMUTABLE WHERE clause. The IMMUTABLE WHERE clause identifies rows that don’t change, which limits the scope of the full refresh. Rows that satisfy the condition are treated as immutable, so Snowflake only recomputes the mutable portion.

For example, the following statement adds a materialization where all orders before January 1, 2020 are treated as immutable:

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;

Updating the immutable condition¶

You can update the IMMUTABLE WHERE condition of an existing materialization without recreating it. This is useful when you need to move a watermark date forward as more data becomes historical.

To update the condition, run ADD MATERIALIZATION again with the same name and updated IMMUTABLE WHERE clause:

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

Suspending and resuming materializations¶

You can manually suspend a materialization to stop its background refreshes and prevent it from being used for query rewrites. To resume it later, use the RESUME command.

Materializations are also automatically suspended when:

• The semantic view is altered in a way that breaks the materialization definition (see Preserving materializations when altering a semantic view).
• A refresh fails or takes too long to complete within the MAX_STALENESS window. Check the refresh history to investigate.

ALTER SEMANTIC VIEW <name> SUSPEND MATERIALIZATION <materialization_name>;
ALTER SEMANTIC VIEW <name> RESUME MATERIALIZATION <materialization_name>;

For example:

ALTER SEMANTIC VIEW revenue_analysis SUSPEND MATERIALIZATION revenue_by_customer;

-- Later, resume it:
ALTER SEMANTIC VIEW revenue_analysis RESUME MATERIALIZATION revenue_by_customer;

Preserving materializations when altering a semantic view¶

By default, running CREATE OR REPLACE SEMANTIC VIEW drops all existing materializations and requires you to recreate them. To preserve materializations across changes to the semantic view, use CREATE OR ALTER SEMANTIC VIEW instead.

If you manage your semantic view through the SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML stored procedure, pass TRUE for the create_or_alter argument to get the same behavior.

CREATE OR ALTER SEMANTIC VIEW <name>
  ...

When you use CREATE OR ALTER:

• A materialization is only suspended if a calculation, relationship, or entity that it uses (directly or indirectly) is altered in a breaking way. For example, changing a metric’s aggregation function from SUM to COUNT suspends materializations that depend on that metric.
• Adding a new calculation, relationship, or entity, or altering one that isn’t used by any materialization, doesn’t affect existing materializations.
• Non-breaking changes (such as updating comments or tags) keep materializations active.
• Suspended materializations aren’t removed. If you decide to revert to the old calculation definition, you can resume the materialization:

ALTER SEMANTIC VIEW revenue_analysis RESUME MATERIALIZATION revenue_by_customer;

If the definition is no longer compatible, drop and recreate the materialization.

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.

Managing materializations declaratively with YAML¶

You can manage all materializations on a semantic view declaratively using SYSTEM$MANAGE_SEMANTIC_VIEW_MATERIALIZATIONS_FROM_YAML. This stored procedure synchronizes the materializations on a semantic view with a YAML specification:

• Materializations in the YAML that don’t exist are created.
• Materializations that exist but have a changed definition are replaced.
• Materializations that already exist with the same definition are left unchanged (no-op).
• Materializations that exist on the semantic view but aren’t in the YAML are dropped.

CALL SYSTEM$MANAGE_SEMANTIC_VIEW_MATERIALIZATIONS_FROM_YAML(
    '<semantic_view_name>',
    '<yaml_specification>'
);

The YAML specification uses the following structure:

materializations:
  - name: <materialization_name>
    warehouse: <warehouse_name>
    dimensions:
      - table: <entity_name>
        name: <dimension_name>
    metrics:
      - table: <entity_name>
        name: <metric_name>
  - name: <another_materialization>
    ...

For example:

CALL SYSTEM$MANAGE_SEMANTIC_VIEW_MATERIALIZATIONS_FROM_YAML(
    'revenue_analysis',
    $$
    materializations:
      - name: revenue_by_region
        warehouse: my_wh
        dimensions:
          - table: orders
            name: region
        metrics:
          - table: orders
            name: total_revenue
      - name: revenue_by_customer
        warehouse: my_wh
        dimensions:
          - table: customers
            name: customer_name
        metrics:
          - table: orders
            name: order_count
    $$
);

To drop all materializations, pass an empty list:

CALL SYSTEM$MANAGE_SEMANTIC_VIEW_MATERIALIZATIONS_FROM_YAML(
    'revenue_analysis',
    'materializations: []'
);

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:

Monitoring materialization refreshes¶

You can monitor materialization refreshes using refresh history and event tables.

Viewing refresh history¶

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:

Setting up alerting with event tables¶

You can configure materializations to emit refresh events to an event table, allowing you to create alerts for failures or suspension.

To enable event logging for a materialization:

ALTER SEMANTIC VIEW <name> ALTER MATERIALIZATION <materialization_name>
  SET LOG_EVENT_LEVEL = 'INFO';

To disable event logging:

ALTER SEMANTIC VIEW <name> ALTER MATERIALIZATION <materialization_name>
  UNSET LOG_EVENT_LEVEL;

Materialization refresh events appear in the event table with snow.executable.type = 'SEMANTIC_VIEW_MATERIALIZATION'. You can query the event table and set up alerts based on refresh status, similar to dynamic table alerting.