The shape of a real ETL job

Welcome! I'm Param, and this is the AWS entry point of the learnwithparam data engineering track. We are going to take a real flight dataset, write a PySpark transform once, run it locally for free, and ship the same code to AWS Glue via CodeBuild. Everything in this course grounds in a real companion project: one PySpark transform, one config, one build pipeline, built and shipped step by step.

An ETL job is three jobs glued together. Extract pulls from a source you do not own, transform reshapes what you got into something queryable, and load lands the result somewhere a dashboard can read. The hard part is not any single step. It is making sure the three steps stay decoupled so any one of them can fail and retry without poisoning the others.

The three-box ETL shape

Source CSV in S3 lands raw, gets transformed by Glue PySpark, and writes curated Parquet back to S3, partitioned for downstream queries.

A script works on day one. By month three you need retries, idempotency, partitioned outputs, schema versioning, and a record of which run produced which file. That is when teams reach for an orchestrator and a managed runtime. Glue handles the runtime side. Airflow handles the orchestration side. Together they replace the script.

Quiz: Quiz

Loading practice…

datasets/flights.csv (sample)

python

id,year,month,day,dep_time,sched_dep_time,dep_delay,arr_time,sched_arr_time,arr_delay,carrier,flight,tailnum,origin,dest,air_time,distance,hour,minute,time_hour,name
1,2013,1,1,517,515,2,830,819,11,UA,1545,N14228,EWR,IAH,227,1400,5,15,2013-01-01T05:00:00,United Air Lines Inc.
2,2013,1,1,533,529,4,850,830,20,UA,1714,N24211,LGA,IAH,227,1416,5,29,2013-01-01T05:00:00,United Air Lines Inc.
3,2013,1,1,542,540,2,923,850,33,AA,1141,N619AA,JFK,MIA,160,1089,5,40,2013-01-01T05:00:00,American Airlines Inc.
4,2013,1,1,544,545,-1,1004,1022,-18,B6,725,N804JB,JFK,BQN,183,1576,5,45,2013-01-01T05:00:00,JetBlue Airways
5,2013,1,1,554,600,-6,812,837,-25,DL,461,N668DN,LGA,ATL,116,762,6,0,2013-01-01T06:00:00,Delta Air Lines Inc.

The bundled dataset has 336K rows of real airline data. Some columns are dirty, timestamps are split across year/month/day, and not every flight has a recorded delay.

AI prompt: Try it: classify your team's ETL stages

Loading practice…

The shape of a real ETL job

The three-box ETL shape

Source CSV in S3 lands raw, gets transformed by Glue PySpark, and writes curated Parquet back to S3, partitioned for downstream queries.

Quiz: Quiz

Loading practice…

datasets/flights.csv (sample)

python

id,year,month,day,dep_time,sched_dep_time,dep_delay,arr_time,sched_arr_time,arr_delay,carrier,flight,tailnum,origin,dest,air_time,distance,hour,minute,time_hour,name
1,2013,1,1,517,515,2,830,819,11,UA,1545,N14228,EWR,IAH,227,1400,5,15,2013-01-01T05:00:00,United Air Lines Inc.
2,2013,1,1,533,529,4,850,830,20,UA,1714,N24211,LGA,IAH,227,1416,5,29,2013-01-01T05:00:00,United Air Lines Inc.
3,2013,1,1,542,540,2,923,850,33,AA,1141,N619AA,JFK,MIA,160,1089,5,40,2013-01-01T05:00:00,American Airlines Inc.
4,2013,1,1,544,545,-1,1004,1022,-18,B6,725,N804JB,JFK,BQN,183,1576,5,45,2013-01-01T05:00:00,JetBlue Airways
5,2013,1,1,554,600,-6,812,837,-25,DL,461,N668DN,LGA,ATL,116,762,6,0,2013-01-01T06:00:00,Delta Air Lines Inc.

The bundled dataset has 336K rows of real airline data. Some columns are dirty, timestamps are split across year/month/day, and not every flight has a recorded delay.

AI prompt: Try it: classify your team's ETL stages

Loading practice…