Enhanced Prompt: Production-Grade ETL Pipeline Architect

🎭 Role

Act as a Senior Data Engineer and Apache Airflow Architect. You specialize in building scalable, idempotent, and highly observable data pipelines using modern best practices (Airflow 2.x+, TaskFlow API, and Infrastructure-as-Code principles).

🌐 Context

We are designing a robust, production-grade ETL framework to be deployed in an enterprise environment. This DAG serves as the gold-standard template for daily data ingestion from heterogeneous sources (PostgreSQL and REST APIs) into a cloud-based data warehouse (Snowflake/BigQuery). The code must prioritize fault tolerance, readability, and operational awareness.

🛠️ Task Instruction

Please generate a production-ready Python script for an Airflow DAG that implements the following requirements:

1. Architecture: Utilize the TaskFlow API (@task decorator) wherever possible. Use TaskGroups to modularize the Extraction, Transformation, and Loading phases.
2. Workflow:
   • Extract: Create distinct tasks for PostgreSQL (using PostgresHook) and REST API (using HttpHook).
   • Transform: Implement a pandas processing logic task that cleans, joins, and aggregates the data.
   • Load: Implement a load task for [TARGET_DATA_WAREHOUSE].
3. Observability & Reliability:
   • Implement on_success_callback and on_failure_callback functions to trigger Slack notifications.
   • Configure retries and retry_delay in the default_args.
   • Define an sla (Service Level Agreement) for the DAG.
4. Data Handling: Implement efficient data passing between tasks using XComs (consider the use of custom XCom backends if data volume is high—provide a comment/note on this).
5. Best Practices: Ensure the code includes proper type hinting, docstrings, and a clean configuration dictionary for environment variables.

⚖️ Constraints & Tone

• Tone: Professional, technical, and pragmatic.
• Code Quality: Follow PEP 8 standards. Avoid hard-coding credentials; use Airflow Connections.
• Avoid: Do not provide generic snippets. Write complete, functional code blocks with descriptive comments explaining why a specific pattern was chosen.

🧩 Variables

• [DAG_ID]: The identifier for the DAG.
• [SCHEDULE_INTERVAL]: The cron expression or timedelta object.
• [SOURCE_DB_CONN_ID]: Connection ID for Postgres.
• [API_CONN_ID]: Connection ID for the REST API.
• [TARGET_DATA_WAREHOUSE]: Snowflake or BigQuery.
• [SLACK_CONN_ID]: Connection ID for Slack notifications.

📝 Output Format

1. High-Level Architecture Overview: A brief summary of the DAG structure and why it is resilient.
2. Implementation: The full Python code for the DAG, including imports and helper functions.
3. Operational Guide: A short list of requirements to configure the Airflow connections and environment variables to make this code work immediately upon deployment.

How to use this:

Simply copy the text above and paste it into ChatGPT or Claude. When you are ready to run it, replace the [VARIABLES] with your specific project details, or instruct the AI to leave them as placeholders so you can fill them in later.