Person
Person

Mini 2: Serverless ETL Pipeline on GCP for CSV Automated Processing to BigQuery

Overview:

Designed and deployed a fully serverless, event-driven ETL pipeline on GCP that automatically processes CSV files from Cloud Storage, transforms the data with a Cloud Function, and loads it into BigQuery for analysis.

Project Description

This project demonstrates a modern cloud data engineering solution by building a hands-off, automated pipeline. When a new CSV file is uploaded to a Google Cloud Storage (GCS) bucket, an event trigger invokes a Gen 2 Cloud Function. This Python-based function uses the Pandas library to clean, validate, enrich, and restructure the raw data before streaming the final, analysis-ready dataset into a predefined BigQuery table.

Problem Statement

The traditional approach to data processing often involves manual scripts, scheduled batch jobs, and server management, which is inefficient, costly, and not scalable. Businesses need a way to ingest and process data in near real-time as it arrives, without the operational overhead. This project solves this by creating a fully automated, event-driven pipeline on GCP that eliminates manual intervention, reduces costs through a pay-per-use model, and provides a scalable framework for converting raw data into actionable insights.
Car Side View

Methodologies & Project Workflow

The project followed a systematic, multi-phase approach from theory to deployment:

  • Phase 1: Foundational Setup:

    A clean, version-controlled local environment was established using a Python virtual environment and Git. Core libraries for data manipulation and GCP interaction ( pandas, google-cloud-bigquery, google-cloud-storage, pyarrow) were installed and documented in requirements.txt.


  • Phase 2: GCP Resource Provisioning:

    All necessary cloud infrastructure was set up on GCP. This included enabling the required APIs (e.g., Cloud Functions, Eventarc, BigQuery), creating a GCS bucket to receive input files, and pre-defining a dataset and table with a specific schema in BigQuery to house the transformed data.


  • Phase 3: Transformation Logic Development:

    The core ETL logic was developed in Python within a Cloud Function. This script was designed to parse the incoming GCS event, download the triggered file, load it into a Pandas DataFrame, perform a series of transformations (handling missing values, standardizing fields, deriving new columns), and ensure the final data conformed to the BigQuery schema.


  • Phase 4: Deployment & End-to-End Testing:

    The Cloud Function was deployed to GCP using the gcloud CLI, configured to be triggered by file uploads to the GCS bucket. The full pipeline was tested by uploading a sample CSV, followed by meticulous verification and troubleshooting using Cloud Logging and BigQuery's query interface to confirm successful data transformation and loading.


  • Phase 5: Documentation & Publishing:

    The project was finalized by creating a comprehensive README.md file detailing the architecture, setup instructions, and key learnings. The complete codebase and documentation were then pushed to a public GitHub repository to serve as a detailed portfolio piece.

Key Results

  • Project-Specific Outcomes: Built and deployed a fully automated, serverless ETL pipeline on GCP that ingests CSV files from GCS, applies pandas-based transformations (e.g., cleaning, datetime standardization), and loads structured data into BigQuery—successfully tested with sample user_events_input.csv, verifying end-to-end flow in a streamlined prototype.

  • Efficiency and Scalability: Leveraged Gen 2 Cloud Functions on Cloud Run for event-driven processing via Eventarc, with 512MiB memory allocation enabling reliable execution without server management; qualitative tests showed seamless triggering and loading, though no quantitative latency metrics were captured.

  • Key Achievements: Overcame deployment hurdles like IAM permissions, pyarrow dependency for efficient data transfer, and memory limits, resulting in a reproducible setup via gcloud CLI; demonstrating practical cloud data engineering for scalable workflows.

  • Pipeline Insights: No custom benchmarks due to mini-project scope, but the design ensures cost-effective, auto-scaling operations; aligns with fintech/e-commerce or any data engineering needs (e.g., real-time event processing).

Key Features & Technical Details

  • Automated Trigger: Event-driven execution upon new file uploads to a GCS bucket, managed by Eventarc.


  • Serverless Processing: Utilizes Gen 2 Cloud Functions, executed as containerized applications on Google Cloud Run, for scalable, cost-effective data transformation without managing servers.


  • Data Transformation: Leverages the pandas library in Python for flexible data manipulation, including cleaning, standardization, and feature derivation.


  • Cloud Data Warehousing: Loads structured, transformed data into Google BigQuery, making it immediately available for SQL-based analytics.


  • Reproducible Deployment: The entire cloud function and its dependencies are defined in code and deployed via the gcloud CLI.

Technical Stack

Cloud Platform: Google Cloud Platform (GCP)

  • Core Services: GCS (Cloud Storage), Cloud Functions (Gen 2), BigQuery, Eventarc, IAM, Cloud Build, Cloud Logging

  • Programming Language: Python

  • Data Libraries: pandas, pyarrow

  • GCP Libraries: google-cloud-storage, google-cloud-bigquery, functions-framework

  • Version Control: Git, GitHub

  • Deployment & Auth: Google Cloud SDK (gcloud CLI), Application Default Credentials (ADC)

Challenges & Solutions

Challenge: The initial deployment failed due to complex and misconfigured IAM permissions between GCP services (Eventarc, GCS, Cloud Functions, and Pub/Sub).

  • Solution: Systematically troubleshot the permissions by carefully reading error logs. This involved granting specific IAM roles, such as giving the Eventarc Service Agent permission to access the GCS bucket and the GCS Service Agent permission to publish Pub/Sub notifications, ensuring all services could securely communicate .


Challenge: The BigQuery load operation failed due to a missing pyarrow dependency and data type mismatches between the Pandas DataFrame and the BigQuery table schema, especially for timestamps.

  • Solution: Added pyarrow to requirements.txt to enable efficient data serialization. The Python transformation logic was updated to explicitly handle missing timestamps and enforce the correct datetime64[ns, UTC] dtype in the DataFrame before loading, ensuring perfect schema alignment.


Challenge: The Cloud Function would occasionally fail with memory exhaustion errors during processing.

  • Solution: Analyzed the function's resource consumption and resolved the issue by explicitly increasing its allocated memory during deployment using the --memory flag in the gcloud deploy command (e.g., --memory 512MiB).

Car Rear Zoom View
Car Front View
Car Front Zoom View
Car Side View

Future Enhancements & Improvement

  • Dead-Letter Queue (DLQ): Implement a GCS bucket as a DLQ to automatically capture and store files that fail processing for later inspection.


  • Schema Validation: Integrate a schema validation step to ensure incoming CSVs have the correct columns and data types before processing begins.


  • CI/CD Pipeline: Build a CI/CD pipeline using Cloud Build or GitHub Actions to automate testing and deployment of the Cloud Function on code changes.


  • Monitoring & Alerting: Set up Cloud Monitoring dashboards and alerts to track pipeline health, function execution times, and error rates.

More Works

(GQ® — 02)

©2024

More Works

(GQ® — 02)

©2024

Person
Person

Mini 2: Serverless ETL Pipeline on GCP for CSV Automated Processing to BigQuery

Overview:

Designed and deployed a fully serverless, event-driven ETL pipeline on GCP that automatically processes CSV files from Cloud Storage, transforms the data with a Cloud Function, and loads it into BigQuery for analysis.

Project Description

This project demonstrates a modern cloud data engineering solution by building a hands-off, automated pipeline. When a new CSV file is uploaded to a Google Cloud Storage (GCS) bucket, an event trigger invokes a Gen 2 Cloud Function. This Python-based function uses the Pandas library to clean, validate, enrich, and restructure the raw data before streaming the final, analysis-ready dataset into a predefined BigQuery table.

Problem Statement

The traditional approach to data processing often involves manual scripts, scheduled batch jobs, and server management, which is inefficient, costly, and not scalable. Businesses need a way to ingest and process data in near real-time as it arrives, without the operational overhead. This project solves this by creating a fully automated, event-driven pipeline on GCP that eliminates manual intervention, reduces costs through a pay-per-use model, and provides a scalable framework for converting raw data into actionable insights.
Car Side View

Methodologies & Project Workflow

The project followed a systematic, multi-phase approach from theory to deployment:

  • Phase 1: Foundational Setup:

    A clean, version-controlled local environment was established using a Python virtual environment and Git. Core libraries for data manipulation and GCP interaction ( pandas, google-cloud-bigquery, google-cloud-storage, pyarrow) were installed and documented in requirements.txt.


  • Phase 2: GCP Resource Provisioning:

    All necessary cloud infrastructure was set up on GCP. This included enabling the required APIs (e.g., Cloud Functions, Eventarc, BigQuery), creating a GCS bucket to receive input files, and pre-defining a dataset and table with a specific schema in BigQuery to house the transformed data.


  • Phase 3: Transformation Logic Development:

    The core ETL logic was developed in Python within a Cloud Function. This script was designed to parse the incoming GCS event, download the triggered file, load it into a Pandas DataFrame, perform a series of transformations (handling missing values, standardizing fields, deriving new columns), and ensure the final data conformed to the BigQuery schema.


  • Phase 4: Deployment & End-to-End Testing:

    The Cloud Function was deployed to GCP using the gcloud CLI, configured to be triggered by file uploads to the GCS bucket. The full pipeline was tested by uploading a sample CSV, followed by meticulous verification and troubleshooting using Cloud Logging and BigQuery's query interface to confirm successful data transformation and loading.


  • Phase 5: Documentation & Publishing:

    The project was finalized by creating a comprehensive README.md file detailing the architecture, setup instructions, and key learnings. The complete codebase and documentation were then pushed to a public GitHub repository to serve as a detailed portfolio piece.

Key Results

  • Project-Specific Outcomes: Built and deployed a fully automated, serverless ETL pipeline on GCP that ingests CSV files from GCS, applies pandas-based transformations (e.g., cleaning, datetime standardization), and loads structured data into BigQuery—successfully tested with sample user_events_input.csv, verifying end-to-end flow in a streamlined prototype.

  • Efficiency and Scalability: Leveraged Gen 2 Cloud Functions on Cloud Run for event-driven processing via Eventarc, with 512MiB memory allocation enabling reliable execution without server management; qualitative tests showed seamless triggering and loading, though no quantitative latency metrics were captured.

  • Key Achievements: Overcame deployment hurdles like IAM permissions, pyarrow dependency for efficient data transfer, and memory limits, resulting in a reproducible setup via gcloud CLI; demonstrating practical cloud data engineering for scalable workflows.

  • Pipeline Insights: No custom benchmarks due to mini-project scope, but the design ensures cost-effective, auto-scaling operations; aligns with fintech/e-commerce or any data engineering needs (e.g., real-time event processing).

Key Features & Technical Details

  • Automated Trigger: Event-driven execution upon new file uploads to a GCS bucket, managed by Eventarc.


  • Serverless Processing: Utilizes Gen 2 Cloud Functions, executed as containerized applications on Google Cloud Run, for scalable, cost-effective data transformation without managing servers.


  • Data Transformation: Leverages the pandas library in Python for flexible data manipulation, including cleaning, standardization, and feature derivation.


  • Cloud Data Warehousing: Loads structured, transformed data into Google BigQuery, making it immediately available for SQL-based analytics.


  • Reproducible Deployment: The entire cloud function and its dependencies are defined in code and deployed via the gcloud CLI.

Technical Stack

Cloud Platform: Google Cloud Platform (GCP)

  • Core Services: GCS (Cloud Storage), Cloud Functions (Gen 2), BigQuery, Eventarc, IAM, Cloud Build, Cloud Logging

  • Programming Language: Python

  • Data Libraries: pandas, pyarrow

  • GCP Libraries: google-cloud-storage, google-cloud-bigquery, functions-framework

  • Version Control: Git, GitHub

  • Deployment & Auth: Google Cloud SDK (gcloud CLI), Application Default Credentials (ADC)

Challenges & Solutions

Challenge: The initial deployment failed due to complex and misconfigured IAM permissions between GCP services (Eventarc, GCS, Cloud Functions, and Pub/Sub).

  • Solution: Systematically troubleshot the permissions by carefully reading error logs. This involved granting specific IAM roles, such as giving the Eventarc Service Agent permission to access the GCS bucket and the GCS Service Agent permission to publish Pub/Sub notifications, ensuring all services could securely communicate .


Challenge: The BigQuery load operation failed due to a missing pyarrow dependency and data type mismatches between the Pandas DataFrame and the BigQuery table schema, especially for timestamps.

  • Solution: Added pyarrow to requirements.txt to enable efficient data serialization. The Python transformation logic was updated to explicitly handle missing timestamps and enforce the correct datetime64[ns, UTC] dtype in the DataFrame before loading, ensuring perfect schema alignment.


Challenge: The Cloud Function would occasionally fail with memory exhaustion errors during processing.

  • Solution: Analyzed the function's resource consumption and resolved the issue by explicitly increasing its allocated memory during deployment using the --memory flag in the gcloud deploy command (e.g., --memory 512MiB).

Car Rear Zoom View
Car Front View
Car Front Zoom View
Car Side View

Future Enhancements & Improvement

  • Dead-Letter Queue (DLQ): Implement a GCS bucket as a DLQ to automatically capture and store files that fail processing for later inspection.


  • Schema Validation: Integrate a schema validation step to ensure incoming CSVs have the correct columns and data types before processing begins.


  • CI/CD Pipeline: Build a CI/CD pipeline using Cloud Build or GitHub Actions to automate testing and deployment of the Cloud Function on code changes.


  • Monitoring & Alerting: Set up Cloud Monitoring dashboards and alerts to track pipeline health, function execution times, and error rates.

More Works

(GQ® — 02)

©2024

Person
Person

Mini 2: Serverless ETL Pipeline on GCP for CSV Automated Processing to BigQuery

Overview:

Designed and deployed a fully serverless, event-driven ETL pipeline on GCP that automatically processes CSV files from Cloud Storage, transforms the data with a Cloud Function, and loads it into BigQuery for analysis.

Project Description

This project demonstrates a modern cloud data engineering solution by building a hands-off, automated pipeline. When a new CSV file is uploaded to a Google Cloud Storage (GCS) bucket, an event trigger invokes a Gen 2 Cloud Function. This Python-based function uses the Pandas library to clean, validate, enrich, and restructure the raw data before streaming the final, analysis-ready dataset into a predefined BigQuery table.

Problem Statement

The traditional approach to data processing often involves manual scripts, scheduled batch jobs, and server management, which is inefficient, costly, and not scalable. Businesses need a way to ingest and process data in near real-time as it arrives, without the operational overhead. This project solves this by creating a fully automated, event-driven pipeline on GCP that eliminates manual intervention, reduces costs through a pay-per-use model, and provides a scalable framework for converting raw data into actionable insights.
Car Side View

Methodologies & Project Workflow

The project followed a systematic, multi-phase approach from theory to deployment:

  • Phase 1: Foundational Setup:

    A clean, version-controlled local environment was established using a Python virtual environment and Git. Core libraries for data manipulation and GCP interaction ( pandas, google-cloud-bigquery, google-cloud-storage, pyarrow) were installed and documented in requirements.txt.


  • Phase 2: GCP Resource Provisioning:

    All necessary cloud infrastructure was set up on GCP. This included enabling the required APIs (e.g., Cloud Functions, Eventarc, BigQuery), creating a GCS bucket to receive input files, and pre-defining a dataset and table with a specific schema in BigQuery to house the transformed data.


  • Phase 3: Transformation Logic Development:

    The core ETL logic was developed in Python within a Cloud Function. This script was designed to parse the incoming GCS event, download the triggered file, load it into a Pandas DataFrame, perform a series of transformations (handling missing values, standardizing fields, deriving new columns), and ensure the final data conformed to the BigQuery schema.


  • Phase 4: Deployment & End-to-End Testing:

    The Cloud Function was deployed to GCP using the gcloud CLI, configured to be triggered by file uploads to the GCS bucket. The full pipeline was tested by uploading a sample CSV, followed by meticulous verification and troubleshooting using Cloud Logging and BigQuery's query interface to confirm successful data transformation and loading.


  • Phase 5: Documentation & Publishing:

    The project was finalized by creating a comprehensive README.md file detailing the architecture, setup instructions, and key learnings. The complete codebase and documentation were then pushed to a public GitHub repository to serve as a detailed portfolio piece.

Key Results

  • Project-Specific Outcomes: Built and deployed a fully automated, serverless ETL pipeline on GCP that ingests CSV files from GCS, applies pandas-based transformations (e.g., cleaning, datetime standardization), and loads structured data into BigQuery—successfully tested with sample user_events_input.csv, verifying end-to-end flow in a streamlined prototype.

  • Efficiency and Scalability: Leveraged Gen 2 Cloud Functions on Cloud Run for event-driven processing via Eventarc, with 512MiB memory allocation enabling reliable execution without server management; qualitative tests showed seamless triggering and loading, though no quantitative latency metrics were captured.

  • Key Achievements: Overcame deployment hurdles like IAM permissions, pyarrow dependency for efficient data transfer, and memory limits, resulting in a reproducible setup via gcloud CLI; demonstrating practical cloud data engineering for scalable workflows.

  • Pipeline Insights: No custom benchmarks due to mini-project scope, but the design ensures cost-effective, auto-scaling operations; aligns with fintech/e-commerce or any data engineering needs (e.g., real-time event processing).

Key Features & Technical Details

  • Automated Trigger: Event-driven execution upon new file uploads to a GCS bucket, managed by Eventarc.


  • Serverless Processing: Utilizes Gen 2 Cloud Functions, executed as containerized applications on Google Cloud Run, for scalable, cost-effective data transformation without managing servers.


  • Data Transformation: Leverages the pandas library in Python for flexible data manipulation, including cleaning, standardization, and feature derivation.


  • Cloud Data Warehousing: Loads structured, transformed data into Google BigQuery, making it immediately available for SQL-based analytics.


  • Reproducible Deployment: The entire cloud function and its dependencies are defined in code and deployed via the gcloud CLI.

Technical Stack

Cloud Platform: Google Cloud Platform (GCP)

  • Core Services: GCS (Cloud Storage), Cloud Functions (Gen 2), BigQuery, Eventarc, IAM, Cloud Build, Cloud Logging

  • Programming Language: Python

  • Data Libraries: pandas, pyarrow

  • GCP Libraries: google-cloud-storage, google-cloud-bigquery, functions-framework

  • Version Control: Git, GitHub

  • Deployment & Auth: Google Cloud SDK (gcloud CLI), Application Default Credentials (ADC)

Challenges & Solutions

Challenge: The initial deployment failed due to complex and misconfigured IAM permissions between GCP services (Eventarc, GCS, Cloud Functions, and Pub/Sub).

  • Solution: Systematically troubleshot the permissions by carefully reading error logs. This involved granting specific IAM roles, such as giving the Eventarc Service Agent permission to access the GCS bucket and the GCS Service Agent permission to publish Pub/Sub notifications, ensuring all services could securely communicate .


Challenge: The BigQuery load operation failed due to a missing pyarrow dependency and data type mismatches between the Pandas DataFrame and the BigQuery table schema, especially for timestamps.

  • Solution: Added pyarrow to requirements.txt to enable efficient data serialization. The Python transformation logic was updated to explicitly handle missing timestamps and enforce the correct datetime64[ns, UTC] dtype in the DataFrame before loading, ensuring perfect schema alignment.


Challenge: The Cloud Function would occasionally fail with memory exhaustion errors during processing.

  • Solution: Analyzed the function's resource consumption and resolved the issue by explicitly increasing its allocated memory during deployment using the --memory flag in the gcloud deploy command (e.g., --memory 512MiB).

Car Rear Zoom View
Car Front View
Car Front Zoom View
Car Side View

Future Enhancements & Improvement

  • Dead-Letter Queue (DLQ): Implement a GCS bucket as a DLQ to automatically capture and store files that fail processing for later inspection.


  • Schema Validation: Integrate a schema validation step to ensure incoming CSVs have the correct columns and data types before processing begins.


  • CI/CD Pipeline: Build a CI/CD pipeline using Cloud Build or GitHub Actions to automate testing and deployment of the Cloud Function on code changes.


  • Monitoring & Alerting: Set up Cloud Monitoring dashboards and alerts to track pipeline health, function execution times, and error rates.

More Works

©2024

Create a free website with Framer, the website builder loved by startups, designers and agencies.