Warehouse Management

the Sundeck Native App’s Warehouse Scheduling feature automates modification of Snowflake virtual warehouse properties according to custom daily schedules. Separate schedules can be set for weekdays (Monday through Friday) and weekends (Saturday and Sunday) for each warehouse. A schedule is composed of one or more periods, defined with a start_at and finish_at time, set at quarter-hour resolution (:00, :15, :30, :45).

The periods configured for a schedule must cover all of the 24 hours in a day (midnight to midnight) with no gaps, meaning that the start_at time for a period must be the same value as the finish_at time for the preceding period for that schedule (or midnight, if the period is the first defined for the schedule).

Warehouse schedules will be applied according to the the Sundeck Native App-configured timezone (available via the Sundeck Native App “Settings” -> “Config”).

Any warehouse property can be modified by a Warehouse Schedule, including: warehouse size, number of minutes before auto-suspend, and auto-resume. For Snowflake Enterprise Edition and Business Critical Edition accounts, a Warehouse Schedule can also modify the auto-scaling policy for multi-cluster warehouses and the maximum number of autoscaled clusters.

After defining schedules for a warehouse, click the “Enable Scheduling” checkbox to activate the schedules. For each enabled schedule, the warehouse’s properties will be updated on the schedule boundaries. the Sundeck Native App does not continuously inspect each warehouse’s configuration to determine if it has deviated from the properties set by the schedule. This means that any manual changes to warehouse configuration will persist until the next defined schedule period, when they will be set to the values defined by the schedule.

Click the “Filter” dropdown to reveal a set of date filters (which apply to Activity Reporting), as well as a list of all warehouses in your Snowflake account. Alongside each warehouse, key properties will be shown:

• The current warehouse size (or “variable” if schedules are defined for that warehouse).
• If this is a multi-cluster warehouse (Snowflake Enterprise Edition and above, only).
• The amount of spend in dollars over the selected time range by queries executed on that warehouse.
• The percentage of time that queries were executing on the warehouse when it was running.
• If the schedules are currently enabled for this warehouse.

To the right of each warehouse is a View & Manage button; clicking this will allow setting a schedule for this warehouse, as well as viewing warehouse utilization statistics.

Warehouse schedules can be created, updated, or deleted using the the Sundeck Native App UI or by calling the Sundeck Native App stored procedures located in the the Sundeck Native App.ADMIN schema.

When creating a new schedule, the new schedule’s finish_at must match an existing schedule’s finish_at time. This can be modeled as splitting an existing schedule in half and defining the new configuration for the “latter” half. When deleting a schedule, the Sundeck Native App will fill any gaps to guarantee that the schedules remaining cover the 24hour period of a day.

After creating a schedule, be sure to enable it with the “Enable Schedule” checkbox.

A team of developers in the marketing department share a single Snowflake warehouse MARKETING_WH. They share one warehouse to simplify cost attribution by their team. However, when all the developers are using Snowflake, they have noticed that their queries are getting queued before running because their shared warehouse is too busy running queries. To reduce queueing, they have increased the size of the MARKETING_WH to be Large, but now find that their costs are going to exceed their monthly budget. They planned for the increase in cost during normal business hours (9am to 5pm), but they are seeing that queries from outside of normal business hours significantly contribute to their team’s costs when there is no queueing problem.

The team can create a warehouse schedule which increases the size of MARKETING_WH to Large from 9am to 5pm on weekdays, and decreases the size of MARKETING_WH to X-Small during midnight to 9am and 5pm to midnight on weekdays and for all hours (midnight to midnight) on weekends. This gives the team the performance they need during business hours, but reduces cost from queries that run outside business hours.

Consider a team who is responsible for producing a report and dashboard that summarizes the previous day’s sales data. This dashboard is built by bulk-loading a number of data sources from S3 into Snowflake, transforming the loaded data, and then joining it against a number of other data sources already in Snowflake. After their sales report is updated, other teams also have processing to perform before start of business the next day. To give adequate time for all processing, this team’s workload starts every day at 2am and needs to finish by 3am so other follow-on workloads can finish before 8am.

The team wants to increase the size of the warehouse to ensure this job finishes within 1 hour, but has struggled to maintain their budget after doing this. They have updated the job to increase the size of the warehouse before the job starts, but have had face challenges ensuring the warehouse is scaled back down after the job finishes, often relying on a team member to notice and manually resize the warehouse.

To solve this problem, the team can create a warehouse schedule which scales up the warehouse to ‘X-Large’ at 1:45am to ensure that the warehouse is ready for the job to start at 2am. Then, they can create a second schedules which resizes the warehouse down at 3 AM, long before any team member starts their work day, ensuring that no team member has to remember to check the warehouse size.