Data Denormalization | Dagster Glossary

Back to Glossary Index

Data Denormalization

Optimize data for faster read access by reducing the number of joins needed to retrieve related data.

Data denormalization definition:

Denormalization is the process of adding redundant data to a database to improve read performance. It involves breaking with normalization rules in order to reduce the number of joins needed to retrieve data. This can be important for improving query performance in data pipelines that have to handle large amounts of data.

Denormalization can be a useful technique for improving query performance in your data pipelines, but it should be used judiciously and with an understanding of the trade-offs involved. Denormalizing data can lead to increased data redundancy and the risk of data inconsistencies.

Data denormalizing in Python:

Please note that you need to have the necessary Python libraries installed in your Python environment to run the code examples below.

In Python, one way to denormalize data is to use the pandas.merge() function to combine data from multiple tables into a single DataFrame. For example, if you have a table of customers and a table of orders, you could denormalize the data by merging the two tables based on the customer ID column:

Given input files of customer.csv as:

customer_id,name,total_orders
1,Mary,42
2,Brian,28
3,Mercedes,7
4,Rose, 20

And an orders.csv file of:

customer_id,item,value
1,cat food,12
1,apples,6
1,books,24
2,bananas,12
2,candles,12
2,coffee,4
3,nails,7
4,paper,18
4,pencils,2

You can merge this data as follows:

import pandas as pd

# Load the customer and order tables into DataFrames
customers = pd.read_csv('customers.csv')
orders = pd.read_csv('orders.csv')

# Merge the two tables based on the customer ID column
merged_data = pd.merge(customers, orders, on='customer_id')

print(merged_data)

This would create a new DataFrame that includes all of the columns from both the customers and orders tables, with the data merged together based on the customer_id column and would look like this:

   customer_id      name  total_orders      item  value
0            1      Mary            42  cat food     12
1            1      Mary            42    apples      6
2            1      Mary            42     books     24
3            2     Brian            28   bananas     12
4            2     Brian            28   candles     12
5            2     Brian            28    coffee      4
6            3  Mercedes             7     nails      7
7            4      Rose            20     paper     18
8            4      Rose            20   pencils      2

Another way to denormalize data in Python is to use a document-oriented database like MongoDB, which allows you to store data in a denormalized format as documents. This can be useful for handling complex, nested data structures that don't fit well into traditional relational database tables. With MongoDB, you can store related data together in a single document, rather than splitting it up into multiple tables with foreign key relationships.

Suppose we have two collections in our MongoDB database: "users" and "orders". The "users" collection contains documents with information about each user, such as their name and email address. The "orders" collection contains documents with information about each order, including the user ID of the user who placed the order.

Here's how we can denormalize this data by embedding user information within each order document:

import pymongo

# Connect to MongoDB
client = pymongo.MongoClient("mongodb://localhost:27017/")
db = client["mydatabase"]

# Get collections
users_col = db["users"]
orders_col = db["orders"]

# Find all orders
orders = orders_col.find()

# Denormalize data
for order in orders:
    user = users_col.find_one({"_id": order["user_id"]})
    order["user_name"] = user["name"]
    order["user_email"] = user["email"]

    # Update order document with denormalized user data
    orders_col.update_one(
        {"_id": order["_id"]},
        {"$set": {
            "user_name": order["user_name"],
            "user_email": order["user_email"]
        }}
    )

In this example, we first connect to our MongoDB database and get references to the "users" and "orders" collections. We then find all orders in the orders collection and denormalize each order document by embedding the user's name and email within the order document. Finally, we update the orders collection with the denormalized data.

Denormalizing data in this way can be useful for optimizing read performance, as it reduces the need to perform multiple queries to retrieve related data. However, it can also increase write complexity and potentially lead to data inconsistency if the denormalized data is not kept up-to-date with changes to the original data. Therefore, it's important to carefully consider the trade-offs before denormalizing data.


Other data engineering terms related to
Data Transformation:
Dagster Glossary code icon

Align

Aligning data can mean one of three things: aligning datasets, meeting business rules, or arranging data elements in memory.
An image representing the data engineering concept of 'Align'
Dagster Glossary code icon

Clean or Cleanse

Remove invalid or inconsistent data values, such as empty fields or outliers.
An image representing the data engineering concept of 'Clean or Cleanse'
Dagster Glossary code icon

Cluster

Group data points based on similarities or patterns to facilitate analysis and modeling.
An image representing the data engineering concept of 'Cluster'
Dagster Glossary code icon

Curate

Select, organize, and annotate data to make it more useful for analysis and modeling.
An image representing the data engineering concept of 'Curate'
Dagster Glossary code icon

Denoise

Remove noise or artifacts from data to improve its accuracy and quality.
An image representing the data engineering concept of 'Denoise'
Dagster Glossary code icon

Derive

Extracting, transforming, and generating new data from existing datasets.
An image representing the data engineering concept of 'Derive'
Dagster Glossary code icon

Discretize

Transform continuous data into discrete categories or bins to simplify analysis.
An image representing the data engineering concept of 'Discretize'
Dagster Glossary code icon

ETL

Extract, transform, and load data between different systems.
An image representing the data engineering concept of 'ETL'
Dagster Glossary code icon

Encode

Convert categorical variables into numerical representations for ML algorithms.
An image representing the data engineering concept of 'Encode'
Dagster Glossary code icon

Filter

Extract a subset of data based on specific criteria or conditions.
An image representing the data engineering concept of 'Filter'
Dagster Glossary code icon

Fragment

Break data down into smaller chunks for storage and management purposes.
An image representing the data engineering concept of 'Fragment'
Dagster Glossary code icon

Homogenize

Make data uniform, consistent, and comparable.
An image representing the data engineering concept of 'Homogenize'
Dagster Glossary code icon

Impute

Fill in missing data values with estimated or imputed values to facilitate analysis.
An image representing the data engineering concept of 'Impute'
Dagster Glossary code icon

Linearize

Transforming the relationship between variables to make datasets approximately linear.
An image representing the data engineering concept of 'Linearize'

Munge

See 'wrangle'.
An image representing the data engineering concept of 'Munge'
Dagster Glossary code icon

Normalize

Standardize data values to facilitate comparison and analysis. Organize data into a consistent format.
Dagster Glossary code icon

Reduce

Convert a large set of data into a smaller, more manageable form without significant loss of information.
An image representing the data engineering concept of 'Reduce'
Dagster Glossary code icon

Reshape

Change the structure of data to better fit specific analysis or modeling requirements.
An image representing the data engineering concept of 'Reshape'
Dagster Glossary code icon

Serialize

Convert data into a linear format for efficient storage and processing.
An image representing the data engineering concept of 'Serialize'
Dagster Glossary code icon

Shred

Break down large datasets into smaller, more manageable pieces for easier processing and analysis.
Dagster Glossary code icon

Skew

An imbalance in the distribution or representation of data.
Dagster Glossary code icon

Split

Divide a dataset into training, validation, and testing sets for machine learning model training.
Dagster Glossary code icon

Standardize

Transform data to a common unit or format to facilitate comparison and analysis.
Dagster Glossary code icon

Tokenize

Convert data into tokens or smaller units to simplify analysis or processing.
An image representing the data engineering concept of 'Tokenize'

Transform

Convert data from one format or structure to another.
Dagster Glossary code icon

Wrangle

Convert unstructured data into a structured format.
An image representing the data engineering concept of 'Wrangle'