Cortex Analyst semantic model specification¶

Why use semantic models?¶

Cortex Analyst allows users to ask questions about Snowflake data using natural language. Typically there is a gap between the vocabulary used by business users when asking data questions and the vocabulary used in the database schema. These users typically use business or domain specific terms, whereas the database schema may use abbreviations or terms often used in ETL pipelines. Database schemas also often lack the semantic details to help someone unfamiliar with a dataset to understand the data. This difference in vocabulary makes it challenging for Cortex Analyst to answer data questions with high precision.

A semantic model is a lightweight mechanism that addresses these issues by allowing for the specification of additional semantic details about a dataset. These additional semantic details, like more descriptive names or synonyms, enable Cortex Analyst to answer data questions much more reliably.

Key concepts¶

The structure and concepts of the semantic model are similar to those in database schemas, but semantic models allow you to provide more semantic information about your data.

A core concept of Snowflake’s semantic models is the logical table. A logical table can be thought of as a simple view over a physical database table or view. Each logical table is a collection of logical columns, which are categorized into dimensions, time_dimensions, and measures.

A logical column can be either a reference to an underlying physical column or an expression involving one or more physical columns. The expression can be simple, such as a string concatenation or a mathematical operation, or it can include aggregation expressions. For example, you can use an expression like SUM(click)/SUM(spend) for calculating cost-per-click (CPC).

The semantic model can also contain a verified query repository (VQR), which can help improve the accuracy and trustworthiness of results by providing a collection of questions and corresponding SQL queries to answer them.

The semantic model for Cortex Analyst specification is in YAML. It provides the necessary semantic information to answer natural language questions with high precision.

The general syntax is:

# Name and description of the semantic model.
name: <name>
description: <string>

# A semantic model can contain one or more logical tables.
tables:

  # A logical table on top of a base table.
  - name: <name>
    description: <string>

    # The fully qualified name of the base table.
    base_table:
      database: <database>
      schema: <schema>
      table: <base table name>

    # Dimension columns in the logical table.
    dimensions:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        expr: <SQL expression>
        data_type: <data type>
        unique: <boolean>

    # Time dimension columns in the logical table.
    time_dimensions:
      - name: <name>
        synonyms:  <array of strings>
        description: <string>
        expr: <SQL expression>
        data_type: <data type>
        unique: <boolean>

    # Measure columns in the logical table.
    measures:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        expr: <SQL expression>
        data_type: <data type>
        default_aggregation: <aggregate function>

    # Commonly used filters over the logical table.
    filters:
      - name: <name>
        synonyms: <array of strings>
        description: <string>
        expr: <SQL expression>

# Example questions and queries that answer them
verified_queries:

# Verified Query (1 of n)
- name:         # A descriptive name of the query.
  question:     # The natural language question that this query answers.
  verified_at:  # Optional: Time (in seconds since the UNIX epoch, January 1, 1970) when the query was verified.
  verified_by:  # Optional: Name of the person who verified the query.
  sql:          # The SQL query for answering the question.

Semantic model generator¶

You can use the semantic model generator tool to generate a semantic model for use with Cortex Analyst. The tool offers multiple options for generating semantic models: through a Streamlit app, via the command line, or directly in your Python code. The Streamlit app is particularly convenient, allowing you to create semantic models from scratch or iterate on existing ones uploaded to a Snowflake stage.

Tips for creating a semantic model¶

• Organize your YAML file by business domain or topic

  • Structure your YAML files to align with specific business domains or topics, keeping the scope focused. For instance, create separate semantic models for sales analytics and marketing analytics.

  • Tailor your use cases based on the target audience, expected questions or KPIs, and required data. Well-defined use cases lead to richer semantic models and more effective data retrieval.

• Think from the end-user perspective

  • Identify key questions that users are likely to ask about the topic and include only the tables and columns needed to answer those questions.

  • Use names and synonyms that are similar to the vocabulary used by your end users.

  • Include important details in description fields that would be helpful to someone writing queries on this dataset for the first time, such as the timezone of a DATETIME column.

• Capture complex calculations

  Incorporate more difficult or business-specific queries into expressions.

• Use wide tables instead of long tables

  If you have a table with columns like “metric” and “value”, flatten the table so that each metric is a column. This approach provides the model with more semantic information on each metric.

• Review auto-generated descriptions

  If you’re using the semantic model generator, it attempts to automatically generate descriptions for your tables and columns. Always review these descriptions to ensure that they are reasonable and relevant; make modifications as needed.

• Start simple and expand gradually

  A well-scoped semantic file ensures higher precision and accuracy of results. Start with a small number of tables and columns and expand the semantic model YAML gradually to cover more kinds of questions. Remember that YAML building is a continuous process.

• Include verified queries

  A verified query repository (VQR), which is a collection of plain-English questions and queries that answer them, can help improve the accuracy and trustworthiness of results.

Known limitations¶

• Cortex Analyst imposes a 1MB size limit on the semantic model file to restrict the size of API inputs.

• Cortex Analyst performs in-memory retrieval of sample values and verified queries added to the semantic YAML. After removing all sample values and verified queries, the semantic model cannot exceed 32K tokens (roughly 4 x 32K characters or approximately 128 KB). You can use the semantic model generator validation command to ensure your file stays within these limits. The limits might increase as support is added for models with larger context windows.

Specification¶

Example YAML¶

This section shows an example semantic model YAML.

This example assumes you have the following table in your schema:

CREATE TABLE sales.public.sd_data (
    id INT PRIMARY KEY,
    dt DATETIME,
    cat VARCHAR(255),
    loc VARCHAR(255),
    cntry VARCHAR(255),
    chn VARCHAR(50),
    amt DECIMAL(10, 2),
    unts INT,
    cst DECIMAL(10, 2)
);

This table has cryptic column names and not much semantic information about the columns. The following is a semantic model YAML that gives more semantic information about this table:

# Name and description of the semantic model.
name: Sales Data
description: This semantic model can be used for asking questions over the sales data.

# A semantic model can contain one or more tables.
tables:

  # A logical table on top of the 'sd_data' base table.
  - name: sales_data
    description: A logical table capturing daily sales information across different store locations and product categories.

    # The fully qualified name of the base table.
    base_table:
      database: sales
      schema: public
      table: sd_data

    # Dimension columns in the logical table.
    dimensions:
      - name: product_category
        synonyms:
          - "item_category"
          - "product_type"
        description: The category of the product sold.
        expr: cat
        data_type: NUMBER
        unique: false
        sample_values:
          - "501"
          - "544"

      - name: store_country
        description: The country where the sale took place.
        expr: cntry
        data_type: TEXT
        unique: false
        sample_values:
          - "USA"
          - "GBR"

      - name: sales_channel
        synonyms:
          - "channel"
          - "distribution_channel"
        description: The channel through which the sale was made.
        expr: chn
        data_type: TEXT
        unique: false
        sample_values:
          - "FB"
          - "GOOGLE"

    # Time dimension columns in the logical table.
    time_dimensions:
      - name: sale_timestamp
        synonyms:
          - "time_of_sale"
          - "transaction_time"
        description: The time when the sale occurred. In UTC.
        expr: dt
        data_type: TIMESTAMP
        unique: false

    # Measure columns in the logical table.
    measures:
      - name: sales_amount
        synonyms:
          - "revenue"
          - "total_sales"
        description: The total amount of money generated from the sale.
        expr: amt
        data_type: NUMBER
        default_aggregation: sum

      - name: sales_tax
        description: The sales tax paid for this sale.
        expr: amt * 0.0975
        data_type: NUMBER
        default_aggregation: sum

      - name: units_sold
        synonyms:
          - "quantity_sold"
          - "number_of_units"
        description: The number of units sold in the transaction.
        expr: unts
        data_type: NUMBER
        default_aggregation: sum

      - name: cost
        description: The cost of the product sold.
        expr: cst
        data_type: NUMBER
        default_aggregation: sum

      - name: profit
        synonyms:
          - "earnings"
          - "net income"
        description: The profit generated from a sale.
        expr: amt - cst
        data_type: NUMBER
        default_aggregation: sum

    # A table can define commonly used filters over it. These filters can then be referenced in user questions directly.
    filters:
      - name: north_america
        synonyms:
          - "North America"
          - "N.A."
          - "NA"
        description: "A filter to restrict only to north american countries"
        expr: cntry IN ('canada', 'mexico', 'usa')