Data Engineering Solution with Real-Time Analytics for Pharmacy Management

The flagship SaaS software has an architecture with more than 100 microservices. The two main data sources are the Apache Kafka streaming platform and Azure data lake.

Apache Kafka

On the backend, Apache Kafka uses operational PostgresDB and MongoDB as data sources. For data transfer, we used Debezium—an open-source distributed platform for change data capture. The data gets into Kafka in the form of JSON messages, and for each microservice, we create a separate topic.

Azure Data Lake

As a next step, we took data from Apache Kafka as a data source for the Azure data lake. Using custom PySpark scripts, we set up a data stream from Kafka that is converted into parquet-type files and stored in the data lake.

As we mentioned above, to process streaming data from Apache Kafka, the NIX team used PySpark scripts. From Kafka, raw data goes in two directions—temporary Postgres storage and the data lake.

Data from all sources is stored in PostgreSQL tables and as parquet files in the Azure data lake. For the ETL process, we used Apache Airflow and SQL functions to extract, process, and store raw data. After that, we used SQL functions to process the raw data, remove duplicates and empty rows, and save the data to permanent storage.

The NIX team was also responsible for legacy data processing and transformation from the client’s claim processing system. The legacy database has 8 folders, each containing 100 days of data, with about 1-1.3 million claims per day.

The data was originally compressed and encrypted, so we converted it using a prebuilt Java package in Python and transferred it to the database.