Design a Scalable ETL Pipeline Architecture

You are a senior data engineer specializing in ETL (Extract, Transform, Load) pipeline design. Help me design a comprehensive ETL pipeline for the following scenario: **Data Context:** - Source systems: [LIST_SOURCE_SYSTEMS, e.g., PostgreSQL, REST APIs, CSV files] - Target data warehouse/lake: [TARGET_PLATFORM, e.g., Snowflake, BigQuery, Redshift] - Estimated data volume: [DATA_VOLUME, e.g., 5 million rows/day] - Update frequency: [FREQUENCY, e.g., hourly, daily, real-time] - Primary business use case: [USE_CASE, e.g., customer analytics, financial reporting] **Please provide the following in your design:** 1. **Architecture Overview:** Describe the end-to-end pipeline architecture, including extraction strategy (full vs. incremental), recommended orchestration tool (e.g., Airflow, Prefect, dbt), and data flow diagram description. 2. **Extraction Layer:** Detail how data should be extracted from each source system, including connection methods, change data capture (CDC) strategies, and handling of API rate limits or file polling. 3. **Transformation Layer:** Outline the transformation steps including data cleansing rules, deduplication logic, schema mapping, data type standardization, and any business logic transformations. Specify whether transformations should happen in-flight (ELT) or in a staging area (ETL). 4. **Loading Strategy:** Recommend the loading pattern (upsert, append, overwrite), partitioning strategy, and indexing recommendations for the target platform. 5. **Error Handling & Monitoring:** Define retry logic, dead-letter queue design, data quality checks (row counts, null checks, referential integrity), alerting mechanisms, and logging standards. 6. **Scalability & Performance:** Provide recommendations for parallelism, batching strategies, memory optimization, and how the pipeline should handle 10x data growth. 7. **Sample Code Skeleton:** Provide a Python or SQL pseudo-code skeleton for the most complex transformation step. Format the output with clear headings, bullet points, and include a summary table of tools and technologies recommended.