Skip to content
Blog/Accelerating Semantic Views with Materializations
Aug 12, 2026/4 min read

Accelerating Semantic Views with Materializations

Benjamin Hilprecht +1

Semantic views give you a single, governed definition of your metrics and dimensions offering one source of truth shared across BI tools, Cortex Analyst and ad hoc Semantic SQL. Just like regular views, Snowflake still computes queries against the base tables, which can result in complex plans and long-running queries on large data sets.语义视图（Semantic views）为您提供了一种统一且受治理的指标和维度定义，为 BI 工具、Cortex Analyst 和即席 Semantic SQL 提供了单一事实来源。与普通视图一样，Snowflake 仍然会对基础表进行查询计算，这可能会导致在大数据集上产生复杂的执行计划和长时间运行的查询。

Semantic view materializations fix this using an approach that is similar to materialized views: We precompute semantic view (SV) aggregations and queries that benefit from the materialization are rewritten automatically to use it. This way, SV workloads can run orders of magnitude faster. In contrast to regular materialized views, materializations are specified directly using the dimensions and metrics of the SV.语义视图物化（Materialization）通过一种类似于物化视图的方法解决了这个问题：我们预先计算语义视图（SV）的聚合，而受益于该物化的查询会自动重写以使用它。通过这种方式，SV 工作负载的运行速度可以提高几个数量级。与普通物化视图不同，物化是直接使用 SV 的维度和指标来指定的。

Snowflake keeps the materialization up to date in the background — bounded by a staleness target you set — and a single materialization can serve many different queries by re-aggregating a fine-grained rollup up to whatever grain each query asks for.Snowflake 会在后台保持物化数据的最新状态（受您设置的陈旧度目标限制），并且单个物化可以通过将细粒度的汇总数据重新聚合到每个查询所需的粒度，从而服务于许多不同的查询。

How it works工作原理

Prerequisites. Before you can materialize anything, set a freshness bound on the semantic view:先决条件。在进行任何物化之前，请为语义视图设置一个新鲜度界限：

ALTER SEMANTIC VIEW revenue_analysis SET MAX_STALENESS = '1 hour';

A max_staleness of 1 hour means that we will never return results that are more than one hour stale — materializations that lag behind by more than one hour are not used automatically. This allows us to trade refresh costs and freshness.max_staleness 为 1 小时意味着我们永远不会返回陈旧超过一小时的结果——滞后超过一小时的物化版本不会被自动使用。这使我们能够在刷新成本和新鲜度之间进行权衡。

Creating a materialization. You declare the dimensions and metrics to precompute and the warehouse that builds and refreshes it:创建物化。您需要声明要预计算的维度和指标，以及用于构建和刷新它的仓库：

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

Staying fresh: Snowflake refreshes the materialization in the background to keep it within MAX_STALENESS, using the warehouse you specified. REFRESH_MODE can be AUTO, FULL or INCREMENTAL; with AUTO, Snowflake picks the mode. You can always force a refresh manually:保持新鲜：Snowflake 使用您指定的仓库在后台刷新物化，以将其保持在 MAX_STALENESS 范围内。REFRESH_MODE 可以是 AUTO、FULL 或 INCREMENTAL；使用 AUTO 时，Snowflake 会自动选择模式。您随时可以手动强制刷新：

ALTER SEMANTIC VIEW revenue_analysis REFRESH MATERIALIZATION customer_year_rollup;

How the planner uses it: When you query the semantic view, the planner selects the lowest-cost materialization that covers the requested dimensions and metrics. Two behaviors make one materialization cover a wide range of queries:规划器如何使用它：当您查询语义视图时，规划器会选择涵盖所请求维度和指标的成本最低的物化版本。两种行为使得单个物化能够涵盖广泛的查询：

  • Re-aggregation of additive metrics: A materialization on (customer_name, order_year) with SUM(total_revenue) can answer a query that asks only for customer_name — Snowflake just sums across years. SUM, COUNT, MIN and MAX all re-aggregate this way.可加性指标的重新聚合：基于 (customer_name, order_year) 和 SUM(total_revenue) 的物化可以回答仅请求 customer_name 的查询——Snowflake 只需跨年份求和即可。SUM、COUNT、MIN 和 MAX 都可以通过这种方式进行重新聚合。
  • WHERE-clause dimensions count as covered. A filter like WHERE order_year = 2024 is satisfied directly from the stored rollup, as long as order_year is a materialized dimension (expressions on those dimensions work too).WHERE 子句中的维度被视为已覆盖。只要 order_year 是已物化的维度（对这些维度的表达式也有效），像 WHERE order_year = 2024 这样的过滤器就可以直接从存储的汇总数据中得到满足。

You can also materialize a filtered subset — for example, just the last two years of orders — by adding a WHERE clause to the materialization. Queries with an equal-or-more-restrictive filter still get accelerated.您还可以通过在物化中添加 WHERE 子句来物化过滤后的子集（例如，仅最近两年的订单）。具有相同或更严格过滤器的查询仍然会得到加速。

Limitations限制

The single most important constraint is additive vs. non-additive metrics, because it determines whether one rollup can serve many queries:最重要的一项约束是可加性指标与非可加性指标的区别，因为它决定了单个汇总数据能否服务于多个查询：

  • Additive metrics — SUM, COUNT, MIN, MAX — can be re-aggregated. A fine-grained rollup serves coarser queries by rolling up across the extra dimensions. This is what makes materializations broadly reusable.可加性指标（SUM、COUNT、MIN、MAX）可以重新聚合。细粒度的汇总数据可以通过跨额外维度进行汇总来服务于更粗粒度的查询。这就是物化具有广泛可重用性的原因。
  • Non-additive metrics cannot be re-aggregated, so a materialization only helps a query at exactly the stored grain. This includes COUNT(DISTINCT), APPROX_COUNT_DISTINCT, MEDIAN, PERCENTILE_CONT/PERCENTILE_DISC, AVG, any DISTINCT aggregation and derived metrics that reference non-additive metrics. An expression on top of an aggregation (for example, 2*SUM(x)+COUNT(y)) is also not additive.非可加性指标无法重新聚合，因此物化仅在完全匹配存储粒度时才对查询有帮助。这包括 COUNT(DISTINCT)、APPROX_COUNT_DISTINCT、MEDIAN、PERCENTILE_CONT/PERCENTILE_DISC、AVG、任何 DISTINCT 聚合以及引用了非可加性指标的派生指标。聚合之上的表达式（例如 2*SUM(x)+COUNT(y)）也是不可加的。

A few other things to know:还需要了解的其他几点：

  • Only Semantic SQL benefits: Queries through the SEMANTIC_VIEW construct or standard SQL against the semantic view are accelerated. Cortex Analyst, Cortex Agents and CoWork paths that emit physical SQL directly against the base tables do not benefit.仅 Semantic SQL 受益：通过 SEMANTIC_VIEW 构造或针对语义视图的标准 SQL 进行的查询会得到加速。直接针对基础表发出物理 SQL 的 Cortex Analyst、Cortex Agents 和 CoWork 路径不会受益。
  • Base-table scans: A query falls back to a base-table scan when no materialization covers the requested dimensions/metrics; the data exceeds the MAX_STALENESS window; a masking or row-access policy exists on a referenced column; a non-additive metric would need re-aggregation; the materialization is suspended; or the query's WHERE is less restrictive than the materialization's filter (or filters a non-materialized column).基础表扫描：当满足以下情况时，查询将回退到基础表扫描：没有物化版本覆盖所请求的维度/指标；数据超过了 MAX_STALENESS 窗口；引用列上存在掩码或行访问策略；需要对非可加性指标进行重新聚合；物化已挂起；或者查询的 WHERE 子句比物化的过滤器限制更少（或过滤了未物化的列）。
  • Auto-suspend on staleness: If MAX_STALENESS is too low for background refreshes to keep up, Snowflake automatically suspends the materialization. Recover by raising MAX_STALENESS or simplifying the definition.陈旧自动挂起：如果 MAX_STALENESS 设置过低导致后台刷新无法跟上，Snowflake 会自动挂起该物化。可以通过提高 MAX_STALENESS 或简化定义来恢复。

Benchmark: four TPC-DS queries, one materialization基准测试：四个 TPC-DS 查询，一个物化

To measure the effect, we took four TPC-DS queries that all share the same star pattern (store_sales × item × date_dim, aggregating SUM(ss_ext_sales_price)) and rewrote them using semantic SQL on an SV on TPC-DS data. Because they share that pattern, one materialization serves all four via additive re-aggregation and covered WHERE dimensions:为了衡量效果，我们选取了四个共享相同星型模式（store_sales × item × date_dim，聚合 SUM(ss_ext_sales_price)）的 TPC-DS 查询，并使用语义 SQL 在 TPC-DS 数据上的 SV 对它们进行了重写。由于它们共享该模式，单个物化通过可加性重新聚合和覆盖的 WHERE 维度为所有四个查询提供了服务：

ALTER SEMANTIC VIEW TPCDS_NLQ_VIEW ADD MATERIALIZATION ss_item_month_rollup
  WAREHOUSE = TEST_WAREHOUSE
  AS
    DIMENSIONS date_dim.d_year, date_dim.d_moy,
               item.i_brand, item.i_brand_id,
               item.i_category, item.i_category_id,
               item.i_manufact_id, item.i_manager_id
    METRICS store_sales.ss_ext_sales_price_sum;

Results结果

Creating a single materialization sped up the four queries between 59x and 91x, resulting in a 1.5x order of magnitude improvement in execution time. Also, we scan significantly less data (359GB vs. just 120MB) since the planner automatically rewrites our queries to scan the (much smaller) materialization instead. In this chart you can see the query runtime duration improvement of four TPC-DS queries (Q3, Q42, Q52 and Q55) after creating a single semantic view materialization.创建一个物化版本使这四个查询的速度提高了 59 到 91 倍，执行时间缩短了 1.5 个数量级。此外，我们扫描的数据量显著减少（从 359GB 减少到仅 120MB），因为规划器会自动重写我们的查询以扫描（小得多的）物化版本。在此图表中，您可以看到在创建单个语义视图物化后，四个 TPC-DS 查询（Q3、Q42、Q52 和 Q55）的查询运行时间得到了改善。

Figure 1. Accelerating semantic views with materializations
Figure 1. Accelerating semantic views with materializations

Try it yourself亲自尝试

Materializations pay off when you have:在以下情况下，物化非常值得：

  • Repeated AI agent or dashboard queries with similar patterns具有相似模式的重复 AI 代理或仪表板查询
  • Additive metrics, so one fine-grained rollup can serve many coarser queries or similar, repeating query shapes存在可加性指标，因此一个细粒度的汇总数据可以服务于许多更粗粒度的查询或类似的重复查询形态
  • Large base tables大型基础表
  • A staleness tolerance you can express with MAX_STALENESS.您可以通过 MAX_STALENESS 表示的陈旧度容忍度。

The trade-off is straightforward: You spend background-refresh compute and some storage to speed up your repeated workloads.权衡很简单：您消耗后台刷新计算资源和一些存储空间，以加速您的重复工作负载。

Links链接

Docs:文档：

Share this post

Subscribe to our blog newsletter

Get the best, coolest and latest delivered to your inbox each week

Where Data Does More

Start for free
Watch a demo