DEV Community: kitchen_code

Incremental Relational Normalization of Semi-Structured API Product Data: An ELT Pipeline Case Study

kitchen_code — Mon, 27 Jul 2026 01:50:15 +0000

Check out my second article. I would be really happy to have your feedback.

Incremental Relational Normalization of Semi-Structured API Product Data: An ELT Pipeline Case Study

kitchen_code — Sat, 25 Jul 2026 23:51:12 +0000

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

Building my first ETL Pipeline: A Healthcare Management System case study

kitchen_code — Mon, 20 Jul 2026 00:26:02 +0000

Data engineering has long been a field of interest to me. Drawing upon my software engineering background, particularly in web development, I decided to undertake a structured learning path to master its core concepts. This article presents my first hands-on exercise in data engineering: the development of an ETL pipeline for a healthcare management system.

For this introductory case study, I chose to build an ETL pipeline because it provides a straightforward way to understand the three fundamental stages of the process: extraction, transformation and loading. By contrast, ELT pipelines are typically used within data warehouse or data lakehouse environments, adding another layer of concepts beyond the scope of this introductory exercise.

To support this case study, I selected the publicly available Healthcare Management System dataset published on Kaggle by Anouska Abhisikta. The dataset consists of five CSV files representing a healthcare management system: Appointment, Billing, Doctor, Medical Procedure and Patient. Its relational structure is particularly suitable for a introductory ETL pipeline practical demonstration. In addition, I deliberately selected a CSV-based dataset to avoid the additional complexity of formats such as JSON and Parquet files, or other data sources that would introduce distractions from the core ETL concepts explored in this exercise.

The objective is to build an ETL pipeline that extracts the data from these CSV files, applies data transformations, and loads the transformed data into a local PostgreSQL database.

METHOD:

The ETL pipeline was developed as a batch processing application using Python. A dedicated virtual environment (venv) was created in order to isolate project dependencies, with Pandas serving as the library for data manipulation. The source CSV files were stored in a data directory within the project and served as the input source throughout the pipeline.

The Python application follows the principle of separation of concerns, with each stage of the ETL pipeline being implemented in a separate module to improve readability, while main.py coordinates the execution of the pipeline (figure 1).

STAGE 1: EXTRACTION

During the extraction stage, each CSV file is independently read in its own dedicated module and the extraction function returns a Pandas DataFrame, marking the first stage of the ETL pipeline (Figure 2).

STAGE 2: TRANSFORMATION

The transformation stage demonstrates basic data processing techniques using the Python library Pandas. Given that this is a beginner case study, this data transformation is intentionally simple and is intended to understand the Transform phase rather than performing complex data manipulation strategies. Depending on the use case, different transformation rules may be applied to the same dataset.

Figure 3 illustrates an example of the data transformation performed on the Appointment dataset. This includes duplicate removal (drop_duplicates()), data type conversion, column renaming (rename()), and the creation of surrogate primary keys.

STAGE 3: LOAD

To load the transformed data, the Psycopg2 library was used to establish a connection between my Python application and a local PostgreSQL database. To centralize the database connection logic, a database.py file was created as shown in figure 4.

As an additional security layer, environment variables were used to avoid hardcoding credentials in the source code. Accordingly, the python-dotenv library was used to load these environment variables into the application at runtime.

Once the database connection is established, dedicated loading functions were implemented for each transformed dataset. These functions follow the same workflow. First, a database cursor is created to execute SQL statements. Then, the corresponding table is created if it does not already exist, and the transaction is committed before the insertion phase. This ensures that the table is successfully created and permanently saved, even if an error occurs during data insertion.

The transformed dataset is then iterated row by row so that each record is inserted into the corresponding table in the local PostgreSQL database. The transaction is committed again to permanently save the inserted data and the cursor is closed to release the associated resources. Figure 5 presents an example of the loading phase implementation.

OPTIONAL:

As an introduction to containerization concepts, the Python application was packaged using Docker. A Dockerfile was created to define the instructions required to build the Docker image as illustrated in figure 6.

Only the Python application was containerized, while the PostgreSQL database is kept running locally outside the image. Figure 7 presents the global architecture of this exercise.

RESULT

The main.py file plays a central role in coordinating the execution of the ETL pipeline. It acts as the entry point of the application by orchestrating the sequential execution of the different stages for each dataset entity.

The complete implementation of this file is shown in the code block below.

from extract.patient import extract_patient_data
from transform.patient import transform_patient_data
from load.patient import load_patient_data

from extract.doctor import extract_doctor_data
from transform.doctor import transform_doctor_data
from load.doctor import load_doctor_data

from extract.appointment import extract_appointment_data
from transform.appointment import transform_appointment_data
from load.appointment import load_appointment_data

from extract.billing import extract_billing_data
from transform.billing import transform_billing_data
from load.billing import load_billing_data

from extract.medical_proecdure import extract_med_procedure_data
from transform.medical_procedure import transform_med_procedure_data
from load.medical_procedure import load_med_procedure_data

from dotenv import load_dotenv
from database import get_connection

patient_data = extract_patient_data()
patient_table_data = transform_patient_data(patient_data)

doctor_data = extract_doctor_data()
doctor_table_data = transform_doctor_data(doctor_data)

appointment_data = extract_appointment_data()
appointment_table_data = transform_appointment_data(appointment_data)

billing_data = extract_billing_data()
billing_data_table = transform_billing_data(billing_data)

procedure_data = extract_med_procedure_data()
procedure_data_table = transform_med_procedure_data(procedure_data)

load_dotenv()
conn = get_connection()

if conn:
    try:
        print("Connected to database")
        load_patient_data(patient_table_data, conn)
        load_doctor_data(doctor_table_data, conn)
        load_appointment_data(appointment_table_data, conn)
        load_billing_data(billing_data_table, conn)
        load_med_procedure_data(procedure_data_table, conn)
    finally:
        conn.close()
else:
    print("Failed to connect to database")

After the execution of the main.py file, the pipeline successfully extracted data from the CSV files, applied the predefined transformations and loaded the transformed data into the PostgreSQL database. All relational tables were created as shown in Figure 8 and 9.

In conclusion, this study provided a practial experience with the fundamental concepts involved in developing a batch ETL pipeline. It enbales you to gain experience in:

Structuring Python projects with separation of concerns (modular packages per ETL stage).
Environment isolation with venv and dependency management with Pip.
Hands-on pandas transformations.
Connection Python applications to a local PostgreSQL database, with psycopg2 and safe credential handling using environment variables.
Basic Docker fundamentals (Dockerfile, building image, running containers).

As of now, the pipeline does not include an orchestration tool for scheduling or automating executions, and the PostgreSQL database is not containerized so the setup isn’t fully portable. These design choices are made to keep this exercise focused on understanding core ETL concepts while adding a basic application of Docker features. Future work will focus on different data sources such as Json, Parquet or perhaps a REST API and eventually explore ELT patterns.

Resources

Kaggle Healthcare Management System dataset (under the Apache 2.0 license)
Github repository of this exercise