DBT is really nice, but not a game changer since it's basically a SQL generator and only as good as the underlying SQL engine of your data warehouse.
Before I got into it I was sceptical because I was thinking It’s just SQL and SQL is pretty easy and straightforward. It breaks bad habits in DW development.
I’m not sure I still get why but it has been one of the few things Ive seen in a long time in this area that has been a big jump in capabilities. There is so much more confidence on my team. We are moving much faster. We can just get on with delivering without having to undo and think of everything is precious or dangerous to change.
It took some hands on work and getting over the initial mind bend to get there but I wouldn’t go back to what we had. I would likely only make my next move if they had a similar setup in place or were open to change.
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).
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.
Also, dbt makes it easier to persist tables instead of views, which has a massive performance improvement.
https://medium.com/@jthandy/how-compatible-are-redshift-and-...