Spotfire: Snowflake database error "Allow Writing In Temp Tables"

Problem:

When an on-demand data source is used, for example, Snowflake, it works during initial testing, but as soon as larger data sets are used, an error "Allow Writing In Temp Tables" occurs.

This problem can happen because the default on-demand query is limited to only a certain number of items (default 10,000) in its IN clause.  Once this limit is reached, it needs to pull its result set data into a temporary table and run a different type of query.

Solution:

The following options can be used to resolve this problem:

• Enable the data-source to allow creation of temporary tables.
• User a data-source that does allow creation of a temporary-tables.
• Select less data, so your on-demand filtering, does not excess the threshold (default 10,000).
• Adjust the threshold, if your data-source allows it.

As an example, when using Snowflake, one can run administration commands to allow the account that Spotfire uses to access it to create temp tables. Add the the following changes in the Data Source Connection initialization:

USE DATABASE (main database);
USE ROLE X (role with table create permissions, but without read permission to the database);
USE SECONDARY ROLE Y (role with no create grants but read on the main schema);
USE SCHEMA RESEARCH.TECH_TEMP_SPOTFIRE (schema where the temp tables role can create tables);

The values in brackets will need to be replaced with appropriate values.

This box will be hidden from the public.
Source Case 276553