Build Simple ETL Pipelines in Bash

1. When a Bash ETL pipeline is the right choice

The term ETL pipeline immediately evokes associations with Airflow, Dagster, or expensive managed services. For many everyday data flows, a nightly export from a legacy system, a synchronization between two databases, a reconciliation between an ERP and a shop system, that amount of tooling is disproportionate. An ETL pipeline in Bash with three clearly separated stages, Extract, Transform, and Load, covers exactly this area without needing a scheduler server, a web UI, or a dedicated Python environment.

The critical mistake in small data integrations is packing everything into a single monolithic script that fetches data, transforms it, and loads it at the same time. If the target connection fails mid-processing, it becomes unclear which data has already been loaded and which has not. An ETL pipeline deliberately split into three separate, sequentially runnable scripts makes each stage individually testable, repeatable, and observable.

This article shows how such an ETL pipeline is built in Bash: from the clean separation of the three stages through checkpointing and idempotency to orchestrating multiple pipelines with a central runner script.

2. Extract: reliably fetching data from sources

The extract stage of an ETL pipeline fetches raw data from the source and writes it unchanged into a staging area, typically a directory with a timestamp in the filename. It matters to separate this stage from the transformation: if extraction fails because the source is unreachable, that must not be confused with a transformation error. Both kinds of failure require different reactions, a network problem justifies a retry, a data format error does not.

For database sources, mysqldump or a direct SELECT ... INTO OUTFILE exports the raw data, for APIs, curl with retry logic handles this task. What matters for a robust ETL pipeline is that the extract stage never silently produces an empty or incomplete file, but explicitly aborts on any error before downstream stages continue working with broken data.

#!/usr/bin/env bash
# 01-extract.sh — pull raw data from source, fail loudly on any problem
set -euo pipefail

readonly RUN_ID="$(date +%Y%m%d-%H%M%S)"
readonly STAGING_DIR="/var/etl/staging/${RUN_ID}"
readonly SOURCE_API="https://legacy-erp.internal/api/orders/export"

mkdir -p "$STAGING_DIR"

# Retry logic: 3 attempts, exponential backoff
attempt=1
max_attempts=3
until curl -sf --max-time 60 "$SOURCE_API" -o "${STAGING_DIR}/orders.raw.json"; do
  if (( attempt >= max_attempts )); then
    echo "[ERROR] Extract failed after $max_attempts attempts" >&2
    rm -rf "$STAGING_DIR"
    exit 1
  fi
  echo "[WARN] Attempt $attempt failed, retrying in $((attempt * 5))s..." >&2
  sleep $(( attempt * 5 ))
  attempt=$(( attempt + 1 ))
done

# Fail fast on an empty or truncated result — never pass bad data downstream
file_size="$(stat -c%s "${STAGING_DIR}/orders.raw.json")"
if (( file_size < 10 )); then
  echo "[ERROR] Extracted file suspiciously small ($file_size bytes)" >&2
  exit 1
fi

echo "$RUN_ID" > /var/etl/current_run_id
echo "[OK] Extracted $(jq 'length' "${STAGING_DIR}/orders.raw.json") records to $STAGING_DIR"

The written current_run_id is the key that ties all three stages of the ETL pipeline together without extract, transform, and load needing to know about each other directly. Every stage reads this ID and knows which staging directory to work with.

3. Transform: normalizing and enriching data

The transform stage of an ETL pipeline is where the actual business logic happens: field names get unified, values normalized, missing data filled with default values, and records optionally enriched with additional information from a second source. This stage reads exclusively from the extract stage's staging directory and writes into a separate transform directory, never directly into the target system.

This strict separation allows rerunning the transform stage as often as needed without querying the source again, which is especially valuable for API sources with rate limits. For an ETL pipeline running daily, this means: if you find a bug in the transformation logic, only this one stage gets fixed and rerun against the already existing raw data, without repeating the extract stage.

#!/usr/bin/env bash
# 02-transform.sh — normalize and enrich extracted data
set -euo pipefail

readonly RUN_ID="$(cat /var/etl/current_run_id)"
readonly STAGING_DIR="/var/etl/staging/${RUN_ID}"
readonly TRANSFORM_DIR="/var/etl/transformed/${RUN_ID}"

mkdir -p "$TRANSFORM_DIR"

jq '
  map({
    order_id: .id,
    customer_email: (.email | ascii_downcase),
    total_amount: (.total | tonumber),
    currency: (.currency // "EUR"),
    status: (if .status == "" then "pending" else .status end),
    processed_at: (now | todate)
  })
' "${STAGING_DIR}/orders.raw.json" > "${TRANSFORM_DIR}/orders.normalized.json"

record_count="$(jq 'length' "${TRANSFORM_DIR}/orders.normalized.json")"
echo "[OK] Transformed $record_count records into $TRANSFORM_DIR"

The order of transformations is deliberate: first normalize the email address to lowercase, then coerce number types, then fill missing fields with fallback values. This order prevents a later rule from operating on a value that has not yet been normalized, a common mistake in growing ETL pipeline scripts when rules get added independently of each other.

4. Load: filling the target system transaction safely

The load stage of an ETL pipeline transfers the transformed data into the target system, a database, a data warehouse, or an API. The most critical aspect of this stage is what happens on a partial failure: if two hundred out of a thousand records get loaded and then the connection drops, it must be clearly defined whether the two hundred already loaded records remain in the target system or get rolled back.

For database targets, an explicit transaction is the safest solution: all inserts of a batch run inside a BEGIN/COMMIT block, so an automatic ROLLBACK kicks in on error and the target system never shows a half finished state. For API targets that do not support transactions, the idempotency described in the next section takes on this role, letting a repeated run recognize and skip already loaded records.

#!/usr/bin/env bash
# 03-load.sh — load transformed data into the target database transactionally
set -euo pipefail

readonly RUN_ID="$(cat /var/etl/current_run_id)"
readonly TRANSFORM_DIR="/var/etl/transformed/${RUN_ID}"
readonly DB_NAME="warehouse"

# Build a single transactional SQL script from the JSON records
sql_file="$(mktemp)"
{
  echo "START TRANSACTION;"
  jq -r '.[] | "INSERT INTO orders (order_id, customer_email, total_amount, currency, status) VALUES (\(.order_id), \"\(.customer_email)\", \(.total_amount), \"\(.currency)\", \"\(.status)\") ON DUPLICATE KEY UPDATE status = VALUES(status);"' \
    "${TRANSFORM_DIR}/orders.normalized.json"
  echo "COMMIT;"
} > "$sql_file"

if mysql "$DB_NAME" < "$sql_file"; then
  echo "[OK] Loaded records from run $RUN_ID"
  rm -f "$sql_file"
else
  echo "[ERROR] Load failed, transaction rolled back automatically" >&2
  rm -f "$sql_file"
  exit 1
fi

ON DUPLICATE KEY UPDATE is set deliberately here, so that a repeated run of the same ETL pipeline with the same records does not produce an error due to duplicate primary keys, but updates the existing record instead. That is the core of idempotency, explored further in the next section.

5. Checkpointing: resuming exactly where you left off after a crash

An ETL pipeline that starts completely over on every error wastes time and resources, especially when the extract stage is slow or subject to rate limits. Checkpointing means recording the progress of every stage in a status file, so a rerun after a crash resumes exactly at the last successfully completed stage instead of starting from extract again.

The implementation is simple: after every successfully completed stage, the script writes a status marker to a file, such as extract:done, transform:done. A central runner script (see section seven) checks before every stage whether the corresponding marker is already set, and skips the stage if so. For an ETL pipeline that, for cost reasons or API limits, cannot afford to rerun completely as often as desired, this pattern is indispensable.

#!/usr/bin/env bash
set -euo pipefail

readonly RUN_ID="$1"
readonly CHECKPOINT_FILE="/var/etl/checkpoints/${RUN_ID}.state"

mkdir -p "$(dirname "$CHECKPOINT_FILE")"
touch "$CHECKPOINT_FILE"

is_stage_done() {
  local stage="$1"
  grep -qx "${stage}:done" "$CHECKPOINT_FILE" 2>/dev/null
}

mark_stage_done() {
  local stage="$1"
  echo "${stage}:done" >> "$CHECKPOINT_FILE"
}

run_stage() {
  local stage="$1" script="$2"
  if is_stage_done "$stage"; then
    echo "[SKIP] Stage '$stage' already completed for run $RUN_ID"
    return 0
  fi

  echo "[RUN] Stage '$stage' starting..."
  if "$script" "$RUN_ID"; then
    mark_stage_done "$stage"
    echo "[OK] Stage '$stage' completed"
  else
    echo "[ERROR] Stage '$stage' failed, checkpoint not advanced" >&2
    exit 1
  fi
}

run_stage "extract"   ./01-extract.sh
run_stage "transform"  ./02-transform.sh
run_stage "load"       ./03-load.sh

6. Idempotency: pipelines you can safely rerun

Checkpointing prevents unnecessary repetition of already completed stages, but does not protect against double processing when a stage partially ran through before failing. An idempotent ETL pipeline ensures that a repeated run with the same input data leads to the same end state, regardless of how often it is executed. That is the decisive difference between a pipeline you can confidently kick off again and one where every repeated run produces duplicates.

For the load stage, idempotency is achieved through ON DUPLICATE KEY UPDATE or an equivalent upsert pattern, as shown in the previous example. For API targets without native upsert support, an idempotency key, usually a deterministic ID derived from the source data, takes on the same role: the target API recognizes based on this key whether a request has already been processed, and does not process it a second time.

#!/usr/bin/env bash
set -euo pipefail

# Generate a deterministic idempotency key from stable source fields
generate_idempotency_key() {
  local order_id="$1" customer_email="$2"
  echo -n "${order_id}:${customer_email}" | sha256sum | cut -d' ' -f1
}

while IFS=$'\t' read -r order_id email amount; do
  idempotency_key="$(generate_idempotency_key "$order_id" "$email")"

  curl -sf -X POST "https://api.target-system.com/v1/orders" \
    -H "Idempotency-Key: ${idempotency_key}" \
    -H "Content-Type: application/json" \
    -d "{\"order_id\": \"${order_id}\", \"email\": \"${email}\", \"amount\": ${amount}}" \
    || echo "[WARN] Failed to submit order $order_id" >&2
done < orders.tsv

The idempotency key is deliberately based only on stable source fields, not on a timestamp or a random UUID, because a rerun with the same source data would otherwise produce a different key and tempt the API into reprocessing. This attention to detail is the difference between an ETL pipeline that is truly idempotent and one that merely appears to be so on the surface.

7. Orchestrating multiple stages with a central runner

Once several ETL pipelines need to run in parallel or dependent on each other, for example customer master data before order data, a central runner script pays off, explicitly defining execution order and dependencies. This runner script is not itself a framework, but a simple Bash function that links pipeline names to their dependencies and, on error, stops the entire chain instead of letting downstream pipelines run with incomplete data.

For most use cases, a simple list of pipeline calls in the correct order within a single cron script is enough. Only once real parallelism with different start times per pipeline is needed does switching to a dedicated orchestration tool pay off, going beyond the scope of a Bash ETL pipeline.

8. Observability: knowing where the pipeline currently stands

An ETL pipeline that runs overnight must make its success visible by morning without manual log digging. Every stage should write a structured status entry at the end, at minimum a timestamp, stage name, number of records processed, and success or failure. These status entries can, as described in the first article of this series, be summarized directly into an HTML report that gets sent automatically after every run.

For an ETL pipeline running continuously, processing duration per stage is a particularly valuable metric, because a slowly growing runtime is often an early indicator of growing data volume or a performance problem in the target connection, long before the pipeline actually hits a timeout.

9. Bash ETL compared to Airflow and managed services

The decision between a Bash ETL pipeline and a full orchestration tool depends on complexity, number of pipelines, and team size. For one to a handful of pipelines with simple, linear dependencies, Bash is usually the more pragmatic choice.

For small to medium data integrations with a clear, linear dependency, a Bash ETL pipeline delivers the same functional benefit as a heavyweight framework, without its operational overhead. Only once you reach double digit pipeline counts with complex dependencies and the need for a web UI for non-technical users does the advantage of a dedicated orchestration tool outweigh the simplicity.

10. Summary

A solid ETL pipeline in Bash deliberately separates extract, transform, and load into three standalone, sequentially runnable scripts, each reading only from the previous stage's staging area. This separation makes each stage individually testable and repeatable, without having to rerun the entire extraction on a bug in the transformation logic. Checkpointing via status files prevents unnecessary repetition of already successfully completed stages after a crash.

Idempotency, implemented via upsert patterns in the database or idempotency keys for API targets, makes an ETL pipeline safely repeatable without producing duplicates. A central runner script orchestrates multiple pipelines with clear dependencies, and structured status entries per stage make the success or failure of every run recognizable at a glance. For small to medium data integrations, this approach delivers the same functional benefit as Airflow, without its operational overhead.

11. FAQ: ETL Pipelines in Bash