Earthquake Data ETL and Monitoring API
Python data pipeline and FastAPI service for earthquake ingestion, persistence, and statistical analysis
An earthquake data processing system that transforms USGS GeoJSON feeds into structured SQLite records and exposes seismic statistics through a REST API.
- Role
- Software Engineer — Python Backend & Data Engineering
- Year
- 2025
Technologies
Technical context
The USGS provides earthquake observations through structured GeoJSON feeds. Integrating this data into analytical applications requires extracting relevant event attributes, maintaining persistent records, and exposing consistent query interfaces.
Problem
Transform nested earthquake data into a queryable database while handling incomplete records, duplicate event identifiers, timestamp conversion, and common statistical queries.
Project overview
The ETL pipeline retrieves earthquake observations from the USGS GeoJSON feed and transforms nested feature data into structured records. Each event includes its identifier, magnitude, location, UTC timestamp, geographic coordinates, depth, and tsunami-alert indicator. SQLite provides persistent storage, with duplicate event IDs handled during batch insertion.
The FastAPI service exposes recent earthquakes with optional magnitude filtering and dedicated endpoints for daily activity summaries and tsunami-alert counts. Pydantic models define the response structures, while SQL performs statistical aggregation directly against the stored dataset.
Technical approach
- Retrieve earthquake records from the USGS monthly GeoJSON feed
- Extract and normalize event attributes, geographic coordinates, and UTC timestamps
- Store structured records in SQLite with primary-key duplicate handling
- Develop FastAPI endpoints with magnitude filtering and typed response models
- Implement daily earthquake summaries and tsunami-alert statistics using SQL aggregation
Technical focus
- ETL pipeline development and external API integration
- Structured data transformation and timestamp normalization
- SQLite database design and duplicate handling
- REST API development and response validation
- SQL aggregation and statistical data processing
Architecture notes
- Standalone Python ETL workflow retrieving and transforming USGS GeoJSON data
- SQLite earthquake table with event identifiers as primary keys
- Batch persistence using parameterized SQL statements and INSERT OR IGNORE
- FastAPI endpoints accessing SQLite through dependency-managed connections
- SQL GROUP BY queries for daily magnitude summaries and tsunami-alert counts
Engineering highlights
- End-to-end processing from USGS data extraction to persistent SQLite storage
- Database snapshot containing 8,612 earthquake records
- Duplicate-safe insertion using unique earthquake identifiers
- Magnitude-filtered retrieval and SQL-based statistical aggregation
- Automatically generated OpenAPI documentation and Swagger interface
Outcome
Implemented an end-to-end ETL and REST API workflow with persistent earthquake records, magnitude-based filtering, and SQL analytics. The included SQLite snapshot contains 8,612 events, accessible through documented API endpoints.