ETL

ETL stands for the three steps it runs in order. Extract reads raw records from sources like a Postgres production database, a Salesforce API, application log files, or a stream. Transform applies business rules: it cleans bad values, converts data types, deduplicates rows, joins tables, aggregates daily totals, and maps source columns to the warehouse schema. Load writes the finished result into the destination, usually a data warehouse such as Snowflake, BigQuery, or Redshift.

The point of ETL is to move data out of systems built for handling one transaction at a time and into a system built for analytics across millions of rows. A production database is tuned for fast single-row writes and reads. A warehouse is tuned for scanning huge tables to answer questions like revenue by region last quarter. ETL is the bridge between the two.

The word that matters most is the middle one. In classic ETL, transformation runs on a separate processing layer before the data ever reaches the warehouse, so only clean, structured data is loaded.

A typical ETL job runs on a schedule, often nightly or hourly, orchestrated by a scheduler like Apache Airflow or Dagster. The extract step connects to each source and pulls either a full snapshot or, more commonly, only the rows that changed since the last run. That incremental pull uses a watermark column such as updated_at, or change data capture that reads the database write-ahead log so the source is never scanned in full.

The transform step is where most of the engineering lives. The pipeline parses raw records, drops malformed rows or routes them to a dead-letter location, standardizes formats like phone numbers and timestamps, looks up reference data, and computes derived fields. Tools range from hand-written Python or Spark jobs to managed services. Heavy transforms on big data sets typically run on a distributed engine like Apache Spark so the work splits across many machines.

The load step writes results in bulk. Warehouses are far faster loading a million rows in one batch than inserting them one at a time, so ETL almost always stages data in object storage like S3 first, then issues a bulk COPY into the warehouse. Loads are usually idempotent: each run can re-load the same partition safely using an upsert or a delete-then-insert by date, so a failed and retried job does not create duplicates.

Use ETL when the destination cannot store raw data cheaply, when you must mask or drop sensitive fields before they land for compliance reasons, or when the transformation logic is expensive and you want it computed once on a controlled cluster rather than inside the warehouse. It is the long-standing pattern for moving operational data into reporting systems.

The main alternative is ELT, which flips the last two steps: load raw data first, then transform inside the warehouse using SQL. Modern cloud warehouses are so fast and cheap that ELT has become the default for many teams, because raw data stays available for re-processing and analysts can rebuild transformations without re-extracting. ETL still wins when source-side compute is needed, when raw storage is a liability, or when the warehouse should not see unfiltered data.

The trade-offs of ETL are latency and rigidity. Batch ETL means analytics lag reality by minutes to a day. And because transforms run before load, fixing a bug in transformation logic means re-running the whole pipeline from extract, since the raw data was never kept.

Picture an e-commerce company. Orders live in a Postgres database, customer support tickets live in Zendesk, and ad spend lives in the Google Ads API. The finance team wants one dashboard showing profit per customer.

An ETL pipeline runs at 2 AM. It extracts yesterday's orders using the updated_at watermark, pulls new tickets from the Zendesk API, and pulls spend from Google Ads. The transform step converts every currency to USD, joins orders to customers, sums ad spend per customer, subtracts refunds flagged in support tickets, and produces one clean fact table keyed by customer and date. The load step bulk-writes that table into BigQuery.

By 7 AM the dashboard shows clean profit numbers without anyone touching the raw, messy source systems. The production database never gets hammered by analyst queries, and the warehouse only ever holds the curated result.