Earthquake Analytics Pipeline

At a glance

Dataset

Approximately 500,000 earthquake events, magnitude 1.0 and above, 2015 to 2025 (USGS Earthquake Catalog)

Infrastructure

Terraform: Cloud Storage bucket, BigQuery dataset, and a service account scoped to GCS and BigQuery

Orchestration

Kestra (Docker), monthly batch runs with a backfill entry point from January 2015

Data lake

Cloud Storage, raw Parquet written per month

Warehouse

BigQuery, partitioned by month and clustered by magnitude category and region

Modeling

dbt in three layers: staging, intermediate, and marts, including a fact table and a monthly aggregate

Geospatial

Latitude, longitude, and depth carried through to the mart for mapping and regional analysis

Dashboard

Looker Studio, embeddable and filterable by magnitude (see below)

Interactive dashboard

Filter by magnitude category and hover the tiles to explore. If the embed does not load, open the report directly in Looker Studio.

Architecture

What I built

• Reproducible infrastructure. Terraform provisions the storage bucket, the BigQuery dataset, and a least-privilege service account, so the whole platform can be torn down and rebuilt from code.
• Monthly batch ingestion. A Python extractor pulls each month from the USGS API (magnitude 1.0 and above) and writes Parquet to Cloud Storage, with a backfill path that replays from January 2015 to the present.
• Cost-aware warehouse design. An external table reads the raw Parquet, then a materialized table is defined with PARTITION BY DATE_TRUNC(event_date, MONTH) and CLUSTER BY mag_category, region, which limits bytes scanned for the dashboard's most common filters.
• Layered transformations. dbt stages the raw events, enriches them with derived fields such as magnitude and depth categories, and produces a fact table plus a monthly aggregate.
• Geospatial output. Coordinates and depth survive into the mart, so the same model supports mapping, regional ranking, and depth-versus-magnitude analysis.
• Dashboard. A Looker Studio report showing earthquake frequency per month (filterable by magnitude category) and the top countries by count for magnitude 5.0 and above, coloured by average magnitude.

Engineering decisions worth noting

• Why partition by month and cluster by magnitude and region. The dashboard always filters by time and usually by magnitude band or country. Partitioning on the date column prunes whole months, and clustering on the two most filtered columns keeps scans small, which is the main cost lever in BigQuery.
• Why Terraform instead of clicking through the console. A migration or rebuild should be a code change and a diff, not a set of undocumented console actions. This is also the habit I bring to client work.
• Why an external table before a materialized table. It keeps the raw Parquet auditable in Cloud Storage while the materialized, partitioned table does the heavy lifting for queries.
• Why monthly batches. The source updates continuously, but the analytical questions here are monthly trends. Batching monthly is the cheapest design that answers them.

Honest notes