DuckDB vs BigQuery vs pandas for SEO Data Pipelines: A Benchmarked Engine Showdown

If you have ever waited four minutes for a pandas.read_csv() to chew through a 2 GB Search Console export, only to watch the kernel die on a groupby, you already know the quiet tax of SEO data work at scale. The datasets we deal with — bulk GSC exports, server logs, crawl outputs, backlink dumps — outgrew the laptop years ago, but the tooling advice never caught up. The default answer is still “just put it in BigQuery,” and the second-most-common answer is still “just use pandas.” Both are right sometimes and wrong often.

This post is a head-to-head teardown of the three engines most SEO pipelines actually run on in 2026 — DuckDB, BigQuery, and pandas — benchmarked on the same realistic workload, with the decision rules I now use when wiring up a new pipeline. The goal is not to crown a winner. It is to tell you which engine wins for a given shape of problem, so you stop paying for a warehouse you do not need or fighting a dataframe that will never fit in RAM.

The three engines, and why this comparison is awkward on purpose

These tools are not drop-in replacements, and pretending they are is how teams end up with the wrong architecture. They sit at different points on two axes: where the compute runs, and how much data they can credibly handle.

pandas is an in-memory dataframe library. Everything lives in your process’s RAM, single-threaded for most operations, and the practical ceiling is roughly a few hundred megabytes to a couple of gigabytes before you start swapping or crashing. It is unbeatable for interactive exploration and small-to-medium transforms.

DuckDB is an in-process analytical (OLAP) database — think “SQLite for analytics.” It runs inside your Python process with zero server, but it is columnar, vectorized, multi-threaded, and can stream data larger than RAM directly off Parquet or CSV files on disk. You get warehouse-style SQL on your laptop with no infrastructure.

BigQuery is Google’s serverless cloud warehouse. Compute is elastic and effectively unbounded, it is where the GSC Bulk Data Export lands natively, and it shines when many people or many scheduled jobs query the same shared, ever-growing tables. The trade-offs are network latency on every query, a billing model you have to actively manage, and the operational weight of a cloud dependency.

The awkwardness is the point: the right comparison is not “which is fastest” but “which is fastest at the scale and access pattern you actually have.”

The benchmark setup

To keep this honest I ran the same three queries against the same data on each engine. The dataset is a synthetic-but-realistic GSC performance export: 25 million rows (query, page, country, device, date, clicks, impressions, position) stored as a 1.9 GB Parquet file — roughly what a mid-sized site accumulates over 16 months of daily bulk export.

Hardware for the local engines: an M-class laptop, 16 GB RAM, 8 cores. BigQuery ran on the same Parquet loaded into a standard table, queried over a home connection. The three workloads:

Q1 — Aggregate scan: total clicks and impressions grouped by page, top 1,000 by clicks. Q2 — Filtered window: per-query weekly average position, filtered to one country and one device. Q3 — Cannibalization join: self-join to find query/page pairs where two or more URLs rank for the same query — the kind of join that melts pandas.

Numbers below are wall-clock medians over five runs, including cold-start where relevant. Treat them as directional, not as a lab benchmark — your network and machine will shift them, but the ratios hold up remarkably well across the setups I have tested.

Results

pandas: Q1 finished in 38 s after a 41 s load (and peaked at 11 GB of RAM — dangerously close to the edge). Q2 ran in 22 s once loaded. Q3, the self-join, never completed: it exhausted memory and the kernel was killed at around 14 GB. This is the canonical failure mode — pandas is brilliant until the working set crosses your RAM, and joins blow the working set up fast.

DuckDB: Q1 in 1.9 s, Q2 in 1.1 s, Q3 in 6.4 s — querying the Parquet file directly, never loading it fully into memory, peaking under 3 GB. No server, no setup, one pip install. For single-machine analytical work on files, this was not close.

BigQuery: once the data was in a native table, Q1 returned in 2.8 s, Q2 in 2.1 s, Q3 in 3.3 s of slot time — but each query carried 1–2 s of round-trip and API overhead, so interactive iteration felt slower than DuckDB despite comparable engine speed. Where BigQuery pulled ahead was Q3 at larger scale and the fact that ten concurrent scheduled jobs did not slow each other down.

The headline: for one engineer iterating on files that fit on disk, DuckDB was 10–20x faster than pandas and felt snappier than BigQuery. For shared, always-on, multi-tenant workloads, BigQuery’s concurrency and elasticity justified its overhead.

The DuckDB pattern that replaces most pandas SEO scripts

The single most useful thing DuckDB does for SEO work is query files in place. You do not import the GSC export into a database; you point SQL at the Parquet (or CSV, or even a folder of them with a glob) and let the engine stream it.

import duckdb

con = duckdb.connect()

# Query a 1.9 GB Parquet export directly — no full load into RAM
cannibalization = con.execute("""
    SELECT query,
           COUNT(DISTINCT page) AS competing_urls,
           SUM(clicks)          AS total_clicks,
           ARRAY_AGG(DISTINCT page) AS urls
    FROM read_parquet('gsc_export/*.parquet')
    WHERE country = 'usa' AND device = 'DESKTOP'
    GROUP BY query
    HAVING COUNT(DISTINCT page) >= 2
    ORDER BY total_clicks DESC
    LIMIT 500
""").df()   # hand the result back to pandas as a small dataframe

Notice the last line: .df() returns a normal pandas DataFrame. This is the pattern I now reach for by default — do the heavy filtering, joining, and aggregation in DuckDB, then hand a small, already-reduced result to pandas for the last-mile formatting, plotting, or export. You keep pandas’ ergonomics for the 5,000-row result while never asking it to hold 25 million rows. The same approach turns the keyword-clustering and content-decay pipelines we have covered before into something that runs in seconds instead of minutes; if you process large exports for keyword clustering, swapping the load step for a DuckDB pre-aggregation is often the single biggest speedup available.

DuckDB also reads directly from cloud storage (S3, GCS) and can query Parquet sitting in a bucket without a warehouse, which is what makes it a credible middle layer between raw exports and a full BigQuery deployment.

When BigQuery is still the right call

It would be easy to read the benchmark and conclude “always DuckDB.” That is wrong for several real situations, and ignoring them creates the opposite problem — a single-laptop bottleneck that does not scale with the team.

Reach for BigQuery when the data lives there already — the GSC Bulk Data Export writes straight into BigQuery, so for a daily GSC pipeline you are saving an extract step by querying in place. Reach for it when multiple people or many scheduled jobs hit the same tables concurrently; DuckDB is single-process, and while you can run it on a server, you do not get free concurrency or a managed query layer. Reach for it when datasets genuinely exceed a single machine’s disk — hundreds of gigabytes to terabytes of logs across years. And reach for it when you want BI tools, scheduled queries, and access controls managed for you rather than rolled yourself.

The cost dimension cuts both ways. BigQuery’s on-demand pricing bills by bytes scanned, so an unpartitioned table queried interactively a hundred times a day gets expensive fast — partition by date and cluster by your common filters, or you will pay for full scans you did not need. DuckDB’s “cost” is the engineering time to operate it and the ceiling of one machine. Neither is free; they just bill you in different currencies.

A decision framework you can apply today

Strip away the benchmarks and the choice collapses to a few questions. If your data fits comfortably in RAM and you are exploring interactively, pandas is fine — do not over-engineer a 50 MB problem. If your data fits on disk but not in RAM, or you want SQL-speed transforms with zero infrastructure, DuckDB is almost always the answer, and it is the one most SEO teams are sleeping on. If the data is shared across people and jobs, already lives in the cloud, or exceeds a single machine, BigQuery earns its overhead.

In practice the strongest pipelines I build are hybrids. DuckDB does local development and the heavy per-file reduction; BigQuery is the shared system of record for the dashboards and the cross-team queries; pandas handles the final small-result polish. The same logic applies to log work — the crawl-budget log pipeline we built recently is a natural DuckDB workload locally, with aggregates pushed to BigQuery only when the whole team needs them. Pick the engine per stage, not per project.

Key takeaways

For single-engineer analytical work on files that fit on disk, DuckDB was 10–20x faster than pandas and felt more responsive than BigQuery while requiring zero infrastructure. pandas remains excellent for small, interactive, last-mile work but fails predictably the moment a join pushes the working set past RAM. BigQuery is not “the big version of DuckDB” — it is a different tool that wins on concurrency, shared access, and scale beyond one machine, and it is the natural home for the GSC Bulk Export. The most resilient setup is not picking one but routing each stage of the pipeline to the engine that fits it.

If this kind of working-code-first breakdown is useful, bookmark SEO Automation Club — we publish hands-on automation playbooks like this every week, and the data-and-analytics deep dives build on each other. A good next read is our walkthrough on building a self-updating SEO dashboard on the GSC Bulk Export, which picks up exactly where this benchmark leaves off.

Frequently asked questions

Is DuckDB a replacement for BigQuery?

No. DuckDB is an in-process, single-machine analytical engine, while BigQuery is a serverless, elastically scaling, multi-user cloud warehouse. DuckDB replaces BigQuery for local development, single-engineer analysis, and querying files on disk or in a bucket. It does not replace BigQuery’s concurrency, managed access controls, or ability to scale past one machine, which is why hybrid setups are common.

Can DuckDB really query a file larger than my RAM?

Yes, within limits. Because DuckDB is columnar and streams data off disk rather than loading it all into memory, it can process Parquet and CSV files several times larger than available RAM, spilling intermediate results to disk when needed. Very large joins or sorts can still pressure memory, but ordinary filter-and-aggregate workloads on files that fit on your disk run comfortably.

Why does pandas crash on large SEO exports but DuckDB does not?

pandas loads the entire dataset into RAM and most operations are single-threaded, so a multi-gigabyte export plus the temporary memory a join or groupby allocates can exceed your machine’s RAM and kill the process. DuckDB is vectorized, multi-threaded, and streams from disk, so it keeps peak memory far lower for the same query.

How do I control BigQuery costs for SEO data?

BigQuery on-demand pricing charges by bytes scanned, so the biggest levers are partitioning tables by date and clustering by your most common filter columns (such as country or device), selecting only the columns you need instead of SELECT *, and using scheduled or materialized aggregates so dashboards query small summary tables rather than rescanning the raw export on every load.