Building a Scalable ETL Pipeline with AWS Glue (CSV to Parquet + Partitioning)

Why I Started Looking Into AWS Glue

At work I handle data at a size I had not dealt with before. Terabytes, and more arriving every day. I can’t go into specifics about where it comes from, but keeping that much of it queryable takes real work.

Working at that size pushed me to learn how large pipelines are actually built. AWS Glue kept coming up as the tool people reach for, so I sat down and built something with it instead of only reading the docs.

I used a sales dataset from Kaggle (Chocolate Sales Dataset 2023-2024) to recreate the core patterns I see at work, only smaller and shareable. This post is that walkthrough.

CSV in, partitioned Parquet out

The plan was simple. Take raw CSV files, turn them into something friendlier to query, and make them reachable from Athena. The flow looks like this:

graph LR
    A[S3 raw CSV] --> B[Glue Crawler]
    B --> C[Data Catalog]
    C --> D[Glue Job PySpark]
    D --> E[S3 Parquet]
    E --> F[Athena]

There is nothing clever in it, which is mostly the point. This is roughly the shape of any real data lake.

Step 1: Getting the Raw Data Into S3

First, upload the CSV files to S3. I kept the folder layout tidy from the start. The S3 layout decides what your data model ends up looking like, and a messy one is expensive to fix later.

raw/
├── sales/
├── products/
├── stores/
├── customers/
└── calendar/

One folder, one schema, one CSV file per folder. Keep files with different structures out of the same directory, otherwise the Crawler registers one table with a mix of columns.

Step 2: Let Glue Crawler Figure Out the Schema

Rather than writing every column definition by hand upfront, I pointed Glue Crawler at the S3 folders and let it auto-detect the schema. It took a few minutes to run, and it registered all the tables into the Data Catalog.

My Crawler config was:

• Data source: s3://<bucket>/raw/
• Crawl subfolders: yes
• Database: choco_db
• Table prefix: raw_

After the crawl, I had these tables registered:

raw_sales
raw_products
raw_stores
raw_customers
raw_calendar

That makes the raw data queryable from Athena straight away, which is handy when you want to check something before transforming it.

One thing I noticed: the Crawler gets data types wrong sometimes. Columns that look like numbers but are actually IDs get read as numbers, and dates are hit or miss. Check the inferred schema yourself before you build anything on it.

Step 3: The Actual ETL: PySpark on Glue

This is the core part. The Glue Job reads from the Data Catalog, does some light transformations, and writes Parquet back to S3, partitioned by year and month.

The code came out cleaner than I expected for PySpark:

from awsglue.context import GlueContext
from pyspark.context import SparkContext
from pyspark.sql.functions import col, to_date, year, month

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session

# Read from Data Catalog
datasource = glueContext.create_dynamic_frame.from_catalog(
    database="choco_db",
    table_name="raw_sales"
)

df = datasource.toDF()

# Parse dates and extract partition columns
df = df.withColumn("order_date", to_date(col("order_date"), "yyyy-MM-dd"))
df = df.withColumn("year", year(col("order_date")))
df = df.withColumn("month", month(col("order_date")))

# Write as partitioned Parquet
df.write \
    .mode("overwrite") \
    .partitionBy("year", "month") \
    .parquet("s3://<your-bucket>/processed/sales_parquet/")

The .partitionBy("year", "month") line does most of the work. Spark takes those column values and lays the output out in subfolders for you.

Step 4: What the Output Looks Like in S3

After the job finishes, the folder structure in S3 becomes:

processed/sales_parquet/
├── year=2023/
│   ├── month=1/
│   ├── month=2/
│   └── ...
├── year=2024/

This is where partitioning earns its keep. Ask for the sales from January 2023 and Athena does not scan the whole dataset. It reads the year=2023/month=1/ folder and stops. On a large dataset that saves query time and money.

It looks obvious in hindsight. It was not obvious to me when I was starting out with data engineering.

Step 5: Manually Defining the Schema in Athena

The Crawler can detect schemas for me, but for the processed tables I define them by hand. I would rather have the control, and hunting a type mismatch at query time is a waste of an afternoon.

CREATE EXTERNAL TABLE processed_sales_parquet (
  order_id STRING,
  order_date DATE,
  product_id STRING,
  store_id STRING,
  customer_id STRING,
  quantity BIGINT,
  unit_price DOUBLE,
  discount DOUBLE,
  revenue DOUBLE,
  cost DOUBLE,
  profit DOUBLE
)
PARTITIONED BY (
  year INT,
  month INT
)
STORED AS PARQUET
LOCATION 's3://<your-bucket>/processed/sales_parquet/';

Then you need to run this to get Athena to recognize the existing partitions:

MSCK REPAIR TABLE processed_sales_parquet;

Without that, Athena shows an empty table while the data sits right there in S3, fine and invisible.

Step 6: Querying With Athena

Basic count check to confirm everything loaded correctly:

SELECT COUNT(*) FROM processed_sales_parquet;

Then a partition-aware query to verify the filtering actually works:

SELECT COUNT(*)
FROM processed_sales_parquet
WHERE year = 2023 AND month = 1;

If those two return different numbers, partitioning is working.

What I Took Away From This

Auto schema detection is helpful, but fragile. I would use the Crawler to explore new data quickly, not to define a production schema. It tends to infer partition columns as strings when they should be integers, which breaks partition pruning.

Your S3 folder structure ends up being your query strategy. I did not appreciate that until I saw how directly it changed query performance. Think about how you will query the data before you decide how to partition it.

Parquet is a big deal. I already knew it was better than CSV for analytics, but seeing faster queries and smaller files in practice is what made it stick. If you are building anything analytics-related, there is no good reason to stay on CSV.

Glue Jobs are expensive if you do not configure them. DPUs add up. For this small project it was fine, but in a real workload you would tune the worker count, and it matters whether the job runs on G.1X or G.2X workers. I still do not have a good formula for picking that up front. You mostly find out by watching a job run and looking at the bill.

What’s Next

Joining the five tables is the next step: sales against products, customers, stores and calendar. Once that is done you have a dataset you can point a dashboard at, or feed into a model.

I’ll write that up in the next post.