How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, missed the clothing rule sitting right there in the expense policy, and tried to report the sum of 20 invoices as the yearly food total. Every one of those answers looked confident. None of them was right.

This post walks through how I rebuilt the RAG behind that assistant for FacturaFlow, my postgraduate final project: an invoice capture and bookkeeping app for PYMES Guatemalan businesses running on AWS. 

It covers where the knowledge comes from, how documents and invoices get vectorized, why I search invoices with a hybrid of SQL and pgvector, which guardrails survived testing, and how every answer gets validated and measured before anyone sees it. 

The stack is Aurora PostgreSQL Serverless v2 with pgvector, Titan Text Embeddings V2, and Amazon Nova Lite on Bedrock, all deployed with Terraform.

Three Sources, One Assistant

The assistant answers questions about three very different kinds of knowledge, and treating them the same way was my first mistake:

• Company policies. Expense rules, travel and per diem, the chart of accounts, the month-end close. Each client uploads its own, and the assistant only reads them if the client authorized it in the contract.
• Provider documents. The app's user manual and a guide to Guatemalan tax rules (VAT, electronic invoices, the fuel tax). They hold no client data, so every client gets them, and they are re-synced from Markdown in the repo on every deploy.
• Invoices. Thousands of rows read by Textract and corrected by people: dates, tax IDs, amounts, line items, and a free-text description.

Anything about money is answered by SQL. The model never writes queries. It calls nine read-only tools with typed arguments, the server sets the tenant and role scope from the token, and the tools return rows. Embeddings only come into play for the parts that are free text: policy paragraphs and invoice descriptions. Both vector indexes live in the same Aurora database as the books, which is the main reason I picked pgvector for this project (I compared the options in AI in SQL Server vs PostgreSQL): a date filter and a similarity search run in the same statement.

Three sources, two vector indexes, one database.

1. Indexing Documents by Section

When someone uploads a document, the file goes to S3 and an EventBridge event wakes an indexer Lambda. PDFs go through Textract text detection, Markdown and text are read as UTF-8. Then comes the decision that mattered most for retrieval quality: chunks are cut at Markdown headings first and only then by size, and every chunk carries "Document > Section" in front of its text.

def chunk(title, text):
    chunks = []
    for heading, body in sections(text) or [("", text)]:
        # every chunk says which document and section it came from
        prefix = f"{title} > {heading}" if heading else title
        for piece in windows(body, size=800, overlap=150):
            chunks.append(f"{prefix}\n{piece}")
    return chunks

A policy section such as "Hospedaje (hotel)" usually fits in one chunk, so the hotel limits never get split across two fragments that each rank badly. The prefix also helps the embedding: a paragraph that only says "Q600 per night" means little until it says it belongs to the travel policy. Each chunk is embedded with Titan Text Embeddings V2 at 1,024 dimensions and stored with an HNSW index on cosine distance.

CREATE TABLE kb_chunks (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES kb_documents (id),
  chunk_no    integer NOT NULL,
  content     text NOT NULL,
  embedding   vector(1024)
);
CREATE INDEX ON kb_chunks USING hnsw (embedding vector_cosine_ops);

I call Bedrock from the Lambda. If your text already lives in Aurora, generating the embeddings directly from SQL removes that hop entirely. The admin screen shows each document with its status and chunk count, so a failed PDF is visible the moment it fails.

The knowledge base: client policies plus the manual and tax guide that ship with the service.

What the screenshot says
Page: "Knowledge base. The policies and procedures the AI consults and cites."
Note: "Only active documents are used. The app's user manual and the Guatemalan accounting and tax guide come included and are maintained by the service provider."
Table: 9 active documents of 9, with Status (Indexed), Chunks and Active:
• Projects and clients catalog: 1
• Month-end close and report review: 2
• Guatemalan accounting and tax guide: 21 (included with the service)
• Factura Flow user manual: 43 (app manual)
• Mission, vision and company profile: 1
• Chart of accounts guide: 3
• Expense and approval policy: 10
• Audit and evidence protocol: 1
• Travel, per diem and client visits: 10

2. Searching With the Person's Own Words

The model decides when to search documents and writes the query, and that turned out to be a weak spot. Asked whether someone could buy clothes for a client meeting, Nova Lite searched for "Política de gastos, viajes y combustible", a phrase that matches every policy a little and the clothing section not at all. Asked who approves a computer purchase, it searched "aprobación de compras" and never retrieved the technology section. It even searched "hotel expenses", in English, against Spanish policies.

The fix costs one extra embedding call of about 100 ms: the server searches with the model's query and with the person's original question, keeps the best score for each chunk, and returns the top five.

def search_documents(args, scope):
    hits = embed.search(args["query"], scope, limit=4)
    question = scope.get("question", "")
    if question and question.lower() != args["query"].lower():
        # the model shortens or translates; search the original too
        best = {(h["document_id"], h["chunk_no"]): h for h in hits}
        for h in embed.search(question, scope, limit=4):
            key = (h["document_id"], h["chunk_no"])
            if key not in best or h["similarity"] > best[key]["similarity"]:
                best[key] = h
        hits = sorted(best.values(), key=lambda h: h["similarity"],
                      reverse=True)[:5]
    return hits

The scope comes from the verified token. It carries the role and whether the client authorized its corporate documents; without that authorization, embed.search filters the query to the manual and the tax guide only. The model cannot widen it, because it never sees the scope as an argument.

The model searched "política de viáticos"; the original question pulled in the hotel section.

What the screenshot says
Question: "How much do they pay me for a hotel per night if I travel inside the country to see a client?"
Answer: "The per-night hotel limit inside the country is Q600, taxes included. Hotels with a corporate agreement or close to the client are preferred, and a more expensive hotel needs prior written approval."
How I searched: "Searched the documents for 'per diem policy': 5 fragments."
Cited documents: Travel, per diem and client visits; Expense and approval policy.

3. Vectorizing Invoices, and Why a Threshold Was Not Enough

Questions like "how much did we spend on food?" started out as a LIKE '%comida%' over the account name, supplier, description, justification and line items. In 2026 there were 458 confirmed food invoices. Not one of them contained the word "comida". They said "Almuerzo con cliente", "Catering para capacitación", "Refacción para reunión de equipo".

So every invoice now gets a vector built from its supplier, its description and the lines the OCR read. It happens right after the OCR saves the invoice and again whenever someone edits the supplier or the description, and a failure there never blocks saving. The backfill of 5,142 invoices took about six minutes. The table stores two things on purpose: the vector of the full text, and a short label (the description) that later steps group by.

My first instinct was a similarity cutoff. I measured it first, and the numbers killed the idea. Best similarity per query, against all invoices:

• "gasolina para los carros": 0.51 (has invoices)
• "comida": 0.33 (has invoices)
• "uniformes": 0.26 to 0.34 (has no invoices)
• "internet": 0.25 (has invoices)
• "asesoría": 0.23 (has invoices)
• "ropa": 0.16 (has no invoices)

Answerable and unanswerable queries overlap, and a cutoff of 0.30 recovered only 2% of the food invoices. The ranking, on the other hand, held up: the top 10 results were mostly the right expense in 15 of the 16 answerable queries, and only "luz" missed entirely. So the hybrid search keeps the ranking and drops the cutoff.

Exact filters first, meaning second, totals from SQL.

Exact filters (period, supplier, tax ID, account, status) run first, so the similarity search only ranks invoices that could be part of the answer. Running it the other way around, a top 50 across all years can easily contain zero rows from 2026. A loose floor of 0.22 throws away the queries where nothing is even close, which is how "ropa" returns zero without asking the model anything.

-- candidate labels, ranked by meaning inside the exact filters
SELECT e.content,
       min(e.embedding <=> CAST(:v AS vector)) AS distance
FROM invoice_embeddings e
JOIN invoices i ON i.id = e.invoice_id
WHERE i.status = 'confirmed'
  AND to_char(i.fecha, 'YYYY') = :period
GROUP BY e.content
HAVING min(e.embedding <=> CAST(:v AS vector)) <= 0.78
ORDER BY 2
LIMIT 50;

Nova Lite gets those 50 labels as a numbered list and answers with the numbers that really are the requested expense: a catering is food, a cleaning service is not. The kept labels then widen the original word filter, and the summary tool adds up every invoice that matches under the same exact filters. No top-k ever touches the total.

-- the literal word still counts; the kept labels add the synonyms
AND (search_text LIKE '%' || :concepto || '%'
     OR EXISTS (
       SELECT 1 FROM invoice_embeddings e
       WHERE e.invoice_id = i.id
         AND e.content IN (
           SELECT jsonb_array_elements_text(CAST(:kept AS jsonb)))))

Two details took a second round of measuring. My first version showed the model the description plus the line items, which turned 458 food invoices into 301 distinct texts, so 30 candidates covered 44 invoices. Grouping by the description alone collapsed them into 35 labels. And when the invoice list tool hit its 20-row limit, the model happily added up those 20 rows and called it the total; now a truncated list also returns the count and sum of every match, computed in SQL.

Q240,049.66 in food for 2026, from invoices that never say "comida".

What the screenshot says
Question: "How much did we spend on food in 2026?"
Answer: "Total food spending for Servicios Integrales del Valle, S.A. in 2026 was Q240,049.66. Monthly breakdown:"
• January Q23,277.12 • February Q22,736.22 • March Q19,736.87
• April Q22,343.88 • May Q51,748.35 • June Q28,868.87
• July Q28,901.76 • August Q24,557.97 • September Q17,878.62
"These amounts include VAT, which totaled Q25,719.53."
Chart: "Amount over time, food, 2026."
How I searched: "Searched invoices with 'comida' in 2026: 20 invoices." / "Summed confirmed purchases with 'comida' in 2026 by month: 9 groups."

Fuel for September matched its accounting account to the cent, and "cosas de librería" found 217 stationery invoices across 16 suppliers. Food lands at roughly three quarters of the food account, mostly because Nova Lite does not read "refacción", the Guatemalan word for an office snack, as food. I kept that gap rather than adding a synonym list, because every hand-written synonym is one more patch to maintain.

4. Guardrails That Earn Their Place

Every question goes through an Amazon Bedrock Guardrail first, and the final answer goes through it again. What stays: the prompt attack filter at high strength, content filters for hate, insults, sexual content, violence and misconduct, blocking of passwords and AWS keys, masking of card numbers, emails and phones, and a single denied topic for credentials and the system prompt. Bedrock lists Spanish as optimized for these filters in its supported languages table, which matters when your users write "dame la contraseña de María".

What went away: denied topics for entertainment and programming. They blocked core accounting questions. "Dame un resumen del último libro diario" (a summary of the latest journal) was refused as a book summary, and "¿cómo se calcula el IDP?" (how the fuel tax is computed) was refused as programming. I tested a narrower definition and then one with explicit exclusions ("does not include accounting, ledgers, taxes or how the app works") on a temporary guardrail with the same tier. The version with exclusions still blocked 4 of 12 legitimate questions, including how to export the purchase ledger to Excel.

Off-topic requests are now handled by the system prompt, which tells the model to answer with one exact sentence. The server recognizes that sentence and renders it with the same "Fuera de alcance" badge the guardrail used. The data stays safe either way, because the tools are read-only and the scope comes from the server. The worst a determined user gets is a TV show recommendation: "recomiéndame una serie" slips through, since "serie" is also a field on every Guatemalan invoice.

A joke request, declined by the model's instructions instead of a guardrail topic.

What the screenshot says
Question: "Tell me a joke."
Badge: "Out of scope."
Answer: "I can only answer questions about your company's invoices, books, alerts and policies."

5. Validating Every Answer Before Anyone Reads It

The model does not know when it is wrong, so the server checks. A deterministic verify() compares the answer with everything the tools returned: every quetzal amount, invoice number and account code must appear in the data, a "no results" answer cannot sit on top of rows that came back, the period searched must match "this month" or "the last 9 weeks", and tool names or notes written for the model must never leak into the text.

Those rules cannot see a policy answer that invents a rule in plain words, so answers built only from documents get a second check: a contextual grounding check on a separate guardrail, called through ApplyGuardrail with the fragments as the source. It lives apart from the main guardrail so the code can read the scores and retry, instead of showing a block message.

# limits: 100,000 chars of source, 1,000 of query, 5,000 of answer
response = bedrock.apply_guardrail(
    guardrailIdentifier=GROUNDING_GUARDRAIL_ID,
    guardrailVersion=GROUNDING_GUARDRAIL_VERSION,
    source="OUTPUT",
    content=[
        {"text": {"text": fragments[:100_000],
                  "qualifiers": ["grounding_source"]}},
        {"text": {"text": question[:1_000], "qualifiers": ["query"]}},
        {"text": {"text": answer[:5_000],
                  "qualifiers": ["guard_content"]}},
    ],
)

Before wiring it in, I tested it in Spanish with real policy text. Faithful answers scored 0.99 to 1.0 on grounding. Invented ones, such as a Q900 hotel limit or reimbursable business clothes, scored 0.01. It adds about 0.3 seconds. The thresholds are 0.5 for grounding and 0.4 for relevance, and they live in Terraform next to the guardrail.

Any failed check withholds the answer and retries once with the same model, told what went wrong. If the retry fails too, the person sees "No pude responder" with suggested rewordings, and the admin gets the flags, the scores and the full tool transcript.

The path every answer takes before it is shown.

One limit worth stating plainly. Asked who approves a computer purchase, the assistant once said the director does "because it exceeds Q5,000.01". The approval table with that amount was in the fragment, so grounding scored 0.99. The policy actually says the director approves any computer regardless of amount. Grounding checks catch facts that are absent from the source; they miss a wrong inference built from facts that are present.

6. Measuring It With a Judge and a Human

That computer answer is why there is also an offline evaluation, built on the ideas from Measuring RAG Solutions. I wrote 40 questions from the policies, the manual and the tax guide: direct ones, informal ones, follow-ups, and five the documents do not answer. Each has the expected document and section, the key facts, and a reference answer.

{"pregunta": "¿me pagan el hotel si voy a ver a un cliente a Xela?",
 "documento": "Viajes, viáticos y visitas a clientes",
 "seccion": "Hospedaje (hotel)",
 "datos": ["Q600"],
 "tipo": "respuesta"}

The script asks each question through the deployed API, reads the exchange log to learn which searches ran, repeats them to get the full fragments, and scores:

• Retrieval: whether the expected section is among the five fragments (hit@5) and at what position (MRR).
• Key facts: whether Q600 or "dirección" actually appear in the answer.
• Faithfulness and correctness: Nova Pro, used only here, splits the answer into claims, marks each as supported or not by the fragments, and grades it from 1 to 5 against the reference. The judge is deliberately a bigger model than the one in production, the same reasoning as in matching the model to the task.
• Unanswerable questions: the answer says the documents do not cover it, and invents nothing.

The judge needs a check of its own. The script writes ten random judged answers to a CSV with an empty "agree" column, a person fills it in, and the agreement rate goes into the report next to the scores. The first run gave hit@5 of 77.1%, faithfulness of 93.1%, correctness of 4.56 out of 5, and 60% on the unanswerable questions. Four of the nine failures were the old guardrail topics blocking legitimate questions, which is how I found out they had to go.

The provider panel: live retrieval and grounding next to the offline evaluation.

What the screenshot says
Panel: "Knowledge base (RAG). Chunks of up to 800 characters with 150 of overlap, cut by document section with the title in front. Titan Text Embeddings V2 (1,024 dimensions); each search returns up to 5 chunks, the closest to the model's query and to the original question."
Live use:
• 124 assistant searches
• 0.501 median similarity of the best chunk
• 24.2% of searches with no relevant chunk (similarity below 0.35)
• 4 of 4 rated answers with documents marked useful
• 101 ms median time to embed the question
• 50.0% of policy answers grounded in the documents (2 checked live)
Evaluation (40 questions, Nova Pro judge, 2026-11-07):
• 77.1% expected section among the 5 chunks
• 93.1% of claims supported by the chunks
• 4.56 correctness against the reference (1 to 5)
• 60.0% of unanswerable questions declined without inventing
• Agreement with human review: pending
Table: each document with its type (app manual, corporate, regulations), status, chunk count, and average and maximum chunk length.

Gotchas Worth Knowing

• Single ambiguous words embed poorly. "luz" (electricity, also "light") found no utility bills, while "energía eléctrica" did. Ask the model to pass the concept as a descriptive phrase.
• Contextual grounding defaults to the Classic tier when you omit the tier, and the Standard tier requires cross-Region inference. Test thresholds again if you switch, since scores differ between tiers.
• Embedding on write is cheap; failing on write is expensive. The invoice vector refresh catches every error so a throttled Bedrock call never stops someone from saving an invoice.

What I Would Tell Myself on Day One

Split the knowledge by shape before writing any retrieval code: free text gets vectors, money gets SQL, and the two meet in one database. Measure a similarity threshold before trusting one, since ranking held up across every query I tried while every cutoff failed somewhere. Keep guardrails for safety and leave topic control to the instructions, and put a deterministic check, a grounding check and an offline judge in front of the user, because each one caught failures the other two missed.

If you only add one piece, add the deterministic check on amounts. It costs nothing to run, needs no model, and it is the one that stopped a made-up yearly total from reaching an accountant.

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.

Keeping Redshift System Table History Past 7 Days with S3 Tables

Redshift keeps its SYS_* monitoring views for seven days in cluster, which is fine until an auditor or an incident review asks for something older. Until now the usual fix was a custom export job to S3 and a Glue crawler to keep it queryable. AWS just added a native system table integration with S3 Tables that skips that pipeline entirely: Redshift can write system table data straight into Amazon S3 Tables, in Apache Iceberg format, with no ETL to maintain.

What it runs on

This applies to Redshift Provisioned on RA3 and RG instance types, and to Redshift Serverless. Older provisioned instance families are not supported. AWS launched it across a wide set of commercial regions rather than a single preview region, but region coverage is the kind of detail that shifts, so confirm your region against the current availability list before you enable it.

Permissions you need

The principal that enables the feature needs:

• redshift:EnableLogging for a provisioned cluster, or redshift-serverless:UpdateNamespace for Serverless
• s3tables:CreateTableBucket, s3tables:PutTableBucketEncryption, and s3tables:PutTableBucketPolicy, to stand up the aws-redshift table bucket

That is the whole permission set. Once the bucket exists, Redshift creates namespaces and tables inside it through a service trust relationship with S3 Tables, so nobody needs standing permission to create namespaces or tables themselves.

1. Decide what you are retaining and how it should be organized

You choose which SYS_* views to publish, from SYS_QUERY_HISTORY and SYS_QUERY_TEXT to SYS_CONNECTION_LOG and SYS_VACUUM_HISTORY, or all of them at once. You also pick a deployment model. Per-warehouse keeps each cluster's data in its own set of tables, which matters if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES could contain sensitive literal values. Consolidated writes every warehouse in the account and Region into shared tables, distinguished by a warehouse_name column, which is better for cross-warehouse observability.

Supported system tables

You can select any of these SYS_* views.

2. Enable delivery on the cluster

For a provisioned cluster, this reuses the existing logging API with a new destination type. The principal running this needs redshift:EnableLogging plus permission to create and configure an S3 table bucket named aws-redshift in the account.

-- publish selected system tables to S3 Tables, consolidated across the account
aws redshift enable-logging \
    --cluster-identifier my-redshift-cluster \
    --log-destination-type s3table \
    --log-exports sys_query_history sys_query_text sys_connection_log \
    --s3-table-granularity account
 
-- Redshift Serverless uses update-namespace instead
aws redshift-serverless update-namespace \
    --namespace-name my-namespace \
    --log-destination-type s3table \
    --s3-table-action Enable \
    --s3-table-names all \
    --s3-table-granularity namespace
  

3. Confirm data is actually flowing

Delivery runs in batches at a fixed frequency and only includes completed activity, so a query still running will not show up until it finishes. Check the last ingestion time per view with describe-logging-status on a provisioned cluster or get-namespace on Serverless.

aws redshift describe-logging-status \
    --cluster-identifier my-redshift-cluster
  

4. Register the table bucket with Glue Data Catalog

Querying the retained data from Redshift, Athena, or any other Iceberg-compatible engine requires the aws-redshift S3 table bucket to be integrated with AWS Glue Data Catalog first. This is a one-time step per account and Region, not something you repeat per cluster.

5. Query the history from Redshift

Once the catalog integration is in place, point an external schema at it and query the historical view like any other table. The rows carry warehouse_name, warehouse_namespace_arn, and s3_tables_ingestion_time alongside the original SYS_QUERY_HISTORY columns, which is what lets a consolidated deployment separate one cluster's activity from another's.

-- replace <glue_database> with the database created by the S3 Tables / Glue integration
CREATE EXTERNAL SCHEMA redshift_history
FROM DATA CATALOG
DATABASE '<glue_database>'
IAM_ROLE 'arn:aws:iam::111122223333:role/RedshiftHistoryReadRole';
 
-- queries older than the 7-day in-cluster window
SELECT warehouse_name, query_id, start_time, elapsed_time
FROM redshift_history.sys_query_history
WHERE start_time < dateadd(day, -7, getdate())
ORDER BY elapsed_time DESC
LIMIT 20;
  

What it costs

Writing the data out of Redshift into S3 Tables is free. What you pay for is standard S3 Tables storage and maintenance, meaning compaction and snapshot upkeep, on whatever you retain, plus normal usage pricing for the engine you query it with, whether that is Redshift Spectrum, Athena, or something else. Since data without an expiration policy is kept forever, an unset retention policy is really a storage cost decision, not just a compliance one.

Gotchas worth knowing before you enable this

• Disabling and re-enabling, or switching between per-warehouse and consolidated, never backfills. Whatever happened during the gap is gone for good.
• Delivered rows are immutable. You cannot update or delete individual rows through Redshift, only through S3 Tables record expiration.
• Dropping the S3 Tables permanently deletes everything retained in them and Redshift does not recreate them automatically. Re-enabling starts a fresh table with no history.
• A customer managed KMS key has to be set the first time you enable the feature. Changing it later means dropping the tables, and the retained data, and starting over.
• The views marked with an asterisk above need patch P203 or later. On an older patch the tables get created but stay empty.
• Everything is scoped to a single account and a single region. Cross-account or cross-region analysis means combining results at query time, not a single unified table.
• Only completed activity is delivered. A query that is still running will not show up until it finishes, aborts, or is canceled.

Before you run this in production

Set a record expiration policy directly in S3 Tables once delivery is running, since without one the data is kept indefinitely and keeps accruing storage cost. Start with the per-warehouse model if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES might carry sensitive literal values, and only move to consolidated once you have confirmed what those views actually capture in your environment. 


If you are pulling SYS_QUERY_EXPLAIN history into this pipeline for performance work, PlanTrace is a free tool for turning those query plans into something you can actually read.

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.

A Guatemalan's Guide to Drinking Coffee Properly

My mug

I grew up surrounded by coffee farms in Guatemala and somehow spent most of my adult life drinking whatever was closest, usually instant, usually while standing over the sink. If that sounds familiar, this is for you. No lectures, no gear worship, just why this coffee is actually worth the extra five minutes, how to pick a bag based on what you like instead of what a label tells you to like, and which machines are actually worth owning depending on how deep you want to go.

Why Guatemalan coffee earns the hype

Guatemala grows coffee on the slopes of active volcanoes, which is either the most metal origin story in the coffee world or just Tuesday for Guatemalan farmers. Volcanic soil is packed with minerals, the highlands stay cool even close to the equator, and slow growth at altitude gives the bean more time to build sugar and flavor before harvest. The regions genuinely taste different from each other too. Antigua leans chocolatey with a citrus edge, Huehuetenango turns bright and almost wine-like, Cobán goes softer and more floral thanks to all the rain. This is the actual, physical reason a bag from one region tastes nothing like a bag from another, not marketing copy dressed up to sound scientific.

Pick your coffee like you pick your music, based on taste, not trends

The single biggest mistake people make buying coffee is grabbing whatever bag has the nicest packaging. What actually matters is what you like in a cup, and that comes down to roast level.

If you like brightness, something with a little zing that wakes you up before the caffeine even kicks in, go lighter roast. Light roasts preserve more of the bean's natural acidity and fruitiness, they don't taste sour, they taste alive.

If you want something sweeter, rounder, and more familiar, medium roast is the safe, correct answer for most people, and it happens to be what most of the Huehuetenango bags below are roasted to. Nobody is judging you for liking the coffee equivalent of a comfortable couch.

Bags worth actually ordering

As I live in Guatemala, the brands usually available here are different from the ones on the international market, so i tried to find some of the brands that resemble the ones I prefer:

Fresh Roasted Coffee, Guatemala Huehuetenango, medium roast, single origin, kosher, sold in a 2-pack of 2 lb bags. Good option if you drink enough coffee that buying in bulk actually makes sense.

Guatemala Coffee, Huehuetenango, whole bean, medium roast, freshly roasted, 5 lb bag. Same region, bigger bag, for the household that goes through coffee like it's water.

Two Volcanoes Guatemalan Coffee, whole bean, 1 lb. Smaller bag, good if you want to try something new without committing to five pounds of a coffee you've never tasted.

The French way, easiest option...

A Veken French Press, 34oz, no plastic touching the coffee, thickened borosilicate glass, skips the paper filter entirely. That means more body, more oils, more of that heavier mouthfeel, which suits a medium roast Huehuetenango particularly well. Cleanup is slightly more annoying, that's the tradeoff, and it doubles as a cold brew pot if you're into that.

The Italian way, the most versatile one...

The Bialetti Moka Express, 12-cup is not technically espresso, don't let anyone tell you otherwise, but it's strong, concentrated, and genuinely delicious over ice or with steamed milk. The 12-cup size means you're not making this twice for a full household. It also makes a very satisfying gurgling noise right before it's ready, which is honestly half the appeal.

The Japanese way, V60 and actual patience

The Hario V60 Pour Over Starter Set, size 02 is the classic for a reason, cheap, simple, and it makes brighter beans genuinely sing. Pair it with the Hario Buono electric gooseneck kettle for actual control over your pour instead of just dumping water and hoping, and keep a stack of Hario V60 paper filters, size 02 on hand since running out mid-morning is its own special kind of tragedy. This method forces you to stand there for three minutes doing nothing but pouring water in circles, which is either meditative or mildly annoying depending on your morning.

Going full home barista

The Ninja Luxe Café Mini is the interesting middle ground, it does both espresso and drip in one machine, with a built-in burr grinder, a precision scale, and a manual steam wand, so you get real control without needing three separate appliances on your counter.

If you'd rather push one button and get a finished drink without thinking about grind size ever again, the Jura E4 in piano black is a fully automatic machine built for exactly that, bean to cup with none of the fuss. The Jura ENA 4 in Nordic white does the same job in a smaller footprint, good if counter space is the actual constraint rather than budget.

So which one do you actually need

None of them, technically. A bag of good Guatemalan coffee and a French press will outperform bad beans in a thousand dollar machine every single time. Buy the coffee that matches what you actually like drinking, and pick whichever method fits your morning, rushed, ritualistic, or somewhere in between. The volcano already did the hard part. You're just not allowed to ruin it on your end... Also, do not listen to the purist that says coffee should be black without sugar, is your money, you choose the way to drink it: sugar, cocoa, milk, cinnamon... go ahead and try new things!

This post contains affiliate links. As an Amazon Associate, studyyourdata.com earns from qualifying purchases at no extra cost to you.

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.