In my previous case study, I built an ETL pipeline using CSV files as the data source. Since CSV is a structured format and the dataset was relatively clean, the transformation stage was more predictable, allowing me to focus on understanding the overall ETL workflow rather than exploring each stage in great technical depth.
For this new case study, I wanted to explore a different type of challenge by building an ELT pipeline using semi-structured data. At the same time, I was exploring relational database design and normalization principles, and I realized that they would naturally fit into the transformation phase.
This article presents the development of an ELT pipeline that combines these objectives: ingesting nested JSON data from a REST API using a Python application, loading the raw data into a PostgreSQL database, and incrementally transforming it into a normalized relational data model through SQL-based transformations.
Methods
System architecture
Figure 1 illustrates the high-level architecture of the ELT pipeline implemented in this case study.
Figure 1. High-level architecture of the data ELT pipeline.
REST API Endpoint
The dataset was obtained from the public DummyJSON REST API, which is released under the MIT license. The API exposes multiple resources; however, but this exercise focuses on the /products endpoint.
To perform the extraction and loading stages of the pipeline, I developed a Python application within a virtual environment (venv). Following the Separation of Concerns (SoC) principle, I implemented each stage in a separate module to improve modularity.
Extraction Stage
First, I installed the requests library to enable the application to send HTTP requests to the DummyJSON REST API and retrieve JSON data from the /products endpoint.
The response was parsed into a Python dictionary, from which the products array was extracted. This allowed the application to iterate over each product individually before the loading stage.
Loading Stage
Using psycopg3, I established a connection between the application and the PostgreSQL database product_catalog.
Each product record was loaded as a raw JSONB document into the raw_products_data table, preserving the original structure before transformation. Figure 2 presents the data model of raw_products_data.
Figure 2. Data model of the raw_products_data table used to store raw product records as JSONB documents.
Figure 3 demonstrates that raw_products_data was successfully populated.
Figure 3. Raw product records stored as JSONB documents in the raw_products_data table before any SQL-based transformations or normalization.
The next step is to transform the raw data into a normalized relational data model.
Transformation Stage
This stage represents the main focus of this study. The transformation is centered on the application of normalization principles to convert the raw JSONB documents into a structured relational data model.
After establishing the initial relational model, higher normal forms are evaluated to determine whether further decomposition is required.
First Normal Form (1NF):
Since the JSONB documents contain nested arrays and objects, their attributes are not represented in atomic values. This violates the requirements of First Normal Form, which states that each attribute must contain an atomic value.
I created the products_stage table to store the atomic attributes associated with each product. Top-level scalar attributes were mapped directly to columns, while nested objects were flattened into individual columns. The tags, images, and reviews attributes were intentionally excluded because they represent multi-valued attributes or relationships requiring separate relational tables.
The following SQL statement creates the products_stage table:
CREATE TABLE IF NOT EXISTS products_stage (
product_id INT PRIMARY KEY,
title TEXT,
description TEXT,
category TEXT,
price DECIMAL(10, 2),
discount_percentage DECIMAL(5, 2),
rating DECIMAL(5, 2),
stock INT,
brand VARCHAR(100),
sku TEXT,
weight DECIMAL(10, 2),
dimension_width DECIMAL(10, 2),
dimension_height DECIMAL(10, 2),
dimension_depth DECIMAL(10, 2),
warranty_information TEXT,
shipping_information TEXT,
availability_status VARCHAR(100),
return_policy TEXT,
minimum_order_quantity INT,
created_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ,
barcode TEXT,
qr_code TEXT,
thumbnail TEXT
)
Compared with raw_products_data shown in Figure 2, Figure 4 illustrates the data model of the products_stage table.
Figure 4. Data model of the products_stage containing only atomic values.
After creating the table, the following INSERT statement was executed to populate it with data extracted from the JSONB documents.
INSERT INTO products_stage(
product_id, title, description, category,
price, discount_percentage, rating,
stock, brand, sku, weight, dimension_width,
dimension_height, dimension_depth,
warranty_information, shipping_information,
availability_status, return_policy,
minimum_order_quantity, created_at,
updated_at, barcode, qr_code, thumbnail
)
SELECT
(raw_data ->> 'id'):: INT,
raw_data ->> 'title',
raw_data ->> 'description',
raw_data ->> 'category',
(raw_data ->> 'price'):: DECIMAL(10, 2),
(
raw_data ->> 'discountPercentage'
):: DECIMAL(5, 2),
(raw_data ->> 'rating'):: DECIMAL(5, 2),
(raw_data ->> 'stock'):: INT,
raw_data ->> 'brand',
raw_data ->> 'sku',
(raw_data ->> 'weight'):: DECIMAL(10, 2),
(
raw_data -> 'dimensions' ->> 'width'
):: DECIMAL(10, 2),
(
raw_data -> 'dimensions' ->> 'height'
):: DECIMAL(10, 2),
(
raw_data -> 'dimensions' ->> 'depth'
):: DECIMAL(10, 2),
raw_data ->> 'warrantyInformation',
raw_data ->> 'shippingInformation',
raw_data ->> 'availabilityStatus',
raw_data ->> 'returnPolicy',
(
raw_data ->> 'minimumOrderQuantity'
):: INT,
(raw_data -> 'meta' ->> 'createdAt'):: TIMESTAMPTZ,
(raw_data -> 'meta' ->> 'updatedAt'):: TIMESTAMPTZ,
raw_data -> 'meta' ->> 'barcode',
raw_data -> 'meta' ->> 'qrCode',
raw_data ->> 'thumbnail'
FROM
raw_products_data
The INSERT statement uses PostgreSQL's JSONB operators (-> and ->>) to navigate the JSON structure, extract values, and cast them to the appropriate SQL data types.
Figure 5 presents a sample of the populated products_stage table in PostgreSQL, showing a subset of its columns.
Figure 5. Sample output of the populated products_stage table in PostgreSQL.
Although the products_stage table satisfies the requirements of the First Normal Form (1NF), the complete relational model is not yet finalized. The excluded attributes must still be extracted into dedicated tables to ensure that multi-valued attributes are properly represented withtin the relational model.
Create the product_images table
To normalize the multi-valued images attribute, I created the product_images table.
Since a single product can have multiple images, the relationship between products_stage and product_images is one-to-many (1:N). Each image is therefore stored as a separate row and associated with its corresponding product through a foreign key.
CREATE TABLE IF NOT EXISTS product_images(
image_id SERIAL PRIMARY KEY,
product_id INT,
image_url TEXT,
CONSTRAINT fk_product_images
FOREIGN KEY(product_id)
REFERENCES products_stage(product_id)
)
Populate the product_images table
INSERT INTO product_images(product_id, image_url)
SELECT (raw_data->>'id')::INT,
jsonb_array_elements_text(raw_data->'images')
FROM raw_products_data
The jsonb_array_elements_text() function expands each element of the images array into a separate row, allowing each image URL to be stored as an atomic value.
The updated relational model is shown in Figure 6.
Figure 6. Updated relational model showing the one-to-many (1:N) relationship between products_stage and product_images.
Create the product_reviews table
The relationship between products_stage and the new product_reviews table is also a one-to-many relationship since one product can have multiple reviews.
The following SQL statement creates product_reviews:
CREATE TABLE IF NOT EXISTS product_reviews(
review_id SERIAL PRIMARY KEY,
product_id INT,
review_rating DECIMAL(3,2),
review_comment TEXT,
review_date TIMESTAMPTZ,
reviewer_name VARCHAR(100),
reviewer_email TEXT,
CONSTRAINT fk_product_reviews
FOREIGN KEY(product_id)
REFERENCES products_stage(product_id)
)
Populate product_reviews
INSERT INTO product_reviews (
product_id,
review_rating,
review_comment,
review_date,
reviewer_name,
reviewer_email
)
SELECT
(raw_data->>'id')::INT,
(review->>'rating')::DECIMAL(3,2),
review->>'comment',
(review->>'date')::TIMESTAMPTZ,
review->>'reviewerName',
review->>'reviewerEmail'
FROM raw_products_data,
jsonb_array_elements(raw_data->'reviews') AS review;
The jsonb_array_elements() function was used to iterate over the reviews array of JSON objects in each row of raw_products_data. Each review object was expanded into a separate row, allowing its individual attributes to be extracted and stored as atomic values.
The updated relational schema is shown in Figure 7.
Figure 7. Updated relational model showing the one-to-many (1:N) relationship between the products_stage table and the product_reviews table.
Create the tags table
Unlike the images and reviews attributes, the relationship between products and tags is many-to-many (M:N). A product can have multiple tags, and a tag can be associated with many products.
To model this relationship, I created a tags table that stores each unique tag only once. The following SQL statement creates the tags table:
CREATE TABLE IF NOT EXISTS tags(
tag_id SERIAL PRIMARY KEY,
tag_name TEXT UNIQUE
)
Populate the tags table
INSERT INTO tags(tag_name)
SELECT DISTINCT
jsonb_array_elements_text(raw_data->'tags')
FROM raw_products_data
I created the junction table product_tags to represent the many-to-many relationship between products with their corresponding tags through foreign keys.
Create the product_tags table
CREATE TABLE IF NOT EXISTS product_tags(
product_id INT,
tag_id INT,
PRIMARY KEY(product_id, tag_id),
CONSTRAINT fk_product_tags_product
FOREIGN KEY (product_id)
REFERENCES products_stage(product_id),
CONSTRAINT fk_product_tags_tag
FOREIGN KEY (tag_id)
REFERENCES tags(tag_id)
)
Populate the product_tags table
INSERT INTO product_tags (product_id, tag_id)
SELECT DISTINCT
(raw_data->>'id')::INT,
t.tag_id
FROM raw_products_data
CROSS JOIN jsonb_array_elements_text(raw_data->'tags') AS tag_name
INNER JOIN tags t
ON t.tag_name = tag_name;
The CROSS JOIN with jsonb_array_elements_text() expands the tags array of each product into individual rows, producing one row for each "product-tag" combination. These tag values are then matched with the corresponding records in the tags table using an INNER JOIN, allowing the tag_id values to be retrieved. Finally, the DISTINCT keyword ensures that each (product_id, tag_id) pair is inserted only once into product_tags.
The updated relational schema is shown in Figure 8.
Figure 8. Updated relational model showing the many-to-many (M:N) relationship between products_stage and tags, implemented through the product_tags junction table.
Second Normal Form (2NF):
For a relational model to satisfy the requirements of the Second Normal Form (2NF), it must first comply with the rules of the First Normal Form (1NF).
In the previous section, all tables were normalized to satisfy this prerequisite.
The next step is to examine whether each table in the 1NF relational model satisfies the second requirement of 2NF. This involves determining whether a non-key attribute within a table depends on only a subset of a composite primary key than the entire key.
Analyze products_stage
products_stage complies with 2NF. Since the primary key of the table is composed of a single attribute product_id (see Figure 4), partial dependencies cannot occur. Furthermore, all non-key attributes describe the product associated with product_id and are therefore fully functionally dependent on the primary key.
Analyze product_images
product_images fulfills the conditions of 2NF. Since the primary key consists of a single attribute image_id (see product_images in Figure 6), partial dependencies cannot occur. In addition, the non-key attributes, product_id and image_url, describe the image identified by image_id and are therefore fully functionally dependent on the primary key.
A common misconception is to assume that image_url depends on product_id because a product can have multiple images. However, that is not how the table is designed.
Each row represents a single image entity identified by image_id, while product_id simply establishes the relationship between the image and its corresponding product.
Analyze product_reviews
product_reviews satisfies the criteria for 2NF. Since the primary key consists of a single attribute review_id (see product_reviews in Figure 7), partial dependencies cannot occur. Furthermore, all non-key attributes are fully functionally dependent on the primary key.
Each row represents a single review entity identified by review_id, while product_id simply establishes the relationship between the review and its corresponding product.
Analyze tags and product_tags
tags satisfies 2NF. Since the primary key consists of a single attribute tag_id (see tags in Figure 8), partial dependencies cannot occur. Furthermore, the non-key attribute tag_name is fully functionally dependent on the primary key.
Unlike the previous tables, the product_tags table uses a composite primary key composed of product_id and tag_id (see product_tags in Figure 8).
Each row represents the association between a product and a tag, uniquely identified by the combination of product_id and tag_id.
However, the table contains no non-key attributes so no partial dependencies can exist. As a result, product_tags also satisfies the requirements of 2NF.
Third Normal Form (3NF):
For a relational model to comply with the conditions of the Third Normal Form (3NF), it must first satisfy the rules of 2NF.
In the previous section, all tables were normalized to satisfy this prerequisite.
The next step is to determine whether each table in the 2NF relational model satisfies 3NF.
During the analysis of each table, potential functional dependency candidates are identified and evaluated to determine whether they could introduce transitive dependencies. In other words, the objective is to identify if any non-key attribute within a table is transitively dependent on another non-key attribute in the same table.
Analyze products_stage
During the analysis, it was observed that products with stock value lower than 8 were consistently assigned the value "Low stock" in their availability_status attribute. Conversely, products with a stock value of 8 or greater were consistently assigned "In stock". This suggests a possible dependency between stock and availability_status.
However, this observation alone is insufficient to establish the existence of a functional dependency. As a result, the 3NF status of this table could not be determined.
Although removing the availability_status attribute would eliminate the potential dependency, it would alter the source data without sufficient proof that the attribute is reundundant.
Since the objective in this case study is to normalize the source data rather than modify its underlying structure, the attribute availability_status was retained in the table.
Analyze product_images
Since a product may have multiple images, product_id does not functionally determine image_url; therefore, no transitive dependency exists between non-key attributes. Every non-key attribute depends directly on the primary key image_id, thus satisfying 3NF.
Analyze product_reviews
During the examination, it was determined that each reviewer_email value was consistently associated with one reviewer_name suggesting a possible functional dependency between the two non-key attributes. However, this observation alone is insufficient to determine the existence of a functional dependency. As a result, the 3NF status of this table could not be determined.
Analyze tags and product_tags
The tags table satisfies 3NF. It contains only one non-key attribute tag_name, which depends directly on the primary key tag_id. Therefore, no transitive dependency can exist. The same reasoning applies to the product_tags table.
Results
First Normal Form (1NF)
The raw product data was initially stored in raw_products_data as JSONB documents.
During the 1NF transformation, the semi-structured data was converted into a set of relational tables: products_stage, product_images, product_reviews, tags and product_tags.
Nested arrays and objects were eliminated and each attribute was stored as an atomic value while preserving the relationships between the entities through primary and foreign keys.
The resulting relational model satisfied the First Normal Form (1NF). Figure 9 presents the complete relational model obtained after the 1NF transformation.
Figure 9. Final relational schema satisfying the requirements of the First Normal Form 1NF, derived from the raw JSONB product data.
Second Normal Form (2NF)
The analysis showed that no partial dependencies exist within the relational model. Tables containing single-attribute primary keys (products_stage, product_images, product_reviews, and tags) cannot exhibit partial dependencies because every non-key attribute is fully functionally dependent on its primary key.
Although the product_tags table contains a composite primary key (product_id, tag_id), it stores no non-key attributes. Consequently, no partial dependencies can exist, and the table therefore does not violate the Second Normal Form (2NF) requirements.
No modifications were required ater the 2NF evaluation. The relational data model remains identical to the one shown in Figure 9.
Third Normal Form (3NF)
The 3NF analysis identified two categories of tables within the relational model.
The first category consists of the product_images, tags, and product_tags, for which no transitive dependencies were identified. These tables comply with 3NF.
The second category consists of products_stage and product_reviews, where potential functional dependencies were observed. However, the available data was insufficient to conclusively establish these dependencies. Therefore, their compliance with 3NF could not be conclusively established.
As a result no modifications to the relational model were required following the 3NF analysis. The final relational model therefore remains identical to the model presented in Figure 9.
The resulting relational model was normalized to 2NF. Although 3NF was established for some tables, the 3NF status of the complete relational model could not be determined.
Discussion
This case study set out to explore an ELT approach for handling semi-structured data and transforming it into a relational data model. Beyond this objective, the study also strengthened both my practical and theoretical skills.
The practical side involves performing HTTP requests with the requests library to consume a REST API in Python. It also involves working extensively with SQL in PostgreSQL by using JSONB operators and set-returning functions.
On the theorical side, the exercise provided hands-on application of normalization principles requiring the evaluation of the data models across the normal forms. In addition, it deepened my understanding of data modeling, particularly how relationships (1:N and M:N) are represented through foreign keys and junction tables as the model evolves.
References
Top comments (0)