DEV Community

Cover image for I Built a Serverless OLAP Engine in the Browser with DuckDB-WASM (No AWS, $0)
DAYAN ELVIS JAHUIRA PILCO
DAYAN ELVIS JAHUIRA PILCO

Posted on Edited on

I Built a Serverless OLAP Engine in the Browser with DuckDB-WASM (No AWS, $0)

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.

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:

  1. A pipeline that builds a well-modeled, columnar dataset (Parquet).
  2. A static host to serve those files.
  3. 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)
Enter fullscreen mode Exit fullscreen mode
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")
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Finally, every table is exported to Parquet:

COPY fact_earthquake TO 'web/data/fact_earthquake.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
Enter fullscreen mode Exit fullscreen mode

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)",
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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')`);
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)