Tag: agent

  • Building AI Agents: What Actually Works in Production

    Building AI Agents: What Actually Works in Production

    Ask ten teams if they’ve built an AI agent and nine will say yes. Look at what they actually shipped and most of it is a workflow with one LLM call in the middle — a fixed pipeline where a model fills in one step, not an autonomous system deciding its own path. That’s not a criticism. It’s the most important thing nobody says out loud about building AI products in 2026: the stuff that actually works in production is far less “agentic” than the demos, and the teams getting value are the ones who figured out which 20% of true agency is worth the risk and hard-coded the other 80%.

    There’s a clean mental model underneath the hype, though, and it’s worth having whether you’re building a real agent or an honest workflow. An agent is just a language model wrapped in five things: tools (what it can do), knowledge (what it knows), memory (what it remembers), a loop (how it acts), and guardrails (what it must not do). Answer “what tools, what knowledge, what memory” for your use case and the architecture mostly writes itself. This is that model, told from a data engineer’s chair — where “tools” means governed access to your warehouse, “knowledge” means your RAG and your data quality, and every one of these five parts fails in ways you’ve seen before under different names.

    TL;DR

    • → An AI agent is an LLM plus five components: tools, knowledge, memory, a reasoning loop, and guardrails — design those and the architecture follows.
    • → Most production “agents” are really workflows with one smart step, and that’s usually the correct, cheaper, safer choice — reach for true agency only when the path genuinely can’t be fixed in advance.
    • → What works: narrow single-purpose agents, curated tool sets, humans approving risky actions, and evals gating every release. What’s still hype: fully autonomous do-anything agents acting unsupervised.
    • → For a data engineer, “give the agent tools” means governed, least-privilege access to your systems — an agent with write access is a service account that makes its own decisions.
    • → “Knowledge” is a RAG-and-data-quality problem, not a model problem; most agent failures trace to bad retrieval or stale data, not a weak LLM.
    • → The reasoning loop needs a hard step cap, and the whole thing needs evals — an agent without evaluation is an untested pipeline with a mind of its own.

    The five parts of an agent

    Strip away the branding and every agent, simple or complex, is a model surrounded by the same five components. The value of the model is that it forces you to answer concrete questions before you write code — and each question maps onto infrastructure you already own.

    The whole model on one page: an LLM is just the reasoner. Tools, knowledge, memory, the loop, and guardrails are the parts you actually engineer — and the parts that actually break.

    Tools — what it can do. The functions the agent can call: query a table, hit an API, write a file. For a data engineer this is the highest-stakes component, because “give the agent a tool” means “grant a non-deterministic system access to your infrastructure.” A read-only tool against account-usage views is low risk; a tool that can suspend a warehouse or write to a table is a service account that makes its own decisions. Least privilege isn’t a nice-to-have here — it’s the whole safety model, which is why I’ve written separately on giving agents access without opening security holes. The emerging standard for exposing these tools cleanly is MCP, which I broke down in MCP explained in 3 levels.

    Knowledge — what it knows. The facts the agent needs that aren’t in the model’s training: your schemas, your docs, your current data. This is a retrieval problem, and it’s where most agent projects actually fail — not because the model is weak, but because the retrieval is bad or the underlying data is stale. If you’re wiring this up on your own data, the mechanics are in the Cortex Search RAG guide. The uncomfortable truth: your agent is only as good as the data platform underneath it.

    Memory — what it remembers. State that persists across steps or sessions, so a follow-up like “now do the same for last quarter” doesn’t start from zero. In practice this is checkpointing — the same durable-state thinking you apply to any pipeline that has to resume after a failure rather than restart.

    The loop — how it acts. The reason-act-observe cycle that separates an agent from a single call: the model reasons, calls a tool, reads the result, and decides what to do next. This loop is the source of an agent’s power and its danger, which is why it always needs a hard cap on steps.

    Guardrails — what it must not do. Input validation, output filtering, step limits, and human approval for risky actions. These are the AI equivalent of the constraints and tests you’d never ship a pipeline without — the difference between a demo and something you can leave running.

    The distinction that matters most: workflow vs agent

    Before you build anything, answer one question honestly: does the task need the model to decide the path, or just to do one hard step along a path you already know? Getting this wrong is the most common and most expensive mistake in the space.

    A workflow runs a path you defined; an agent decides the path at runtime. The runtime freedom is exactly what makes agents powerful — and exactly what makes them harder to test, secure, and cost-control.

    workflow is a fixed sequence you designed, with a model doing the intelligent part of one or more steps — classify this ticket, extract these fields, draft this summary. You control the path; the model fills in the reasoning. An agent hands the model the wheel: it decides which tools to call and in what order, looping until the task is done. The agent is more flexible and can handle open-ended tasks a rigid pipeline can’t — but that same freedom is what makes it harder to test (the path changes every run), harder to secure (it can call tools in combinations you didn’t anticipate), and harder to cost-control (each loop is a billed call). The senior move is to default to a workflow and escalate to agency only where the task genuinely can’t be pre-planned — which is the same restraint I argued for in choosing tools over subagents.

    What actually works (and what’s still theater)

    Here’s the honest cut from teams actually running this in production, stripped of vendor optimism. The pattern is consistent: narrow beats broad, supervised beats autonomous, and boring beats clever.

    The consistent signal from production: narrow beats broad, supervised beats autonomous, curated beats sprawling. The right column is where budgets and pilots go to die.

    The single most reliable pattern is the narrow, single-purpose agent: one job, a small curated set of tools, and a human in the approval path for anything consequential. The fantasy that keeps failing is the autonomous do-anything agent with dozens of tools and no supervision — it demos beautifully and falls apart on the long tail of real inputs. Multi-agent “swarms” are especially over-applied; most problems that get a swarm proposal are better served by one well-scoped agent or, more often, a workflow. And underneath all of it: without evals gating releases, you have no idea whether a change helped or hurt, which means “it looked good in the demo” becomes your entire QA process. This connects to a deeper reliability problem I covered in why larger models give confidently wrong answers in production — more autonomy multiplies the blast radius of that failure mode.

    A pragmatic way to start

    If you’re building your first AI product, resist starting with “let’s build an agent.” Start with the three founding questions — what tools, what knowledge, what memory — and answer them for the narrowest useful version of the task. Then build the simplest thing that could work, which is almost always a workflow: a fixed path with one or two LLM-powered steps, real retrieval behind the knowledge step, and a guardrail on anything that touches production. Add evals before you add capability. Only when you hit a task where you genuinely can’t predetermine the path — where the model really does need to choose among tools dynamically — do you graduate to a true agent, and even then you keep the tool set tight and a human on the risky actions. The teams shipping working AI products aren’t the ones who built the most autonomous system. They’re the ones who were honest about how little autonomy the job actually required.

    The gotchas nobody warns you about

    Most “agents” should be workflows. If you can draw the path in advance, you don’t need an agent — you need a pipeline with a smart step. Building an agent for a workflow problem buys you cost, unpredictability, and a security surface you didn’t need.

    Every tool is an attack surface and a cost center. More tools means more ways for the agent to do something surprising, and more tokens spent deciding among them. Curate ruthlessly; a tight tool set outperforms a sprawling one.

    Your agent’s ceiling is your data platform. Bad retrieval, stale data, or missing metadata will sink a great model. Agent quality is a data-quality problem wearing a trench coat.

    The loop will run away if you let it. A missing step cap turns a confused agent into a runaway bill. Set a hard recursion limit before you set anything else.

    No evals means no engineering. If you can’t measure whether a change improved the agent, you’re not building a product, you’re tuning a slot machine. Evals — including credit for the agent saying “I can’t do this” — come before more features.

    The one principle

    An agent is a language model wrapped in tools, knowledge, memory, a loop, and guardrails — and “what actually works” is deciding, for each of those five, how little you can get away with. The instinct that ships working AI products isn’t reaching for maximum autonomy; it’s the engineer’s instinct to build the simplest system that solves the problem, grant the least access that does the job, and measure everything. You already have those instincts. Building agents is mostly the discipline of not abandoning them because the technology is exciting.


    Related reading: MCP: how agents get their tools · Giving agents access safely · Tools vs subagents: don’t over-build · Build the knowledge layer (RAG) · OpenAI: a practical guide to building agents · AI agent systems: architectures & evaluation

  • 20 AI Concepts Every Data Engineer Actually Needs

    20 AI Concepts Every Data Engineer Actually Needs

    There are a hundred “AI concepts explained for beginners” listicles, and most of them are written for people who will never build anything. This one isn’t. If you’re a data engineer, you already understand pipelines, storage, and cost better than most ML tutorials assume — what you actually need is a map of the AI vocabulary that keeps showing up in your Slack, your architecture reviews, and your on-call, with a straight answer to the only question that matters: what does this mean for the systems I build?

    So this is the 20-concept tour, but curated and framed for practitioners. No math derivations, no “imagine a neuron is like a brain cell” hand-waving. Each concept gets a plain definition and a one-line reason it matters in a data pipeline. They’re grouped into four tiers — foundations, language models, grounding, and production — because that’s roughly the order the ideas build on each other, and roughly the order you’ll hit them in real work.

    TL;DR

    • → For data engineers, the AI stack reduces to four tiers: foundations (how models learn), language models (how LLMs behave), grounding (how you make them use your data), and production (how you ship them safely).
    • → The three concepts you’ll argue about most are RAG, fine-tuning, and MCP — RAG is what the model knows, fine-tuning is what it is, MCP is what it can do.
    • → The 2026 default escalation is prompt → RAG → fine-tune → distill; you move right only when the cheaper option provably hits a wall.
    • → Most AI failures in production are data problems, not model problems — Gartner projected at least 30% of generative-AI projects would be abandoned after proof of concept, with poor data quality a leading cause.
    • → Embeddings and vector databases are just a new index type over your data; you already have the instincts to reason about them.
    • → Tokens and context windows are cost and correctness levers, not trivia — they decide your bill and your accuracy.
    • → Evals and guardrails are the AI equivalent of tests and constraints; a model without them is an untested pipeline.

    Tier 1: Foundations — how models learn

    1. Neural network. A stack of simple math functions with tunable weights that, together, learn to map inputs to outputs. For your purposes it’s a black box that turns numbers into numbers; the engineering interest is that it’s just a big parameterized function, not magic.

    2. Training. The process of adjusting those weights by showing the network examples and nudging it toward less wrong. Relevance: training is a batch job with a colossal input dataset — the same data-quality-in, garbage-out rule you already live by applies, at scale.

    3. Parameters (weights). The learned numbers inside the model; “7B” or “70B” refers to how many. More parameters means more capacity and more cost to run — model size is a compute-budget decision, not just an accuracy one.

    4. Tokens. The chunks (roughly word-pieces) that models read and write. This is the one every engineer underestimates: tokens are the unit you’re billed in and the unit context limits are measured in. A pipeline that sends 40,000 tokens per call has a cost and latency profile you must design for.

    5. Embeddings. A way to turn text (or images, or rows) into a fixed-length vector of numbers where “similar things are near each other.” Relevance: this is just a new kind of index over your data. If you can reason about a hash index, you can reason about an embedding — it’s a lookup keyed on meaning instead of exact match.

    Tier 2: Language models — how LLMs behave

    6. Large Language Model (LLM). A very large neural network trained to predict the next token over enormous text corpora, which turns out to be enough to answer questions, write code, and summarize. Treat it as a stochastic function from prompt to text — powerful, useful, and never guaranteed correct.

    7. Context window. The maximum tokens a model can consider at once — prompt plus retrieved data plus its own output. This is a hard architectural constraint: it caps how much of your data a single call can see, and, as I’ve written about in why larger models give wrong answers in production, accuracy often degrades long before you actually fill it.

    8. Temperature. A knob for randomness in the output. Low means consistent and predictable; high means varied and creative. For data pipelines you almost always want it low — but even at zero, output isn’t fully deterministic, which trips up a lot of teams.

    9. Prompting. The instructions you give the model. It’s the cheapest, fastest way to change behavior, and the first rung of the escalation ladder below. Underrated skill: a precise prompt often beats a fancier technique.

    10. Non-determinism. The same prompt can produce different answers on different runs. This breaks the mental model engineers bring from deterministic systems, and it’s why you can’t validate an LLM feature with a single spot-check — I dug into the mechanics in why LLMs give different answers to the same question.

    Tier 3: Grounding — making models use your data

    This is the tier that turns a generic model into something useful on your organization’s data, and it’s where the three most-argued-about concepts live. The distinction is worth getting exactly right, because teams routinely pick the wrong one and waste weeks.

    The distinction teams get wrong most often: RAG, fine-tuning, and MCP solve three different problems and combine freely — they’re not competing choices.

    11. RAG (Retrieval-Augmented Generation). At query time, you retrieve relevant documents from your own data and feed them into the prompt, so the model answers from current, private context instead of only its training. It’s the knowledge layer, the default starting point for most enterprise use cases, and the reason answers can carry citations.

    12. Vector database. The store that holds embeddings and answers “find the N most similar chunks to this query.” It’s the retrieval engine underneath RAG — conceptually, a similarity index you query with meaning. If you want the hands-on version, I walked through building this on Snowflake in the Cortex Search RAG guide.

    13. Fine-tuning. Actually retraining the model’s weights on your examples to change its behavior — tone, format, domain reasoning. It’s the behavior layer, not a way to teach the model new facts (RAG does that better). In 2026, small-model fine-tunes via LoRA/QLoRA are cheap; full fine-tuning rarely makes sense for a product team.

    14. Hallucination. When a model produces confident, fluent, wrong output. This is the single most important failure mode for anyone putting AI on data, because it fails silently — the answer looks right. RAG with citations is the most reliable mitigation; blind trust is the most common mistake.

    15. Context engineering. The umbrella discipline (formalized by Anthropic in its 2025 write-up on effective context engineering) of deliberately designing everything that goes into the model’s context — retrieved docs, tools, history, instructions. The 2026 reframing is that “RAG vs long-context vs MCP” is the wrong debate; they’re all tools inside context engineering.

    Tier 4: Production — shipping models safely

    16. Agents. An LLM in a loop that can reason, call tools, observe results, and repeat until a task is done. This is the agency layer — the shift from “answers questions” to “takes actions.” It’s also where the risk jumps, which is why guardrails below matter.

    17. MCP (Model Context Protocol). A standard way to expose typed tools — APIs, databases, systems — that an agent can invoke. If RAG is declarative memory, MCP is agency: the capacity to act. It’s fast becoming the interface layer between agents and your platform; I explained it at three depths in MCP explained in 3 levels.

    18. Evals. Systematic tests that measure whether a model’s output is good enough — accuracy, format, abstention, safety. Evals are to AI what unit and integration tests are to code: without them you’re shipping an untested pipeline and hoping. Critically, a good eval rewards a model for saying “I don’t know” instead of guessing.

    19. Guardrails. The bounds around a model in production — input validation, output filtering, step/recursion limits, human approval for risky actions. They’re the difference between a demo and something you can leave running, and they belong in the release gate, not as an afterthought.

    20. Distillation. Training a smaller, cheaper model to mimic a bigger one, to cut cost and latency once a use case is proven. It’s the last rung of the escalation ladder — reached rarely, and only after cheaper options are exhausted.

    The canonical 2026 sequence. Start at the bottom-left; move up and right only when the cheaper option provably fails — not because the next rung sounds more serious.

    How these fit together

    The concepts aren’t a flat glossary; they stack. Foundations explain why a model behaves like a probabilistic function. The language-model tier explains the levers you actually touch — tokens, context, temperature. Grounding is where you inject your data (RAG), reshape behavior (fine-tuning), and grant agency (MCP). Production is where evals and guardrails keep the whole thing honest. When someone proposes “let’s fine-tune a model on our data so it answers support questions,” you now have the vocabulary to catch the mistake: they want RAG (new facts), not fine-tuning (new behavior), and the actual bottleneck will be data quality — which is where Gartner expected many generative-AI efforts to stall: data problems dressed up as model problems.

    The gotchas nobody warns you about

    Fine-tuning is not how you add knowledge. The most common expensive mistake is fine-tuning to teach facts. Fine-tuning changes behavior; RAG adds knowledge. Reach for RAG first, almost always.

    Tokens are a cost and correctness lever, not trivia. Underestimating token usage blows budgets and, via context degradation, quietly lowers accuracy. Treat token count as a first-class number in any AI pipeline design.

    Your AI project is a data project. The model is rarely the bottleneck — retrieval quality, freshness, lineage, and governance are. If your data platform is shaky, no model choice will save the feature.

    “Agent” is often overkill. A single prompt or a RAG call solves many problems a full agentic loop is proposed for. Agents add capability and cost and risk; use them when the task genuinely needs multi-step tool use.

    No evals means no idea. Without evaluation you cannot tell whether a model change helped or hurt. Ship evals with the feature, and make abstention (“I don’t know”) a passing answer, not a failing one.

    The one principle

    Every one of these twenty concepts is, for a data engineer, a variation on problems you already know — indexing, cost, testing, data quality, and access — wearing new vocabulary. You don’t need to become an ML researcher to build serious AI systems. You need to map the new terms onto the engineering instincts you already have, know which layer a given concept lives in, and remember that in production the model is almost never the hard part — your data is.


    Related reading: MCP explained in 3 levels · Build RAG on your own data · Why bigger models still get it wrong · Why LLMs give different answers · It’s not AI — it’s automation

  • Why Larger LLMs Give Incorrect Answers in Production

    Why Larger LLMs Give Incorrect Answers in Production

    A team I worked with upgraded their support-ticket triage pipeline to a bigger, newer model expecting fewer mistakes. Benchmarks said it was smarter across the board. Two weeks in, the error rate on a specific ticket category had gotten worse, not better — and worse in a particular way: the new model was wrong just as often as the old one, but its wrong answers now came with confident, detailed justifications instead of the old model’s hedgy “I’m not entirely sure, but…” The support team started trusting the wrong answers more, because they sounded more certain. Nobody had budgeted for that.

    That’s the part the “AI hallucinates” conversation usually skips. Bigger models are not simply less wrong — in several measured respects they’re differently wrong, and the difference that matters most in production is confidence, not accuracy. There’s real, recent research behind this, and it points at three separate mechanisms: how models are trained to answer instead of abstain, how their reliability quietly degrades as the input grows even inside benchmarks-passing context windows, and how production conditions differ from the clean single-turn evals every leaderboard measures. None of that means larger models are worse. It means “larger” doesn’t buy you the thing production actually needs, which is knowing when to say “I don’t know.”

    TL;DR

    • → OpenAI’s 2025 research reframes hallucination as an incentive problem: next-token training and standard evals reward confident guessing over calibrated “I don’t know,” so models learn to bluff.
    • → Pretrained models are reasonably well-calibrated; RLHF alignment measurably degrades that calibration, making models more confident without making them more correct — an effect researchers call the alignment tax.
    • → Scaling model size doesn’t eliminate this; multiple studies describe larger models producing “confident nonsense” at a similar or greater rate, just more fluently.
    • → Chroma’s 2025 “Context Rot” study tested 18 frontier models and found every one degrades as input length grows — well before the context window is full, even on simple tasks.
    • → Distractors (content that’s topically close but doesn’t answer the question) hurt accuracy more than irrelevant filler, and the effect compounds as input grows.
    • → In Chroma’s tests, Claude models abstained more under ambiguity while GPT models more often produced confident, incorrect answers — model families differ meaningfully in this failure mode.
    • → Production adds failure modes benchmarks don’t test: retrieval quality in RAG, long accumulated agent context, and non-deterministic sampling — so a benchmark-topping model can still underperform in your actual pipeline.

    The training incentive: models are rewarded for guessing

    Start with the most fundamental mechanism, because it explains why this doesn’t go away as models get bigger. OpenAI’s 2025 paper, Why Language Models Hallucinate, argues that the standard training and evaluation setup structurally rewards confident guessing over calibrated uncertainty. A model is trained to predict the next token, and it’s evaluated on benchmarks that score right-or-wrong with no separate credit for a correct “I don’t know.” A model that always guesses will, on average, score higher than one that abstains when unsure — even if the abstaining model is more trustworthy. The behavior isn’t a bug that better data fixes; it’s the predictable output of an incentive structure, and scaling the model up scales the same incentive with it.

    This gets compounded at the alignment stage. Research tracing model calibration through the training pipeline — from Kadavath et al.’s foundational 2022 work through more recent studies on the “alignment tax” — has found that base, pretrained models are reasonably well-calibrated: when a pretrained model says it’s 80% confident, it’s right roughly 80% of the time. RLHF, the fine-tuning stage that makes a model helpful and fluent, measurably degrades that calibration. The model comes out more confident and more polished, but not more accurate — confidence and correctness get pulled apart at exactly the stage designed to make the model pleasant to use.

    Each stage of the standard training pipeline independently pushes toward confident answers. None of them individually looks like a bug — the compounding is the problem.

    Scaling doesn’t fix this because scaling doesn’t change the incentive. A survey published in Frontiers in AI in 2025 makes the point directly: larger models remain capable of “confident nonsense,” and model scaling alone amplifies rather than eliminates hallucination in certain contexts. A bigger model has more capacity to construct a fluent, internally consistent, wrong answer — which is a worse failure mode for a downstream system to catch than an obviously garbled one.

    Context rot: accuracy degrades long before the window fills

    The second mechanism is specific to production because it’s about what happens once you start feeding a model real, long inputs — RAG context, chat history, tool outputs, agent state — rather than the short, clean prompts most benchmarks use. A 2025 Chroma technical report, deliberately titled Context Rot, tested 18 frontier models including GPT-4.1, Claude 4, Gemini 2.5, and Qwen3, and found that every one of them degrades as input length increases — often well before the context window is close to full, and even on tasks as simple as finding one fact in a document or exactly repeating a string back.

    Chroma’s 2025 study found this pattern held across all 18 models tested — accuracy declines with input length even when the task itself doesn’t get harder.

    Three details from that report matter for anyone building production RAG or agent systems. First, ambiguity compounds the effect: when the needed fact doesn’t closely match the wording of the question — the realistic case, since users rarely phrase things the way a document does — performance degrades faster as input grows. Second, distractors hurt more than plain irrelevant filler; content that’s topically related but doesn’t actually answer the question pulls accuracy down non-uniformly, and the effect gets worse, not better, as more distractors are added. Third — and this is the one worth sitting with — the researchers found that structurally coherent context (well-organized, logically flowing text) degraded model performance more than shuffled, incoherent text did. That’s the opposite of the intuitive assumption that cleaner input is always easier for a model to use.

    There’s also a genuinely useful, model-specific finding buried in that report: under ambiguity, Claude models more often abstained — explicitly stating that an answer couldn’t be determined from the given context — while GPT models more often produced a confident, incorrect answer instead. That’s not a claim that one vendor is unconditionally more accurate; it’s evidence that how a model handles uncertainty under long, messy context is a real, measurable, model-specific property worth testing before you pick what runs in production, not something you can assume from a benchmark leaderboard alone.

    Why this specifically bites in production and not in your evaluation

    Put those two mechanisms together and the production gap makes sense. Most public benchmarks are short, clean, single-turn, and score right-or-wrong with no credit for calibrated abstention — which is exactly the setup that rewards confident guessing and doesn’t test context rot at all. Production is the opposite on every count: it’s long (RAG context, chat history, tool outputs), ambiguous (real user phrasing rarely matches document wording), and cumulative (an agent’s context grows with every tool call it makes). A model can genuinely top the leaderboard and still be the wrong choice for a pipeline that hands it 40,000 tokens of retrieved documents and expects a single correct fact back.

    This is also where architecture choices you already know matter start to compound the problem instead of solving it. Retrieving too many chunks “to be safe” in a RAG pipeline doesn’t just cost more — per the context rot findings, it actively degrades accuracy, especially when the extra chunks are distractors rather than clean irrelevant filler. An agent that accumulates tool results across a long-running task is accumulating exactly the kind of long, structurally coherent context Chroma found hurts performance most. And a model under uncertainty defaulting to a confident guess rather than an abstention is the last-mile version of the training-incentive problem — it’s not a separate bug, it’s the same one showing up downstream.

    What actually helps

    None of this is a reason to avoid larger models — it’s a reason to design around known failure modes instead of assuming scale solves them. A few things follow directly from the research above. Keep retrieved context tight rather than generous: fewer, more relevant chunks measurably outperform “retrieve broadly and let the model sort it out,” because more chunks means more distractors and distractors compound with length. Test for abstention behavior specifically, not just accuracy — a model that says “I can’t determine this from the given context” on an ambiguous case is behaving correctly even though a naive eval scores it as a miss; the more dangerous system is the one that never abstains. And treat long-running agent context as a liability to actively manage — summarizing or pruning accumulated state periodically rather than letting it grow unbounded, since the Chroma findings suggest that coherent, well-organized accumulated context degrades performance rather than protecting it.

    It’s also worth remembering that non-determinism sits underneath all of this: the same prompt can produce a different answer on a different run, for reasons rooted in how these models sample tokens — I’ve written separately about why LLMs give different answers to the same question, and that variance means a single spot-check of a prompt tells you less than it feels like it does. If you’re using AI to generate or review structured output like SQL, the same discipline applies: never trust a green run as proof of correctness, a point I’ve made about why passing tests still ship bad data. And if agents are reading your pipeline’s metadata to answer questions, the context rot findings are a direct argument for curating what reaches them rather than dumping everything and hoping — the same principle behind giving agents metadata access deliberately rather than broadly.

    The gotchas nobody warns you about

    A benchmark win doesn’t transfer to your pipeline. Public leaderboards are short, clean, single-turn tests. If your production input is long, ambiguous, or accumulated over many turns, the benchmark isn’t measuring the failure mode you’ll actually hit.

    More retrieved context is not a safety margin. The instinct to retrieve generously “in case the model needs it” directly works against the research: more chunks means more distractors, and distractors degrade accuracy faster as volume grows.

    A model that never says “I don’t know” is a red flag, not a feature. If your eval only scores right/wrong, you can’t distinguish a model correctly abstaining from one confidently guessing wrong — and the second one is more dangerous precisely because it’s harder to catch downstream.

    Well-organized context can hurt more than messy context. Counterintuitively, Chroma found coherent, logically flowing input degraded performance more than shuffled text with the same content. Don’t assume tidy prompt construction is automatically safer.

    Model families differ on this specific behavior. Whether a model abstains or guesses under ambiguity is a measurable, model-specific property. If reliability under uncertainty matters for your use case, test for it directly rather than assuming it from general capability scores.

    The one principle

    A larger model gives you more capability, not more honesty about its limits — those are trained in separately, and mostly not trained in at all. The fix isn’t a bigger model or a longer context window; it’s designing the system around the specific, now well-documented ways models fail — tight retrieval instead of generous, explicit testing for abstention, and active management of anything that accumulates context over time. Production doesn’t need a model that’s never wrong. It needs one — and a system around it — that’s honest about when it doesn’t know.


    Related reading: Why LLMs give different answers to the same question · Why passing tests still ship bad data · Giving agents metadata access safely · It’s not AI you should worry about — it’s automation · Chroma: Context Rot research report · Why Language Models Hallucinate (OpenAI, 2025)

  • 7 Steps to Building and Deploying Your First Autonomous Agent

    7 Steps to Building and Deploying Your First Autonomous Agent

    The Slack message came in at 9:14 on a Tuesday: “why is yesterday’s Snowflake bill $4,200 over budget and who approved it.” Nobody had approved anything. A dashboard refresh job had been silently retrying every five minutes since Saturday after a schema change broke one of its filters, and by the time anyone noticed, it had burned three days of a warehouse running at full tilt for no reason. The fix took ten minutes once someone looked. The problem was that someone had to look, and by Tuesday the money was already gone.

    That’s the gap autonomous agents are actually good for closing — not “AI does your job,” but “something is watching at 3 a.m. so a $4,200 mistake gets caught in twenty minutes instead of three days.” And it’s worth being honest about where the industry actually is on this: Gartner expects more than 40% of agentic AI projects to be canceled by the end of 2027, and its stated reasons are almost never about the model being too weak — they’re escalating costs, unclear value, and inadequate risk controls. The pattern I’ve seen up close matches that: teams skip scoping, skip guardrails, and skip deployment, then wonder why the “agent” never left someone’s laptop. This is a practical walkthrough of a small, real agent — one that watches Snowflake spend and flags anomalies — built the way that actually survives contact with production. If you want the higher-level argument for why this kind of role shift is happening at all, I’ve made that case in why automation, not AI, is what’s really changing this job.

    TL;DR

    • → Write down the agent’s one job, what success looks like, and what it’s never allowed to do — before opening an editor. Skipping this is the single biggest cause of stalled agent projects.
    • → LangGraph has become the closest thing to a 2026 production default for stateful agents — multiple independent sources put it at 30–90M+ monthly downloads with Klarna, Uber, and LinkedIn running it live — while Microsoft has moved AutoGen into maintenance mode.
    • → The core of any agent is a loop: the model reasons, calls a tool if needed, reads the result, and repeats — and that loop needs a hard step cap or it can run away and burn your API budget.
    • → Memory (checkpointing) is what lets an agent handle a follow-up like “now show me last week too” without starting from zero.
    • → Guardrails — input validation, a recursion limit, and a bounded retry — are what separate a demo from something safe to leave running unattended.
    • → Deployment is not optional polish: wrapping the agent in a small API and a container is what turns “it worked on my machine” into something a dashboard, a Slack bot, or another service can actually call.

    Step 1: Decide what it does — and what it’s never allowed to do

    Before any code, write three sentences: the one job, what success looks like, and the hard boundary it can’t cross without a human. For a cost-anomaly agent, that’s:

    The job: on a schedule, pull the last 24 hours of Snowflake warehouse spend, compare it against the trailing 14-day baseline, and flag any warehouse running more than 2x its typical cost.
    Success looks like: a short written alert naming the warehouse, the dollar delta, and a plausible cause pulled from the query history — posted to Slack.
    The hard boundary: it can query cost and query-history tables freely, but it never suspends a warehouse, kills a query, or changes a resource monitor without a human approving first.

    That boundary is doing real work. An agent that can only look and report is low-risk to leave running unattended; one that can act on what it finds needs a different level of trust entirely. Skipping this step is exactly the kind of ambiguity Gartner points to when it names unclear scope and inadequate risk controls as the top reasons agentic projects get killed before they ever prove their value.

    Step 2: Pick the framework — and don’t pick AutoGen

    Two choices matter here: which model reasons, and which framework runs the loop of thinking, acting, and checking the result. For the model, any current frontier model with reliable tool-calling works; the code below uses Claude. For the framework, here’s where 2026 has actually settled:

    LangGraph models an agent as nodes and edges in a graph with built-in checkpointing, so a failed step can resume instead of restarting from scratch. Independent industry write-ups through mid-2026 consistently cite it running in production at companies like Klarna, Uber, and LinkedIn, with monthly download figures that vary by source but are unambiguously in the tens of millions. CrewAI gets a prototype running faster — often under 20 lines — and has real traction of its own, but teams commonly outgrow its coordination model once a workflow gets non-trivial and migrate to LangGraph. AutoGen, once a default for multi-agent conversation patterns, is worth naming only as a warning: Microsoft has shifted it into maintenance mode in favor of a unified Microsoft Agent Framework, so it’s not where you want to start something new.

    For a cost-anomaly agent that runs unattended on a schedule, the checkpointing is the deciding factor — a Snowflake query that times out shouldn’t mean the whole run starts over — so this build uses LangGraph.

    Step 3: Set up the project

    # Create and enter the project folder
    mkdir cost-anomaly-agent && cd cost-anomaly-agent
    
    # Isolate dependencies in a virtual environment
    python3 -m venv venv
    source venv/bin/activate   # Windows: venv\Scripts\activate
    
    # langgraph        - orchestrates the reasoning loop
    # langchain-anthropic - connects LangGraph to Claude
    # snowflake-connector-python - lets the agent query Snowflake directly
    # python-dotenv    - loads credentials from .env, never hardcoded
    pip install langgraph langchain-anthropic snowflake-connector-python python-dotenv

    Create a .env file for credentials, and make sure it’s in .gitignore alongside venv/ before you write a single line of agent logic:

    # .env — never commit this file
    ANTHROPIC_API_KEY=your-anthropic-key-here
    SNOWFLAKE_ACCOUNT=your-account-locator
    SNOWFLAKE_USER=your-service-user
    SNOWFLAKE_PASSWORD=your-password
    SLACK_WEBHOOK_URL=your-slack-webhook

    Keep agent.py (the agent logic) separate from app.py (the API wrapper), the same separation of concerns that makes any pipeline easier to reason about — the same instinct behind structuring a dbt project into clean layers.

    Step 4: Build the core reasoning loop

    This is the heart of it: the model reads the task, decides whether it needs a tool, calls it, reads the result, and decides what to do next.

    The loop that powers every tool-using agent. Without a hard cap on step count, a stuck tool call can spin this loop indefinitely — and each spin is a billed model call.

    # agent.py
    from dotenv import load_dotenv
    from langchain_anthropic import ChatAnthropic
    from langchain_core.tools import tool
    from langgraph.prebuilt import create_react_agent
    import snowflake.connector
    import os
    
    load_dotenv()
    
    # Low temperature: we want a consistent read of the numbers, not creative variation
    model = ChatAnthropic(model="claude-sonnet-4-6", temperature=0.1, max_tokens=1200)
    
    @tool
    def get_warehouse_spend(lookback_days: int = 14) -> str:
        """Returns daily credit usage per warehouse for the given lookback window,
        most recent day first. Use this to compare yesterday against the baseline."""
        conn = snowflake.connector.connect(
            account=os.environ["SNOWFLAKE_ACCOUNT"],
            user=os.environ["SNOWFLAKE_USER"],
            password=os.environ["SNOWFLAKE_PASSWORD"],
        )
        cur = conn.cursor()
        cur.execute("""
            SELECT warehouse_name, start_time::date AS usage_date,
                   SUM(credits_used) AS credits
            FROM snowflake.account_usage.warehouse_metering_history
            WHERE start_time >= DATEADD('day', -%s, CURRENT_DATE())
            GROUP BY warehouse_name, usage_date
            ORDER BY usage_date DESC
        """, (lookback_days,))
        rows = cur.fetchall()
        conn.close()
        return "\n".join(f"{r[0]}, {r[1]}, {r[2]:.2f} credits" for r in rows)
    
    agent = create_react_agent(model, tools=[get_warehouse_spend])
    
    def run_triage() -> str:
        """Asks the agent to review recent spend and flag anomalies."""
        result = agent.invoke({
            "messages": [
                ("system",
                 "You monitor Snowflake warehouse spend. Compare yesterday's "
                 "credit usage per warehouse against its trailing 14-day average. "
                 "Flag any warehouse running more than 2x its baseline. For each "
                 "flag, state the warehouse, the percentage over baseline, and "
                 "the dollar impact at $3/credit. If nothing is anomalous, say so."),
                ("user", "Review the last 24 hours of warehouse spend."),
            ]
        })
        return result["messages"][-1].content
    
    if __name__ == "__main__":
        print(run_triage())

    What’s doing the real work here: get_warehouse_spend is a plain Python function turned into a callable tool by the @tool decorator — and its docstring isn’t documentation, it’s the description the model reads to decide when to call it. create_react_agent is LangGraph’s shortcut for the classic reason-act-observe loop (ReAct) without hand-writing graph nodes. The system prompt is what turns a generic tool-calling agent into a specific one: it names the exact comparison, the exact threshold, and the exact output shape, which is the difference between a useful alert and a vague paragraph.

    Step 5: Add memory and a second tool

    Right now the agent forgets everything between runs, which is fine for a scheduled job but breaks the moment someone asks a natural follow-up like “what caused that spike on the ETL_WH warehouse?” Add a second tool that pulls query history, and LangGraph’s built-in checkpointer to persist state across a conversation thread:

    # agent.py (additions)
    from langgraph.checkpoint.memory import MemorySaver
    
    @tool
    def get_top_queries(warehouse_name: str, hours: int = 24) -> str:
        """Returns the most expensive queries run on a specific warehouse
        in the given window, to help explain a cost spike."""
        conn = snowflake.connector.connect(
            account=os.environ["SNOWFLAKE_ACCOUNT"],
            user=os.environ["SNOWFLAKE_USER"],
            password=os.environ["SNOWFLAKE_PASSWORD"],
        )
        cur = conn.cursor()
        cur.execute("""
            SELECT query_text, user_name, total_elapsed_time / 1000 AS seconds
            FROM snowflake.account_usage.query_history
            WHERE warehouse_name = %s
              AND start_time >= DATEADD('hour', -%s, CURRENT_TIMESTAMP())
            ORDER BY total_elapsed_time DESC
            LIMIT 5
        """, (warehouse_name, hours))
        rows = cur.fetchall()
        conn.close()
        return "\n".join(f"{r[1]}: {r[0][:80]}... ({r[2]:.0f}s)" for r in rows)
    
    memory = MemorySaver()
    agent = create_react_agent(
        model,
        tools=[get_warehouse_spend, get_top_queries],
        checkpointer=memory,
    )
    
    def run_triage(thread_id: str = "daily-triage") -> str:
        config = {"configurable": {"thread_id": thread_id}}
        result = agent.invoke(
            {"messages": [("user", "Review the last 24 hours of warehouse spend.")]},
            config=config,
        )
        return result["messages"][-1].content

    MemorySaver is what lets a follow-up question in the same thread_id reuse everything the agent already found, instead of re-querying from zero — the same reason warehouse result caching saves you from redoing work that hasn’t changed.

    Step 6: Guardrails — the step most tutorials skip

    An agent that only reads cost data is low risk. But even a read-only agent needs bounds around runaway loops and bad state, and this is exactly the gap Gartner’s cancellation numbers point back to — not model quality, but the absence of operational discipline around it.

    # agent.py (guardrails)
    import time
    
    MAX_RETRIES = 2
    RECURSION_LIMIT = 12   # caps reasoning/tool-call steps in a single run
    
    def run_triage_safely(thread_id: str = "daily-triage") -> str:
        config = {
            "configurable": {"thread_id": thread_id},
            "recursion_limit": RECURSION_LIMIT,
        }
        for attempt in range(1, MAX_RETRIES + 1):
            try:
                result = agent.invoke(
                    {"messages": [("user", "Review the last 24 hours of warehouse spend.")]},
                    config=config,
                )
                return result["messages"][-1].content
            except Exception as e:
                if attempt == MAX_RETRIES:
                    return f"Triage failed after {MAX_RETRIES} attempts: {e}"
                time.sleep(3)

    recursion_limit is the single most important line in this block. Without it, a Snowflake connection hiccup or a confusing result can send the agent into extra reasoning steps that quietly rack up model calls — the agentic equivalent of the retrying dashboard job that started this article. The bounded retry handles the ordinary case of a transient network blip without masking a real failure.

    Step 7: Ship it somewhere real

    A script that runs when you remember to run it isn’t monitoring anything. Wrap it in a small API and a scheduled container so it runs whether or not you’re watching.

    Guardrails make the agent safe to run unattended; the container and API are what let something else — a scheduler, a Slack bot, a dashboard — actually trigger it.

    # app.py
    from fastapi import FastAPI
    from agent import run_triage_safely
    
    app = FastAPI(title="Cost Anomaly Agent")
    
    @app.get("/health")
    def health():
        return {"status": "ok"}
    
    @app.post("/triage")
    def triage():
        return {"report": run_triage_safely()}
    # Dockerfile
    FROM python:3.11-slim
    WORKDIR /app
    COPY requirements.txt .
    RUN pip install --no-cache-dir -r requirements.txt
    COPY . .
    EXPOSE 8000
    CMD ["sh", "-c", "uvicorn app:app --host 0.0.0.0 --port ${PORT:-8000}"]

    Push it to a container host, point a scheduler (a cron trigger, an Airflow task, or the host’s own scheduled jobs) at POST /triage every morning, and pipe the response into Slack. If you’re already orchestrating pipelines, wiring this into the same system you use for everything else is straightforward — the pattern is no different from triggering any other scheduled job against Snowflake. And because this agent only reads account usage data, giving it credentials safely is worth doing properly — see the broader pattern in giving agents metadata access without opening security holes.

    The gotchas nobody warns you about

    A read-only agent still needs a recursion limit. “It can’t do damage, it only reads” is not the same as “it can’t run forever.” Every reasoning step is a billed model call.

    The system prompt is the actual product. The framework, the tools, and the code are plumbing. The threshold, the comparison window, and the exact output format live in the prompt — get that vague and the agent produces vague alerts no matter how solid the code is.

    Account usage views lag. Snowflake’s ACCOUNT_USAGE schema can trail real-time by up to a few hours. An agent triaging “the last hour” against that view will occasionally miss the very spike it was built to catch — know the latency of your data source before you trust the silence.

    A demo that works once is not a deployed agent. The gap between “it worked in my terminal” and “it’s live and something else can call it” is exactly steps 6 and 7 — and it’s the gap most abandoned agent projects never cross.

    Framework churn is real; the concepts aren’t. AutoGen’s shift to maintenance mode is a reminder that frameworks move fast. The scoping, loop, memory, and guardrail concepts in this article transfer to whatever framework wins next.

    The one principle

    An autonomous agent is not a smarter script — it’s a script with a loop, a memory, and a leash, and the leash is what makes it safe to leave running. The model reasoning is the easy 20%; the scoping, the guardrails, and the deployment are the 80% that decide whether this becomes something that catches a $4,200 mistake at 3 a.m., or one more repo nobody ever pushed past a terminal window.


    Related reading: It’s not AI you should worry about — it’s automation · Giving agents metadata access safely · Tools vs subagents: don’t over-build · MCP explained in 3 levels · Gartner: 40% of agentic AI projects canceled by 2027 · LangGraph production case studies

  • The Future of Data Engineering in an AI-Driven World

    The Future of Data Engineering in an AI-Driven World

    Two facts from 2026 sit uncomfortably next to each other. Databricks has said that most new databases created on its platform are now spun up by AI agents rather than humans. And in the same year, one of the more sober industry write-ups pointed out that actual adoption of agentic data engineering — agents autonomously building and running production pipelines — is still very low, and that most companies are still fighting to get basic BI right, never mind autonomous AI. Both are true. The future of data engineering is being wildly oversold and quietly underbuilt at the same time.

    That gap is where the honest version of this conversation lives. If you strip out the LinkedIn futurism in both directions — “data engineering is dead” and “agents will build everything by Christmas” — what’s left is a real, structural shift in what the job is. It’s not shrinking. It’s moving. I’ve argued the seed of this before in the piece on how automation, not AI, is the thing actually reshaping the work; this article is the longer look at where that leads, grounded in what’s shipping today rather than what a keynote promised.

    TL;DR

    • → Data engineering isn’t being automated away; the role is moving up the stack from writing pipelines to governing the systems that write them.
    • → AI reliably absorbs the typing — drafting SQL, tests, boilerplate, and docs — while judgment, correctness, context, and governance stay human.
    • → Your pipelines now have a new consumer: AI agents, which need machine-readable context (semantic layers, metadata, contracts) and don’t file a ticket when data is wrong.
    • → Because AI consumes data at scale, “close enough” data quality is now actively dangerous, pushing data contracts and testing from conference talk into real adoption.
    • → The hype is ahead of reality: enterprise text-to-SQL still lands far below its demos, agentic-DE adoption is low, and batch pipelines are not going anywhere soon.
    • → The durable skills are the un-automatable ones — deciding what’s correct, modeling the domain, and owning the trade-offs an agent can’t be accountable for.

    The prediction everyone gets wrong

    The loud prediction is that AI will automate data engineering out of existence. The evidence points the other way: the role is getting harder and more strategic, not easier and more automated. As AI systems become the biggest consumers of data, someone has to build the reliable pipelines, trustworthy metadata, and governance those systems depend on — and that someone is a data engineer whose remit just expanded. The typing gets automated; the accountability does not.

    It helps to be precise about which parts actually move. AI is genuinely good at producing a first draft of the mechanical work. It is not good at knowing whether that draft is right for your business, and it cannot be held responsible when it isn’t.

    The line isn’t “simple vs hard” — it’s “producible vs accountable.” AI drafts; humans own the parts someone has to answer for.

    This is why “learn to prompt” is shallow career advice. Prompting is a skill with a short half-life. The right-hand column of that diagram is where a career compounds — and notably, it’s the same reason SQL became more valuable in the AI era, not less: reading generated SQL critically is now the job, and you can’t review what you don’t deeply understand.

    Your pipeline has a new consumer

    For a decade the mental model was simple: pipelines end at a human. Someone writes a query, reads a dashboard, interprets the result. That assumption is breaking. A growing share of your data’s consumers are now AI agents — RAG systems, autonomous workflows, coding agents querying the warehouse — and they behave nothing like the analyst you designed for.

    Agents are a new class of consumer: they need machine-readable context and are unforgiving of ambiguity — and they never file a Jira ticket when something’s off.

    A human analyst can look at a slightly mislabeled column and infer what it means. An agent can’t — it needs explicit context: a semantic layer that defines metrics, metadata that describes lineage and freshness, and contracts that guarantee shape. This is why the unglamorous work of curating context is becoming central, and why standards for feeding that context to agents matter. If you’re wiring agents into your platform, understanding the Model Context Protocol as the interface agents actually use is quickly moving from optional to core, and doing it without opening security holes is its own discipline.

    Why “close enough” just died

    When a human was the last mile, a slightly wrong number got caught by someone who knew the business. When an agent is the last mile, a wrong number propagates — into a generated report, an automated decision, a customer-facing answer — with no one in the loop to sanity-check it. AI consumption raises the cost of bad data by removing the human circuit-breaker.

    That’s the real reason the “shift left” movement — data contracts, automated testing, CI/CD for pipelines — has finally moved from conference slideware into genuine enterprise adoption. It’s no longer a nice-to-have; it’s the thing standing between you and an agent confidently acting on garbage. But adoption alone isn’t a fix: I’ve written about how tests can pass while you still ship bad data, and that failure mode gets more dangerous, not less, when the consumer is an agent. The same goes for schema stability — an agent has no instinct that a renamed column means the data changed; it just produces confident nonsense.

    The new job: from builder to conductor

    Put those threads together and the shape of the role emerges. Less hand-writing every transform; more designing systems that agents can operate safely and that other systems can trust. The day-to-day tilts toward orchestrating AI coding agents, curating the context they run on, enforcing governance, and owning cost — the platform coding agents that run inside the security perimeter, like Snowflake’s Cortex Code and its Databricks equivalents, are already normalizing this. Governing what those agents are allowed to do is fast becoming a core responsibility, which is exactly why securing agent workflows in production is now a data-engineering problem, not just a security one.

    It also raises the bar on restraint. The temptation in an agentic world is to build elaborate multi-agent contraptions for problems that don’t need them; the discipline of knowing when a tool beats a subagent is part of the new craft. And the open-format shift — Iceberg becoming the default table format across platforms — is part of the same story: agents and multiple engines all need to read the same data, which pushes architecture toward open, engine-neutral storage.

    The honest part: what’s overhyped

    A future-of piece that only sells the future is marketing. Here’s the counterweight. Enterprise text-to-SQL, the headline “anyone can query in English” promise, still performs far below its demos — the best systems on the public BIRD-SQL benchmark reach the low 80s in execution accuracy on research data and only with hand-fed hints, and drop sharply on realistic enterprise schemas. Agentic data engineering adoption remains low outside a handful of sophisticated teams. Batch processing isn’t dying on the timeline the streaming evangelists claim; event-driven architectures are still a small slice of real deployments. And as Joe Reis keeps reminding the field, most organizations are still struggling with fundamentals — the vanilla work of ETL, warehousing, and reliable batch is still the majority of the job. The forward-looking reference worth reading here is Datafold’s 2026 predictions, which is candid that the gap between capability and adoption is large.

    The numbers behind the shift

    The market signal is mixed in a way that rewards the well-positioned. Reported data and analytics job postings softened through late 2025 even as overall tech hiring cooled, so raw volume isn’t booming. But compensation held up and trended higher — median data-engineer pay sits in the low-to-mid $130Ks, with senior roles in major hubs clearing $180K–$220K and Big Tech totals well beyond. Surveys of practitioners in early 2026 found AI tooling already table stakes, with a large majority using it daily. Read together: fewer easy junior seats, more demand for engineers who can do the up-the-stack work, and a widening pay gap between those who can and those who can’t. The floor rose and the ceiling rose with it.

    The gotchas nobody warns you about

    Automating a broken process just breaks it faster. Pointing agents at a pipeline with no contracts, no tests, and no lineage doesn’t modernize it — it industrializes the mess. Fix the foundations before you add autonomy.

    “The agent did it” is not an accountability model. When an autonomous workflow ships a wrong number, the org still needs a human who owns the outcome. Design for a human accountable owner, not just a human in the loop.

    Context debt is the new tech debt. Undocumented tables and undefined metrics were survivable when humans filled the gaps. Agents can’t, so the cost of missing semantic context is now paid in wrong answers at scale.

    Chasing every trend is its own failure mode. Streaming, multi-agent systems, and open formats each solve real problems — and each is over-applied. Adopt them where the use case demands it, not because a vendor slide said 2026 requires it.

    The junior pipeline is at risk, and that’s a team problem. If AI absorbs the entry-level tasks people used to learn on, teams that don’t deliberately train juniors will find they have no seniors in five years.

    The one principle

    In an AI-driven world, data engineering stops being about producing pipelines and becomes about being accountable for systems — the correctness, context, and governance that AI can consume but cannot own. The engineers who thrive won’t be the ones who typed the most SQL or prompted the most cleverly. They’ll be the ones who understood their data and their business well enough to decide what’s true — and to stand behind it when an agent, a dashboard, and a CFO are all asking at once. That job isn’t going anywhere. It’s just getting more serious.


    Related reading: It’s not AI you should worry about — it’s automation · MCP: the interface agents use · Governing AI agents in production · Why passing tests still ship bad data · BIRD-SQL benchmark · Datafold: data engineering in 2026

  • What Happens When You Give Your Local Agent a Real Memory

    What Happens When You Give Your Local Agent a Real Memory

    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.

    ApproachContext sent per new taskRelative cost per task
    Full transcript replayGrows unbounded; truncated once it exceeds the model’s context windowIncreases every session, then degrades silently
    Retrieval, top 3 notes~150–300 tokens, regardless of history lengthFlat, independent of how long the agent has been running

    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

  • AI Agent Tool Design: What Works and What Doesn’t

    AI Agent Tool Design: What Works and What Doesn’t

    The incident report blamed the model. “The agent called the delete endpoint twice and wiped a partition it shouldn’t have touched.” Everyone nodded, someone filed a ticket to “upgrade to a smarter model,” and the actual cause sat there in plain sight: the delete tool had no idempotency key, no confirmation gate, and a description that said only “deletes records.” The model did exactly what the interface let it do. A better model would have done the same thing faster.

    This is the pattern almost nobody names correctly. Most agent failures look like model mistakes — wrong tool, bad arguments, mishandled errors — but the model is only ever reasoning from the interface you gave it: the tool name, its description, the parameter schema, and the parameter descriptions. When that interface is vague, loosely typed, or missing its guardrails, failures stop being accidents and become predictable. You can throw a stronger model at a bad tool surface and it will still fail, just with more confidence. This is the field guide to designing the tool surface itself — five patterns that work, five that break under real workloads, each paired with its opposite so you can see why it fails, not just what to replace it with.

    If you’re still deciding whether a given capability should even be a tool versus a full subagent, start with our companion piece on tools vs subagents — this article assumes you’ve decided it’s a tool and focuses on designing that tool well.

    TL;DR

    → Most agent failures are tool-design failures, not model failures. The model reasons only from the interface: name, description, schema, parameter docs. Fix the interface and the “model mistakes” largely disappear.

    → One tool, one responsibility. A tool that switches behavior on an action parameter forces the model to pick a mode before it can solve the task. Split it into single-purpose tools with unambiguous names.

    → Tight schemas make invalid states impossible. Enums, validators, and typed fields encode constraints so the model doesn’t guess. Validation fails at the tool boundary instead of as a cryptic downstream error.

    → Descriptions define scope, not just purpose — they say when to use the tool and when not to. Without the “do NOT use this for…” boundary, the model infers scope from the name and picks wrong at scale.

    → Structured error returns (error_coderecoverablesuggested_action) give the model something to branch on. A raw stack trace gives it noise to hallucinate against.

    → The failure modes that pass demos and break in production: thin wrappers over raw APIs, loading every tool into every context, silent partial success, overlapping tool names, and single-call destructive actions.

    Why tool design — not model capability — is the root cause

    A model can only reason from what the tool interface exposes. That’s the entire premise, and it’s worth sitting with because it inverts how most teams debug. When an agent picks the wrong tool, the instinct is to blame the model’s judgment. But the model’s judgment is a function of the tool name, the description, the parameter schema, and the parameter descriptions — nothing else. If two tools have near-identical descriptions, the model isn’t being dumb when it confuses them; it’s being given no basis to tell them apart.

    Anthropic’s engineering team makes this point directly in their guide on writing effective tools for agents: the tool interface is model-facing documentation, and its clarity determines the agent’s reliability more than raw model horsepower does. Stronger models reduce some mistakes, but they cannot reliably compensate for a flawed interface. That framing matters for data teams especially, because the tools we hand agents — warehouse queries, pipeline triggers, catalog lookups — often started life as internal APIs never designed for a reasoning model to consume.

    What works, pattern by pattern

    1. One tool, one responsibility

    Diagram comparing avoidable multi-action tools vs. preferred single-action tools for customer management, showing individual actions as clearer and more responsible than combining them into one tool.

    A tool that multiplexes behavior through an action parameter makes the model choose a mode before it can act. Single-purpose tools remove that whole layer of ambiguity.

    A tool should represent a single, clear operation. When one tool handles create, get, update, delete, and suspend through an action parameter, the model has to figure out which mode to invoke before it can reason about the actual task. That’s a second decision you’ve forced into every call.

    # Avoid: action-based multi-behavior tool
    @tool
    def manage_customer(action: str, customer_id: str | None = None,
                        data: dict | None = None):
        """action: create | get | update | delete | suspend"""
        ...
    
    # Prefer: single-responsibility tools
    @tool
    def create_customer(data: CustomerInput) -> Customer:
        """Create a new customer record."""
        ...
    
    @tool
    def suspend_customer(customer_id: str, reason: str) -> SuspensionResult:
        """Suspend a customer account."""
        ...

    Single-responsibility tools give the model an unambiguous function and give you cleaner error handling and easier observability — the same reasoning behind the audit-per-operation approach in our ETL audit logger guide, where one clear operation per unit makes debugging tractable. One caveat: this is a strong default, not a universal law. Some domains — shell, filesystem, browser, calendar — legitimately benefit from a constrained multi-action interface because the action space is part of the abstraction itself.

    2. Schemas that make invalid states impossible

    The model constructs tool-call arguments by reasoning from your schema. A loose schema means it guesses at constraints; a tight schema encodes them so no guessing is required. This is where Pydantic models with enums and field validators earn their keep:

    from pydantic import BaseModel, Field
    from enum import Enum
    
    class Priority(str, Enum):
        LOW = "low"
        MEDIUM = "medium"
        HIGH = "high"
    
    class CreateTaskInput(BaseModel):
        title: str = Field(
            description="Short, actionable title. Imperative: 'Review PR', not 'PR Review'.",
            min_length=5, max_length=100)
        priority: Priority = Field(
            description="Use HIGH only for blockers affecting other work.",
            default=Priority.MEDIUM)
        due_date: str = Field(
            description="ISO 8601 date: YYYY-MM-DD. Must be a future date.",
            pattern=r"^\d{4}-\d{2}-\d{2}$")

    Enums are especially valuable for small sets of valid values — they eliminate an entire class of plausible-but-invalid outputs. And validation failures surface right at the tool boundary rather than as a confusing error three steps downstream in your pipeline.

    3. Descriptions that define scope, not just purpose

    Tool descriptions are model-facing documentation, and they need to do two things: explain when to use the tool and when not to. Most descriptions only do the first, which leaves the model inferring scope from the tool name — a reliable source of selection errors at scale.

    # Weak: says what it does, not when NOT to use it
    """Search for documents in the knowledge base."""
    
    # Strong: purpose, scope, and boundaries
    """
    Search the internal knowledge base for policies and reference material.
    Use when the user asks about company procedures, product specs, or documented workflows.
    Do NOT use for real-time data (prices, availability, current status) — use get_live_data().
    Returns up to 5 results ranked by relevance. No results means it's not in the knowledge base.

    The “do NOT use this for…” line is the one most teams omit and the one that most improves selection accuracy. A good tool definition draws its boundaries relative to the outcome, not relative to other tools — which is also the cleanest way to avoid the overlap problem covered below.

    4. Structured, actionable error returns

    When a tool fails, the model reads the error and decides what to do next. An unhandled exception produces noise-driven behavior; a structured error gives the model something concrete to branch on:

    class ToolError(BaseModel):
        error_code: str        # machine-readable, for the model to branch on
        message: str           # human-readable description
        recoverable: bool      # can the agent retry?
        suggested_action: str  # what the agent should do next
    
    return ToolError(
        error_code="RECORD_NOT_FOUND",
        message="No user record found with ID 'usr_123'.",
        recoverable=True,
        suggested_action="Call list_users() to get valid IDs before retrying.")

    The recoverable flag and suggested_action field are what actually change agent behavior. Without them, models retry non-retryable errors — burning tokens and warehouse credits — or abandon recoverable ones. This matters doubly when the tool touches sensitive systems; see our guide on giving agents access to pipeline metadata safely for how structured returns keep a compromised agent’s blast radius small.

    5. Idempotent state-changing operations

    Every tool that mutates state — creates a record, sends a message, triggers a pipeline run — must be safe to call twice, because agents retry, networks fail, and the reasoning loop may fire a second call when confirmation of the first never arrived. The simplest defense is an idempotency key on every write:

    @tool
    def send_email(to: str, subject: str, body: str,
        idempotency_key: str = Field(
            description="Unique key for this send. Hash of recipient+subject+timestamp. "
                        "Same key on retry returns the original result without re-sending.")
    ) -> dict:
        """Send an email. Idempotent: same key will not trigger a second send."""
        existing = idempotency_store.get(idempotency_key)
        if existing:
            return existing
        result = email_service.send(to=to, subject=subject, body=body)
        idempotency_store.set(idempotency_key, result, ttl=86400)
        return result

    Without idempotency, a transient failure quietly becomes a duplicate action — a doubled Slack alert, a re-run backfill, a second funds transfer.

    What doesn’t work

    1. Thin wrappers around unfiltered APIs

    Pointing an agent at a REST API and exposing it raw is the most common shortcut and the most common source of production failures. As Anthropic’s tool-writing guide notes, APIs built for developers expose far more than an agent needs: responses packed with hundreds of fields, pagination, opaque internal IDs, and error codes that require domain knowledge to interpret. A purpose-built wrapper handles pagination internally, projects only the fields the agent needs, and maps API errors to the structured ToolError format above. The flip side: over-wrapping into dozens of hyper-narrow tools fragments the surface. The goal is a consistent, agent-friendly abstraction — not maximal abstraction.

    2. Loading all tools into every context

    Diagram comparing two tool-loading approaches: left panel shows “All tools, every call” with many tool names; right panel shows “Only the current step’s tools” with fewer, step-specific tools. Accuracy drops as more tools are loaded.

    Agent accuracy drops as the tool catalog grows. Loading only the tools relevant to the current step keeps the decision space small and the token budget lean.

    Accuracy degrades as the tool catalog grows. LongFuncEval, a 2025 study on tool-calling across long contexts, found performance drops substantially as the tool catalog grows — even in models with 128K context windows. Loading every tool into every system prompt compounds it by eating token budget before any task content is processed. The fix is dynamic loading: determine which tools are relevant to the current step and include only those.

    STEP_TOOL_MAP = {
        "research": ["search_documents", "search_web", "get_url_content"],
        "write":    ["create_document", "update_document", "format_text"],
        "send":     ["send_email", "post_to_slack", "create_calendar_event"],
    }
    
    def get_tools_for_step(step_type: str, available_tools: list) -> list:
        relevant = STEP_TOOL_MAP.get(step_type, [])
        return [t for t in available_tools if t.name in relevant]

    This is the tool-level analogue of scoping a subagent’s tools, one of the justifications for reaching for a subagent at all in our tools vs subagents guide.

    3. Silent partial success

    Partial success becomes a bug when a tool completes only part of the work but returns something that looks fully successful, so the agent proceeds with a misleading view of system state. It usually happens when a tool swallows internal failures:

    # Silently misleads the agent
    @tool
    def bulk_create_tasks(tasks: list) -> dict:
        created = []
        for task in tasks:
            try:
                created.append(task_api.create(task).id)
            except Exception:
                pass  # silent failure: this is the bug
        return {"created": created}
    
    # Makes partial success explicit
    @tool
    def bulk_create_tasks(tasks: list) -> BulkCreateResult:
        created, failed = [], []
        for task in tasks:
            try:
                created.append(task_api.create(task).id)
            except TaskCreationError as e:
                failed.append({"input": task.title, "reason": str(e)})
        return BulkCreateResult(
            created_ids=created, failed_items=failed,
            success=len(failed) == 0,
            partial_success=len(created) > 0 and len(failed) > 0)

    The partial_success flag gives the model a branch: retry the failed items, surface the partial result, or halt. Silent swallowing gives it a false green light.

    4. Overlapping tool names and descriptions

    When two tools do similar things, the model reasons about which to use on every single call — burning tokens and introducing errors. Classic offenders: search_documents and find_documents with identical purpose; get_user and fetch_user_profile with unclear difference; create_taskadd_task, and new_task for one operation. Renaming alone isn’t the fix. Every tool needs a purpose describable without reference to the others — if a description needs “unlike X, this one…” to make sense, that’s a design problem. This is the same governance discipline we apply in governing agentic workflows: a tool surface audited before deployment, not after an incident.

    5. Destructive actions without a confirmation gate

    A diagram compares single-step deletion, which is irreversible, with a two-step staged deletion using a token and user confirmation to prevent immediate destructive actions.

    An irreversible action should never be completable in one reasoning step. Staging plus a short-lived confirmation token forces a deliberate second call.

    Any tool that takes an irreversible action — deleting records, messaging real users, executing transactions — needs a structural two-step confirmation, not an in-prompt “are you sure?” Separate staging from execution and require a short-lived token between them:

    @tool
    def stage_deletion(record_ids: list[str], reason: str) -> StagedDeletion:
        """Stage records for deletion. Does NOT delete anything.
        Returns a confirmation token that expires in 60 seconds."""
        token = generate_deletion_token(record_ids)
        staged_deletions[token] = {"ids": record_ids, "expires": now() + 60}
        return StagedDeletion(token=token, records_to_delete=len(record_ids),
                              expires_in_seconds=60)
    
    @tool
    def confirm_deletion(token: str) -> DeletionResult:
        """Execute a staged deletion. IRREVERSIBLE. Confirm only after user approval."""
        staged = staged_deletions.get(token)
        if not staged or staged["expires"] < now():
            raise ValueError("Token invalid or expired. Stage the deletion again.")
        # proceed

    Two distinct calls mean the model can’t complete a destructive operation in a single reasoning step — that’s the point. One caution: two-step flows aren’t sufficient on their own. Production systems also need single-use tokens, strict session binding, and replay protection so a token can’t be reused or executed across sessions.

    The decisions at a glance

    Every one of these is a design decision you make explicitly or make by accident: tool scope (single responsibility, not an action parameter); schema (tight enums and validators, not free strings); descriptions (scope boundaries and when-not-to-use, not happy path only); write operations (idempotent with keys, not fire-and-forget); error returns (structured with error_code/recoverable/suggested_action, not raw exceptions); tool count (dynamic per-step loading, not all tools in every context); API wrapping (purpose-built agent-facing schema, not unfiltered exposure); partial success (an explicit flag, not silent swallowing); destructive actions (two-step staging, not single-call); and tool overlap (semantically distinct and audited, not similar names competing).

    The one principle

    The agent reasons only from the interface you expose, so most “model failures” are design failures you can fix at the tool boundary. Give each tool one responsibility, a schema tight enough to make invalid calls impossible, a description that says when not to use it, structured errors it can branch on, and a confirmation gate on anything irreversible. Do that and a mid-tier model behaves reliably. Skip it and the smartest model available will still delete the wrong partition — confidently, and on the first try.

    Related reading: Anthropic: Writing effective tools for agents · LongFuncEval: tool-calling in long contexts · Pydantic documentation · Tools vs Subagents: When to Use Each · Giving Agents Safe Access to Pipeline Metadata · Governing the AI Agent: CoCo + MCP Security

  • Tools vs Subagents: Building Effective AI Agents Without Over-Engineering

    Tools vs Subagents: Building Effective AI Agents Without Over-Engineering

    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

    A diagram compares Tool Call and Subagent Call methods, showing their workflows, reasoning styles, and differences in speed, cost, and latency. Tool is fast and cheap; Subagent has extra reasoning but higher latency.

    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

    Diagram titled “When a Subagent Is Worth the Trouble” with four color-coded boxes listing scenarios: Multi-step reasoning, Parallel work, Own tool set, Isolate noisy work, each with brief descriptions and examples.

    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

    A diagram showing the Handoff Contract, where an Orchestrator Agent passes tasks down to a Subagent, which then passes results up. Clean tasks and summaries vs shared mutable state are compared.

    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