71–80 of 100 posts
I thought this was a pretty standard, table stakes move in the analytics world.
if it makes it easier for the data warehouse team to build a kimball model and then put views on top of it to deliver the flat tables, or it works better to build a kimball model because you're going to set powerbi on top of it, fine.
for most data analysts, data scientists, etc. stopping at a dimensional model just leaves them needing to put the pieces together themselves.
It sounds like the author is calling a kind of materialized/persisted view "denormalized tables". The actual DB tables stay untouched and fully normalized. It sure makes sense to love them, views are great. I don't know why they need a new name. > Transformation tools such as dbt (Data Build Tool) have revolutionized the management and maintenance of denormalized tables. With dbt, we can establish clear relationships…
> It sure makes sense to love them, views are great. I don't know why they need a new name. in very many cases, old things get new names so that more people can share in the claim that they invented what has been in fact merely re-invented
*rediscovered
Data denormalization makes sense if the data is written once and never updated - like with a data warehouse / analytics. If you need to update the data then denormalization can turn into a big source of trouble. For example you end up with many copies of the same stuff, and you must make an extra effort to update all the duplicates upon update. Or you end up with multiple entries, where the validity of an entry is de…
Earlier quoted context omitted.
> It sure makes sense to love them, views are great. I don't know why they need a new name. in very many cases, old things get new names so that more people can share in the claim that they invented what has been in fact merely re-invented
merely re-invented *rediscovered
Many things are re-invented/rediscovered from first principles. In maths, some people prefer to think maths are created, and some prefer to think in terms of discovering/uncovering sth that was already there.
It sounds like the author is calling a kind of materialized/persisted view "denormalized tables". The actual DB tables stay untouched and fully normalized. It sure makes sense to love them, views are great. I don't know why they need a new name. > Transformation tools such as dbt (Data Build Tool) have revolutionized the management and maintenance of denormalized tables. With dbt, we can establish clear relationships…
There was me assuming it was about normal form, you know, what everyone else means when they talk about 'normalising' in a database context... Which even more confusingly almost applies but the opposite way around: > create denormalized analytics datasets on top of them A common pattern is to have source data 'warehoused' or whatever you want to call it, and then build your, er, normalised schema on top of that.
Earlier quoted context omitted.
There was me assuming it was about normal form, you know, what everyone else means when they talk about 'normalising' in a database context... Which even more confusingly almost applies but the opposite way around: > create denormalized analytics datasets on top of them A common pattern is to have source data 'warehoused' or whatever you want to call it, and then build your, er, normalised schema on top of that.
One of main reason to normalize data is aim to eliminate duplication, and find "the source data" by properly designing the relationships of tables.
Data denormalization makes sense if the data is written once and never updated - like with a data warehouse / analytics. If you need to update the data then denormalization can turn into a big source of trouble. For example you end up with many copies of the same stuff, and you must make an extra effort to update all the duplicates upon update. Or you end up with multiple entries, where the validity of an entry is de…
I'd argue that most of the time it is better to write normalized data and use some form of permanently existing database views (rather than actual table) to read from pseudo-denormalized data. That way you combine the best of both worlds.
Earlier quoted context omitted.
> if the cost exceeds the value, then don't do it. That isn't complicated. Could you try to elaborate on what you mean by "cost" and "value"? The complicated part is not the part you said, but specifying those two very abstract terms.
In the case of this analyst, say every couple of days he is writing SELECT * FROM events JOIN projects. Everyone is asking him for information about events and projects. He's going to save time and make less mistakes if he uses DBT and has a denormalised tables that merges events and projects. However you can think of to define costs and benefits, it'll turn out to be a good choice. As he discovered in the article. G…
E.g.
``` CREATE VIEW vw_events_and_projects AS SELECT * FROM events JOIN projects ```
Then
``` SELECT * FROM vw_events_and_projects ```
Edit:
And if you need OLAP, replicate the normalized table to a database that handles analytics workflows better (e.g. ClickHouse).
Then you get the normalized forms for your OLTP workflows (your "bread and butter"); and you also get the efficiency and ergonomics of real-deal OLAP.
Of course, your biggest issue is going to be keeping the two in-sync. Obvious case is to have your OLTP database stream synchronization data to the replica whenever data is modified.