Changing Data Integration Architecture: ETL vs. ELT

Data Integration / ETL / ELT / Data Warehouse

Why Enterprises are Rethinking Traditional Integration Processes

Corporate information systems accumulate ever-increasing volumes of data. ERP systems record financial and business operations, CRM systems store customer interaction history, electronic document management systems process contracts, and web services generate significant arrays of technical events.

Corporate data warehouses (DWH) are used to form a unified information picture of the enterprise.

Traditionally, loading information into DWH is organized through ETL: Extract, Transform, Load. Data is extracted from initial sources, transformed according to corporate rules, and only then loaded into the target warehouse.

This approach has been widely used for many years. Industrial platforms such as Informatica PowerCenter, IBM InfoSphere DataStage, Microsoft SQL Server Integration Services, and other tools allow for complex data transformations.

However, the development of analytical DBMS is gradually changing the conditions for performing these tasks.

Modern corporate warehouses utilize parallel processing, columnar storage, and SQL query optimizers. Cloud platforms allow access to significant computing resources without building in-house server infrastructure.

As a result, the question arises: is it necessary to perform all transformations before loading information into the DWH, if the warehouse itself is capable of efficiently processing large datasets?

An alternative is ELT — Extract, Load, Transform.

This approach assumes that information first arrives at the target environment and is then transformed using its computational capabilities.

However, the choice between ETL and ELT should not be determined solely by performance. Data quality, access control, computational reproducibility, and accountability for corporate information resources remain important.

Traditional ETL: Transform Before Loading

In the classic ETL model, the main work of preparing information is performed between the source systems and the target repository.

• ERP · CRM · DMS · Files
• Source Systems
• ETL Platform
• Extract → Transform → Load
• Cleansing, MDM, Mapping, Aggregation
• Corporate DWH
• Prepared information for reporting

Simplified traditional ETL architecture. Some operations can be performed directly in the source or target DBMS.

Extract

In the first stage, the system receives the necessary data.

For example, an ETL task reads new invoices from ERP daily, updated customer records from CRM, and contract information from EDM.

It is important to correctly define the change detection mechanism. If only the document creation date is used, later adjustments may go unnoticed.

Therefore, update timestamps, change logs, or other controlled mechanisms are needed.

Transform

In the second stage, data is transformed into the corporate model. For example, product codes from multiple ERPs are mapped to a single MDM reference, amounts are converted to the reporting currency, and incorrect records are sent for verification.

A significant amount of computation may be performed at this stage.

Load

After transformation, the results are loaded into the DWH. For example, information is written to fact and dimension tables.

The advantage of this approach is that the target data warehouse receives already prepared information.

However, complex transformations can create a significant load on individual ETL servers.

ELT: Leveraging Warehouse Computational Power

In the ELT model, the order of operations is changed.

The company initially transfers the initial data to the appropriate DWH zone or another analytical environment.

After that, it uses SQL, distributed computing, or other mechanisms of the target platform to prepare the information.

• ERP · CRM · ECM · Files
• Extract → Load
• Loading into the staging zone
• Transform within the platform
• SQL · Parallel queries · Analytical computations
• Prepared DWH / BI tables

The ELT architecture moves the main transformations closer to the storage environment. Security and quality control remain necessary. For example, sales information from three ERPs can be loaded into the staging tables of the corporate warehouse.

After this, SQL procedures perform cleaning, reference matching, and analytical indicator calculation.

Such a solution can be effective if the warehouse has sufficient resources for parallel operation execution.

Advantages of ELT

Firstly, the need to transfer large intermediate sets between separate computational components is reduced. Secondly, the optimizer and parallel capabilities of the target DBMS can be used.

Thirdly, initial data can be stored for re-calculating results. Fourthly, individual transformations can be implemented through standard SQL, which simplifies their analysis for specialists with relevant competencies.

Limitations of ELT

However, ELT does not guarantee automatic performance improvement. If the target DBMS has insufficient resources, heavy SQL transformations can interfere with regular analytical queries.

Unprocessed personal or confidential data may also enter the staging zone.

Therefore, it is necessary to properly organize access, retention periods, and control the origin of information.

Comparison of ETL and ELT

In real corporate architectures, both approaches are often combined. For example, critical personal data may undergo minimization even before transfer, while large analytical aggregations will be performed directly in the DWH.

Therefore, it is more appropriate to evaluate the place of each specific transformation, rather than choosing one method for the entire organization.

Why Cloud DWHs Facilitate the Spread of ELT

Analytical cloud platforms have become an important factor in the development of ELT.

Amazon Redshift

Amazon Redshift became generally available in February 2013.

The platform uses columnar storage and parallel query execution across multiple cluster nodes.

To load large datasets, Amazon S3 and the COPY command can be used. For example, after migrating CSV files to S3, an enterprise can load them into a Redshift staging table.

Further transformations will be performed using SQL.

However, it is necessary to correctly define table structures and data distribution across nodes.

Inefficient joins of large tables can cause significant inter-node traffic.

Google BigQuery

In June 2016, Google introduced Standard SQL in BigQuery in beta, and on September 29, announced its general availability.

This expanded the capabilities for using familiar SQL constructs for analytical work with large datasets. For ELT, BigQuery is interesting because a significant portion of transformations can be performed via SQL without deploying your own compute cluster.

However, the nature of queries and the payment model must be evaluated.

Unlimited rescanning of large datasets can incur significant costs.

Azure SQL Data Warehouse

Microsoft Azure SQL Data Warehouse became generally available in July 2016.

The platform uses distributed SQL computations and allows you to change computing power according to the load.

For enterprise projects, this creates an opportunity to perform transformations closer to the stored analytical data.

However, Azure SQL Data Warehouse should not be confused with regular Azure SQL Database: they have different purposes and architectural characteristics.

ELT in practice: SQL transformations in the warehouse

Let’s consider a conditional example of sales consolidation.

The enterprise has two ERPs that use different local product codes.

After loading information into the staging area, the following tables are formed:

stg_sales_erp_a
stg_sales_erp_b
mdm_product_map
dwh_fact_sales

To prepare a unified model, you can use SQL:

INSERT INTO dwh_fact_sales
(source_system, document_id, product_key, quantity, net_amount)
SELECT
s.source_system,
s.document_id,
m.product_key,
s.quantity,
s.net_amount
FROM stg_sales AS s
INNER JOIN mdm_product_map AS m
ON m.source_system = s.source_system
AND m.source_product_id = s.product_id;

This example demonstrates the basic idea of ELT: loaded data is transformed within the analytical environment. However, for an industrial process, this query is insufficient.

It is necessary to check if all product codes have matches, if there are no duplicate keys, and if reprocessing of loads is handled correctly.

A particularly dangerous situation arises when INNER JOIN silently excludes records without a match in MDM.

For example, the initial dataset contained 100 thousand rows, but after the join, only 98 thousand remained.

The SQL query might complete successfully, but two thousand records will not be included in the analytical table.

Therefore, the results must be reconciled with the initial data.

Data Quality in ELT Architecture

A common misconception is that ELT supposedly allows for the elimination of data cleaning. In reality, only the execution location of some procedures changes.

Before the information becomes available for official reporting, it must undergo defined checks.

Completeness Check

It is necessary to compare the number of received and loaded records.

If part of the information is rejected, the reasons must be documented.

Reference Data Control

Customer and product identifiers must be reconciled with corporate MDM registries.

Ambiguous records are passed for appropriate verification.

Financial Indicators Control

For critical datasets, reconciliation of summary totals must be performed.

For example, the total sum of operations for the day must match the ERP control indicator, taking into account approved transformations.

Publishing Only Verified Data

A separate principle is the separation of the staging area and datasets available for regular business analytics.

An analyst should not mistakenly use the initial table as the approved source of corporate revenue.

Therefore, it is necessary to use schemas, access rights, clear naming conventions, and documentation.

Data Lineage and Transformation Reproducibility

ELT can simplify working with large datasets, but at the same time, it creates a need to control SQL transformations. For example, an enterprise changes the rules for calculating net revenue.

If the initial data is stored in a staging area, the corresponding period can be reprocessed. However, it is necessary to know which version of the rule was used previously.

For this purpose, it is advisable to store SQL scripts in a version control system and link them to ETL/ELT logs. Data Lineage should show dependencies between initial tables, the staging area, transformations, and final analytical objects.

For example, a change in the structure of the customer_id field can affect several SQL procedures and reports.

Without documented dependencies, an enterprise risks discovering a problem only after an incorrect metric appears.

Apache Spark and ELT for Large Datasets

ELT does not necessarily have to be performed solely within a traditional relational DBMS. For large datasets, distributed computing platforms can be used.

As of April 2017, Apache Spark 2.1.0 is available, released on December 28, 2016. Version 2.1.1 will be released only on May 2, 2017, so it should not be included in the April architecture.

Spark SQL and the DataFrame API allow reading datasets from HDFS, Parquet, and other supported sources, performing transformations, and writing the prepared results. For example, an enterprise might accumulate raw web logs in a Data Lake and then use Spark for normalization and aggregation.

However, using Spark does not automatically provide the transactional guarantees of a traditional enterprise RDBMS.

For critical reporting, integrity and retry rules must be defined separately.

Practical Scenario: Modernizing a Retail Company’s DWH

Let’s consider a hypothetical company that has four regional ERPs and a centralized data warehouse.

A traditional ETL platform performs a significant number of complex transformations on a separate server.

As sales volume increases, the nightly load begins to exceed the acceptable window.

Stage 1. Load Analysis

The enterprise measures the execution time of each operation.

It is found that the most resources are consumed by aggregating and joining large tables.

Stage 2. Transformation Definition

The command separates operations requiring specialized cleanup logic from regular SQL computations.

Stage 3. Staging Creation

Initial records arrive in the secure DWH staging zone.

The source, time, and number of records are logged for each load.

Stage 4. SQL Transformations

Large aggregations are moved to the analytical DBMS’s compute resources.

Stage 5. Quality Control

After transformations, reconciliations of product codes, financial totals, and operation counts are performed.

Stage 6. Performance Testing

The full cycle time is compared before and after modernization.

At the same time, the impact of ELT on standard analytical queries is being evaluated.

If transformations significantly overload the DWH during business hours, the execution schedule or architecture needs to be reviewed.

Organizational and Economic Implications

The transition to ELT can change the distribution of work among specialists.

ETL developers increasingly interact with DBAs, analysts, and data architects. The role of SQL optimization and understanding the mechanisms of the target analytical platform is growing.

For cloud DWHs, it is important to control the cost of computation and storage. For example, moving transformations to BigQuery can simplify the operation of on-premises servers but increase costs for executing inefficient queries.

Therefore, the economic assessment should include the full cycle: data acquisition, loading, transformations, reprocessing, and storage.

Protection of initial data is equally important. ELT often involves loading information into an analytical environment before full cleansing.

For personal and confidential datasets, legal grounds, minimization rules, and access control must be defined in advance.

Intecracy Group Competencies in ETL and ELT Development

The choice of integration architecture requires a combination of information, application, and infrastructure competencies. IQusion IT operates in the fields of system integration, BPM, and electronic document management. Relevant competencies are important for connecting operational systems and organizing business processes where information originates.

Softline IT specializes in server and network infrastructure and storage systems. These areas are significant for the performance of local DWH and computing environments. Moreover, the correctness of business data processing rules must be approved by the responsible departments of the enterprise.

Practical Guidelines for Implementation

ELT is becoming an important alternative to traditional ETL due to the development of high-performance analytical DBMS and cloud platforms. Moving part of the transformations to the target environment allows utilizing its SQL optimizers and parallel computing resources.

This approach can be particularly promising for large analytical datasets that require complex grouping and table joins. However, ELT does not eliminate the need for Data Quality, MDM, and information flow control.

For critical enterprise systems, it is important to maintain the reproducibility of transformations, verify financial summaries, and protect initial data.

The most practical solution often becomes a hybrid architecture where some checks are performed before loading, and resource-intensive analytical transformations are done directly in the data warehouse.

The main principle is not to choose ETL or ELT as a universal technology, but to place each information transformation where it can be performed most efficiently, securely, and controllably.