Does Snowflake Warehouse needs to be modeled? Is the Dimension modeling still relevant in Snowflake?

My organization is migrating from a traditional warehouse and migrating(building from scratch) to Snowflake. Does Snoflake still needs to be modeled based on the dimensional modeling?. is there any data modeling tool for snowflake that maintains versioning of tables?

1. With the advance of MPP and columnar storage is the traditional warehouse concept of having star schema’s still valid?.

2. Storage cost’s being cheap and joins between tables costing more do we need to have dimension tables?

3. How do we manage SCD2 tables in the new architecture?

4. Are the views on the staging tables going to replace the actual star schema tables?

dammalapatisree

posted on 15 Jan 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

1. Design for Simplicity

The Complex task in the world is to come up with a simple solution

With Columnar storage, Having giant flat table simplifies everything (ETL, Support, Reporting). If possible, always go for fewer, fatter tables, They have significant ROI compared to highly normalized model. Since the data warehouse is columnar, its going to scan only the columns that you select, and you will end up paying only for those columns that you access.

Star schema will continue to exist, But with the new advancements in the cloud, the dimension tables are going to get fat, and the Fact tables will get leaner

2. Of course, we do need Dimension tables

Dimension tables are separate entities. We will continue to have dimension tables in the cloud, However they can be fat and broad, covering 360 degrees. One reason i say this is, there is no way in the world we can combine Employee Dimension table and Product Dimension table. Its simply not possible. We do need to have separate dimension tables for doing any meaningful analysis

3. SCD will happen the same way as it is today in traditional data warehousing

I dont believe we are at a point where we can make radical changes to the way we do change data capture

Simplicity trumps everything! Use good judgement and make it easy on you and ultimately on the end users. Group similar attributes into giant fat dimension tables and have summarized metrics in few fact tables which will be lean.

With the advancements in cloud, Now is the best time to give some thought to the end user experience and delight them with simple, elegant solution