An end-to-end data engineering pipeline built on the Brazilian E-Commerce (Olist) public dataset.

This project ingests ~100K orders from Kaggle, transforms them through a modern data stack (PySpark, S3, Snowflake, dbt), orchestrates with Airflow, and surfaces insights in a Streamlit dashboard -- all provisioned with Terraform and validated through CI/CD.

 DataFlow Pro -- System Architecture ============================================================================ +----------+ +-----------+ +----------+ +----------+ +------------+ | | | | | | | | | | | Kaggle | --> | PySpark | --> | AWS S3 | --> | Snowflake| --> | Streamlit | | (source) | | (ingest) | | (lake) | | (DWH) | | (dashboard)| | | | | | | | | | | +----------+ +-----------+ +----------+ +----+-----+ +------------+ | +----+-----+ | | | dbt | | (models) | | | +----------+ Orchestration: Apache Airflow Infrastructure: Terraform CI/CD: GitHub Actions Monitoring: dbt tests + Airflow alerts ============================================================================ 

Data flow in detail:

1. Extract -- Download 9 CSV files (~100K orders, 8 entity tables) from Kaggle via the API.
2. Transform & Load (Ingestion) -- PySpark reads raw CSVs, applies schema validation, deduplication, timestamp casting, and string standardization, then writes clean Parquet files to S3.
3. Load into Warehouse -- Snowflake COPY INTO loads Parquet from the S3 stage into the RAW schema.
4. Model (dbt) -- Three-layer dbt project (staging, intermediate, marts) builds a clean analytics-ready star schema.
5. Serve -- Streamlit dashboard queries the marts layer to visualize KPIs, trends, and geographic distribution.

ecommerce/ |-- .github/ | `-- workflows/ | |-- dbt_ci.yml # dbt CI on pull requests | `-- deploy.yml # Full deployment pipeline | |-- dashboard/ | |-- app.py # Streamlit application entrypoint | |-- requirements.txt # Dashboard Python dependencies | `-- utils/ | `-- snowflake_connector.py # Snowflake connection manager | |-- data/ | `-- .gitkeep # Placeholder (raw CSVs downloaded here) | |-- dbt_project/ | |-- dbt_project.yml # dbt project configuration | |-- packages.yml # dbt packages (dbt_utils, dbt_expectations) | |-- profiles.yml # Connection profiles (dev, prod, ci) | `-- models/ | `-- staging/ | |-- _staging__sources.yml | |-- _staging__models.yml | |-- stg_orders.sql | |-- stg_order_items.sql | |-- stg_customers.sql | |-- stg_products.sql | |-- stg_sellers.sql | |-- stg_payments.sql | |-- stg_reviews.sql | `-- stg_geolocation.sql (referenced in models.yml) | |-- infrastructure/ | |-- main.tf # Root Terraform module | |-- variables.tf # Input variables | |-- outputs.tf # Output values | |-- environments/ # Per-environment tfvars | `-- modules/ | |-- s3/ # S3 buckets (raw + processed) | |-- iam/ # Cross-service IAM roles | `-- snowflake/ # Snowflake DB objects via Terraform | |-- ingestion/ | |-- notebooks/ | | `-- 01_ingest_csv.py # PySpark batch ingestion script | |-- src/ | | |-- schemas.py # PySpark StructType schemas for all 8 tables | | |-- transformations.py # Reusable transform functions | | `-- utils.py # Spark session, path helpers, Parquet writer | `-- tests/ | `-- __init__.py | |-- orchestration/ | |-- dags/ # Airflow DAG definitions | |-- plugins/ # Custom Airflow operators | `-- config/ # Airflow configuration overrides | |-- scripts/ | |-- download_dataset.py # Kaggle dataset downloader | |-- run_pipeline.sh # End-to-end pipeline runner | `-- setup_env.sh # Environment bootstrap script | |-- snowflake/ | `-- setup/ | |-- 01_databases.sql # Create ECOMMERCE database | |-- 02_schemas.sql # Create RAW/STAGING/INTERMEDIATE/MARTS + RBAC | |-- 03_warehouses.sql # Create compute warehouses | |-- 04_stages.sql # Create S3 external stages | `-- 05_file_formats.sql # Create CSV/Parquet file formats | |-- .env.example # Template for environment variables |-- .gitignore |-- docker-compose.yml # Airflow + Streamlit local stack |-- Dockerfile.airflow |-- Dockerfile.streamlit |-- Makefile # Developer task runner |-- requirements.txt # Core Python dependencies `-- requirements-dev.txt # Development/testing dependencies 

# 1. Clone the repository git clone https://github.com/<your-username>/dataflow-pro-ecommerce.git cd dataflow-pro-ecommerce # 2. Run the automated setup script bash scripts/setup_env.sh # 3. Configure credentials cp .env.example .env # Edit .env with your Snowflake, AWS, and Kaggle credentials # 4. Activate the virtual environment source .venv/bin/activate # 5. Download the Olist dataset from Kaggle make download # 6. Run the full pipeline (ingest -> transform -> test) make pipeline # 7. Launch the Streamlit dashboard make dashboard

Tip: Run make help to see all available commands.

The ingestion layer reads the 8 raw Olist CSV files, enforces PySpark schemas, and writes clean Parquet output.

# Ingest all tables make ingest # Or run the PySpark script directly with options python ingestion/notebooks/01_ingest_csv.py \ --input-dir data/raw \ --output-dir data/processed \ --tables orders customers products

What the ingestion pipeline does:

• Applies predefined StructType schemas for type safety
• Cleans column names (lowercase, strip whitespace)
• Removes duplicate rows
• Casts timestamp strings to proper TimestampType
• Standardizes city/state strings (trim + lowercase)
• Filters rows with null primary keys
• Appends _ingested_at and _source_file audit columns

Run the SQL setup scripts in order against your Snowflake account. These create the database, schemas (RAW, STAGING, INTERMEDIATE, MARTS), warehouses, external stages, file formats, and RBAC roles.

# Review the setup order make snowflake-setup # Execute in Snowflake UI or via SnowSQL: # snowflake/setup/01_databases.sql # snowflake/setup/02_schemas.sql # snowflake/setup/03_warehouses.sql # snowflake/setup/04_stages.sql # snowflake/setup/05_file_formats.sql

Roles created:

The dbt project lives in dbt_project/ and connects to Snowflake via profiles.yml.

# Install dbt packages (dbt_utils, dbt_expectations) make dbt-deps # Run all models make dbt-run # Run tests make dbt-test # Build (run + test in DAG order) make dbt-build # Generate and serve documentation make dbt-docs

Profile targets:

Airflow runs via Docker Compose and manages the end-to-end DAG:

# Start Airflow (webserver + scheduler + Postgres) make airflow-up # Access the UI at http://localhost:8080 # Username: admin # Password: admin # Stop Airflow make airflow-down

The interactive dashboard connects to the Snowflake marts layer and visualizes:

• Revenue trends and order volume over time
• Customer geographic distribution across Brazilian states
• Product category performance
• Seller metrics and delivery time analysis
• Payment method breakdowns

# Run locally make dashboard # Or via Docker Compose docker-compose up streamlit

The dashboard supports a demo mode that renders with sample data when Snowflake credentials are not configured.

Terraform provisions all cloud resources: S3 buckets with lifecycle policies, IAM roles for Snowflake-to-S3 access, and Snowflake database objects.

cd infrastructure # Initialize providers terraform init # Preview changes terraform plan -var-file=environments/dev.tfvars # Apply terraform apply -var-file=environments/dev.tfvars

Modules:

The dbt project follows a three-layer medallion architecture:

Materialized as views. One-to-one mapping with raw source tables. Responsibilities:

• Rename columns for consistency (e.g., order_delivered_customer_date -> delivered_customer_at)
• Cast data types (string -> timestamp_ntz, numeric(16,2))
• Standardize strings (uppercase city/state names)
• Filter invalid records
• Coalesce nulls in dimension attributes

Materialized as ephemeral (CTEs inlined into downstream models). Business logic joins and enrichments:

• int_order_enriched -- Join orders with items, payments, and reviews
• int_customer_orders -- Aggregate order history per customer
• int_seller_performance -- Seller-level delivery and revenue metrics

Materialized as tables. Star-schema models optimized for dashboard queries:

• fct_orders -- Grain: one row per order with total revenue, items, delivery times
• fct_order_items -- Grain: one row per line item with denormalized product/seller attributes
• dim_customers -- Customer dimension with geographic attributes and order history
• dim_products -- Product dimension with category and physical attributes
• dim_sellers -- Seller dimension with location and performance metrics
• dim_dates -- Date dimension for time-series analysis

# Run all ingestion tests make test # With coverage pytest ingestion/tests/ -v --cov=ingestion/src --cov-report=term-missing

The project uses both generic and schema tests defined in _staging__models.yml:

# Run all dbt tests make dbt-test # Run tests for a specific model cd dbt_project && dbt test --select stg_orders

# Check style make lint # Auto-format make format

The project uses two GitHub Actions workflows:

Triggered on PRs that modify dbt_project/**.

Each PR gets an isolated Snowflake schema (CI_PR_42, CI_PR_87, etc.) so parallel PRs never conflict.

Triggered on push to main.

lint-and-test ──> terraform-apply ──> dbt-deploy ──> deploy-dashboard 

The terraform-apply, dbt-deploy, and deploy-dashboard jobs use the production GitHub environment, which can be configured with required reviewers and deployment protection rules.

Contributions are welcome. Please follow these guidelines:

1. Fork the repository and create a feature branch from main.
2. Write tests for any new ingestion logic or dbt models.
3. Follow the style guide:
   • Python: Black formatter (line length 120), flake8 linting
   • SQL: Lowercase keywords, trailing commas, CTEs over subqueries
   • Commits: Conventional Commits format (feat:, fix:, docs:, etc.)
4. Run the checks locally before pushing:

   make lint make test make dbt-build

5. Open a Pull Request against main with a clear description of changes.
6. CI must pass before the PR can be merged.

# Install all dependencies (including dev tools) make setup # Run the full lint + test suite make lint && make test

This project is licensed under the MIT License.

MIT License Copyright (c) 2024 DataFlow Pro Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 

• Dataset: Brazilian E-Commerce Public Dataset by Olist (CC BY-NC-SA 4.0)
• dbt Packages: dbt_utils by dbt Labs, dbt_expectations by Calogica