Showing posts with label AI. Show all posts
Showing posts with label AI. Show all posts

Friday, September 4, 2026

Measuring RAG Solutions: Are We Retrieving the Right Information?


A RAG pipeline that answers questions in the demo is not the same thing as a RAG pipeline that answers them correctly. 

The gap between the two only shows up once you start measuring precision, recall, and faithfulness instead of eyeballing a handful of outputs. 

This post covers two ways to run that measurement: a fully local setup with a vector database and an open model as judge, and Amazon Bedrock's native Knowledge Bases evaluation.

How the evaluation knows what is correct

This is the part that trips people up, and it splits the metrics into two families. Faithfulness and answer relevancy do not need a labeled correct answer at all. For faithfulness, the judge LLM breaks the generated answer into individual factual claims, then checks each one against the retrieved context: a claim not supported by anything retrieved counts against the score. For answer relevancy, the judge generates a handful of questions that the answer would plausibly be responding to, then compares those against the original question using embedding similarity. Both checks are self-contained: question, context, and answer, nothing else.

Context recall, and usually context precision, work differently and do need a labeled example. You have to supply a ground truth, meaning a correct reference answer written by you or a subject matter expert ahead of time. The judge decomposes that reference answer into statements and checks whether each one is backed by something in the retrieved context. If the reference says three facts and only two show up in what got retrieved, context recall lands at roughly 0.67, regardless of what the model went on to generate. This is why the practical bottleneck in either approach below is not the tooling, it is building a small set of representative questions with a correct answer already written for each one. Fifteen to thirty well chosen pairs, covering both easy and edge case queries, will tell you more than a hundred pairs nobody reviewed. Everything else in this post assumes you already have that set.

What you are actually measuring

RAG evaluation splits into two layers, and conflating them is the most common mistake. The retrieval layer asks whether the right chunks came back from the vector search: context precision (how many of the retrieved chunks are relevant) and context recall (how many of the relevant chunks got retrieved). The generation layer asks whether the model did something sensible with those chunks: faithfulness (is the answer grounded in the retrieved text, or invented) and answer relevancy (does the answer actually address the question). You can have perfect retrieval and a hallucinating generator, or a great generator fed garbage context. Measuring only the end-to-end answer hides which half is broken.

Both approaches below compute these same four metrics. What differs is where the judging happens and what it costs you to get there.

Diagram showing RAG evaluation and metrics

 

Approach 1: Local vector database with an open model as judge

This setup runs a vector store (Chroma, in the example below) and a local LLM through Ollama, then hands both to RAGAS to compute the scores. Nothing leaves your machine, and there is no per-token bill. If your embeddings already live inside a relational database rather than a standalone store, generating them directly from SQL against Bedrock is worth a look before you stand up a separate pipeline just for this evaluation.

Pros: zero API cost per evaluation run, full data privacy for sensitive corpora, no dependency on AWS region availability, easy to iterate quickly on chunking or embedding changes since a run costs only compute time.

Cons: judge quality depends on the local model, and smaller open models are noticeably less reliable graders than Claude or GPT-4 class models. RAGAS issues with Ollama timeouts are a known friction point on CPU-only machines. You also own the entire pipeline: embeddings, chunking, retriever tuning, and judge prompt behavior, with no managed comparison view across runs.

A minimal setup looks like this. Install the pieces first:

# local stack: chroma, sentence embeddings, ollama-backed judge
pip install ragas chromadb sentence-transformers openai
ollama pull llama3.1
ollama pull nomic-embed-text
  

Build the retriever over your own documents, run a handful of test questions through it, and collect question, retrieved contexts, generated answer, and (if you have them) reference answers into a dataset. Then point RAGAS at your local Ollama server through its OpenAI-compatible endpoint:

-- pseudocode-style Python, adjust names to your pipeline
from openai import OpenAI
from ragas.llms import llm_factory
from ragas import evaluate
from ragas.metrics import (
    context_precision, context_recall,
    faithfulness, answer_relevancy,
)
from datasets import Dataset
 
client = OpenAI(api_key="ollama",
                 base_url="http://localhost:11434/v1")
judge = llm_factory("llama3.1", provider="openai", client=client)
 
eval_set = Dataset.from_dict({
    "question": questions,
    "contexts": retrieved_contexts,
    "answer": generated_answers,
    "ground_truth": reference_answers,
})
 
result = evaluate(
    eval_set,
    metrics=[context_precision, context_recall,
             faithfulness, answer_relevancy],
    llm=judge,
)
print(result)
  

The output is a dictionary of scores between 0 and 1 for each metric, and calling result.to_pandas() gives you a per-question breakdown so you can see exactly which retrievals or answers dragged the average down. Swap Chroma for FAISS or Qdrant, or the embedding model for a different sentence-transformers checkpoint, rerun, and compare the numbers directly. If you are deciding between a dedicated vector store like this and keeping vectors inside a relational engine, the trade-offs across SQL Server and PostgreSQL's native vector support are worth reading before you commit to either path.

Approach 2: Amazon Bedrock Knowledge Bases evaluation

Bedrock has a built-in evaluation job type for Knowledge Bases, generally available since March 2025, that uses a foundation model as judge and computes the metrics for you, with no RAGAS or custom scoring code required. It supports retrieval-only evaluation, full retrieve-and-generate evaluation, and evaluating a RAG system hosted anywhere by supplying your own inference responses in the input dataset. Citation precision and citation coverage were added later to check whether generated answers are actually grounded in the sources they cite.

Pros: no evaluation infrastructure to build or maintain, console comparison view across multiple evaluation runs (useful when testing different chunking strategies or embedding models side by side), stronger judge models available (Claude models as evaluator), and you can evaluate a knowledge base you already run in production without extra tooling.

Cons: you pay standard Bedrock on-demand pricing for both the evaluator model and the generator model on every run, it is currently limited to a subset of AWS regions, the service is optimized for English content, and your evaluation data has to live in S3 with a specific JSONL structure, which adds setup overhead compared to a Python dict.

The evaluation dataset is a JSONL file where each line carries a question and a reference answer:

{"conversationTurns":[{
  "referenceResponses":[{"content":
    [{"text":"A trigger invokes a Lambda function."}]}],
  "prompt":{"content":
    [{"text":"What is a Lambda trigger?"}]}
}]}
  

From the console, under Evaluations, Knowledge Bases, you create a job, pick the evaluator model, choose whether to evaluate retrieval only or retrieval plus generation, select the metrics, and point it at the S3 dataset. The same job can be created through boto3 for a repeatable pipeline:

import boto3
 
bedrock = boto3.client("bedrock", region_name="us-east-1")
 
bedrock.create_evaluation_job(
    jobName="kb-rag-eval-run-1",
    roleArn="arn:aws:iam::ACCOUNT_ID:role/BedrockEvalRole",
    applicationType="RagEvaluation",
    evaluationConfig={
        "automated": {
            "datasetMetricConfigs": [{
                "taskType": "General",
                "dataset": {
                    "name": "kb_eval_set",
                    "datasetLocation": {
                        "s3Uri": "s3://my-bucket/eval-set.jsonl"
                    }
                },
                "metricNames": [
                    "Builtin.ContextCoverage",
                    "Builtin.ContextRelevance",
                    "Builtin.Faithfulness",
                    "Builtin.Correctness",
                ]
            }],
            "evaluatorModelConfig": {
                "bedrockEvaluatorModels": [{
                    "modelIdentifier":
                      "anthropic.claude-3-5-sonnet-20241022-v2:0"
                }]
            }
        }
    },
    inferenceConfig={
        "ragConfigs": [{
            "knowledgeBaseConfig": {
                "retrieveAndGenerateConfig": {
                    "knowledgeBaseId": "KB_ID",
                    "modelArn":
                      "anthropic.claude-3-haiku-20240307-v1:0"
                }
            }
        }]
    },
    outputDataConfig={"s3Uri": "s3://my-bucket/eval-output/"}
)
  

The exact metric names and payload shape depend on whether you are running a retrieval-only or a retrieve-and-generate job, so check the current Bedrock knowledge base evaluation documentation before wiring this into a pipeline. Once the job finishes, the console gives you a metric summary, a per-metric breakdown with the judge's reasoning for each score, and a side by side comparison view if you run the same dataset against two different knowledge base configurations, which is the part a local RAGAS script does not give you out of the box.

 

Which one to reach for

Local with RAGAS and Ollama fits fast iteration during development, sensitive or regulated corpora that cannot leave your environment, and situations where the evaluation itself has to run at effectively no marginal cost. Bedrock's native evaluation fits teams already running Knowledge Bases in production, cases where you want a stronger judge model without hosting one, and any workflow where comparing several configurations side by side matters more than shaving cost. Nothing stops you from running both: prototype and tune locally, then confirm the final numbers with Bedrock's evaluator before shipping.

Tuesday, August 18, 2026

From DBA or Data Engineer to AI Engineer: A Realistic Path

If you spend your days tuning queries, managing pipelines, or keeping a production database alive, you already carry most of what an AI engineering role needs. What is missing is not a new career from scratch, it is a specific set of additions on top of what you already do well. Here is the path that actually gets you there, without pretending you need to become a research scientist first.

1. Treat Python as your primary language, not a scripting add-on

Most DBAs and data engineers already write Python for ETL glue code, but AI engineering asks for more: comfort with async code, working with SDKs like OpenAI's or Anthropic's, and writing code that calls external APIs reliably, with retries and error handling. If your Python has mostly lived inside pandas scripts, push it into building small services and command line tools before touching anything AI-specific.

2. Learn how LLMs actually work, at the level you need to use them well

You do not need to understand transformer internals to be effective. You do need to understand tokens and context windows, the difference between a base model and an instruction-tuned one, prompt structure, temperature and sampling parameters, and function or tool calling. This is the layer that separates someone who can call an API from someone who can design a reliable system around one.

3. Build on the database skills you already have: vector search and RAG

This is where your background gives you a real head start. Retrieval Augmented Generation is fundamentally a data engineering problem with a language model bolted on the end: chunk documents, generate embeddings, store and query vectors, and feed the results into a prompt. If you already know PostgreSQL, pgvector gets you there without learning a new database engine. If you work in Redshift or SQL Server environments, understanding how vector search differs from traditional indexing will make you far more useful on an AI team than someone coming in without any database grounding.

4. Get hands-on with a managed AI platform on the cloud you already use

If your infrastructure background is AWS, go deep on Amazon Bedrock: model access, knowledge bases, and agents. If you live in Azure, do the same with Azure OpenAI Service. The point is not to learn every platform, it is to learn one well enough to provision it, secure it with proper IAM, and monitor its cost and usage the way you already monitor a database.

5. Ship one project that proves you can build, not just describe

Interviews for AI engineering roles increasingly ask for a working example over a certificate. A small RAG chatbot over your own documents, using a vector-enabled Postgres and a Bedrock or Azure OpenAI model, is enough to demonstrate the full pipeline: ingestion, embedding, retrieval, and generation. I laid out this exact project, along with four others that mix cloud data warehousing and AI, in an earlier post on starter projects for an AI and data engineering portfolio.

6. Learn to evaluate and monitor AI systems, not just build them

A model that answers well in a demo can fail quietly in production: hallucinated answers, drifting retrieval quality, or cost spikes from runaway token usage. Employers increasingly want people who can set up evaluation pipelines and observability around an AI system, not just wire the initial integration. This is a natural extension of the monitoring instincts a DBA or data engineer already has, applied to a new kind of workload.

What This Path Actually Buys You

You are not competing with machine learning researchers for these roles, and you do not need to be one. Most AI engineering work in production is systems work: reliable pipelines, sound data models, and careful integration of a model into something people use. That is the job you have been doing all along, with a new component added on top. Start with the RAG project, since it touches every skill above at once, and let the rest follow from there.

If you are setting up a machine specifically for this kind of work, I covered the full setup, from picking a model to getting PostgreSQL, Docker, and PyTorch running natively, in an earlier post on configuring a Mac for data engineering and AI work.

Friday, August 7, 2026

Narrow Task Decomposition in AI Pipeline Agents

A nightly load into sales_fact fails. Ask an orchestrating agent to break down the diagnosis and it will usually stop at the first two subtasks that come to mind: check the logs, check the schema. That decomposition is narrow, and a fix built on it can miss the real cause. The problem is not the model, it is that the coordinator never questioned its own first list before delegating.

1. See what narrow decomposition misses

A first-pass breakdown of "why did sales_fact fail" often lands on log-analyzer and schema-inspector and stops there. Both are reasonable, and both are incomplete. Neither one checks whether the upstream extract delivered bad or late data, whether another job holds a lock on the same table, or which downstream dashboards are now stale because the load never completed. A fix based on two subtasks treats the visible half of the failure as the whole failure.

2. Add a self-critique step to the coordinator prompt

The fix is a coordinator prompt that forces a second pass before any delegation happens: generate an initial subtask list, ask what is missing, add subtasks to cover the gap, and only then hand work to subagents.

You are a coordinator diagnosing an ETL failure. When
decomposing the task:

1. Generate an initial list of subtasks.
2. Ask yourself: what systems, data sources, or failure
   modes are missing from that list?
3. Add subtasks to cover those gaps.
4. Only then begin delegating to subagents.

For an ETL failure specifically, consider:
- the failing job AND its upstream dependencies
- a structural cause (schema) AND a data cause (quality)
- this run AND whether it is a recurring pattern
- the technical root cause AND the downstream impact
  

Run the sales_fact example through that loop and the initial two-item list grows to four: log-analyzer and schema-inspector, plus a source-data-validator that checks the upstream extract for null spikes or row-count drops, and a downstream-impact-checker that lists which dashboards or scheduled jobs depend on the table. The self-critique step costs one extra generation and catches the two subtasks that a narrow first pass always skips.

3. Define each subtask as a subagent

With Claude's Agent SDK, each subtask becomes an AgentDefinition, with a description Claude matches against the task and a prompt scoped to that one job.

from claude_agent_sdk import AgentDefinition

log_analyzer = AgentDefinition(
    description="Diagnoses ETL failures from pipeline logs. "
                "Use when a nightly load job fails and the "
                "cause is unclear.",
    prompt="Find the failing step and report the exact "
           "error message and line number. Do not propose "
           "a fix.",
    tools=["Read", "Grep", "Bash"],
    model="haiku",
)

schema_inspector = AgentDefinition(
    description="Checks table structure for drift against "
                "the last known good schema.",
    prompt="Compare current columns, types, and "
           "constraints to the last successful load.",
    tools=["Read", "Bash"],
)

source_data_validator = AgentDefinition(
    description="Checks the upstream extract for the failed "
                "load. Use for row-count drops or null spikes.",
    prompt="Compare today's source row count and null "
           "rate to the 7-day average.",
    tools=["Read", "Bash"],
    model="haiku",
)

downstream_impact_checker = AgentDefinition(
    description="Lists dashboards and jobs that depend on "
                "the table that failed to load.",
    prompt="Find consumers of sales_fact and flag which "
           "are now stale.",
    tools=["Read", "Grep"],
)
  

AgentDefinition also takes a model field, so a subtask that is closer to pattern matching, like reading a log file or comparing two row counts, can run on a cheaper model while one that needs judgment, like assessing schema drift or downstream impact, stays on the default. That is the same idea covered in matching the model to the task, not the app, applied at the subagent level instead of a single orchestrator.

4. Wire the coordinator to call the agents

The coordinator prompt from step 2 becomes the system_prompt, and the four AgentDefinition objects from step 3 go into agents, keyed by the name Claude will use to call each one. Claude decides on its own which of the four to invoke, based on how the task matches each description.

from claude_agent_sdk import query, ClaudeAgentOptions

AGENTS = {
    "log-analyzer": log_analyzer,
    "schema-inspector": schema_inspector,
    "source-data-validator": source_data_validator,
    "downstream-impact-checker": downstream_impact_checker,
}

async for message in query(
    prompt="The sales_fact load failed last night, find out why.",
    options=ClaudeAgentOptions(
        system_prompt=COORDINATOR_PROMPT,
        # "Agent" auto-approves each subagent call
        allowed_tools=["Read", "Grep", "Bash", "Agent"],
        agents=AGENTS,
    ),
):
    for block in getattr(message, "content", None) or []:
        if getattr(block, "name", None) == "Agent":
            print("routed to:", block.input.get("subagent_type"))
  

Claude does not call a Python function directly, it emits a tool_use block named Agent, and block.input["subagent_type"] holds the name of the agent it picked, one of the four keys in AGENTS. Logging that value is how you confirm the coordinator actually routed to source-data-validator or downstream-impact-checker instead of quietly falling back to the two obvious subtasks. To force a specific agent instead of letting Claude choose, name it in the prompt: "use the downstream-impact-checker agent to list what depends on sales_fact."

5. The same routing in OpenAI's Agents SDK

OpenAI's Agents SDK expresses the same coordinator through handoffs. The triage agent's instructions carry the same four-step self-critique, and each of the four specialists is passed in its handoffs list, exposed to the model as a callable tool named transfer_to_<agent_name>. When Runner.run finishes, result.new_items contains a HandoffOutputItem for any handoff that fired, and its target_agent.name is the concrete signal, the OpenAI equivalent of reading subagent_type off Claude's Agent tool call.

6. One failure, traced end to end

Take one run instead of a list of what each agent can do. At 02:14, the nightly load into sales_fact fails, and the engineer on call prompts the coordinator with "the sales_fact load failed last night, find out why." The coordinator's first move is always the same: call log-analyzer, since nothing else can be scoped correctly before the actual error is known.

routed to: log-analyzer
  MERGE INTO sales_fact failed at 02:14:07 with:
  duplicate key value violates unique constraint
  "sales_fact_pkey" (order_id, sale_date)
  

A duplicate key on a merge has two plausible explanations: something changed about the table that now lets duplicates in, or the source data itself contains rows that collide on that key. Both are live possibilities under the self-critique step from earlier, so the coordinator calls schema-inspector and source-data-validator next. It does not call downstream-impact-checker yet, since nothing so far says anything about who reads this table.

routed to: schema-inspector
  no DDL changes on sales_fact in the last 14 days

routed to: source-data-validator
  source row count: 41,209 (7-day avg: 68,450, -40%)
  null rate on customer_id: 6.2% (7-day avg: 0.1%)
  

schema-inspector rules out a structural cause. source-data-validator explains the collision: a partial extract with a null spike on customer_id delivered rows that collapsed onto the same order_id and sale_date, tripping the unique constraint on merge. The coordinator stops here and never calls downstream-impact-checker, because the merge failed and rolled back, so nothing downstream ever read the bad batch. That agent earns its place in a run where a bad load succeeds silently instead of failing loudly, a different failure mode from this one.

Three of the four candidate agents ran, in two rounds instead of one batch, and the proposed fix follows directly from what they found: quarantine the extract that arrived overnight rather than retrying the load as is, and add a row-count and null-rate check ahead of the merge so a batch shaped like this one gets rejected before it reaches sales_fact, not after.

Before You Trust the Decomposition

Test the coordinator prompt against a case where you already know the full list of causes, and check whether its self-critique step actually surfaces the ones a naive first pass would miss. If it keeps landing on the same two or three subtasks regardless of the failure, the gap-checking questions in the prompt are too generic and need to name the specific systems in your pipeline.

Not every failure needs any of this. A connection timeout, a transient throttling error, a job that fails because another job it depends on is still running, these have deterministic fixes: retry with backoff, wait and requeue, alert and stop. Route the alert through a cheap classifier first, or a plain if-statement on the error code, and reserve the coordinator and its agents for failures that pattern-matching cannot already explain. Calling four agents to conclude "retry it" is not decomposition, it is waste.

Friday, July 31, 2026

Programmatic Tool Calling with the Claude SDK

Programmatic Tool Calling with the Claude SDK

Every morning somebody on the team runs the same check. Thirty tables in the staging schema, and the only thing anyone wants to know is which ones did not get loaded overnight. It is a five minute job, it is boring, and it is exactly the sort of thing you would hand to an agent. So you do. And then you watch it take thirty separate trips to the model, one table at a time, dragging every result set through the context window on the way. The answer was three table names. You paid for thirty round trips to get them.

The LLMCompiler paper put a name on this in 2024. Treat the tool calls like a compiler treats instructions: work out what depends on what, then run everything else at the same time. Two years later the idea is in the API, and it arrived in a shape nobody quite predicted. There is no plan format to parse. Claude just writes the Python.

1. One field changes everything

Add allowed_callers to a tool and switch on code execution. Your tool stops being something the model asks for one call at a time and becomes an async function sitting inside a sandbox, taking a dict, returning a string. From there, loops and conditionals and asyncio.gather come free. They are just Python.

import anthropic

client = anthropic.Anthropic()

TOOLS = [
    {"type": "code_execution_20260120",
     "name": "code_execution"},
    {
        "name": "run_sql",
        "description": ("Run a read-only query on the warehouse. "
                        "Returns a JSON list of row objects."),
        "input_schema": {
            "type": "object",
            "properties": {"sql": {"type": "string"}},
            "required": ["sql"],
        },
        "allowed_callers": ["code_execution_20260120"],
    },
]

resp = client.messages.create(
    model="claude-sonnet-5",
    max_tokens=4096,
    tools=TOOLS,
    messages=[{"role": "user", "content":
        "For every table in the stage schema, get the latest "
        "load timestamp and list only those older than 6 hours."}],
)
  

Sonnet 5 is the right call here. Plan, fan out, summarize, nothing that needs the expensive model, which is the same reasoning behind matching the model to the task rather than the app. The floor is the code execution version, not the tier, so any Sonnet from 4.5 forward will do.

2. Somebody else writes the loop

Back comes a server_tool_use block with the generated script inside it. The first time you read one of these it is a little uncanny, because it is roughly the code you would have written yourself on a Tuesday afternoon.

# written in the container, not by you
import json, asyncio

tables = json.loads(await run_sql({"sql": LIST_SQL}))
rows = await asyncio.gather(*[
    run_sql({"sql": f"select max(load_ts) ts from {t['name']}"})
    for t in tables
])

stale = [(t["name"], r) for t, r in zip(tables, rows)
         if hours_since(r) > 6]
print(stale)
  

One query to find the tables, then all thirty timestamps at once, then a filter. Three names get printed. Three names is all the model ever sees. The other twenty seven results lived and died in the sandbox, which is the whole point.

3. You still answer the phone

Your database is not in that container, so every time the script calls the tool, everything stops and waits for you. The response shows up with stop_reason of tool_use, a container id, and one block per pending call. Run the query, hand back the rows, the script picks up mid-line.

while resp.stop_reason == "tool_use":
    results = [
        {"type": "tool_result",
         "tool_use_id": b.id,
         "content": execute(b.name, b.input)}
        for b in resp.content if b.type == "tool_use"
    ]
    # tool_result blocks only on this turn, nothing else
    messages += [{"role": "assistant", "content": resp.content},
                 {"role": "user", "content": results}]
    resp = client.messages.create(
        model="claude-sonnet-5",
        max_tokens=4096,
        container=resp.container.id,  # required while calls pend
        tools=TOOLS,
        messages=messages,
    )
  

Two things will trip you on the first attempt. That user turn takes tool results and absolutely nothing else, not even a stray line of text. And the full tools array has to ride along on every continuation, code execution included, otherwise the paused script has nothing to wake up into.

4. Is it worth it

Depends entirely on the shape of your work, and the documentation is refreshingly blunt about it. On a 75-tool agent benchmark, about 38 percent fewer billed input tokens with accuracy unchanged. Across production traffic declaring 10 to 49 tools, savings of 20 to 40 percent. And on a benchmark where each turn made one or two sequential calls, it cost 8 percent more. Fan-out wins. Short chains lose.

Before you point this at production

Keep an eye on the clock. A pending call gives up after about four minutes and throws a timeout inside the running script, and idle containers get reclaimed after about five, so a query that takes ten minutes needs chunking, not optimism. Availability is uneven at the moment: Claude API, Claude Platform on AWS and Microsoft Foundry yes, Bedrock and Google Cloud not yet. And the obvious one, which is worth saying anyway. Every query in that fan-out is generated text. Give the tool a read-only connection and let the database enforce it, because allowed_callers is a hint to Claude about how to use the tool, not a wall around it.

Friday, July 24, 2026

Match the Model to the Task, Not the App: An Orchestrator-Worker Pattern in Python

Most apps that call an LLM send every request to the same model. That works until the bill arrives. A classification job that returns one word costs the same as a multi-step reasoning job, because you picked one model at design time and never revisited it. The fix is an orchestrator-worker setup: a small central router reads the incoming task, decides which tier of model it needs, and delegates to a worker. The router sits at the hub, each model tier is a spoke, and no worker talks to another. This post builds that in Python using both the Anthropic and OpenAI SDKs, so you can mix providers in the same pipeline.


1. Define the tiers before writing any code

Three tiers cover almost everything. Fast handles extraction, classification, tagging, and format conversion. Balanced handles summarization, SQL generation, and short analysis. Deep handles multi-step reasoning, code review, and anything where a wrong answer is expensive. Map each tier to a model from each provider so you can fail over between them.

# tiers.py
TIERS = {
    "fast":     {"anthropic": "claude-haiku-4-5", "openai": "gpt-5.6-luna"},
    "balanced": {"anthropic": "claude-sonnet-5",  "openai": "gpt-5.6-terra"},
    "deep":     {"anthropic": "claude-opus-4-8",  "openai": "gpt-5.6-sol"},
}
  

2. The orchestrator itself belongs on the cheap tier

The orchestrator runs on every request, before any real work happens. Whatever it costs gets multiplied across your entire traffic, and its latency lands in front of every response. Putting a deep model there means paying premium prices to decide what to do. Routing is short classification against a closed label set, which is precisely what the small models handle well.

The risk is that router errors are asymmetric. Sending a fast task to the deep tier wastes money and you see it in the bill. Sending a deep task to the fast tier produces a plausible wrong answer that flows downstream unnoticed. Three controls keep that in check: a closed label set instead of free-form output, a hard cap on max_tokens so the router cannot drift into explanation, and the validator gate covered further down, which catches the cases where the cheap tier was not enough.

import anthropic

client = anthropic.Anthropic()

ROUTER_PROMPT = """Classify the task into exactly one tier.
fast: extraction, classification, tagging, formatting
balanced: summarization, query generation, short analysis
deep: multi-step reasoning, code review, planning
Answer with the tier name only."""

def route(task_text):
    msg = client.messages.create(
        model="claude-haiku-4-5",
        max_tokens=8,
        system=ROUTER_PROMPT,
        messages=[{"role": "user", "content": task_text}],
    )
    tier = msg.content[0].text.strip().lower()
    return tier if tier in TIERS else "balanced"
  

The fallback on the last line matters. Routers misfire, and an unrecognized label should land on a safe middle tier rather than raise. And when your app already knows the task type because the user clicked a specific button or the pipeline stage is fixed, skip the router entirely. A dictionary lookup costs nothing and never misclassifies.


3. Wrap each worker behind one function signature

The two SDKs differ in shape. Anthropic uses messages.create with a separate system argument and a required max_tokens. OpenAI uses responses.create with an input list and a reasoning effort setting. Normalize both to take a prompt and return a string, and the orchestrator stops caring which vendor answered. The Anthropic Python SDK and the OpenAI model catalog document the current identifiers for each family.

import anthropic
from openai import OpenAI

anthropic_client = anthropic.Anthropic()
openai_client = OpenAI()

def call_anthropic(model, system, prompt, max_tokens=1024):
    msg = anthropic_client.messages.create(
        model=model,
        max_tokens=max_tokens,
        system=system,
        messages=[{"role": "user", "content": prompt}],
    )
    return msg.content[0].text

def call_openai(model, system, prompt, effort="low"):
    resp = openai_client.responses.create(
        model=model,
        instructions=system,
        input=prompt,
        reasoning={"effort": effort},
    )
    return resp.output_text
  

4. Dispatch, and allow one escalation

The orchestrator is now small. Pick the tier, pick the provider, run the call. Add a single escalation path: if the fast tier returns something your validator rejects, retry once on the next tier up. That ladder is a model cascade, and it should stay short. One retry, not a loop. Unbounded escalation turns a cost optimization into a cost multiplier.

LADDER = ["fast", "balanced", "deep"]

def run_task(prompt, system, validator, provider="anthropic"):
    tier = route(prompt)
    idx = LADDER.index(tier)

    for attempt_tier in LADDER[idx:idx + 2]:
        model = TIERS[attempt_tier][provider]
        if provider == "anthropic":
            out = call_anthropic(model, system, prompt)
        else:
            effort = "low" if attempt_tier == "fast" else "high"
            out = call_openai(model, system, prompt, effort)

        if validator(out):
            return {"tier": attempt_tier, "model": model, "output": out}

    raise ValueError("no tier produced a valid result")
  

The validator is the piece people skip, and it is what makes the whole design safe. For extraction, check that the JSON parses and the required keys exist. For SQL generation, run EXPLAIN against the target database. Without a validator you have no signal that the cheap tier was good enough, and the routing decision becomes a guess.


5. Log the tier, not just the answer

Store the tier, model, token counts, and validator result for every call. After a week you will see which task types actually escalate and which ones you over-provisioned from the start. That log is the only honest way to tune the routing prompt, and it usually reveals that a large share of traffic never needed anything above the fast tier.


6. A real pipeline: invoice ingestion

Consider a service that receives scanned invoices and has to produce clean rows in a database. Four distinct jobs happen per document, and only one of them is hard. Classifying the document type is a label from a short list. Field extraction is bounded and schema-driven. Totals reconciliation is arithmetic plus judgment about rounding and tax lines. Resolving an ambiguous line item against a product catalog is where reasoning actually pays for itself. Hardcode the first three, and let the orchestrator decide only on the last one.

# pipeline.py

STAGE_TIERS = {
    "classify_doc":   "fast",
    "extract_fields": "fast",
    "reconcile":      "balanced",
}

def run_stage(stage, system, prompt, validator, provider="anthropic"):
    # known stage, no router call needed
    tier = STAGE_TIERS[stage]
    model = TIERS[tier][provider]
    ...  # same call plus escalation logic as run_task

def process_invoice(ocr_text):
    doc_type = run_stage(
        "classify_doc",
        system="Answer with one of: invoice, receipt, credit_note, other.",
        prompt=ocr_text[:2000],
        validator=is_known_doc_type,
    )
    if doc_type != "invoice":
        return route_to_manual_queue(ocr_text, doc_type)

    fields = run_stage(
        "extract_fields",
        system=EXTRACTION_SCHEMA_PROMPT,
        prompt=ocr_text,
        validator=parses_as_invoice_json,
    )

    if not totals_match(fields):
        fields = run_stage(
            "reconcile",
            system=RECONCILE_PROMPT,
            prompt=describe_mismatch(fields),
            validator=totals_match,
        )

    # only unresolved items reach the orchestrator and the expensive tiers
    for item in unmatched_items(fields):
        item["sku"] = run_task(
            prompt=catalog_match_prompt(item),
            system=CATALOG_PROMPT,
            validator=sku_exists,
        )["output"]

    return persist(fields)
  

Notice what the structure buys you. Every invoice pays for two fast calls. The balanced tier only runs when the arithmetic disagrees, and the deep tier only touches line items the catalog lookup could not resolve on its own. On a clean batch, most documents never leave the cheap tier, and the expensive models are reserved for the handful of cases that genuinely earn them.


Wrapping Up

Orchestrator-worker does not have to mean an elaborate framework. A tier table, a cheap orchestrator, one wrapper per SDK, and a validator gate get you most of the benefit in under a hundred lines. Both providers expose a low-cost tier that handles far more than people expect once the prompt is narrow enough.

One practical note before you ship: pin the versioned model identifiers in production rather than the family aliases. Aliases move when a vendor releases a new generation, and a routing table that silently changes tiers under you is a debugging session nobody enjoys.


Friday, June 12, 2026

5 Starter Projects for Your AI and Data Engineering Portfolio

Reading tutorials is fine. Shipping something is better. If you are trying to break into data engineering or AI, nothing on your resume carries more weight than a GitHub repo with working code and a problem you actually solved. These five projects are designed to give you hands-on experience with real tools while producing portfolio artifacts you can point to in an interview.


Project 1 — Automated ETL Pipeline with Scheduling

Tech Stack: Python, PostgreSQL, Apache Airflow, Docker

Build a pipeline that pulls data from a public API (weather, exchange rates, or any open dataset), transforms it with Python, and loads it into PostgreSQL on a schedule. Airflow handles the orchestration, and Docker keeps the environment reproducible. This project teaches you the core ETL loop and gives you a DAG you can walk through in any data engineering interview. The scheduling angle forces you to think about idempotency and failure handling from day one.

# Minimal Airflow DAG skeleton
from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime
 
def extract(): ...
def transform(): ...
def load(): ...
 
with DAG("etl_pipeline", start_date=datetime(2025, 1, 1), schedule="@daily") as dag:
    t1 = PythonOperator(task_id="extract", python_callable=extract)
    t2 = PythonOperator(task_id="transform", python_callable=transform)
    t3 = PythonOperator(task_id="load", python_callable=load)
    t1 >> t2 >> t3
  

Project 2 — Cloud Data Warehouse on AWS

Tech Stack: AWS S3, Amazon Redshift Serverless, Python (boto3), SQL

Upload a dataset to S3, provision a Redshift Serverless workgroup, and load the data using the COPY command. Write a set of analytical queries against it. The goal is not just to run queries — it is to understand how a cloud warehouse differs from a local database: columnar storage, distribution keys, and the cost model. This project demonstrates cloud data skills that show up in virtually every modern data engineering job description. Use the AWS Free Tier to keep costs at zero while learning.

-- Load from S3 into Redshift
COPY sales
FROM 's3://your-bucket/sales.csv'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftRole'
FORMAT AS CSV
IGNOREHEADER 1;
  

Project 3 — ETL Pipeline with Data Factory in Microsoft Fabric

Tech Stack: Data Factory in Microsoft Fabric, Microsoft Fabric Lakehouse, OneLake, SQL Server, Python (Fabric Notebook)

Microsoft has consolidated its data integration story into Microsoft Fabric, and Data Factory in Microsoft Fabric is its next-generation replacement for Azure Data Factory. Build a pipeline that ingests data from SQL Server or a flat file, transforms it using a Fabric Notebook (Python/PySpark), and lands the result in a Fabric Lakehouse backed by OneLake. This project exposes you to the unified Fabric workspace model — one platform for pipelines, notebooks, warehouses, and Power BI — which is exactly where Microsoft data engineering is heading. A free Fabric trial requires no credit card and gives you full access to build this end to end.


Project 4 — RAG Chatbot over Your Own Documents

Tech Stack: Amazon Bedrock, Claude or Llama 3 (via Bedrock), pgvector (PostgreSQL), Python, LangChain

Take a set of PDF or text documents you own, chunk them, generate embeddings using a Bedrock foundation model, store the vectors in PostgreSQL with the pgvector extension, and build a simple question-answering interface on top. This is Retrieval Augmented Generation (RAG) in its simplest form. It teaches you the full AI data pipeline: ingestion, embedding, vector search, and prompt construction. RAG is one of the most in-demand AI engineering patterns in production today.

# Store a document embedding in pgvector
INSERT INTO documents (content, embedding)
VALUES (%s, %s::vector);
 
-- Semantic similarity search
SELECT content
FROM   documents
ORDER BY embedding <-> '[query_vector]'::vector
LIMIT 5;
  

Project 5 — End-to-End ML Pipeline with Feature Engineering

Tech Stack: Python, pandas, scikit-learn, SQL Server or PostgreSQL, MLflow

Pick a structured dataset with a clear prediction target (churn, sales forecast, classification). Build the full cycle in Python: data extraction from a relational database, feature engineering, model training, and experiment tracking with MLflow. The data engineering side is the extraction and feature pipeline; the AI side is the model and tracking. Publishing your MLflow experiment results in a public repo gives reviewers something concrete to evaluate. This project bridges the two disciplines in a single deliverable.

import mlflow
from sklearn.ensemble import RandomForestClassifier
 
with mlflow.start_run():
    model = RandomForestClassifier(n_estimators=100)
    model.fit(X_train, y_train)
    mlflow.log_metric("accuracy", model.score(X_test, y_test))
    mlflow.sklearn.log_model(model, "model")
  

Five Projects. Two Cloud Platforms. One GitHub Profile Worth Showing.

Together these projects cover the full surface area hiring managers scan for: pipeline orchestration, cloud warehousing on AWS and Azure, AI integration, and end-to-end ML tracking. Pick the stack you are least afraid of, break it, fix it, and commit the mess. The best time to start was last month. The second best time is now.

If you are curious about what a working portfolio looks like in practice, you can take a look at my own projects.



Monday, June 8, 2026

AI in SQL Server vs PostgreSQL: Vector Search, Embeddings, and RAG Compared

Both SQL Server and PostgreSQL are moving fast into AI territory — and if you manage either (or both), you've probably wondered how they compare when it comes to storing embedding, running vector searches, and building RAG pipelines. The short answer: the capabilities are converging, but the approach is very different. Here's a practical breakdown.


1. The VECTOR data type: built-in vs extension

SQL Server 2025 ships with a native VECTOR data type, part of the core engine — no extra install needed. It stores embedding as optimized binary, surfaced as JSON arrays, with support for up to 1,998 dimensions. See the official type reference for the full syntax.

PostgreSQL takes the extension route via pgvector. You install it once and get a vector type that integrates natively with your existing tables. It's open source, available on RDS, Azure Database for PostgreSQL, Google Cloud SQL, and self-hosted instances.

-- SQL Server 2025
CREATE TABLE articles (
    id        INT PRIMARY KEY,
    title     NVARCHAR(500),
    embedding VECTOR(1536)
);
 
-- PostgreSQL + pgvector
CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE articles (
    id        SERIAL PRIMARY KEY,
    title     TEXT,
    embedding VECTOR(1536)
);

2. Similarity search: VECTOR_DISTANCE vs the <=> operator

SQL Server 2025 uses the VECTOR_DISTANCE() function with explicit metric names ('cosine', 'euclidean', 'dot'). PostgreSQL with pgvector uses operator syntax: <=> for cosine, <-> for L2 (Euclidean), <#> for negative inner product. Both let you combine similarity with regular WHERE filters in the same query.

-- SQL Server 2025: top 5 similar articles
SELECT TOP 5
    title,
    VECTOR_DISTANCE('cosine', embedding, @query_vector) AS distance
FROM   articles
ORDER BY distance ASC;
 
-- PostgreSQL + pgvector: top 5 similar articles
SELECT
    title,
    embedding <=> $1 AS distance
FROM   articles
ORDER BY distance
LIMIT  5;

3. Approximate nearest neighbor (ANN) indexing

For large datasets, exact KNN scans get expensive. SQL Server 2025 introduces DiskANN-powered vector indexes via CREATE VECTOR INDEX (currently in preview, requires enabling PREVIEW_FEATURES). pgvector supports two ANN index types: ivfflat (IVF with flat quantization) and hnsw (Hierarchical Navigable Small Worlds). HNSW is generally preferred for query latency.

-- SQL Server 2025: DiskANN vector index (preview)
CREATE VECTOR INDEX idx_articles_embedding
    ON articles (embedding)
    WITH (METRIC = 'cosine');
 
-- PostgreSQL: HNSW index via pgvector
CREATE INDEX idx_articles_embedding
    ON articles
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

4. External model integration

SQL Server 2025 introduces EXTERNAL MODEL, which lets you register AI models (Azure OpenAI, OpenAI, Ollama, etc.) as first-class T-SQL objects and call them directly from queries. PostgreSQL doesn't have this built into the core engine — you typically generate embeddings outside the DB (Python, LangChain, application layer) and then insert the resulting vectors. Azure Database for PostgreSQL does offer an azure_ai extension that bridges this gap for Azure-hosted instances.


5. Which one fits your use case?

If your workload is already on SQL Server and you want everything — embeddings, vector search, and model calls — inside the engine with zero extra infrastructure, SQL Server 2025 is compelling. The DiskANN index is designed to scale to billions of vectors without offloading to a dedicated vector store.

If you're on PostgreSQL, pgvector gets you 90% of the way there with far less licensing cost and broader managed service support. HNSW indexes are mature and production-proven. The trade-off is that embedding generation stays outside the database — you own that pipeline.


Bottom line

Both platforms now let you store and query embeddings directly alongside relational data — which is the real win, because it eliminates the data synchronization headache of maintaining a separate vector store. SQL Server 2025 bets on deeper engine integration and managed model calls; PostgreSQL bets on ecosystem flexibility and open-source momentum. The syntax is surprisingly similar. Pick the one you're already running, and start small: create one table with a VECTOR column, generate embeddings for a real dataset, and measure your query latency with and without an ANN index. That benchmark will tell you more than any comparison article.


Measuring RAG Solutions: Are We Retrieving the Right Information?

A RAG pipeline that answers questions in the demo is not the same thing as a RAG pipeline that answers them correctly.  The gap between the...