Data integration: ETL, ELT and pipelines

Why this matters

A single query answers a single question. An organisation's reporting needs data from many systems gathered, combined and refreshed on a schedule, reliably, without anyone running queries by hand. That is data integration, and the processes that do it are pipelines. Analysts may not build every pipeline, but they depend on them, and most "the numbers are wrong this morning" problems turn out to be a pipeline that failed, ran late or loaded half its data.

The exam asks you to distinguish the integration approaches and to recognise what can go wrong. This lesson covers ETL and ELT, batch and streaming loads, change data capture, scheduling and failure, and integrating different kinds of source into one place.

The lesson

Extract, transform, load against extract, load, transform

ETL stands for extract, transform, load. Data is extracted from source systems, transformed on a separate processing server (cleaned, standardised, joined and reshaped), and then loaded into the target, usually a data warehouse, in its finished form. ETL is the traditional approach. Its strengths are that only clean, conformed data reaches the warehouse, and that sensitive fields can be removed or masked before they are ever stored there. Its weakness is rigidity: the transformations must be designed in advance, and a question that needs raw detail the transformation discarded cannot be answered later.

ELT stands for extract, load, transform. Raw data is loaded into the target first, usually a cloud warehouse or lake, and transformed there, using the target's own processing power, typically with SQL. ELT has become common because cloud warehouses are powerful enough to do the transformation themselves. Its strengths are flexibility and speed of loading: raw data is kept, so new transformations can be written later without re-extracting. Its risks are that raw, possibly sensitive data now sits in the warehouse and needs governing, and that the warehouse can fill with raw tables nobody uses.

In short, ETL transforms before storing and ELT stores before transforming. The choice depends on the platform, the need to keep raw history, and where sensitive data is allowed to rest.

Batch against streaming, and full against incremental loads

Pipelines differ in how often and how much they move.

Batch processing moves data in scheduled chunks: every night, every hour. It is simple, efficient and the most common approach for reporting. Its limitation is latency: a nightly batch means data is up to a day old.

Streaming (or real-time) processing moves each event as it happens, so data is available within seconds. It suits fraud detection, live operations dashboards and alerting. It is more complex to build and run, and many questions do not need it: a monthly sales report gains nothing from second-by- second updates.

Independently, each run can be full or incremental.

• A full load replaces the whole target with a fresh copy of the source. It is simple and self-correcting, and becomes slow and expensive as data grows.
• An incremental load moves only what has changed since the last run. It is fast and scales well, and it is harder to get right, because the pipeline must know reliably what changed, and a mistake can leave records missing or duplicated for good.

Change data capture and the watermark

Incremental loads need a way to identify new and changed records.

The simplest is a watermark: a value recording how far the last load got, usually the latest updated_at timestamp or the highest ID loaded. The next run extracts records beyond the watermark and then advances it. Watermarks depend on the source maintaining those columns correctly: a record changed without its timestamp being updated will never be picked up.

Change data capture (CDC) reads changes directly from the source database, often from its transaction log, and captures every insert, update and delete. Deletions are the important difference. A watermark on "updated since" never sees a row that was deleted, so an incremental pipeline based on it keeps records in the warehouse that no longer exist in the source. CDC catches them.

Loading changed records into the target is often done with an upsert (update if the record exists, insert if it does not), sometimes called a merge.

Scheduling a pipeline and knowing when it failed

Pipelines run on a schedule or when triggered by an event, managed by a scheduler or an orchestration tool that runs steps in the right order and retries on failure. Steps usually depend on each other: the sales table cannot be built until the orders extract has finished.

The dangerous failures are the quiet ones. A pipeline that crashes is noticed. A pipeline that runs "successfully" but loads zero rows because the source file was empty, or loads yesterday's file again, or stops halfway through, can leave reports showing stale or partial numbers with no error at all.

Good pipelines therefore check themselves:

• Row count checks: compare rows extracted with rows loaded, and flag a load far smaller or larger than usual.
• Freshness checks: record when each table was last loaded, and alert when it is overdue.
• Alerts on failure, sent to someone who will act on them.
• Logs of each run: when it started and finished, how many rows moved, and any errors.
• Idempotence: designing a load so that running it twice produces the same result as running it once, which makes safe retries possible.

For an analyst, the practical habit is to check the last-refreshed time of any data before presenting results from it.

Integration from APIs, files and databases into one place

A real pipeline usually combines several kinds of source.

• Databases are extracted with queries, ideally from a replica or with change data capture, to avoid loading the production system.
• APIs are called with authentication, pagination and rate limits handled, and their nested JSON flattened.
• Files arrive in a folder, a storage bucket or by transfer, and must be detected, validated and archived so the same file is not loaded twice.

Bringing them together requires conforming the data: the same customer must have the same identifier in every source, dates and currencies must share one format, and codes must mean the same thing. Mismatched keys are the main obstacle, and a mapping table that links each system's identifiers is often the core of an integration.

The goal is a single, consistent place to analyse from, with each record traceable back to where it came from. The source, the time it was loaded and the pipeline run that loaded it are often stored as extra columns on every record, which makes that tracing possible.