Google Drive to PostgreSQL + Power BI Pipeline

Job Title:
Automated CSV Pipeline from Google Drive to PostgreSQL + Power BI Dashboard (Multi-Company Setup)

⸻

Project Overview:
We are looking for a freelance developer to build an automated data pipeline that retrieves CSV files (exported from SAP/Sage), placed manually by our client in a Google Drive folder, processes and loads them into a PostgreSQL database, and feeds a Power BI dashboard for monitoring key logistics, HR, and administrative KPIs.

The client works with multiple companies, and each company provides its own data exports. The system must be able to handle a multi-entity structure, with proper file naming and schema organization.

⸻

Project Objectives:
• Automatically fetch daily CSV files from Google Drive
• Clean, normalize, and insert data into a PostgreSQL database (with multi-tenant structure)
• Automate the daily process using cron or Airflow
• Connect the PostgreSQL database to Power BI and build a functional KPI dashboard

⸻

Technical Requirements:

1. Google Drive Integration
• Access the folder via Google Drive API (service account)
• Automatically detect and download new files based on naming conventions
• Prevent reprocessing of files (via logs or file archiving)

2. Data Cleaning & Transformation
• Use Python (pandas) to read and clean CSVs
• Standardize formats (dates, strings, numeric types)
• Normalize column names
• Handle common errors (missing columns, encoding issues, malformed rows)

3. PostgreSQL Insertion
• Connect to an existing or new PostgreSQL database
• Design a multi-company structure (schemas or table prefixes per client)
• Ensure deduplication logic (via primary keys or hashes)
• Maintain a log table to track processed files (file name, date, status)

4. Scheduling
• Automate daily execution at 7:00 AM
• Use either cron (simple) or Airflow (if orchestration and monitoring are needed)
• Store logs of each execution run (timestamp, files processed, error summary)

5. Power BI Integration
• Create a .pbix file connected to the PostgreSQL database
• Include filtering by company
• Display the following KPIs:
• Order processing time
• Preparation time
• Time between delivery and invoice
• Employee attendance
• Engagement rate
• Number of days without incident

⸻

Deliverables:
• Python automation script(s)
• Config file (.env or .json) with multi-client setup
• PostgreSQL schema (SQL file or hosted access)
• Power BI dashboard (.pbix file)
• Log table and/or log files
• Technical documentation (setup, usage, client instructions)

⸻

Required Skills:
• Python (pandas, psycopg2, Google Drive API)
• PostgreSQL (schema design, inserts, error handling)
• Power BI Desktop (SQL data sources)
• Git (version control)
• Optional: Airflow, Docker, Linux scripting

⸻

Estimated Timeline:
10 to 14 business days