Building an Optimized Data Processing Pipeline for Large Datasets

Abhishek sharma


Share

We describe a data engineering project aimed at processing large-scale datasets exceeding 100 million records from diverse sources like CSV and Excel files. The goal was to address challenges in schema standardization, data cleaning, validation, deduplication, and efficient storage to enable seamless analysis and query performance for big data.

Solving this problem involved breaking it into the following key steps:

  1. Cleaning data to remove duplicates, validate fields like gender and phone numbers, and correct errors.

  2. Standardizing the schema across varied data sources and maintaining unmapped fields in JSON format to ensure no data is lost.

  3. Deduplicating data and storing the processed output in Parquet format for efficient querying and analysis via AWS S3 and Athena.

Key Highlights from the Solution:

Highlight 1: Leveraging Athena for Big Data Processing

Athena, a serverless query service, played a crucial role in optimizing big data processing. Here’s why Athena was chosen over traditional SQL databases:

  • Serverless Architecture: Eliminates the need for database server provisioning, reducing costs significantly.

  • Parquet Integration: Faster and more efficient querying due to Parquet’s columnar storage format.

  • Scalability: Automatically handles growing datasets without additional infrastructure.

For deduplication, Athena queried the master_table to identify unique records based on specified fields (e.g., phone numbers), leveraging its distributed architecture for parallel query execution.

Highlight 2: Optimizations for Handling Unstructured Data

The pipeline added extra columns in JSON format to store metadata or fields not mapped explicitly in the configuration. This approach ensured no data was lost and provided flexibility to parse and use unmapped data when needed.

For example, unstructured fields such as additional notes or custom metadata from the input files were preserved, enabling future enrichment or analysis.

Highlight 3: Parallel Processing for Speed

To reduce processing time for large datasets:

  • Multi-Core Utilization: Enabled parallel processing using a configurable --parallel_processing flag and a user-defined --num_cores.

  • Performance Boost: For example, processing a file with 1 crore records using two cores reduced the execution time by half compared to single-threaded processing.

When processing an entire folder of files with multi crores of data, this parallel processing capability reduces total time from 15 minutes to 5 minutes, depending on system resources.

This pipeline design demonstrated how large datasets can be processed efficiently and flexibly, paving the way for faster analysis and improved query performance in big data environments.