previous article
Virtual warehouses are the main tool in Snowflake for the flexible scaling of workloads. Due to its platform independence, however, WhereScape is not optimized for virtual warehouse usage. Here, we'll show you what you need to do to add this functionality to WhereScape.
The importance of virtual warehouses
Virtual warehouses are the main tool in Snowflake for the flexible scaling and parallelization of workloads. The number of virtual warehouses and how they are used greatly depends on the particular use case. A distinction is often made between
- virtual warehouses for development, test and production environments
- virtual warehouses for the type of workloads, including read and write access
- virtual warehouses for large and small workloads
An essential requirement of any Snowflake data warehouse automation solution is often the ability to individually configure the number of database objects used by the virtual warehouse. The finest (although not necessarily the most useful) granularity can be achieved by specifying that each table should be loaded into a particular warehouse.
Unfortunately, WhereScape RED's platform independence means that there is just one default option for doing this: the virtual warehouse is specified in the ODBC connection that will later be used to create the connection to the data warehouse. All the tables in your data warehouse are subsequently loaded with the aid of a single virtual warehouse, and workloads – such as loading hubs, links and satellites in data vault models – cannot be separated.
A major advantage of WhereScape RED is that it can be adapted to suit individual requirements and use cases by means of templates, extended properties and parameters, which you can use to bypass the single virtual warehouse limitation.
Extending the functionality of WhereScape
In order to integrate virtual warehouse usage into WhereScape RED, we need to enhance the tool using templates and extended properties. The aim of the following instructions is to allow an extended property to create a virtual warehouse for each table object. This is then retrieved from the table objects' loading templates and a USE-WAREHOUSE command sent to Snowflake at the beginning of each procedure.
1. Adding an extended property
First, you need to create a new extended property via Tools > Extended Properties > Maintain Extended Properties > New. Be sure to select "Other" under databases. Under Object Types, select the types of object you wish to enable for subsequent customized warehouse usage.
2. Review and maintain the extended property
The second step is to manually assign a value (i.e. a virtual warehouse name) to the extended property. To do this, navigate from an object via Properties > Extended Properties to the corresponding overview page and then enter a value in the extended property you created earlier. In our example, a virtual warehouse with the name WH_HUBS is assigned to the hub h_categories.
Tip: For additional automation, you can generate and automatically enter the virtual warehouse names in 3D via Model Conversion Rules, which eliminates the need for manual maintenance.
3. Modify the templates responsible for loading
The third step involves modifying the templates responsible for loading the object types you selected in step 1. The following code retrieves the virtual warehouse name for an object and sends a USE-WAREHOUSE command to Snowflake. This should, of course, be inserted at the beginning of the relevant template to ensure that all subsequent commands are executed by the appropriate virtual warehouse.
If no virtual warehouse name is selected, the warehouse stored in the ODBC connection is used by default for all commands contained in the procedure.
4. Regenerating the procedures
In the final step, the procedures of an object are regenerated via Properties > Update Procedure > Regenerate to allow the code added to the template to be used.
Note: You can check whether the procedure has been successful by inspecting the history tab in Snowflake. If you have initiated a procedure in WhereScape, you should find the USE-WAREHOUSE command there. Also, any subsequent commands issued by the procedure should be executed by the virtual warehouse defined in the extended property rather than by the virtual warehouse stored in the ODBC connection.
Templates, extended properties and parameters are powerful tools that you can use to customize WhereScape RED to suit your needs. In this article, we have shown how templates and extended properties can be used in combination to assign a virtual warehouse to tables, or their loading procedures, within WhereScape.
If you'd like further information about this topic or need support in using WhereScape, please don't hesitate to contact us!
