The data warehouse
The most common use: data from sales, accounting and HR systems gathered in one warehouse, in one format, for analysts.
Data platform
What ETL means, how the process runs step by step, and how to tell whether your project needs ETL or continuous synchronisation.
Explainer Published 5 min read
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.
| Step | What happens | Example |
|---|---|---|
| Extract | Data is read from the source systems | At 1 a.m., the previous day's sales are read from the point-of-sale system of every branch |
| Transform | Data is cleaned, unified, joined and calculated | Branch and product codes are mapped to one list, returns are deducted, and totals per branch and per product are calculated |
| Load | The results are written to the target | The totals are added to the sales tables of the data warehouse, and the daily report reads them at 7 a.m. |
Extract
from the source systems: databases, exported files, application interfaces
Transform
Load
into the target: a data warehouse or a reporting database
Each run passes through the three stations in the same order.
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.
Relatedchange data capture guide
Transformation is the heart of the process, and it is where most errors happen. It covers four kinds of work:
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.
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.
The most common use: data from sales, accounting and HR systems gathered in one warehouse, in one format, for analysts.
Monthly and quarterly reports built from several systems, checked and unified first.
When one company joins another, ETL maps customer and product codes between the two systems so that one report covers both.
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.
Three questions settle the choice in most projects:
When the goal is to move a whole system, ETL vs CDC explains the difference in detail.
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.
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.
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.
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.
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.
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.
Relatediblync
Next
Next step
Tell us where your data comes from and where it must arrive, and ask one question.
What happens next
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