cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Best Data Model for moving from DW to Delta lake

StephanieAlba
Valued Contributor III

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)

1 ACCEPTED SOLUTION

Accepted Solutions

-werners-
Esteemed Contributor III

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).

View solution in original post

2 REPLIES 2

Hubert-Dudek
Esteemed Contributor III

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)

-werners-
Esteemed Contributor III

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).

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group