ETL (Extract, Transform, Load)

What does ETL (Extract, Transform, Load) mean?

The term ETL describes a workflow that originally comes from data warehousing and is now used wherever data has to be transferred from one system into another. The three letters stand for the three consecutive phases of the process: Extract, Transform and Load. Each phase has a clearly defined task, and only their interaction turns raw data into a usable data set.

The order matters: with classic ETL, the data is converted and checked before it reaches the target system. The target system, an ERP for instance, therefore only receives data that already has the agreed structure and quality. This is what distinguishes ETL from a simple copy or a pure data import-export: between pulling from the source and loading into the target lies a deliberate preparation stage in which errors are corrected and formats are unified.

The three phases of ETL in detail

Each of the three phases fulfils its own function. Together they form a chain whose result is only as good as its weakest link, and in practice it is above all the middle phase that determines the success or failure of a migration or integration project.

ETL in the ERP system: migration and data exchange

The most common point of contact between ETL and the ERP is data migration during an ERP rollout. Anyone switching from a legacy system to a new ERP has to carry over master data such as items, customers, suppliers and accounts as well as selected transaction data such as open items and stock levels. The data models of the two systems rarely match one to one, and the legacy data is often polluted over the years. An ETL process extracts the legacy data, cleans it and maps it onto the structure of the new system and loads it in a controlled way, typically with prior test runs into a test client.

Besides the one-off migration, ETL is also used for ongoing operations. In this way, order and revenue data from several channels can be consolidated nightly, posting data can be prepared for financial accounting, or key figures can be aggregated for reporting and BI. In many mid-sized landscapes, ETL runs in the background as a scheduled batch job that processes data in fixed time windows instead of synchronizing it immediately.

Distinction: ETL, ELT, middleware and iPaaS

ETL is often confused with related concepts. The most important distinction concerns ELT (Extract, Load, Transform): here the raw data is first loaded into the target system and only converted there. ELT uses the processing power of modern cloud databases and is suited to very large data volumes, while classic ETL cleans before loading and thereby ensures a "clean" target system. For ERP migrations in mid-sized businesses, ETL is usually the more appropriate route, because the ERP should not take in faulty raw data.

A distinction must also be drawn from middleware or an iPaaS (Integration Platform as a Service). These usually connect systems in an event-driven way and almost in real time via APIs, for example when an order in the shop moves into the ERP immediately. ETL, by contrast, is classically batch-oriented and designed for the bundled processing of large data volumes. In practice the boundaries overlap: many iPaaS platforms offer ETL functions, and modern integration projects combine real-time interfaces for ongoing operations with ETL runs for mass data and analysis.

Why ETL is decisive for data quality

The real value of ETL lies less in transporting the data than in preparing it. Without the Transform phase, errors, duplicates and format breaks would pass unfiltered into the target system, with the consequence that a new ERP works with poor data from day one. A well-thought-out ETL process is therefore the prerequisite for a clean golden record, for reliable analyses and for smooth processes from purchasing to accounting.

For DACH projects there is an added legal aspect: if personal data, such as customer master records, is processed in the course of an ETL process, the GDPR requirements on data minimization and purpose limitation apply. Likewise, for accounting-relevant data the requirements for traceability and immutability must be kept in mind so that the migration does not jeopardize GoBD compliance. A cleanly documented ETL run with logged rules and check steps is valuable here from a compliance perspective too.