-1

The company where I am working at is initiating a redesign process of our entire DWH. The idea is to migrate to a cloud solution and to use the data lake concept. From the lake, data is supposed to be delivered to a 3NF DWH and finally, to the end users via marts.

The purpose of these marts would be diverse. From reporting to accounting and training ML models.

I have the opportunity to take a central role in designing those marts, but I have never done that before.

Do you have any tips on where to start from? A book, an article?

I hope that this is the right place to ask such a question and thanks for your contributions.

givnv
  • 3
  • 2

1 Answers1

0

This question will probably be closed as opinion-based (I'd vote too broad), but as someone who's worked in this space most of my career, I can provide my perspective as this:

  1. Everyone says their DWH is 3NF, but if you're using Kimball or using words like "star-schema" it is most certainly not 3NF. Good warehouse should look like production should be, if production wasn't spread across a number of systems throughout the years. Legible ER diagram, good keys, composite keys, narrow tables, reference tables.
  2. If you have a good DWH, there's no need for data marts - you can meet most needs with views. Materialized views for high-level aggregation.
  3. If there isn't a good DWH (odds are pretty slim if it's already existing), chances are your data marts are going to be a lot of massaging the existing DWH data to fit customer needs, or just bypassing the DWH altogether and going directly to the source data. But if it's under your control, the mart should have everything your DWH doesn't but should - so legible ER diagrams, good keys, composite keys, narrow tables, reference tables. Views for consumption. Materialized views for high-level aggregation.
  4. Always normalize to at least 3NF. For some types of very wide/sparse data 6NF (vertical partitioning) may be a good option for a lot of ad hoc queries.
  5. Clustered indexes, clustered tables, index-organized tables are your friend.

Are their good books or articles? None that I can find. Everything I've learned has been going back to the well, trial and error, or a few really good SO answers to point me in the right direction.

These links may provide some guidance/perspective:

Bill Inmon: A Tale of Two Architectures

SO: Should OLAP Databases Be Denormalized For Read Performance

dba.SE: Integrity constraints in a relational database should we overlook them (adding since lack of FK/unique constraints in DWH/data marts is a constant headache for me)

  • Thanks a lot. And yes, you are right. Current DWH is in 3NF only partially. That is why the company is looking for a replacement solution. Moreover, the current model is out-scaled by the business. I don't think that we can satisfy the user with views (your point 2) due to performance concerns. There are just quite a lot of users with very diverse set of requirements. – givnv Feb 12 '21 at 04:27