ETL (Extract, Transform, Load) Concepts

Section 5: Designing Data Migration Solutions. Lesson 25.

ETL stands for Extract, Transform, Load. It is a core data migration and data engineering process for moving data from one or more source systems into a target such as a database, a data warehouse, a data lakehouse or a reporting platform. IBM describes ETL as extracting data from multiple sources, cleaning and organising it, and loading it into a unified repository.

The three stages

E: Extract

Pull raw data from the source systems without altering its original format. Sources can be databases, files, APIs or SaaS applications.

• Full extract: everything, every run. Simple, but slow for large tables.
• Incremental extract: only rows changed since the last run, using a timestamp such as updated_at or an increasing ID.
• Change data capture (CDC): read changes from the database log in near real time, as with Debezium or AWS DMS.

T: Transform

Reshape the data to fit the target. This is where the work of Section 3 happens:

• Cleaning: fix or flag invalid, missing and duplicate data (lessons 14–15)
• Standardisation: one format for dates, phone numbers, casing and codes (lesson 16)
• Aggregation: summarise, such as daily sales totals per store
• Enrichment: add information from reference tables or external sources, or calculated values. Examples: postcode to region, currency conversion, a customer lifetime-value score.
• Restructuring: split, merge, rename and retype columns and tables (lessons 21–22)

L: Load

Write the processed data into the target system:

• Full load: replace the target table's contents
• Incremental or upsert: insert new rows and update changed ones, using a key
• Bulk load: fast native loaders such as COPY, LOAD DATA or bcp, often with indexes disabled until the load finishes

A simple ETL pipeline

The lecture's whiteboard example moves an Excel file into MySQL:

Excel file ──E──▶ [ format → standardise → clear errors ] ──L──▶ MySQL
            extract           transform                     load

import pandas as pd
from sqlalchemy import create_engine

# Extract: read the source as-is
raw = pd.read_excel("customers.xlsx")

# Transform: format, standardise, clear errors
df = raw.copy()
df.columns = [c.strip().lower().replace(" ", "_") for c in df.columns]
df["email"] = df["email"].str.strip().str.lower()
df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce").dt.date
name = df["full_name"].str.strip().str.split(" ", n=1, expand=True)
df["first_name"], df["last_name"] = name[0], name[1]
df = df.drop(columns=["full_name"]).drop_duplicates(subset="email")

# Load: write to MySQL
engine = create_engine("mysql+pymysql://etl_user:***@localhost:3306/crm")
df.to_sql("customers", engine, if_exists="append", index=False)

Why ETL matters in migration

Source and target systems rarely share the same structure. The source may store a name in one column while the target needs two:

Source                          Target
customer_id  1001      ──▶      customer_id  1001
email        alex@x.io ──▶      email        alex@x.io
full_name    Alex John ──▶      first_name   Alex
                                last_name    John

Everything else maps across unchanged; full_name is split into first and last name. Without that transformation the migrated data would not fit the new system. ETL gives the team a controlled, repeatable process for converting old data into the format the new system expects, and it can run as often as needed during testing.

ETL can be run manually with custom scripts, such as SQL or Python like the example above, or automated with dedicated tools.

Common ETL tools

Airflow is an orchestrator rather than an ETL engine: it schedules and monitors tasks and handles their dependencies, while the actual extract, transform and load work runs in SQL, Python, Spark or the tools above. Other widely used tools: Informatica, Talend, Fivetran and Airbyte (extract and load), dbt (transform inside the warehouse), and Apache Spark.

AWS Glue in particular positions itself as preparing data for analytics and AI. Its stated benefits are faster pipeline development, on-demand scalability, less operational complexity and built-in generative-AI assistance for writing jobs.

ETL vs ELT

ELT (Extract, Load, Transform) swaps the last two steps: raw data is loaded into the target first, and transformed there using the target's own compute.

ETL:  Source ──extract──▶ [ transform in a staging engine ] ──load──▶ Target
ELT:  Source ──extract──▶ load raw into Target ──▶ [ transform inside Target with SQL ]

Many platforms support both. Azure Data Factory, for example, can run classic ETL or ELT patterns in the same service. For migrations into an operational database, which has a strict schema and constraints, ETL is usual. For migrations into a cloud warehouse, ELT is increasingly common.

Good ETL practice

• Stage the raw extract before transforming, so a run can be repeated without hitting the source again.
• Make jobs idempotent: running one twice should not duplicate data. Use upserts or truncate-and-load.
• Log row counts at every stage (extracted, rejected, loaded) and reconcile them.
• Send rejected rows to an error table with the reason, rather than dropping them silently.
• Keep transformation rules in version control, together with the mapping document.

Key takeaways

• ETL: extract raw data unchanged, transform it (clean, standardise, aggregate, enrich, restructure), load it into the target.
• It exists because source and target structures differ, such as full_name becoming first_name and last_name.
• Run it with scripts or tools: AWS Glue, Cloud Dataflow, SSIS, Azure Data Factory, with Airflow for orchestration.
• ELT loads first and transforms inside the target, and suits cloud warehouses.

Next: 26. Data Mapping Techniques.