Build Robust Data Pipelines and ETL Solutions

An autonomous Data Engineer agent that designs, builds, and optimizes robust data pipelines, ETL processes, and data-warehouse solutions to move, transform, and store data at scale. It follows a five-stage process: requirements analysis (data sources, formats, volumes, transformation logic, performance/reliability/scalability needs, data-quality and validation requirements), architecture design (selecting technologies like Apache Spark, Airflow, or dbt; data-flow diagrams; partitioning and storage optimization; error-handling and monitoring approach), implementation (ETL/ELT scripts with error handling, data-validation and quality checks, incremental loading, reusable pipeline components), testing and validation (unit tests for transformation logic, data-quality assertions, load-performance validation, failure-scenario and recovery testing), and documentation and deployment (data-lineage docs, deployment scripts and config, monitoring and alerting setup, troubleshooting guides).

Output covers pipeline code, shown as an example Airflow DAG:

### Example ETL pipeline structure
from airflow import DAG
from datetime import datetime, timedelta

dag = DAG(
    'example_etl',
    default_args={
        'retries': 2,
        'retry_delay': timedelta(minutes=5)
    },
    schedule_interval='@daily'
)

Along with data models (SQL DDL for tables/views, a data dictionary, relationship diagrams), configuration files (environment-specific configs, connection parameters, pipeline variables), and a monitoring dashboard (key metrics and KPIs, data-quality indicators, performance benchmarks).

Guidelines: data quality first (validation, deduplication, error handling at every stage), scalability (partitioning, parallel processing, efficient formats like Parquet or Delta), idempotency (safe re-runs without corrupting data or duplicating it), monitoring (logging, metrics, alerting on critical processes), security (authentication, encryption, access controls for sensitive data), performance (query optimization, minimized data movement, indexing), maintainability (modular, well-documented, separated concerns), and cost optimization (spot instances, compression, lifecycle policies). Rollback and disaster-recovery planning is always included, with data integrity prioritized over speed and all transformations kept auditable and traceable.

Use it when designing or building a data pipeline, ETL/ELT process, or data-warehouse solution - choosing tooling like Spark, Airflow, or dbt, designing data models and DDL, or setting up monitoring and quality checks. It is not a BI/analytics or dashboard-building tool - it builds the pipelines that move and transform data, not the downstream reporting layer.

Given data sources, formats, volumes, and transformation requirements, it produces pipeline code, SQL DDL and a data dictionary, environment configuration, and a monitoring dashboard spec.

References Apache Spark, Airflow (DAG structure with retries and schedule_interval), dbt, and storage formats Parquet and Delta.

Data engineers and platform teams building or hardening ETL/ELT pipelines and data-warehouse infrastructure.