What is dbt ? How's it related to denormalized tables ? The article is a bit confusing to me. What're alternatives ?
I stopped worrying and learned to love denormalized tables
41–50 of 100 posts
Re: I stopped worrying and learned to love denormalized tables
#42You have to denormalize in very common cases even for mostly OLTP workloads in order to get sortable data into an index. Consider a cloud storage product with folders connecting with a many to one to documents. The product wants to display the most recently used folders ordered by their inner document modification date. Because composite indexes commonly can't span tables, you have to push the last modification date…
Couldn't you also use a materialised view here? Or, alternatively, what about an index on the documents table alone? It might depend on whether your db has a flexible enough indexing system to do what you want, but I don't see why the index would have to span tables since it only needs to depend on the folder id and the document modification time
Re: I stopped worrying and learned to love denormalized tables
#43While this talks mostly about data warehousing, oftentimes denormalization is useful for everyday web app data storage. If your web app (usually on Postgres) is mostly frequent reads and rare writes (most web apps are) — there's no excuse for your pages to load slower than a static site. Store your data as normalized as you want, add a denormalized materialized view, update it on writes, render pages based on the vie…
95% of the apps don't need a database. Just store data in files. one layer less to worry about.
Re: I stopped worrying and learned to love denormalized tables
#44Earlier 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…
What about using logs as the source of truth? It's a pattern I've seen multiple times.
Re: I stopped worrying and learned to love denormalized tables
#45Earlier quoted context omitted.
I kinda observe this from a different point of view: what are the use cases needed for the data and what are the queries that could satisfy those? The dynamodb book was enlightening from this point of view even for designing sql databases where we normalize data as much as possible no matter what.
Hi there, when you say the dynamodb book, do you mean https://www.dynamodbbook.com/ ?
Yes, I mean that one!
Re: I stopped worrying and learned to love denormalized tables
#46Well... 1) Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it. That isn't complicated. Denormalised data sometimes points at design flaws, but even then all systems have design flaws and they don't automatically need to be fixed. Quality is expensive, like every other property (even doing things the cheap way is expensive, ironically - software is all about managing costs). 2) For…
I kinda observe this from a different point of view: what are the use cases needed for the data and what are the queries that could satisfy those? The dynamodb book was enlightening from this point of view even for designing sql databases where we normalize data as much as possible no matter what.
The book is this one: https://www.dynamodbbook.com/
Re: I stopped worrying and learned to love denormalized tables
#47Earlier quoted context omitted.
I kinda observe this from a different point of view: what are the use cases needed for the data and what are the queries that could satisfy those? The dynamodb book was enlightening from this point of view even for designing sql databases where we normalize data as much as possible no matter what.
Which DynamoDB book? It's very relevant to my interests.
Re: I stopped worrying and learned to love denormalized tables
#48Denormalized table is just an hard to mantain (materialized)view over normalized tables.
Re: I stopped worrying and learned to love denormalized tables
#49Earlier 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…
Plus there are many cases where you want to see the data as of a given time, for instance all the positions of a fund at a given time. Then it makes sense to denormalise because this data should never be updated in the future.
Re: I stopped worrying and learned to love denormalized tables
#50Earlier quoted context omitted.
i think i heard once that there are only a few problems in computer science, including off-by-one errors, and concurrent update handling oh yeah and overengineering, probably
Concurrency.""There are three hard things in computer science: cache invalidation, naming things, off-by-one errors, and Original quote: There are only two hard things in computer science: cache invalidation and naming things. — Phil Karlton The one that most people know: There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors. - Jeff Atwood
- Knock knock
- Race condition
- Who's there?