topic Re: Best Data Model for moving from DW to Delta lake in Data Engineering

Best Data Model for moving from DW to Delta lake

StephanieAlba — Wed, 24 Nov 2021 20:26:25 GMT

I’m curious what Databricks recommends how we model the data. Do they recommend that the data be in 3rd normal form (3NF). Or should be it be dimensionally modeled (facts and dimensions)

Re: Best Data Model for moving from DW to Delta lake

Hubert-Dudek — Wed, 24 Nov 2021 22:06:17 GMT

I think that everyone can get what they like as all of them are fully supported (denormalized table or star/snowflake schema thanks to Spark SQL)

Re: Best Data Model for moving from DW to Delta lake

-werners- — Thu, 25 Nov 2021 08:13:53 GMT

It all depends on the use case.

3NF is ideal for transactional systems. So for a data warehouse/lakehouse that might not be ideal.

However there certainly are cases where it is interesting.

Star schema's are def still relevant, BUT with the processing power of spark you can decide to flatten it beforehand (so joining all dims with the fact and write it). Like that you have to do less joins at query runtime, which is often faster.

IMO the beauty of all this is the freedom you get. You are not locked into a certain model. Start with what you think is a good approach, and see where it gets you.

The good old Inmon/Kimball theory is still valid up to a certain point (fact tables, SCD etc) but a lot has also become obsolete.

In my lakehouse I have a mix of flattened star schema's, actual star schema's and plain tables (bronze/silver).