ETL Pipelines

Why ETL matters

In Data Warehousing, you modeled analytics-ready tables. In Database Querying, you treated SQL as a contract: joins at the right grain, safe parameters, and queries that return trusted results.

Now we connect those ideas into a repeatable system: an ETL pipeline.

A pipeline answers:

• Extract: Where does raw data come from?
• Transform: How do we clean, validate, and shape it?
• Load: How does it land safely in warehouse tables?
• Operate: How do we rerun, monitor, and recover it?

The goal is not just to move data once. The goal is to move data reliably every day.

The basic ETL loop

A practical ETL pipeline usually looks like this:

Source systems
  |  Extract
  v
Raw / staging data
  |  Transform
  v
Clean modeled data
  |  Load
  v
Warehouse tables

That diagram is simple, but each arrow hides engineering choices:

• Batch size: one day, one hour, one file, one event group?
• State: how does the job remember what it already processed?
• Data quality: what gets rejected, repaired, or alerted on?
• Idempotency: what happens if the same job runs twice?

For an intermediate Python, SQL, and Spark learner, the important mental shift is this: an ETL pipeline is a program plus a data contract plus an operating procedure.

A teammate says: “Our script successfully copied yesterday’s CSV into the warehouse once, so the ETL pipeline is production-ready.”

What is the best response?

Extraction should avoid guessing

A common beginner mistake is extracting “everything” every time. That can work for tiny datasets, but it becomes slow, expensive, and risky.

A more scalable pattern is incremental extraction:

1. Store a marker for the last successful run.
2. Fetch only records newer than that marker.
3. Process and load them.
4. Update the marker only after success.

That marker is often a timestamp, increasing ID, file offset, or partition name.

Below is a tiny Python example using an updated_at timestamp as pipeline state.

```python
from datetime import datetime

source_rows = [
    {'order_id': 1, 'amount': 25.0, 'updated_at': '2026-04-20T10:00:00'},
    {'order_id': 2, 'amount': 40.0, 'updated_at': '2026-04-21T12:30:00'},
    {'order_id': 3, 'amount': 15.0, 'updated_at': '2026-04-22T09:15:00'},
]

last_successful_watermark = datetime.fromisoformat('2026-04-21T00:00:00')

extracted = [
    row for row in source_rows
    if datetime.fromisoformat(row['updated_at']) > last_successful_watermark
]

new_watermark = max(datetime.fromisoformat(row['updated_at']) for row in extracted)

print('Extracted rows:')
for row in extracted:
    print(row)

print('New watermark:', new_watermark.isoformat())
```

True or false: A pipeline is reliable if it succeeds once on a small sample dataset.

Transformation is where trust is built

Transformations are not just string cleanup. They enforce the shape and meaning that downstream users expect.

Building on Data Warehousing, transformations often prepare rows for fact and dimension tables. Building on Database Querying, transformations should preserve grain: an order_items table should not accidentally become one row per customer.

Useful transformation checks include:

• Required fields are present.
• Types are valid.
• Amounts and dates are plausible.
• Duplicate records are handled intentionally.
• Rejects are visible, not silently ignored.

Here is a small validation-and-cleaning transform in Python.

```python
raw_orders = [
    {'order_id': '1001', 'customer_id': '42', 'amount': '19.99'},
    {'order_id': '1002', 'customer_id': '', 'amount': '8.50'},
    {'order_id': '1001', 'customer_id': '42', 'amount': '19.99'},
    {'order_id': '1003', 'customer_id': '99', 'amount': '-4.00'},
]

seen_order_ids = set()
clean = []
rejects = []

for row in raw_orders:
    try:
        order_id = int(row['order_id'])
        customer_id = int(row['customer_id'])
        amount = float(row['amount'])

        if amount < 0:
            raise ValueError('amount must be non-negative')
        if order_id in seen_order_ids:
            raise ValueError('duplicate order_id')

        seen_order_ids.add(order_id)
        clean.append({
            'order_id': order_id,
            'customer_id': customer_id,
            'amount': amount,
        })
    except Exception as error:
        rejects.append({'row': row, 'reason': str(error)})

print('Clean rows:', clean)
print('Rejected rows:')
for reject in rejects:
    print(reject)
```

A stored value such as the maximum successfully processed updated_at timestamp is commonly called a pipeline _____.

Loading should be idempotent

A load step writes transformed data into the warehouse. The dangerous case is not the first run — it is the retry.

If a job fails halfway and runs again, you want a predictable result. That is why many pipelines use idempotent loading patterns.

An idempotent operation can run more than once and still produce the same final state.

Common load strategies include:

• Append: add new rows only; simple but duplicate-prone.
• Overwrite partition: replace a known time slice, such as one day.
• Upsert / merge: insert new rows and update existing rows by key.

This tiny example simulates an upsert load into a warehouse table represented as a dictionary.

```python
warehouse_orders = {
    1001: {'order_id': 1001, 'customer_id': 42, 'amount': 19.99},
}

batch = [
    {'order_id': 1001, 'customer_id': 42, 'amount': 21.99},
    {'order_id': 1002, 'customer_id': 77, 'amount': 8.50},
]

def upsert_orders(table, rows):
    for row in rows:
        table[row['order_id']] = row.copy()

print('Before load:', warehouse_orders)
upsert_orders(warehouse_orders, batch)
print('After first load:', warehouse_orders)
upsert_orders(warehouse_orders, batch)
print('After retrying same load:', warehouse_orders)
print('Row count:', len(warehouse_orders))
```

A batch load inserts rows into fact_orders. The orchestrator may retry the task automatically after a timeout.

Which loading approach is safest for avoiding duplicate facts?

Pipelines need an operating model

Once extraction, transformation, and loading work locally, the next question is: how does this run every day without you watching it?

A production-minded pipeline tracks:

• Schedule: when should it run?
• Dependencies: what must finish first?
• Logs: what happened during the run?
• Metrics: row counts, reject counts, duration, freshness.
• Alerts: who is notified when expectations fail?
• Backfills: how do we safely rerun historical periods?

A useful pattern is to make every task answer three questions:

What input did I read?
What output did I produce?
What evidence proves it was correct?

In the next lesson, you’ll see how cloud services help host, schedule, store, and monitor these pieces at scale.

What you can now reason about

You have moved from warehouse design and SQL querying into the system that keeps warehouse data current.

A strong ETL pipeline is:

• Stateful: it knows what it has already processed.
• Validated: it checks assumptions before publishing data.
• Idempotent: retries do not corrupt results.
• Observable: failures and data quality issues are visible.
• Aligned with the warehouse: it loads tables at the intended grain.

When you design an ETL pipeline, do not start with tools. Start with the contract:

Given these sources and this schedule,
produce these warehouse tables,
at this grain,
with these quality guarantees,
and this recovery behavior.

That is the data engineering mindset in action.