Modify Snowflake warehouse
You can use the Modify Snowflake warehouse job entry to edit warehouse settings. For more information about working with Snowflake in PDI, see PDI and Snowflake
Modifying a warehouse is useful if your data and usage needs change through the day, week, month, or year. For example, your users might only perform simple queries which require a small warehouse. However, to meet your ETL service-level agreements (SLA), you may need a larger warehouse during the ETL process. Using this job entry, you can modify the warehouse at the beginning of the ETL process to scale it up, and then modify it to scale it back down when the ETL process is complete.
For more information about Snowflake warehouse settings, see the Snowflake documentation.
General
The following field is general to this job entry:
- Entry Name: Specify the unique name of the Modify Snowflake warehouse entry on the canvas. You can customize the name or leave it as the default.
Options
The Modify Snowflake warehouse entry includes several options for your virtual warehouse and warehouse attributes. Each option is described below.
Database connection and warehouse
In this section, specify the Snowflake database connection and identify the virtual warehouse you want to modify.
| Option | Description |
| Database connection | Select an existing Snowflake database from the list. If you do not have an existing connection, click New. If you need to modify an existing connection, click Edit. See Define Data Connections for instructions. Note: If timeout errors occur, see Snowflake timeout errors to troubleshoot. |
| Use default warehouse | Select this option to use the Snowflake default warehouse that is identified and included in the Database connection. |
| Use an existing warehouse | Click this option to enter the name of an existing virtual
warehouse from a list. Select this option to use a virtual warehouse other than the Snowflake default warehouse identified in the Database connection. |
Warehouse settings
You can use these options to resize a warehouse at any time, even while the warehouse is running. These options are useful if your data operations require varying levels of computing resources.
The Auto suspend (in seconds) option verifies that when the warehouse is inactive, it stops consuming credits. The Auto resume warehouse option verifies that the warehouse starts up again as soon as it is needed.
This section also includes resource monitor and scaling policy controls that can be modified from within a PDI job. For more information about virtual warehouse sizes, scaling policies, and resource monitor permissions, see the Snowflake documentation.
| Option | Description |
| Warehouse size | Select a size for this virtual warehouse:
CautionThe size
determines the number of servers in each cluster in the warehouse and the number
of credits consumed while the warehouse is running. |
| Scaling policy | Select the policy for automatically starting and shutting down
clusters in a multi-cluster warehouse running in Auto-scale mode. The following policies are available:
|
| Auto suspend (in seconds) | Specify the time, in seconds, for this virtual warehouse to
auto-suspend when it becomes inactive. When a warehouse is suspended, it does not
accrue any credit usage. The default value is 600 seconds (10 minutes). You must specify a number of 60 or greater because the minimum amount of time a warehouse can run before it suspends is 60 seconds (1 minute). CautionIf you leave this
option blank, the warehouse will never suspend. Leave this option blank only if
your query workloads require a continually running virtual
warehouse. |
| Resource monitor | Select the name of an existing resource monitor to assign to this
existing virtual warehouse. The default value is blank. No resource monitor will be assigned to this warehouse. NoteA resource
monitor is a permissions-based control to govern the monthly credits
used by the warehouse and all other warehouses to which the monitor is
assigned. |
| Auto resume warehouse | Specify whether to automatically resume a warehouse when a SQL
statement, such as QUERY, is submitted.
|
Cluster settings
The maximum and minimum cluster settings work together. Depending on your processing needs, you can use the cluster settings to run a multi-cluster warehouse in either Maximized mode or Auto-scale mode.
- If you set the minimum and maximum cluster count with the same value, then the warehouse runs in Maximized mode.
- If you set the minimum cluster count less than the maximum cluster count, then the warehouse runs in Auto-scale mode.
See the Snowflake documentation for more details.
| Option | Description |
| Minimum cluster count | If you are creating a multi-cluster warehouse, select the minimum
number of server clusters for this virtual warehouse. Valid values are 1 to 10. The default value is 1. This value must be equal to or less than the Maximum cluster count. |
| Maximum cluster count | Select the maximum number of server clusters for this virtual
warehouse. Valid values in the drop-down list are 1 to 10. The default value is
1. NoteHigher values require the Snowflake
Enterprise Edition. |
Activity settings
This option controls what happens if the PDI job requests a Snowflake warehouse that does not exist.
| Option | Description |
| Fail if warehouse doesn't exist | Specify how the PDI job should behave if the requested warehouse does not exist.
|