Home / Works / Philippine Earthquake Warehouse

Open the live map

Overview

An end-to-end analytics warehouse for earthquakes in and around the Philippines. Airflow loads the USGS earthquake API into Snowflake every morning, dbt models it into a star schema with slowly changing history, and GitHub Actions lints and builds every change in an isolated schema. The same marts feed a public interactive map that refreshes daily. The project was scaffolded with Claude Code and then reviewed, run and extended by me.

How it works

Two sources land in raw Snowflake tables, dbt turns them into a star schema, and the marts feed both the analytics models and the public map. Every change goes through CI first.

InputStepDecision / optionData storeOutput

What it demonstrates

• Warehouse: Snowflake with a dedicated warehouse, role and key-pair service user, an internal stage, raw VARIANT tables and a monthly resource monitor.
• Ingestion: Python loader with 30-day windows and a 7-day overlap, newline-delimited JSON staged and loaded with COPY INTO, plus a dry-run mode used by CI.
• dbt: staging, intermediate and mart layers, a seed, tests, source freshness, and generated docs with lineage.
• Dimensional modelling: a star schema with an earthquake fact and date, province, magnitude-band and event-history dimensions, including an offshore row for events outside every province.
• Incremental loads: the fact table merges on a load-time watermark, so re-running a day does not duplicate rows.
• Geospatial SQL: point-in-polygon assignment of each event to a province using Snowflake GEOGRAPHY.
• Slowly changing history: a dbt snapshot tracks USGS revisions to an event and feeds the event-history dimension.
• Advanced SQL: rolling windows over a date spine, lag, first and last value, and qualify with row_number.
• Orchestration and CI: an Airflow DAG (load, source freshness, dbt build) on a daily schedule with retries; GitHub Actions runs SQL lint, the loader dry-run and a full dbt build in an isolated schema per pull request.
• Delivery: a daily export commits a data file only when it changed, and the map is a static page with no server or database.

Screenshots

What it found

From 8,104 events between January 2016 and early November 2026 in the USGS catalogue.

• 83.8% of events are offshore, outside every province polygon, including 8 of the 9 events of magnitude 7 or more.
• Two aftershock sequences dominate: December 2023 (793 events after the M7.6) and June 2026 (365 after the M7.8), against a median of 46 events in a typical month.
• The catalogue is incomplete below about magnitude 4.5: the Gutenberg-Richter b-value is 0.71 from M4.0 but 1.15 from M5, so frequency questions should use M5 and above.

Limits

• The history tracking is built and tested, but no real USGS revision has been captured yet, so I describe it as tracking revisions, not as having tracked any.
• Airflow runs locally in Docker on my laptop, so it is not an always-on production scheduler. The daily export to the map runs on GitHub's servers and is the reliable part.
• There is no separate production environment; models build into development schemas.
• The Snowflake trial ends in early December 2026. After that the warehouse stops refreshing and the live map keeps working from the last committed data file until I move the models to an open-source engine.
• Province polygons were simplified (about 1 km) for the map, and the warehouse uses the simplified set too.
• The code lives in private repositories for now.

Credits