Understanding the ETL Script

Introduction: The Role of the ETL Script

Welcome back! In the previous lesson, you prepared your AWS data lake by uploading sample JSON data and organizing your S3 folders. Now, you are ready to take the next step: transforming your raw data into a format that is easier to analyze and more efficient to store. This is where the ETL (Extract, Transform, Load) script comes in. The ETL script is a key part of your data pipeline. It reads the raw JSON data you uploaded to S3, applies important transformations, and writes the cleaned and optimized data back to S3 in a new format. In this lesson, you will learn how the ETL script works, what transformations it performs, and why these steps are important for building a reliable and high-performance data lake. This understanding will prepare you for running and monitoring ETL jobs in AWS Glue in the next part of the course.

Understanding the ETL Process: Extract, Transform, Load

Before diving into the script details, let's understand what ETL means and why it's essential for data analytics: Extract : This is where we read data from its source. In our case, we're extracting JSON data from your S3 bucket. JSON is great for storing and transmitting data, but it's not optimized for analytics queries. Transform : This is where we clean, modify, and restructure the data. We handle missing values, add new columns for better organization, and prepare the data for efficient querying. Think of this as organizing a messy room - we're making everything neat and putting things where they belong. Load : This is where we write the transformed data back to storage, but in a format that's optimized for analytics. Instead of keeping the data as JSON, we convert it to Parquet format, which is much faster and cheaper to query.

Why Parquet? Understanding the Format Change

You might wonder: "Why not just keep the data as JSON?" Here's why we convert to Parquet: JSON limitations for analytics : JSON stores data row by row, which means to calculate something like "average fine amount," you have to read every single record JSON includes field names repeatedly, wasting storage space JSON doesn't compress well, leading to higher storage costs Querying JSON is slow because analytics tools have to parse text for every operation Parquet advantages : Columnar storage : Parquet stores data column by column. When you want the average fine amount, it only reads that specific column, not entire records Better compression : Similar data values are stored together, allowing for much better compression (often 75% smaller files) Faster queries : Analytics tools like Athena can skip entire chunks of data that don't match your query criteria Schema evolution : Parquet handles changes to your data structure better than JSON Think of it this way: JSON is like storing books by throwing them randomly in a warehouse, while Parquet is like organizing them by subject on specific shelves - much easier to find what you need!

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

The ETL Script We'll Build and Use

ETL scripts are incredibly powerful and flexible - they can perform virtually any data transformation you need. You could use them to join data from multiple sources, perform complex calculations, apply machine learning models, clean messy text data, or restructure entire datasets. The possibilities are endless, and in real-world projects, ETL scripts often contain hundreds of lines of sophisticated data processing logic.

However, for this course, we're starting with a focused script that demonstrates the core ETL concepts you need to master. This script performs the most common and essential transformations that you'll use in almost every data pipeline project.

Here's the complete ETL script that you'll be building and using throughout this course:

Python

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql import functions as F

# Parse job parameters - these tell the script where to find data and where to put results
args = getResolvedOptions(sys.argv, [
    'JOB_NAME',           # Name of this ETL job
    'SOURCE_BUCKET',      # S3 bucket containing raw JSON data
    'SOURCE_KEY',         # Path to JSON files within the bucket
    'TARGET_BUCKET',      # S3 bucket for processed Parquet data
    'TARGET_PREFIX'       # Path where Parquet files will be stored
])

# Initialize AWS Glue and Spark contexts - this sets up the distributed processing environment
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# EXTRACT: Read JSON data from S3
input_path = f"s3://{args['SOURCE_BUCKET']}/{args['SOURCE_KEY']}"
print(f"Reading data from: {input_path}")
df = spark.read.json(input_path, multiLine=True)

# Display the automatically inferred schema
print("Schema detected from JSON data:")
df.printSchema()
print("Sample of raw data:")
df.show(5, truncate=False)

# TRANSFORM: Clean and enhance the data
print("Applying transformations...")

# Handle missing values - fill nulls with sensible defaults
df = df.fillna({
    "fine_amount": 0.0,    # No fine if amount is missing
    "overdue": False       # Not overdue if status is missing
})

print("Transformation complete. Final schema:")
df.printSchema()
print("Sample of transformed data:")
df.show(5, truncate=False)

# LOAD: Write as Parquet files
output_path = f"s3://{args['TARGET_BUCKET']}/{args['TARGET_PREFIX']}"
print(f"Writing Parquet data to: {output_path}")

df.write.mode("overwrite").parquet(output_path)

print("ETL job completed successfully!")
job.commit()

When you run this script, you'll see output showing the data transformation process:

text

Reading data from: s3://your-data-lake-bucket/raw-data/library-data/
Schema detected from JSON data:
root
 |-- borrow_id: string (nullable = true)
 |-- borrow_date: string (nullable = true)
 |-- return_date: string (nullable = true)
 |-- patron_id: string (nullable = true)
 |-- patron_name: string (nullable = true)
 |-- book_id: string (nullable = true)
 |-- book_title: string (nullable = true)
 |-- overdue: boolean (nullable = true)
 |-- fine_amount: double (nullable = true)

Sample of raw data:
+----------+-----------+-----------+---------+------------+--------+---------------------+-------+-----------+
|borrow_id |borrow_date|return_date|patron_id|patron_name |book_id |book_title           |overdue|fine_amount|
+----------+-----------+-----------+---------+------------+--------+---------------------+-------+-----------+
|BRW-0001  |2025-09-14 |2025-09-28 |P-1007   |Grace Miller|B-2006  |Pride and Prejudice  |false  |0.0        |
|BRW-0002  |2025-08-26 |2025-09-09 |P-1005   |Eva Brown   |B-2007  |The Catcher in the Rye|false |0.0        |
+----------+-----------+-----------+---------+------------+--------+---------------------+-------+-----------+

Applying transformations...
Transformation complete. Final schema:
root
 |-- borrow_id: string (nullable = true)
 |-- borrow_date: string (nullable = true)
 |-- return_date: string (nullable = true)
 |-- patron_id: string (nullable = true)
 |-- patron_name: string (nullable = true)
 |-- book_id: string (nullable = true)
 |-- book_title: string (nullable = true)
 |-- overdue: boolean (nullable = true)
 |-- fine_amount: double (nullable = true)

Sample of transformed data:
+----------+-----------+-----------+---------+------------+--------+---------------------+-------+-----------+
|borrow_id |borrow_date|return_date|patron_id|patron_name |book_id |book_title           |overdue|fine_amount|
+----------+-----------+-----------+---------+------------+--------+---------------------+-------+-----------+
|BRW-0001  |2025-09-14 |2025-09-28 |P-1007   |Grace Miller|B-2006  |Pride and Prejudice  |false  |0.0        |
|BRW-0002  |2025-08-26 |2025-09-09 |P-1005   |Eva Brown   |B-2007  |The Catcher in the Rye|false |0.0        |
+----------+-----------+-----------+---------+------------+--------+---------------------+-------+-----------+

Writing Parquet data to: s3://your-data-lake-bucket/processed-data/library-data/
ETL job completed successfully!

Notice how the script handled missing values properly.

ETL Script Workflow and Key Transformations

Now let's break down exactly what this script does, step by step, and understand why each transformation matters:

Step 1: Setup and Configuration The script starts by setting up the AWS Glue environment and reading configuration parameters that tell it where to find input data and where to write output data. This makes the script flexible - you can run it against different datasets without changing the code.

Step 2: Extract - Reading the JSON Data with Schema Inference The script connects to your S3 bucket and reads the JSON files using spark.read.json(input_path, multiLine=True). The multiLine=True option matters here because the sample input is stored as a JSON array rather than newline-delimited JSON. Here's where something powerful happens: schema inference. Instead of manually telling the script "this field is text, this field is a number," the script automatically figures this out by examining your data. This makes your pipeline flexible - if you add new fields to your JSON data later, the script will handle them automatically.

Step 3: Transform - Cleaning and Enhancing the Data This is where the magic happens. Our script performs a critical transformation:

Missing Value Handling: Real-world data is messy. Sometimes a library book doesn't have a fine amount recorded, or the overdue status is missing. Our script fills these gaps:

Missing fine_amount becomes 0.0 (no fine)
Missing overdue becomes False (not overdue)

This prevents errors in later analysis and ensures consistent data quality.

Step 4: Load - Writing Parquet Files The script writes the cleaned data back to S3 in Parquet format. All the data is written to the specified output path, without partitioning into subfolders.

Once you master these fundamental transformations, you'll have the foundation to build much more complex ETL processes. You could extend this script to merge customer data with transaction data, calculate rolling averages, detect anomalies, or perform any other data transformation your business requires.