Running Ad-Hoc Video Analytics with DuckDB on Parquet Exports

Last month I needed to answer what looked like a trivial question: across our eight regions, which channels had the steepest week-over-week drop in watch-through rate? On TrendVidStream we discover and rank trending streams from US, GB, DE, FR, IN, BR, AU and CA, and every region runs its own cron that writes view/engagement snapshots into per-site SQLite databases. The data was all there. The problem was that it was sharded across eight files on four hosts, and the join I wanted to run touched roughly 40 million rows. SQLite did it eventually, but "eventually" was 90 seconds per iteration, and analytics is an iterative game — you run a query, see something weird, refine, run again. By the third refinement I'd lost the thread.

The fix wasn't a data warehouse. We're a small operation with FTP-based deploys and no appetite for standing up Snowflake or a Postgres analytics replica. The fix was nightly Parquet exports plus DuckDB as a query engine I run from my laptop or a cron box. This post is the actual pipeline: how we export region snapshots to Parquet, how DuckDB queries them without an import step, and the handful of patterns that turned 90-second iterations into sub-second ones.

Why SQLite stops scaling for analytics

SQLite is the backbone of our serving path. Every region's PHP 8.4 app reads from a local SQLite file with FTS5 for search, and for transactional, single-row-ish lookups it is genuinely hard to beat. The trouble is that analytics is the opposite workload. An OLAP query scans huge column ranges and aggregates them; it almost never wants a single row by primary key.

SQLite stores data row-major. To compute AVG(watch_seconds) over 40M rows it has to walk every row and pull every column off disk even though you asked for one. There's no vectorized execution and no native columnar compression. You feel all three limits at once:

• Full-table scans read everything. Row storage means a one-column aggregate still pages in the whole row.
• No parallelism. A single analytical query runs on one core while your other 7 sit idle.
• Joins materialize a lot. Cross-region joins over wide tables blow past the page cache and start hitting disk.

DuckDB is the inverse: columnar, vectorized, multi-threaded, and built specifically to scan-and-aggregate. And critically, it reads Parquet files directly as if they were tables — no load step, no second copy of the data to keep in sync. That last property is what makes it fit a deploy model like ours where the source of truth is a pile of files, not a server.

Exporting region snapshots to Parquet

The first job is getting clean Parquet out of each region's SQLite. I do this in the same cron that already runs the fetch, right after the snapshot write. DuckDB can attach a SQLite file and copy straight out of it, which means the export script has zero dependencies beyond the DuckDB CLI.

Here's the export step. It attaches the live SQLite DB read-only, then writes a partitioned Parquet dataset keyed by region and snapshot date. Partitioning matters — it's what lets later queries skip files they don't need.

# export_snapshots.py — run per region after the fetch cron writes its snapshot
import duckdb
import sys
import os

REGION = sys.argv[1]              # e.g. "US"
SQLITE_PATH = sys.argv[2]         # e.g. /var/www/dwv/data/app.db
OUT_DIR = "/srv/analytics/parquet"

con = duckdb.connect()
con.execute("INSTALL sqlite; LOAD sqlite;")
# Read-only attach so we never block the serving path's writers
con.execute(f"ATTACH '{SQLITE_PATH}' AS live (TYPE sqlite, READ_ONLY);")

# Normalize once, here, so downstream queries don't carry per-region quirks.
con.execute(f"""
    COPY (
        SELECT
            video_id,
            channel_id,
            '{REGION}'                       AS region,
            CAST(snapshot_ts AS TIMESTAMP)   AS snapshot_ts,
            CAST(snapshot_ts AS DATE)        AS snapshot_date,
            view_count,
            like_count,
            comment_count,
            watch_seconds,
            duration_seconds
        FROM live.video_snapshots
        WHERE snapshot_ts >= now() - INTERVAL 2 DAY
    )
    TO '{OUT_DIR}'
    (FORMAT parquet,
     PARTITION_BY (region, snapshot_date),
     COMPRESSION zstd,
     OVERWRITE_OR_IGNORE true);
""")
con.close()
print(f"[{REGION}] exported snapshots to {OUT_DIR}")

A few decisions in there earn their keep:

• READ_ONLY attach. The serving cron is writing to this same SQLite file. Read-only means DuckDB takes a shared lock and never stalls the writer, which is non-negotiable on a live host.
• PARTITION_BY (region, snapshot_date). This writes a Hive-style directory tree (region=US/snapshot_date=2026-06-26/...). When you later filter on those columns, DuckDB prunes whole directories without opening the files.
• COMPRESSION zstd. Our snapshot columns are mostly integers with low cardinality channel IDs. ZSTD on this shape routinely gives 8-12x over the raw SQLite pages, which also makes the files cheap to ship over FTP.
• A 2-day window. Each run only re-exports recent partitions. Old days are immutable, so there's no reason to rewrite them. OVERWRITE_OR_IGNORE lets the run replace today's partition cleanly while leaving history alone.

The output is a single logical dataset that every region appends to. After all eight regions run, /srv/analytics/parquet/ holds the whole multi-region history as Parquet, and nothing had to be imported anywhere.

Querying Parquet directly, no import step

This is the part that changes how you work. DuckDB treats a glob of Parquet files as a table. You point read_parquet at the tree and query it like it was always there.

-- adhoc.sql — answer the question that started all this
-- Week-over-week watch-through rate drop, per channel, across all regions.
WITH daily AS (
    SELECT
        channel_id,
        region,
        snapshot_date,
        SUM(watch_seconds)                                   AS total_watch,
        SUM(view_count * duration_seconds)                   AS total_available,
        SUM(watch_seconds) * 1.0
            / NULLIF(SUM(view_count * duration_seconds), 0)  AS wtr
    FROM read_parquet('/srv/analytics/parquet/**/*.parquet', hive_partitioning = true)
    WHERE snapshot_date >= current_date - INTERVAL 14 DAY
    GROUP BY channel_id, region, snapshot_date
),
weekly AS (
    SELECT
        channel_id,
        region,
        CASE WHEN snapshot_date >= current_date - INTERVAL 7 DAY
             THEN 'this_week' ELSE 'last_week' END           AS wk,
        AVG(wtr)                                             AS avg_wtr
    FROM daily
    GROUP BY channel_id, region, wk
)
SELECT
    channel_id,
    region,
    MAX(avg_wtr) FILTER (WHERE wk = 'last_week')  AS last_week,
    MAX(avg_wtr) FILTER (WHERE wk = 'this_week')  AS this_week,
    MAX(avg_wtr) FILTER (WHERE wk = 'this_week')
      - MAX(avg_wtr) FILTER (WHERE wk = 'last_week') AS delta
FROM weekly
GROUP BY channel_id, region
HAVING last_week IS NOT NULL AND this_week IS NOT NULL
ORDER BY delta ASC
LIMIT 30;

The whole 40M-row version of this runs in under a second on a laptop. Two things make that happen, and they're worth understanding rather than treating as magic:

• hive_partitioning = true turns the region=/snapshot_date= directory names into real columns. The WHERE snapshot_date >= current_date - INTERVAL 14 DAY predicate then prunes at the directory level — DuckDB never opens 12 of the 14+ days of history, let alone reads them.
• Columnar projection. The query touches maybe six columns. Parquet stores each column contiguously, so DuckDB reads only those column chunks off disk and skips the rest. On a wide snapshot row that's the difference between reading 6 columns and reading 30.

Notice there is no CREATE TABLE, no INSERT, no COPY ... FROM. The Parquet files are the table. When tomorrow's export lands, the same query sees the new data automatically. For an iterative analytics session this is the entire point: you edit the SQL, re-run, and you're looking at fresh results in the time it takes to read the file footers.

The patterns that actually matter

Getting DuckDB to query Parquet is easy. Getting it fast and keeping it fast took learning a few things the hard way.

Push filters down to the partitions

The single biggest lever is making sure your WHERE clause hits partition columns (region, snapshot_date) so whole files get skipped. A query filtered on region = 'US' AND snapshot_date >= ... reads a fraction of the dataset. The same query filtered only on a non-partition column like channel_id has to open every file's row-group metadata to check min/max stats. Both work, but the first is an order of magnitude less I/O. When I design an export, I partition on whatever I expect to filter on most.

Watch your file count, not just your file size

Parquet has real per-file overhead — a footer, row-group metadata, the open/seek cost. Our early export wrote one file per region per hour, and after a few months we had tens of thousands of tiny files. Query planning time ballooned because DuckDB was reading thousands of footers before it could do any real work. Partitioning by day instead of hour, and letting each daily partition be one healthy file in the 50-500MB range, fixed it. The rule of thumb I settled on:

• Too many tiny files → planning overhead dominates, metadata reads thrash.
• A few enormous files → you lose parallelism and row-group pruning granularity.
• The sweet spot → files in the hundreds-of-MB range, partitioned on your common filter columns.

If you've already made the mess, DuckDB compacts it for you — read the glob and COPY it back out partitioned, and the small files collapse into clean ones.

Persist derived tables when you'll reuse them

Direct-Parquet querying is perfect for exploration. But once a query becomes a recurring report, re-scanning raw snapshots every time is wasteful. DuckDB has its own native storage format, and writing a rolled-up table into it is fast and gives you indexes and statistics. I keep a small .duckdb file with daily channel rollups that the dashboards hit, rebuilt nightly from the Parquet:

# rollup.py — build a small persistent DuckDB file from the Parquet lake
import duckdb

con = duckdb.connect("/srv/analytics/rollups.duckdb")
con.execute("PRAGMA threads=8;")

con.execute("""
    CREATE OR REPLACE TABLE channel_daily AS
    SELECT
        channel_id,
        region,
        snapshot_date,
        SUM(view_count)       AS views,
        SUM(watch_seconds)    AS watch_seconds,
        SUM(like_count)       AS likes,
        COUNT(DISTINCT video_id) AS active_videos
    FROM read_parquet('/srv/analytics/parquet/**/*.parquet', hive_partitioning = true)
    WHERE snapshot_date >= current_date - INTERVAL 90 DAY
    GROUP BY channel_id, region, snapshot_date;
""")

# A covering query over the rollup is now trivially cheap.
top = con.execute("""
    SELECT region, channel_id, SUM(views) AS views_7d
    FROM channel_daily
    WHERE snapshot_date >= current_date - INTERVAL 7 DAY
    GROUP BY region, channel_id
    ORDER BY views_7d DESC
    LIMIT 10;
""").fetchall()

for region, channel, views in top:
    print(f"{region}\t{channel}\t{views:,}")
con.close()

The split is: raw Parquet for ad-hoc exploration over full history, a small persisted DuckDB file for the handful of queries that run on a schedule. The rollup table is two orders of magnitude smaller than the raw snapshots, so the dashboard queries are instant and don't re-scan the lake.

Set threads deliberately

DuckDB defaults to using every core, which is great on a laptop and rude on a shared LiteSpeed host that's also serving traffic. On the production cron box I cap it with PRAGMA threads=4; so an analytics rebuild can't starve the PHP workers. On my laptop I let it use everything. It's a one-line knob that's easy to forget until a rebuild makes the site stutter.

Wiring it into a PHP stack

Our app is PHP 8.4, and most of the time PHP never touches DuckDB — the analytics layer is a separate Python/CLI path that produces the rollup file and a few cached JSON summaries. But when an admin page does need a fresh number, shelling out to the DuckDB CLI is clean and dependency-free. The CLI speaks JSON, so PHP just decodes it:

<?php
// AnalyticsQuery.php — call DuckDB from PHP without a native extension
declare(strict_types=1);

final class AnalyticsQuery
{
    public function __construct(
        private readonly string $duckdbBin = '/usr/local/bin/duckdb',
        private readonly string $dbPath = '/srv/analytics/rollups.duckdb',
    ) {}

    /** @return array<int, array<string, mixed>> */
    public function run(string $sql): array
    {
        $cmd = sprintf(
            '%s -json %s %s',
            escapeshellarg($this->duckdbBin),
            escapeshellarg($this->dbPath),
            escapeshellarg($sql),
        );

        $output = shell_exec($cmd);
        if ($output === null || $output === '') {
            throw new RuntimeException('DuckDB returned no output');
        }

        $rows = json_decode($output, true, flags: JSON_THROW_ON_ERROR);
        return is_array($rows) ? $rows : [];
    }
}

// Usage in an admin controller:
$analytics = new AnalyticsQuery();
$top = $analytics->run(
    "SELECT region, SUM(views) AS views_7d
     FROM channel_daily
     WHERE snapshot_date >= current_date - INTERVAL 7 DAY
     GROUP BY region ORDER BY views_7d DESC"
);

foreach ($top as $row) {
    printf("%s: %s views\n", $row['region'], number_format((int) $row['views_7d']));
}

In production I never pass raw user input into that SQL string — the admin queries are fixed templates, and anything parameterized goes through values I control, not request data. For a read-only analytics box querying a derived rollup the blast radius is small, but escaping the binary and DB path and keeping the SQL server-side is the baseline. If you want zero shell-out, there's a community PHP FFI binding to DuckDB's C API, but for an FTP-deployed app the CLI is far simpler to ship — it's one static binary you drop next to the rollup file.

What this replaced and what it didn't

The honest framing: DuckDB didn't replace SQLite, it replaced me running slow SQLite queries for analytics. The serving path is still SQLite with FTS5, still single-file, still trivially deployable over FTP. Nothing about the live site changed. What changed is that the analytical workload — the cross-region joins, the week-over-week deltas, the "why did India's watch time crater on Tuesday" investigations — moved to a columnar engine that was built for it.

The pieces that made it work for a small, file-oriented operation:

• Parquet as the interchange format — immutable, compressed, partitioned, and cheap to ship between hosts. The export is a side effect of the cron we already run.
• Direct querying with no import — the files are the table, so there's no second copy to keep in sync and no schema migration dance.
• Partition pruning and columnar projection — the reason a 40M-row query finishes before you've let go of the Enter key.
• A tiny persisted DuckDB rollup — for the recurring dashboard queries, so they don't re-scan history.

If you're sitting on per-service or per-region SQLite databases and your analytics questions have started taking longer than your patience, you almost certainly don't need a warehouse. Export to partitioned Parquet, point DuckDB at the glob, and keep your serving stack exactly as boring as it already is. The iteration speed you get back is the whole prize — analytics is only useful when you can ask the next question before you've forgotten why you asked the first one.