Abstract
IPOS point-of-sale software, widely used by Indonesian small and medium retail enterprises (UMKM), exports transaction data as Excel files with no enforced schema—producing format-variable, multi-row receipt blocks with heterogeneous date representations, locale-dependent numeric formats, and embedded unit strings that resist conventional relational import. Transforming these unstructured exports into a relational database requires a structured architectural approach capable of handling format variability, type inconsistency, and record duplication. This study designs and implements a Spring Boot-based ETL (Extract, Transform, Load) service that applies the Data Transfer Object (DTO) pattern through ten purpose-specific DTO classes covering each pipeline phase, structured within a four-layer Model-View-Controller (MVC) architecture (Controller-Service-Repository-Entity). The Extractor employs a streaming Excel reader with dynamic column-layout detection based on header keywords, producing raw String-typed ExtractedReceipt and ExtractedItem DTOs. The Transformer applies six normalization steps via four utility classes—StringNormalizer, DateParser (seven date-format patterns), NumberParser (Indonesian and Western currency formats), and a HashSet-based duplicate detector—converting raw strings into typed ValidatedReceipt and ValidatedItem DTOs with explicit error logging. The Loader performs batch inserts per 1,000 records using pre-loaded duplicate sets for O(1) lookup. The pipeline operates asynchronously, returning a jobId immediately while processing continues on a background thread. Functional evaluation across ten scenarios yielded a 100% pass rate, covering valid files, invalid file types, date-format heterogeneity, embedded-unit quantity strings, Indonesian numeric formats, cross-file and intra-file duplicate detection, grand-total reconciliation tolerance, and product-variation tracking. Performance observation shows that files of 200–500 receipts complete within 5–15 seconds. These results indicate that a DTO-centric, explicitly mapped ETL pipeline over Spring Boot MVC provides a maintainable, auditable, and production-ready solution for UMKM retail data integration.
Article Details
How to Cite
Citation FormatReferences
26 References- Aghili, S., Asadi, S., Shukur, Z., & Nematbakhsh, M. A. (2023). Automating extract, transform, load (ETL) pipelines using machine learning triggered workflow optimization. International Journal of Intelligent Systems and Applications in Engineering. https://doi.org/10.18178/ijisae.2023.11.2.7193
- Ayet, A., Marti-Carvajal, A. J., Agreda-Perez, L. H., & Sola, I. (2023). Data extraction and comparison for complex systematic reviews: A step-by-step guideline. Systematic Reviews. https://doi.org/10.1186/s13643-023-02381-4
- Barahama, A. D., & Wardani, R. (2022). Analysing user reviews with ETL using Pentaho Data Integration. Multimedia Tools and Applications. https://doi.org/10.1007/s11042-022-12410-4
- Filho, N. (2025). Best practices for using DTOs (Data Transfer Objects) in a clean architecture. Zenodo Repository. https://doi.org/10.5281/zenodo.14531413
- Ginting, M., & Rahmatulloh, A. A. (2025). Analysis of the effectiveness of data warehousing and ETL in management information systems using the neural networks method. Telematika: Jurnal Informatika dan Teknologi Informasi, 22(3), 86–97. https://doi.org/10.31315/telematika.v22i3.14027
- Gobin, M., Duclos, A., Jannot, A. S., & Prouteau, A. (2025). From data extraction to analysis: A comparative study of capabilities in scientific literature. Frontiers in Artificial Intelligence. https://doi.org/10.3389/frai.2025.1587244
- Gupta, V. K. (2025). Building robust REST APIs with Spring Boot. World Journal of Advanced Engineering Technology and Sciences. https://doi.org/10.30574/wjaets.2025.15.3.1078
- Hartono, M. A., & Mailoa, E. (2024). Pembuatan website REST API dengan kombinasi framework Spring Boot dan MyBatis Generator. Jurnal Indonesia: Manajemen Informatika dan Komunikasi. https://doi.org/10.35870/jimik.v5i2.712
- Hayes, M., Cruz, J. D., & Santos, R. A. (2024). Point of sale's transaction data: Envisioning micro small and medium enterprise (MSME)'s inventory management strategy. Management Journal. https://doi.org/10.60016/majcafe.v32.25
- Hutabalian, P., Ginting, B. R. P., Zaidan, M. Q., Alfisyahrina, N., & Rozikin, C. (2024). Implementasi pipeline ETL/ELT dan model dimensional untuk analisis penjualan Shopee menggunakan PostgreSQL, Docker, dan Apache Superset. JITET (Jurnal Informatika dan Teknik Elektro Terapan), 13(3S1). https://doi.org/10.23960/jitet.v131351.8093
- Ivanov, A., Kovalev, S., Petrov, M., & Smirnov, D. (2024). The analysis of customers' transactions based on POS and RFID data using big data analytics tools in the retail space of the future. Applied Sciences, 14(24), 11567. https://doi.org/10.3390/app142411567
- Izonin, I., Tkachenko, R., Shakhovska, N., Ilchyshyn, B., & Singh, K. K. (2022). A two-step data normalization approach for improving classification accuracy in the medical diagnosis domain. Mathematics, 10(11), 1942. https://doi.org/10.3390/math10111942
- Jánki, Z. R., & Bilicki, V. (2023). The impact of the Web Data Access Object (WebDAO) design pattern on productivity. Computers, 12(8), 149. https://doi.org/10.3390/computers12080149
- Kurniawan, R. (2025). Implementation of point of sale (POS) systems in culinary MSMEs: Improving operational efficiency. JOINTECS (Journal of Information Technology and Computer Science), 8(1). https://doi.org/10.31328/jointecs.v8i1.7330
- Li, X., Zhang, Y., Wang, J., & Liu, H. (2022). Handling unstructured Excel data via automated normalization in ETL pipelines. IEEE Access. https://doi.org/10.1109/ACCESS.2022.3159021
- Pandey, A., Sharma, R., Singh, P., & Kumar, V. (2025). Building robust REST APIs with Spring Boot: A practical guide. Journal of Computer Science and Technology Studies. https://doi.org/10.32996/jcsts.2025.7.4.120
- Ramadugu, G. (2024). Microservices with Spring Boot: Simplifying distributed systems. International Journal For Multidisciplinary Research. https://doi.org/10.36948/ijfmr.2024.v06i05.28930
- Saputra, D. A. (2024). Penerapan data pipeline guna kebutuhan analisis produk elektronik pada website e-commerce Tokopedia [Undergraduate thesis, STT Terpadu Nurul Fikri].
- Singi, K. R. (2023). Performance optimization strategies for high-concurrency Spring Boot microservices in enterprise financial systems. The Eastasouth Journal of Information System and Computer Science. https://doi.org/10.58812/esiscs.v1i02.883
- Supriyanto, A. (2024). Efficiency comparison in prediction of normalization with data mining classification. Advances in Science, Technology and Engineering Systems. https://doi.org/10.25046/aj060415
- Suryadana, I. G. A., Dewi, N. K. C., & Pratama, I. M. A. (2024). Descriptive analytics sales data visualization with ETL. Technovate. https://doi.org/10.59890/technovate.v1i2.49
- Sutanto, B. (2024). Optimizing decision making in MSMEs through business intelligence dashboards and POS integration. Seminar Nasional Inovasi Teknologi. https://doi.org/10.31294/snit.v1i1.8634
- Syaputra, H., Apriyandi, D., Husin, M. N., & Desnelita, Y. (2023). Database consistency improvement in job offering system using normalization method. Jurnal Teknologi dan Open Source. https://doi.org/10.36378/jtos.v6i1.4022
- Trisnawati, E., Susanto, T. D., & Handayani, P. W. (2024). Generating user personas for eliciting requirements using online data from point-of-sale patterns. Journal of Information Systems Engineering and Business Intelligence, 10(1), 110–125. https://doi.org/10.20473/jisebi.10.1.110-125
- Vieira, R., Souza, L., Lima, M., & Costa, F. (2024). From DTO to ViewModel: Mapping strategies between layers in C# and Java. Leaders Tec. https://doi.org/10.5281/zenodo.11186754
- Yalamati, S. S. A. (2025). Resilient microservice patterns using Java 17 and Spring Boot 3.2 in cloud-native systems. International Journal of Science and Research Archive. https://doi.org/10.30574/ijsra.2025.16.3.2559
License
Copyright (c) 2026 Adhi Septian Nugroho, Yeremia Alfa Susetyo

This work is licensed under a Creative Commons Attribution 4.0 International License.
This is an open-access journal distributed under the Creative Commons Attribution 4.0 International License (CC BY 4.0), which permits unrestricted use, distribution, reproduction, and adaptation in any medium (even commercially), provided appropriate credit is given to the original author(s), a link to the license is provided, and any changes made are indicated. Authors retain copyright without restrictions and grant the journal the right of first publication as well as non-exclusive publishing rights for archiving, indexing, and scholarly dissemination.
Downloads
OJS UsageStats PluginMost read articles by the same author(s)
- Implementation of a Mental Health Care Website for Psychological Counseling and Education Based on Spring Boot and Tailwind CSS Yosua Christian Prasetio, Yeremia Alfa Susetyo · Vol. 6 No. 1 (2026): APRIL 2026
- Performance Analysis of NoSQL Databases: MongoDB Document Store and Redis Key-Value Store in Microservices-Based Applications Using Flask Dava Ataya Shafi Andali, Yeremia Alfa Susetyo · Vol. 5 No. 2 (2025): AUGUST 2025
- The UI/UX Design of a Mobile-Based Catering Application Using the Activity Centered Design Method Faizal Rahmadani, Yeremia Alfa Susetyo · Vol. 5 No. 1 (2025): APRIL 2025
Recommended Articles
Journal Transparency
Additional InformationLearn more about how this journal ensures quality, ethics, and transparency.