What are the pitfalls?
Pricing: If you are not careful, you could end up spending lot more money, it is like any cloud, people get sticker stock. I have heard that one of the data warehouse that went from terradata to snowflake we end up paying like 5x or 6x. - You have to optimize the crap out of warehouse and run stuff in batches. - Models have to be clean( snow schema ) not clutter - If you are doing streaming etc, you could end up with a lot more data so when you are comparing to on prem, please compare apples to apples. - scaling compute when you want is very good if you can otpimize it. - Consider snapshot backing it up to S3 as well so if you need to move off snowflake in future you can use it
Pitfalls: 1. Cost of compute and changing our thinking on compute i.e. in onprem etc we just think of running as many queries as we want or even something in RDS, snowflake compute could vary and could end up costing lot of money.
2. If amazon us east is down or even if you are testing for geo redundancy, they need to explicitly copy eveything from east to west and takes some time, I don't think you would lose things, you could have hours of outage instead of just working off another region.