DuckDB for Medium-Scale Analytics: Replacing Spark for 100GB Data on a Single Machine

DuckDB for Medium-Scale Analytics: Replacing Spark for 100GB Data on a Single Machine

Setting up Spark takes 2 hours. It costs $50/hr on EMR. DuckDB queries the same 100GB Parquet files in 45 seconds from your laptop.

Your data pipeline groans under the weight of its own ceremony. You’re not doing petabyte-scale joins across continents; you’re trying to summarize a few hundred gigabytes of user events stored as Parquet. Yet the default answer for "big data" is still a distributed system designed for a rack of machines, not your dev box. Meanwhile, data cleaning accounts for 45% of total data science project time (Anaconda State of Data Science 2025). You can't afford to spend the other 55% waiting for a cluster to spin up.

This is the medium-scale analytics gap: too big for pandas to handle comfortably in memory, but too small to justify the operational tax of Spark. Enter DuckDB, an in-process analytical database. It’s a library you pip install, not a service you manage. It speaks SQL fluently and reads Parquet natively. It’s here to reclaim those 2 hours and $50.

DuckDB Architecture: Why Columnar Processing Is 50x Faster Than Row Stores

Traditional databases (PostgreSQL, SQLite) are row-stores. They write and read data horizontally: all columns for a single record sit together on disk. This is great for transactional workloads where you need to fetch or update a whole user row. It’s terrible for analytics, where you need to calculate the average of one column across 100 million rows.

DuckDB is a columnar-store. It stores data vertically. All values for column_a are packed together, then all values for column_b, and so on. When you run SELECT AVG(revenue) FROM events, DuckDB only has to read the revenue column block from disk. It can stream those values directly into a vectorized CPU operation, minimizing I/O and maximizing cache efficiency. This is the same architectural superpower that drives systems like Spark and Snowflake, but baked into a single, embeddable binary.

The performance difference isn't incremental; it's fundamental. Polars is 5–50x faster than pandas on common operations (GitHub stars +300% YoY) for the same reason: columnar, vectorized execution. DuckDB applies this principle not just to data in its native format, but directly to Parquet files, which are themselves columnar.

Querying Parquet Files Directly: No ETL, No Database Import Required

The most liberating feature of DuckDB is that it requires no ingestion step. You don't need to CREATE TABLE and then COPY FROM. Your Parquet files are the database. This obliterates the ETL bottleneck for exploration.

Let's say you have a directory of daily Parquet files from your data lake. Here's how you start analyzing, directly from Python in your Jupyter notebook (used by 80% of data scientists daily - JetBrains Developer Ecosystem 2025).

import duckdb


con = duckdb.connect()

# Query a Parquet file as if it were a table.
# DuckDB automatically infers the schema.
query = """
SELECT
    date_trunc('day', event_timestamp) as day,
    country,
    COUNT(*) as event_count,
    SUM(revenue_usd) as daily_revenue
FROM 's3://my-bucket/events/*.parquet'
WHERE event_timestamp >= '2025-01-01'
GROUP BY 1, 2
ORDER BY daily_revenue DESC
LIMIT 10;
"""

# Execute and fetch the result directly into a pandas DataFrame.
top_days_df = con.execute(query).fetchdf()
print(top_days_df.head())

There is no intermediate step. The query planner scans the Parquet files, pushes down predicates (like the WHERE clause on event_timestamp), and performs the aggregation in a single pass. If your data is partitioned by date in the path (e.g., year=2025/month=01/), DuckDB can leverage that for partition pruning, making it even faster.

SQL Features in DuckDB: Window Functions, PIVOT, and List Aggregations

DuckDB isn't a SQL toy; it's a full implementation. Need to calculate a 7-day rolling average? Use a window function. Need to pivot? Use the PIVOT statement. It handles the complex SQL that often forces you back into Pandas.

-- Calculate session metrics with window functions
WITH user_sessions AS (
  SELECT
    user_id,
    session_id,
    SUM(revenue_usd) as session_revenue,
    LAG(session_revenue) OVER (PARTITION BY user_id ORDER BY session_start) as prev_session_revenue
  FROM 'user_sessions.parquet'
  GROUP BY user_id, session_id
)
SELECT
  user_id,
  AVG(session_revenue) as avg_revenue,
  CORR(session_revenue, prev_session_revenue) as revenue_autocorrelation
FROM user_sessions
GROUP BY user_id;

It also supports advanced aggregations like LIST and STRING_AGG, which are perfect for creating features for machine learning or debugging data groups.

Real Error & Fix: You might try to pivot in pandas and hit a MemoryError loading 20GB CSV. The pandas approach would be to read_csv or read_parquet and then use pd.pivot_table(), which materializes the entire DataFrame in memory.

• Fix: Do the pivot in DuckDB, where the operation is performed in a streaming, columnar fashion. Use duckdb.sql("PIVOT data ON category USING SUM(value)") and fetchdf() only the much smaller result.

Integrating with Polars and pandas: Zero-Copy DataFrames

You live in Python. DuckDB doesn't force you out. Its Python API is designed for zero-copy interchange with the major DataFrame libraries. You can query a Parquet file and get a Polars DataFrame without serialization overhead. You can also register an existing DataFrame as a temporary table and query it with SQL.

import polars as pl
import duckdb

# Read a large dataset with Polars (lazy, so it doesn't load yet).
lazy_df = pl.scan_parquet("large_dataset/*.parquet")

# Register the Polars LazyFrame directly with DuckDB.
# No data is copied at this point.
duckdb.register("lazy_frame_view", lazy_df)

# Now use DuckDB's SQL engine on the Polars data.
complex_result = duckdb.sql("""
    SELECT category,
           AVG(value) as avg_val,
           STDDEV(value) as std_val
    FROM lazy_frame_view
    WHERE value IS NOT NULL
    GROUP BY category
    HAVING std_val > 0
""").pl()  # .pl() fetches the result as a Polars DataFrame

# Continue working in Polars
print(complex_result.filter(pl.col("avg_val") > 100))

This is a best-of-both-worlds workflow. Use Polars for its elegant, expressive API for complex transformations, and use DuckDB's SQL when a set-based operation is clearer or when you need a specific SQL function. The same works with pandas DataFrames using .fetchdf() and .register().

Real Error & Fix: When you do manipulate pandas DataFrames, you might see: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.

• Fix: This is a pandas classic. Be explicit. Use .loc[row_indexer, col] = value instead of chained indexing df['a']['b'] = value. If you need a copy, call .copy() explicitly. Better yet, for large transformations, push the logic down into a DuckDB query where this ambiguity doesn't exist.

Querying S3 and GCS Parquet Directly with DuckDB httpfs Extension

Your data isn't on your laptop; it's in S3 or Google Cloud Storage. DuckDB's httpfs extension lets you treat cloud storage like a local filesystem. Install it once, configure your credentials, and query directly.

# Install and load the extensions
con.execute("INSTALL httpfs;")
con.execute("LOAD httpfs;")
# Configure AWS credentials (or set environment variables)
con.execute("SET s3_region='us-east-1';")
con.execute("SET s3_access_key_id='YOUR_KEY';")
con.execute("SET s3_secret_access_key='YOUR_SECRET';")

# Now query directly from S3
cloud_query = """
SELECT *
FROM 's3://my-data-lake/transactions/year=2025/month=*/day=*/part-*.parquet'
WHERE amount > 1000
LIMIT 100;
"""
suspicious_tx_df = con.execute(cloud_query).fetchdf()

This changes the game. You can run exploratory queries against terabytes in your data lake from your CI/CD runner or a lightweight EC2 instance, without moving a byte.

DuckDB vs Spark at 10GB, 100GB, 1TB: When the Crossover Happens

So when does Spark finally win? It's not about raw data size alone; it's about working set size and cluster memory. The crossover happens when your data or intermediate results no longer fit in the aggregate memory of a single, reasonably sized machine.

Here’s a practical benchmark based on typical operations:

The Rule of Thumb: If your data and the core working set of your query can fit in the available RAM of a single large machine (say, 64GB-512GB), DuckDB will almost certainly be simpler and faster. The moment you need multiple passes over data that exceeds memory, or complex shuffles that benefit from many CPUs, Spark's distributed architecture becomes necessary. For the vast "medium-scale" range of 10GB to 500GB, DuckDB is the undisputed champion of velocity.

Persistent DuckDB: Saving Results and Incremental Processing

DuckDB isn't just for ephemeral queries. You can create a persistent .duckdb file to store intermediate results, create pre-aggregated summaries, or build a local feature store.

# Connect to a persistent database file
persistent_con = duckdb.connect('my_analytics.duckdb')

# Create a table from the result of a Parquet query
persistent_con.execute("""
    CREATE OR REPLACE TABLE daily_aggregates AS
    SELECT date, category, SUM(sales) as total_sales
    FROM 'sales_data/*.parquet'
    GROUP BY date, category;
""")

# Now query the materialized table instantly
fast_query = """
SELECT category, AVG(total_sales) as avg_daily_sales
FROM daily_aggregates
GROUP BY category
ORDER BY avg_daily_sales DESC;
"""
print(persistent_con.execute(fast_query).fetchdf())

# Incremental update: append new day's data
persistent_con.execute("""
    INSERT INTO daily_aggregates
    SELECT date, category, SUM(sales) as total_sales
    FROM 'sales_data/new_day.parquet'
    GROUP BY date, category;
""")

This pattern is incredibly powerful. Use it to cache the expensive parts of your pipeline. Great Expectations prevents 73% of silent data quality failures in production pipelines (user survey 2025). You can run Great Expectations on the data before you insert it into your persistent DuckDB table, ensuring only valid data is materialized.

Next Steps: Building Your First DuckDB Pipeline

Stop planning for scale you don't need yet. Start by replacing one Spark job or one painful pandas read_parquet/merge/groupby cycle.

1. Profile Your Bottleneck: Find a query on 10-100GB of Parquet that feels slower than it should be.
2. DuckDB-ify It: Write the equivalent SQL. Use duckdb.connect() and .fetchdf() or .pl() to drop it into your existing notebook or script.
3. Benchmark: Time it. Compare it to your old method. The difference will be obvious.
4. Iterate: Start using DuckDB for joins and initial aggregations, then pull the smaller result into Polars or pandas for visualization (seaborn, plotly) or stats (scipy.stats, pingouin).
5. Productionize: For recurring jobs, write a Python script that uses a persistent .duckdb file as a cache. Use pandera for lightweight schema validation (with its negligible 0.3s overhead on 1M rows) on ingestion, or Great Expectations for full contracts.

The tooling is mature. The performance is proven. The time you save not babysitting clusters is time you can spend on the actual analysis, where pandas 2.0 with PyArrow backend uses 60–80% less memory than pandas 1.x for string columns, making the final-mile visualization and modeling smoother than ever.

Your laptop is more powerful than you think. Let DuckDB prove it.