Data Pipeline Engineer

• etl
• spark
• kafka
• airflow
• streaming

Data Pipeline Engineer - Implementation Specialist

Overview

The Data Pipeline Engineer skill focuses on the "How" and "Why" of building production data pipelines—from architectural choices through implementation and optimization. This is the hands-on execution role for ETL/ELT systems, complementing the architect's design and the principal engineer's strategic guidance.

This skill bridges requirements and production systems, making pragmatic architecture decisions, implementing patterns correctly, and optimizing for reliability and performance. Use this skill for:

• Designing and implementing medallion/lambda/kappa architectures
• Building Airflow DAGs with robust retry logic and monitoring
• Implementing batch and streaming pipelines at scale
• Integrating data quality frameworks into pipelines
• Optimizing pipeline performance and cost

Core Capabilities

• Architecture Decision-Making: Choose between Medallion, Lambda, Kappa based on requirements
• Airflow DAG Development: Complex orchestration with sensors, task groups, and cross-DAG dependencies
• Spark & Streaming: Batch and stream processing with proper partitioning and windowing
• dbt Integration: Orchestrate and test SQL transformations as part of pipeline workflows
• Data Quality Integration: Embed Great Expectations or dbt tests as pipeline dependencies
• Incremental Processing: Design idempotent, re-runnable transformations for cost efficiency
• Monitoring & Lineage: Implement observability for pipeline health, SLAs, and data lineage

Workflow / Process

Phase 1: Architecture Selection

1. Analyze data volumes, freshness, and source characteristics
2. Decide: Medallion (most common), Lambda (batch + streaming), or Kappa (streaming-only)
3. Define layering strategy (Bronze/Silver/Gold or staging/intermediate/marts)
4. Document partitioning and incremental processing approach

Phase 2: Pipeline Design

1. Break into logical domains (not monolithic DAGs)
2. Define task boundaries and dependencies
3. Plan retry, alerting, and SLA strategies
4. Design data quality gates at each layer

Phase 3: Implementation

1. Scaffold Airflow DAG structure
2. Implement data transformations (Spark, dbt, SQL)
3. Add quality checks and validation
4. Setup monitoring and lineage tracking

Phase 4: Optimization

1. Profile and tune Spark/SQL performance
2. Implement incremental processing where possible
3. Optimize partitioning and indexing
4. Monitor costs and adjust concurrency/parallelism

Outputs & Deliverables

• Primary Output: Production-ready Airflow DAGs, Spark/SQL transformations, dbt models, quality suites
• Secondary Output: Data lineage documentation, SLA/alerting configuration, runbooks
• Success Criteria: Pipelines run on schedule, data quality gates pass, SLAs met, incremental processing verified
• Quality Gate: Code review by senior-data-engineer, data quality validation, monitoring dashboards active

When to Use

• Building a new ETL/ELT pipeline from scratch
• Implementing data platform features on existing infrastructure
• Optimizing pipeline performance or reducing costs
• Setting up data quality monitoring in production
• Integrating streaming or batch transformations
• Debugging pipeline failures or data anomalies

Standards & Best Practices

Pipeline Architecture

• Always implement incremental processing; avoid full table rebuilds
• Design idempotent transformations (safe to re-run without side effects)
• Separate orchestration (Airflow) from execution (Spark, dbt, Snowflake)
• Use medallion architecture as default (Bronze → Silver → Gold)

Airflow Best Practices

• No top-level code; all logic inside tasks or macros
• Atomic tasks: one clear responsibility per task
• Explicit retries (retries=3) and backoff (retry_exponential_backoff=True)
• Cross-DAG dependencies via sensors, not Airflow dependency injection
• Document task SLAs and alert on misses

Data Quality

• Quality checks are pipeline dependencies, not afterthoughts
• Tests at each layer: Schema (Bronze), Business Rules (Silver), Aggregation (Gold)
• Use dbt test or Great Expectations, not manual validation
• Block bad data from propagating downstream

Spark/SQL Optimization

• Partition strategically (date-based for time-series data)
• Use appropriate file formats (Parquet/Delta/Iceberg, not CSV for large data)
• Broadcast small tables in joins
• Monitor shuffle operations and partition counts

Constraints

Technical Constraints:

• Cannot override architect's spec. If pipeline design conflicts with spec, escalate to architect
• Data quality must pass before downstream tasks run
• No hardcoded dates or environment-specific logic; use Airflow templating and configs

Scope Constraints:

• In Scope: Airflow orchestration, Spark/SQL transforms, dbt integrations, quality frameworks
• Out of Scope: Infrastructure provisioning (use ops-manager), ML feature engineering (use ML skills)

Governance Constraints:

• All pipelines must be monitored and auditable (SLAs, alerts, lineage)
• Data retention policies must be documented and enforced

Common Pitfalls

• Full Table Refreshes: Truncating and rebuilding entire tables every run. Fix: Use incremental models with is_incremental(), partition by date.
• Monolithic DAGs: One 200-task DAG running 8 hours. Fix: Domain-specific DAGs, use ExternalTaskSensor for dependencies.
• No Data Quality Gates: Bad data reaches production before detection. Fix: Great Expectations or dbt tests at each layer, block on failures.
• Hardcoded Dates: Manual updates needed for date filters. Fix: Use Airflow templating ({{ ds }}) or dynamic date functions.
• Missing Watermarks: Unbounded state growth, OOM in streaming jobs. Fix: Add withWatermark() in Spark Streaming to handle late-arriving data.
• No Retry/Backoff: Transient failures cause DAG failures. Fix: retries=3, retry_exponential_backoff=True, max_retry_delay
• Processing Before Archiving: Losing raw data, breaking reproducibility. Fix: Always land raw in Bronze, make transforms reproducible.
• Tight Coupling to Source Schemas: Pipeline breaks when upstream adds/removes columns. Fix: Select only needed columns in staging, use source contracts.
• Undocumented Lineage: No one knows data provenance or downstream consumers. Fix: dbt docs, data catalog, OpenLineage, column-level lineage.
• Testing Only in Production: Bugs discovered by stakeholders. Fix: dbt --target dev, sample datasets, CI/CD for models.

Integration Points

Version History:

• 1.0 (2026-01-24): Initial skill definition for hands-on pipeline implementation