ETL Pipeline Design Pattern

You are a data engineer designing an ETL pipeline architecture for a production environment. Based on the following requirements, create a comprehensive pipeline design.

Data Flow: [SOURCE SYSTEMS TO TARGET SYSTEMS - describe the end-to-end journey]
Processing Volume: [DATA VOLUME AND VELOCITY]
Orchestration Tool: [AIRFLOW/PREFECT/DAGSTER/STEP FUNCTIONS/NONE YET]
Processing Engine: [SPARK/DBT/PANDAS/SQL-BASED]
SLA Requirements: [DATA FRESHNESS REQUIREMENTS AND PROCESSING WINDOWS]
Team Experience: [TEAM SIZE AND SKILL LEVEL WITH DATA ENGINEERING]

Design the ETL pipeline across these six sections:

1. PIPELINE ARCHITECTURE PATTERNS
Select and detail the appropriate ETL architecture pattern for the requirements. Compare ELT versus ETL approaches and recommend the right one based on the target system capabilities. Design the pipeline using a DAG structure with clearly defined stages: extract, validate, transform, load, and verify. Implement the medallion or multi-hop architecture with bronze, silver, and gold layers where each layer has a specific responsibility. Define the contract between pipeline stages including expected schemas, quality guarantees, and SLAs. Create architecture diagrams showing data flow, dependencies, and failure boundaries. Address whether to use micro-batch or true streaming based on the latency requirements.

2. EXTRACTION PATTERNS AND SOURCE MANAGEMENT
Design extraction logic for each source system. Implement incremental extraction using watermark columns such as updated_at timestamps or auto-incrementing IDs to avoid full table scans on every run. For sources without reliable watermarks, implement change data capture or full extraction with downstream deduplication. Handle schema evolution at the source including new columns, type changes, and dropped columns using a schema registry or flexible schema-on-read approach. Build source system abstractions that isolate the rest of the pipeline from source-specific details. Implement extraction monitoring including row count validation, schema drift detection, and source system health checks.

3. TRANSFORMATION DESIGN AND IMPLEMENTATION
Design the transformation logic following software engineering best practices. Implement transformations as pure functions that take input data and produce output data without side effects, making them testable and composable. Define a transformation registry where each transformation has a name, input schema, output schema, and business owner. Use SQL with dbt or Python with pandas or PySpark depending on the use case, and provide templates for common patterns including deduplication, type casting, null handling, slowly changing dimensions, and fact table surrogate key generation. Implement data quality checks between transformation stages using assertion-based testing. Create transformation lineage documentation that shows how every output column is derived from source columns.

4. LOADING STRATEGIES AND IDEMPOTENCY
Design the loading layer to be idempotent so pipelines can be safely rerun without creating duplicates or data corruption. Implement upsert patterns using merge statements for databases or partition overwrite for data lakes. For data warehouses, design staging table patterns where data is loaded into a temporary area, validated, then atomically swapped into the production table. Handle late-arriving data and out-of-order events gracefully. Implement loading strategies appropriate for the target system: bulk loading for warehouses, streaming inserts for real-time systems, and file-based loading for data lakes. Create rollback procedures for failed loads that restore the previous state.

5. ERROR HANDLING AND RECOVERY
Build a comprehensive error handling framework. Categorize errors into transient errors that should be retried such as network timeouts and permanent errors that need human intervention such as schema violations. Implement retry logic with exponential backoff and circuit breaker patterns for external system calls. Create a dead letter queue for records that fail transformation or validation so they do not block the pipeline. Implement checkpoint and restart capabilities so failed pipelines resume from the last successful stage rather than reprocessing everything. Design alerting with appropriate severity levels and routing so critical failures page on-call engineers while minor issues create tickets. Provide runbooks for common failure scenarios.

6. MONITORING, TESTING, AND OPERATIONS
Build the observability layer for the pipeline. Implement structured logging with correlation IDs that trace a record from extraction through loading. Create operational dashboards showing pipeline run history, duration trends, record counts at each stage, and error rates. Implement data freshness SLA monitoring with automated alerts when data is stale. Design the testing strategy covering unit tests for individual transformations, integration tests for stage interactions, and end-to-end tests with representative data samples. Create a CI/CD pipeline for deploying pipeline changes including automated testing, staging environment validation, and blue-green deployment patterns. Establish operational procedures for backfilling historical data, handling schema migrations, and performing pipeline upgrades without downtime.