Building a RAG Ingestion Pipeline with Talend: Chunking, Embeddings, and pgvector Upserts

Every AI assistant project we have been pulled into over the last two years has had the same shape: a chat interface that everyone demos, and behind it a fragile script that loads documents into a vector store. The interface is not the hard part. The ingestion pipeline is, and it is an ETL problem — extract, transform, load, incremental, idempotent, monitored. Which is exactly what a Talend shop already knows how to build.

This walks through the design we use when a client wants retrieval-augmented generation (RAG) fed by their own documents and database content, implemented with Talend jobs rather than a notebook nobody owns.

The pipeline in one picture

sources -> extract (text + metadata) -> hash/change detect
        -> chunk -> embed (batched API) -> upsert into pgvector
        -> prune deleted chunks -> log run metrics

Five stages, each a subjob, orchestrated by one parent job. Nothing exotic. The discipline is in change detection, batching, and idempotency.

1. Extract: text plus metadata, not just text

Sources are usually a mix: a file share of PDFs and Office documents, a Confluence or SharePoint API, and a few database tables (product descriptions, ticket bodies, policy text).

• Files: tFileList to iterate, then a tJava/tJavaRow wrapper around Apache Tika to get plain text and document properties. Tika handles PDF, DOCX, XLSX, HTML and email in one API, which keeps you from bolting on a parser per format.
• APIs: tRESTClient plus tExtractJSONFields, same pattern as in Using tRESTClient and tExtractJSONFields to Access API Data. Page through and persist the paging cursor in a context variable or control table.
• Databases: ordinary tDBInput with a watermark on updated_at.

Whatever the source, normalise to one row shape early:

column	purpose
doc_id	stable natural key (path, page id, primary key)
source_system	which connector produced it
title, url	shown back to the user in citations
updated_at	for watermarking
acl_group	who is allowed to retrieve it
body_text	extracted plain text
content_hash	SHA-256 of body_text

Two columns earn their keep later. acl_group is the one teams forget: if your retriever cannot filter by permission, the assistant will happily quote the salary spreadsheet to the intern. Capture permissions at ingestion, because you cannot reconstruct them at query time. And content_hash is what stops you paying to re-embed 40,000 unchanged documents every night.

2. Change detection on the hash, not the timestamp

Many source systems touch updated_at when nothing meaningful changed — a re-index, a metadata edit, a permissions sync. Embeddings cost money per token, so compare hashes.

Keep a control table:

create table rag_document (
  doc_id        text primary key,
  source_system text not null,
  content_hash  text not null,
  chunk_count   int  not null default 0,
  embed_model   text not null,
  last_embedded timestamptz,
  deleted_at    timestamptz
);

Join the extracted rows against it with tMap (inner join on doc_id, reject-on-no-match into the "new" branch) and route three ways:

• new — no control row: chunk and embed.
• changed — content_hash differs, or embed_model differs from the current model context variable: delete existing chunks, then chunk and embed.
• unchanged — skip, but still refresh mutable metadata such as acl_group and title with a cheap update.

Including the model name in the comparison is what makes re-embedding a configuration change instead of a project. When you move from one embeddings model to another, you bump context.embed_model, and the next run naturally treats every document as changed.

3. Chunking: the step that decides retrieval quality

Chunking is where most homegrown pipelines go wrong. Splitting every 1,000 characters cuts sentences and tables in half, and the resulting vectors retrieve badly.

What has worked for us:

• Split on structure first — headings, then paragraphs. For Markdown and HTML that is straightforward; for PDFs, Tika's output plus blank-line detection is usually enough.
• Pack the structural pieces into chunks of roughly 500–1,000 tokens (about 2,000–4,000 characters for English prose), never crossing a top-level heading boundary.
• Overlap neighbouring chunks by 10–15 percent so a sentence that answers a question is not orphaned at a boundary.
• Prefix each chunk with its document title and heading path (Employee Handbook > Leave > Parental leave). This costs a few tokens and measurably improves retrieval, because the chunk becomes self-describing.
• Keep tables whole if at all possible, or serialise each row as column: value text. A half table is worse than no table.

Implement it as a Java routine — see Creating And Using Java Routines — exposing something like Chunker.split(String text, int targetTokens, int overlapPct) that returns a list of chunk strings. Call it from tJavaFlex and emit one row per chunk with doc_id, chunk_seq, heading_path, chunk_text, and a chunk_hash.

One rule: chunk_seq must be deterministic for the same input. Deterministic chunk keys are what let the load be an upsert instead of a delete-and-reload.

4. Embed: batch, retry, and respect the rate limit

One API call per chunk is the naive implementation, and it is slow and expensive in wall-clock terms. Every mainstream embeddings endpoint accepts an array of inputs. Batch them.

The pattern:

1. tMap computes an approximate token count per chunk (characters divided by four is close enough for planning).
2. A tJavaFlex accumulator groups chunks until either 64 items or roughly 200,000 characters, whichever comes first, then emits one batch row holding a JSON array. Keep an ordered list of the chunk keys for that batch so responses can be matched back by index.
3. tRESTClient posts the batch. Authentication is normally a bearer token; if your provider uses OAuth 2.0 client credentials, reuse the token-caching approach from Calling OAuth 2.0 APIs from Talend rather than fetching a token per call.
4. tExtractJSONFields over $.data[*] yields index and embedding; join back to the batch's chunk key list on index.

Things that will bite you:

• 429 and 5xx are normal. Wrap the call in a retry with exponential backoff and jitter (say 1s, 2s, 4s, 8s, 16s). Do not retry 400s — those are your payload bugs, and retrying just burns quota.
• Truncation is silent. If a chunk exceeds the model's input limit, most providers either error or truncate. Enforce your own maximum in the chunker and assert it before sending.
• Empty and whitespace chunks produce garbage vectors that match everything. Filter them out.
• Dimensions must match the column. A vector(1536) column will reject a 3,072-dimension vector at insert time, which is a good thing. Store the dimension alongside the model name in your run log.
• Cost is a first-class metric. Log tokens sent per run. A tStatCatcher-fed run table with rows, chunks, tokens, and duration is what lets you answer "why did this month cost triple" without guessing.

Run the embed subjob with a modest degree of parallelism — four to eight concurrent batches via tParallelize or a partitioned iteration — and keep it below your provider's requests-per-minute ceiling. More parallelism just converts throughput into 429s.

5. Load: idempotent upserts into pgvector

Target table:

create extension if not exists vector;

create table rag_chunk (
  doc_id       text not null,
  chunk_seq    int  not null,
  chunk_hash   text not null,
  source_system text not null,
  title        text,
  url          text,
  heading_path text,
  acl_group    text,
  chunk_text   text not null,
  embedding    vector(1536) not null,
  embed_model  text not null,
  updated_at   timestamptz not null default now(),
  primary key (doc_id, chunk_seq)
);

create index on rag_chunk using hnsw (embedding vector_cosine_ops);
create index on rag_chunk (acl_group);

Load with tDBOutput in upsert mode, or better, bulk-load a staging table and run one insert ... on conflict (doc_id, chunk_seq) do update set .... The staging route is faster and gives you a natural place to validate row counts before touching the served table.

Two details make reruns safe:

• Delete the tail. If a document shrinks from 40 chunks to 22, chunks 23–40 are stale and must go: delete from rag_chunk where doc_id = ? and chunk_seq > ?. Forgetting this leaves phantom content that the retriever will cheerfully cite. Same idea as the rerun-safety discipline in Slowly Changing Dimensions in Talend — the load has to be a function of current source state, not an append.
• Build the HNSW index once. Do not drop and rebuild it on every incremental run; on a large table that is minutes of downtime for no benefit. Rebuild only on a full re-embed, and in that case write to a new table and swap.

For a full model migration, the swap pattern is worth the extra table: load rag_chunk_v2 completely, verify counts and spot-check retrieval, then rename. Retrieval quality regressions are much easier to undo when the old vectors still exist.

6. Deletions and the tombstone problem

Deleted source documents are the most common correctness bug in RAG pipelines, because most connectors only report what exists.

If the source can report deletions, consume them and hard-delete the chunks. If it cannot, use a run-scoped reconciliation: stamp every doc_id seen in this run into a rag_seen table, then after a successful full pass mark anything not seen as deleted and remove its chunks.

Only do that reconciliation on runs that genuinely enumerated the whole source. A partial crawl that failed halfway and then "reconciles" will wipe most of your index. Gate it on a status flag written by the extract subjob, and add a sanity threshold: if more than, say, 10 percent of documents look deleted, fail the job and alert instead of deleting. That guard has saved a client's index at least twice.

7. Orchestration, monitoring, scheduling

Parent job runs the subjobs with tRunJob on OnSubjobOk, with tLogCatcher/tStatCatcher feeding a run table exactly as in Error Handling and Observability for Talend Jobs. Per-run metrics worth keeping: documents scanned, documents changed, chunks written, chunks deleted, tokens embedded, API retries, duration.

Schedule it like any other pipeline — see Scheduling Talend Jobs in 2026. Nightly full scan plus a frequent incremental pass over high-churn sources covers most needs. If the jobs run in containers, the Docker and Kubernetes CronJob patterns apply unchanged; the only new secret is the embeddings API key, which belongs in your secret store, not in a context file checked into Git.

Add one non-obvious check: a retrieval smoke test. Keep twenty question/expected-document pairs and, after each run, embed the questions and assert the expected document appears in the top five results. It is the vector-store equivalent of a data assertion, and it catches dimension mismatches, botched chunking changes, and half-loaded indexes before users do. The same thinking as Testing Talend Jobs, applied to a fuzzy target.

Why Talend for this at all

A fair question, given the ecosystem of purpose-built ingestion frameworks. The honest answer: if your team already runs Talend, you already have the connectors, the scheduler, the logging conventions, the secret handling, and the operational habits. A RAG pipeline is 90 percent ordinary integration work and 10 percent embeddings API call. Building it in the platform your on-call team understands beats introducing a second stack for the 10 percent.

Where we would not use Talend: sub-second freshness on a streaming source. That is a Kafka-shaped problem — see Streaming with Talend for what is realistic there.

Checklist

• Normalise every source to doc_id, metadata, body_text, content_hash, acl_group.
• Change-detect on content hash and model name, not timestamps.
• Chunk on structure, overlap slightly, prefix with the heading path.
• Batch embeddings calls, retry 429/5xx with backoff, log tokens.
• Upsert on (doc_id, chunk_seq) and delete the stale tail.
• Reconcile deletions only after a verified full crawl, with a threshold guard.
• Smoke-test retrieval after every run.

Get those seven right and the assistant on top has a fighting chance of being trusted. If you want help designing or building this on your stack, contact us.