Modify Snowflake warehouse

Last updated
Save as PDF

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: X-Small (default) Small Medium Large X-Large 2X-Large 3X-Large 4X-Large 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: Standard Economy
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. If this check box is clear (default), the warehouse will only resume when using the Start Warehouse job entry. If this check box is selected, the warehouse will resume when a new 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. Leave this check box selected (default) if you want the PDI job to fail. Clear this check box if you want the PDI job to continue when the requested warehouse does not exist. The PDI job moves to the next entry in the job.