Extract, Transform, and Load (ETL)

Goal

ETL brings together in one repository data that several systems hold separately, each with its own format, its own coding scheme and its own update cycle.

A dashboard, a reserves calculation, a regulatory report read only the target repository. What they display is only as good as the process that filled it: its frequency sets how current the figures are, its rules set their meaning, its rejects set their completeness. A discrepancy between two reports is almost always resolved in the process rather than in the tool that displays them.

Usage

When to use it

• Several source systems to bring together: diverging formats, coding schemes and dictionaries to reconcile.
• A recurring report to feed: once the rules are settled, the same process replays at every due date with no new decision.
• Descriptive analytics or a proof of concept: where the guide places classic ETL, ahead of any large-scale rollout.
• Traceability required: the audit trail of the load says which value changed, when and under which run.

When not to use it

• Near-real-time algorithmic decisions: batch processing will not meet the latency requirement; the answer is stream processing.
• A one-off analysis on a single source: a query or an export is enough; the investment does not pay for itself.

Description

Extract

Extraction pulls the useful data out of the systems that hold it, and the guide distinguishes three tasks in it.

The first identifies the sources and the data types from the business problem: it starts at the large sets (customer relationship management, billing, sales channels) and works down to the entities and then the fields as the need takes shape.

The second establishes a universal classification. Every source arrives with its own conventions and its own data dictionary; definitions, descriptions and formats are reconciled so that one extraction schema applies to all of them.

The third checks the integrity of what comes out. Part of it is checked automatically, through a mapping that matches the source elements to the extracted ones: conformity of sizes and formats, redundancies, losses in transmission. The other part stays manual: minimum and maximum values, identifiers, permitted values, sampling. The DMBOK gives the vocabulary these checks are filed under, completeness, validity, consistency, currency and uniqueness, which keeps a check from being named after the script that runs it.

That leaves the question only incremental extraction raises: what has changed since the last run? Kimball and Caserta handle change data capture as an extraction technique in their ETL architecture, alongside a discipline of recovery and restart. The last-modified timestamp is the cheapest and the most fragile: it does not see deletions and it does not move when a correction came through a technical job, so the gaps pile up with no signal until the day a manual reconciliation finds them. A monthly or quarterly full load bounds that drift. Reading the transaction log of the source database sees every write, at the price of access the operators do not always grant. A full comparison against the previous snapshot sees everything, at the price of a full extraction at every run.

The staging area

The guide defines the staging area as a logical location for the data, one that makes the transformations easier. Kimball and Caserta give it a second function: the restart point. A process that lands its extract and keeps it will restart from that extract after a failure. Restarting from the staging area takes a few minutes. Re-extracting three production systems takes several hours and returns a snapshot that is no longer the one the batch started from, since the systems kept running in the meantime.

The same authors set a condition on recovery: the process has to replay without side effects, that is, produce the same state, with the same number of rows. The property is obtained by deleting and then reinserting the identified batch or by writing against a natural key. Without it, a process restarted after a partial failure rewrites what it had already written: the duplicates pass the format checks and inflate the aggregates by a few per cent, a magnitude too small to raise an alarm and too large to ignore. A dry run in a test environment is what verifies the property.

Transform

Transformation turns the extracted data into a usable and accurate format that matches the business logic.

The analyst's part is about meaning. They check that no business rule ends up contradicted: that grouping into bands does not hide the threshold a decision turns on, that an imputation does not manufacture an observation, that a rounding applied before a sum does not shift the total. This is the last moment the question "does this value still mean the same thing?" gets asked before the figure enters a calculation.

Load

Loading brings the transformed data from the staging area into the target repository. The usual tasks are to review the target format and the load mode against the business need, write the batch, produce the audit trail, then standardise the sequence so that it replays identically at every due date.

The incremental load compares the batch with the data already present and writes only the difference, which shortens the refresh of decision-support reports. The full load replaces the whole of the target's data; the guide holds it to be better suited to predictive and prescriptive work, which operates on a set rebuilt as at a given date. Kimball and Caserta treat this choice as an architecture decision, taken in full knowledge of its two consequences: the length of the load window and the burden placed on the source systems.

Overwrite or keep the history

Loading carries a management decision: what becomes of the old value when an attribute changes? Kimball and Ross catalogued the answers under the name of slowly changing dimensions. Type 1 overwrites: the new value replaces the old one and the history disappears. Type 2 adds a row, closes the previous one with an end date and flags the current version, so that each fact stays attached to the version in force at the moment it happened. Type 3 keeps one earlier value, in an extra column. A Kimball Group design tip describes the extensions numbered 0, 4, 5, 6 and 7.

Type 1 is the default behaviour of most tools, which makes it the quietest trap in the load: it produces no error, no reject and no alert. Last year's report, regenerated this year, no longer returns the same figures, and nobody holds the value that would let the difference be reconstructed. The choice belongs to the business, attribute by attribute: does a calculation, a report or a legal obligation need to know what this attribute held at a past date? The canton an insured person lives in and the rate applied to a contract need it. A correspondence address or a phone number rarely does.

The audit trail and the reconciliation

The audit trail records, for each run, what the load wrote: the rows added, those modified or replaced, the earlier value and the identifier of the run. It is produced at the load because the load is the moment the value changes.

It feeds the reconciliation that follows each run: the count of rows read, transformed, loaded and rejected, checksums on the amounts, the difference explained row by row. The reject register is read the next morning, with one reason per record and a written instruction on what happens to them: correction at the source and pick-up in the next batch, manual correction in the target or a deliberate decision to drop the record.

This trace differs from data lineage, which the DMBOK treats under metadata management. Lineage describes the path an element travels between systems and the transformations it undergoes, independently of any run. Lineage answers "where does this field come from?", the audit trail "who changed this value on Tuesday evening?"

The boundary with data mapping

Data mapping declares, ETL executes. The specification states, for each target attribute, which source feeds it and under which rule; the process applies that declaration to the real records. Everything that has to do with time belongs to the process: the scheduling, the load window, the recovery after a failure, the rejects, the trace of what was written. Post-load reconciliation is on this side of the boundary too. This is where a rule applied by the process but absent from the specification shows up.

ETL or ELT

High-volume technologies invert the order of the last two steps: extract, load, then transform. The raw data lands on a distributed file system or in a cloud warehouse, and the transformation runs on the computing power of the target platform. This is the usual answer to data that is massive, heterogeneous or loosely structured. The guide places the limits of classic ETL tools there, while maintaining that the principles of ETL hold whatever the tools: only the order and the place of the transformation change. For the analyst, the check on the business rules then happens after the load, on data already available to users, which obliges the designer to keep the raw sets apart from the validated ones inside the repository; otherwise a dashboard ends up reading an intermediate table.

What makes the process fail

Silent rejects. A tool that sets aside non-conforming records with no register and no counter delivers a target short by an unknown number of records. The check fits in one comparison: rows read at the source against rows loaded plus rows rejected.

The source schema that moves. A column added, renamed or retyped in a source system breaks the extraction or, worse, lets it keep running on a value that has become false. Bringing the source systems under change control and making the process fail on an unexpected schema costs less than finding the discrepancy three months later in a report.

AI considerations

Generating the transformation code is the most immediate use. A rule written in the mapping specification translates into a SQL expression or a tool transformation with nothing invented, and a first draft is quick to read over. Test datasets that cover the edge cases of a rule (a null value, a value out of range, the maximum length) are obtained the same way and are the part of the work that teams write least.

Monitoring the runs lends itself to statistical learning. A model trained on the history of the loads flags a batch where the volume, the distribution of an amount or the rate of null values departs from what comparable runs return. A fixed-threshold check does not see the 12% drop in a regional feed on a public-holiday Monday; a model that has seen two years of batches does. Proposing the lookup table between two regional coding schemes is a similarity match, whose result is then validated row by row.

The meaning of a business rule cannot be delegated. A model produces a syntactically correct transformation on a field without knowing what that field means inside the organisation. The choice between keeping the history and overwriting it depends on downstream uses and on legal obligations the schema does not carry. The decision on the rejects, setting a record aside, correcting it or loading it as it stands, commits the completeness of everything calculated afterwards.

That leaves the data itself. A transformation step that calls an external service hands it production records at every run, AHV numbers, amounts and dates included. Disclosing personal data to a third party that processes it on behalf of the controller requires a processor agreement within the meaning of art. 9 of the Federal Act on Data Protection. A service hosted outside Switzerland adds art. 16, on disclosure abroad. Masking therefore goes before the call.

Examples

A health insurer loads the claims transactions from its regional systems every night into a warehouse that feeds the reserves calculation and the risk equalisation between cantons. An insured person moves from Vaud to Geneva on 1 June 2024. The next night's extraction brings up the new canton, and the load mode decides the fate of the old one.

An invoice dated 12 March 2024, for CHF 1'240.50, points to surrogate key 4711. Under type 2 it stays attached to the Vaud row and the 2024 risk-equalisation statement keeps counting it against Vaud. Under type 1 the same invoice now counts against Geneva: the per-canton total of a closed year changes on the day of a move, with no write having touched the claims table. The distinct surrogate key on the second row is what makes keeping the history possible; reusing key 4711 for the Geneva version would bring back type 1 behaviour.

Of 40'000 insured persons in Vaud, 1.5% change canton within the year: 600 people at an average cost of CHF 3'800. Overwriting moves some CHF 2'280'000 of claims from one canton to another in a statement already filed.

The same insured person leaves for Fribourg in March 2025. Under type 2, the following night closes the Geneva row and opens a third with its own key; the 2024 invoices stay on 4711 and 5290 according to their date. Under type 1, the same night replaces GE with FR on row 4711 and moves the 2024 statement a second time.

Visualisations

The process is worth drawing, because two of its properties are seen better than they are read: the place a run restarts from after a failure and the fact that the audit trail is a product of the load step. The two branch points are worth drawing as well, because they are decided independently of each other: incremental or full at the extraction, writing the difference or replacing at the load.

Two registers stay as tables, the catalogue of transformation types and the comparison between overwriting and keeping the history: the material fits in rows and columns. Turning it into an image would take away selection and sorting.

Cost

Tooling

Graphical ETL tools (Talend, SQL Server Integration Services, Azure Data Factory, Informatica, Pentaho) are the family the guide describes: connectors to the enterprise sources, a graphical view of the sequence, which opens part of the work to users who write no code. Their limitation is readability for the business, which nevertheless has to agree to the rules: a transformation expressed in the tool is hard to approve in a meeting.

Schedulers (Apache Airflow, Dagster or the enterprise scheduler already in place) carry what the transformation tool does not: the dependencies between tasks, the execution windows, the retries and the alerting. This is where recovery is settled.

In-warehouse transformation tools (dbt first among them) match the ELT pattern: the transformations are written in versioned SQL, run on the target platform and are tested by assertions attached to each table. The business rule becomes readable again, at the price of a dependency on the metered computing power of the platform.

Target platforms (Snowflake, BigQuery, Databricks or a PostgreSQL warehouse on in-house infrastructure) determine which load mode is available and what a full replacement costs. The choice commits the nightly load window.

Profiling and quality tools (Great Expectations, the profiling functions of the ETL tools, a set of in-house queries) measure the DMBOK dimensions on each batch and make the process fail when a threshold is crossed. A check that logs without stopping the process lets in the very data it has just called false.

Sources

• IIBA, Guide to Business Data Analytics, §3.10 Extract, Transform, and Load (ETL): the definition, the three steps and their tasks, the staging area, the catalogue of transformation types, the incremental and full load modes, the production of audit trails, the ELT sequence of high-volume technologies, as well as the strengths and limitations of the technique.
• Ralph Kimball and Joe Caserta, The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data, Wiley, 2004: the staging area as a restart point, the replay without side effects as the condition of recovery, change data capture as an extraction technique, the choice between an incremental load and a full load as an architecture decision.
• Kimball Group, ETL Architecture's 34 Subsystems: change data capture and recovery after a failure as named components of an ETL architecture.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd edition, Wiley, 2013: slowly changing dimensions of type 1, 2 and 3.
• Kimball Group, Design Tip #152: Slowly Changing Dimension Types 0, 4, 5, 6 and 7: the extensions to the catalogue of slowly changing dimensions.
• DAMA International, DAMA-DMBOK: Data Management Body of Knowledge, 2nd edition: chapter 12 Metadata Management for data lineage, chapter 13 Data Quality for the quality dimensions cited at the extraction.
• Federal Act on Data Protection (FADP), SR 235.1, art. 9 and 16: the processor agreement required when a third party processes personal data on behalf of the controller; the conditions for disclosure abroad when that third party is hosted outside Switzerland.