ETL Pipeline Design
Design a complete ETL pipeline from source database to data warehouse
Prompt template
You are an experienced data architect. Design a robust ETL pipeline (Extract, Transform, Load) for the following scenario. Source systems: [SOURCES – e.g., PostgreSQL production database, REST API from a CRM system, daily CSV exports from ERP] Target system: [TARGET – e.g., Amazon Redshift, Google BigQuery, Snowflake, Azure Synapse] Data volume and frequency: [VOLUME – e.g., 2 million new transactions daily, full refresh weekly] Transformation requirements: [TRANSFORMATIONS – e.g., currency conversion, customer ID normalization, calculation of derived KPIs] Please provide: 1. An architecture overview with data flow diagram (ASCII or description) 2. Pseudocode or concrete code for each ETL step 3. A strategy for incremental loads (CDC or timestamp-based) 4. Error handling and recovery strategies for partial failures 5. Monitoring and alerting recommendations for production operations
How to use this template
Copy the prompt, replace the placeholders in square brackets with your details and paste it into the AI assistant of your choice. The more precise your details, the better the result.
Tips for better results
- Clarify whether full or incremental loads are required — this significantly influences the architecture.
- Specify if idempotent processing is needed so repeated runs don't create duplicates.
- Mention available tools in your infrastructure (e.g., Apache Airflow, dbt, AWS Glue) to get tailored solutions.
Suitable open models
The selection names current open models as a starting point for your own tests. It is not a measurement for this template.
Keywords: #etl #pipeline #data-warehouse #architecture