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

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:

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.

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.

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:

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

• 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.

• 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

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.

• 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

• 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.

• 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%.

• 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?

• 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

• 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

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

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.

• 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).

• 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.

• 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

• 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.

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

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

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

Python for Data Engineering

• 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:

-- 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;

-- 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

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:

Azure Data Engineering Stack

My Formula 1 data engineering project used:

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

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

What Makes a Good Portfolio Project

• 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)

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

Project Ideas That Demonstrate Skill

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

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

• 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

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

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

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%

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

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

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

Recommended Certifications

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

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

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

Certification Strategy

• 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

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

Mistake Deep Dive: Tool Collecting

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

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.