Erik on Software

Community Comment: Part 26 Discussion with purported training firm CEONormalized versus denormalized dataPerformance is important considerationMore factors involved than just record count

The comments I provided in reaction to a community discussion thread:
https://www.linkedin.com/feed/update/urn:li:activity:6988859629183123456?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A6988859629183123456%2C6989175385397608448%29

Founder & CEO of "Big Data" Training Firm:

Normalization vs Denormalization

Normalization is a process of dividing the data into multiple smaller tables with an intent to reduce data redundancy & inconsistency.

However, Denormalization is totally opposite of above idea. 
Denormalization is the technique of combining the data into a big single table. This definitely leads to redundancy in the data.

Note: Redundancy causes inconsistency -
Consider that same data is repeated 2 times and when updating you update at one place and forget to do at second place. This leads to inconsistent state.

When retrieving data from Normalized tables we need to read many tables and perform join which is a costly operation.
However, when reading the data from denormalized tables it's quite fast as no joins are required.

When to use Normalized tables vs Denormalized ones?
when we talk about OLTP systems (Online Transaction Processing) where we deal with lot of insert, delete and updates then you should go for Normalized tables.

However, when you talk about OLAP (Online Analytical Processing) systems where you need to analyse historical data then Denormalized tables are best fit. Since you wont be doing updates on data here, even though after having redundancy we wont end up in inconsistent state.

Let's take a simple Example - when you make purchase on amazon then it requires a OLTP system (a rdbms kind of database). Here the tables should be normalized.

when amazon is doing data analysis of historical data, they will create denormalized tables just to make sure analysis is faster and costly joins can be avoided.

I hope you liked the explanation, feel free to add more in comments!

What more topics you want me to cover?

PS ~ I teach big data and my students are leading big data teams in top companies. DM to know more!

#dataanalysis #dataengineering #dbms #normalization

What does this say to you?

Gfesser: One overarching theme is missing here: performance. And performance for analytical operations typically needs to be handled differently than performance for transactional operations. Why is this? Because when analytics is performed, scanning many records is often needed to calculate aggregates etc, unlike targeted queries which are looking for specific records. From my experience, it's still relatively common to hear development teams in the workplace talk about the number of records involved when it comes to performance. But there are many other additional factors. How many fields does each record contain? What queries will be run against these records? What are the data types of the field values being queried? What are the cardinalities of the field values being queried? and What database product type is being used to store this data, or conversely, What query engine / compute layer is being used, if storage is kept separate in cases of data lakes etc? All of these are important considerations.

Media Query Source: Part 43 CNBC (US business news television channel)Benefits of using self-service analytics toolsTimely, custom exploratory analyses enabledAppropriate data governance needed

The responses I provided to a media outlet on March 3, 2023:

Media: I'm working on an article about the benefits of using self-service analytics tools, and looking to include comments from an IT or data analytics executive. Only looking for executives whose organizations are using these tools.

Gfesser: When used and governed by capable hands, self-service analytics tools can provide speed of delivery for custom analyses that otherwise wouldn't be available to users. Such custom analyses are typically exploratory in nature, because the very nature of self-service implies human users. Exploring data essentially means getting familiar with data values and data relationships, and such exploration can eventually lead to discoveries of ongoing value to an organization, at which time the one-off nature of self-service analytics can be productized via automation. 

Self-service analytics tools can provide many benefits, as long as these are used and governed by capable hands. In other words, users of these tools need to understand how to use these tools as well as understand the data processed by these tools, and the data made available through these tools needs to be reliable. If any of these aspects are missing, the benefits of readily available data can actually turn into liabilities because the convenience provided to users can lead to faulty output. Think of it this way: what good does it do an organization if data is readily available to users, but either the data or the analyses performed on this data is problematic?

---

See all of my responses to media queries here.