Case Study · Data Engineering
Earthquake Data ETL and Monitoring API
Automated USGS earthquake ingestion, validation, storage and statistical monitoring through a documented FastAPI service.
A functional data engineering API that extracts recent earthquake data from the USGS GeoJSON feed, transforms and validates each event, stores the results in SQLite and exposes searchable records and aggregated statistics through FastAPI.
- ROLE
- Lead Developer · Data Engineer
- YEAR
- 2025
TECH_STACK
Technologies
CONTEXT
Technical context
Public earthquake data is continuously generated through global monitoring networks, but raw GeoJSON feeds are not always convenient for direct analysis or application integration. Extracting useful insights requires structured ingestion, validation, normalization and access through a consistent interface. This project was developed to transform recent USGS earthquake data into a clean, queryable dataset. It combines an ETL pipeline, relational storage and a documented REST API so that earthquake events and derived statistics can be accessed efficiently by applications, analysts or monitoring tools.
PROBLEM
Problem
Raw earthquake feeds contain nested GeoJSON structures, inconsistent optional fields and data that is not optimized for direct querying or statistical analysis. Applications consuming the feed repeatedly would need to handle extraction, validation, transformation and aggregation independently. The challenge was to build a reliable pipeline that could convert external USGS event data into normalized database records while preserving essential geographic, temporal and alert information. The system also needed to provide efficient filtering, daily summaries and tsunami-related statistics through a clear and documented REST API.
OVERVIEW
Project overview
The system is structured as a data pipeline that connects the USGS GeoJSON feed with a local SQLite database and a FastAPI REST interface.
The extraction layer retrieves earthquake records from the USGS monthly feed and processes the returned GeoJSON feature collection.
Each event is transformed into a normalized structure containing its identifier, magnitude, location, timestamp, latitude, longitude, depth and tsunami-alert status.
Pydantic models validate the processed records and maintain consistent data types across the ingestion, storage and API layers.
The transformed events are stored in SQLite, creating a persistent and queryable local dataset that does not require repeated processing of the raw external feed.
The FastAPI application exposes endpoints for retrieving all stored earthquakes and filtering events by a configurable minimum magnitude.
Additional aggregation endpoints generate daily earthquake summaries and statistics for events associated with tsunami alerts.
Swagger and OpenAPI documentation provide an interactive interface for testing requests and understanding the available response schemas.
The separation of extraction, transformation, persistence and API delivery makes the system easier to maintain and extend.
The resulting architecture can serve as a backend foundation for seismic dashboards, monitoring applications, alerting tools and further geospatial analysis.
APPROACH
Technical approach
- Built an ETL pipeline that retrieves recent earthquake data from the USGS GeoJSON feed.
- Parsed nested GeoJSON features and extracted magnitude, location, timestamp, coordinates, depth and tsunami status.
- Validated and normalized incoming records before database insertion.
- Transformed Unix timestamps and geographic values into consistent application-ready formats.
- Stored processed earthquake events in a structured SQLite database.
- Implemented FastAPI endpoints for retrieving and filtering earthquake records.
- Added minimum-magnitude filtering for targeted event queries.
- Created daily statistical summaries from the stored dataset.
- Implemented tsunami-alert aggregation for rapid risk-related analysis.
- Used Pydantic models to enforce consistent request and response schemas.
- Exposed interactive Swagger and OpenAPI documentation for testing and integration.
- Separated data ingestion, transformation, storage and API delivery into clear processing layers.
FOCUS
Technical focus
- ETL Pipeline Design
- External API Integration
- GeoJSON Data Processing
- Data Extraction and Normalization
- Pydantic Data Validation
- Timestamp and Geospatial Transformation
- SQLite Data Modeling
- Persistent Event Storage
- FastAPI REST API Development
- Magnitude-Based Event Filtering
- Daily Statistical Aggregation
- Tsunami Alert Analysis
- OpenAPI and Swagger Documentation
- Layered Data Architecture
- Seismic Data Processing
ARCHITECTURE
Architecture notes
- The system follows a layered architecture separating data extraction, transformation, storage and API delivery.
- The extraction layer retrieves recent earthquake records from the USGS GeoJSON feed over HTTP.
- The transformation layer parses nested GeoJSON features and converts them into normalized earthquake event records.
- Pydantic models enforce consistent field types and validate data before it reaches the persistence and response layers.
- Each normalized event contains its identifier, magnitude, location, timestamp, latitude, longitude, depth and tsunami-alert status.
- The persistence layer stores processed earthquake records in a local SQLite database for reusable querying.
- The FastAPI layer exposes REST endpoints for retrieving stored events and filtering them by minimum magnitude.
- Dedicated aggregation endpoints calculate daily earthquake summaries and tsunami-alert statistics from the persisted dataset.
- The API automatically generates OpenAPI schemas and interactive Swagger documentation for testing and integration.
- The architecture avoids repeated transformation of raw external data by separating ingestion from downstream querying.
- The modular structure allows the data source, storage engine, validation rules and analytical endpoints to be extended independently.
HIGHLIGHTS
Engineering highlights
- Functional End-to-End Earthquake Data Pipeline
- Automated Extraction from the USGS GeoJSON Feed
- Structured ETL Processing and Data Normalization
- Validated Earthquake Records with Pydantic
- Persistent SQLite Event Storage
- FastAPI REST Interface for Seismic Data
- Minimum-Magnitude Event Filtering
- Daily Earthquake Summary Statistics
- Tsunami Alert Aggregation
- Interactive Swagger and OpenAPI Documentation
- Layered Extraction, Transformation, Storage and Delivery Architecture
- Reusable Backend for Seismic Dashboards and Monitoring Tools
OUTCOME
Outcome
The project resulted in a functional earthquake data service that automatically transforms raw USGS GeoJSON records into a structured and queryable SQLite dataset. Users and applications can retrieve recent events, filter earthquakes by magnitude and access aggregated daily and tsunami-related statistics through a documented FastAPI interface. The system demonstrates the complete data-engineering workflow from external API extraction and transformation to validation, relational storage and REST API delivery. It provides a reusable foundation for earthquake dashboards, alerting tools, analytical applications and other systems that require reliable access to normalized seismic data.