One Framework, Many Engines: Source-Agnostic Automated Data Comparison across Heterogeneous Databases
DOI:
https://doi.org/10.15680/IJCTECE.2021.0402006Keywords:
data comparison, data reconciliation, heterogeneous databases, database migration, data quality, data validation, ETL testing, schema mapping, NoSQL, data warehousingAbstract
Organizations undertaking database migrations, platform consolidations, cloud data warehouse adoption, or merger driven data integration routinely face a problem that is deceptively difficult: confirming that two datasets, residing in two different database engines, with two different type systems, two different null handling conventions, and two different query languages, actually represent the same information. This article examines automated data comparison across heterogeneous database engines as a distinct engineering discipline, separate from both conventional software testing and conventional data quality management, though related to both. The article reviews the relational, data warehousing, data quality, and distributed systems literature that bears on the problem, enumerates the specific failure modes that arise when comparison logic implicitly assumes a single engine's semantics, and proposes a layered Source-Agnostic Comparison Framework, referred to throughout as SACF, built around an Abstraction Layer, a Normalization Layer, a Comparison Layer, a Discrepancy Classification Layer, and a Governance and Reporting Layer. The article describes the architectural components needed to operate SACF, concrete comparison techniques organized by data volume and consistency requirements, governance practices for sustaining the discipline over time, and two illustrative composite scenarios drawn from patterns commonly discussed in public industry commentary prior to March 2021. Several data tables are included to summarize engine specific quirks, comparison strategy selection, and failure mode mappings in a structured, referenceable form. All numeric figures outside the structured tables are presented as indicative, order of magnitude observations rather than as a validated empirical result, and the article closes with a discussion of limitations and directions for continued practice.
References
1. Batini, C., and Scannapieco, M. (2006). Data Quality: Concepts, Methodologies and Techniques. Berlin: Springer.
2. Bernstein, P. A., and Haas, L. M. (2008). Information Integration in the Enterprise. Communications of the ACM, 51(9), 72 to 79.
3. Brewer, E. (2000). Towards Robust Distributed Systems. Keynote address, Proceedings of the Nineteenth Annual ACM Symposium on Principles of Distributed Computing.
4. Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377 to 387.
5. DAMA International. (2017). DAMA-DMBOK: Data Management Body of Knowledge, 2nd edition. Basking Ridge: Technics Publications.
6. Dasu, T., and Johnson, T. (2003). Exploratory Data Mining and Data Cleaning. Hoboken: Wiley.
7. Date, C. J. (2003). An Introduction to Database Systems, 8th edition. Boston: Addison Wesley.
8. Elmagarmid, A. K., Ipeirotis, P. G., and Verykios, V. S. (2007). Duplicate Record Detection: A Survey. IEEE Transactions on Knowledge and Data Engineering, 19(1), 1 to 16.
9. English, L. P. (1999). Improving Data Warehouse and Business Information Quality. New York: Wiley.
10. Gilbert, S., and Lynch, N. (2002). Brewer's Conjecture and the Feasibility of Consistent, Available, Partition Tolerant Web Services. ACM SIGACT News, 33(2), 51 to 59.
11. Halevy, A., Rajaraman, A., and Ordille, J. (2006). Data Integration: The Teenage Years. Proceedings of the 32nd International Conference on Very Large Data Bases.
12. Humble, J., and Farley, D. (2010). Continuous Delivery: Reliable Software Releases through Build, Test, and Deployment Automation. Boston: Addison Wesley.
13. Inmon, W. H. (1992). Building the Data Warehouse. New York: Wiley.
14. International Organization for Standardization. (2008). ISO/IEC 25012:2008, Software Engineering, Software Product Quality Requirements and Evaluation, Data Quality Model. Geneva: ISO.
15. Kimball, R., and Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd edition. Hoboken: Wiley.
16. Kleppmann, M. (2017). Designing Data Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. Sebastopol: O'Reilly Media.
17. Linstedt, D., and Olschimke, M. (2015). Building a Scalable Data Warehouse with Data Vault 2.0. Burlington: Morgan Kaufmann.
18. Loshin, D. (2001). Enterprise Knowledge Management: The Data Quality Approach. San Diego: Academic Press.
19. Loshin, D. (2010). Master Data Management. Burlington: Morgan Kaufmann.
20. Newman, S. (2019). Monolith to Microservices: Evolutionary Patterns to Transform Your Monolith. Sebastopol: O'Reilly Media.
21. Rahm, E., and Do, H. H. (2000). Data Cleaning: Problems and Current Approaches. IEEE Data Engineering Bulletin, 23(4), 3 to 13.
22. Redman, T. (1996). Data Quality for the Information Age. Norwood: Artech House.
23. Sadalage, P. J., and Fowler, M. (2012). NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence. Boston: Addison Wesley.
24. Stonebraker, M., and Ilyas, I. F. (2018). Data Integration: The Current Status and the Way Forward. IEEE Data Engineering Bulletin, 41(2), 3 to 9.
25. Wang, R. Y., and Strong, D. M. (1996). Beyond Accuracy: What Data Quality Means to Data Consumers. Journal of Management Information Systems, 12(4), 5 to 33.
