English Experimental Comparison of Data Validation Tools in ETL Pipelines

Abstract

This paper proposes a reproducible experimental process for comparing validation tools for ETL pipeline execution results in two data storage environments: Postgres and Spark tables. In the first environment, Great Expectations (GX), SodaCL, SQL, and dbt tests were examined, while in the second environment, GE, SodaCL, Deequ, and Spark SQL were used. Both environments test the same sets of logical operations during extraction, transformation, and loading using soccer data from football-data.org (tiny), scaled to small, medium, and large datasets. The experiments are conducted in a deterministic environment with controlled fault injection, and time and resource metrics are recorded. While detecting violations functionally equivalently, the selected tools differ in criteria such as peak CPU load, execution time, and RAM consumption. Validation tool usage strategies are divided into two types: "universal," where a single tool covers three ETL stages, and "staged," where different tools are used at individual stages. In the first loop on a large dataset, the efficiency of SQL was confirmed at the transformation stage, with 129.9 ms compared to GX with 12513.9 ms and SodaCL with 384.9 ms. Therefore, the SodaCL+SQL+SodaCL staged strategy slightly outperforms the universal SodaCL strategy and is approximately 22 times faster than the universal GX strategy. GX incurs the highest costs during loading results validation. dbttest loses to other tools in terms of performance. In the second loop on a large dataset, CPU and RAM costs also remain roughly the same for all tools, as all tools are converted to Spark SQL queries. The universal Soda strategy leads in speed by a slight margin over SodaCL+SQL+SodaCL. The developed setup and experimental artifacts are publicly available.