DuckDB is genuinely excellent at what it does, and the hype around it is mostly deserved. But "run this in DuckDB" has become a reflexive recommendation in data engineering circles for problems that it doesn't always fit, and the honest tradeoffs deserve examination.
This post is specifically about local analytics workflows — using DuckDB on a development machine or in a CI environment to query data files, develop transforms, and validate results. Not about deploying DuckDB as a production OLAP service (MotherDuck handles that). The local use case is where DuckDB genuinely changes the workflow, and also where the limits become most apparent.
What Makes DuckDB Fast Locally
DuckDB's architecture is an in-process columnar OLAP database. It runs inside your Python process (or R, or CLI), reads columnar data formats directly, and uses vectorized query execution across all available CPU cores. There's no network hop, no client-server protocol overhead, no server startup time.
The key to its performance on local workflows is Parquet zero-copy reads. When you query a Parquet file with DuckDB:
SELECT
date_trunc('month', event_time) AS month,
event_type,
count(*) AS event_count
FROM read_parquet('events/*.parquet')
WHERE event_time >= '2025-01-01'
GROUP BY 1, 2DuckDB doesn't read the entire Parquet file into memory. It reads only the row groups that contain matching data (using Parquet row group statistics for predicate pushdown), and within those row groups, reads only the columns referenced in the query. On a 10GB Parquet file, a query referencing 3 columns with a selective date filter might read less than 200MB of actual data. On a modern NVMe SSD, this runs in a few seconds.
Compare this to Pandas: pd.read_parquet('events/*.parquet') reads every column into a DataFrame in memory before any filtering happens. For a 10GB file, you're allocating 10GB+ of memory and waiting for the full read. DuckDB is not just faster — it uses fundamentally less memory for selective queries.
The Right Use Cases for Local DuckDB
Local DuckDB is a strong fit for:
Development and testing of dbt models. Instead of running dbt against Snowflake (which costs credits and introduces latency), you can export a representative sample of your source tables as Parquet files and develop your transforms against those locally. DuckDB's SQL dialect is close enough to Snowflake/BigQuery that most SQL logic ports directly. You catch syntax errors and logic bugs before spending warehouse compute.
One-off exploratory analysis of large data files. A data engineer receives a 6GB CSV dump from a vendor. Instead of uploading it to BigQuery (which requires creating a dataset, waiting for load, then querying), they spin up DuckDB: SELECT * FROM 'vendor_dump.csv' LIMIT 100. Instant. Then run aggregations, check distributions, look for anomalies. The full exploration workflow happens locally in under five minutes.
CI/CD data pipeline testing. Running integration tests against a cloud warehouse in CI is expensive and slow. With DuckDB and Parquet test fixtures, your dbt CI job runs transforms against local files. A GitHub Actions runner can execute 200 dbt model tests against DuckDB in the time it would take to spin up a Snowflake warehouse cluster.
Python pipeline prototyping. When building a new ingestion or transform script, iterating against DuckDB is faster than iterating against a cloud warehouse. You can also use DuckDB as the execution engine for local dbt runs with the dbt-duckdb adapter, which has first-class support.
Where Local DuckDB Has Real Limits
The honest part: there are workflows where "just use DuckDB" is wrong advice.
Data that doesn't fit in local disk. DuckDB spills to disk when query results exceed available memory, but the base data needs to be accessible — either as local files or via S3/GCS (which DuckDB supports, but with network latency). If your data lives entirely in Snowflake and is measured in hundreds of GB, pulling it out as Parquet for local analysis is not free. The transfer cost and time matter.
Production analytics serving multiple users. DuckDB is a single-writer engine. It handles concurrent readers reasonably well, but it's not designed for multi-user concurrent write workloads. MotherDuck extends DuckDB with a cloud service that handles multi-user scenarios, but that's a different product. For a shared analytics environment where 10 analysts are running queries simultaneously, Snowflake or BigQuery's architecture (separate compute from storage, multi-cluster warehouses) is genuinely better.
Real-time or near-real-time queries. DuckDB queries against Parquet files on local disk or S3 are fast, but they're batch queries. If you need sub-second freshness, you need a streaming query system. DuckDB has no concept of streaming reads from Kafka or change data capture.
Complex governance and access control. Cloud warehouses have mature role-based access control, column masking, row-level security, and audit logging built in. DuckDB has none of this. For a data environment with strict access control requirements, local DuckDB is a development tool — it's not a substitute for the warehouse's security model.
DuckDB in the Context of the Modern Data Stack
The most sensible pattern we've seen at growing analytics teams: DuckDB as the local development and testing layer, cloud warehouse as the production layer. dbt models are developed and tested against DuckDB (using dbt-duckdb with Parquet test fixtures), then deployed to Snowflake or BigQuery for production runs. The SQL is compatible; the models work in both environments.
Loomkindle supports DuckDB as a first-class target in this local development pattern. When you define your semantic model against a DuckDB source, the routing and schema monitoring work the same way as against Snowflake — which means you can develop and test semantic model changes locally before deploying to the cloud warehouse, without the round-trip cost of hitting production infrastructure during development.
The WASM build of DuckDB is also worth noting: it runs in the browser, which means analytics tools like Observable and Hex can run DuckDB queries client-side on data the user has uploaded, without sending it to a server. This is genuinely useful for data tools that need to operate on sensitive user-provided files without centralized storage.
The Benchmark Question
DuckDB benchmarks favorably against BigQuery sandbox queries and local Postgres for analytical workloads in the 1-50GB range. For data in this range on local NVMe storage, a DuckDB aggregate query on 20GB of Parquet typically runs in 5-15 seconds. A comparable BigQuery query on the same data, including the job setup overhead, runs in 8-20 seconds if the slot allocation is immediate.
We're not saying DuckDB is faster than BigQuery for large-scale analytics — at 1TB+ and high concurrency, BigQuery's distributed architecture wins. We're saying that for the development and testing workloads where most data engineers actually spend their time, DuckDB's local-first architecture removes latency and cost that would otherwise slow down the iteration cycle.
The right mental model: DuckDB is a tool for the data engineer's local environment, in the same category as your IDE and your git client. It speeds up development. The cloud warehouse is for production. Use both, and be clear about which problems each one is solving.