Is Your Data Really Big?

Fast depends on the size of the problem

It’s a common notion that when dealing with huge amounts of data, you should use big data technologies like Spark or Hadoop. That’s right — it can take hours for a single machine to operate on hundreds of GBs or TBs of data, while Spark can reduce that time substantially by distributing the workload.

It is fast — but people have different definitions of fast. For example:

Aggregating over 10 billion rows:

Single machine: 2 hours

Distributed system: 10 minutes (Fast!)

Aggregating over 1 million rows:

Single machine: 0.2 seconds

Distributed system: 3 seconds (Slow?)

The reason is simple. For 10 billion rows, the time saved by parallelizing the computation far outweighs the overhead of distributing it. For 1 million rows, the computation itself is cheap, so job scheduling, task coordination, serialization, and communication become the overhead.

This can be a form of premature optimization: “Let’s design the application to support huge amounts of data — let’s use Spark!”

The solution: have different paths for different scales. But then, how do we handle the awkward data size — data that isn’t big enough to justify this overhead, but isn’t small enough to fit comfortably in memory?

How to handle awkward data?

We decided to try a library that was rapidly gaining popularity for its ability to get much more out of a single machine. The idea was appealing: keep computation local, like Pandas, but use a query engine built for analytical workloads — processing data in batches, using multiple cores, and not requiring the entire dataset to fit in memory.

DuckDB is an in-process analytical database well suited for analytical workloads on a single machine. It gives you the convenience of local computation, like Pandas, but with a vectorized SQL engine that can efficiently use multiple cores and work with data larger than memory. For many analytical workloads, it fills the gap between “just use Pandas” and “bring in Spark.”

For us, one of DuckDB’s biggest advantages was its support for popular data sources — from AWS and Snowflake to Oracle — along with direct access to file formats like Parquet and CSV.

What we learned running DuckDB on Snowflake

Our data was in Snowflake, so the first question was simple: how well does DuckDB work with Snowflake?

DuckDB has a Snowflake extension that lets you attach a Snowflake database and query its tables directly.

ATTACH '' AS snow_db ( TYPE snowflake, SECRET snowflake_cred, READ_ONLY );

SELECT * FROM snow_db.schema.customers WHERE customer_id = 123;

Pretty neat. But this is also where we learned a few things.

Pushdown matters

When querying Snowflake through DuckDB, there are two places where the work can happen:

Snowflake → data → DuckDB → filter

or

Snowflake → filter → data → DuckDB

The second approach is generally preferable when pushdown is possible.

This is called query pushdown — DuckDB pushes parts of the query down to Snowflake instead of bringing all the data locally.

Our first approach using snowflake_query() didn't work well for us. We weren't getting the pushdown we needed, queries were slow, and we also ran into memory issues with unbounded VARCHAR columns.

Using an attached Snowflake database with pushdown worked much better.

The first query isn't just a query

Once things were working, we started measuring.

For one of our flows, we were reading from seven Snowflake tables. It took around 13 seconds. That was surprising, so we broke the time down:

Fetch / setup ~3.0s Create views ~2.8s Run queries ~6.9s ----------------------- Total ~13s

One thing stood out: the first Snowflake access was considerably slower. Later accesses were much faster.

There is a fair amount of work hidden behind that first query — establishing the Snowflake connection, fetching metadata and schema information, initializing the integration, and populating caches that subsequent queries can reuse.

Initially, our abstraction made this easy to miss because every operation looked like an independent query.

We changed that by keeping the DuckDB/Snowflake connection around for longer instead of recreating it for every operation. That allowed multiple queries in the same flow to reuse the setup work from the first access.

If you keep connections alive, you also need some lifecycle management around them. In our case, connections weren't meant to live forever: they could expire after a TTL and be cleaned up when they were no longer useful. The important part was that the lifetime matched the workload rather than the lifetime of a single query.

This also changed how we benchmarked things.

A cold query includes connection and initialization costs. A warm query benefits from work that has already happened. Those are two different numbers, and both are useful. So when benchmarking DuckDB + Snowflake, measure cold and warm query performance separately. Otherwise, you might spend time optimizing SQL when a meaningful part of the number you're looking at is actually connection setup.

Our abstraction looked roughly like this:

Snowflake table ↓ DuckDB relation ↓ DuckDB view ↓ query

Creating those views was costing us around 0.3–0.5s per table. With enough tables, that starts adding up.

We tried different ways of registering them:

relation.create_view(...) duckdb.register(...) CREATE VIEW ...

The timings weren't dramatically different. Eventually, the more useful question became:

Do we need to create the view at all?

DuckDB has a useful feature called replacement scans. If a Pandas DataFrame exists as a Python variable, DuckDB can discover it by name and query it directly.

So instead of:

duckdb.register("view_1", df_1) duckdb.register("view_2", df_2)

duckdb.sql(""" SELECT * FROM view_1 JOIN view_2 USING (id) """)

we could simply do:

view_1 = df_1 view_2 = df_2

duckdb.sql(""" SELECT * FROM view_1 JOIN view_2 USING (id) """)

No register(). No CREATE VIEW. No extra catalog object just to give something a SQL-visible name.

This ended up being a recurring theme in the optimization work: before trying to make an abstraction cheaper, check whether you still need the abstraction.

Watch your types

This one was unexpected. For one query, we observed:

WHERE APPLICATION_ID = 1 → ~0.2s WHERE APPLICATION_ID = 1.0 → ~1s WHERE APPLICATION_ID = '1' → ~0.2s

Same value. Very different query time.

Once another database is involved, small things like types and casts can affect what gets pushed down and how the remote query behaves.

So if a simple predicate is mysteriously slow, check the generated query and the types before looking for a bigger hammer.