Integrating Semantic Transformation Within Extract-Transform-Load Pipelines: A Big Data Perspective

In recent years, digital data generation has exploded across industries due to the rapid growth of internet services, social media, mobile devices, and decentralized applications [1, 2]. This deluge of information is commonly characterized by the "4Vs" of big data: Volume, Variety, Velocity, and Veracity [3]. In practical terms, organizations are confronted with massive, fast‐growing datasets that are often highly heterogeneous (varying formats and structures) and generated continuously in real time [4-7]. Managing and extracting value from such data is challenging, yet crucial – modern enterprises rely on data-driven decision systems in domains like e-commerce, social media analysis, and healthcare, which demand continuous data processing and analysis [8-10]. The inherent value in these large-scale datasets makes it imperative to store, integrate, and analyze the data despite the difficulties in handling their size and complexity [3-6, 8].

To harness big data for analytics, organizations typically consolidate data from multiple sources into a data warehouse – a centralized repository designed for query processing and decision support [11, 12]. Extract-Transform-Load (ETL) processes are the backbone of data warehousing [8, 12]. In an ETL pipeline, data is extracted from diverse source systems, transformed into a unified schema (e.g., a multi-dimensional data cube format) through cleaning and restructuring, and then loaded into the warehouse for future analysis [8, 11, 12]. Effective ETL is critical: analytical tools and on-line analytical processing (OLAP) systems can only deliver reliable insights if the underlying data is well-structured, clean, and readily accessible [8]. ETL enables moving data from operational databases to analytical repositories, making integrated historical data available for business intelligence and interoperability across cloud or distributed environments [11, 12]. However, conventional ETL pipelines struggle with big data. Despite their importance to big data analytics [13], traditional ETL methods often become time-consuming and cost-prohibitive when dealing with extremely large, heterogeneous inputs [14]. The complexity of extracting and transforming data from numerous sources at scale can overwhelm existing ETL tools, leading to bottlenecks in data warehousing.

A number of research efforts and commercial tools have attempted to improve ETL performance under big data constraints, but significant limitations remain. Academic approaches: Many studies focus on optimizing the transformation phase (e.g., data cleaning, filtering, merging) to streamline ETL [8, 11, 12]. While these techniques can accelerate certain tasks, generalizing them to arbitrary heterogeneous data sources remains difficult [8]. Some works introduce semantic or ontological methods to enrich and speed up transformations [8-17], yet these often incur high computational overhead or latency [8-13]. To support real-time analytics, researchers have also explored parallel processing frameworks—Hadoop MapReduce [17] and Apache Spark [18]—to expedite data loading. These frameworks improve throughput, but they come with trade-offs: Hadoop’s distributed storage and processing can introduce substantial computational overhead [16-18], and Spark (while faster) does not inherently ensure data completeness or accuracy (addressing Volume and Veracity) [16-18]. Industry tools: Modern ETL software (open-source platforms like Pentaho Kettle, Talend [19, 20], and commercial suites like Informatica, SAS Data Management, Oracle Data Integrator, IBM DataStage [21-23]) provide user-friendly GUIs and flexibility in designing workflows. Despite their usability, these tools still struggle with the 4V challenges at extreme scale [8, 24]. They excel at mapping data between sources and targets, but issues like complex data heterogeneity, high-speed streaming input, and data quality assurance are only partially addressed. For example, some ETL configurations include duplicate record removal and data merging to reduce data volume, but this can lead to false positives (erroneous deletions) under typical big data conditions such as imbalanced, high-dimensional data with complex, non-linear patterns [25]. Simply dropping data to save space may improve memory usage, yet it risks compromising analytical accuracy (Veracity) if important information is lost [8]. Balancing efficiency (Volume/Velocity) with information preservation (Veracity) remains an open challenge in current ETL solutions.

Table 1. Limitations of existing Extract-Transform-Load (ETL) approaches

Given these limitations as shown in Table 1, there is a clear research gap: current ETL methodologies do not fully meet big data requirements. No existing solution provides a generalized, scalable ETL pipeline that simultaneously handles huge volumes, diverse data types, high ingestion rates, and stringent accuracy needs [8]. Integrating data quality optimization into each stage of ETL (to maintain Veracity while addressing Volume, Variety, and Velocity) is largely unexplored in the state-of-the-art approaches. This gap forms the motivation for our work.

To address these challenges, we propose a novel ETL framework called Semantic Transformation assisted Correlative Multi-Parametric Duplication Removal-Extract-Transform-Load (STCM-ETL). The STCM-ETL approach augments the standard extract–transform–load pipeline with a data quality optimization layer, targeting all 4Vs of big data. In the proposed framework, input from multiple heterogeneous sources (e.g., CSV, JSON, XML files) is first logically parsed and extracted using rule-based mappings to ensure no significant information is lost (addressing Volume and Variety). The data is then subjected to semantic transformation: we employ word embedding techniques (e.g., Word2Vec) to represent textual and unstructured data in a consistent feature space, enabling the integration of diverse data types and reducing dimensionality. Next, a Correlation-based Multi-Parametric Duplication Removal (CMDR) algorithm operates on the transformed data to identify and eliminate redundant records across incremental batches. This step uses statistical measures (such as cross-correlation and significance testing) to drop duplicate or highly correlated entries, minimizing data volume while preserving critical information (thereby improving processing Velocity and maintaining Veracity). The refined, cleaned data is then loaded into the data warehouse for analysis. To further ensure high data quality, the framework incorporates additional measures like resampling (to handle class imbalances), normalization, and even a lightweight machine learning validation step that checks if predictive accuracy remains high after ETL. Through this integrated design, STCM-ETL addresses the full spectrum of big data ETL challenges: it is built to scale with large volumes, accommodate variety via semantic integration, ingest streaming data efficiently, and uphold veracity by filtering out noise and enforcing data quality standards.

The novelty of STCM-ETL lies in its combination of semantic transformation and correlation-driven data reduction within the ETL pipeline to tackle big data's core challenges holistically. This is, to our knowledge, one of the first ETL solutions to cumulatively address Volume, Variety, Velocity, and Veracity requirements in a unified framework. Experimental results demonstrate that the proposed approach can drastically improve performance and reliability: for example, our prototype achieved about 93% reduction in data storage requirements while maintaining ~98% accuracy (95% F-measure) on analytics tasks, confirming that significant efficiency gains are possible without sacrificing data quality. Thus, STCM-ETL fills the identified research gap by providing a robust, scalable ETL strategy for real-time big data analytics, ensuring that enterprises can derive timely and trustworthy insights from their rapidly growing data assets.

Efficient big data analytics depends critically on the quality, relevance, and structure of data produced during the ETL pipeline. However, existing ETL approaches predominantly optimize isolated stages—either extraction (rule-based parsing and cleansing), transformation (semantic or rule-based mapping), or loading (Hadoop/Spark-based distribution). This fragmented optimization leads to suboptimal outcomes because data redundancy, low-significance attributes, and heterogeneous formats continue to propagate through the pipeline, degrading analytic accuracy, increasing storage cost, and inflating computation time. Despite several attempts at feature reduction and duplication elimination, current models do not adequately examine how such reductions affect the veracity and reliability of analytical outputs.

A clear research gap exists: no unified ETL framework simultaneously performs semantic transformation, multi-parameter duplication removal, and data-quality optimization while preserving veracity for incremental, multi-source data streams. Existing methods either reduce data without assessing its analytical significance or load cleaned data without verifying that the remaining subset is both computationally efficient and analytically reliable. This calls for an integrated ETL strategy that treats extraction, transformation, loading, and quality assurance as a cohesive pipeline rather than independent tasks.

3.1 Core limitations in existing Extract-Transform-Load models

• Focus on isolated stages (extraction-only or loading-only optimization) rather than an end-to-end integrated pipeline.
• Redundancy removal methods do not assess the analytical significance or contribution of retained features.
• Semantic transformation techniques reduce dimensionality but do not ensure preservation of data veracity.
• Loading frameworks (e.g., Hadoop/Spark) operate blindly on whatever data arrives, without built-in quality or relevance checks.
• Lack of support for incremental, multi-source, and heterogeneous data ingestion with reliability guarantees.
• Insufficient evaluation of how data reduction impacts downstream analytics accuracy and stability.

3.2 High-level architecture of the proposed solution

To address these gaps, the proposed STCM-ETL architecture integrates:

• Rule-based multi-source extraction: logical parsing, heterogeneity handling, and unified sequence generation.
• Semantic transformation: tokenization followed by Word2Vec-based embedding to obtain low-dimensional semantic vectors.
• CMDR: eliminating duplicates and low-significance elements using correlation and statistical ranking tests.
• Incremental & interval-based loading: controlled warehouse population with reduced data size and latency.
• Data-quality optimization layer: using Synthetic Minority Over-sampling Technique and Edited Nearest Neighbor (SMOTE-ENN), normalization, and ML-based validation to ensure reliability and veracity of stored data.

5.1 Overview of Semantic Transformation assisted Correlative Multi-Parametric Duplication Removal- Extract-Transform-Load framework

We propose a Semantic Transformation assisted STCM-ETL framework tailored for big data. The system performs multi-level optimization across the ETL pipeline (Extraction, Transformation, Loading) to address the 4Vs of big data (Volume, Variety, Velocity, Veracity). Figure 1 illustrates the overall architecture (multi-source input, transformation, and optimized loading). In summary, STCM-ETL first integrates data from heterogeneous sources, then applies cleaning and semantic embedding to reduce data volume, followed by the novel CMDR mechanism to eliminate redundant information while preserving important features. Finally, an incremental loading strategy stores the optimized data, and an optional analytics module ensures the veracity of the outcomes. The key innovative components – Word2Vec-based embedding, CMDR (correlation analysis with a Wilcoxon rank sum significance test), and incremental loading – work in concert to improve ETL efficiency under high Volume (large data), Variety (multiple formats), Velocity (streaming inputs), and Veracity (data quality). Below, we detail each stage of the proposed method and its contributions. The proposed ETL model was executed, as depicted in Figure 1.

5.2 Extraction phase

Multi-source heterogeneous data extraction: To handle variety, STCM-ETL supports multiple file formats (e.g., CSV, JSON, XML). We employ format-specific parsing and logical rules to extract and unify data from these sources. For instance, an XML parser (Python’s xml.etree module) is used to convert XML files into structured elements (tags, attributes, text) for further processing. Similarly, JSON and CSV inputs are parsed using appropriate routines. All extracted data fields are then joined/merged into a consolidated dataset. This flexible extraction mechanism allows the framework to ingest 15 sequential data chunks (in our experiments, 5 in each format CSV/JSON/XML) which emulate incremental real-world feeds. By integrating disparate sources in a streaming fashion, the extraction phase addresses Variety and contributes to Velocity (through continuous, batched ingestion).

5.3 Transformation phase

After extraction, the unified data undergoes a series of transformation steps to reduce noise, generate semantic features, and remove duplicates:

5.3.1 Pre-processing and tokenization

We apply standard text preprocessing to normalize the data. This includes removing punctuation and other non-word symbols, eliminating stop words (using NLTK stop-word lists), and converting text to lower-case. These steps strip out noise and ensure consistency without losing informational content. Next, each pre-processed text entry is tokenized into words. By splitting sentences into individual word tokens, we prepare the data for vectorization. (These basic cleaning steps are well-known in ETL; hence we summarize them briefly.)

5.3.2 Semantic embedding with Word2Vec

To tackle data volume and produce a machine-readable representation, STCM-ETL transforms tokens into semantic embeddings using Word2Vec. We leverage Google’s pre-trained Word2Vec model (300-dimensional vectors) to encode each word token into a dense numeric vector. This yields a low-dimensional feature matrix for the input corpus, dramatically reducing data size while preserving meaningful relationships between words. In our implementation, each token is mapped to a latent vector, and token context (neighboring words within a window) influences the embedding. The result is an embedding matrix that captures semantic similarities. By compressing text into numeric vectors, this step decreases storage and memory needs (addressing Volume) and can speed up processing (Velocity) due to the smaller feature space. It also provides a unified feature representation across heterogeneous sources, aiding Variety handling.

5.3.3 Correlative multi-parametric duplication removal

This is the core novel contribution of our method, aimed primarily at reducing redundant data volume and improving processing velocity without compromising data veracity. CMDR operates on the Word2Vec embeddings to detect and remove duplicate or highly correlated data points across the incremental input streams. It consists of two complementary techniques: a cross-correlation analysis to find homogeneous (highly related) features, and a Wilcoxon rank sum test (WRST) to evaluate each feature’s significance. The motivation is that simply dropping all strongly correlated data might remove information that is still important for predictive accuracy. Thus, CMDR filters out only those features that are both duplicated and insignificant.

5.3.4 Correlation-based redundancy check

We compute pairwise correlations among the semantic feature vectors using Pearson’s correlation coefficient (Eq. (1)). If the correlation between two data vectors exceeds a threshold (set to 0.6 in our design), they are considered homogeneous duplicates. High correlation (values near 1) indicates that one feature is largely redundant given the other. Such duplicated elements can increase storage and computation costs without benefiting the analysis [10]. We mark all features with ≥ 0.6 correlation for potential removal to reduce the data volume. This correlation-driven filtering ensures only one representative from any group of highly similar features is retained, thus cutting down data repetition.

$r=\frac{\sum_{i=1}^n\left(x_i-\bar{x}\right) \sum_{i=1}^n\left(y_i-\bar{y}\right)}{\sqrt{\sum_{i=1}^n\left(x_i-\bar{x}\right)^2} \sum_{i=1}^n\left(y_i-\bar{y}\right)^2}$           (1)

5.3.5 Significance filtering with Wilcoxon Rank-Sum Test (WRST)

Rather than dropping all highly-correlated items, we introduce a significance test to preserve those that are still informative. We apply the Wilcoxon Rank-Sum Test (WRST)– a non-parametric test on paired samples – to assess each feature’s importance (contribution to the target outcome). WRST evaluates whether the distribution of a feature’s values significantly differs with respect to the target class labels (in our case, sentiment classes). In practice, we compute a signed rank for each feature and derive a test statistic (T) to measure significance (Eq. (2)). Features yielding high WRST scores are deemed significant predictors for the analytics task (e.g., sentiment classification), whereas low-score features are considered insignificant. By doing this, even if a feature is highly correlated (duplicated) with others, it will not be removed if it carries unique predictive significance. This two-tiered strategy (correlation + WRST) balances Volume/Velocity optimization with Veracity: we eliminate truly redundant data but retain duplicates that are valuable for accuracy.

5.3.6 Correlative Multi-Parametric Duplication Removal algorithm

The pseudocode in Algorithm 1 summarizes how correlation and WRST are combined in CMDR to determine the final set of features to keep. Eq. (2) (referenced in the pseudocode) formalizes the rule for retaining data elements that pass either the homogeneity check or the significance check (or both). Essentially, a data item is retained if it is not highly correlated, or if it is correlated but also statistically significant according to WRST.

By applying CMDR on the embedded data, the transformation phase yields an optimal feature set – a reduced dataset containing only non-duplicate, significant features ready for loading. This greatly shrinks the data size (fewer features and samples) and speeds up downstream analytics, addressing the Volume and Velocity challenges. Notably, because CMDR uses WRST to guard against dropping important data, the Veracity (accuracy potential) of the data is preserved. In effect, STCM-ETL’s transformation ensures the data warehouse only stores meaningful and non-redundant information from the heterogeneous inputs.

5.4 Load phase

After duplication removal, the loading phase stores the curated data into a target repository (data warehouse). We implement an incremental loading mechanism (Algorithm 2) to update the warehouse with each incoming batch of processed data. Initially, if the warehouse is empty, it simply loads the first batch. For subsequent batches, the new retained data (output of CMDR) is concatenated with the existing warehouse data. This procedure ensures that as new data streams in, only the new unique significant features are added to the warehouse, preventing any re-loading of duplicates. In our implementation, we used a serialized binary format (Python pickle file) as a stand-in for the warehouse for simplicity. The incremental loading strategy supports Velocity, allowing real-time or periodic updates to the stored data without re-processing the entire dataset each time.

5.4.1 Ensuring veracity with data optimization

While the above steps address Volume, Variety, and Velocity, we include an additional computing optimization module to bolster Veracity (data quality and accuracy for analysis). The concern is that even after CMDR, the data might suffer from issues like class imbalance or scale disparities, which could hurt analytics reliability. To mitigate this, the loaded data is passed through a resampling and normalization process before analysis. We apply an integrated SMOTE-ENN resampling technique to balance the class distribution: SMOTE generates new samples for minority classes, and ENN cleaning removes any ambiguous synthetic points. This yields a more balanced dataset, improving the fairness and representativeness of the data for model training. We then perform Min-Max normalization to scale all features to [0,1] range, reducing the effect of differing value scales (Eq. (3)). Finally, to validate that our ETL output indeed maintains high fidelity for decision-making (veracity), we test it with downstream machine learning classification. In our study, we feed the processed data into various classifiers (Naïve Bayes, SVM, Decision Tree, k-NN, Logistic Regression, etc.) to predict the sentiment labels. The classification accuracy and F-measure serve as indicators of data quality – if our ETL preserves veracity, the models should achieve strong predictive performance. This computing optimization stage is essentially a plug-in analytic layer on top of STCM-ETL: it does not alter the ETL process itself, but it confirms that the ETL’s output supports accurate analytics and thereby meets the Veracity criterion of big data. By incorporating this step, the overall framework not only produces smaller and faster-to-process data, but also verifies that the reduced data is still informative and trustworthy for end-use.

The proposed STCM-ETL framework innovatively combines rule-based multi-source extraction, semantic embedding, and the CMDR (correlation + WRST) mechanism to optimize the ETL pipeline for big data. These contributions directly address the challenges of Variety (handling heterogeneous formats), Volume (dimensionality reduction and duplicate elimination), Velocity (faster processing via data reduction and incremental loading), and Veracity (ensuring the retained data maintains predictive quality). By the end of the process, the data warehouse contains a significantly smaller yet information-rich dataset, which can be analyzed more efficiently without sacrificing accuracy.

5.4.2 Experimental setup: dataset

We evaluated the STCM-ETL framework using a benchmark social media sentiment dataset. This dataset consists of approximately 36,000 text reviews (sentences), each labeled with a sentiment (Positive/Negative). After initial feature extraction, each review is represented by a 300-dimensional feature vector (reflecting the Word2Vec embedding size). The corpus was chosen to mimic real-world big data characteristics: it is large-scale (tens of thousands of instances, addressing Volume), contains unstructured text from social platforms (Variety), and can be fed into the ETL system in successive chunks to simulate streaming (Velocity). The source of the data is a public sentiment analysis benchmark (social media reviews), ensuring that results are relevant to practical big data analytics scenarios. We also constructed 15 sequential input files from this corpus (5 each in CSV, JSON, and XML format) to serve as the multi-source input for our ETL pipeline. Each file contains a subset of the reviews, and these were ingested one after the other by STCM-ETL, emulating an incremental data arrival process. This setup tests the framework’s ability to handle heterogeneity and continuous data flow, while ultimately preserving the dataset’s ground truth labels for evaluating veracity.

5.4.3 Hardware and software configuration

All experiments were conducted in a Python-based environment on a standard computing platform. Below is a summary of the system configuration:

• Hardware: PC with an octa-core Intel processor (Core i7 or equivalent) and 16 GB RAM. No specialized GPU or cluster computing was required, as the focus is on the efficiency of the ETL process itself.
• Software: Windows 10 OS (64-bit) with Python 3.x. Key libraries used include pandas for data handling, xml.etree and json libraries for parsing XML/JSON, NLTK for text preprocessing (stop-word removal, etc.), and Gensim (with Google’s pre-trained Word2Vec model) for semantic embedding. The SciPy/NumPy stack was used for correlation calculations and implementing the WRST. For the veracity evaluation stage, we used the scikit-learn library (version 0.24) for SMOTE-ENN resampling (via the imbalanced-learn extension) and various classifier algorithms (Naïve Bayes, SVM, Decision Tree, k-NN, Logistic Regression, etc.).

This configuration ensures that the implementation is reproducible and that performance metrics (memory usage, execution time) are measured in a realistic computing setup.

5.4.4 Baseline Extract-Transform-Load (ETL) models for comparison

To validate the contributions of each component in STCM-ETL, we compared our framework against two baseline ETL strategies:

Baseline 1 – traditional rule-based ETL: This baseline performs conventional ETL operations without any semantic embedding or advanced duplicate removal. It involves extracting from the multiple sources and applying the same basic pre-processing (punctuation removal, stop-words filtering, case normalization, tokenization), but then directly loading all data to the warehouse as-is. In other words, it relies on simple rule-based parsing and cleansing, without reducing feature dimensionality or eliminating duplicates. This represents a typical “syntactic” ETL pipeline that might be used in practice, and serves as a control to measure improvements in Volume and Velocity made by our semantic and CMDR enhancements.

Baseline 2 – semantic ETL without duplication removal: This variant introduces semantic transformation (Word2Vec embedding) but omits the CMDR step. After tokenization, data is converted into 300-dimensional word embedding vectors (just as in STCM-ETL), and all features are then loaded to the warehouse without performing correlation-based filtering. This approach benefits from data compression via embeddings (addressing Volume to some extent), but it does not remove redundant data. We use this baseline to isolate the impact of the CMDR module – by comparing it with STCM-ETL, we can quantify how much duplicate removal further improves storage efficiency and processing speed, as well as any effect on analytics accuracy.

Each ETL model (baselines and the proposed STCM-ETL) is run on the same sequential input dataset for a fair comparison. We track key performance metrics: memory usage (to store the output data) and number of features/records retained after ETL (indicating Volume reduction), ETL processing time per batch (indicating Velocity improvement), and the accuracy/F-measure of a downstream classifier trained on the ETL output (indicating Veracity of the retained data). By analyzing these metrics, the experimental setup directly evaluates how the novel components of STCM-ETL contribute to performance under each of the 4Vs conditions. For instance, improvements in memory and feature count over Baseline 1 highlight the effect of embedding and duplicate removal on Volume; faster loading times highlight Velocity gains; the ability to handle all three file formats demonstrates Variety handling; and maintaining high accuracy and F-measure in sentiment classification confirms that Veracity is not compromised (and is in fact enhanced by the veracity-optimization step).

Overall, this experimental design validates the proposed model by comparing it with simpler ETL pipelines and showing that each added component (semantic transformation and CMDR) yields measurable benefits. The chosen dataset and performance measures align with the 4Vs objectives, allowing us to rigorously assess the STCM-ETL framework’s efficacy in a realistic big data context.