How manoj-sai/customer-sales-analytics-gcp works
This repository implements a batch analytics pipeline with a clear split between:
1. Local Spark-based ETL
2. Parquet outputs as the handoff format
3. Manual cloud analytics setup in GCS + BigQuery
4. SQL validation of business metrics
It is not a general-purpose application; it is a data engineering workflow centered on the Olist e-commerce dataset.
High-level architecture
The repository follows a simple layered flow:
• Raw input layer
- Olist source files downloaded locally
- Stored under data/raw_download/olist/
- A smaller sample subset exists under data/sample/olist_subset/
• Processing layer
- PySpark batch job reads raw or sample data
- Cleans, validates, joins, and aggregates it
- Produces two curated analytical datasets:
- order_summary at order grain
- customer_sales at customer grain
- Outputs are written locally as Parquet
• Analytics layer
- Curated Parquet folders are manually uploaded to Google Cloud Storage
- BigQuery tables are created from those Parquet files
- SQL queries are run for KPI validation and analysis
The important architectural boundary is that the repository’s automated pipeline ends at local Parquet generation. The cloud warehouse portion is intentionally manual in this project.
Main subsystems
1. Spark batch ETL subsystem
Located under src/etl/batch/
This is the core processing subsystem. Based on the file names, it is organized into stage-specific modules:
• batch_etl.py
- The main orchestration entry point for the pipeline
- Likely coordinates extract → clean → transform → write
- This is the file passed to spark-submit
• extract.py
- Reads source datasets from the raw layer or sample layer
- Materializes source DataFrames for downstream processing
• clean.py
- Handles data quality cleanup
- Likely normalizes types, filters invalid records, and prepares join keys
• transform.py
- Performs joins and aggregations
- Produces the curated datasets at the target analytical grains
• load.py
- Persists the curated outputs, most likely as Parquet directories on disk
- In this repo, “load” does not mean loading directly into BigQuery; the warehouse load is manual later
• create_subset.py
- Supports creation of a smaller sample dataset
- Useful for local development and fast iteration
This ETL subsystem is the main computational boundary of the repository.
2. Configuration subsystem
Located under src/config/settings.py
This is the central runtime configuration layer. It controls:
• Whether the pipeline runs on sample or full data
• Local input and output paths
• GCP project identifiers
• GCS bucket name
• BigQuery dataset name
• Likely execution flags such as RUN_MODE
Architecturally, this file is important because it decouples the ETL logic from environment-specific values. The pipeline can run locally in Docker while still being aligned with the GCP analytics target.
3. Runtime utilities subsystem
Located under src/utils/
• spark_session.py
- Creates and configures the Spark session used by the batch job
- This is the runtime foundation for the ETL job
• logger.py
- Central logging support for the pipeline
- Useful for tracing stage-level execution and failures
These utilities support the ETL layers but are not business logic themselves.
4. Validation and inspection tools
Located under scripts/
• check_parquet.py
• check_parquet.sh
These scripts validate the ETL outputs after the Spark job runs. The checks described in the README indicate they verify:
• Output exists
• Output contains rows
• Schema is as expected
• Files can be read successfully by Spark
This is a useful boundary between transformation and downstream consumption.
5. SQL analytics layer
Located under sql/analytics_queries.sql
This file contains BigQuery SQL used after the data is made available in the warehouse. It is the business-facing analysis layer, where the curated datasets are queried for metrics and validation.
This subsystem is separate from ETL, which keeps transformation logic and analytical querying distinct.
6. Infrastructure and execution environment
Located under docker/
• docker/docker-compose.yml
- Defines the local Spark cluster
- README indicates a Spark master plus worker containers
- This is the execution substrate for the batch job
The architecture depends on Docker-based Spark rather than a standalone local Python runtime. The Spark job runs through spark-submit inside the containerized cluster.
End-to-end data flow
Step 1: Raw data ingestion
The Olist dataset is available locally in raw form. The project supports:
• full raw download data
• a smaller sample subset for quicker runs
The pipeline chooses which input set to use based on configuration in src/config/settings.py.
Step 2: Spark ETL processing
Inside the Docker Spark environment:
• batch_etl.py starts the job
• extract.py reads the source tables
• clean.py prepares the data for reliable joins and aggregation
• transform.py builds the analytical outputs
The transformations are designed around analytical grain, which is a key modeling choice in this repository.
Step 3: Curated output generation
The pipeline emits two main datasets:
• order_summary
- Grain: 1 row per order
- Supports order-level metrics like total items, total product value, freight, and payment amount
• customer_sales
- Grain: 1 row per customer
- Supports customer-level metrics like order count, total items, and total payment value
These are written locally as Parquet folders:
• data/output/order_summary_parquet/
• data/output/customer_sales_parquet/
Step 4: Output validation
The generated Parquet files can be checked using the helper scripts in scripts/.
This forms a quality gate before cloud upload.
Step 5: Manual cloud analytics handoff
The repository does not automate the cloud load. Instead:
• Parquet folders are manually uploaded to Google Cloud Storage
• BigQuery tables are manually created from those files
This manual step is an explicit architectural choice in the README, and it separates the local ETL pipeline from the cloud warehouse layer.
Step 6: Analytics in BigQuery
Once loaded into BigQuery:
• the curated tables are queried with SQL
• sql/analytics_queries.sql contains the validation and analysis queries
This is the consumption layer of the architecture.
Key design boundaries
Local processing vs cloud analytics
The biggest boundary is between:
• Spark ETL on local Docker
• GCS + BigQuery analytics in GCP
The repository intentionally stops at curated Parquet. It does not include automated cloud orchestration, ingestion jobs, or warehouse table creation scripts.
Raw data vs modeled data
Another important boundary is the shift from source-shaped raw tables to analytical datasets with defined grain.
The curated outputs are not just cleaned copies of source tables; they are modeled analytic tables meant for business reporting.
Sample vs full data execution
The config supports a development mode using sample data and a production-style mode using the full raw dataset. This affects runtime behavior without changing the pipeline structure.
Technologies that matter to the architecture
• Python
- Project language and glue code
• PySpark
- Main transformation engine
• Docker / Docker Compose
- Local Spark cluster runtime
• Parquet
- Intermediate and handoff storage format
• Google Cloud Storage
- File staging for warehouse ingestion
• BigQuery
- Final analytics warehouse
• SQL
- Business querying and validation
These technologies are central because they define the execution and storage boundaries of the system.
Repository organization from an architecture perspective
• src/
- Core application logic
- ETL stages, config, runtime utilities
• docker/
- Cluster/runtime definition
• scripts/
- Operational validation helpers
• sql/
- Analytical query layer
• docs/
- Design notes and screenshots
- docs/transformation_design_framework.md likely explains the grain-based modeling approach
• requirements.txt
- Python dependencies for local tooling or helper scripts
• .env.example
- Example environment configuration for local execution
What an architecture diagram should emphasize
If you are drawing this repo, the diagram should show:
1. Source data from Olist in local raw storage
2. Dockerized Spark cluster executing the batch ETL
3. Stage modules for extract, clean, transform, and output
4. Parquet outputs as the durable local artifact
5. Manual transfer to GCS
6. BigQuery tables built from those Parquet files
7. SQL analytics/validation on top of BigQuery
8. Configuration and utilities as cross-cutting support
The most important conceptual message is that this is a batch ELT/ETL pipeline with a warehouse handoff, not a continuously running service. The curated datasets are designed around analytical grain so they can support downstream reporting and KPI analysis cleanly.
Main entry points: batch_etl.py Batch ETL · load.py Load · extract.py Extract · transform.py Transform
Components 14
Runtime
Containerized Spark execution environment and shared utilities.
- Spark cluster · Defines the local Spark master/worker execution environment.
docker/docker-compose.yml - Spark session · Creates and configures the Spark session used by the batch job.
src/utils/spark_session.py - Logger · Shared logging support for pipeline execution.
src/utils/logger.py - Settings · Central runtime configuration for paths, modes, and GCP targets.
src/config/settings.py
Batch ETL
Core Spark pipeline that extracts, cleans, transforms, and writes curated outputs.
- Batch ETL · Main Spark job entrypoint coordinating the batch pipeline.
src/etl/batch/batch_etl.py - Extract · Reads raw or sample Olist source tables into DataFrames.
src/etl/batch/extract.py - Clean · Cleans and normalizes records for reliable downstream joins.
src/etl/batch/clean.py - Transform · Builds analytical datasets through joins and aggregations.
src/etl/batch/transform.py - Load · Writes curated outputs as local Parquet handoff artifacts.
src/etl/batch/load.py - Create subset · Generates a smaller sample dataset for faster local iteration.
src/etl/batch/create_subset.py
Validation
Operational checks for generated Parquet outputs.
- Parquet check · Verifies output existence, schema, and readability after ETL.
scripts/check_parquet.py - Check script · Shell entrypoint for running Parquet validation checks.
scripts/check_parquet.sh
Analytics
BigQuery-facing SQL analysis and validation layer.
- Analytics SQL · Business metric and validation queries for the curated warehouse tables.
sql/analytics_queries.sql
Docs
Design notes and project guidance.
- Design guide · Explains the transformation and analytical grain approach.
docs/transformation_design_framework.md
Connections 18
- Spark cluster → Spark session: runs · Dockerized Spark cluster executes the batch job.
- Settings → Batch ETL: configures · Runtime settings control input mode, paths, and targets.
- Settings → Extract: paths · Extraction uses configured raw or sample locations.
- Batch ETL → Extract: starts · Main orchestration begins with data extraction.
- Batch ETL → Clean: pipes · Clean stage prepares records for modeling.
- Batch ETL → Transform: pipes · Transform stage produces analytical grains.
- Batch ETL → Load: ends · Load stage writes the curated Parquet outputs.
- Extract → Clean: feeds · Raw tables are normalized before modeling.
- Clean → Transform: feeds · Cleaned data enters join and aggregation logic.
- Transform → Load: writes · Transformed datasets are persisted as Parquet.
- Create subset → Extract: supports · Subset generation helps produce smaller input data.
- Spark session → Batch ETL: runtime · Spark session underpins the batch execution.
- Logger → Batch ETL: observes · Logging spans the pipeline stages.
- Load → Parquet check: validates · Validation checks are run on generated Parquet outputs.
- Check script → Parquet check: wraps · Shell script invokes the Python validation tool.
- Load → Analytics SQL: handoff · Parquet artifacts are manually staged for warehouse consumption.
- Design guide → Transform: guides · Design notes explain the analytical grain used in transforms.
- Analytics SQL → Load: queries · SQL analysis consumes the curated datasets after warehouse load.