🌦️ Weather Data Engineering Application

This project implements an end-to-end data engineering pipeline for ingesting, aggregating, and serving large-scale time-series weather data.
It demonstrates schema design, idempotent batch processing, REST APIs, automated testing, and cloud deployment considerations.

---

📌 Project Highlights

• PostgreSQL schema designed using pure SQL DDL
• Idempotent weather data ingestion and aggregation
• REST API built using FastAPI
• Filtering, pagination, and statistics endpoints
• Automated API tests using pytest
• AWS deployment strategy documented

---

1️⃣ Clone Repository & Initialize Git

git clone <REPO_URL>
cd weather-data-app
git init
git branch -M main
git status

---

2️⃣ Database Setup (PostgreSQL)

PostgreSQL is used as the relational datastore.
The schema enforces data integrity through uniqueness constraints and improves query performance through indexing.

🗄️ Create Tables

SQL Queries:

CREATE TABLE weather_observations (
    id SERIAL PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    observation_date DATE NOT NULL,
    max_temp_c NUMERIC,
    min_temp_c NUMERIC,
    precipitation_mm NUMERIC,
    CONSTRAINT unique_station_date UNIQUE (station_id, observation_date)
);

CREATE INDEX idx_weather_station_date
ON weather_observations (station_id, observation_date);

CREATE TABLE weather_yearly_stats (
    id SERIAL PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    year INTEGER NOT NULL,
    avg_max_temp_c NUMERIC,
    avg_min_temp_c NUMERIC,
    total_precipitation_cm NUMERIC,
    CONSTRAINT unique_station_year UNIQUE (station_id, year)
);

CREATE INDEX idx_stats_station_year
ON weather_yearly_stats (station_id, year);

🔍 Verify Table Creation

In pgAdmin: weather_db → Right Click → Query Tool

Run the following query:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

---

3️⃣ Python Environment Setup

Create and activate a virtual environment:

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

Verify the PostgreSQL driver:

python -c "import psycopg2; print('psycopg2 OK')"

🧠 VS Code Interpreter Selection

Cmd + Shift + P → Python: Select Interpreter

Select:

Python 3.12.x (venv)

---

4️⃣ Database Connection Validation

Configure database credentials in app/db/session.py:

import psycopg2

def get_connection():
    return psycopg2.connect(
        dbname="weather_db",
        user="postgres",
        password="DATABASE_PASSWORD",
        host="localhost",
        port=5432
    )

Validate the database connection:

python - <<EOF
from app.db.session import get_connection
conn = get_connection()
print("DB session established")
conn.close()
EOF

---

5️⃣ Python Package Initialization

To ensure reliable imports and predictable execution, mark directories as Python packages:

touch app/__init__.py
touch app/db/__init__.py
touch app/services/__init__.py
touch app/api/__init__.py

---

6️⃣ Weather Data Ingestion

Run the ingestion pipeline:

python -m scripts.ingest_weather

Expected output: Records inserted: 1729957

Re-run to validate idempotency:

python -m scripts.ingest_weather

Expected output: Records inserted: 0

Commit:

git add .
git commit -m "Add weather data ingestion pipeline"

---

7️⃣ Yearly Aggregation Pipeline

Run aggregation:

python -m scripts.compute_stats

Verify results: SELECT COUNT(*) FROM weather_yearly_stats;

Re-run and confirm count remains unchanged. Commit:

git add .
git commit -m "Add yearly weather aggregation pipeline"

---

8️⃣ Run the API

Start FastAPI:

uvicorn app.main:app --reload

🔗 Available Endpoints /api/weather

/api/weather?station_id=USC00110072

/api/weather?start_date=1990-01-01&end_date=1990-12-31

/api/weather?limit=10&offset=20

/api/weather/stats

/api/weather/stats?year=1990

/api/weather/stats?station_id=USC00110072

📘 API Docs (Swagger) http://127.0.0.1:8000/docs

---

9️⃣ Run Tests

pytest

Expected: 3 passed in <1s Commit:

git add tests/test_api.py
git commit -m "Fix API root test to use router prefix"

---

🔟 Deployment Notes (AWS)

A high-level AWS deployment strategy is documented in deployment.txt, covering:

Aurora PostgreSQL

ECS Fargate + ECR

EventBridge for scheduled ingestion

Application Load Balancer

CloudWatch for logs and metrics

Commit:

git add deployment.txt
git commit -m "Add AWS deployment approach documentation"

---

Summary

This project demonstrates a production-oriented data engineering workflow, including:

Robust schema design

Large-scale time-series ingestion

Deterministic batch processing

REST API development

Automated testing

Cloud deployment planning

The architecture cleanly separates batch and real-time workloads while remaining simple, scalable, and reliable.