Developing ETL Scripts with Claude

1. Why ETL scripts are rarely robust on the first try

A typical ETL script is often created under time pressure: data must move from system A to system B, the logic is written quickly, a test run with a handful of rows works, the script goes into production. The actual problems show up only later, when the script runs a second time and produces duplicates, when a network error in the middle of a run leaves the target table in an inconsistent state, or when an unexpected NULL value crashes the entire transformation.

Claude can considerably reduce this class of problems if you do not simply ask it to "move data from A to B", but explicitly ask about idempotency, error handling and restart capability. The difference between a naive and a robust ETL script rarely lies in the core transformation logic, but in the handling of edge cases: what happens on a duplicate run, what happens on a partial failure, what happens with unexpected data formats. This article shows how to systematically bring these aspects into the prompt, instead of patching them after a production incident.

2. Generating extraction: describing source systems correctly

The extraction step of an ETL script must handle the peculiarities of the source: pagination for APIs, rate limits, incremental versus full extraction. Claude generates usable extraction code when you describe the source concretely, including response format, pagination mechanism and known limits. Without these details, Claude tends to write a naive version that breaks on the first page with more than a thousand records or ignores the source's rate limit.

Especially important for recurring ETL jobs is incremental extraction: instead of reading the complete source on every run, only the data changed since the last run should be queried. Claude reliably generates this logic when you provide a field for the last successful extraction timestamp and explicitly ask for an incremental strategy, instead of implicitly expecting a full extraction.

# ETL extraction step, generated with explicit pagination and rate limit context
import time
import requests
from datetime import datetime, timezone

def extract_orders_incremental(since: datetime, api_key: str) -> list[dict]:
    """
    Extracts orders modified since the given timestamp.

    @param since Timestamp of the last successful extraction run.
    @param api_key Authentication token for the source API.
    @return List of order records as returned by the source system.
    """
    orders: list[dict] = []
    page = 1
    while True:
        response = requests.get(
            "https://source-system.example/api/orders",
            params={"modified_since": since.isoformat(), "page": page, "per_page": 200},
            headers={"Authorization": f"Bearer {api_key}"},
            timeout=30,
        )
        if response.status_code == 429:
            retry_after = int(response.headers.get("Retry-After", "5"))
            time.sleep(retry_after)
            continue
        response.raise_for_status()

        payload = response.json()
        orders.extend(payload["data"])
        if not payload.get("has_more"):
            break
        page += 1

    return orders

# Store the timestamp only after the full pipeline (extract+transform+load)
# succeeds, never right after extraction — a partial failure downstream
# must not advance the incremental watermark.

3. Thinking about idempotency from the start

Idempotency means that a repeated run of the same ETL script with the same input data leads to the same final state instead of creating duplicates. This is the most important property a production ready ETL script needs, because repeats after a failure, a timeout, or a manual re trigger are unavoidable in practice. Without idempotency, every re run potentially causes duplicate rows in the target, which has serious consequences especially for financial or inventory data.

Claude reliably implements idempotency when explicitly required in the prompt, typically via UPSERT operations (INSERT ... ON DUPLICATE KEY UPDATE or ON CONFLICT) instead of plain INSERT statements, or via a unique business key checked before insertion. Without this explicit requirement, Claude sometimes generates simple INSERT logic in short example snippets that produces duplicates on a second run.

-- WRONG: plain INSERT, creates duplicates on every re-run
-- INSERT INTO target_orders (order_ref, customer_id, total)
-- VALUES ('ORD-2026-0512', 4471, 89.90);

-- RIGHT: idempotent UPSERT using the natural business key
INSERT INTO target_orders (order_ref, customer_id, total, updated_at)
VALUES ('ORD-2026-0512', 4471, 89.90, NOW())
ON DUPLICATE KEY UPDATE
  customer_id = VALUES(customer_id),
  total = VALUES(total),
  updated_at = VALUES(updated_at);
-- Requires a UNIQUE constraint on order_ref, established by the schema

-- For PostgreSQL targets, Claude generates the equivalent:
-- INSERT INTO target_orders (order_ref, customer_id, total, updated_at)
-- VALUES ('ORD-2026-0512', 4471, 89.90, NOW())
-- ON CONFLICT (order_ref) DO UPDATE SET
--   customer_id = EXCLUDED.customer_id,
--   total = EXCLUDED.total,
--   updated_at = EXCLUDED.updated_at;

4. Generating transformation logic with clear business rules

The transformation logic of an ETL script is where business rules are implemented: price calculations, status mappings between two systems with different enum values, or merging multiple source fields into a target format. Claude generates this logic most reliably when the business rule is provided as a concrete example table, for example "status A becomes status X, statuses B and C both become status Y", instead of as an abstract description.

A common mistake in manually written transformations is forgetting edge cases: what happens if a source status occurs that is not covered by any of the known mapping rules? On request, Claude generates explicit else branches that clearly log an unknown value and place it into a defined fallback category, instead of silently dropping it or crashing the script.

5. Fault tolerance: handling partial failures without total outage

When processing thousands of records, a single faulty record, for example an unexpected date format or a missing required field, often causes the entire processing to abort in a naive implementation. A robust ETL script catches such individual failures, logs the problematic record with enough context for later analysis, and continues processing the remaining records. Claude reliably generates this pattern when explicitly asked for "graceful degradation per row instead of total failure".

Balance is important here: not every error should be silently skipped. A missing optional field is uncritical, a missing primary key or an obviously corrupt row should stop the entire run, because it points to a structural problem in the source that individual error handling cannot solve. This distinction should be explicitly given to Claude, so the generated error handling is neither too lenient nor too strict.

# Row-level error tolerance: continue processing, log failures with context
from dataclasses import dataclass, field

@dataclass
class TransformResult:
    """Aggregates successful and failed rows from a transformation pass."""
    succeeded: list[dict] = field(default_factory=list)
    failed: list[dict] = field(default_factory=list)

def transform_batch(raw_records: list[dict]) -> TransformResult:
    """Transforms each record independently, isolating per-row failures."""
    result = TransformResult()
    for record in raw_records:
        try:
            transformed = {
                "order_ref": record["order_ref"],          # required, hard fail if missing
                "customer_id": int(record["customer_id"]),  # required
                "total": round(float(record.get("total", 0)), 2),  # optional, default 0
                "notes": record.get("notes"),                # optional, may be None
            }
            result.succeeded.append(transformed)
        except (KeyError, ValueError, TypeError) as exc:
            result.failed.append({"record": record, "error": str(exc)})
            logger.warning(f"Skipping malformed record: {exc}", extra={"record": record})

    if len(result.failed) > len(raw_records) * 0.1:
        # More than 10% failures indicates a structural source problem —
        # stop the whole run instead of silently accepting bad data quality
        raise RuntimeError(f"{len(result.failed)} of {len(raw_records)} records failed, aborting run")

    return result

6. Building data quality checks directly into the pipeline

Instead of checking data quality afterward with a separate verification script, it pays off to integrate quality checks directly into the ETL script. Claude typically generates such checks as a standalone step between transformation and load: does the row count match expectations, do numeric values fall within a plausible range, are required fields actually always populated. If one of these checks fails, the load is blocked instead of silently adopting questionable data.

These built in checks are especially valuable for recurring migrations, because they make regressions immediately visible. If, for example, the source API's response format changes unnoticed, the check aborts with a clear message instead of writing faulty or incomplete data into the target system. The effort for these checks is low with Claude, because the rules can usually be derived directly from the already existing transformation logic.

7. One time data migrations versus recurring ETL jobs

A one time data migration, for example moving a legacy shop into a new system, differs in important ways from a recurring ETL job. For a one time migration, idempotency remains useful for test runs, but there is no need for incremental extraction, while there is a higher need for thorough validation before the final cutover. Claude should explicitly know in the prompt which type of task this is, because the generated structure differs significantly.

For one time migrations, a dry run mode is additionally recommended, where all transformations execute but no write operations are sent to the target system. Claude can build this mode as a flag into the generated script, so a complete test run with real production data is possible without the risk of accidentally altering the target system before the actual cutover is due.

8. Verifying ETL scripts against test data before going live

Every ETL script generated by Claude should be tested against a representative sample of real data before production use, including known edge cases such as NULL values, duplicate keys and unusual characters. A proven approach: ask Claude to generate test cases with exactly these edge cases in addition to the actual ETL code, so the error handling is not just claimed but demonstrably works.

For high risk migrations, a two stage approach pays off: first test against an anonymized copy of the production data, then migrate a limited portion of the real data and spot check it against the source, before starting the full run. This caution costs time, but prevents the considerably more expensive consequences of a faulty full migration.

9. ETL approaches in direct comparison

Depending on the requirement, the properties an ETL script generated by Claude must have differ. The following overview classifies typical scenarios.

In each of these scenarios, the difference does not lie in Claude's ability to write the core code, but in whether the critical property was explicitly required in the prompt. Whoever runs through these requirements as a fixed checklist before every ETL generation significantly reduces the number of production incidents.

10. Summary

Developing ETL scripts with Claude works most reliably when critical properties like idempotency, fault tolerance and data quality checking are explicitly required from the start, instead of being implicitly expected. UPSERT operations instead of plain INSERTs prevent duplicates on repeat runs, row level error handling prevents a single faulty record from stopping the entire processing, and built in quality checks make regressions immediately visible instead of silently adopting them.

The difference between one time data migrations and recurring ETL jobs should be clearly stated in the prompt, because requirements like incremental extraction or dry run mode differ fundamentally. Before every production use, testing against real data samples with known edge cases remains the most important step to build trust in the generated pipeline.

11. FAQ: Developing ETL Scripts with Claude