Exactly what we've been doing for 2+ years at my job.
It's amazing.
Snowflake is amazing, but watch out for search optimization costs (it's great for append only), left joins taking FOREVER (avoid left joins as much as possible for large datasets).
It has to join each part of the previous join to the next join, and if you have a lot of joins this can get out of hand.
We have a lot of joins in our final fct orders from our intermediate table, and looks like this:
from foo
left join bar on bar.common_id = foo.common_id
left join baz on baz.common_id = foo.common_id
left join qux on qux.common_id = foo.common_id
left join waldo on waldo.common_id = foo.common_id
So waldo joins to qux, which joins to qux... I call it a "staircase join", as that's what it looks like in the SF profiler.
It's amazing.
Snowflake is amazing, but watch out for search optimization costs (it's great for append only), left joins taking FOREVER (avoid left joins as much as possible for large datasets).