Global Earthquake Monitor

I first set up a lakehouse named earthquakes_lakehouse to store raw seismic data from the USGS API. It is the foundation for structuring the data with the Medallion architecture. Ingestion is done from a notebook in the lakehouse, using Python to call:

GET https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2014-01-01&endtime=2014-01-02

The data is then stored as Delta Lake tables, which bring ACID transactions, schema enforcement and time travel — a reliable, scalable format for the next stages.

A Python script retrieves earthquake events from the API and stores them in the Bronze layer, over a rolling 7-day window so the dataset stays current.

1. 01Dynamic dates — start_date is seven days ago and end_date is yesterday; both are later parameterised from Data Factory.
2. 02API request — the script builds the URL with these dates, requests GeoJSON and keeps only the features field, which holds the events.
3. 03Lakehouse storage — on success, the events are saved as JSON in /lakehouse/default/Files/ with a dated filename (YYYY-MM-DD_earthquake_data.json).

1.1.png

The JSON file is then loaded into a Spark DataFrame so the data can be progressively refined through the Medallion layers.

1.2-1.png

In the Silver layer the raw events are cleaned and structured: relevant attributes are extracted and meaningful transformations applied.

1. 01Extracting fields — nested JSON is flattened and renamed, focusing on location (latitude, longitude, elevation), magnitude, significance, timestamps and descriptions.
2. 02Reshaping — geometry.coordinates is split into longitude, latitude and elevation; properties provides title, place, significance (sig), magnitude (mag), magnitude type (magType) and timestamps.

2.1-1.png

1. 01Readable timestamps — time and updated are stored as Unix epoch milliseconds and converted to standard timestamps.
2. 02Silver table — the cleaned data is persisted to earthquake_events_silver in append mode, so new events are added without overwriting history.
3. 03Querying — the Silver table can be explored with SQL directly in the lakehouse.

3.1-2.png3.2.png

• →Only recent events (after start_date) are read from Silver for processing.
• →The country code is derived from latitude and longitude with the reverse_geocoder library.
• →Because Spark works on distributed data, get_country_code is registered as a UDF to be used in transformations.

4.png

1. 01Country code — get_country_code_udf adds a country_code column for geographic analysis.
2. 02Significance classes — events are classified as Low (sig < 100), Moderate (100 ≤ sig < 500) or High (sig ≥ 500).
3. 03Gold table — the refined DataFrame is appended to earthquake_events_gold.

4.2-1.png4.3.png

A Data Factory pipeline runs the three notebooks every day, in order:

1. 01Bronze Notebook — runs the raw ingestion with dynamic parameters: start_date → yesterday, end_date → today.
2. 02Silver Notebook — chained on success, receives start_date.
3. 03Gold Notebook — chained on success, receives start_date.

5.png

The whole workflow is now automated: Bronze, Silver and Gold run sequentially on a schedule, so fresh earthquake data is available every day without manual intervention.

On top of the Gold layer, a semantic model defines relationships and calculated measures so that analysis and visualisation in Power BI are efficient.

6.1.png6.2-2.png

The final report visualises global seismic activity. Users filter by date range and significance (High, Moderate, Low); the map sizes each bubble by significance, and KPIs show the total number of earthquakes and the maximum significance.

dh3.png