DuckDB for data engineers: practical tips and trade-offs

DuckDB for data engineers: practical tips and trade-offs

You're building a data pipeline and spinning up a Spark cluster feels like overkill for a 50 GB CSV or a handful of Parquet files. DuckDB for data engineers offers a refreshing alternative: an in-process SQL engine that runs on your laptop and executes analytical queries with impressive speed. We've found it invaluable for local development, one-off transformations, and replacing small Spark jobs that don't justify cluster overhead.

In-process SQL engine

DuckDB runs entirely within your application process—no daemon, no network calls, no cluster coordination. This architecture makes it fundamentally different from Spark or traditional databases. When you query data, the engine executes in the same memory space as your code, eliminating serialization and network latency.

The practical advantage is speed and simplicity. We spin up a DuckDB connection and run queries in milliseconds, even on moderately sized datasets. For a typical 2 GB Parquet file, a filtering and aggregation query completes in under 100 milliseconds on a modern laptop. Compare that to Spark, where job startup time alone can exceed several seconds.

Here's a minimal example:

import duckdb

# Create an in-process connection
conn = duckdb.connect(':memory:')

# Or persist to disk
conn = duckdb.connect('analytics.duckdb')

# Run a query directly
result = conn.execute('SELECT COUNT(*) FROM read_parquet("sales.parquet")').fetchall()
print(result)

The connection object is your entry point. You pass SQL directly and get results back as Python tuples, DataFrames, or Apache Arrow tables. No configuration, no YAML, no cluster readiness checks. For teams working on embedded analytics, reporting systems, or data validation pipelines, this simplicity is a game-changer.

Reads Parquet and CSV directly

DuckDB's killer feature for data engineers is native, zero-copy reads of Parquet and CSV files. You don't stage data into a table first; you query the file itself. The engine understands columnar formats and pushes down predicates, so it only touches the columns and partitions you actually need.

Parquet support is especially strong because of how well the columnar format aligns with DuckDB's execution model. When you filter on a date column or aggregate by category, DuckDB skips irrelevant data entirely. For a 10 GB Parquet file with 500 columns, querying just two columns runs as if you'd pre-selected them.

CSV handling is flexible. DuckDB auto-detects schemas, supports compression (gzip, bzip2, zstd), and handles edge cases like quoted delimiters and escaped newlines. You can query a CSV without writing a single line of schema definition:

import duckdb

# Query CSV directly—DuckDB infers the schema
query = '''
SELECT 
  customer_id,
  SUM(amount) as total_spent,
  COUNT(*) as purchase_count
FROM read_csv("transactions.csv")
WHERE purchase_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 5
'''

result = duckdb.execute(query).fetch_df()
print(result)

You can also query multiple files or directories with glob patterns. For instance, querying a year's worth of monthly Parquet files:

query = '''
SELECT year, month, region, SUM(revenue) as total
FROM read_parquet('data/sales/2024/*.parquet')
GROUP BY year, month, region
'''

DuckDB also supports Iceberg, Delta Lake, and S3 paths if you configure the right extensions. This flexibility means you can prototype transforms locally and switch to cloud storage without rewriting queries.

Great for local development

Local development with Spark usually means PySpark on a pseudo-distributed setup or mocking with small test CSVs. DuckDB flips this: you can test against realistic data sizes and formats without infrastructure. Copy a production Parquet export, run your full analytical query locally, and iterate in seconds.

We've used this pattern extensively when designing ETL logic. A team member builds a complex aggregation locally with DuckDB, validates the output, then translates it to Spark for production. The round-trip is fast enough that you can refine your SQL and test edge cases before touching the cluster.

DuckDB also ships with a REPL, letting you explore data interactively:

$ duckdb
D DESCRIBE read_parquet('orders.parquet');
# Shows schema instantly

D SELECT COUNT(*) FROM read_parquet('orders.parquet');
# Quick sanity check

For unit tests, DuckDB is ideal. You create an in-memory database, load fixture data, run your transformation query, and assert on the results—all without Docker or external services. Tests run in parallel because each test gets its own in-process connection. We've seen entire suites of data validation tests run in under a second.

Often replaces small Spark jobs

Many production Spark jobs don't genuinely need a cluster. They read a single input file, apply filtering and aggregation, and write output. If the input fits in memory (or is accessed columnar-efficiently), Spark's overhead costs more than the actual computation.

DuckDB is a pragmatic replacement for these jobs. A typical migration looks like this:

Original PySpark job:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("daily_metrics").getOrCreate()

df = spark.read.parquet("events.parquet")
result = df \
  .filter(df.event_date >= "2024-01-01") \
  .groupBy("user_segment") \
  .agg(F.count("*").alias("event_count"), F.avg("duration").alias("avg_duration")) \
  .write.parquet("output/daily_metrics.parquet")

DuckDB equivalent:

import duckdb

conn = duckdb.connect()

conn.execute('''
CREATE TABLE daily_metrics AS
SELECT 
  user_segment,
  COUNT(*) as event_count,
  AVG(duration) as avg_duration
FROM read_parquet("events.parquet")
WHERE event_date >= '2024-01-01'
GROUP BY user_segment
''')

conn.execute('COPY daily_metrics TO "output/daily_metrics.parquet" (FORMAT PARQUET)')

The DuckDB version is shorter, starts instantly (no JVM or Spark context overhead), and executes faster on single-node data. We've replaced dozens of small Spark jobs this way, cutting infrastructure costs and simplifying deployment pipelines.

DuckDB also handles Python integration gracefully. You can register custom functions, read from Pandas DataFrames, and export results directly to NumPy arrays or PyArrow tables. This makes it natural for Python-heavy teams.

Common mistakes

Assuming it scales to arbitrarily large datasets: DuckDB is single-process. If your data is 500 GB and your machine has 64 GB of RAM, you need external memory (spill to disk), which works but is slower than Spark. For true big data, Spark remains the right choice.

Forgetting to partition mental models: DuckDB's query planning is excellent but simpler than Spark's. A query that works on 10 GB might perform poorly on 100 GB. Always test with realistic data sizes and monitor query plans using EXPLAIN.

Not leveraging pushdown predicates: DuckDB can only skip data if your filter conditions allow it. If you read an entire Parquet file and then filter in Python, you've negated the benefit. Always push filters into the SQL query itself.

Overlooking transaction semantics: DuckDB supports ACID transactions, but in-memory mode doesn't persist across connections. For production use, enable durability explicitly and understand the trade-offs.

Ignoring extension dependencies: Some features (S3, Iceberg) require extensions. Ensure your deployment environment has network access to download them, or bundle them explicitly.

Key takeaways

• DuckDB's in-process design eliminates cluster overhead; queries run in milliseconds on laptops.
• Direct Parquet and CSV reads with predicate pushdown mean you query files without staging or schema registration.
• Local development becomes fast and realistic; test against production data formats without infrastructure.
• Small Spark jobs (single input, filtering, aggregation) often execute faster and cheaper on DuckDB.
• Use DuckDB for one-off analyses, ETL prototyping, unit tests, and embedded analytics.
• Know the limits: single-process architecture maxes out around 100-200 GB with spill-to-disk; beyond that, Spark or distributed systems are necessary.
• Always test query performance with realistic data and monitor EXPLAIN plans to catch efficiency issues early.