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

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

Comments

[Luis Cruz]

I appreciate how you've structured your ETL pipeline using separate modules for each stage, following the principle of separation of concerns, which makes the code more readable and maintainable. The use of Pandas for data manipulation and Psycopg2 for connecting to the PostgreSQL database are also great choices. I'm curious to know, have you considered implementing any data validation or error handling mechanisms to ensure the pipeline's robustness, especially when dealing with real-world datasets that may contain inconsistencies or missing values?

[kitchen_code]

Thank you! I really appreciate your feedback.

At this stage, I intentionally kept the project focused on understanding the core ETL workflow and building a clean, modular architecture. The dataset I used was relatively clean, so I only applied basic transformations such as renaming columns, removing duplicates, and standardizing formats.

For real-world datasets, I completely agree that robust validation and error handling become essential. I would consider adding data quality rules before loading the data. These are definitely areas I plan to explore in future iterations as I build more advanced pipelines.

Thanks for bringing this up!

[kitchen_code]

Thank you for taking the time to leave such thoughtful feedback. I really appreciate your insights.

I completely agree with your points about data lineage and data quality. Those are definitely areas I want to explore further in my upcoming projects and articles. I'd be happy to exchange ideas and connect.

Thanks again!

[Luis Cruz]

Thanks and I would be happy, too.
Please check my profile and portfolio.