Lab 7.2: convert CSV to partitioned Parquet with local Spark
Run PySpark 3.5 in Docker (the same Spark and Python versions as AWS Glue 5.0) to turn transaction CSV into typed, partitioned Parquet, compare five layouts by size, and discover why partitioning 1,000 rows makes the data bigger, not smaller.
Assumes: Python 3.11 ยท Apache Spark / PySpark 3.5.5 ยท Java 17 ยท pytest 8.4.2 (in Docker) ยท matches AWS Glue 5.0 versions ยท labs/m07-transaction-pipeline/local. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lab you can:
- Run PySpark locally in Docker and explain why local Spark is a faithful stand-in for the Glue transform logic.
- Read raw CSV with an explicit all-string schema, cast it to typed columns (including
DECIMAL(18,2)money) and write Parquet partitioned by year, month and day. - Compare CSV, gzip CSV, partitioned Parquet (Snappy and ZSTD) and single-file Parquet by size, and explain each number.
- Explain the small-files effect and choose a partitioning scheme that suits the data volume.
- Predict what a schema printed from Parquet shows, including the surprising integer type of the partition columns.
Prerequisites
- Lab 7.1 (you need
transactions.csv; the committed sample inlabs/m07-transaction-pipeline/data/samples/is enough) - File formats: CSV, JSON, Parquet and PySpark in Glue for beginners
- Docker Desktop. The first build downloads about 600 MB (Python, Java and Spark)
Cost label
Free. Spark runs in a container on your machine with a 2 GB memory limit. Nothing touches AWS.
Scenario
Harbourline's analysts query transactions in Athena, which bills by the bytes it scans. Raw CSV is the worst format to scan: every query reads every byte of every column. Your task is the first transformation of the project pipeline: read the CSV once, give every column a real type, and write a columnar copy that Athena can read selectively. Before you spend money on a Glue job, you prove the transformation on your laptop.
Architecture
The container is built from local/spark.Dockerfile: Python 3.11 on Debian with Java 17 and pyspark==3.5.5. It does not contain the awsglue libraries, so the job's logic must live in plain PySpark and Python. That is a design rule of this module: keep the transformation testable offline and keep the Glue-specific code (reading sources, bookmarks, committing) in a thin wrapper.
The files
| File | Role in the lab |
|---|---|
local/spark.Dockerfile | Spark 3.5 + Python 3.11 + Java 17 image |
local/docker-compose.yml | Service spark; mounts the lab folder at /lab; 2 GB memory limit; started only on request (profiles: ["tools"]) |
local/csv_to_parquet.py | The conversion and the size report |
glue/tests/test_spark_local.py | The pytest that runs the same functions and asserts exact money |
Part A: build the image and run the conversion
Build the Spark image once
From labs/m07-transaction-pipeline:
docker compose -f local/docker-compose.yml build sparkIt takes a few minutes the first time and is cached afterwards. The image is tagged hl-m07-spark.
Convert the small sample
docker compose -f local/docker-compose.yml run --rm spark python local/csv_to_parquet.py data/samples/transactions.csv local/outThe author's run printed (Spark's progress bars removed):
rows: 1012
layout bytes
csv (raw) 220622
csv_gzip 76158
parquet_snappy 366702
parquet_zstd 332468
parquet_single 108597
root
|-- transaction_id: string (nullable = true)
|-- account_id: string (nullable = true)
|-- booking_date: date (nullable = true)
|-- value_date: date (nullable = true)
|-- amount: decimal(18,2) (nullable = true)
|-- currency: string (nullable = true)
...
|-- created_at: timestamp (nullable = true)
|-- year: integer (nullable = true)
|-- month: integer (nullable = true)
|-- day: integer (nullable = true)Read the numbers before reading the explanation
| Layout | Bytes | Compared with CSV | Why |
|---|---|---|---|
| CSV (raw) | 220,622 | 100% | Text, every byte read for every query |
| gzip CSV (one file) | 76,158 | 35% | Compression helps size, but gzip CSV is not splittable and is still row-based |
| Parquet + Snappy, partitioned | 366,702 | 166% | About 47 day-folders, each with its own tiny file, footer and metadata |
| Parquet + ZSTD, partitioned | 332,468 | 151% | Better compression, same overhead |
| Parquet + Snappy, one file | 108,597 | 49% | Columnar and typed, with a single footer |
The partitioned Parquet is bigger than the CSV it came from. This is not a bug; it is the lesson. With 1,012 rows over 47 days, each partition holds about 20 rows. A Parquet file carries a footer with a schema and statistics for every column, and it stores each column in its own chunk, so a file with 20 rows is mostly overhead. The compression and encoding that make Parquet small need volume to work.
Rules of thumb that come out of this experiment (the target sizes are in S3 data lake design):
- Partition by a column when each partition will hold files of hundreds of megabytes, not kilobytes.
- For small data, use no partition or a coarse one (month), and write a few big files, not many small ones.
- Daily partitions are right for the project's volume (hundreds of thousands of rows a day), not for a 1,000-row lab file. The lab uses them anyway so you can see the effect and practise the layout.
- gzip CSV can look attractive on size alone, but it cannot skip columns and cannot be split for parallel reads.
Look at what was written
ls local/out/parquet_snappy/year=2026/ | head
ls local/out/parquet_snappy/year=2026/month=10/day=20/You will see Hive-style folders (month=10, day=20) and inside them one or more part-*.snappy.parquet files, plus Spark's _SUCCESS marker and .crc checksum files in the output root. Athena and Spark both ignore names starting with an underscore or a dot, but do not upload them to S3 anyway; Lab 7.3 excludes them.
Scale up (optional)
Generate the medium dataset in Lab 7.1 Part C (100,146 transactions), copy output-medium/csv/transactions.csv to labs/m07-transaction-pipeline/data/medium/transactions.csv, and run:
docker compose -f local/docker-compose.yml run --rm spark python local/csv_to_parquet.py data/medium/transactions.csv local/out-mediumThe medium CSV is 21,838,184 bytes, and the generator's own Parquet file for the same rows is 10,379,367 bytes (47.5%). With 100,000 rows over about 200 days each partition has hundreds of rows, so the partitioned layouts are still small files, but the gap to CSV narrows. I did not time the Spark run on the medium file; if the container runs out of its 2 GB, raise mem_limit in the compose file. Do not commit data/medium/ or local/out*/: both are generated.
Part B: read the code
RAW_SCHEMA = T.StructType([T.StructField(name, T.StringType(), True) for name in (
"transaction_id", "account_id", "booking_date", "value_date", "amount", "currency", "type", "channel",
"counterparty_name", "counterparty_iban", "merchant_category_code", "reference", "status",
"idempotency_key", "created_at")])
def read_raw(spark: SparkSession, path: str) -> DataFrame:
# mode=FAILFAST: a malformed line stops the job instead of silently becoming nulls.
return spark.read.csv(path, header=True, schema=RAW_SCHEMA, mode="FAILFAST", encoding="UTF-8")
def to_curated(raw: DataFrame) -> DataFrame:
"""Typed columns + partition keys. Clean input only: Lab 7.4 adds the data-quality gate."""
return (raw
.withColumn("booking_date", F.to_date("booking_date", "yyyy-MM-dd"))
.withColumn("value_date", F.to_date("value_date", "yyyy-MM-dd"))
.withColumn("amount", F.col("amount").cast(T.DecimalType(18, 2)))
.withColumn("created_at", F.to_timestamp("created_at", "yyyy-MM-dd'T'HH:mm:ss'Z'"))
.withColumn("year", F.date_format("booking_date", "yyyy"))
.withColumn("month", F.date_format("booking_date", "MM"))
.withColumn("day", F.date_format("booking_date", "dd")))RAW_SCHEMAโ all 15 columns are strings. This is schema on read, done explicitly: you decide the schema; Spark does not guess it. Inferred schemas turn a column of0001-style codes into integers and lose the zeros.mode="FAILFAST"โ the default (PERMISSIVE) turns a bad line into nulls and carries on. For money, stopping is better than guessing. (Lab 7.4 shows the better design: quarantine the bad rows instead of failing the file.)F.to_date(..., "yyyy-MM-dd")andto_timestampโ convert text to real types with a strict pattern. In a messy file these functions return null for unparsable values, which is exactly why this function says "clean input only".cast(T.DecimalType(18, 2))โ money as a fixed-precision decimal. Never usefloatordoublefor amounts:0.1 + 0.2is not0.3in binary floating point, and a ledger must add to the penny. The test asserts the GBP total is exactly152326.64.year,month,dayas zero-padded strings ("11","05") โ these become the folder names (month=11,day=05), which must match the Athena projection settings (two digits) in Lab 7.3..partitionBy("year", "month", "day")(inmain) โ Spark removes these columns from the files and encodes them in the folder path.coalesce(1)โ reduces the DataFrame to one partition before writing, so one output file. It is cheap on small data and a bottleneck on big data (everything funnels through one task).folder_size()โ sums file sizes but skips names starting with.or_, so checksum and marker files are not counted.
The integer surprise. The schema printout shows year: integer. You wrote strings, so why? When Spark reads a partitioned folder it infers the type of the folder-name values, and 2026 looks like an integer. In Athena you declare the partition columns as string and keep the leading zeros, and Athena does not infer. Know the difference, because a Spark job that reads day=05 and writes it back may produce day=5. Setting spark.sql.sources.partitionColumnTypeInference.enabled to false keeps them as strings โ VERIFY on your Spark version.
Part C: run the automated test
docker compose -f local/docker-compose.yml run --rm spark python -m pytest glue/tests -q -p no:cacheproviderExpected: 18 passed in about 19s. Seventeen of those tests are pure Python (the validator, masking, reports); one is the Spark test test_csv_to_parquet_keeps_every_row_and_exact_money, which calls read_raw and to_curated, writes partitioned Parquet to a temp folder, reads it back and asserts: 1,012 rows, amount has type decimal(18,2), the GBP sum is exactly 152326.64 and the only top-level partition folder is year=2026. Without Docker, pip install -r glue/requirements.txt && python -m pytest glue/tests -q runs the 17 pure-Python tests and skips the Spark test.
Security considerations
โ Learning shortcut โ not for production
The container reads and writes files on your own disk without encryption, and the Parquet output contains the counterparty IBANs in clear text, because this lab stops at format conversion. In the project pipeline the job replaces the IBAN with a keyed token and a masked form before writing, and the output goes to an SSE-KMS bucket. Never convert real customer files on a laptop. Even a "temporary" local copy is an uncontrolled copy of personal data.
- Pin the image's package versions (
pyspark==3.5.5,pytest==8.4.2) so a rebuild cannot silently change behaviour. - The Dockerfile does not run as a non-root user. That is acceptable for a throwaway learning container but not for an image you ship.
FAILFASTis a data-integrity control: silent nulls in money columns are a reconciliation incident waiting to happen.
Common mistakes
- Inferring the schema (
inferSchema=True) โ type surprises and a second pass over the data. Declare the schema. - Using
doublefor amounts โ rounding errors. UseDECIMAL(18,2). - Partitioning by a high-cardinality column (account id) โ millions of tiny folders. Partition by something with tens to hundreds of values.
- Judging Parquet by a 1,000-row experiment โ as you saw, overhead dominates. Test at realistic volume.
- Uploading Spark's
_SUCCESSand.crcfiles to S3 โ harmless but untidy; exclude them. - Writing with
coalesce(1)at 100 GB โ one task does all the work and runs out of memory.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
docker compose ... run says no such service | Wrong folder | Run from labs/m07-transaction-pipeline and keep -f local/docker-compose.yml |
| Build fails fetching packages | No network or proxy | Retry; check Docker Desktop network settings |
| Container killed, exit 137 | Out of memory (2 GB limit) | Raise mem_limit, or use a smaller input |
FileNotFoundError for the CSV | Path is relative to /lab (the lab folder) | Use data/samples/transactions.csv, not an absolute Windows path |
Malformed records are detected in record parsing | FAILFAST met a bad row (you passed a messy file) | Use the clean sample, or see Lab 7.4 for quarantine |
Spark warnings about native-hadoop library and ui | Normal in a local container | Ignore |
local/out already exists and has old data | A previous run | The script uses mode("overwrite"); delete the folder if in doubt |
Cost and cleanup
Free. Remove the generated output and, if you want the disk back, the image.
rm -rf local/out local/out-medium data/medium
docker image rm hl-m07-spark # about 600 MB; rebuilt automatically next time
docker compose -f local/docker-compose.yml down --remove-orphansExercises
- ๐ข Guided: Run the conversion and fill in the table of five layouts from your own output. Compute each as a percentage of the raw CSV and explain, in one sentence each, why the partitioned layouts are larger.
- ๐ก Challenge: Change
to_curatedto partition byyearandmonthonly, and run again. Record the new sizes and the number of files per layout (find local/out/parquet_snappy -name "*.parquet" | wc -l). At what daily row count would you switch back to daily partitions? - ๐ด Live problem: The pipeline writes one Parquet file per partition every ten minutes through the day, and Athena queries have become slow although the data volume is modest. Diagnose the cause from what you learned here, and propose two remedies (one in how files are written, one in how they are read or compacted).
Assignment
Add a short docs/parquet-layout.md to your portfolio repository with your measured table of layouts, a recommendation for your own partitioning scheme at 1,000, 100,000 and 10 million rows per day, and the reasoning. Include the command you used.
Solutions
Your percentages should be close to: gzip CSV 35%, partitioned Snappy about 166%, partitioned ZSTD about 151%, single Parquet about 49%. The partitioned layouts are larger because 47 day-folders each contain a separate file with its own footer, schema and per-column statistics for about 20 rows, so metadata outweighs data. ZSTD is smaller than Snappy because it compresses more aggressively, at slightly higher CPU cost, but it cannot fix per-file overhead.
With year and month only there are two folders (October and November), so there are far fewer files and the partitioned Parquet falls towards the single-file size (still somewhat larger because there are a few files). Switch to daily partitions only when a day's data reaches the tens or hundreds of megabytes. At roughly 100 bytes of compressed Parquet per row, that means on the order of hundreds of thousands to millions of rows per day. For the 100,000-row medium dataset over about 200 days, month partitions are the better fit.
Writing every ten minutes creates thousands of tiny files per day (the small-files problem). Athena opens each file, reads its footer and plans splits per file, so latency grows with file count, not data size. Remedies: (1) write fewer, larger files: buffer longer in Firehose (the buffer interval and size settings) or write hourly or daily; (2) compact: run a periodic Glue or Athena CTAS/INSERT job that rewrites a finished partition into a few 128 MB to 1 GB files, or use an Iceberg table with its compaction. Also use partition projection or a partition index so the planner does not list every folder.
Knowledge check
Answer: Each of about 47 daily partitions got its own Parquet file with a footer, schema and column statistics, and each file held only about 20 rows. Overhead per file outweighed the savings from columnar encoding.
Answer: Binary floating point cannot represent most decimal fractions exactly, so sums drift. A fixed-point decimal keeps pennies exact, which a ledger and reconciliation require.
Answer: It stops the read at the first malformed line instead of turning it into nulls. For a production pipeline that must continue past bad rows, you quarantine the bad rows with reason codes instead (Lab 7.4).
Answer: A gzip file cannot be split, so one task reads it end to end, and being row-based it cannot skip columns. Parquet is splittable by row group and lets the engine read only the needed columns.
Interview questions
- "How would you choose partition columns?" โ Pick the column most queries filter on, with moderate cardinality (tens to hundreds of values), such that each partition holds large files. Date is the usual choice for transactions.
- "What is the small-files problem?" โ Many small objects make engines spend more time on listing, opening and planning than on reading. Fix with larger writes, compaction or table formats with built-in compaction.
- "Why test Spark logic locally before using Glue?" โ It is faster, free and repeatable. Keeping logic in plain PySpark and Python, with Glue-specific calls in a thin wrapper, makes it testable offline.
Certification practice
A table of 5 GB per month is partitioned by year/month/day/hour. Queries filter by month. Query planning is slow and each Parquet file is about 200 KB. What should the engineer do?
A. Add more hourly partitions. B. Partition by month and compact data into larger files. C. Convert the data to CSV. D. Partition by customer id.
Answer: B. Fine-grained partitions produced tiny files. Coarser partitions that match the query filter and larger files fix planning time. A makes it worse, C loses columnar benefits, D has very high cardinality.
Analysts query a 2 TB CSV dataset in Amazon Athena and each query reads only 3 of 40 columns. What change most reduces cost per query?
A. Increase the Athena workgroup concurrency. B. Convert the data to partitioned, compressed Parquet. C. Move the data to S3 Glacier. D. Use a larger query result bucket.
Answer: B. Athena bills by bytes scanned; columnar Parquet lets it read only the needed columns, and partitions let it skip folders. A, D do not change the scan; C makes data unavailable for querying.
Completion checklist
- I built the Spark image and ran the conversion
- I recorded the five layout sizes and can explain each
- I can explain why partitioned Parquet was larger here and when it would not be
- I ran the pytest suite (18 passed in the author's run)
- I removed
local/outand any medium data
Key takeaways
- Parquet's advantages need volume; per-file overhead dominates at 20 rows per file.
- Declare schemas and use
DECIMAL(18,2)for money;FAILFASTbeats silent nulls. - Partition on the column queries filter by, at a granularity that keeps files large.
- A local Spark container with Glue 5.0's versions lets you test transformation logic for free.
- Spark infers partition-column types from folder names; Athena does not.
Further reading
labs/m07-transaction-pipeline/local/csv_to_parquet.pyandglue/tests/test_spark_local.py- Previous: Lab 7.1 ยท Next: Lab 7.3: Athena on CSV versus Parquet
Independent learning resource โ not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Lab 7.1: generate seeded banking transactions, including the messy ones
Use the C# dataset generator to produce 1k, 100k or 1M reproducible Harbourline transactions in CSV, JSON Lines and Parquet, inject deliberate defects with an answer key, and prove with the pipeline's own validator that every defect is found.
Lab 7.3: Athena on CSV versus Parquet, by bytes scanned
Upload the same transactions as CSV and as partitioned Parquet to S3, query both through a capped, encrypted Athena workgroup, and compare the bytes each query scans, with partition projection instead of a crawler.