I thought of doing something similar for our data warehouse with AWS Fargate and Postgres but the cold starts and limited disk space required too much engineering on top to make it work.
Moving to Snowflake comes at the cost of losing so many Posgtres features in exchange for speed. Things like foreign keys, constraints, extensions etc which requires so much engineering to replace in Snowflake. I would be happy to pay 25x the price for a 10x speed increase for a specific query.