Most OLAP tutorials start the same way: spin up a ClickHouse cluster, a Druid deployment or a Snowflake warehouse, then pay for it. I wanted to see how far I could get with zero servers and zero cost, while still doing real Online Analytical Processing: a star schema, slice and dice, roll-up and drill-down, pivot tables, CUBE and ROLLUP.
The result is Earthquake OLAP Explorer: two years of global earthquakes (~58,000 events) that you can analyze with SQL inside your browser.
- π Live dashboard: https://dayan-18.github.io/earthquake-olap-duckdb-wasm/
- π» Repository: https://github.com/Dayan-18/earthquake-olap-duckdb-wasm
The idea: move the OLAP engine to the client
A classic OLAP stack looks like this: data source β ETL β OLAP server β BI tool. The OLAP server is the expensive part, because it has to run all the time.
DuckDB is an in-process columnar analytical database, and DuckDB-WASM compiles it to WebAssembly. That means the vectorized query engine runs in the user's browser tab. For datasets measured in megabytes, you don't need an OLAP server at all. You need:
- A pipeline that builds a well-modeled, columnar dataset (Parquet).
- A static host to serve those files.
- DuckDB-WASM to query them.
USGS API βββΊ GitHub Actions (daily) βββΊ GitHub Pages βββΊ Browser
Extract (Python) (static DuckDB-WASM
Transform (DuckDB SQL) files) = OLAP engine
Load (Parquet)
Validate (10 checks)
| Layer | Tool |
|---|---|
| Source | USGS Earthquake Catalog API (public, no key) |
| Processing | Python + DuckDB |
| Storage | Parquet (ZSTD) |
| OLAP engine | DuckDB-WASM |
| Automation | GitHub Actions |
| Hosting | GitHub Pages |
| Charts | Chart.js + Leaflet |
No AWS, no credit card.
Step 1: Extract from the USGS API
The USGS FDSN Event API returns earthquakes as CSV. It caps a single query at 20,000 events, so the pipeline requests one calendar month at a time:
def month_windows(start, end):
cur = start
while cur < end:
nxt = (cur.replace(day=1) + dt.timedelta(days=32)).replace(day=1)
yield cur, min(nxt, end)
cur = nxt
for s, e in month_windows(start, end):
text = fetch_csv({
"format": "csv",
"starttime": s.isoformat(),
"endtime": e.isoformat(),
"minmagnitude": 2.5,
"eventtype": "earthquake",
})
(RAW_DIR / f"usgs_{s:%Y_%m_%d}.csv").write_text(text, encoding="utf-8")
fetch_csv retries with backoff, so a temporary API error doesn't break the nightly run.
Step 2: Transform into a star schema with DuckDB
OLAP is about dimensional modeling. I designed a star schema with one fact table and four dimensions:
dim_date dim_magnitude
(year, quarter, month, week) (Minor β¦ Great)
\ /
dim_region βββββββ fact_earthquake βββββββ dim_depth
(country β region) (1 row per event) (Shallow/Intermediate/Deep)
- Grain: one row per earthquake.
- Measures: magnitude, depth, number of stations, azimuthal gap.
- Hierarchies: year β quarter β month β week β day, and country β region.
All of the transformation is plain SQL running in DuckDB. First, a staging table that types, cleans and deduplicates the raw CSVs. USGS revises events, so I keep only the latest version of each one:
CREATE TABLE stg_earthquake AS
SELECT DISTINCT ON (event_id) *
FROM typed
WHERE magnitude IS NOT NULL AND depth_km IS NOT NULL
ORDER BY event_id, updated_ts DESC;
The place field looks like "10 km SW of Ridgecrest, CA". I extract the area after the last comma and map US state codes to a real hierarchy, United States β California. Then the fact table gets its surrogate keys through range joins against the dimensions:
CREATE TABLE fact_earthquake AS
SELECT s.event_id,
CAST(strftime(s.event_ts, '%Y%m%d') AS INTEGER) AS date_key,
r.region_key, m.magnitude_key, dp.depth_key,
s.magnitude, s.depth_km, s.latitude, s.longitude, s.place
FROM stg_earthquake s
JOIN dim_region r ON r.region_name = s.region_name AND r.country = s.country
JOIN dim_magnitude m ON s.magnitude >= m.min_mag AND s.magnitude < m.max_mag
JOIN dim_depth dp ON s.depth_km >= dp.min_km AND s.depth_km < dp.max_km;
Finally, every table is exported to Parquet:
COPY fact_earthquake TO 'web/data/fact_earthquake.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
58,025 earthquakes fit in about 2 MB. That is the advantage of columnar storage plus compression.
Step 3: A data quality gate
A public dashboard shouldn't break because an API returned something odd, so validate.py runs 10 checks before every deploy. They cover unique event_ids, no NULL foreign keys, referential integrity with ANTI JOIN, physical ranges for magnitude and coordinates, and a date dimension with no gaps:
"every region_key exists in dim_region":
"SELECT count(*) FROM fact_earthquake f ANTI JOIN dim_region r USING (region_key)",
If any check fails, the script exits with code 1, the workflow stops, and the previous version of the site stays online.
Step 4: Deploy through automation with GitHub Actions
The workflow runs on every push, every day at 06:00 UTC, and on demand. It extracts fresh data, transforms it, validates it and publishes it:
on:
push:
branches: [main]
schedule:
- cron: "0 6 * * *"
workflow_dispatch:
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
- uses: actions/setup-python@v7
with: { python-version: "3.11", cache: pip }
- run: pip install -r requirements.txt
- run: python etl/pipeline.py --days 730 --min-mag 2.5
- run: python etl/validate.py
- uses: actions/configure-pages@v6
- uses: actions/upload-pages-artifact@v5
with: { path: web }
deploy:
needs: build
runs-on: ubuntu-latest
environment: { name: github-pages }
steps:
- uses: actions/deploy-pages@v5
The generated Parquet files are not committed to git. They are build artifacts, the same way a compiled binary would be.
Step 5: The OLAP engine runs in your browser
When the page loads, it downloads DuckDB-WASM from a CDN, fetches the Parquet files and loads them into an in-memory database:
import * as duckdb from 'https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.0/+esm';
const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
const workerUrl = URL.createObjectURL(
new Blob([`importScripts("${bundle.mainWorker}");`], { type: 'text/javascript' }));
const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), new Worker(workerUrl));
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
const conn = await db.connect();
for (const t of ['fact_earthquake', 'dim_date', 'dim_region', 'dim_magnitude', 'dim_depth']) {
const buf = new Uint8Array(await (await fetch(`data/${t}.parquet`)).arrayBuffer());
await db.registerFileBuffer(`${t}.parquet`, buf);
await conn.query(`CREATE TABLE ${t} AS SELECT * FROM read_parquet('${t}.parquet')`);
}
From then on, every filter change produces SQL that runs locally. Refreshing the whole dashboard (KPIs, time series, top regions, pivot table and map) takes around 100β250 ms, with no network round-trip.
The OLAP operations
Every classic OLAP operation is available in the UI:
| Operation | How it works in the dashboard |
|---|---|
| Slice | Choose one country or one depth class |
| Dice | Combine date range, minimum magnitude, country and depth |
| Roll-up / Drill-down | Time grain selector: year β quarter β month β week β day (date_trunc) |
| Geographic drill-down | Click "United States" to see Alaska, California, Nevada⦠|
| Pivot | Depth Γ magnitude class matrix with row and column totals |
There is also a SQL console with ready-made examples. My favorite uses GROUP BY ROLLUP to get subtotals per year and a grand total in a single query:
SELECT d.year, d.quarter, count(*) AS events, round(avg(f.magnitude), 2) AS avg_mag
FROM fact_earthquake f
JOIN dim_date d USING (date_key)
GROUP BY ROLLUP (d.year, d.quarter)
ORDER BY d.year NULLS LAST, d.quarter NULLS LAST;
Other examples cover GROUP BY CUBE (country Γ magnitude class), window functions (3-month moving average with lag), QUALIFY for top-N per group, and an ASCII histogram with DuckDB's bar() function.
What the data shows
- The strongest event in the window is the M8.8 2025 Kamchatka Peninsula earthquake. Its aftershocks make Russia the second most active country in the dataset.
- About 80% of M2.5+ earthquakes are shallow (< 70 km), and those are the most dangerous for people at the surface.
- Deep earthquakes (β₯ 300 km) are rare but almost never "Minor". Only very energetic events are detected at that depth.
Limitations and when to use something else
This architecture is great for datasets up to a few hundred MB, read-heavy public dashboards, and teams that want zero infrastructure. It is not a replacement for ClickHouse, Druid or Pinot when you need:
- Billions of rows or real-time streaming ingestion.
- Many concurrent users writing data.
- Row-level security, because every user downloads the full dataset.
For those cases, the same star schema and SQL can move to a server engine. DuckDB SQL is very close to what ClickHouse or Snowflake accept.
Try it yourself
git clone https://github.com/Dayan-18/earthquake-olap-duckdb-wasm
cd earthquake-olap-duckdb-wasm
pip install -r requirements.txt
python etl/pipeline.py && python etl/validate.py
python -m http.server 8000 --directory web
To deploy your own copy, fork the repo, set Settings β Pages β Source: GitHub Actions, and push.
Live demo: https://dayan-18.github.io/earthquake-olap-duckdb-wasm/
Code: https://github.com/Dayan-18/earthquake-olap-duckdb-wasm
If you have questions or ideas, such as adding Apache Arrow streaming or a ClickHouse version for comparison, leave a comment. π
Top comments (0)