Search or Analytics ❌ -> Search & Analytics ✅

We’ve written before about how DuckDB, dlt, and Cognee can streamline RAG systems. This new post goes a step further: not just preparing and structuring data, but running SQL analytics directly beside your graph-native retrieval.

Traditional AI memory systems force a trade-off: fast semantic search (embeddings) or powerful SQL analytics. These rarely both work well together—vector databases excel at similarity search but struggle with complex analytical queries; SQL databases handle analytics beautifully but can’t do semantic retrieval without costly, complex integrations.

Meanwhile, DuckDB can crunch through gigabytes of data in seconds, run complex aggregations, and handle analytical workloads that would choke traditional databases — all while being embeddable and requiring zero infrastructure.

At the same time, AI memory frameworks produce rich, evolving models that users want to query with natural language (e.g., “What are the trending topics this quarter?” or “Who all is involved in Project X?”). Traditional vector stores don’t handle these workloads efficiently.

The solution: bring DuckDB's analytical power directly into cognee’s AI memory graph layer. Enriched with Kuzu as the knowledge graph store, the DuckDB vector store integration creates a synergy of semantic knowledge analytics and cognee’s retrieval capabilities.

How cognee Works (the ECL Path)

cognee is built around a modular Extract, Cognify, Load (ECL) pipeline.

• Extract: ingestion of raw content from APIs, databases, or documents.
• cognify: splitting the content into chunks, generating embeddings, identifying key entities, and mapping their relationships.
• Load: writing of vector representations and graph connections to the memory backends.

This produces a semantic layer that can represent time, entities, and objects, and establish meaningful relationships between them.

DuckDB Adapter (Literal Schema & Writes)

Starting with cognee's latest release, DuckDB integration is available for both local analytics and cloud-scale processing (parallel, async), so you can run analytical queries directly alongside your knowledge graph queries.

This integration means knowledge graph embeddings are stored in DuckDB’s columnar format and uses vectorized execution for fast SQL analytics. It sits next to cognee’s graph-native retrieval, so you can analyze embeddings with SQL while cognee connects those embeddings to the knowledge graph.

Copy code
CREATE TABLE IF NOT EXISTS {collection_name} (
    id VARCHAR PRIMARY KEY,
    text TEXT,
    vector FLOAT[{vector_dimension}],
    payload JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Copy code
create_data_points_query = f"""
INSERT OR REPLACE INTO {collection_name} (id, text, vector, payload) VALUES ($1, $2, $3, $4)
"""
await self._execute_transaction(
    [(create_data_points_query, [
        str(data_point.id),
        DataPoint.get_embeddable_data(data_point),
        data_vectors[i],
        json.dumps(serialize_for_json(data_point.model_dump()))
    ]) for i, data_point in enumerate(data_points)]
)

Getting Started

Before running queries, you first need to configure cognee to use DuckDB as the vector store. The example below shows a minimal setup: pruning any previous data, adding new content, running the ECL pipeline (cognify), and then searching against the stored embeddings.

Copy code
import os
import asyncio
from cognee import config, prune, add, cognify, search, SearchType

# Import the register module to enable DuckDB support
from cognee_community_hybrid_adapter_duckdb import register

async def main():
    # Configure DuckDB as vector database
    config.set_vector_db_config({
        "vector_db_provider": "duckdb",
        "vector_db_url": "my_database.db",  # File path or None for in-memory
    })

    # Optional: Clean previous data
    await prune.prune_data()
    await prune.prune_system()

    # Add your content
    await add("""
    Natural language processing (NLP) is an interdisciplinary
    subfield of computer science and information retrieval.
    """)

    # Process with cognee
    await cognify()

    # Search (use vector-based search types)
    search_results = await search(
        query_type=SearchType.CHUNKS,
        query_text="Tell me about NLP"
    )

    for result in search_results:
        print("Search result:", result)

if __name__ == "__main__":
    asyncio.run(main())

Copy code
CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;
SELECT * FROM ducks;