Running Ad-Hoc Video Analytics with DuckDB on Parquet Exports

The 2 AM query that broke my SQLite habit

Last winter a partner asked me a simple-sounding question: which video categories drove the most watch-through on mobile in India over the previous 90 days, broken down by week, and how did that compare to desktop. On DailyWatch our production store is SQLite with FTS5 behind PHP 8.4 and LiteSpeed, and that stack is genuinely great for what it does — full-text discovery, fast single-row lookups, cheap hosting. But this was an analytical question over roughly 40 million watch events, and running it against the live database meant either locking up the file that serves real traffic or building yet another summary table I'd have to maintain forever.

I already export raw events to Parquet nightly for archival. The obvious move was to query those Parquet files directly, without loading anything into a warehouse, without spinning up Spark, without paying for BigQuery slots to answer a one-off question. That's exactly what DuckDB does: it's an in-process analytical SQL engine — think "SQLite for analytics" — that reads Parquet natively, runs vectorized columnar queries, and disappears when you close the process. No server, no daemon, no cluster.

This article is the workflow I settled on after a few months of using DuckDB for these ad-hoc questions. It covers how to export clean Parquet from SQLite, how to query it directly (including remote files over HTTP), the join and window patterns that actually matter for video metrics, and the performance knobs that took my worst query from 90 seconds to under 3.

Why Parquet, and why DuckDB reads it well

Parquet is a columnar format. A watch-event row has maybe 15 fields — video_id, user_hash, category_id, country, device, watch_seconds, video_seconds, timestamp, referrer, and so on — but an analytical query usually touches three or four of them. Row-oriented storage (CSV, a SQLite table) forces you to read every byte of every row even when you only want two columns. Parquet stores each column contiguously, so DuckDB reads only the columns your query names, and it reads them compressed.

The practical wins for video analytics specifically:

• Projection pushdown: SELECT country, SUM(watch_seconds) touches two columns out of fifteen. On a 6 GB export that's the difference between reading 6 GB and reading ~700 MB.
• Predicate pushdown with row-group statistics: Parquet files store min/max per column per row group. A WHERE ts >= '2025-01-01' skips entire row groups whose max timestamp is older, without decompressing them.
• Zero-copy scanning: DuckDB doesn't import Parquet into an internal format first. It scans the file in place. You point a query at a file (or a glob of files) and go.

The last point is the one that changed my habits. There is no load step. The Parquet file is the table.

Exporting clean Parquet from SQLite

Before any analytics, you need trustworthy exports. The trap here is types: SQLite is dynamically typed, so a column that's "mostly integers" can contain the occasional string, and if you export naively you get a Parquet column typed as VARCHAR that ruins downstream aggregation. I export through a small PHP script that runs against a read-only copy of the live database, casting explicitly so the Parquet schema is stable.

<?php
declare(strict_types=1);

// export_events.php — nightly Parquet export from SQLite for DuckDB analytics.
// Runs against a snapshot copy so it never contends with live traffic.

$snapshot = '/var/backups/dailywatch/events-' . date('Y-m-d') . '.sqlite';
copy('/srv/dailywatch/data/app.sqlite', $snapshot);

$pdo = new PDO('sqlite:' . $snapshot, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('PRAGMA query_only = TRUE;');

// DuckDB can attach and read a SQLite file directly, but exporting to
// partitioned Parquet keeps the analytical store independent of the
// production schema and gives us cheap columnar reads.
$duck = '/usr/local/bin/duckdb';
$outDir = '/srv/dailywatch/warehouse/events';

// One partition per day keeps files small and predicate pushdown sharp.
$day = date('Y-m-d', strtotime('yesterday'));

$sql = <<<SQL
INSTALL sqlite; LOAD sqlite;
ATTACH '{$snapshot}' AS src (TYPE sqlite);
COPY (
    SELECT
        CAST(video_id     AS BIGINT)   AS video_id,
        CAST(category_id  AS INTEGER)  AS category_id,
        CAST(country      AS VARCHAR)  AS country,
        CAST(device       AS VARCHAR)  AS device,
        CAST(watch_seconds AS DOUBLE)  AS watch_seconds,
        CAST(video_seconds AS DOUBLE)  AS video_seconds,
        CAST(ts           AS TIMESTAMP) AS ts
    FROM src.watch_events
    WHERE DATE(ts) = DATE '{$day}'
) TO '{$outDir}/dt={$day}/events.parquet'
  (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 122880);
SQL;

$cmd = escapeshellarg($duck) . ' -c ' . escapeshellarg($sql);
exec($cmd, $out, $code);
if ($code !== 0) {
    fwrite(STDERR, "DuckDB export failed: " . implode("\n", $out) . "\n");
    exit(1);
}
unlink($snapshot);
echo "Exported {$day} to {$outDir}/dt={$day}/events.parquet\n";

Three decisions in there matter:

• ZSTD compression gives better ratios than Snappy at a small CPU cost — for archival Parquet you read more often than you write, so it pays off.
• ROW_GROUP_SIZE 122880 (120K rows) is a sane default. Too-small row groups bloat metadata; too-large ones weaken predicate pushdown because each group's min/max spans more data.
• Hive-style partitioning (dt=YYYY-MM-DD/) lets DuckDB prune whole directories when you filter on dt, and it lets you glob a date range without listing every file.

Querying Parquet directly — no load step

Now the fun part. From a plain DuckDB shell or any client, you query the files as if they were tables. Globs and Hive partitioning just work.

-- Weekly watch-through rate by category, mobile vs desktop, for India.
-- Reads only the columns and partitions the query needs.
SELECT
    date_trunc('week', ts)                          AS wk,
    category_id,
    device,
    COUNT(*)                                        AS plays,
    SUM(watch_seconds) / NULLIF(SUM(video_seconds), 0) AS watch_through
FROM read_parquet(
        '/srv/dailywatch/warehouse/events/dt=*/*.parquet',
        hive_partitioning = true
     )
WHERE country = 'IN'
  AND device IN ('mobile', 'desktop')
  AND ts >= now() - INTERVAL 90 DAY
GROUP BY wk, category_id, device
ORDER BY wk, watch_through DESC;

On my export this scans ~90 daily partitions, and because country, device, and ts are all pushed down, DuckDB never decompresses the columns it doesn't need or the row groups outside the window. The whole thing returns in a couple of seconds on a laptop.

A few patterns I reach for constantly:

• read_parquet('.../dt=*/*.parquet') — glob across partitions. Combine with hive_partitioning = true so dt becomes a usable column.
• SELECT * FROM 'file.parquet' — DuckDB lets you put a Parquet path directly in the FROM clause with no function call at all, which is perfect for quick pokes.
• DESCRIBE SELECT * FROM 'file.parquet' — inspect the schema without reading data. First thing I run against an unfamiliar export.
• read_parquet([...]) with an explicit list when you want specific files rather than a glob.

Window functions carry video metrics

Most interesting video questions are ranking or sequencing questions: top N videos per category, session drop-off, rolling retention. DuckDB has full SQL window support and it's fast because it's vectorized. Here's the query I use to find, per category, the videos that overperform their category's median watch-through — the ones worth surfacing more aggressively in discovery.

WITH per_video AS (
    SELECT
        category_id,
        video_id,
        COUNT(*)                                           AS plays,
        SUM(watch_seconds) / NULLIF(SUM(video_seconds),0)  AS wtr
    FROM read_parquet('/srv/dailywatch/warehouse/events/dt=*/*.parquet',
                      hive_partitioning = true)
    WHERE ts >= now() - INTERVAL 30 DAY
    GROUP BY category_id, video_id
    HAVING COUNT(*) >= 500          -- ignore low-sample noise
)
SELECT
    category_id,
    video_id,
    plays,
    round(wtr, 3)                                          AS wtr,
    round(median(wtr) OVER (PARTITION BY category_id), 3)  AS cat_median,
    round(wtr - median(wtr) OVER (PARTITION BY category_id), 3) AS lift,
    rank() OVER (PARTITION BY category_id ORDER BY wtr DESC)    AS rnk
FROM per_video
QUALIFY rnk <= 10
ORDER BY category_id, rnk;

Two things worth calling out. First, QUALIFY filters on a window function result without a subquery — it's the WHERE for window expressions, and it makes top-N-per-group queries readable. Second, median() OVER (...) gives you a per-category baseline in the same pass, so "lift over category median" falls out directly. Doing this against the live SQLite database would mean a correlated subquery per row or a temp table; here it's one vectorized scan.

Driving DuckDB from application code

Ad-hoc doesn't have to mean the interactive shell. I wire the same engine into small internal tools. DuckDB has a native Go driver (database/sql compatible), which is what I use for a lightweight internal dashboard endpoint. The key is that DuckDB opens in-process against :memory: and reads the Parquet warehouse on demand — there's no connection pool to a remote server because there's no server.

package main

import (
    "database/sql"
    "encoding/json"
    "log"
    "net/http"

    _ "github.com/marcboeker/go-duckdb" // pure-cgo DuckDB driver
)

func main() {
    // In-process, in-memory catalog. Parquet files are the tables.
    db, err := sql.Open("duckdb", "")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Tune once at startup; DuckDB caps memory and threads itself.
    db.Exec(`SET memory_limit = '4GB'`)
    db.Exec(`SET threads = 4`)

    http.HandleFunc("/api/top-countries", func(w http.ResponseWriter, r *http.Request) {
        const q = `
            SELECT country, COUNT(*) AS plays,
                   round(SUM(watch_seconds)/3600.0, 1) AS watch_hours
            FROM read_parquet('/srv/dailywatch/warehouse/events/dt=*/*.parquet',
                              hive_partitioning = true)
            WHERE ts >= now() - INTERVAL 7 DAY
            GROUP BY country
            ORDER BY plays DESC
            LIMIT 20`

        rows, err := db.QueryContext(r.Context(), q)
        if err != nil {
            http.Error(w, err.Error(), 500)
            return
        }
        defer rows.Close()

        type row struct {
            Country    string  `json:"country"`
            Plays      int64   `json:"plays"`
            WatchHours float64 `json:"watch_hours"`
        }
        var out []row
        for rows.Next() {
            var x row
            if err := rows.Scan(&x.Country, &x.Plays, &x.WatchHours); err != nil {
                http.Error(w, err.Error(), 500)
                return
            }
            out = append(out, x)
        }
        w.Header().Set("Content-Type", "application/json")
        json.NewEncoder(w).Encode(out)
    })

    log.Fatal(http.ListenAndServe(":8088", nil))
}

Because the engine is embedded, a container running this binary needs nothing but the binary and read access to the Parquet directory. It sits behind the same Cloudflare edge as everything else, and I cache the JSON responses aggressively since the underlying Parquet only changes once a day.

If you prefer Python for exploration, the ergonomics are even nicer — DuckDB returns results straight into a DataFrame and reads back out of one:

import duckdb

con = duckdb.connect()  # in-process, ephemeral

# Query Parquet, get an Arrow-backed DataFrame back.
df = con.execute("""
    SELECT date_trunc('day', ts) AS d,
           COUNT(*)             AS plays,
           COUNT(DISTINCT video_id) AS distinct_videos
    FROM read_parquet('/srv/dailywatch/warehouse/events/dt=*/*.parquet',
                      hive_partitioning = true)
    WHERE ts >= now() - INTERVAL 14 DAY
    GROUP BY d ORDER BY d
""").df()

# DuckDB can also query a DataFrame by name, no registration needed.
spikes = duckdb.query("""
    SELECT d, plays
    FROM df
    WHERE plays > (SELECT avg(plays) * 1.5 FROM df)
""").df()

print(spikes)

That last query treats the df variable as a table by name — DuckDB's replacement scan finds the Python object in scope. It makes the loop between "pull aggregate" and "drill into anomaly" almost frictionless.

Querying remote Parquet without downloading it

A thing that surprised me: DuckDB can read Parquet over HTTP and S3 directly, and thanks to Parquet's footer-and-row-group layout, it uses HTTP range requests to fetch only the byte ranges it needs. When my exports live in object storage, I don't download the file to query it.

INSTALL httpfs; LOAD httpfs;

-- Reads the footer, then only the row groups the predicate needs,
-- over HTTP range requests. No full download.
SELECT category_id, COUNT(*) AS plays
FROM read_parquet('https://cdn.example.com/warehouse/events/dt=2025-11-01/events.parquet')
WHERE device = 'mobile'
GROUP BY category_id
ORDER BY plays DESC
LIMIT 10;

For S3 you set credentials with SET s3_region, s3_access_key_id, etc., and use s3:// URLs. The mental model to keep: a WHERE on a well-partitioned, well-sorted column can turn a 2 GB remote file into a few megabytes of actual transfer. If your predicates don't prune, though, you'll pull the whole thing — which brings us to performance.

The performance knobs that actually moved the needle

My original 90-second query was a 90-day, multi-country aggregation over a single giant Parquet file with no partitioning and default settings. Here's what fixed it, roughly in order of impact:

• Partition by date. Splitting the monolith into dt=YYYY-MM-DD/ files meant a 90-day query touched 90 small files and skipped everything else. This alone was the biggest win — directory pruning happens before any file is opened.
• Sort within files on your common filter columns. If you frequently filter by country, writing the Parquet sorted by country tightens the per-row-group min/max stats, so predicate pushdown skips more groups. Unsorted data means every row group might contain every country, defeating the statistics.
• Right-size row groups. The 120K-row default is fine; the anti-pattern is thousands of tiny files or tiny row groups, where metadata overhead dominates. If you're producing many small files, periodically compact them.
• Set memory_limit deliberately. DuckDB spills to disk when it exceeds the limit, so a query never OOMs, but a too-low limit forces spilling on large sorts/joins. On a 16 GB box I give it 10 GB and leave headroom.
• Let it use all cores. DuckDB parallelizes scans and aggregations across threads automatically. On a shared host I sometimes cap SET threads to be a good neighbor, but for a dedicated analytics box, don't throttle it.
• Use EXPLAIN ANALYZE. It shows you exactly how many row groups were scanned versus skipped. If a predicate you expected to prune isn't pruning, this is where you catch it — usually the fix is sorting the export on that column.

After partitioning, sorting on country, and giving it real memory, that 90-second query runs in about 2.5 seconds. Same laptop, same data, no server added.

Where this fits, and where it doesn't

DuckDB did not replace SQLite for us, and it shouldn't for you. Production request-path lookups, full-text discovery through FTS5, single-row writes — SQLite remains the right tool, and it keeps serving live traffic on LiteSpeed without contention because the analytics engine reads a completely separate set of files. What DuckDB replaced was the reflex to either abuse the production database for analytical questions or stand up heavyweight infrastructure to answer them.

The workflow that stuck:

• Export clean, explicitly-typed, date-partitioned Parquet from SQLite nightly.
• Query those files in place with DuckDB — locally, from Go or Python, or straight over HTTP.
• Lean on window functions and QUALIFY for the ranking and lift questions that video metrics are full of.
• Partition and sort your exports so predicate pushdown does the heavy lifting, and confirm it with EXPLAIN ANALYZE.

The thing I'd tell my past self: you probably don't need a warehouse yet. An in-process engine reading columnar files answers a startling fraction of "can you pull the numbers on…" questions in seconds, costs nothing to run, and leaves no infrastructure behind when the question is answered. Start there, and only graduate to something bigger when you've actually outgrown it.