ETL and ELT

In ETL (extract, transform, load), extract reads data from databases, APIs, CSV drops or Kafka 129 topics; transform cleans and reshapes it (deduplicates, converts types, handles missing values, validates, joins); load writes the result to a target such as a warehouse: MySQL 524 , then a Python ETL job, then Snowflake. Two transformations appear in nearly every pipeline: the text "RM 1,200.50" must become the number 1200.50, and the timestamp 2026-09-11 05:30:00 the date 2026-09-11. Here is a complete, tiny ETL job for a Malaysian reseller's payout feed:

etl_transform.py: extract, transform in Python, loadPython

import re, sqlite3
from datetime import datetime, timezone
from decimal import Decimal
from zoneinfo import ZoneInfo
def to_amount(text):                  # "RM 1,200.50" -> Decimal("1200.50")
    return Decimal(re.sub(r"[^0-9.]", "", text))
def to_date(text, zone="UTC"):        # "2026-09-11 05:30:00" -> 2026-09-11
    t = datetime.strptime(text, "%Y-%m-%d %H:%M:%S").replace(tzinfo=ZoneInfo(zone))
    return t.astimezone(timezone.utc).date()
raw = [("MY-1001", "RM 1,200.50", "2026-09-11 05:30:00"),   # extract: all text
       ("MY-1001", "RM 1,200.50", "2026-09-11 05:30:00"),   # a duplicate
       ("MY-1002", "RM 89.90", None)]                       # a missing timestamp
clean = {ref: (ref, str(to_amount(amt)), str(to_date(ts)))  # transform
         for ref, amt, ts in raw if ts is not None}
db = sqlite3.connect(":memory:")                            # load
db.execute("CREATE TABLE payouts (ref TEXT PRIMARY KEY, amount_myr TEXT, paid_date TEXT)")
db.executemany("INSERT INTO payouts VALUES (?, ?, ?)", clean.values())
print(db.execute("SELECT * FROM payouts").fetchall())
print("read as Malaysia time:", to_date("2026-09-11 05:30:00", "Asia/Kuala_Lumpur"))

Output

[('MY-1001', '1200.50', '2026-09-11')]
read as Malaysia time: 2026-09-10

The last line is the trap in "timestamp to date": the text carries no zone. On Malaysia time (UTC+8), 05:30 is 21:30 UTC the day before. Record the source zone, convert to UTC once and derive dates from UTC.

ELT (extract, load, transform) lands raw rows untouched and transforms them with SQL inside the platform, as ETL and ELT did with BookNest's JSON orders; in PostgreSQL 1,289 the amount cleanup is regexp_replace(amount, '[^0-9.]', '', 'g')::numeric. In short, ETL transforms data before loading it into the target system, while ELT loads it first and transforms it inside the target data platform. Cloud stacks (S3 into Redshift 24 , Snowflake or BigQuery 1 , then SQL) favor ELT: warehouses transform at scale, and the raw copy lets you fix a bug by re-running SQL instead of extracting again. ETL still wins when personal data must be removed before it lands (Security and Privacy Basics). BookNest's daily pipeline is ELT.

MENU