Preview — 9.20 Release Notes¶

CREATE INDEX command supports INCLUDE columns¶

With this release, you can use the CREATE INDEX command to create secondary indexes with INCLUDE columns. In previous releases, INCLUDE columns were supported only for secondary indexes defined within CREATE HYBRID TABLE statements.

Semantic views: Listing dimensions and metrics in a view, schema, database, or account¶

To list the dimensions and metrics in a semantic view, schema, database, or account, run the following commands:

• SHOW SEMANTIC DIMENSIONS

• SHOW SEMANTIC METRICS

You can also list the dimensions that you can specify when querying for a specific metric. When you specify a dimension and metric in a query, the base table for the dimension must be related to the base table for the metric. In addition, the base table for the dimension must have an equal or lower level of granularity than the base table for the metric.

For example, the following example queries the tpch_analysis view and returns the customer_order_count metric and the order_date dimension:

SELECT * FROM SEMANTIC_VIEW (
  tpch_analysis
  DIMENSIONS orders.order_date
  METRICS customer.customer_order_count
);

This query fails because the orders table for the dimension has a higher level of granularity than the customer table for the metric:

010234 (42601): SQL compilation error:
Invalid dimension specified: The dimension entity 'ORDERS' must be related to and
have an equal or lower level of granularity compared to the base metric or dimension entity 'CUSTOMER'.

To list the dimensions that have base tables that are related to and are at an equal or lower level of granularity than the base table for a metric, run the SHOW SEMANTIC DIMENSIONS FOR METRIC command. For example:

SHOW SEMANTIC DIMENSIONS IN tpch_analysis FOR METRIC customer_order_count;

New query insights about join performance and optimization¶

The QUERY_INSIGHTS view now includes insights about the following conditions that might have affected query performance:

• A query or subquery has no WHERE clause, which means that the query scans an entire table and might return more rows than intended.

• A join that includes the output of at least one other join is returning many more rows than are in the tables being joined.

• A join of two data sets (for example, tables, views, or output from table function calls) is returning many more rows than are in the tables being joined.

• The performance of a query has been improved through search optimization.

Each insight includes a message that explains how query performance might have been affected and provides a general recommendation for next steps.

Tasks: New EXECUTE AS USER option and IMPERSONATE privilege for user objects¶

With this release, organizations that assign Snowflake security privileges by user can allow users to run team tasks by using their existing user accounts. As a best practice, we recommend that teams create a service user that represents a team, and assign required privileges to that user. You can then use GRANT IMPERSONATE ON USER <user_name> TO ROLE <role_name> to grant users privileges to create or modify tasks based on the team user account. Individual users can then run tasks on behalf of the team user to use their privileges with the new parameters: CREATE TASK … EXECUTE AS USER <user_name> and ALTER TASK … EXECUTE AS USER <user_name>.

Dynamic tables: Disallowed use of the COPY_SESSION attribute while manually refreshing dynamic tables on a serverless warehouse¶

Using COPY_SESSION with a dynamic table in a serverless context causes the refresh to inherit the serverless warehouse, leading to unsupported and undefined behavior. This configuration now results in an error.