ETL Pipeline: API → PostgreSQL → Power BI — 50K+ Records, Fully Automated

The Problem

The client had operational data living entirely inside a third-party platform, accessible only through its API. There was no way to analyze it at scale, combine it with other data sources, or build dashboards without manual exports.

• Data was locked inside an API — no direct database access
• Manual CSV exports were stale the moment they were downloaded
• 50,000+ records with new data coming in daily — too large for manual handling
• No reporting layer — analysts had to work directly in raw exports
• Previous attempts to use Power BI direct query on the API were too slow

The Solution

I designed and built a complete ETL pipeline: a Python script handles API extraction and transformation, PostgreSQL serves as the analytics warehouse, and Power BI connects to the database for fast, automated reporting.

• Python handles OAuth authentication, pagination, rate limiting, and error recovery
• Incremental load logic: only new or updated records are fetched on each run — not a full reload
• Data is cleaned and transformed in Python before loading (type casting, null handling, deduplication)
• PostgreSQL star schema: fact and dimension tables optimized for reporting queries
• Power BI connects via DirectQuery to PostgreSQL — dashboards always show current data
• Pipeline runs on a daily schedule with email alerts on success or failure

Architecture

What Was Built

• Python ETL script — API extraction with auth, pagination, retry logic, and incremental sync
• PostgreSQL schema — Fact tables, dimension tables, materialized views for reporting
• Power BI dashboard — 5-page reporting suite: overview, trends, breakdown, drillthrough, alerts
• Monitoring & alerts — Email notification on success, error logging for failures
• Documentation — Full setup guide, schema docs, and runbook for the client's team

Tech Stack

• Python: pandas, requests, psycopg2, SQLAlchemy, schedule, smtplib
• Database: PostgreSQL with star schema and indexed views
• BI Layer: Power BI (DirectQuery mode, DAX, scheduled refresh)
• Infrastructure: Script hosted on client's server (Windows Task Scheduler)

Results