ETL and ELT Fundamentals

Overview

Extract, Transform, Load (ETL) is a data pipeline pattern for moving data from one or more source systems into a destination — normalising and cleaning it along the way so the destination can serve analytical queries efficiently.

Every data pipeline implicitly does ETL. The value of the term is a shared vocabulary that maps to tool categories, job types, and service billing models — including the AWS exam keyword that points directly to AWS Glue.

The Three Phases

Extract — Read from sources

The pipeline connects to source systems and reads raw data without modifying the source.

Common sources:

• Relational databases (MySQL, PostgreSQL, Oracle) via JDBC
• Object storage (S3 CSVs, JSON files, Parquet)
• REST APIs and SaaS systems (Salesforce, Stripe)
• Event streams (Kinesis, Kafka)
• NoSQL databases (DynamoDB, MongoDB)

Simple example:

-- Extract: read all orders placed in the last 24 hours from a MySQL source
SELECT order_id, customer_id, amount, status, created_at
FROM orders
WHERE created_at >= NOW() - INTERVAL 1 DAY;

Transform — Clean and reshape

The Transform phase applies business logic to the extracted data before it reaches the destination. This is the most variable phase — it can be trivial or arbitrarily complex.

Data cleansing examples:

AWS Glue example — rename column and cast type in a DynamicFrame:

from awsglue.transforms import ApplyMapping

mapped = ApplyMapping.apply(
    frame=raw_orders,
    mappings=[
        ("order_id",    "string",  "order_id",    "string"),
        ("customer_id", "string",  "customer_id", "string"),
        ("amount",      "string",  "amount",      "decimal(10,2)"),  # cast
        ("created_at",  "string",  "event_time",  "timestamp"),      # rename + cast
    ]
)

Load — Write to destination

The processed data is written to the target system in its final form.

Common destinations:

• Data warehouses: Amazon Redshift, Snowflake, BigQuery
• Data lakes: Amazon S3 (Parquet, ORC, or Avro format)
• Search indexes: Amazon OpenSearch Service
• Reporting stores: Amazon Redshift, RDS read replicas

Simple example:

-- Load: insert transformed rows into the Redshift fact table
INSERT INTO analytics.fact_orders (order_id, customer_id, amount, event_time)
SELECT order_id, customer_id, amount, event_time
FROM staging.orders_clean;

ETL vs. ELT

The modern variant — ELT (Extract, Load, Transform) — reverses the last two phases. Raw data lands in the warehouse first; transformations run in-place using SQL.

Full End-to-End Example

A retail company ingests daily order exports from MySQL into Redshift for BI dashboards.

MySQL (OLTP)
    │
    │  Extract: JDBC read — orders from last 24h
    ▼
AWS Glue ETL Job (Apache Spark)
    │  Transform:
    │    - cast amount string  → DECIMAL
    │    - drop NULL customer_id rows
    │    - normalise country codes
    │    - deduplicate by order_id
    ▼
S3 staging bucket (Parquet)
    │
    │  Load: COPY command
    ▼
Amazon Redshift — fact_orders table
    │
    ▼
Amazon QuickSight dashboard

Quick Reference

AWS Implementation Options

Related Services / See Also

• AWS Glue — serverless ETL engine and data catalog; primary AWS ETL service
• Amazon Redshift — cloud data warehouse; common ETL destination
• Amazon Kinesis and Managed Flink — streaming ingestion and real-time transformation
• Amazon Athena — query S3 directly; supports schema-on-read ELT patterns
• Database Performance Fundamentals — OLTP vs. OLAP workload differences