Database / DuckDB Interview questions
1. What is DuckDB?
DuckDB is an in-process, open-source SQL database management system built specifically for analytical (OLAP) workloads. It runs directly inside a host application, a Python script, an R session, a command-line process, rather than as a separate server a client connects to over a network, similar ...
2. What does "in-process" mean for a database like DuckDB?
An in-process database runs as a library loaded directly inside the same operating system process as the application using it, rather than as a separate server process the application connects to over a socket or network. There's no client-server round-trip, no separate database process to start,...
3. What is columnar storage, and how does DuckDB use it?
Columnar storage organizes data by column rather than by row: all values for a single column are stored contiguously, instead of a traditional row-oriented layout where all fields of a single record sit next to each other. DuckDB stores and processes data this way internally. This layout matters ...
4. What is vectorized query execution in DuckDB?
Vectorized execution processes data in batches, or "vectors," of many values at once (rather than one row at a time), applying each operation to an entire batch before moving to the next stage of the query plan. DuckDB's execution engine is built around this model from the ground up. This matters...
5. What formats can DuckDB query directly?
DuckDB can read and query several common data formats directly, without a separate load or import step, treating a file (or a set of files) essentially like a table. SELECT * FROM 'data.parquet' ; SELECT * FROM 'events.csv' ; SELECT * FROM read_json( 'logs.json' ); SELECT * FROM 's3://my-bucket/d...
6. What is the DuckDB CLI?
The DuckDB command-line interface is a standalone, single-binary executable that provides an interactive SQL shell, letting a user run queries, explore files, and manage databases directly from the terminal without writing any code in a host language. $ duckdb D SELECT * FROM 'sales.csv' LIMIT 5 ...
7. What is the Python API for DuckDB used for?
DuckDB's Python API, installed via pip install duckdb , lets Python code execute SQL queries directly against DuckDB and exchange data with common Python data tools with minimal overhead. import duckdb import pandas as pd df = pd . read_csv( "sales.csv" ) result = duckdb . sql( "SELECT category, ...
8. What is Parquet, and why does DuckDB work well with it?
Parquet is an open, columnar, binary file format designed for efficient storage and retrieval of large analytical datasets, widely used across the modern data ecosystem (Spark, Iceberg, Delta Lake, and many others all read and write it). Because Parquet is already columnar and stores per-column s...
9. What is zero-copy integration with Pandas/Arrow?
Zero-copy integration means DuckDB can read data from (and hand data back to) tools like Pandas or Apache Arrow without physically duplicating that data in memory first, by sharing the same underlying columnar memory layout (Arrow's format) rather than serializing and deserializing between two di...
10. What are DuckDB extensions?
Extensions are optional, loadable modules that add functionality beyond DuckDB's core engine, keeping the base installation small and dependency-free while still letting users opt into exactly the additional capabilities they need. INSTALL httpfs; LOAD httpfs; INSTALL spatial; LOAD spatial; Commo...
11. What is the httpfs extension used for?
The httpfs extension lets DuckDB read (and, for some backends, write) files directly over HTTP/HTTPS and from S3-compatible object storage, without first downloading the entire file to local disk. INSTALL httpfs; LOAD httpfs; SELECT * FROM read_parquet( 's3://my-bucket/data/2026/*.parquet' ); For...
12. What is ATTACH used for in DuckDB?
ATTACH connects DuckDB to another database, either another DuckDB file or, via extensions, an entirely different database system like PostgreSQL, MySQL, or SQLite, making its tables directly queryable (and in some cases writable) from within the same DuckDB session. ATTACH 'other_database.duckdb'...
13. What is MotherDuck?
MotherDuck is a cloud data platform built on top of DuckDB, offering a managed, serverless environment for running DuckDB workloads in the cloud, with features like hybrid execution that can seamlessly split a query between a user's local DuckDB instance and MotherDuck's cloud backend depending o...
14. What is DuckLake?
DuckLake is an open table format for data lakehouses, created by DuckDB Labs, built around a simple but distinctive idea: storing a lakehouse's table metadata (schema, snapshots, file listings, statistics) in a proper SQL database rather than in the large number of small JSON and Avro metadata fi...
15. What is a row group in DuckDB's storage format?
A row group is a horizontal partition of a table, a contiguous set of rows, stored and compressed together as a unit within DuckDB's on-disk columnar storage format, conceptually similar to Parquet's own row-group structure. Each row group stores its columns' data compressed and, importantly, kee...
16. What is a zone map, and how does DuckDB use it?
A zone map (also called a min/max index or block-level statistics) is a lightweight summary, typically just the minimum and maximum values, recorded for a chunk of data (like a row group), letting a query engine determine whether that chunk could possibly contain rows matching a filter condition ...
17. What are DuckDB's ACID transaction guarantees?
Despite being an embedded, analytics-focused database, DuckDB provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction guarantees for operations against its own storage format, the same fundamental correctness guarantees expected of a traditional transactional database. BEGI...
18. What is DuckDB-Wasm?
DuckDB-Wasm is a WebAssembly build of DuckDB that runs entirely inside a web browser, letting SQL queries execute directly on data loaded into a browser tab, in JavaScript memory or files a user has selected, without any server-side database or backend query processing at all. import * as duckdb ...
19. What is the difference between OLAP and OLTP, and where does DuckDB fit?
OLTP (Online Transaction Processing) systems are optimized for many small, concurrent read/write operations, typically touching a few rows at a time, like processing individual orders in an e-commerce system. OLAP (Online Analytical Processing) systems are optimized for the opposite pattern: comp...
20. What client languages/APIs does DuckDB support?
DuckDB ships official client libraries and interfaces across a broad range of languages and integration points, reflecting its design goal of being easy to embed wherever analytical work already happens. CLI - a standalone interactive SQL shell. Python - the most widely used client, with deep Pan...
21. What is a single-file DuckDB database?
A DuckDB database can be persisted as a single file on disk (conventionally with a .duckdb extension), containing all of the database's tables, indexes, and metadata in one self-contained file, similar in spirit to a SQLite database file. import duckdb con = duckdb . connect( "analytics.duckdb" )...
22. What is the DuckDB JSON extension used for?
The JSON extension (largely bundled by default in recent DuckDB versions) adds functions and a dedicated data type for working with semi-structured JSON data directly within SQL queries, letting JSON fields be parsed, queried, and reshaped without a separate preprocessing step. SELECT data ->> 'n...
23. What is the spatial extension used for in DuckDB?
The spatial extension adds geospatial data types (like points, lines, and polygons) and a set of geospatial functions (distance calculations, spatial joins, geometry operations) to DuckDB, letting it handle location-based analytical queries alongside standard relational and columnar data. INSTALL...
24. What are the main use cases for DuckDB?
DuckDB's combination of speed, simplicity, and direct file-querying capability suits a range of scenarios where a full server-based data warehouse would be excessive overhead but real analytical SQL power is still needed. Exploratory data analysis - quickly querying local CSV/Parquet files or a P...
25. What is the relationship between DuckDB and DuckDB Labs?
DuckDB is the open-source project and codebase itself, freely available under the MIT license, developed as a community and academically-rooted project originating from CWI in Amsterdam. DuckDB Labs is a commercial company, founded by DuckDB's original creators, that provides professional support...
26. Explain the execution flow of a query in DuckDB from SQL to result?
Running a SQL query in DuckDB passes through several distinct stages inside the same process, from raw SQL text to a materialized result, all without any network round-trip since everything happens in-process. sequenceDiagram participant App as Host Application participant Parser participant Bind...
27. Why is DuckDB often described as "SQLite for analytics"?
The comparison points to deployment model, not internal architecture: like SQLite, DuckDB runs in-process, requires no separate server to install or manage, and can persist to (or be entirely contained within) a single portable file, making it trivially easy to embed directly into an application ...
28. How does DuckDB differ from a traditional client-server database like PostgreSQL?
PostgreSQL runs as a standalone server process that client applications connect to over a network protocol (even if that network is just localhost), designed from the ground up around many concurrent clients, robust multi-user access control, and a row-oriented storage engine well suited to trans...
29. What is the difference between row-oriented and columnar storage for analytical queries?
Row-oriented storage keeps all fields of a single record contiguous on disk, which is efficient for retrieving or modifying one complete record at a time, the dominant access pattern for transactional workloads. Columnar storage keeps each column's values contiguous instead, which is efficient fo...
30. How do you query a remote Parquet file on S3 directly using DuckDB?
Querying a remote Parquet file in S3 (or any S3-compatible object storage) requires loading the httpfs extension and, typically, configuring credentials, after which the S3 path can be used directly in a query exactly as if it were a local file. INSTALL httpfs; LOAD httpfs; SET s3_region = 'us-ea...
31. When should you use DuckDB instead of a distributed system like Spark?
Spark and similar distributed engines exist specifically to scale processing across many machines when a single node's memory and CPU genuinely aren't enough to handle the data volume or computation involved. DuckDB, by contrast, is a single-node engine, extremely fast and efficient within that c...
32. How do you troubleshoot slow query performance in DuckDB?
Slow queries in DuckDB usually trace back to a handful of common causes, most diagnosable directly through DuckDB's own query plan inspection tools. Check the query plan with EXPLAIN or EXPLAIN ANALYZE - reveals whether filters are being pushed down, which join algorithm was chosen, and where tim...
33. What is the difference between DuckDB's in-process mode and its new client-server (Quack) protocol?
DuckDB's traditional, and still default, mode is fully in-process: the database engine is a library loaded directly inside the calling application, with no network protocol or separate server involved at all. The newer Quack protocol adds an optional, genuine client-server deployment mode on top ...
34. How does DuckDB achieve high performance without a separate server process?
DuckDB's performance comes from a combination of architectural decisions working together, rather than any single trick, and the absence of a separate server process is actually one contributing factor rather than a limitation to work around. No client-server serialization overhead - running in-p...
35. Explain the internal working of morsel-driven parallelism in DuckDB?
Morsel-driven parallelism divides a query's work into many small, independent chunks, "morsels", of data (a portion of a row group, for instance), which are then dynamically assigned to whichever worker thread becomes available next, rather than statically dividing work into a fixed number of lar...
36. What is the difference between DuckDB and Apache Iceberg/Delta Lake for table formats?
DuckDB itself is a query engine, it executes SQL and can read and write many table formats, but isn't itself a table format. Apache Iceberg and Delta Lake are open table format specifications: they define how table metadata, schema, snapshots, file listings, is organized and tracked over a set of...
37. How does DuckLake's data inlining solve the small-file problem?
The "small-file problem" in traditional Parquet-based lakehouses happens when frequent, small write transactions (like streaming inserts arriving one at a time or in tiny batches) each generate their own small Parquet file, quickly accumulating an enormous number of tiny files that hurt both stor...
38. Why does DuckLake store metadata in a database instead of files, unlike Iceberg/Delta Lake?
Iceberg and Delta Lake track table metadata, schema versions, snapshot history, file listings, through a layered structure of small JSON and Avro files sitting in the same object storage as the actual data. This design works, and is proven at large scale, but coordinating consistent, concurrent u...
39. What is the difference between DuckLake and traditional Parquet-based data lakes?
A "traditional" (pre-lakehouse-format) data lake is often just a collection of Parquet files in object storage with no formal metadata layer at all, an application or query engine has to infer a table's structure and current state by listing and inspecting files directly, with no built-in schema ...
40. How does DuckDB use zone maps and Parquet statistics to prune I/O?
Both DuckDB's own native storage format and Parquet files carry per-row-group statistics, typically minimum and maximum values for each column within that row group, which the query engine can compare against a query's filter conditions before ever reading the row group's actual data. -- If a row...
41. When would you attach DuckDB directly to a PostgreSQL database instead of exporting data first?
Exporting data from PostgreSQL before analyzing it, dumping to CSV, loading into another tool, adds a manual step, introduces staleness (the exported snapshot immediately starts drifting from the live database), and requires managing that export/import pipeline as its own piece of infrastructure....
42. How do you optimize a DuckDB query against a large Parquet dataset?
Optimization for large Parquet queries in DuckDB mostly comes down to helping the engine prune as much unnecessary I/O as possible and avoiding unnecessary intermediate data materialization. Select only needed columns - avoid SELECT * when only a few columns are actually needed, since columnar pr...
43. What is the difference between MotherDuck's hybrid execution and running DuckDB fully locally?
Running DuckDB fully locally means every table, file, and computation involved in a query lives and executes entirely on the local machine, with no external service involved at all. MotherDuck's hybrid execution model instead lets a single query span both a local DuckDB instance and MotherDuck's ...
44. Explain the lifecycle of a write operation in a DuckLake-backed table?
Writing to a DuckLake table involves both the actual data (destined for Parquet in object storage) and metadata about that write (destined for DuckLake's SQL-database-backed catalog), coordinated so that a query always sees a consistent view regardless of exactly where a given row's data currentl...
45. How do you troubleshoot memory issues when DuckDB processes a dataset larger than available RAM?
DuckDB is designed to handle out-of-core processing, datasets larger than available memory, by spilling intermediate data to disk when necessary, but memory pressure can still cause slowdowns or, in extreme cases, failures if not managed appropriately. Check the configured memory limit - DuckDB's...
46. What is the difference between DuckDB's vectorized execution and traditional row-at-a-time execution?
Traditional row-at-a-time (or "tuple-at-a-time," Volcano-style) execution processes a query by pulling one row through the entire operator pipeline before moving to the next row, calling a next() -style function repeatedly for every single row. This is simple to implement and reason about, but th...
47. How does DuckDB's cost-based optimizer decide on a query plan?
DuckDB's optimizer uses statistics about the data, row counts, distinct value counts, min/max ranges, collected for tables and maintained (or estimated) for external files like Parquet, to estimate the relative cost of different possible ways to execute a given query, then chooses the plan it est...
48. Why should you avoid treating DuckDB as a high-concurrency, multi-writer OLTP database?
DuckDB's traditional embedded model is fundamentally single-process: while it supports full ACID transactions and MVCC-based concurrency within that one process, it isn't designed around the pattern a production OLTP system needs, many entirely separate client processes, potentially on different ...
49. What is the DuckDB Quack protocol, and how does it change DuckDB's deployment model?
Quack is a client-server protocol, introduced as part of a broader 2026 effort to make DuckDB deployable as a real, standalone network-accessible service, complete with a production-grade OAuth/OIDC authentication layer, rather than remaining exclusively an in-process, embedded library. This repr...
50. How do you troubleshoot schema evolution issues when querying a DuckLake table over time?
Schema evolution issues in a DuckLake table, unexpected column types, missing data for older snapshots, queries returning different shapes than expected, generally trace back to the interaction between a table's schema history and whichever specific snapshot or point in time a query is actually r...
