An automated data pipeline that collects weather and air quality data for Indian cities from two different APIs, cleans it, checks its quality, and loads it into a database that is ready for analysis and dashboards.

Demo data: 35+ Indian cities, updated every 3 hours. The same design works for any business that needs to pull data from several messy sources into one reliable place: sales platforms, ad accounts, CRMs, IoT sensors, or public data.

Data lives in different APIs and spreadsheets, in different formats, with inconsistent names and gaps. Analysts end up cleaning it by hand every week, and mistakes slip into reports.

Collects automatically. Scheduled runs pull data from two APIs (Open-Meteo and WAQI) with no manual work.

Keeps the original data. Raw responses are stored untouched in MongoDB, so nothing is lost and past runs can be re-checked or re-processed.

Cleans messy inputs. Handles whitespace, inconsistent capitalisation, old city names (Bombay → Mumbai), missing coordinates, duplicates, and placeholder values.

Matches names intelligently. Uses exact, alias, and fuzzy matching, and sends anything uncertain to a review table instead of guessing.

Checks quality before anyone uses the data. 10 automated checks (freshness, completeness, valid ranges, duplicates, and more) run after every load. Serious problems stop the run and are logged.

Safe to re-run. Re-running a day never creates duplicate rows.

Handles failures. If one city's API call fails, only that task retries and the rest continue.

Analysis-ready. Data is organised as a star schema in PostgreSQL, with example SQL for rolling averages, pollution streaks, and weekly city rankings.

Tested. 77 automated tests, all running without a live database or API.

APIs → raw storage (MongoDB) → cleaning and matching (Python/Pandas) → data warehouse (PostgreSQL) → quality checks → analytics queries

Combining data from several SaaS tools into one reporting database

Automating weekly or daily data refreshes for dashboards (Tableau, Power BI, Metabase)

Cleaning legacy spreadsheets and CSV exports

Adding automated data quality monitoring to an existing pipeline

Python, Apache Airflow, MongoDB, PostgreSQL, Pandas, RapidFuzz, Docker Compose, pytest

I build and automate ETL pipelines and data cleaning workflows for small businesses. Contact: rishibarapatre@gmail.com