Virtual Warehouses Scaling - Part I

1. Virtual Warehouses Scaling - Part I

When I was younger, my older brother Dan told me that writing code is the closest thing we’ve got to modern day magic – You type out these incantations, and run them, and voila, the world is different. This is definitely how I feel about scaling virtual warehouses. With a single line of code you can scale up a virtual warehouse. This means you’re temporarily laying claim to dramatically more compute resources from the Snowflake compute pool. Then with another line of code, you can scale back down, all within a single workflow. Being able to scale up and down like this is really useful, because this makes you much more efficient. You don’t need to use vast compute resources for small jobs just because at one point in your workflow you’ll require massive compute to, say, analyze a huge table. One concrete example – If we’re working with the Tasty Bytes menu table, which is only 100 rows, we don’t need a large machine. But then we might need to query the orders table, which has more than 600 million rows. Scaling up and scaling down saves the day so we’re not trying to run a huge job through a small machine, which would take a long time, or wastefully run a small job on a large machine. So here’s what you need to know about scaling warehouses: There are currently 10 standard warehouse sizes (x-small, small, medium, large, x-large, 2XL, 3XL, 4XL, 5XL, and 6XL). The amount of compute you’re accessing doubles each time you go up a size, as does the number of credits you use per hour. So you can keep an extra small running for an hour and only use 1 credit (2 to the zeroth power), but a 6XL would use 512 credits in an hour (2 to the ninth power). In the stuff I do, I basically never use a 6XL because that’s like harnessing an army of 512 machines to work for me for a time. We won’t discuss this idea more in this course, but there are times when scaling to a huge number of machines for a short time can actually save you credits relative to running a compute-intensive task on a smaller number of machines for a longer time. Or at the very least there are lots of moments when scaling up can save you time at equivalent cost. In any case, scaling up is a powerful tool. To do it through the UI, just go back to your list of warehouses under the admin tab, click on the three dots on the right hand side of the screen, select “edit,” and then select your desired size from the dropdown. But usually, you’re probably not going to want to do this through the UI if you’re trying to scale up and down within a given workflow, so instead you’ll want to go to your worksheet, and use an ALTER WAREHOUSE command, like this: You type “ALTER WAREHOUSE,” followed by the warehouse name, followed by “warehouse_size \=” and then the desired warehouse size. In our case, let’s scale warehouse_dash to medium, and run a query. ALTER WAREHOUSE warehouse_dash SET warehouse_size=MEDIUM; Now let’s sort the menu items by how much profit we get per item when we sell it. SELECT menu_item_name, (sale_price_usd - cost_of_goods_usd) AS profit_usd FROM tasty_bytes_sample_data.raw_pos.menu ORDER BY 2 DESC; Cool, selling a unit of tonkotsu ramen yields a profit of over $10! Now let’s scale the warehouse down again: ALTER WAREHOUSE warehouse_dash SET warehouse_size=XSMALL; Look at us! We scaled up, ran a query, and scaled down, all with a few lines of code. “Yer a wizard, Harry!” In this case, we’re dealing with a tiny dataset, so the scaling up was not particularly helpful, but in the next video we’ll deal with a sufficiently compute-heavy data ingestion process that it will really save us time to scale up. To recap, in this video, we learned about scaling vertically. In particular, we talked about the compute power & credit consumption of different warehouse sizes, and we learned how to resize a warehouse with the UI and the ALTER WAREHOUSE command with SET WAREHOUSE_SIZE. Coming up, we’ll pick up right where we left off and learn about scaling horizontally.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.