Data Engineer Roadmap: A Complete Guide from a Data Engineer Who Built ETL Pipelines at Scale

Expert Insight by

Daniel Abraham Mamudgi

Data Engineer, MS Computer Science

Data Engineering / Healthcare AnalyticsLinkedIn

Daniel has 4+ years of data engineering experience building scalable, cloud-based data pipelines. At Optum (UnitedHealth Group), he designed ETL workflows processing data from 20+ US states, built a JSON-to-Airflow DAG framework saving $120K annually, and integrated Kafka-based real-time streams. Recently completed his MS in Computer Science at University of Illinois Chicago with a 4.0 GPA, where he researched high-performance computing. Recognized twice as 'Talent in Spotlight' for high-impact contributions.

Verified Expert

What is a Data Engineer?

When I joined Optum as a Data Engineering Analyst in 2020, I quickly learned that data engineering is the backbone of any data-driven organization. Data scientists get the headlines, but without reliable data pipelines, they have nothing to work with.

Here's what data engineers actually do day-to-day:

Build Data Pipelines: Extract data from various sources (databases, APIs, files), transform it into usable formats, and load it into destinations like data warehouses or lakes.

Ensure Data Quality: Implement validation, monitoring, and alerting to catch data issues before they impact downstream consumers.

Design Data Architecture: Make decisions about storage formats, partitioning strategies, and processing frameworks that affect performance and cost.

Collaborate Across Teams: Work with analysts to understand data needs, with DevOps on deployment, and with business stakeholders on requirements.

The role sits at the intersection of software engineering and data management. You need coding skills, but also understanding of data modeling, distributed systems, and cloud infrastructure.

The Data Engineer Roadmap: 4 Stages

Based on my journey from entry-level to leading critical data initiatives, here's the realistic roadmap for data engineering career progression.

The timelines are guidelines, not rules. I've seen engineers reach mid-level in 18 months through intense project exposure, and others stay junior for 4+ years by avoiding challenging work.

The acceleration secret: Volunteer for the hard problems. When a messy data integration project comes up that nobody wants, take it. Those are the projects that build real skills.

Stage 1: Foundation (0-6 Months)

Whether you're in a bootcamp, self-learning, or just starting your first job, the foundation stage is about building core competencies that everything else rests on.

Python for Data Engineering

Python is the lingua franca of data engineering. You need proficiency in:

Data Manipulation:

• Pandas for tabular data processing
• Working with JSON, CSV, Parquet files
• API interactions with requests library
• File system operations

Scripting and Automation:

• Writing maintainable, production-quality code
• Error handling and logging
• Configuration management
• Unit testing basics

# Example: A simple ETL pattern you'll use constantly
import pandas as pd
from pathlib import Path
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def extract(source_path: str) -> pd.DataFrame:
    """Extract data from source file."""
    logger.info(f"Extracting from {source_path}")
    return pd.read_csv(source_path)

def transform(df: pd.DataFrame) -> pd.DataFrame:
    """Apply business transformations."""
    logger.info(f"Transforming {len(df)} rows")
    # Clean, validate, enrich
    df = df.dropna(subset=['id'])
    df['processed_at'] = pd.Timestamp.now()
    return df

def load(df: pd.DataFrame, target_path: str) -> None:
    """Load data to destination."""
    logger.info(f"Loading {len(df)} rows to {target_path}")
    df.to_parquet(target_path, index=False)

# This pattern scales from scripts to production pipelines

SQL Fundamentals

SQL is non-negotiable. At Optum, I wrote complex queries daily against massive healthcare datasets.

Essential skills:

• JOINs (inner, left, right, full, cross)
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER)
• CTEs (Common Table Expressions) for readable queries
• Subqueries and correlated subqueries
• Aggregations and GROUP BY with HAVING
• Query optimization basics (indexes, explain plans)

-- Example: Window functions are essential for data engineering
WITH member_activity AS (
    SELECT 
        member_id,
        activity_date,
        activity_type,
        ROW_NUMBER() OVER (
            PARTITION BY member_id 
            ORDER BY activity_date DESC
        ) as recency_rank,
        COUNT(*) OVER (
            PARTITION BY member_id
        ) as total_activities
    FROM member_activities
    WHERE activity_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT *
FROM member_activity
WHERE recency_rank = 1;  -- Latest activity per member

Basic ETL Concepts

Understand the fundamentals before diving into tools:

• Batch vs. Streaming: When to use each, tradeoffs
• Data formats: CSV, JSON, Parquet, Avro — pros and cons
• Data modeling basics: Star schema, snowflake, normalization
• Idempotency: Why pipelines must be re-runnable safely

Stage 2: Junior Data Engineer (6-18 Months)

As a junior data engineer, you're contributing to production systems under guidance. This is where you learn what "production-grade" really means.

Cloud Platform Basics

Pick AWS or Azure and learn it properly. At Optum, I worked primarily with AWS initially, then expanded to Azure for specific projects.

AWS essentials:

• S3: Object storage (your data lake foundation)
• Redshift: Data warehouse for analytics
• Glue: Managed ETL service
• Athena: Serverless SQL queries on S3
• Lambda: Serverless compute for lightweight processing

Azure essentials:

• ADLS Gen2: Data lake storage
• Databricks: Unified analytics platform
• Data Factory: Orchestration and ETL
• Synapse Analytics: Data warehouse and analytics

Orchestration with Apache Airflow

Airflow is the industry standard for pipeline orchestration. At Optum, I built a JSON-to-Airflow DAG code generation framework that migrated 35+ pipelines from Talend.

Key Airflow concepts:

• DAGs (Directed Acyclic Graphs) for workflow definition
• Operators (Python, Bash, cloud-specific)
• Task dependencies and parallelism
• XComs for passing data between tasks
• Connections and Variables for configuration
• Sensors for waiting on external conditions

# Example: Basic Airflow DAG structure
from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.amazon.aws.operators.s3 import S3CopyObjectOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'data_engineering',
    'retries': 3,
    'retry_delay': timedelta(minutes=5),
}

with DAG(
    'daily_data_pipeline',
    default_args=default_args,
    schedule_interval='@daily',
    start_date=datetime(2024, 1, 1),
    catchup=False,
) as dag:
    
    extract_task = PythonOperator(
        task_id='extract_data',
        python_callable=extract_from_source,
    )
    
    transform_task = PythonOperator(
        task_id='transform_data',
        python_callable=apply_transformations,
    )
    
    load_task = PythonOperator(
        task_id='load_to_warehouse',
        python_callable=load_to_redshift,
    )
    
    extract_task >> transform_task >> load_task

Data Quality Fundamentals

This is where junior engineers often stumble. Production data is messy. At Optum, I built a Python-based data quality tool that automated monthly QA checks, reducing manual validation effort by 80%.

Data quality dimensions:

• Completeness: Are required fields populated?
• Accuracy: Does the data reflect reality?
• Consistency: Does data match across sources?
• Timeliness: Is data fresh enough for use cases?
• Uniqueness: Are there unexpected duplicates?

Practical implementation:

• Add validation steps to every pipeline
• Set up alerting for anomalies (row count drops, null spikes)
• Document expected data contracts
• Build self-healing mechanisms where possible

What Junior Engineers Should Focus On

Do:

• Write clean, documented code
• Ask questions when requirements are unclear
• Take ownership of assigned pipelines
• Learn from code reviews
• Understand the business context of your data

Don't:

• Over-engineer solutions before understanding requirements
• Skip testing because "it works locally"
• Ignore monitoring and alerting
• Stay in your comfort zone

Stage 3: Mid-Level Data Engineer (18-36 Months)

The jump from junior to mid-level is less about learning new tools and more about thinking differently. You're no longer just implementing — you're designing.

Architectural Thinking

Mid-level engineers make architectural decisions that affect performance, cost, and maintainability.

Questions you should be asking:

• What's the data volume today? In 6 months? In 2 years?
• What are the latency requirements?
• How will this integrate with existing systems?
• What happens when this fails?
• What's the cost at scale?

At Optum, I led the design of transformation workflows for the Optum Care Delivery platform. This required thinking beyond single pipelines to how data flows across the entire organization.

Advanced Spark/PySpark

Spark becomes essential when data volumes exceed what Pandas can handle (typically > 1-10 GB).

Key concepts to master:

• Lazy evaluation and DAG execution
• Partitioning strategies
• Broadcast joins vs. shuffle joins
• Caching and persistence
• Spark SQL and DataFrame API
• Performance tuning (spark.conf settings)

# Example: Optimized PySpark transformation
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, when, broadcast

spark = SparkSession.builder.appName("OptimizedETL").getOrCreate()

# Read with schema enforcement (faster than inference)
df = spark.read.schema(defined_schema).parquet("s3://bucket/raw/")

# Broadcast small dimension table for efficient join
dim_state = spark.read.parquet("s3://bucket/dim/states/")
df_enriched = df.join(
    broadcast(dim_state),  # Broadcast hint for small tables
    df.state_code == dim_state.code,
    "left"
)

# Repartition before expensive operations
df_processed = (
    df_enriched
    .repartition(200, "partition_key")  # Control parallelism
    .transform(apply_business_rules)
    .cache()  # Cache if reused
)

# Write with optimized partitioning
df_processed.write \
    .partitionBy("date", "region") \
    .mode("overwrite") \
    .parquet("s3://bucket/processed/")

Real-Time Data Processing

At Optum, I integrated Kafka-based real-time member identity streams into the data lake. This was a game-changer for enabling centralized, de-duplicated member data across teams.

Streaming fundamentals:

• Apache Kafka for message streaming
• Event-driven architectures
• Exactly-once vs. at-least-once delivery
• Windowing and watermarks
• Stream-batch integration patterns

Leading Projects

Mid-level engineers lead significant initiatives. At Optum, I built the JSON-to-Airflow DAG code generation framework that:

• Migrated 35+ ETL pipelines from Talend
• Reduced average execution time by 20%
• Saved $120K annually in infrastructure and licensing costs

What project leadership looks like:

• Scoping work and breaking it into phases
• Making technical decisions and documenting rationale
• Coordinating with dependent teams
• Handling blockers and escalating appropriately
• Delivering on commitments

Mentoring Junior Engineers

Teaching others is how you solidify your own understanding and demonstrate leadership potential.

Effective mentoring:

• Code reviews that explain the "why"
• Pairing on complex problems
• Creating documentation and runbooks
• Being available without being a bottleneck

Stage 4: Senior Data Engineer (36+ Months)

Senior data engineers shape technical direction and solve the hardest problems. My research at UIC's Electronic Visualization Laboratory, where I contributed to high-performance computing initiatives, gave me exposure to this level of technical thinking.

Technical Strategy

Seniors think beyond individual projects to organizational capabilities:

• What data infrastructure do we need in 2-3 years?
• How do we reduce technical debt while delivering features?
• What build-vs-buy decisions should we make?
• How do we scale the team's capabilities?

Cross-Team Influence

Seniors work across organizational boundaries:

• Aligning data architecture with business strategy
• Building relationships with engineering, analytics, and product teams
• Standardizing practices across the organization
• Representing data engineering in technical decisions

Platform Thinking

Instead of building pipelines, seniors build platforms that enable others to build pipelines:

• Self-service data infrastructure
• Reusable frameworks and templates
• Governance and security patterns
• Observability and debugging tools

Core Skills Deep Dive

Let me break down the skills that matter most, based on what I've actually used in production.

Python for Data Engineering

What to focus on:

• Writing production-quality code (not just scripts that work)
• Understanding performance implications
• Using type hints and static analysis
• Testing your code properly

SQL Mastery

SQL is where most data engineers spend their time. Master these patterns:

Analytical queries:

-- Running totals and comparisons
SELECT 
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as cumulative_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) as daily_change,
    revenue / NULLIF(LAG(revenue) OVER (ORDER BY date), 0) - 1 as pct_change
FROM daily_sales;

Data quality checks:

-- Validate data completeness
SELECT 
    COUNT(*) as total_rows,
    COUNT(DISTINCT member_id) as unique_members,
    SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) as null_emails,
    SUM(CASE WHEN created_at > CURRENT_DATE THEN 1 ELSE 0 END) as future_dates
FROM members
WHERE load_date = CURRENT_DATE;

Distributed Processing (Spark)

Spark is essential when data doesn't fit on a single machine. Key areas:

• Understanding the execution model
• Optimizing shuffles (the expensive operation)
• Partitioning strategies
• Memory management
• Integration with cloud storage

Cloud Platforms: AWS vs Azure

I've built production systems on both. Here's my honest comparison.

AWS Data Engineering Stack

My projects at Optum and personal work used this stack:

Storage: S3 for everything. Cheap, reliable, integrates with everything.

Processing: Glue for managed Spark, EMR for more control, Lambda for lightweight transforms.

Warehouse: Redshift for analytics workloads.

Query: Athena for ad-hoc S3 queries without moving data.

Orchestration: Managed Airflow (MWAA) or self-hosted on EKS.

Azure Data Engineering Stack

My Formula 1 data engineering project used:

Storage: ADLS Gen2 with hierarchical namespace.

Processing: Azure Databricks (excellent Spark experience).

Data Factory: For orchestration and data movement.

Delta Lake: For ACID transactions on the lake.

This follows the medallion architecture (bronze, silver, gold layers) that's becoming standard for lakehouse designs.

Building Your Project Portfolio

Your portfolio demonstrates what you can build. Here's how to create projects that actually impress.

What Makes a Good Portfolio Project

Do include:

• End-to-end pipelines (not just one component)
• Real data sources (APIs, public datasets)
• Data quality checks and monitoring
• Clear documentation and architecture diagrams
• Cloud deployment (not just local)

Don't include:

• Tutorial copy-paste projects
• Projects without data quality considerations
• Unfinished work
• Projects without clear business context

Project Ideas That Demonstrate Skill

1. Real-Time Analytics Pipeline

• Ingest streaming data (Twitter API, stock prices, IoT sensors)
• Process with Kafka + Spark Streaming
• Store in time-series database
• Visualize in dashboard

2. Data Lake with Medallion Architecture

• Raw (bronze) → Cleaned (silver) → Aggregated (gold)
• Implement on AWS or Azure
• Include data quality checks at each layer
• Document the transformation logic

3. ETL Framework with Automation

• Config-driven pipeline generation (like my JSON-to-Airflow work)
• Parameterized for different sources
• Includes logging, alerting, retry logic
• Deployed on cloud with CI/CD

My Portfolio Projects

Cricket Analytics with AWS: Built a cloud-based data pipeline analyzing IPL ball-by-ball data using S3, Glue, and Athena. Transformed complex nested JSON into queryable format through data modeling.

Formula 1 Data Engineering with Azure: Built a pipeline using Databricks and Spark following medallion architecture. Transformed semi-structured data into Delta Lake tables stored in ADLS Gen2, orchestrated with Data Factory.

LLM-Powered Wumpus World Agent: Built an intelligent agent using Claude 3.5 and DeepSeek-R1 to navigate under uncertainty. Engineered dynamic prompts and memory for context-aware decision-making across thousands of simulation trials.

Real Project: Healthcare Data Platform at Optum

Let me walk through a real system I built — the healthcare data standardization platform at Optum.

The Problem

Optum's Care Delivery platform needed to analyze cost efficiency and operational outcomes across multiple business units. The challenge:

• Data from 20+ US states with different formats
• Multiple source systems with inconsistent schemas
• Millions of member records requiring identity resolution
• Strict healthcare compliance requirements (HIPAA)
• Multiple downstream teams depending on reliable data

The Solution Architecture

Source Systems → Extraction Layer → Standardization → Data Lake → Analytics
     ↓                ↓                   ↓              ↓           ↓
  20+ states     Talend/Airflow      Transform      S3/HDFS    Redshift
  Various APIs   Scheduled jobs      Data model     Partitioned  Reports
  Files/DBs      Error handling      Quality checks Gold layer   Dashboards

Key Components I Built

1. 25+ ETL Pipelines for Multi-Source Standardization

Each state had different data formats, field names, and business rules. I designed transformation workflows that:

• Mapped heterogeneous schemas to a unified data model
• Handled edge cases specific to each state
• Maintained data lineage for compliance
• Supported incremental and full loads

2. Python-Based Data Quality Tool

Manual QA was taking days of engineering time monthly. I built an automated tool that:

• Ran configurable validation rules against datasets
• Generated reports highlighting anomalies
• Compared metrics against historical baselines
• Reduced manual validation effort by 80%

3. Kafka Integration for Real-Time Member Data

Member identity data was scattered across systems, leading to duplicates and inconsistencies. I integrated Kafka-based real-time streams that:

• Centralized member master data
• De-duplicated records across sources
• Enabled near-real-time updates
• Improved downstream analytics reliability

4. JSON-to-Airflow Migration Framework

We needed to migrate 35+ pipelines from Talend to Airflow. Instead of manual conversion, I built a framework that:

• Read pipeline definitions from JSON config files
• Generated Airflow DAG code automatically
• Maintained consistency across all pipelines
• Reduced migration time dramatically
• Saved $120K annually in licensing costs

Lessons Learned

Start with data quality, not features. The pipelines that caused the most problems weren't slow or complex — they were the ones that silently produced wrong data.

Build for the team, not yourself. The migration framework worked because it was configurable and documented. Other engineers could use it without my involvement.

Healthcare data is unforgiving. HIPAA compliance isn't optional. Every pipeline needed audit logging, access controls, and data handling documentation.

Automation compounds. The initial investment in the QA tool and migration framework paid dividends every month.

Certifications That Matter

Certifications can accelerate your career, but they're not a substitute for hands-on experience.

Recommended Certifications

AWS:

• AWS Certified Data Engineer – Associate (new, directly relevant)
• AWS Certified Solutions Architect – Associate (foundational)
• AWS Certified Database – Specialty (if database-focused)

Azure:

• Microsoft Certified: Azure Data Engineer Associate (DP-203)
• Microsoft Certified: Azure Fundamentals (AZ-900, start here)

Databricks:

• Databricks Certified Data Engineer Associate
• Databricks Certified Data Engineer Professional

Certification Strategy

When to certify:

• After 6-12 months of hands-on experience
• When job postings in your target role require them
• When you need structured learning for new platforms

Don't certify:

• Before you have practical experience to contextualize the material
• To collect credentials without applying the knowledge
• At the expense of building real projects

Common Mistakes to Avoid

Mistake Deep Dive: Tool Collecting

It's tempting to learn every new tool that appears on Hacker News. Don't.

The problem: Shallow knowledge across many tools is less valuable than deep expertise in core technologies.

The fix: Master Python, SQL, and one cloud platform deeply. Add tools only when you have a specific use case.

Mistake Deep Dive: Avoiding Complex Work

Early in my career, I learned the most from the projects nobody wanted — the messy data integration work with unclear requirements and legacy systems.

The problem: Easy, well-defined tasks don't build problem-solving skills.

The fix: Volunteer for the hard problems. They're uncomfortable, but they're where growth happens.