Apache DataFusion vs DuckDB: How to Choose

Apache DataFusion is an extensible query engine built in Rust around Apache Arrow. DuckDB is an embedded analytical database with its own storage and transaction system. Both execute SQL inside an application process and read common analytical file formats.

The practical choice depends on which responsibilities the application needs to own. DataFusion exposes components for building query systems. DuckDB packages more database behavior behind a connection API. Both can query files directly, and both can be extended.

For a notebook analyzing Parquet, DuckDB offers a direct starting point. For a service with custom storage and planning requirements, DataFusion offers explicit integration points. Neither description sets an absolute boundary: DataFusion has Python bindings and a CLI, and DuckDB has a substantial extension system.

What Does Each Project Include?

Apache DataFusion: a query engine framework

DataFusion parses SQL, builds logical plans, optimizes them, and executes physical operators. It passes data through the execution pipeline as Arrow record batches. Its project introduction describes built-in file readers, asynchronous object-store access, and customization interfaces.

Reading ordinary Parquet or CSV files does not require writing a connector. Custom table providers become useful when data lives behind a specialized API or storage system. Applications can also register in-memory Arrow data and query it directly.

DataFusion does not include a general-purpose durable database engine with its own transactional storage format. A surrounding system supplies persistence, catalog durability, access controls, and service operations as needed. Table providers can implement writes, so describing DataFusion as inherently read-only would also be inaccurate.

DuckDB: an embedded analytical database

DuckDB includes SQL planning, vectorized execution, native columnar storage, and ACID transactions for its database tables. Applications can open an in-memory database or persist tables in a database file. They can also query external files without importing those files first.

The distinction matters for deployment. A script that only reads Parquet does not need a persistent DuckDB database. An application that creates and updates local tables can use the database's storage and transaction machinery.

DuckDB offers clients for multiple languages, including Python, R, Java, and Rust. Its extension system adds capabilities such as formats, functions, and external storage integrations. Calling it a closed engine understates that architecture.

Architecture and Feature Comparison

Both projects use columnar, vectorized execution. The larger difference is the boundary between the engine and the application around it.

Embedding either engine removes a required network hop to a separate query server. It does not remove storage reads, object-store requests, memory allocation, or result conversion. Those costs can dominate query time.

The Same Parquet Query in Both Engines

Suppose orders.parquet contains region, status, and numeric amount columns. The application needs completed revenue by region. These Python examples use the same SQL aggregation and source file.

With the duckdb package installed:

import duckdb

with duckdb.connect() as connection:
    rows = connection.execute("""
        SELECT region, SUM(amount) AS revenue
        FROM read_parquet('orders.parquet')
        WHERE status = 'completed'
        GROUP BY region
        ORDER BY revenue DESC, region
    """).fetchall()
    print(rows)

With the datafusion package installed:

from datafusion import SessionContext

context = SessionContext()
context.register_parquet("orders", "orders.parquet")
batches = context.sql("""
    SELECT region, SUM(amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY region
    ORDER BY revenue DESC, region
""").collect()
for batch in batches:
    print(batch)

DataFusion's Python data-source guide documents file registration. Registration gives the source a table name; it does not require importing the entire file into memory. Execution starts when the application collects results.

The examples also expose an integration choice. DuckDB returns Python rows here, and DataFusion returns Arrow batches. DuckDB can export Arrow results too. For a benchmark, choose the same result representation and consume all results before stopping the timer.

In both cases, the application still decides where files live, which credentials read them, and who can submit SQL. A short query example demonstrates access, not a complete production service.

Extensibility and SQL Federation

Extending DataFusion

DataFusion's table-provider interface describes schemas and constructs execution plans. Providers report which filters they can evaluate at the source. Custom plan nodes and optimizer rules can add behavior beyond reading rows.

Consider a service that combines object storage with an operational database. A provider can push a selective filter into the database before returning Arrow batches. The surrounding system still needs connector credentials, connection limits, cancellation, and compatible type handling.

Implementing a trait is only the beginning of a connector. Test null behavior, timestamp precision, decimal conversion, and source errors. An incorrect pushdown can return wrong results even when the query runs quickly.

Extending DuckDB

DuckDB extensions can do more than add scalar functions. The PostgreSQL extension can attach a remote PostgreSQL database and query its tables. Applications can combine those tables with local files through SQL.

The evaluation question is whether existing extensions satisfy the required sources and query behavior. Check filter pushdown, supported types, transactions, and installation requirements for each extension. Do not infer these properties from the presence of an extension alone.

DataFusion usually fits teams that need direct control over planning and execution components. DuckDB usually reduces integration work when its database APIs and extensions already cover the task. A small prototype against the hardest source is more useful than counting connectors.

Persistence, Concurrency, and Deployment

DuckDB's native database file provides persistence without requiring a separate storage service. Within one writer process, multiple threads can execute transactions, subject to conflict handling. Multiple processes can open the native file read-only when no process writes it.

Sharing a writable database file between arbitrary application replicas is a different requirement. DuckDB's concurrency documentation distinguishes direct embedded access from coordinated deployment options, including DuckLake and the Quack protocol. Validate the selected mode and its maturity for the release being deployed.

DataFusion applications define their own concurrency and storage boundaries. Several engine instances can read an object store, but that fact does not create coordinated transactions. The catalog, table format, or storage implementation must define visibility and write coordination.

Neither a multithreaded engine nor a library dependency automatically creates a distributed query cluster. Scheduling work across machines also requires coordination, transport, retries, and resource management. DataFusion-based systems such as Apache Ballista add those responsibilities around the engine.

For an API service, budget resources across requests rather than per query alone. Ten simultaneous analytical queries can exhaust a process that easily completes each query individually. Apply admission limits, deadlines, and cancellation at the service boundary.

How to Compare Performance Fairly

There is no reliable universal winner for DataFusion versus DuckDB. Implementation language alone cannot predict performance. File organization, operator selection, data distribution, memory limits, and output handling all affect the result.

A useful evaluation separates at least three workloads: local file analysis, remote object-store queries, and queries against integrated sources. A result from one category does not establish performance in another.

Use the following process for a comparison:

1. Record engine versions, hardware, configuration, and enabled extensions.
2. Run identical queries against the same files and verify result equality.
3. Measure cold reads separately from repeated queries with warm caches.
4. Include planning, execution, and complete result consumption in the reported duration.
5. Repeat under realistic concurrency and data-refresh activity.
6. Record peak memory, temporary disk use, scanned bytes, and latency percentiles.

Inspect execution plans when results differ. A missing filter pushdown can outweigh improvements in vectorized execution. A large result converted into Python objects can obscure a faster engine. A selective query can favor a layout that performs poorly for full scans.

Keep storage comparisons explicit. Reading compressed Parquet in one engine and preloaded native tables in another measures different execution paths. Both are useful tests, but label the storage and preparation costs.

Benchmark maintenance effort too. A faster prototype that requires substantial connector development creates an ongoing engineering commitment. Record upgrade work, incident diagnosis, and deployment complexity alongside resource cost. Those factors often determine the practical choice after both engines meet the latency requirement.

Include the operating model

An embedded engine still needs an owner in production. Decide who investigates slow plans, validates extensions, and maintains engine upgrades. A team choosing DataFusion owns its custom integration code. A team choosing DuckDB still owns the application, deployment mode, and external dependencies around its database connections.

Record the reason for every customization. Revisit it during upgrades because a built-in capability can replace code the application previously maintained. Conversely, changing a connector or extension version can alter type conversion or query planning. Keep a small set of representative correctness queries alongside the application.

A Decision Framework

Start with the requirement that would be expensive to change later: storage ownership, integration depth, or deployment model. Then test the least certain part with representative data.

A finance analyst exploring monthly Parquet exports can begin with DuckDB and a notebook. A platform team exposing a proprietary storage format through SQL has a stronger reason to evaluate DataFusion. A Python application querying Arrow data can reasonably prototype both.

Keep the decision reversible where possible. Preserve source data in portable formats and separate application queries from engine-specific functions. Record intentional SQL differences instead of assuming PostgreSQL-like syntax guarantees identical semantics.

Migration tests need more than successful parsing. Compare decimal rounding, timestamp zones, null ordering, and functions that accept implicit casts. Explicitly define result ordering when an API depends on it. SQL results without an ORDER BY clause do not promise a stable row sequence.

Advanced Topics

Arrow integration and copying

Arrow defines an in-memory columnar representation that components can exchange without serializing every value. DataFusion uses Arrow internally, making it a natural fit for applications already managing Arrow batches.

That does not make every operation zero-copy. Filters, joins, casts, and output conversion can allocate new buffers. Sending Arrow over a network also incurs transport costs. Measure the complete data path, including ownership changes at language boundaries.

DuckDB's Arrow integration is useful for the same reason: applications can exchange columnar results instead of constructing individual language objects. The cheapest path depends on the actual types and client API.

Memory pressure and spilling

Analytical operators can retain substantial state. A high-cardinality aggregation needs more memory than a scan that streams rows to a consumer. Sorting a large result can require temporary storage even when the source file fits on disk.

DataFusion exposes memory-pool configuration to applications, and spill behavior depends on the operators and configuration. DuckDB documents larger-than-memory execution, including operators and aggregate states with limitations. Neither engine guarantees completion for every query under an arbitrarily small memory budget.

Test temporary disk capacity, cancellation during spills, and recovery after resource exhaustion. Set container limits with space for allocations outside tracked operator memory. A query benchmark should report memory failures rather than omitting them from the comparison.

External sources and consistent results

A local transaction does not automatically create a consistent snapshot across unrelated external databases. A join can observe one source before an update and another source after it. This issue belongs to the integration architecture, not just the SQL syntax.

For financial reconciliation or other sensitive joins, define the required visibility boundary. Options include querying a common snapshot, replicating to one store, or restricting the workload to sources with compatible guarantees. Test updates during execution, especially when comparing a federated query with a local copy.

Connector tests also need failure cases. Cancel a query during a remote scan and confirm that source work stops. Interrupt a connection during result streaming and verify error propagation. Resource cleanup and partial-result behavior matter when the same engine runs inside a long-lived service.

DataFusion and DuckDB with Spice

Spice SQL federation and acceleration uses DataFusion for query planning and execution. DuckDB is one available accelerator for storing selected datasets locally. The two engines therefore serve different responsibilities within the same runtime.

A dataset can select DuckDB through its acceleration configuration:

datasets:
  - from: file:./orders.parquet
    name: orders
    acceleration:
      enabled: true
      engine: duckdb
      mode: file

The DuckDB accelerator documentation explains storage paths and memory settings. Source access still depends on the configured data integration, and refresh behavior determines local data freshness.

For data lake acceleration, evaluate the available engines against query shape, update rate, persistence needs, and resource limits. Selecting an accelerator is a storage decision within Spice; it does not replace the runtime's DataFusion query layer.