What is the TTL for a Result set when Asynchronous Query is executed in Snowflake?

1. How and where does snowflake store the result set, is there any config available for this at the account/session level?

All row-producing queries have their results persisted in the cloud host's object storage (S3, Azure Blob, GCP Storage). This is in an area separate from your object storage for table micro-partitions and files in internal stages. The only setting to configure is whether or not the same queries can potentially use the result set cache. alter session set use_cached_result = {true | false}. Note that setting used_cached_result to false still allows you to retrieve the result using select * from table(result_scan('<query_id>'))

One note about running queries like this, partition pruning is not available when applying a where clause to the result set cache. If you intend to filter the results, do it before creating the original result set to ensure effective partition pruning.

2. What is the TTL for the Query Result of the Async Operation ? and is there a way to delete the query after reading the ResultSet.

Robert's answer covered that comprehensively.

3. Is there a way we can directly upload the ResultSet from Snowflake for an Async Query Execution to S3?

You can't upload the result set, but if the goal is to write the results to your own area of S3 instead of the result set cache, you can do that. Whether or not it's worth the complexity is up to you.

What you can do is take your select and use it to run a COPY INTO statement. The COPY INTO option is described in the documentation. Use an external stage to have access directly using standard methods to access S3.

When the query completes, the results will go to the specified location in one or more files depending on the size of the result set. You can select the file type to write. As of October 2021, the options are CSV (delimited with a specified format), Parquet, and JSON.

Here's a simple example:

copy into @my_external_stage from (select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION");
select $1, $2, $3, $4 from @my_external_stage;

https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html

You'll need to copy the files into different locations across runs or use different file names because file name collisions will cause an error.

4. Is there a way we can figure out what is the approx time that will take a Query to run, This will help us identify to run the queries Synchronously or Asynchronously ?

The web ui shows information of scan progress, so it's available to the REST API. I have never seen that exposed to a driver. In any case, it is displaying scan times, which do not always line up well with overall query times.

Final note: If you're using JDBC there is a method to get the query ID without having to run another select statement to get the last query ID. This is also available in the Snowflake JavaScript stored procedure API, called getQueryId() on the Statement object.

I believe you are talking about the Query Result Cache. When a query is executed, the result is persisted (i.e. cached) for a period of time at the virtual warehouse level. At the end of the time period, the result is purged from the system.

Each time the persisted result for a query is reused, Snowflake resets the 24-hour retention period for the result, up to a maximum of 31 days from the date and time that the query was first executed. After 31 days, the result is purged and the next time the query is submitted, a new result is generated and persisted.

You cannot directly control the virtual warehouse cache. You can clear the cache by suspending the warehouse:

ALTER WAREHOUSE <wh_name> SUSPEND;

You can also direct Snowflake to bypass the cache alltogether:

ALTER SESSION SET use_cached_result = FALSE;

You can access the result cache through a query using the RESULT_SCAN table function:

SELECT * FROM TABLE(RESULT_SCAN(last_query_id())) 

To obtain the query ID you can use QUERY_HISTORY , QUERY_HISTORY_BY_* table functions or the LAST_QUERY_ID function. You could use this to unload the results into S3.

The only way I know to estimate the run time is to check run times on smaller samples of the data, and extrapolate (there may be a better/easier way)