Storing 2M Analytics Events: From Heavy JSON to Parquet & DuckDB

Storing 2 Million Analytics Events: From Heavy JSON to Parquet & DuckDB

I recently pulled a month of raw page view logs from Google Analytics to do some local analysis. The dataset contained 2,000,000 events—page views, referrers, device categories, countries, and session IDs.

Here is the story of how I went from a bloated 617 MB JSON dump down to a 40 MB Parquet file queried blazingly fast with DuckDB.

---

1. Default Choice: Raw JSON (617 MB)

JSON is usually my go-to default format for quick data dumps. Each event in the dump looked like a standard analytics payload:

{
  "event_id": 1,
  "occurred_at": "2026-06-28T08:46:53.251Z",
  "event_name": "page_view",
  "visitor_id": "v_0181321",
  "session_id": "s_01269248",
  "page_path": "/base64-encoder/",
  "source": "google",
  "medium": "organic",
  "country": "US",
  "device_category": "desktop",
  "browser": "Safari",
  "engagement_time_msec": null,
  "is_engaged": 0
}

The problem? Across 2 million records, repeating field names like "event_id", "page_path", and "device_category" for every single row added up to 617.76 MB.

Parsing or holding a 600+ MB JSON array in memory was slow and clunky for simple exploration.

---

2. Step 1: Moving to CSV (234 MB)

My first reaction was simple: JSON is wasting huge amounts of space repeating schema keys. CSV makes far more sense here because the header line defines the field names once.

Converting the dataset to CSV dropped the file size down to 234.41 MB—a ~62% reduction.

While 234 MB was much better, filtering and aggregating a raw CSV file using custom Python scripts or command-line utilities felt like reinventing a query engine. I wanted real SQL.

---

3. Step 2: Loading into SQLite (239 MB)

To get proper SQL support, the obvious next move was SQLite. I created a table and loaded the 2 million rows.

This immediately hit two walls:

1. Size didn't improve: The .sqlite file came out to 239.82 MB—slightly larger than CSV! SQLite's row-oriented storage adds per-row header metadata, and text fields aren't compressed across rows.
2. Slow analytical queries: SQLite is built for operational transactions and point lookups, not analytical scans. Running an aggregation query like GROUP BY page_path meant scanning every single byte of every row sequentially. It worked, but it felt sluggish.

---

4. Step 3: Discovering Parquet + DuckDB (40 MB)

At this point, I wasn't sure where to go next. I had heard of Apache Parquet in the past, but always associated it with complex, enterprise Hadoop and Big Data infrastructures. And I didn't know DuckDB even existed.

After probing an LLM for hours about better ways to query large event dumps locally, it suggested converting the data to Parquet and querying it with DuckDB.

Many discussions online conflate the two, but the distinction is clear: Parquet is the compressed storage format, and DuckDB is the query engine used to run fast SQL against it.

Why Parquet Shrank the File to 40 MB

When I converted the dataset to Parquet, the file size plummeted to 40.38 MB—over 93.5% smaller than JSON and 82.8% smaller than CSV.

Because Parquet is a columnar storage format, values in the same column (like country, browser, device_category, or page_path) are stored together contiguously. In analytics data, these columns have low cardinality and lots of repeated values, allowing dictionary encoding and bit-packing to compress the file dramatically.

Fast Queries with DuckDB (and in the Browser!)

DuckDB lets you query Parquet files directly using standard SQL without needing to import them into a database server first:

SELECT
  country,
  page_path,
  COUNT(*) AS page_views
FROM 'events.parquet'
WHERE occurred_at >= TIMESTAMP '2026-06-24'
  AND occurred_at < TIMESTAMP '2026-07-01'
GROUP BY country, page_path
ORDER BY page_views DESC
LIMIT 20;

Because DuckDB reads only the specific columns needed for a query, running analytical aggregations across 2 million rows takes milliseconds.

Even better: thanks to DuckDB-Wasm, DuckDB and Parquet can run entirely client-side inside the browser—making it possible to build fast, local analytics dashboards with zero server backend.

---

Summary of Data Sizes

Stage / Format	File Size	Notes
1. JSON (Default)	617.76 MB	Repeating field keys 2M times wastes space; heavy in memory
2. CSV	234.41 MB	Strips key repetition (~62% smaller), but hard to query cleanly
3. SQLite	239.82 MB	Easy SQL, but row-oriented overhead offers no size win & slower OLAP scans
4. Parquet + DuckDB	40.38 MB	Columnar compression crushes repetitive text; DuckDB queries it instantly

---