@Hrishi1did you consider setting a default SCRATCH_LIMIT at the resource pool level so that queries will fail if they spill too much data? I know a lot of cluster admins do things like that to prevent runaway queries, and also so that users will come to them if they're trying to run big queries instead of them having to contact users.
I understand that it's not exactly what you're looking for but I've seen people have success with it.
Thank you for your help in this thread.
Yes. For now, I have set the scratch limit for the specific resource pool. I set it to zero to prevent disk-to-spill and created a trigger to test whether it is working or not [IF (SELECT queries_spilled_memory_rate WHERE serviceName=$SERVICENAME AND max(queries_spilled_memory_rate) > 1) DO health:concerning]. Ideally, the trigger should not fire since the scratch limit is set to zero.
For your advice.