From Flat Files to Structured Data Stores: A Reproducible Benchmark for Enterprise Data Modernization

Introduction

It is possible for an enterprise to scan a form and retain the scanned work in exported files. If those records start to generate recurring reports or analytics, then how those records are stored is part of the problem of transformation. Vial and Verhoef et al. view digital transformation not as the acquisition of a new tool, but as technological change that is linked to changes in how organizations and value is created. The three reviews cited above (Nadkarni, 2021; Hanelt et al., 2021; Zhu et al., 2021) all come to a similar conclusion from different literatures: technology needs to fit into the existing processes, structures and capabilities.

This larger thesis is directly linked with the data layer. Legacy systems are recognized as a challenge for complex transformations, and coherent enterprise data architecture is tied to a better machine-learning capability, according to Cao and Iansiti . Data, digital platforms, and organizational processes are also included in the same management problem by Han et al. . In day to day implementation, it’s a less abstract question. When operational records are reused again and again, the storage choice becomes practical rather than abstract. An enterprise then has to decide whether exported flat files are still sufficient or whether the records should move into a structured store.

CSV remains common for good reasons: the format is portable, easy to inspect, and can be produced by almost any business application. The same simplicity becomes costly when only a small part of a large file is needed, because an unindexed file is normally parsed before the requested rows can be isolated. A binary table may reduce storage and parsing work, whereas a relational store can add indexes for frequently used keys. Neither choice is free. Compression may save disk space without helping every aggregation, and an index may make a lookup fast even though extra time and storage were spent when the database was built.

Database research gives several pieces of this comparison. Column-oriented systems were developed so that analytical queries would not have to read attributes they did not use . Pavlo et al. showed that loading, execution, and tuning costs can change the ranking of large-scale analytical systems. Lakehouse work later separated durable data storage from the services that query and manage it . But many enterprise teams start much closer to the desktop: they obtain departmental extracts, transaction exports or local databases. At that scale the immediate choice is not which distributed warehouse to buy but what is gained when repeated analysis moves away from a flat file.

We consider that smaller migration step with one fixed workload. The same transaction data are stored as CSV processed with pandas , as compressed HDF5 table , and as SQLite with B-tree indexes . The second configuration utilizes the binary table representation and selection mechanisms afforded by HDF5 . The third uses the in-process relational engine from SQLite . Its storage is row oriented though the engine can perform analytical SQL . For the two file-based cases, pandas offers the common tabular analysis layer .

The benchmark is driven by four questions:

• RQ1: investigates how the storage footprint and the materialization time scale as the transaction table grows from 100,000 to 1,000,000 rows.

• RQ2: compares end-to-end latencies for broad scans, aggregations, range filters and selective lookups.

• RQ3: investigates if one representation is still preferred over these query types.

• RQ4: uses the measured differences to discuss migration trade-offs for enterprise data modernization; it does not treat the experiment as a universal ranking of the three tools.

Rather than propose another enterprise architecture, we isolate one decision that appears inside many such architectures. A deterministic generator produces the transaction data, the records are written in three equivalent representations, and six queries with the same semantics are run against each one. We report storage, write time, and query latency from those executions. One result is especially useful for the migration question: the indexed relational case is very strong on selective access, yet it is not the fastest case for every aggregation. Mixed workloads therefore matter more than a single best-case query.

Section 2 gives the digital-transformation and data-management context. Section 3 defines the workload and the three representations, Section 4 records the execution protocol, and Section 5 examines the measured results. The limitations are discussed in Section 6 before the paper is concluded in Section 7.

Complete Article

The complete article, including all figures, tables, equations and algorithms, is available in the official publication PDF.

View Full PDF

Conclusion

We compared three local representations of the same enterprise transaction table: CSV processed with pandas, compressed HDF5 table, and indexed SQLite. Each representation was tested with six queries ranging from broad aggregation to selective customer lookup, at 100k, 500k and 1M rows.

The measurements do not favor one best representation. HDF5 reduced storage from 92.09 MiB to 44.26 MiB and was 3.69 times faster to materialize from the in-memory table with 1 million rows. On the selective operations, it reached 5.18-9.07 times of the baseline CSV. It took 209.86 MiB and 9.18 s for SQLite to load and index the same table with one million rows. This preparation was followed by a 30-day filter that took 0.101 s, the filtered departmental aggregation took 0.051 s and the customer lookup took about 0.00013 s. Q4 was the other way, with the top product aggregation being a little slower than CSV.

For enterprise digital transformation the practical lesson is limited to the workload we measured. The migration from flat files should be considered in light of what queries will actually be repeated after the data is moved. The footprint and parsing cost can be reduced with compact binary storage and indexed relational storage can repay extra preparation when selective access is common. A broader follow-up should include Parquet, DuckDB, PostgreSQL and cloud object storage, repeat the benchmark on public enterprise-scale data, and test cold-cache behavior, concurrency and end-to-end energy use.

References

1. G. Vial, “Understanding digital transformation: A review and a research agenda, ” The Journal of Strategic Information Systems, vol. 28, no. 2, pp. 118–144, 2019, doi: 10.1016/j.jsis.2019.01.003
2. P. C. Verhoef, T. Broekhuizen, Y. Bart, A. Bhattacharya, J. Q. Dong, N. Fabian, and M. Haenlein, “Digital transformation: A multidisciplinary reflection and research agenda, ” Journal of Business Research, vol. 122, pp. 889–901, 2021, doi: 10.1016/j.jbusres.2019.09.022
3. S. Nadkarni and R. Pruegl, “Digital transformation: A review, synthesis and opportunities for future research, ” Management Review Quarterly, vol. 71, pp. 233–341, 2021, doi: 10.1007/s11301-020-00185-7
4. A. Hanelt, R. Bohnsack, D. Marz, and C. Antunes Marante, “A systematic review of the literature on digital transformation: Insights and implications for strategy and organizational change, ” Journal of Management Studies, vol. 58, no. 5, pp. 1159–1197, 2021, doi: 10.1111/joms.12639
5. X. Zhu, S. Ge, and N. Wang, “Digital transformation: A systematic literature review, ” Computers & Industrial Engineering, vol. 162, p. 107774, 2021, doi: 10.1016/j.cie.2021.107774
6. R. Cao and M. Iansiti, “Digital transformation, data architecture, and legacy systems, ” Journal of Digital Economy, vol. 1, no. 1, pp. 1–19, 2022, doi: 10.1016/j.jdec.2022.07.001
7. X. Han, Y. Hu, L. Wang, and R. Zhou, “Enterprise digital management: Research review, current status and prospects, ” Management System Engineering, vol. 3, art. no. 8, 2024, doi: 10.1007/s44176-024-00032-z
8. M. Stonebraker et al., “C-Store: A column-oriented DBMS, ” in Proc. 31st Int. Conf. Very Large Data Bases, 2005, pp. 553–564.
9. D. J. Abadi, S. Madden, and N. Hachem, “Column-stores vs. row-stores: How different are they really?” in Proc. ACM SIGMOD Int. Conf. Management of Data, 2008, pp. 967–980, doi: 10.1145/1376616.1376712
10. A. Pavlo, E. Paulson, A. Rasin, D. J. Abadi, D. J. DeWitt, S. Madden, and M. Stonebraker, “A comparison of approaches to large-scale data analysis, ” in Proc. ACM SIGMOD Int. Conf. Management of Data, 2009, pp. 165–178, doi: 10.1145/1559845.1559865
11. M. Armbrust, A. Ghodsi, R. Xin, and M. Zaharia, “Lakehouse: A new generation of open platforms that unify data warehousing and advanced analytics, ” in 11th Conf. Innovative Data Systems Research (CIDR), 2021.
12. M. Folk, G. Heber, Q. Koziol, E. Pourmal, and D. Robinson, “An overview of the HDF5 technology suite and its applications, ” in Proc. 2011 EDBT/ICDT Workshop on Array Databases, 2011, pp. 36–47, doi: 10.1145/1966895.1966900
13. K. P. Gaffney, M. Prammer, L. C. Brasfield, D. R. Hipp, D. R. Kennedy, and J. M. Patel, “SQLite: Past, present, and future, ” Proceedings of the VLDB Endowment, vol. 15, no. 12, pp. 3535–3547, 2022, doi: 10.14778/3554821.3554842
14. W. McKinney, “Data structures for statistical computing in Python, ” in Proc. 9th Python in Science Conf., 2010, pp. 56–61, doi: 10.25080/Majora-92bf1922-00a
15. H. Alghamdi, “Assessing the impact of enterprise architecture on digital transformation success: A global perspective, ” Sustainability, vol. 16, no. 20, p. 8865, 2024, doi: 10.3390/su16208865
16. B. M. V. Bernardo, H. S. Mamede, J. M. P. Barroso, and V. M. P. D. dos Santos, “Data governance & quality management: Innovation and breakthroughs across different fields, ” Journal of Innovation & Knowledge, vol. 9, no. 4, p. 100598, 2024, doi: 10.1016/j.jik.2024.100598