Transforming Data Formats: CSV to Apache Parquet

This study guide focuses on the technical patterns and AWS services used to transform data from row-based formats (like CSV and JSON) to optimized columnar formats (like Apache Parquet and ORC), a critical task for cost and performance optimization in the AWS Certified Data Engineer - Associate exam.

Learning Objectives

Compare row-based (CSV) and columnar (Parquet) storage characteristics.
Select the optimal AWS service (Glue, Lambda, Firehose) based on latency and volume requirements.
Explain the impact of format transformation on downstream query performance (Athena/Redshift).
Implement transformation workflows using S3 Event Notifications and Glue Jobs.

Key Terms & Glossary

Apache Parquet: A columnar storage format that optimizes analytical queries by reading only the columns requested.
Columnar Storage: A method of storing data where each column's values are stored together, enabling high compression ratios and efficient filtering.
Row-Based Storage (CSV/JSON): Formats that store entire records sequentially, ideal for transactional (write-heavy) workloads but inefficient for large-scale analysis.
SerDe (Serializer/Deserializer): The protocol used to communicate between the data format and the processing engine (e.g., Hive OpenCSV SerDe).
Splittable Format: A file format that can be processed in parallel across multiple nodes (Parquet is splittable; compressed CSV often is not).

The "Big Idea"

In modern data lakes, storage is cheap, but compute (IO) is expensive. Storing data in CSV format forces a query engine like Amazon Athena to read the entire file line-by-line even if you only need one column. Transforming data to Apache Parquet changes the paradigm: the engine only reads the specific bytes on disk corresponding to the columns in your SELECT statement. This reduction in I/O directly translates to faster queries and lower costs (since Athena charges per GB scanned).

Formula / Concept Box

Feature	CSV / JSON (Row-based)	Apache Parquet (Columnar)
Best Use Case	Data Ingestion, Small Datasets	Analytics, Large-scale BI
Query Speed	Slow (scans all columns)	Fast (scans specific columns)
Compression	Poor / Average	High (schema-aware compression)
Athena Costs	High (more data scanned)	Low (less data scanned)
Splittability	Limited (if compressed)	Excellent

Hierarchical Outline

Why Transform Data?
- Cost Efficiency: Athena scans ~90% less data with Parquet vs CSV.
- Query Performance: Parquet stores metadata (min/max values) for faster filtering.
AWS Services for Transformation
- AWS Glue (Batch): Best for high-volume ETL; uses Spark to convert massive CSV datasets.
- Amazon Data Firehose (Streaming): Real-time conversion of incoming JSON streams to Parquet before S3 delivery.
- AWS Lambda (Event-Driven): Ideal for small files (10-100MB) triggered by S3 PutObject events.
- Amazon Redshift: Uses UNLOAD to export query results into Parquet format on S3.
Optimization Techniques
- Compression: Use Snappy with Parquet for the best balance of speed and size.
- Partitioning: Organize S3 paths (e.g., s3://bucket/year=2024/) to further prune data.

Visual Anchors

Data Transformation Workflow

Loading Diagram...

Row vs. Columnar Storage Layout

\begin{tikzpicture} % Row storage \draw[fill=gray!20] (0,0) rectangle (4,0.5); \node at (2,0.25) {Record 1: ID, Date, Amt}; \draw[fill=gray!20] (0,-0.6) rectangle (4,-0.1); \node at (2,-0.35) {Record 2: ID, Date, Amt}; \node at (2,0.8) {\textbf{CSV (Row Storage)}};

code

% Column storage
\draw[fill=blue!10] (6,0) rectangle (7.5,-1.5);
\node at (6.75,-0.75) {All IDs};
\draw[fill=green!10] (7.7,0) rectangle (9.2,-1.5);
\node at (8.45,-0.75) {All Dates};
\draw[fill=red!10] (9.4,0) rectangle (10.9,-1.5);
\node at (10.15,-0.75) {All Amts};
\node at (8.45,0.8) {\textbf{Parquet (Columnar Storage)}};

\end{tikzpicture}

Definition-Example Pairs

Schema Evolution: The ability to change a data structure (e.g., adding a column) without breaking existing files.
- Example: A Glue Crawler detects a new discount_code column in a CSV and updates the Data Catalog, allowing Parquet files to include this new field seamlessly.
Predicate Pushdown: A query optimization where filtering occurs at the storage level rather than in the compute engine.
- Example: Athena reads a Parquet file and uses the internal metadata to skip entire blocks of data that don't match WHERE year = 2024.

Worked Examples

Scenario: Converting S3 CSVs using Lambda

Trigger: User uploads sales.csv to the raw/ bucket.
Automation: S3 Event Notification triggers a Lambda function.
Processing: The Lambda function uses the Pandas or PyArrow library to read the CSV into a DataFrame.
Export: Lambda writes the DataFrame to the processed/ bucket as sales.parquet using Snappy compression.
Outcome: Athena queries on the processed/ folder run 5x faster and scan 80% less data.

Checkpoint Questions

Why is Amazon Athena significantly cheaper when querying Parquet files compared to CSV files?
Which service is best for converting a continuous stream of JSON events from Kinesis into Parquet on S3?
What is the maximum execution time for an AWS Lambda function, and how does it affect your choice for CSV transformation?
Which compression codec is standard for Parquet files in AWS to balance performance and storage?

[!TIP] Answer 1: Athena charges per GB scanned. Parquet is columnar, so Athena only scans the columns requested, whereas for CSV, it must scan every character in the row.

Comparison Tables

Choosing the Right Transformation Service

Service	Latency	Complexity	Ideal Use Case
AWS Lambda	Real-time	Medium (Code)	Small files, event-driven triggers.
AWS Glue	Batch (Minutes)	Medium (Visual/Code)	Large historical datasets, complex JOINs.
Data Firehose	Near Real-time	Low (Config-only)	Streaming JSON to Parquet delivery.
EMR	Batch	High	Massive scale, specific Hadoop ecosystem tools.

Muddy Points & Cross-Refs

When to keep CSV? Don't convert to Parquet if your primary access pattern is reading individual full records (Point Lookup) or if the data volume is extremely small (< 10 MB), as the metadata overhead of Parquet can outweigh the benefits.
Firehose Schema Source: Remember that Data Firehose needs the AWS Glue Data Catalog to understand the schema of the incoming JSON before it can convert it to Parquet.
Partitioning vs. Bucketing: Use partitioning for high-level folders (e.g., Date) and bucketing for high-cardinality columns (e.g., UserID) to further optimize Parquet performance.

Transforming Data Formats: CSV to Apache Parquet in AWS