1
0
During the last post we discussed about three types of parameters that can be set for your account. However, In this final post we will be talking about Snowflake Parameters Insight in details.
1 : DATA_RETENTION_TIME_IN_DAYS: Can be set for Account » Database » Schema » Table.
It defines Number of days for which Snowflake retains historical data for performing Time Travel actions. A value of 0 effectively disables Time Travel .
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Firstly, Create the Schema:
create or replace schema Employees_Tables;
show schemas
—Retention period would be 1 day by default:
Secondly, Alter the DATA_RETENTION parameter for schema.
ALTER SCHEMA DEMO_DB.Employees_Tables set data_retention_time_in_days=90;
Create the Permanent table:
create or replace table employees(employee_id number,salary number,manager_id number)
show tables
–Retention period would be 90 days as it inherit from the Schema property:
Finally, create the Temporary table:
Create Temp table:
create or replace temporary table employees_temp(employee_id number,salary number, manager_id number);
However, I would see issue here as temp table are related to specific session only, so retention should be zero day. Hence query into INFORMATION_SCHEMA.TABLES, I get the correct information.
SELECT * FROM DEMO_DB.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘EMPLOYEES_TABLES’;
2: MAX_DATA_EXTENSION_TIME_IN_DAYS:
MAX_DATA_EXTENSION_TIME_IN_DAYS:
Type : Can be set for Account » Database » Schema » Table
By default, if the DATA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed. Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account.
Therefore, MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic extension period to control storage costs for data retention or for compliance reasons.
3. ABORT_DETACHED_QUERY: Can be set for Account » User » Session
Specifies the action that Snowflake performs for in-progress queries if connectivity is loss due to abrupt termination of a session (e.g. network outage, browser termination, service interruption).
Values
TRUE: In-progress queries are aborted 5 minutes after connectivity is lost.
FALSE: In-progress queries are completed.
Testing:
- Firstly, Set ABORT_DETACHED_QUERY to TRUE.
- Secondly, Run a query which last more than 5 minutes in worksheet;
- Disconnect the internet connectivity.
- Login Browser Google Chrome again
- Finally, Query was kill automatically
ABORT_DETACHED_QUERY works as expected.
4. STATEMENT_TIMEOUT_IN_SECONDS: Let say you have submitted a long running query in Snowflake. how long the query can run till snowflake automatically cancels the query?
The default value is 172800 seconds (48 hours), Snowflake keeps query running for next 48 hours until we explicitly kill the query.
STATEMENT_TIMEOUT_IN_SECONDS : set for Account » User » Session; can also be applicable for individual warehouses. IT determines
- A warehouse has a timeout of 1000 seconds.
- The timeout for the session is 500 seconds.
The session timeout takes precedence (i.e. any statement submit in the session is cancel after running for longer than 500 seconds).
For rest of the parameters, please click here.
