Snowflake table design

The speed at which data is fetched from Snowflake is largely determined by how much effort is required by the query engine to retrieve the data.

In general, a regular table, dynamic table or materialized view will perform better than a view, especially if there are joins or functions involved. The use of non-deterministic functions in views can also prevent query result reuse, which can prevent repeated Salesforce record/list view operations from benefiting from this Snowflake feature.

The most reliable way to understand query performance is to look at the query history of the queries coming from Salesforce.

If you need some help figuring out performance, there are some useful Snowflake performance optimisation apps on the Snowflake marketplace, or reach out to us at [e-mail].

There are two types of queries which Omnata Connect submits to Snowflake:

Individual record lookups

When opening a record page in Salesforce, the Snowflake query will look like:

select <columns> from <table> where <ID column> = <value>

These queries typically complete very quickly without any tuning, since Snowflake can efficiently prune the query.

Range lookups

When opening a list view in Salesforce, the Snowflake query will look like:

select <columns> from <table> where <list view criteria>

Since the list view criteria can vary, the ability for these queries to complete quickly can depend on the clustering of the table. It is recommended that the most popular list views (e.g. recent date ranges) are catered for in the clustering of the table.

If a list view is expected to routinely have a lot of different filtering criteria applied, it may be beneficial to enable the Search Optimization Service on the table.