DuckCon #7
Grep your lakehouse: Search-first retrieval for DuckDB-powered agents
Sylvain Utard · DuckCon #7
9
sections
17
slides
01 / The search-first instinct
Coding agents always search first
- Coding agents (Claude Code, Cursor) share one first instinct after a prompt: search the whole codebase, ignoring folders and structure.
- Given a user question like 'How are indices handled in DuckDB?', the agent runs grep / ripgrep / semantic search before writing a grounded answer.
- The same search-first pattern is now arriving in the data world.
- Sylvain Utard: 13 years building search engines; C++ engineer at an early French Google competitor, then first employee and VP Eng at Algolia, now co-founder and CEO of Altertable.
More detail
Altertable is building a data runtime for the AI era on top of the lake: a lakehouse with federation (they explicitly 'hate pipelines'), a knowledge graph, and always-on AI agents. TL;DR of the talk: they extended DuckLake with search capabilities via a ducklake_search extension.
02 / The missing primitive
Data agents need schema-agnostic retrieval
- Data analyst agents are very good at text-to-SQL, but SQL is highly structured: tables and columns.
- Agents still struggle to know which table and which column to use for a given use case.
- A semantic layer helps (especially in BI, where the schema is documented), but in practice it is often missing.
- The missing primitive for data agents is schema-agnostic retrieval: search across the data without knowing its structure.
03 / Search on top of the lake
Bringing full-text and semantic search to DuckLake
- The data already lives in the lakehouse, so there is no need to copy it elsewhere.
- Nobody enjoys building and maintaining pipelines to keep a database in sync with a separate search engine.
- Add new retrieval modes (full-text, semantic) directly on top of the same DuckLake storage layer.
- Result: same data, different retrieval modes, no synchronization pipeline.
04 / How search works
Reminder: full-text and semantic indexing
- Full-text indexing tokenizes and normalizes text, then builds an inverted index mapping each term to the document IDs that contain it.
- At query time a full-text search is efficient: look up the postings lists and intersect them (e.g. 'red AND apple' returns doc 42).
- Semantic indexing embeds documents into vectors and stores them in an HNSW graph (a vector index) for fast approximate nearest-neighbor search.
- At query time the query is embedded into a vector and a kNN graph traversal returns the top-k most semantically similar objects.
05 / Extending DuckLake
ducklake_search: extending an extension
- Inspired by DuckDB's extension system, they extended the DuckLake extension with their own extensions ('extensions on an extension').
- ducklake_search is built on top of DuckLake, Tantivy, hnsw-rs, turboquant, and model2vec-rs, reusing existing open source rather than starting from scratch.
- Guiding principle, borrowed from the DuckDB team: 'It's all just SQL', so they started by extending DuckLake's SQL surface.
More detail
Tantivy (Rust) is the full-text engine and lives beside the DuckDB (C++) world, which required careful FFI bridging so the two sides cooperate without reinventing the wheel; the Tantivy index reuses DuckDB's file system.
06 / SQL surface
CREATE INDEX and search operators
- New CREATE INDEX syntax for DuckDB: USING FTS for full-text and USING SEM for semantic indices, with WITH options.
- Search in a WHERE clause matches a string against a field, and the double-at (@@) operator provides a concise full-text predicate.
- A virtual column exposes the relevancy score so results can be ranked (like stopping at Google's first result).
- Using * as the column extends the SQL dialect to search across ALL columns at once, avoiding a big OR that agents would otherwise generate.
- Semantic search gets its own elegant operator, enabling analysis over the subset of candidates similar to a phrase like 'GDPR EU residency'.
07 / Implementation
Implementing ducklake_search close to the parquet files
- DuckLake is essentially a catalog (Postgres for now) plus parquet files on distributed storage.
- Adding search means placing the small optimized indices right next to the parquet files, so they fit the snapshotting and time-travel model of the lakehouse.
- New metadata tables (ducklake_index, ducklake_index_file) track which indices exist and where the index files live, plus table functions to explain search.
- ducklake_fts(...) and ducklake_vs(...) functions return row IDs and scores; the search results bridge to Tantivy over FFI.
08 / Query rewriting
Rewriting the query to run smoothly on DuckLake
- At query time the search query is rewritten into a form DuckDB can execute efficiently against the parquet files.
- The full-text / vector search functions return row IDs and scores, then a rewrite filter and virtual score column feed the scan.
- Simplified example: SELECT * FROM tickets WHERE rowid IN (SELECT rowid FROM ducklake_vs('*', 'GDPR EU Residency')) ORDER BY score DESC.
- The takeaway echoes DuckDB: it's all just SQL, and so is Duck Search.
09 / Wrap-up
TL;DR and a call to build extensions
- DuckLake's bet is 'it's all just SQL', and ducklake_search follows the same bet.
- DuckLake extensions could be an excellent super-secret next big thing; Altertable loves OSS and would love to open-source it.
- A lot of the search internals were simplified for the talk.
- Closing message: 'I hate pipelines, and you should too.'
Key Takeaways
- Every modern coding and data agent searches first: schema-agnostic retrieval is the missing primitive for data agents that otherwise only do text-to-SQL.
- Altertable extended DuckLake with ducklake_search to add full-text and semantic search directly on the lakehouse, eliminating the DB-to-search-engine sync pipeline.
- Full-text search uses an inverted index (Tantivy); semantic search uses vector embeddings in an HNSW graph for kNN retrieval.
- The extension reuses open source (DuckLake, Tantivy, hnsw-rs, turboquant, model2vec-rs) and builds 'extensions on an extension'.
- SQL is the interface: CREATE INDEX ... USING FTS/SEM, a @@ full-text operator, a semantic operator, a virtual score column, and searching across all columns with *.
- Indices live next to the parquet files so they inherit DuckLake's snapshotting and time-travel; queries are rewritten into plain SQL that returns row IDs and scores.
- Core philosophy: it's all just SQL, and pipelines should be avoided wherever possible.