Back to Details

snowflake-semanticview

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Snowflake Semantic Views

Snowflake语义视图

One-Time Setup

一次性设置

Workflow For Each Semantic View Request

每个语义视图请求的工作流程

  1. Confirm the target database, schema, role, warehouse, and final semantic view name.
  2. Confirm the model follows a star schema (facts with conformed dimensions).
  3. Draft the semantic view DDL using the official syntax:
  4. Populate synonyms and comments for each dimension, fact, and metric:
    • Read Snowflake table/view/column comments first (preferred source):
    • If comments or synonyms are missing, ask whether you can create them, whether the user wants to provide text, or whether you should draft suggestions for approval.
  5. Use SELECT statements with DISTINCT and LIMIT (maximum 1000 rows) to discover relationships between fact and dimension tables, identify column data types, and create more meaningful comments and synonyms for columns.
  6. Create a temporary validation name (for example, append 
    __tmp_validate
    ) while keeping the same database and schema.
  7. Always validate by sending the DDL to Snowflake via Snowflake CLI before finalizing:
    • Use 
      snow sql
      to execute the statement with the configured connection.
    • If flags differ by version, check 
      snow sql --help
      and use the connection option shown there.
  8. If validation fails, iterate on the DDL and re-run the validation step until it succeeds.
  9. Apply the final DDL (create or alter) using the real semantic view name.
  10. Run a sample query against the final semantic view to confirm it works as expected. It has a different SQL syntax as can be seen here: https://docs.snowflake.com/en/user-guide/views-semantic/querying#querying-a-semantic-view Example:
SQL
SELECT * FROM SEMANTIC_VIEW(
    my_semview_name
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
)
ORDER BY customer_market_segment;
  1. Clean up any temporary semantic view created during validation.
  1. 确认目标数据库、模式、角色、数据仓库以及最终的语义视图名称。
  2. 确认模型遵循星型架构（带一致性维度的事实表）。
  3. 使用官方语法起草语义视图DDL：
  4. 为每个维度、事实和指标填充同义词与注释：
  5. 使用带DISTINCT和LIMIT（最多1000行）的SELECT语句来发现事实表与维度表之间的关系，识别列数据类型，并为列创建更有意义的注释和同义词。
  6. 创建临时验证名称（例如，追加
    __tmp_validate
    ），同时保持数据库和模式不变。
  7. 在定稿前，始终通过Snowflake CLI将DDL发送到Snowflake进行验证：
    • 使用
      snow sql
      通过已配置的连接执行语句。
    • 如果不同版本的标志不同，请查看
      snow sql --help
      并使用其中显示的连接选项。
  8. 如果验证失败，迭代修改DDL并重新运行验证步骤，直到成功。
  9. 使用真实的语义视图名称应用最终的DDL（创建或修改）。
  10. 针对最终语义视图运行示例查询，确认其按预期工作。其SQL语法有所不同，可在此查看：https://docs.snowflake.com/en/user-guide/views-semantic/querying#querying-a-semantic-view 示例：
SQL
SELECT * FROM SEMANTIC_VIEW(
    my_semview_name
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
)
ORDER BY customer_market_segment;
  1. 清理验证期间创建的所有临时语义视图。

Synonyms And Comments (Required)

同义词与注释（必填）

  • Use the semantic view syntax for synonyms and comments:
WITH SYNONYMS [ = ] ( 'synonym' [ , ... ] )
COMMENT = 'comment_about_dim_fact_or_metric'
  • Treat synonyms as informational only; do not use them to reference dimensions, facts, or metrics elsewhere.
  • Use Snowflake comments as the preferred and first source for synonyms and comments:
  • If Snowflake comments are missing, ask whether you can create them, whether the user wants to provide text, or whether you should draft suggestions for approval.
  • Do not invent synonyms or comments without user approval.
  • 使用语义视图语法来定义同义词和注释：
WITH SYNONYMS [ = ] ( 'synonym' [ , ... ] )
COMMENT = 'comment_about_dim_fact_or_metric'
  • 同义词仅作信息参考；请勿在其他地方使用它们引用维度、事实或指标。
  • 优先使用Snowflake注释作为同义词和注释的首选来源：
  • 如果缺少Snowflake注释，请询问用户是否允许创建、是否要提供文本，或者是否需要起草建议供其审批。
  • 未经用户批准，请勿自行创建同义词或注释。

Validation Pattern (Required)

验证模式（必填）

  • Never skip validation. Always execute the DDL against Snowflake with Snowflake CLI before presenting it as final.
  • Prefer a temporary name for validation to avoid clobbering the real view.
  • 切勿跳过验证步骤。在定稿前，始终通过Snowflake CLI将DDL提交到Snowflake执行验证。
  • 验证时优先使用临时名称，避免覆盖真实视图。

Example CLI Validation (Template)

CLI验证示例（模板）

bash
undefined
bash
undefined

Replace placeholders with real values.

替换占位符为实际值。

snow sql -q "<CREATE OR ALTER SEMANTIC VIEW ...>" --connection <connection_name>

If the CLI uses a different connection flag in your version, run:

```bash
snow sql --help
snow sql -q "<CREATE OR ALTER SEMANTIC VIEW ...>" --connection <connection_name>

如果你的版本中CLI使用不同的连接标志，请运行：

```bash
snow sql --help

Notes

注意事项

  • Treat installation and connection setup as one-time steps, but confirm they are done before the first validation.
  • Keep the final semantic view definition identical to the validated temporary definition except for the name.
  • Do not omit synonyms or comments; consider them required for completeness even if optional in syntax.
  • 将安装和连接设置视为一次性步骤，但在首次验证前需确认已完成这些步骤。
  • 最终语义视图定义应与经过验证的临时定义完全一致，仅名称不同。
  • 请勿省略同义词或注释；即使语法上是可选的，也需将其视为完整性的必填项。