Claude/ChatGPT Prompt to Architect a Production ETL/ELT Data Pipeline

What this prompt does

This prompt generates a complete, runnable data pipeline specification and code by translating seven distinct engineering concerns — extraction frequency, transformation rules, load strategy, retry logic, quality gates, monitoring hooks, and backfill support — into a single coherent implementation. The template's numbered structure mirrors how a senior data engineer actually scopes a pipeline, which is why the output tends to be architecturally sound rather than a loose collection of snippets.

The [pipeline_type] variable (ETL vs ELT, streaming vs batch, CDC vs full-load) drives the entire shape of the output. Changing it from "batch ETL" to "streaming ELT" shifts the generated code from a scheduled extraction pattern to an event-driven ingestion pattern with in-warehouse transformation. The template encodes that decision upfront so the AI does not have to guess.

What makes this work in practice is that the prompt forces specificity on the hardest parts: [retry_strategy] (exponential backoff? dead-letter queue?), [quality_rules] (null checks, referential integrity, schema drift detection?), and [load_strategy] (upsert, append-only, SCD Type 2?). Vague prompts produce vague pipelines. This template eliminates that.

When to use it

• Building a nightly ETL from a SaaS CRM (Salesforce, HubSpot) into a data warehouse like BigQuery or Redshift.
• Migrating from a legacy batch pipeline to a CDC-based streaming pipeline with Kafka or Debezium.
• Setting up an ELT pipeline where raw data lands in Snowflake and dbt handles transformations.
• Designing a multi-source ingestion pipeline that merges API responses, flat files, and database exports.
• Adding backfill capability to an existing pipeline that was built without historical replay support.
• Prototyping a pipeline architecture before handing specs to a data engineering team.

Example output

For pipeline_type=batch ELT, data_source=PostgreSQL orders table, source_format=CDC via pglogical, destination=Snowflake, volume=500K rows/day, frequency=hourly, tools=Airbyte + dbt + Airflow:

# Airflow DAG: orders_elt_pipeline

extract_task → raw_load_task → dbt_transform_task → quality_gate_task → alert_task

# dbt model: stg_orders.sql
SELECT order_id, customer_id, total_amount,
       COALESCE(status, 'unknown') AS status,
       _airbyte_emitted_at AS ingested_at
FROM {{ source('raw', 'orders') }}
WHERE _airbyte_emitted_at > '{{ var("start_ts") }}'

# Quality gate: Great Expectations checkpoint
- orders.order_id: not_null, unique
- orders.total_amount: between(0, 100000)
- Row count delta < 20% vs prior hour

# Monitoring: Airflow on_failure_callback → Slack webhook
def alert_on_failure(context):
    send_slack_message(
        channel="#data-alerts",
        text=f"Pipeline failed: {context['task_instance'].task_id}"
    )

# Retry: 3 attempts, exponential backoff (60s, 180s, 540s)
# Backfill: airflow dags backfill -s 2026-01-01 -e 2026-06-01 orders_elt_pipeline

Pro tips

• Set [load_strategy] to upsert (MERGE) rather than truncate-reload for tables over 1M rows — the prompt will generate the appropriate MERGE SQL or on_schema_change dbt config instead of a naive overwrite.
• For [quality_rules], list at least one schema-drift check (unexpected new columns) alongside value checks. Schema drift is the silent killer in API-sourced pipelines and the prompt handles it if you name it.
• Pair [retry_strategy] with a dead-letter queue destination — write it as exponential backoff with DLQ to S3/GCS. The generated code will separate poison-pill records from retriable transient failures.
• When using [tools] with dbt, specify dbt-core vs dbt Cloud — the generated scheduler integration and profiles.yml structure differ materially between the two.
• For backfill scenarios, add [frequency]=event-driven + daily historical backfill to get both the streaming trigger logic and a separate Airflow DAG for historical replay in one output.