Quick answer
ETL stands for extract, transform, load: a process that copies data out of source systems, cleans and reshapes it, and loads the result into a destination such as a data warehouse. An ETL pipeline is the automated set of jobs that does this repeatedly.
Formula: Extracted rows = loaded rows + rejected rows + duplicate rows
What is ETL?
ETL stands for extract, transform, load: a process that copies data out of source systems, cleans and reshapes it, and loads the result into a destination, most often a data warehouse. The transformation happens before the data lands, in a staging area, so the destination only ever receives data that already fits its structure.
ETL is one way to do data integration, and its transform step is where data preparation happens. An ETL pipeline is the automated, ordered set of jobs that does this repeatedly. Its mirror image, ELT, loads the raw data first and transforms it inside the destination. This page defines the term; the comparison between the two, with a worked example, lives in ETL vs ELT.
The three steps
| Step | What happens | Typical rules | What goes wrong here |
|---|---|---|---|
| Extract | Raw data is copied from sources (databases, CRM and ERP systems, APIs, files, web pages) into a staging area | Pull everything first, then only what changed since the last run | The source is slow or down; a column is renamed |
| Transform | Staged data is cleaned and reshaped to fit the target | Remove duplicates, drop or fix invalid rows, convert units, currencies and date formats, mask regulated fields, join and aggregate | A rule silently discards rows; time zones are mixed up |
| Load | The result moves into the target | Load a full set first, then incremental changes, with an occasional full refresh | A load fails halfway; a re-run loads the same rows twice |
AWS and IBM describe the same three steps. The common core is that nothing reaches the destination until it has passed the transform step.
ETL pipeline vs data pipeline
The two terms are often used as if they were the same. Informatica separates them on three points:
| ETL pipeline | Data pipeline | |
|---|---|---|
| Scope | A subset of data pipelines; it ends when the data is loaded | The broader category; it can go on to trigger other systems |
| Transformation | Always has a transform step | Moves data between systems, with or without transforming it |
| Timing | Usually runs in batches | Can run in real time |
A job that copies a table from one database to another with no changes is a data pipeline but not an ETL pipeline.
Batch and streaming ETL
Traditional ETL is batch-oriented: data is collected, transformed and loaded at set times, often in a quiet "batch window". Streaming ETL transforms events continuously as they arrive, usually on a streaming platform such as Apache Kafka, with low delay. Both are ETL, and what separates them is how old the data in the destination may be.
Sample data: a batch run takes 40 minutes. The worst case is an event that happens just after an extract ends, which then waits a whole cycle plus the run.
| Schedule | Worst-case age of a new event in the warehouse |
|---|---|
| Daily at 02:00 | 24 h + 40 min = 24 h 40 min |
| Hourly, 10-minute run | 1 h + 10 min = 1 h 10 min |
Pick the schedule from the question the data has to answer. A weekly finance report does not need an hourly run; a data latency target of minutes does, or a streaming design.
A worked example: reconciling one load
Sample data: a nightly job extracts 10,000 order rows from a shop database.
| Result of the transform | Rows |
|---|---|
| Rejected: missing order ID | 150 |
| Dropped: duplicate order ID | 220 |
| Rejected: date could not be parsed | 60 |
| Loaded | 10,000 − 150 − 220 − 60 = 9,570 |
The check is a simple identity: extracted = loaded + rejected + duplicates, here 10,000 = 9,570 + 430. Rejected and dropped rows are 430 ÷ 10,000 = 4.3%. If tomorrow's run shows 15%, a source system has probably changed, even though the job reported success.
Now re-run the same file. If the load only appends, the table holds 9,570 × 2 = 19,140 rows. If the load writes by order ID (an upsert), it still holds 9,570. A pipeline that can be safely re-run is one that identifies each row, which is the single most useful property when a job fails halfway.
Why ETL pipelines break
| Cause | Symptom | Check |
|---|---|---|
| The source changes its structure (a column is renamed or its type changes) | Nulls appear in a field, or the transform step fails | Compare column names and types at every extract |
| Late-arriving data | Yesterday's totals change today | Re-extract a trailing window, such as the last few days, instead of only "since last run" |
| A re-run appends the same rows | Counts and sums double | Load by a unique key; compare row counts |
| A load fails partway | Some tables have the new day, some do not | Load into a new table, then swap, or make the load restartable |
| Mixed time zones | Daily totals shift around midnight | Convert to one zone in the transform step and keep it everywhere |
ETL or ELT?
The shortest rule is about history. If you may need to recompute past results under new rules, keep the raw data, which is the ELT side. If data has to be cleaned or have personal fields removed before it may enter the destination at all, transform first, which is ETL. Most real pipelines mix the two. The full argument, with an example of a changed metric definition, is in ETL vs ELT.
Common mistakes
- Treating "the job succeeded" as "the data is right". Reconcile row counts on every load.
- Letting transform rules drop rows silently. Count and log every rejected row with its reason.
- Building a pipeline that cannot be re-run. Every load should be safe to repeat.
- Calling every data movement ETL. A plain copy with no transform is a data pipeline, and that matters when you compare tools.
- Reading a batch schedule as real time. A dashboard fed by a daily run is up to 24 hours old.
ETL and TapCub
TapCub is not an ETL tool. It does not extract from your databases or run transformation jobs for you. It sits at either end of your pipeline: as a place to load data into, and as a place to take raw data out of. Suppose you have order history in an old system and you want it next to your live analytics.
- Do the extract and transform yourself. Export the rows from the old system to a UTF-8 CSV, fixing formats and dropping duplicates first, as in the example above.
- Load in the importer. Open All-platform analytics › Data › Data import. It accepts CSV events, CSV user properties and Nginx or Apache logs, up to 100,000 rows per file; split larger files. The wizard maps your columns to TapCub fields, and it does a small transform on the way in: it reads timestamps with 10 or 13 digits and ISO and common date formats, converts numbers and
trueorfalsein mapped property columns to those types, and, in user-property files, hashes email and phone before binding them as identities. - Reconcile the result. The job list shows rows, imported and failed for each file, which is the identity from the example. For logs, skipped lines such as static assets and crawlers also count as failed, so a high number is not necessarily an error.
- Do not load the same file twice. The importer does not dedupe: importing the same file twice gives two copies of the data.
- For continuous loads, send events from your server. The
/api/v1/trackendpoint takes up to 1,000 events per request and deduplicates on theinsert_idyou give each event, so a retried batch is safe. Imports are for one-off history, not an ongoing channel. - To take raw data out, create an asynchronous job in All-platform analytics › Data › Data export. It exports raw rows (events, sessions, people), not a screenshot of a report, and rows older than your retention period are already gone.
See also: What is data processing? Steps, types and a worked example.
See also: What Is Reverse ETL? How It Works, Examples and Pitfalls.
Sources
- AWS, What is ETL?
- IBM, What is ETL (extract, transform, load)?
- Informatica, What is an ETL pipeline?
Was this explanation clear?
We use your answers to fix and expand this page.
Couldn’t send. Please try again in a minute.
Thanks for the feedback. We’ll use it to improve this page.You’ve already sent feedback on this page. Thanks.

Data Engineer
Writes about event design, data quality, and collection workflows, with verifiable implementation methods for reliable analytics.