Schema owner to be able to modify warehouse

[inkapbang]

Hello,

I am hoping you can guide me on how to solve this problem:

We have a procedure that'll dynamically scale warehouse size depending on the load. I defined the procedure and the task, and they're created and owned by the schema owner role.

However, it does not have permission to modify the warehouse as schema owner role, and I am looking for suggestions on how to resolve that. We do have a business role that has both schema owner role + technical role that has modify on the specific warehouse, but that additional technical role doesn't seem to be applicable due to the task/procedure being owned by the schema owner role rather than the business role.

I would love to hear from you on how to solve this issue. I was hoping I can specify owner_warehouse_modify or something like that in the schema role yaml definition, but that didn't work. It looks like owner_warehouse_usage is the only valid one to be used.

Thank you in advance.

[littleK0i]

Good question. I've considered allowing variable warehouse sizes, but ultimately decided against it.

Alternative approaches:

1. Try using DYNAMIC for warehouse size parameter. This feature was around for some time. Not sure if it works properly right now, but its main goal was to achieve the same result natively, without procedures.
2. Instead of altering warehouse sizes directly, put them into some external storage, e.g. like object in S3 bucket. Use SnowDDL programmatic config, connect to external storage, read desirable warehouse sizes and adjust WarehouseBlueprint objects. SnowDDL will manage warehouse sizes for you. This approach works for any kind of dynamic parameters and all object types.
3. If you want to save significant amount of credits, consider using realtime monitoring tool like SnowKill. Adjusting warehouse sizes is basically counting pennies compared to power of realtime monitoring, which detects and "kills" bad queries proactively.