Skip to content

Data & Analytics

ETL and ELT pipelines: run it twice, get the same answer

A pipeline is a job that runs every night with nobody watching. It has to run its steps in the right order, wait when a source is late instead of building on half the data, and be safe to rerun: run Tuesday again and you get Tuesday's numbers, not double. Then fixing last month is a rerun, not a weekend. The result is morning numbers nobody has to double-check before a meeting.
Discuss your systemSee three nights ↓
Night run · Oct 1↻ rerun Sep 14Extract CRM✓ doneExtract scheduling✓ doneExtract accountingfailed · timeoutretry ✓Load rawwaitingLoad raw✓ doneStagingwaitingStaging✓ doneModelwaitingModel✓ donePublishwaitingPublish✓ done01:00 · extract CRM, scheduling ✓01:02 · extract accounting ✕ timeout · downstream waits01:32 · retry accounting ✓01:40 · load → staging → model ✓02:05 · publishedrerun Sep 14 · same totals: 3 jobs, $4,180

Load first, shape after

The letters are about where the shaping happens. We usually load raw data first, because it makes every later fix a rebuild instead of a re-extract.
Where shaping happensBefore loading, inside the pipelineAfter loading, in the database
The raw dataDiscarded once shapedKept as delivered
When a rule changesExtract again from every sourceRebuild from the raw copy
Good forStrict targets; removing sensitive fields earlyMost reporting and analysis

Either way, fields you shouldn't keep, like card numbers, are removed before anything is stored.

What makes a pipeline dependable

  1. 01

    Safe to rerun

    Same input, same result

    Each step replaces its own output for the day it covers, keyed by record, so a retry never adds a second copy.

    steps that append without a key.

  2. 02

    Steps in order

    Nothing runs on partial data

    Each step waits for the steps it depends on. If one fails, everything after it waits too.

    schedules that start steps by the clock instead of by what finished.

  3. 03

    Backfills

    Any past day, on request

    When a rule changes, rebuild the days it affects from the raw copy.

    rules that only apply to new data.

  4. 04

    Every run recorded

    What ran, how many rows, how long

    Each run leaves a record per step, so “why is this number different?” starts with facts.

    logs that say “success” and nothing else.

  5. 05

    Someone told

    The owner, with the failed step

    A failed or late run reaches the person who can fix it, before the report goes out.

    alerts sent to a shared inbox nobody reads.

Three nights

The same pipeline on three different nights.

Run · Sep 30

  1. extract 3 sources

    01:00–01:06

    done

  2. load raw

    2,914 rows

    done

  3. staging

    0 unmapped values

    done

  4. model

    jobs, revenue, customers

    done

  5. publish

    01:22

    done

Twenty-two minutes, every step recorded. Reports built at 06:00 use it.

What a pipeline hands off

In

Source extracts
on schedule or on change
Raw copies
kept as delivered
Rules
versioned, in code
Pipeline run
extract → load → transform → publish

Out

Modelled tables
keyed, rebuilt the same way
Run record
rows, timings, failures
Alerts
to the owner, with the failed step

Worth building when the same data moves every day

Good fit

  • Reports depend on data from more than one system, moved on a schedule.
  • Someone fixes broken exports or reruns scripts by hand.
  • Rules change and past numbers need rebuilding.

Not the right fit

  • One system with built-in reports already answers the questions.
  • Data moves once, for a migration. That's a one-off job, not a pipeline.

Where pipelines run

Next to pipelines

Questions about ETL and ELT pipelines

Do we need a big orchestration tool?

Usually not at first. A scheduler that respects dependencies and records each run is enough for a handful of sources; heavier tools come in when the number of sources and steps grows.

How often should it run?

As often as the decisions that use it. Most reporting is fine overnight; anything that drives the working day may need hourly runs or changes as they happen.

What happens when a source changes its format?

The load still lands the raw copy, a check flags the change, and the steps after it wait. Nothing quietly fills with blanks.

Can it run in real time?

Parts can. We use change-by-change loading where a decision needs minutes, and keep the rest on a schedule, because it's simpler to run and check.