What Is ETL
The acronym is a reasonable description of the sequence, though real pipelines are messier than three clean steps suggest. Each stage has its own characteristic failure modes, and understanding which stage a problem belongs to speeds diagnosis considerably. Transformation is where most complexity accumulates, because it encodes business rules that change over time.
The acronym is a reasonable description of the sequence, though real pipelines are messier than three clean steps suggest. Each stage has its own characteristic failure modes, and understanding which stage a problem belongs to speeds diagnosis considerably. Transformation is where most complexity accumulates, because it encodes business rules that change over time.
Reading data from source systems, whether databases, APIs, files, or event streams. Extraction must avoid disrupting operational systems, which usually means reading replicas or scheduled windows.
Cleaning, standardising, joining, deduplicating, and reshaping. This is where business logic lives, including how conflicting definitions between source systems get reconciled into one agreed meaning.
Writing the result to the destination, either replacing existing data or appending incrementally. Incremental loading is more efficient and considerably harder to get right.
Scheduling, dependency management, retries, and failure alerting. Our data migration projects treat orchestration as a deliverable rather than as scripting.
ELT reverses the last two stages, loading raw data first and transforming inside the destination. This became practical once cloud warehouses made storage cheap and compute elastic. Both approaches remain valid, and the choice depends on your destination’s capability and whether you need raw data retained for reprocessing.
Transform before loading, so only cleaned data reaches the destination. Suits fixed destinations with limited compute and cases where raw data must not be stored.
Load raw data first, transform with the destination’s own compute. Retains original data for reprocessing when business rules change, which happens more often than expected.
Cloud warehouses provide elastic compute cheaply, making in-destination transformation practical. Our data warehousing work usually favours this pattern.
Where sensitive fields must be removed before storage, where the destination lacks transformation capability, or where volumes are large and only a summary is needed downstream.
Pipeline failures are rarely dramatic. They are usually silent, producing plausible but wrong numbers that people act on for weeks before anyone notices. The failure modes below account for most incidents, and each is detectable with monitoring that costs little to implement compared with the cost of discovering the problem through a bad decision.
An upstream team adds or renames a column without notice. Pipelines either break loudly, which is fine, or silently drop data, which is considerably worse.
The same field meaning different things in different systems. Reconciling these is a business decision rather than a technical one and needs documenting explicitly.
Records arriving twice or arriving after the window that should have contained them. Both distort aggregates in ways that are difficult to spot retrospectively.
A job completing while having processed only part of its input. Without row count validation this passes unnoticed, and every downstream number is quietly wrong.
A pipeline is production infrastructure and needs the same operational treatment as any other service. The distinguishing requirement is data quality validation, because a pipeline can run successfully and still produce wrong output. Monitoring that the job completed is necessary and nowhere near sufficient, which is the single most common gap in data operations.
Check row counts, null rates, value ranges, and referential integrity after each run. Job success without data validation is a false assurance.
Re-running should produce the same result rather than duplicating data. This makes recovery from failure straightforward rather than a manual repair exercise.
Record which source produced which output field. When a number looks wrong, lineage turns an investigation into a lookup, which our business intelligence work depends on.
Keep transformation code in version control with tests. Business rules encoded in undocumented scripts become impossible to change confidently as staff turn over.
A sudden change in row count or distribution usually indicates a problem even when nothing errored. Our API integration services work applies the same principle to feed monitoring.
Building with What Is ETL? Let's talk.
ETL means extracting data from source systems, transforming it into a consistent usable structure, then loading it into a destination such as a data warehouse. It reconciles the way operational systems store data with the way analysis needs it presented.
ETL transforms data before loading it, so only cleaned data reaches the destination. ELT loads raw data first and transforms it using the destination’s own compute. ELT became common because cloud warehouses made storage cheap and compute elastic.
Because it encodes business logic, including how conflicting field definitions across source systems get reconciled into one agreed meaning. Those are business decisions requiring stakeholder agreement, and they change over time, which makes transformation the part that keeps needing work.
Upstream schema changes that cause data to be dropped rather than error, partial job completion without row count validation, duplicate or late-arriving records distorting aggregates, and inconsistent definitions between sources. All produce plausible but wrong numbers.
Not only whether the job completed. Validate row counts, null rates, value distributions, and referential integrity after each run, and alert on anomalous changes even when nothing errored. Job success without data validation gives false confidence.
Managed pipeline tools handle common source-to-warehouse connections well and cheaply. Build custom extraction for unusual sources or complex business transformation. Most organisations use both, buying connectors for standard systems and writing their own transformation logic.