Category: Developer Productivity

Two weeks ago, an agent I run locally for pipeline maintenance rewrote a retry handler using a flat, fixed-delay retry. It looked reasonable. It was also the exact pattern that caused a duplicate-row incident in May, one I’d personally debugged for four hours and was very sure I’d never see again. I hadn’t told the agent to avoid it in that session. I’d told a different session, six weeks earlier, in a different conversation that no longer existed anywhere the model could see it. The model didn’t get dumber between May and July. It just never actually knew anything to begin with, past whatever fit in that one conversation’s context window.

That’s the gap between “context” and “memory,” and it’s wider than most agent tooling admits. So I spent a weekend building the smallest version of real memory I could: a local Ollama agent, a SQLite database, and a habit of writing things down. It’s about 80 lines of Python. It’s also the difference between an agent that repeats your worst incidents and one that doesn’t.

The agent embeds its own query, searches a local SQLite store, and only pulls in the notes that actually match — not the entire conversation history.

TL;DR

• → Most “agent memory” in demos is just re-sending the whole conversation transcript on every turn, which is a longer prompt, not memory.
• → Real memory means distilling a short note after a session ends and retrieving only the relevant notes before the next one starts, using embeddings and cosine similarity, not a full transcript replay.
• → Ollama’s /api/embed endpoint combined with the sqlite-vec SQLite extension gives you a working local memory layer in under 100 lines of Python, with no hosted vector database.
• → In testing, an agent with this memory layer correctly recalled a team’s pandas-to-polars migration and avoided repeating a retry-logic mistake tied to a real past incident, both from notes written weeks earlier.
• → Retrieval only helps if notes are written for retrieval: short, dated, and tied to one concrete decision, not a copy-paste of the conversation that produced them.
• → The real failure mode isn’t forgetting, it’s confidently recalling something stale; a memory store needs a way to expire or overrule old notes, or it will resurface outdated decisions with total conviction.

Why Most “Agent Memory” Isn’t Memory

We covered the mechanics of this failure in detail in Why AI Agents Forget: a model has no persistent state between API calls, only whatever text you hand it as context. “Memory” in a lot of agent frameworks is really just a growing transcript, re-sent in full on every turn until it hits a context limit, at which point older turns get silently truncated. That’s not recall, it’s a longer prompt with an expiration date.

Actual memory needs two things a plain transcript doesn’t have: a write step that decides what’s worth keeping after the fact, and a read step that retrieves only what’s relevant to the current task, not everything ever said. That’s a search problem, not a context-window problem, and it’s the same shape of problem as full-text search over any other document store.

Building an Actual Memory Layer

The setup has three pieces: Ollama running a chat model and an embedding model, a SQLite database with the sqlite-vec extension loaded for vector search, and two small functions, one to write a note, one to recall notes.

ollama pull llama3.2
ollama pull nomic-embed-text

pip install sqlite-vec ollama

The Write Path

After each agent session, a short summarization pass turns the transcript into one or two standalone notes, each embedded and stored with a timestamp:

import sqlite3, sqlite_vec, ollama, json, time

def get_db():
    db = sqlite3.connect("memory.db")
    db.enable_load_extension(True)
    sqlite_vec.load(db)
    db.execute("""
        CREATE VIRTUAL TABLE IF NOT EXISTS notes USING vec0(
            embedding float[768],
            +text TEXT,
            +created_at TEXT
        )
    """)
    return db

def write_memory(note_text: str):
    db = get_db()
    resp = ollama.embed(model="nomic-embed-text", input=note_text)
    embedding = resp["embeddings"][0]
    db.execute(
        "INSERT INTO notes(embedding, text, created_at) VALUES (?, ?, ?)",
        (json.dumps(embedding), note_text, time.strftime("%Y-%m-%d")),
    )
    db.commit()

The note itself matters more than the plumbing. "Refactored ingest_events.py" is useless six weeks later. "Ingest job retries must use exponential backoff — a flat retry caused the May 3 duplicate-row incident" is something worth retrieving.

The Read Path

Before the agent starts a new task, it embeds the task description and pulls the closest notes by cosine distance:

def recall_memory(query: str, top_k: int = 3):
    db = get_db()
    resp = ollama.embed(model="nomic-embed-text", input=query)
    query_embedding = json.dumps(resp["embeddings"][0])
    rows = db.execute(
        """
        SELECT text, created_at, distance
        FROM notes
        WHERE embedding MATCH ?
        ORDER BY distance
        LIMIT ?
        """,
        (query_embedding, top_k),
    ).fetchall()
    return rows

Rendered output for a real query looks like this:

>>> recall_memory("add a retry to the ingest job")
[("Ingest job retries must use exponential backoff — a flat
   retry caused the May 3 duplicate-row incident", "2026-05-04", 0.13),
 ("Team migrated pandas -> polars in week 2", "2026-06-02", 0.46),
 ("Prod warehouse resizes to L on Mondays, cost review", "2026-06-10", 0.69)]

Lower distance means a closer match, so the retry note — written five weeks earlier, in a session that no longer exists in any active context window — comes back first and gets injected into the system prompt for the new task.

Wiring Memory Into the Agent Loop

The integration is two calls bookending whatever loop already drives the agent, a pattern that lines up with how we’ve written about designing agent tools generally: keep the interface small, and let the model decide what to do with what it’s given, rather than hardcoding the logic yourself.

def run_task(task: str):
    memories = recall_memory(task, top_k=3)
    memory_block = "\n".join(f"- {text}" for text, _, _ in memories)

    response = client.chat.completions.create(
        model="llama3.2",
        messages=[
            {"role": "system", "content": f"Relevant past notes:\n{memory_block}"},
            {"role": "user", "content": task},
        ],
    )
    result = response.choices[0].message.content

    # after the task completes, distill and store a new note
    summary = summarize_for_memory(task, result)
    write_memory(summary)
    return result

The Token Math

The other reason this beats “just send the whole history” is cost, not just accuracy. Assume an agent that’s been in use for three months, with roughly 400 prior sessions worth of context.

That flat cost curve is the same argument for retrieval over brute-force context stuffing that shows up in MCP-style tool design: give the model a narrow, queryable interface to what it needs, instead of handing it everything up front and hoping the important part doesn’t get truncated.

The Gotchas Nobody Warns You About

Stale notes get recalled with total confidence. A cosine-similarity match doesn’t know that a note is six months old and describes an architecture that’s since changed. Store a created_at timestamp and either expire notes past a threshold or have the agent flag anything older than some age as “may be outdated” before acting on it.

Changing the embedding model invalidates the whole store. A vector from nomic-embed-text and a vector from any other embedding model live in different mathematical spaces and aren’t comparable. If you upgrade models, you re-embed every stored note, not just new ones going forward.

Unfiltered note-writing turns into note bloat. If every session writes a note regardless of whether anything worth keeping happened, retrieval quality degrades as the noise-to-signal ratio grows. Gate the write step behind a simple check: did this session change a decision, fix a real bug, or establish a constraint? If not, don’t write anything.

Concurrent agents writing to the same SQLite file will collide. sqlite-vec doesn’t solve multi-writer concurrency for you. If more than one agent instance can run at once, put a lock around the write path or move to a proper client-server database once you’re past a single-agent prototype.

Memory silently retains whatever you fed it. A distilled note about a bug fix can carry along a credential, an internal hostname, or a customer identifier that happened to be in the task description. Treat the memory store like any other data store with retention and access rules, not a scratchpad that’s exempt from them.

The One Principle

A memory system is a curation problem before it’s a storage problem — deciding what’s worth writing down matters more than the vector database you bolt on to retrieve it.

The 80 lines of SQLite and embedding calls above are the easy part, and they’d work identically whether the notes were good or garbage. The actual engineering is in the write path: forcing every note to be short, dated, standalone, and tied to a real decision. Get that part right and it doesn’t matter whether the retrieval layer is sqlite-vec, a hosted vector database, or something fancier — the agent stops repeating May’s incident in July, which was the entire point.

Related reading: Why AI Agents Forget · AI Agent Tool Design · Model Context Protocol Explained · Running Ollama Inside a Data Pipeline · Ollama Embeddings Docs · sqlite-vec

A data engineer I know spent three weeks building a “multi-agent data quality system.” It had an orchestrator agent, a profiling subagent, an anomaly-detection subagent, and a remediation subagent, all passing messages around. It was elegant. It was also slower, more expensive, and harder to debug than the thing it replaced — which was a single agent that called four Python functions. When a check failed at 2 a.m., nobody could tell which agent had made the wrong call, because the reasoning was scattered across four isolated context windows. He rebuilt it in an afternoon as one agent with four tools, and it has run clean ever since.

That story is the whole debate in miniature. Every agent you build in a data pipeline hits the same fork: a task needs doing — query a warehouse, validate a schema, profile a table, summarize a run — and you have to decide whether it should be a tool the agent calls directly, or a subagent that handles the work in its own reasoning loop. Get it wrong toward tools and you get a bloated agent drowning in its own context. Get it wrong toward subagents and you’ve bought coordination overhead, extra LLM calls, and debugging pain for a problem a function would have solved. This is the guide to making that call correctly, every time, without over-engineering.

TL;DR

→ A tool executes your code — an API call, a SQL query, a file operation, a calculation. It’s fast, deterministic, cheap, and its result lands directly in the agent’s context. Tools don’t reason; they execute.

→ A subagent is a separate LLM call with its own system prompt, its own context window, and often its own tools. It runs a full multi-step reasoning loop and returns only a summary. From the orchestrator’s view it looks like a tool call — send a task, get a result — but a whole reasoning process happens in between.

→ Default to tools. If you can write the behavior as a function with typed inputs and outputs and it doesn’t need multi-step reasoning, it’s a tool. This covers the large majority of pipeline agent work.

→ Reach for a subagent only when the task needs genuine multi-step reasoning, when its intermediate work would pollute the orchestrator’s context, when it needs its own scoped tool set, or when independent tasks can run in parallel.

→ The decision reduces to three questions: is it execution or reasoning, does the intermediate work matter to the orchestrator, and can it run independently?

→ Every subagent adds a context window, a reasoning loop, and a handoff — more latency, more cost, more moving parts. The contract that keeps multi-agent systems debuggable: pass tasks down, pass conclusions back up.

What a tool actually is

A tool is a capability the agent uses to act on the world beyond the model’s own knowledge. In a data-engineering context, tools are the functions you already write: a Snowflake query, a call to the Airflow REST API, a dbt run trigger, a file read from S3, a schema validation, a row-count check. You expose them to the model through a defined interface — typed inputs, typed outputs — and the model decides when to call them, not how they run.

The interaction loop is simple. The model gets a task and decides it needs external data or an action. It emits a structured tool call with arguments. Your application runs the tool and returns the result. The result goes back into the same conversation, and the model keeps reasoning. The key property: the tool does no reasoning itself. It runs a predefined operation and returns data. All the planning and interpretation stays with the model.

Because a tool executes code rather than spinning up another LLM, it’s fast, deterministic, and cheap. A SQL query that returns a row count costs you the query, not an inference cycle. That’s why tools are the primary way agents touch the outside world — and why they should be your default.

What a subagent actually is

From the orchestrator’s view both look the same — send a task, get a result. The difference is what happens in between: a tool runs code; a subagent runs a whole reasoning loop in its own context.

A subagent is a separate LLM call — a distinct agent instance with its own system prompt, its own context window, and often its own tools — that receives a task, works through it independently, and returns a result to the orchestrating agent. From the orchestrator’s perspective, calling a subagent looks identical to calling a tool: send a task, get a result back.

What differs is the middle. A subagent runs its own multi-step reasoning loop, potentially makes its own tool calls, and manages its own state. The orchestrator has no visibility into that process; it only sees the summary at the end. That isolation is the whole point — and also the whole cost. The single most important consequence is the context window: when an agent calls a tool, the result lands in the same context it’s already reasoning in. When it spawns a subagent, that subagent starts fresh with only what it was handed, and everything it does stays sealed off.

Tools vs subagents: the differences that matter

The one-line version: tools execute code, subagents execute reasoning. Everything else follows from that. A tool runs your code in the orchestrator’s shared context with no reasoning, structured error returns, execution-only cost, and low latency — and when it breaks, it’s a bad schema, an API failure, or wrong arguments, all visible in the orchestrator’s context. A subagent runs another LLM in an isolated context with a full reasoning loop, additional inference cost, higher latency, and partial visibility — and when it breaks, it’s a hallucination, lost context, or a coordination failure that’s much harder to trace.

For a data pipeline, that visibility difference is the one that bites. A tool’s result — a row count, a query output, a validation pass/fail — sits in the orchestrator’s context where you can inspect it. A subagent’s internal steps are opaque by design; you get the conclusion, not the path to it. When you’re debugging why a pipeline agent did something wrong, opaque reasoning is exactly what you don’t want unless the isolation is buying you something concrete.

When a tool is the right choice

Use a tool when the operation is well-defined, deterministic, and doesn’t need multi-step reasoning. In practice, that’s most of what a pipeline agent does:

Call an external system. Fetch a table’s metadata, trigger a dbt model, post a run summary to Slack, query the warehouse. These are pure execution — the model decides to call them, your code runs them.

Transform or validate data. Run a regex, cast a type, compute a hash, check a row count against a threshold, validate a schema against a contract. Deterministic operations belong in functions, not LLM calls.

Read or write. Open a file in S3, write a manifest, check whether a partition exists, update a metadata row. Predictable and fast as direct tool calls.

Run a search or query. A SQL query, a vector search over a table catalog, a lookup in your data dictionary. The query runs deterministically and returns results; the model interprets them, but the query itself is a tool.

The practical test is one sentence: if you can write the behavior as a Python function with typed inputs and outputs, and it doesn’t need to reason through multiple steps, it should be a tool.

When a subagent earns its complexity

The four situations where a subagent’s added complexity actually pays for itself. If none of these apply, a tool is the better choice.

Use a subagent when the task genuinely needs one of these four things:

Non-obvious intermediate steps. “Investigate why last night’s pipeline run took three times as long” involves deciding what to check, reading logs, forming a hypothesis, checking the next thing, and synthesizing a root cause. Each step depends on the last. That’s a reasoning process, and it belongs in its own context.

Parallelizable work. Profiling twenty tables independently runs far faster across twenty concurrent subagents than sequentially in one context. When subtasks don’t depend on each other, parallel subagents are a real speedup.

Its own tool set. A code-writing subagent needs a code executor and file tools; a data-profiling subagent needs warehouse-query tools. Giving the orchestrator every tool at once creates tool overload — and agent accuracy is known to degrade as the tool count grows. Scoping tools per subagent keeps each agent’s decision space small and its tool-calling accurate.

Noisy intermediate output. A single query result is compact and useful in context. A multi-step investigation spanning dozens of query outputs is noise. Isolating that work in a subagent and surfacing only the conclusion keeps the orchestrator’s reasoning clean. Context isolation also improves reliability — a subagent in a fresh context can’t be distracted by the orchestrator’s accumulated history.

The three-question decision framework

Most of the time the choice comes down to three questions, in order.

1. Is the task primarily execution or reasoning? A well-defined operation with predictable inputs and outputs — a query, an API call, a calculation, a file op — is a tool. A task that requires exploring, analyzing, synthesizing, or making a chain of dependent decisions is a subagent.

2. Does the intermediate work matter to the orchestrator? If the result is small and immediately useful — a row count, a validation result — keep it in context as a tool. If the task generates a lot of intermediate work — multiple queries, document reviews, iterations — a subagent isolates that and returns only the conclusion.

3. Can the task run independently? A tool runs inline as part of the workflow and returns before the workflow continues. A subagent fits when the work can be delegated, run independently, or executed in parallel — processing many tables, researching many topics, coordinating specialized workflows.

The over-engineering trap

The most common mistake — the one from the opening story — is reaching for subagents before you need them. A subagent can make an architecture cleaner, but it also adds another context window, another reasoning loop, and another handoff. That’s more latency, more cost, and more moving parts to debug. In a lot of cases a well-designed tool is simply enough, and a separate agent creates more overhead than value.

The rule of thumb: start with a single agent and a small set of well-designed tools. Introduce subagents only when they solve a specific problem tools cannot solve cleanly — isolating large amounts of intermediate work, enabling parallel execution, or giving a complex task its own reasoning space. The question to ask before adding one: what does this subagent actually buy me? If the answer is “a little processing before returning a result,” a tool is enough. If it’s “independent reasoning, context isolation, specialized tools, or parallelism,” the subagent is justified. Tools are the default; subagents are the exception you can defend.

What adding a subagent actually costs

The contract that keeps multi-agent systems debuggable: a focused task goes down, a concise conclusion comes back up — never the full trail of intermediate work.

Calling a tool is simple: inputs in, result out. Calling a subagent means delegating part of the thinking, and that has a cost beyond the extra LLM call. The orchestrator has to define the task clearly enough for the subagent to work alone, because the subagent doesn’t inherit the orchestrator’s goals, assumptions, or conversation history. It only knows what it was handed.

So good subagent architectures live or die on clean handoffs. The orchestrator sends a focused, self-contained task. The subagent does its own reasoning and tool use. The subagent returns a concise result — “identified the three slowest tasks and the shared root cause,” not every log line and intermediate query that led there. Keeping that boundary clean does two things: it stops the orchestrator’s context from filling with intermediate noise, and it makes the system debuggable because each subagent has one clear responsibility and one well-defined output.

The rule that captures it: pass tasks down, pass conclusions back up. Clean task in, clean summary out is the contract. The moment you let subagents share mutable state or pass partial results back mid-task, you’ve introduced coordination complexity that quickly outgrows the problem you started with.

The one principle

Tools execute code; subagents execute reasoning. Default to tools — if the work fits a typed function that doesn’t reason across steps, it’s a tool — and add a subagent only when it buys you something concrete: multi-step reasoning, context isolation, a scoped tool set, or parallelism. When you do delegate, pass tasks down and conclusions back up, and nothing else. The best agent architecture is the simplest one that solves the problem, and for most pipeline work that’s one agent with a handful of sharp tools — not a committee of agents talking to each other at 2 a.m.

Related reading: Anthropic: Building Effective Agents · Google Cloud: Subagents vs agents-as-tools · Governing the AI Agent: Securing CoCo and MCP Workflows · Giving AI Agents Access to Pipeline Metadata Safely · How to Use MCP in Snowflake CoCo Desktop