Data platform

What is ETL? Extract, transform, load, explained

What ETL means, how the process runs step by step, and how to tell whether your project needs ETL or continuous synchronisation.

  • Saudi technology company, in Riyadh since 1999 Interkey, Riyadh
  • ISO 9001:2015 and ISO/IEC 27001:2022 Quality and information security management
  • Implements and operates, after go-live too The same team before and after the switch

ETL stands for extract, transform, load. It is a process that takes data out of source systems, reshapes it into an agreed format, and loads it into a target such as a data warehouse, usually on a schedule. ETL suits reporting and analytics. When two operational systems must stay identical minute by minute, continuous synchronisation is the better fit.

The ETL process in one table

The three steps of ETL, with one example: the sales of every branch gathered into a daily report
StepWhat happensExample
ExtractData is read from the source systemsAt 1 a.m., the previous day's sales are read from the point-of-sale system of every branch
TransformData is cleaned, unified, joined and calculatedBranch and product codes are mapped to one list, returns are deducted, and totals per branch and per product are calculated
LoadThe results are written to the targetThe totals are added to the sales tables of the data warehouse, and the daily report reads them at 7 a.m.

Extract, transform, load

  1. Extract

    from the source systems: databases, exported files, application interfaces

  2. Transform

    clean unify join calculate
  3. Load

    into the target: a data warehouse or a reporting database

Each run passes through the three stations in the same order.

Extract

Extraction reads data from where it lives: the databases of operational applications, files that systems export, and application programming interfaces (APIs). It works in one of two ways. A full extract reads all the data on every run; it is simple, but it grows heavy as tables grow. An incremental extract reads only what changed since the last run, using a column that records the last update time or a sequence number, or the database's own change log, which our change data capture guide explains.

Because it reads from production systems, its load on the source is planned: it runs outside working hours, reads from a replica, or is limited in speed. It only reads; it changes nothing in the source.

Transform

Transformation is the heart of the process, and it is where most errors happen. It covers four kinds of work:

  • Cleaning: removing duplicate records, handling empty values, and correcting the format of dates and mobile numbers.
  • Unifying: one code per branch, one unit of measure, and one calendar where systems mix Hijri and Gregorian dates.
  • Joining: matching sales to the product list and the branch list, so each figure is read with what it belongs to.
  • Calculating: totals, averages, margins and derived columns.
Read more

Errors usually come from the mapping rules: a new branch missing from the reference list, or a code changed in the source but not in the rules. So each run counts records in and out, keeps rejected records in a separate table with the reason, and the data owners approve the written rules.

Load

Loading writes the results into the target, usually a data warehouse or a reporting database. A full load replaces the contents of a table on every run and suits small tables. An incremental load adds new records and updates changed ones by a stable key, so a run repeated after a failure does not create duplicates.

The job runs on a schedule, usually nightly, or hourly if the report needs it. Reference tables such as branches and products are loaded before the sales tables that depend on them.

Where ETL is used

01

The data warehouse

The most common use: data from sales, accounting and HR systems gathered in one warehouse, in one format, for analysts.

02

Periodic reports for management and auditors

Monthly and quarterly reports built from several systems, checked and unified first.

03

Combining data after an acquisition

When one company joins another, ETL maps customer and product codes between the two systems so that one report covers both.

04

A one-off migration

ETL can move data once from an old system to a new one, if the old one can stop for the move. If it cannot, read ETL vs CDC.

ETL or continuous synchronisation

Three questions settle the choice in most projects:

  • Is the target for analysis or for operations? A warehouse that analysts read is a job for ETL. A system that staff and customers use needs data that matches the source at every moment.
  • Is a daily update enough? The morning report is served by a nightly run. If two systems must stay identical minute by minute, the need is continuous synchronisation.
  • Does the shape of the data change? Data that is aggregated and reshaped is transformed for analysis. Data that moves with its columns and values unchanged is synchronised or migrated.

When the goal is to move a whole system, ETL vs CDC explains the difference in detail.

Questions about ETL

What does ETL stand for?

ETL stands for extract, transform, load. The three words name the three steps in order: data is extracted from source systems, transformed into an agreed format, and loaded into a target such as a data warehouse. The term names a process, not a product; it runs as scripts or on ETL tools.

What is an ETL process?

An ETL process is a repeatable job that takes data out of source systems, reshapes it into an agreed format and loads it into a target, usually on a schedule. It mostly feeds reporting and analytics, and each run follows the same order, so the report built on it can be trusted day after day.

What is an example of ETL?

A retailer with many branches is a common example. Each night, the sales of every branch are extracted from its point-of-sale system, branch and product codes are unified, returns are deducted, and the totals are loaded into a data warehouse. By morning, management reads one daily report that covers every branch.

Is ETL still used today?

Yes. ETL remains common for reporting and analytics, wherever data must be checked and reshaped before anyone reads it. A related approach, ELT, loads the raw data into the target first and transforms it there, using the processing power of the data warehouse. Many teams use both and choose for each data set.

What is the difference between ETL and data migration?

ETL is a recurring process that feeds analysis; data migration moves a system's data to another system once, or in phases, before the old one is switched off. ETL can carry a migration if the old system can stop for the move. If it cannot, the move needs continuous sync: ETL vs CDC explains the choice.

Is iblync used for ETL?

No. iblync is Interkey's platform for migration and synchronisation between operational systems; it is not built to transform data for analytics. If your project loads a data warehouse for reporting, an ETL tool does that job. If two operational systems must stay identical, or a system is moving to a new database, iblync is built for that.

Next step

Ask an engineer one question

Tell us where your data comes from and where it must arrive, and ask one question.

Required field

By sending this form, you agree to our Privacy Notice.

What happens next

  1. We read your request
  2. An engineer contacts you
  3. You get an estimate or a demo

Or directly

+966-11-2180999 [email protected]

Tawuniya Towers, North Tower, 7th Floor, King Fahad Highway, Olaya, P.O. Box 56835, Riyadh 11564, Saudi Arabia

or ETL vs CDC: how to choose

Published by Interkey. Last updated . Interkey is registered in Riyadh, Saudi Arabia under commercial registration 1010156897.