DuckDB Concepts
An analytical database that runs inside your process. No network in the hot path, no cluster, no concurrency story — and that combination changes what a pipeline stage costs, not just how fast a query is.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What changes about a data pipeline when the analytical database is a library in your process rather than a service across the network?
Consumers who do not want a platform: a transformation job processing one partition, a CI run asserting that a model's SQL produces the expected rows, an analyst opening a folder of Parquet on a laptop, an application embedding an analytical query per user session. Each of them needs an answer over a bounded amount of data with no infrastructure between them and it (Who Actually Consumes This Data).
The unit is a process: one program, one engine instance, one address space, and — for its own storage format — one writer. Everything distinctive follows from that unit, because it is what removes the network from the query path and removes multi-tenancy from the design at the same time.
Treat it as a small warehouse: point it at your data, write SQL, and expect the same operational surface with less setup. That instinct gets a lot right — the SQL is real, the optimiser is real, and the columnar execution is the same idea a warehouse uses — which is exactly why the differences are worth stating explicitly rather than discovering.
A second process is started to run a parallel job against the same database file, and it cannot open it for writing. The single-writer model is not a limitation to work around; it is the design, and a pipeline that assumed otherwise has to be restructured (Partial Failure).
- A second process is started to run a parallel job against the same database file, and it cannot open it for writing. The single-writer model is not a limitation to work around; it is the design, and a pipeline that assumed otherwise has to be restructured (Partial Failure).
- A query reads Parquet from object storage and is far slower than the same query on local files. Nothing is wrong: the network moved from between the client and the engine to between the engine and the data, and it is now in the scan path (Object Storage as Data Infrastructure).
- A job that worked on a sample runs out of memory on the real partition, because an aggregation's intermediate state does not fit and the spill path is bounded by one machine's disk (Memory Pressure, Swap and the OOM Killer).
- The transformation is promoted to production and now three teams want to query its output. There is no server to point them at, because there is no server (Data Marts).
- A dashboard is pointed at a file the pipeline rewrites, and readers intermittently see a partially-written file — a problem the warehouse's transaction manager was quietly solving (Atomic Publish).
- Two engines — this one locally and the warehouse in production — disagree on a result, because type coercion, null handling in an aggregate, or timestamp semantics differ. The SQL is identical and the answers are not (Two Dashboards, Two Numbers).
What is actually happening
- The engine is a library linked into your process. There is no server, no connection, no wire protocol and no serialization boundary between the query and the program that asked for it — a result set is memory the caller can already see (The Process Memory Layout). That single fact is what changes the cost model. In a client-server database, a large result is expensive because it must be encoded, pushed through a socket and decoded. Here it is a pointer. So patterns that are absurd against a warehouse — pulling a million rows into the calling program to do something SQL cannot express — become ordinary (Everything Is I/O).
- Execution is vectorized over columnar batches and parallelized across the machine's cores. This is the same execution idea a distributed warehouse uses inside each worker; the difference is that there is exactly one worker and it is the machine you are on (Vectorized Execution, SIMD: One Instruction, Many Elements).
- It reads Parquet and other formats in place, pushing projections and predicates into the file so that only the required column chunks and row groups are read. That is why a folder of well-laid-out Parquet is a usable table without any load step at all (The Parquet Read Path, Predicate Pushdown).
- Data larger than memory is handled by spilling intermediate state to disk. It works, and it is bounded by one machine — which is the honest limit of the model and the point at which a distributed engine starts being the right answer (Distributed Data Processing).
- Its own storage format is a single file with ACID transactions and one writer at a time. There is no concurrency story beyond that, because multi-tenant concurrency is a server's problem and there is no server (Transactions and ACID).
- Because it is a library, it inherits the *deployment* model of whatever embeds it. It scales the way your job scales: one instance per partition, per test, per request, per user session — isolation by process rather than by workload management (Workload Isolation).
The database is a library, and that is the whole story
Every architectural difference here descends from one fact: the engine runs inside your process. There is no connection to establish, no wire protocol, no serialization of results, and no second machine that can be busy. A query result is memory your program already has a pointer to.
That reframes what a pipeline stage costs. Against a client-server database, "pull a million rows into the job and do something SQL cannot express" is a bad idea because the rows have to be encoded, shipped and decoded. In-process, the same move is a handoff, so the boundary between "what SQL does" and "what the program does" stops being a performance decision and becomes a clarity decision (Everything Is I/O).
It also deletes a set of problems by construction. There is no connection pool, no credential rotation for a data plane, no query queue, no noisy-neighbour tenant, no cluster to resume. What replaces them is a single hard boundary: one machine, one process, one writer. Nothing degrades gracefully past it — which is a virtue, because the failure is loud rather than quiet (Serverless and Database Connections).
Spin up a distributed engine, submit a job, wait for executors, read the partition, shuffle it, write it back, tear the cluster down. Do this for every partition, including the ones holding an hour of data.
Run one process per partition with the engine linked in, read the Parquet in place with projection and predicate pushdown, transform, write the output to a temporary path and move it into place. Parallelism comes from running many such processes, each independent.
A distributed engine earns its coordination overhead when the work does not fit on one machine or when a shuffle across the whole dataset is unavoidable. For per-partition work that fits in memory, the coordination is pure cost: cluster startup, executor scheduling and result serialization all exist to solve a problem this workload does not have. The in-process version also fails more usefully — one partition's process dies, is retried, and nothing else is affected.
What you gain, and what quietly disappears
It is tempting to describe an embedded engine only by what it removes — no cluster, no connection, no operations. The more useful description is a ledger, because several of the things it removes are things a warehouse was doing on your behalf and you may not have noticed.
Two rows deserve attention. Atomic publish disappears: there is no transaction manager deciding when readers see new data, so writing output to the place consumers read from is once again a way to show them a half-written file. Governance disappears: there is no central place where access to a dataset is granted or revoked, so an embedded engine reading a bucket has exactly the permissions of the process, and a per-user embedded engine is a per-user permission problem (Data Access Control).
The gains are just as real and are usually understated. Determinism and testability are the biggest: a transformation that runs entirely in a process over immutable input files is reproducible in a way that a job scheduled on shared infrastructure is not, and that is what makes it such a good fit for CI (Data Tests).
| Concern | In a server-based warehouse | In an in-process engine |
|---|---|---|
| Concurrency | Many sessions, admission control, workload management, queueing. | None. One process, one writer for its own format. Parallelism comes from running more processes. |
| Isolation | Between tenants and workloads, enforced by the server. | Between processes, enforced by the operating system. Within a process, the query shares the host's memory budget. |
| Network | Between your code and the engine; results are serialized. | Absent between code and engine; present between engine and remote data, where it lands in the scan path. |
| Transactions and publish | A transaction manager decides what readers see and when. | ACID inside its own file; for outputs to other systems, atomic publish is yours to implement again. |
| Governance | A central place to grant, revoke and audit access to a dataset. | Whatever permissions the host process has. No dataset-level policy surface exists. |
| Durability | Replicated, backed up, someone else's pager. | The durability of the filesystem or object store it wrote to, and nothing more. |
| Failure domain | A shared service; one bad query can affect others. | One process. A runaway query kills its own process and nothing else. |
| Testability | Needs credentials, a live warehouse and usually a shared environment. | A fixture, a function call and an assertion, in a few seconds, in CI, offline. |
Extension availability, remote-filesystem support, spilling behaviour for larger-than-memory queries, and the compatibility of its own storage format across versions all change quickly in this project — it moves faster than the server products in this module. Pin the version, and verify current documentation rather than relying on what a tutorial showed.
Where it belongs in a platform that also has a warehouse
The productive framing is not "instead of a warehouse" but "in the places a warehouse was never a good fit". Four of those places are common enough to name: testing transformation logic, per-partition batch work, an analyst exploring files, and analytics embedded in an application where each session wants its own isolated engine.
The decision below has no winner, and the criteria are the point. Notice that three of the four options are about *bounded, single-tenant* work. That is the shape the model fits, and every one of its limits — one writer, one machine, no governance surface — is only a limit when the work is not that shape (Workload-First Thinking).
The cost bars are relative and unitless. What they say is that the dominant term for an embedded engine reading remote data is the fetch, not the compute — which is why the file layout advice from the layout module applies here unchanged, and why a folder of small files hurts this engine as much as it hurts any other reader (File Size and the Small-Files Problem).
The dominant term whenever data is not local. Includes the request count for footers and row groups, which is why many small files hurt disproportionately.
The binding constraint far more often than CPU, and shared with the host process. Aggregations and joins with large intermediate state are what end a run.
Reduced by projection and predicate pushdown into the file, so it is governed by the file layout rather than by the engine.
Every process starts with an empty cache and there is no shared one, so the same remote bytes are read once per process. Invisible unless request counts are instrumented.
Vectorized execution uses the machine well, so on local data this is rarely the limit. It becomes visible only when the data is already resident.
Effectively absent, and that absence is the model's whole argument. There is no cluster to start, no executors to schedule and no results to serialize back across a wire.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights within this comparison only. The ranking is the teaching: an embedded engine reading remote files is an I/O and memory story, not a CPU story, so layout decisions matter here exactly as much as they do for a distributed engine.
Is this unit of work bounded, single-tenant and re-runnable?
when Always worth doing. Fixture data, the real model SQL, exact row assertions, no credentials and no shared environment.
cost A second engine in the platform whose results can diverge from the production one, so cross-engine result diffs become a test you have to write and keep (Reconciliation).
when Each partition fits comfortably on one machine, partitions are independent, and parallelism comes from running many processes.
cost You give up cross-partition operations, and you own atomic publish, retries and orchestration yourself. A shuffle across the whole dataset has nowhere to happen (Atomic Publish).
when An analyst or engineer wants to query a folder of Parquet without loading it anywhere. The single best use of the model.
cost Nothing is governed, nothing is catalogued and nothing is reproducible unless the person makes it so. Ad-hoc exploration has a way of becoming a load-bearing script (Data Discovery).
when Each user session queries its own bounded dataset, and per-session isolation is exactly what you want.
cost No shared cache between sessions, so the same remote bytes are fetched repeatedly, and access control becomes the application's problem rather than the data platform's (Row and Column Security).
when It does not. Many concurrent readers, shared governance and a stable endpoint are what a server exists to provide.
cost Choosing this anyway means rebuilding a server's responsibilities — concurrency, isolation, access control, availability — around a library that deliberately does not have them (The Data Warehouse).
How to build it
Most important first.
- Use it where the work is naturally bounded and single-tenant: one partition, one test, one request, one analyst's exploration. A workload that decomposes into independent bounded pieces fits the model exactly, and one that needs shared mutable state does not (Incremental Processing).
- Keep the data in an open columnar format and let the engine read it in place. The moment data lives in a single-process database file, everything else in the platform has to go through that process to see it (Parquet, Open Table Formats).
- Lay out the files for pushdown. This engine can only skip what the file lets it skip, so partition directories and row-group statistics do the same job here that they do for any other reader (Partition Pruning, Parquet Internals).
- Use it for testing transformation logic in CI. A model's SQL run against fixture data in-process, asserting rows, is the cheapest data test in existence and needs no warehouse credentials (Data Tests). If it also runs in production, pin the engine version and run the same SQL against both engines in CI, diffing results. Cross-engine result divergence is a real and quiet class of bug (Reconciliation).
- Publish atomically. Write output to a temporary path and move it into place, or write through a table format — the transaction manager you no longer have was doing this for you (Atomic Publish).
- Draw the boundary before you cross it: when the work stops fitting on one machine, or when several writers or many concurrent readers appear, the model has been outgrown and no tuning brings it back (Distributed Data Processing).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- ACID transactions within its own database file, with one writer at a time. That is a real guarantee and a narrow one (Transactions and ACID).
- No multi-process concurrency for writes, and no coordination between instances. Two jobs are two independent databases unless they share files read-only (Shared Memory: Zero Copies, Zero Protection).
- No durability beyond the machine. There is no replication, no failover and no second copy — the durability of your data is the durability of that filesystem or object store (Replication and Read Scaling).
- No isolation between the engine and the program hosting it: they share an address space and a memory budget, so a runaway query is a runaway process (Memory Pressure, Swap and the OOM Killer).
- Reads of external Parquet see whatever the file system shows at that moment. There is no snapshot across a set of files unless a table format provides one (Open Table Formats).
- Nothing about completeness or correctness of the data itself, as everywhere else in this domain (Data Quality).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check this model makes uniquely cheap is a fixture-based transformation test: load a small, hand-written input in-process, run the real model SQL, and assert the exact output rows. It runs in CI in seconds, needs no warehouse and no credentials, and it catches logic errors before they touch data (Data Tests).
- It misses everything about scale and layout — a model that is correct on twelve fixture rows can be unusable or wrong on a real partition where nulls, duplicates and late rows exist (Late-Arriving Data).
- It also misses cross-engine divergence unless you deliberately test for it: the same SQL producing a different answer on the production warehouse is exactly the bug a local-only test cannot see (Two Dashboards, Two Numbers).
- The engine adds essentially no latency of its own: there is no scheduling, no queue, no cluster to resume, and the fixed cost of starting a query is small enough that it does not shape the design (Latency Is a Distribution, Not a Number).
- When it reads remote object storage, the freshness a consumer feels is dominated by the round trips to fetch footers and column chunks. Layout — file size, row groups, partition directories — is what makes that tolerable (File Size and the Small-Files Problem).
- It cannot make anything fresher upstream. Embedding an engine in the consuming application removes the serving hop, not the pipeline behind it (Batch vs Streaming Ingestion).
- Reading files in place means schema evolution is the *file format's* problem, not the database's: a Parquet column added upstream simply appears, and a retyped one produces a read error or a coercion depending on the reader (Schema Evolution).
- Its own storage format evolves with the engine, and a database file is tied to the version that wrote it more tightly than a server-based system's data is. That is an argument for treating its file as a cache or a workspace rather than as a system of record.
- Semantic change passes straight through, as everywhere. The engine will happily aggregate a column whose meaning changed last month (Semantic Changes).
- Recovery is trivially cheap in the direction that matters: re-run the process. A bounded, single-tenant job with deterministic SQL over immutable input files is the most re-runnable thing in a data platform (Idempotent Data Pipelines).
- Recovery of its own database file is your job entirely — copy it, version it, or better, do not depend on it. Treat the file as derived state and keep the durable copy in an open format on object storage (Keeping Raw History: The Recovery Position and the Liability).
- A failed run must not leave a half-written output visible. Write to a temporary path and rename or commit, so a retry starts from a clean state and consumers never read a partial file (Atomic Publish, Retries in Pipelines).
What can go wrong
- Memory exhaustion on a real partition after success on a sample, because the engine shares a memory budget with the host process and the spill path is bounded by one machine.
- A second writer that cannot open the database, discovered when a job is parallelised.
- Remote reads dominating runtime, because the network moved into the scan path and the file layout was never designed for pushdown.
- Cross-engine result divergence between local development and the production warehouse, discovered by a stakeholder rather than by a test (Two Dashboards, Two Numbers).
- The mitigation failing too: a fixture test suite that passes forever because the fixtures never grew the nulls, duplicates and late rows that production has (The Dimensions of Data Quality).
- A production dependency that grew accidentally — a laptop script that became load-bearing, with no scheduling, no monitoring and no owner (Data Platform Anti-Patterns).
- "It is a small database, so it is for small data." It is a single-machine database, which is a different constraint. One machine now holds a great deal of memory and many cores, and a well-laid-out columnar dataset that fits on one is comfortably within reach (Horizontal vs Vertical Scaling).
- "It replaces the warehouse." It replaces the warehouse for bounded, single-tenant work. It does not provide concurrency, isolation, shared governance or a place for eighty dashboards to point, and those are most of what a warehouse is for (The Data Warehouse).
- "There is no network, so it is always fast." The network moved rather than disappeared. Reading remote Parquet puts it directly in the scan path, where file layout and pushdown decide everything (The Parquet Read Path).
- "It is embedded, so there is nothing to operate." The process it lives in has a memory budget, a failure mode and an owner. A laptop script that quietly became load-bearing is an operational problem with no monitoring (Pipeline Observability).
- "If the SQL runs locally it will behave the same in production." Two engines can disagree on type coercion, null handling in aggregates and timestamp semantics. Diff the results, do not assume them (Reconciliation).
Operating it
- Peak resident memory of the host process per run, per input size. This is the signal that predicts the failure this model actually has (Memory Leaks: Growth That Does Not Come Back).
- Bytes fetched from object storage per query, and the number of range requests behind it. Remote reads are the dominant term whenever data is not local (Scan Cost).
- Wall-clock per partition against input row count, which tells you when the bounded-work assumption is starting to fail (Throughput: Requests, Packets and Bytes per Second).
- Cross-engine result diffs in CI, as a first-class test result rather than an occasional investigation (Reconciliation).
- At 10x within one machine, nothing changes structurally — more cores and more memory carry it, and the spill path handles the rest (Horizontal vs Vertical Scaling).
- At 100x, or at the point where one partition no longer fits comfortably on one machine, the model is simply over. There is no distributed mode to grow into, and that clarity is a feature: the boundary is visible rather than gradual (Distributed Data Processing).
- Consumer count is the axis that ends it soonest. Many concurrent readers need a server, and embedding an engine per consumer means every consumer re-reads the data with no shared cache and no shared governance (Data Access Control).
- Bytes fetched from remote storage, including the request count for footers and row groups — the dominant driver whenever the data is not on the local disk (Egress: Moving Data Costs Money, Not Just Storing It).
- Memory, which is the binding constraint far more often than CPU and is shared with whatever else the process is doing (Reading Memory: RSS, Heap, Working Set and the Number on Your Dashboard).
- Machine time on whatever runs the process — a container in a job, a laptop, an application server. It is ordinary compute rather than a separate metered service (Compute Waste).
- Repeated fetching: many short-lived processes each re-reading the same remote files, with no shared cache between them because there is no shared anything (Caching Patterns).
- Engineering time not spent operating a cluster, which is a real and frequently decisive saving in the opposite direction (Scoring Operational Complexity).
- Removing the network from the query path buys enormous simplicity and gives up everything a server provides: concurrency, isolation, shared caching, central access control and a single place to point consumers at.
- Reading open files in place buys portability — the same files serve every other engine — and costs you the transaction manager, so atomic publish becomes your responsibility again (Open Table Formats).
- Using it in CI as well as in production buys extremely fast tests and introduces a second engine whose results must be diffed against the first, because two optimisers agreeing is an assumption rather than a guarantee.
Dataset review questions
This lesson uses the shared review exercise.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- ENGINE-SPECIFICThe in-process model, the single-writer storage file and the absence of any distributed mode are properties of this engine; a warehouse gives you concurrency and governance and takes the network back, and a distributed engine gives you more than one machine and takes back the simplicity of a function call.
- SCALE-SPECIFICEverything here holds while the work fits on one machine and is single-tenant; above that boundary the advice inverts completely, and the useful skill is recognising the boundary in advance rather than discovering it when a job stops fitting.
- GENERALThe underlying observation — that removing a serialization and network boundary changes which patterns are affordable, not merely how fast they are — applies to any embedded engine, and is the reason embedded analytics keeps reappearing in different forms.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns the question this engine answers by refusing it: what coordination costs, and why a single-machine design avoids an entire class of failure by having no second machine to disagree with.
- — DevOps / Production Engineering owns how a process-per-partition job is packaged, scheduled, retried and resourced, and how a memory limit is chosen so a runaway query fails cleanly instead of taking a node with it.