DEV Community

Cover image for Embeddings Refresh Pipelines: Incremental Updates, Cost, Drift Monitoring
Gowtham Potureddi
Gowtham Potureddi

Posted on

Embeddings Refresh Pipelines: Incremental Updates, Cost, Drift Monitoring

#python #sql #dataengineering #interview

embeddings refresh is the quiet operational discipline that separates a RAG prototype from a production embeddings pipeline — and the one component senior data engineers keep discovering is missing on day one of an "our semantic search results are getting worse" incident. Every product ships retrieval-augmented generation or semantic search in 2026; every one of those products backs onto a vector store — pgvector, Pinecone, Weaviate, Milvus — that is a stateful asset on the same footing as the warehouse. The moment the source corpus changes, the vectors decay; the moment the embedding model version rolls forward, the whole space rotates; the moment the OpenAI bill lands, someone in finance asks why a vector refresh cost 12,000 dollars this month. The engineering trade-off lives in which change-detection strategy you pick, which cost tier you batch into, and which drift metric you alert on — not in whether refresh matters.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "how do you re-embed a 10-million-document corpus without paying full price on the OpenAI dashboard?", "what is your incremental embedding strategy when only 2% of rows change per day?", or "how do you monitor embedding drift before your product manager files a bug about relevance?". It walks through the four axes senior interviewers actually probe — change detection, cost, model versioning, drift monitoring — the SHA-256 content-hash pattern that turns a full-corpus re-embed into a 2% incremental job, the OpenAI text-embedding-3 and Cohere pricing math that decides between managed API and self-hosted BGE/E5, the pgvector refresh column layout with a per-row model_tag that makes migrations reversible, and the recall@k golden-query monitor that catches embedding drift before the retrieval-quality graph hits the pager. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

PipeCode blog header for embeddings refresh — bold white headline 'Embeddings Refresh' with subtitle 'Incremental · Cost · Drift' over a document scroll converging with a vector cube onto a purple wax seal reading REFRESH, on a dark gradient with purple, orange, green, and blue accents and a small pipecode.ai attribution.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

On this page

1. Why embedding refresh became a first-class data-eng problem

Every embedding is a snapshot — the moment content changes or the model rolls forward, your vectors go stale

The one-sentence invariant: an embedding is a lossy, model-versioned function of a piece of content, so the pair (content_version, model_version) uniquely determines the vector — change either half and the vector on disk is stale. Nothing about vector stores makes this obvious. A pgvector row looks like an ordinary column; a Pinecone upsert looks like a write to a key-value store. But every vector is really a cache of f(content, model), and every cache needs a refresh policy. Ignore this, and the RAG system quietly rots — user queries return yesterday's answer, then last month's answer, then answers about products the company no longer sells.

The four "must-answer" axes interviewers actually probe.

  • Incremental refresh. How do you find the 2% of documents that changed since the last run, embed only those, and leave the rest alone? A senior interviewer expects a content-hash + change-queue answer, not "we re-embed everything nightly." Full-corpus re-embed on a 10M-doc catalogue is 6-figure-a-year money on OpenAI pricing; the incremental strategy is the cost lever.
  • Cost accounting. What does embedding cost per document actually work out to on text-embedding-3-small versus text-embedding-3-large versus a self-hosted BGE or E5 encoder? The senior signal is naming batch pricing (50% off), the crossover point where self-hosting wins, and the fact that tokens — not documents — are what the invoice line items count.
  • Model versioning. When OpenAI ships text-embedding-4-tiny in 2027, how do you migrate a 10M-doc index without downtime? The right answer stamps every row with a model_tag, dual-writes during migration, and keeps the ability to roll back. The wrong answer discovers there is no way to tell which vectors are on which model.
  • Drift monitoring. What tells you the retrieval quality is degrading before the product team files a bug? The senior answer is a golden query set + recall@k trend, not "we'll notice." Drift is silent; the pipeline that does not monitor for it is the pipeline that ships a regression.

Why re-embed is not a "one-off migration."

  • Corpora move. Product catalogues update daily. Documentation is edited weekly. Support tickets stream in continuously. Any corpus that backs a semantic search is always in motion; the pipeline is a stream, not a batch.
  • Models move. Between 2022 and 2026, OpenAI shipped text-embedding-ada-002, then text-embedding-3-small, then text-embedding-3-large — each with a different dimension, a different price, and a different quality profile. Cohere shipped embed-english-v3, then embed-multilingual-v3, then embed-v4. Self-hosted encoders (BGE, E5, GTE) release a new checkpoint every quarter. The stack the team picked at launch is not the stack in production two years later.
  • Vocabulary moves. New product names, new terminology, new user intents show up in the query stream before they show up in the corpus. The embedding model's implicit vocabulary was frozen at training time; the corpus and the query stream keep moving. That gap is drift.
  • Compliance moves. GDPR erasure requests, DMCA takedowns, customer opt-outs — every one of these deletes a row from the corpus, and every one of those deletions must remove the matching vector from the store. Skip this and the pipeline ships a privacy bug.

What a healthy embeddings pipeline looks like end-to-end.

  • Ingest layer. New / updated / deleted documents flow in via CDC, Kafka, or an ETL job. Each document has a stable doc_id, a content field, and a sha256_hash of the content.
  • Change-detection layer. Compare the new hash to the last-seen hash; if different, mark the row for re-embed. Cheapest possible first pass — bytes in, bytes out, no model calls.
  • Embed layer. Batch the change queue, call the embedding API (or self-hosted encoder), write the resulting vector plus the model_tag back to the row. This is the expensive layer; every cent spent here is a cent that comes off embedding cost.
  • Vector-store sync. Upsert the vector into pgvector / Pinecone / Weaviate. Delete vectors for tombstoned rows. Track write success per batch.
  • Drift monitor. Nightly job runs a golden query set through the retrieval pipeline; reports recall@k. Alert when the trend crosses the threshold.
  • Model-migration path. When the team decides to move from text-embedding-3-small to a new model, a background job re-embeds the corpus into a shadow column, validates recall@k against the old, cuts the read path over, drops the old column.

What interviewers listen for.

  • Do you say "content hash + model tag" in the first sentence when asked how to detect a stale vector? — senior signal.
  • Do you mention batch-tier pricing (50% off) unprompted when asked about cost? — senior signal.
  • Do you push back on "we re-embed everything nightly" with the cost + drift argument? — required answer.
  • Do you describe embedding drift as a trend on recall@k over a golden query set, not as an intuition? — required answer.

Worked example — the "we re-embed everything nightly" anti-pattern

Detailed explanation. The textbook mistake: a team ships RAG on top of a 5-million-document knowledge base. On launch day they set up a nightly cron that iterates every row, calls text-embedding-3-small, and upserts the vector. Everything works. Three months later finance flags a 15,000-dollar-a-month line item on the OpenAI invoice. Walk an interviewer through the true cost, the actual change rate, and the incremental architecture that replaces the nightly full re-embed.

  • The symptom. OpenAI invoice grows linearly with corpus size × refresh rate.
  • The naive assumption. "The API is cheap, so nightly is fine."
  • The real math. 5M docs × 30 nights × average tokens per doc × per-token rate = a five-figure monthly bill.
  • The correct fix. Only re-embed rows whose content hash changed since the last run.

Question. A 5-million-document corpus of internal wiki pages runs a nightly full-corpus re-embed on text-embedding-3-small at $0.00002 per 1K tokens. Average doc length is 400 tokens. Actual per-day churn is 2% of rows. Quantify the wasted spend and propose the incremental architecture.

Input.

Parameter Value
Corpus size 5,000,000 docs
Average tokens per doc 400
Model text-embedding-3-small
Price per 1K tokens $0.00002
Refresh cadence nightly (30 runs / month)
Actual per-day change rate 2%

Code.

# Cost of the nightly full re-embed
def full_reembed_monthly_cost(docs, tokens_per_doc, price_per_1k, runs_per_month):
    tokens_per_run = docs * tokens_per_doc
    price_per_token = price_per_1k / 1000.0
    return docs * tokens_per_doc * runs_per_month * price_per_token

# Cost of the incremental re-embed
def incremental_monthly_cost(docs, tokens_per_doc, price_per_1k, runs_per_month, change_rate):
    return full_reembed_monthly_cost(docs, tokens_per_doc, price_per_1k, runs_per_month) * change_rate

full_cost = full_reembed_monthly_cost(
    docs=5_000_000,
    tokens_per_doc=400,
    price_per_1k=0.00002,
    runs_per_month=30,
)
inc_cost = incremental_monthly_cost(
    docs=5_000_000,
    tokens_per_doc=400,
    price_per_1k=0.00002,
    runs_per_month=30,
    change_rate=0.02,
)
print(f"Full nightly:  ${full_cost:,.2f}/month")
print(f"Incremental:   ${inc_cost:,.2f}/month")
print(f"Savings:       ${full_cost - inc_cost:,.2f}/month  ({(1 - inc_cost / full_cost) * 100:.1f}%)")
Enter fullscreen mode Exit fullscreen mode 
-- Incremental change-queue schema (Postgres)
CREATE TABLE documents (
  doc_id        TEXT PRIMARY KEY,
  content       TEXT NOT NULL,
  sha256_hash   TEXT NOT NULL,
  embedding     vector(1536),
  model_tag     TEXT,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  embedded_at   TIMESTAMPTZ
);

-- Change-queue is a view over docs whose content hash advanced past the embedded snapshot
CREATE OR REPLACE VIEW embed_queue AS
SELECT doc_id, content, sha256_hash
FROM   documents
WHERE  embedding IS NULL
   OR  embedded_at IS NULL
   OR  embedded_at < updated_at;
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The full nightly job re-embeds 5M docs × 400 tokens × 30 nights = 60 billion tokens per month. At $0.00002 per 1K tokens that is $1,200 per month straight to OpenAI — before the team even touched the model tier.
  2. The actual change rate is 2% per day, so at most 100K rows genuinely need re-embedding on any given night. 100K × 400 × 30 = 1.2 billion tokens per month — a 50× reduction on volume for the same freshness guarantee.
  3. The incremental architecture stores sha256_hash on every row and updates updated_at whenever content changes. The embed job selects WHERE embedded_at IS NULL OR embedded_at < updated_at — only the rows that actually need work.
  4. The schema also stores embedded_at — the timestamp of the last successful embed. This is the freshness cursor; a nightly job comparing updated_at > embedded_at finds exactly the rows that drifted since last run.
  5. The nightly cron becomes: (a) select from embed_queue, (b) batch, (c) call the API, (d) UPDATE documents SET embedding = ..., embedded_at = now(), model_tag = ... for each returned vector. The remaining 98% of rows are never touched.

Output.

Architecture Docs re-embedded / night Tokens / month Monthly cost Savings
Full nightly re-embed 5,000,000 60,000,000,000 $1,200 baseline
Incremental (2% churn) 100,000 1,200,000,000 $24 98%
Incremental + batch tier 100,000 1,200,000,000 $12 99%

Rule of thumb. Never re-embed a corpus you have not verified is dirty. The content-hash + embedded_at cursor turns a linear-in-corpus job into a linear-in-churn job. The cost delta is usually 20× to 100×; the freshness guarantee is identical.

Worked example — mapping the four axes to a job flow

Detailed explanation. Senior interviewers often ask the candidate to sketch the pipeline diagram from first principles. The correct answer is a four-layer flow — ingest, change detection, embed, sync — with a drift monitor as a fifth sidecar. Every arrow in the flow is one of the four axes the interviewer is probing. Walk through the flow and name which axis each arrow addresses.

  • The ingest arrow addresses the incremental axis — CDC or ETL brings only changed rows into scope.
  • The embed arrow addresses the cost axis — batch, tier, model choice.
  • The write-back arrow addresses the versioning axis — the model_tag column tags every vector.
  • The drift-monitor sidecar addresses the drift axis — golden queries + recall@k.

Question. Draw the end-to-end embeddings pipeline for a knowledge-base RAG stack and annotate each edge with the axis it addresses.

Input.

Layer Responsibility Axis addressed
Source (Postgres knowledge_base) System of record —
CDC (Debezium) Change stream Incremental
Change queue SHA-256 hash diff, embed_queue view Incremental
Embed batch job Call embedding API in batches Cost
Vector store (pgvector) Serve retrieval —
Drift monitor Nightly golden-query recall@k Drift
Model migration job Shadow column re-embed Versioning

Code.

# End-to-end pipeline sketch — Prefect / Dagster / Airflow flavour agnostic

from dataclasses import dataclass

@dataclass
class Doc:
    doc_id: str
    content: str
    sha256_hash: str

def ingest():
    """CDC or ETL yields only rows that changed since last run."""
    for row in cdc_stream():
        yield Doc(row["doc_id"], row["content"], sha256(row["content"]))

def change_detect(docs):
    """Compare hash to last-seen; only forward changed rows."""
    for d in docs:
        if last_hash_for(d.doc_id) != d.sha256_hash:
            yield d

def embed_batch(docs, model="text-embedding-3-small", batch=256):
    """Batch call the embedding API for cost efficiency."""
    buf = []
    for d in docs:
        buf.append(d)
        if len(buf) == batch:
            yield from embed_and_tag(buf, model=model)
            buf = []
    if buf:
        yield from embed_and_tag(buf, model=model)

def write_back(rows):
    """Upsert vector + model_tag into pgvector."""
    upsert_pgvector(rows)

def drift_monitor():
    """Sidecar: nightly recall@k against a golden query set."""
    score = recall_at_k(golden_queries, k=10)
    emit_metric("embedding.recall_at_10", score)
    if score < 0.85:
        page_oncall("embedding drift alert")

# Drive
def refresh_pipeline():
    changed = change_detect(ingest())
    write_back(embed_batch(changed, model="text-embedding-3-small"))
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. ingest() reads only rows that changed since the last CDC checkpoint. This is the incremental boundary — the pipeline never sees the 98% of rows that did not change. Full-corpus scans are a cost bug; CDC is the cure.
  2. change_detect() guards against the case where an upstream job touched updated_at without actually changing the content (a common Postgres UPSERT footgun). Hash the content bytes; forward only genuine mismatches.
  3. embed_batch() accumulates 256 rows before calling the API. Providers charge per token, but network overhead per request is fixed; batching 256 rows into one HTTP call is 256× the throughput of one-row-per-call. Cost per document falls proportionally.
  4. write_back() upserts the vector and the model_tag in the same transaction. Every vector is stamped with the model that produced it; migrations later query on this tag to find "old-model" rows.
  5. drift_monitor() is a sidecar — it runs on a separate schedule, not in the write path. Its output is a metric, not a gate. The pipeline never blocks on drift; the pager fires if the trend crosses a threshold.

Output.

Edge From To Axis
ingest → change-detect Source Hash diff Incremental
change-detect → embed Queue API batch Incremental + cost
embed → write-back API pgvector Cost + versioning
write-back → serve pgvector Retrieval —
corpus → drift-monitor Serve path Metric Drift

Rule of thumb. Any embeddings pipeline missing any of the four arrows is a pipeline waiting for an incident. Draw the diagram before you write the first line of code; label the axes; check them off in every design review.

Worked example — GDPR erasure request through the pipeline

Detailed explanation. A user submits a GDPR erasure request. Their record in Postgres is deleted. If the vector store still has their embedding, retrieval can leak content the user has legally requested erased. The pipeline must propagate the delete end-to-end. Walk through the tombstone pattern that makes deletes as first-class as inserts.

  • The compliance requirement. GDPR Article 17 mandates deletion "without undue delay." Practical SLA is 24 hours.
  • The naive bug. The nightly re-embed skips deleted rows and never issues a delete on the vector store. The vector lives on.
  • The fix. A tombstone row (or a deleted_at column) drives an explicit delete on the vector store.

Question. Extend the pipeline so a Postgres row deletion results in a Pinecone / pgvector delete within 15 minutes. Show the schema, the delta stream, and the vector-store call.

Input.

Component Setting
Corpus size 5M docs
Delete rate ~100/day (steady state), spikes on legal takedown days
Compliance SLA 24 hours (aim: 15 minutes)
Vector store pgvector

Code.

-- Soft-delete pattern: tombstone rather than DELETE
ALTER TABLE documents ADD COLUMN deleted_at TIMESTAMPTZ;

CREATE OR REPLACE VIEW embed_delete_queue AS
SELECT doc_id
FROM   documents
WHERE  deleted_at IS NOT NULL
  AND  embedding IS NOT NULL;   -- still has a vector to remove

-- After the sync job runs, wipe the vector column and mark the row purged
-- (deleted_at is kept for audit; embedding is NULL to mark tombstone processed)
Enter fullscreen mode Exit fullscreen mode 
# Deletion sync — small, frequent job
import psycopg2

def sync_deletes(batch=200):
    conn = psycopg2.connect("postgres://svc@db.internal/analytics")
    conn.autocommit = False
    cur  = conn.cursor()
    cur.execute("""
        SELECT doc_id
        FROM   embed_delete_queue
        LIMIT  %s
        FOR UPDATE SKIP LOCKED
    """, (batch,))
    to_purge = [row[0] for row in cur.fetchall()]
    if not to_purge:
        conn.rollback()
        return 0

    # Vector-store delete (pgvector: just NULL the column and DELETE from mirrors)
    cur.execute("""
        UPDATE documents
        SET    embedding  = NULL,
               model_tag  = NULL
        WHERE  doc_id = ANY(%s)
    """, (to_purge,))
    conn.commit()
    return len(to_purge)
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The system-of-record row is never hard-deleted immediately. Instead, the app writes deleted_at = now() — a soft-delete tombstone. This preserves audit lineage while flagging the row for downstream propagation.
  2. The embed_delete_queue view isolates rows that are tombstoned and still carry a vector. Once the delete job runs and NULLs the vector, the row drops out of the view — the queue self-drains.
  3. The sync job runs every minute (or on CDC event). It selects a batch with FOR UPDATE SKIP LOCKED — safe under concurrency, so multiple worker instances can run without stomping on the same row.
  4. The vector-store call is a single UPDATE documents SET embedding = NULL in pgvector. For Pinecone, it would be index.delete(ids=to_purge). For Weaviate, a batch DELETE by ID.
  5. The deleted_at column stays populated forever — it is the audit trail. embedding = NULL is the "processed" flag. If a future engineer needs to prove GDPR compliance, the query is SELECT doc_id, deleted_at FROM documents WHERE embedding IS NULL AND deleted_at IS NOT NULL.

Output.

Event Action SLA
User erasure request UPDATE documents SET deleted_at = now() seconds
embed_delete_queue drains Vector NULL'd, mirrors purged 1–15 min
Retrieval no longer returns the vector pgvector query skips NULL vectors immediate after sync
Audit trail deleted_at column retained forever

Rule of thumb. Model deletes as first-class events, not as "the absence of an insert." The tombstone pattern makes compliance auditable and lets the same pipeline handle inserts, updates, and deletes with the same change-queue mechanism.

Senior interview question on the four axes of embedding refresh

A senior interviewer often opens with: "You inherit a RAG stack that re-embeds the entire 10-million-document corpus every night on text-embedding-3-small. The CFO wants the OpenAI bill cut 90%. The PM wants freshness under 15 minutes. Walk me through the architecture you ship, what you measure, and how you'd sequence the migration."

Solution Using a content-hash change queue, batch pricing tier, and CDC-driven incremental refresh 

# Blueprint — the four-axis embeddings refresh pipeline

# 1. INGEST (incremental)  — CDC stream from Postgres via Debezium
# 2. CHANGE DETECT         — SHA-256 hash + embedded_at cursor
# 3. EMBED BATCH           — text-embedding-3-small, batch=256, batch-tier pricing
# 4. WRITE-BACK            — pgvector upsert + model_tag column
# 5. DRIFT MONITOR         — nightly recall@10 on golden queries; alert < 0.85

import hashlib
from typing import Iterable
import openai
import psycopg2
from psycopg2.extras import execute_values

MODEL   = "text-embedding-3-small"
DIMS    = 1536
BATCH   = 256

def sha256_of(text: str) -> str:
    return hashlib.sha256(text.encode("utf-8")).hexdigest()

def stream_changed_rows() -> Iterable[dict]:
    """Debezium → Kafka → this consumer. Yields only rows whose content changed."""
    for msg in kafka_consume("cdc.documents"):
        row = msg.value
        new_hash = sha256_of(row["content"])
        if new_hash != row.get("sha256_hash"):
            row["sha256_hash"] = new_hash
            yield row

def embed_batch(rows: list[dict]) -> list[dict]:
    texts = [r["content"] for r in rows]
    resp  = openai.embeddings.create(model=MODEL, input=texts)
    for row, item in zip(rows, resp.data):
        row["embedding"]  = item.embedding
        row["model_tag"]  = f"{MODEL}@v1"
    return rows

def upsert(conn, rows: list[dict]) -> None:
    values = [(r["doc_id"], r["sha256_hash"], r["embedding"], r["model_tag"]) for r in rows]
    execute_values(conn.cursor(), """
        INSERT INTO documents (doc_id, sha256_hash, embedding, model_tag, embedded_at)
        VALUES %s
        ON CONFLICT (doc_id) DO UPDATE
          SET sha256_hash = EXCLUDED.sha256_hash,
              embedding   = EXCLUDED.embedding,
              model_tag   = EXCLUDED.model_tag,
              embedded_at = now()
    """, values, template="(%s, %s, %s, %s, now())")
    conn.commit()

def run():
    conn = psycopg2.connect("postgres://svc@db.internal/rag")
    buf: list[dict] = []
    for row in stream_changed_rows():
        buf.append(row)
        if len(buf) == BATCH:
            upsert(conn, embed_batch(buf))
            buf = []
    if buf:
        upsert(conn, embed_batch(buf))
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Step Before (nightly full) After (CDC + hash)
Docs embedded / day 10,000,000 200,000 (2% churn)
Tokens / month 120 billion 2.4 billion
Monthly cost @ $0.00002/1K $2,400 $48
Batch-tier savings (50%) n/a $24
Freshness (avg age of embedding) 12 hours < 15 minutes
Model-tag column on every row no yes
Drift monitor absent nightly recall@10

After the rollout, the OpenAI invoice drops from $2,400 to under $50 per month, freshness improves from a 12-hour average to sub-15-minute p99, and every row carries its own model_tag so the next model migration is a controlled shadow-column rollout instead of a full-corpus outage.

Output:

Metric Before After
Docs re-embedded / day 10M 200K
Monthly cost $2,400 $24 (batch tier)
Embedding freshness p99 12 h < 15 min
Model tag on every row no yes
Drift alerting none recall@10 nightly

Why this works — concept by concept:

  • Content hash as the primary lever — the SHA-256 diff means the pipeline pays only for the rows that changed. On a corpus with 2% daily churn, this is a 50× reduction in tokens and dollars.
  • CDC over polling — Debezium (or Postgres logical decoding) delivers changes with sub-second latency and zero query load on the source. Polling every 5 minutes with WHERE updated_at > $cursor works but adds warehouse load; CDC is the right pattern past 1M rows.
  • Batch pricing tier — OpenAI and Cohere both offer a batch-inference tier at roughly 50% of real-time pricing. For refresh workloads (as opposed to inference workloads), the 24-hour turnaround is a non-issue. Free 50% by moving refresh onto the batch tier.
  • Model_tag column — every vector is stamped with the exact model + version that produced it. Migration to a new model is now a "re-embed rows where model_tag != current_model" job; rollback is an UPDATE documents SET embedding = old_embedding WHERE model_tag = 'ada-002@v1' if the shadow column was kept.
  • Cost — the pipeline's runtime cost is O(changed_rows) per day, not O(corpus_size). On a 10M-doc corpus with 2% churn that is a 50× cost reduction with no loss of freshness. The drift-monitor sidecar adds negligible cost (one golden-query call per night); the compliance win of the tombstone pattern is priceless.

SQL
Topic — sql
SQL problems on change-detection queries and hash diffs

Practice →

ETL Topic — etl ETL problems on incremental refresh pipelines

Practice →

2. Change detection + incremental refresh

Content hash + embedded_at cursor is the whole incremental story — everything else is a variation

The mental model in one line: an incremental embedding job is a WHERE embedded_at < updated_at query with a SHA-256 content hash to guard against false positives, and everything more elaborate — CDC streams, Kafka fanouts, materialised change queues — is an optimisation of the same primitive. Get the primitive right and the rest of the pipeline follows; get it wrong and you either miss updates or re-embed the whole corpus.

Iconographic change detection diagram — a document table with a sha256_hash column, a diff-glyph comparing old vs new hash, and a re-embed queue on the right, with SHA-256, changed, and re-embed labels, on a light PipeCode card.

The four "must-answer" axes for change detection.

  • Change signal. What tells you a document changed? Options: content hash, updated_at timestamp, CDC event, ETL delta table. Each has a false-positive / false-negative profile. Hash is the safest; timestamp is the cheapest; CDC is the freshest.
  • Change queue. Where do you materialise the "these rows need re-embedding" list? Options: a view (embed_queue), a Kafka topic, a Redis stream, a Postgres queue table. Materialisation is the operational lever — a view is the simplest, a Kafka topic is the highest-throughput.
  • Batch cadence. How often do you drain the queue? Options: minute-level for fresh corpora, hourly for slow-moving, nightly for stable. Cadence is the freshness knob — tune it against the SLA and the cost tier.
  • Idempotency + retry. What happens if the embed call fails halfway through the batch? The queue must survive a crash, and re-runs must not double-embed. FOR UPDATE SKIP LOCKED on the queue rows is the standard Postgres pattern.

Change signal — the four sources.

  • Content hash. Cheapest first pass. sha256_hash = sha256(content) computed at write time or by a CDC transformer. Compare the new hash to the last-known hash; if different, flag for re-embed. Immune to spurious updated_at bumps.
  • updated_at timestamp. Every OLTP schema already has this. Query WHERE embedded_at < updated_at — trivial. Downside: an UPDATE documents SET updated_at = now() WHERE ... that touched no content still flags the row.
  • CDC event. Debezium / logical decoding streams every row change with sub-second latency. Highest freshness; requires infrastructure (Kafka + Debezium + a consumer).
  • ETL delta table. For batch-heavy stacks (dbt / Airflow), a nightly changed_docs table is idiomatic. Simple, but freshness is bound by the batch cadence.

Change-queue materialisation — four options.

  • View (embed_queue). CREATE VIEW ... AS SELECT ... WHERE embedded_at < updated_at. Zero storage; the embed worker just queries the view. Best for small-to-medium corpora with < 1M change events per day.
  • Postgres queue table. INSERT INTO embed_queue (doc_id) ... on write; worker DELETE ... RETURNING .... Durable, transactional, easy to reason about. The standard pattern for team-managed pipelines.
  • Kafka topic. CDC → Kafka → embed consumer. Highest throughput; scales to millions of events per hour. Adds operational overhead (Kafka + schema registry + consumer offsets).
  • Redis stream / Sidekiq / SQS. For non-Postgres stacks. Same semantics as the Kafka option, different infrastructure.

Cadence — matching the tier to the SLA.

  • Minute-level. Product catalogues (price / stock changes), news feeds, user-generated content. The freshness SLA is single-digit minutes; the pipeline runs continuously or every 60 seconds.
  • Hourly. Documentation, wiki content, internal knowledge bases. 1-hour p99 freshness is fine; hourly cron jobs are the simplest fit.
  • Nightly. Reference data (product taxonomies, employee directories, static help centre articles). Once a day is plenty; run overnight to align with the batch-tier pricing window.
  • On-demand. Manual refresh triggered by an editor's "publish" button, dry-run in a staging environment, or a compliance takedown that needs immediate propagation.

Idempotency — the boring but critical part.

  • FOR UPDATE SKIP LOCKED. Standard Postgres pattern for a queue table. Multiple workers can drain the queue in parallel without stepping on each other's toes.
  • Idempotency key. Each embed call is keyed by (doc_id, sha256_hash). A retry re-computes the same vector; the upsert is a no-op if the vector already exists.
  • Advisory locks for whole-corpus operations. A model migration that touches every row should acquire pg_advisory_xact_lock(hashtext('embed_migration_v3')) — prevents two migration jobs racing.
  • Poison-pill handling. A document whose content triggers a provider error (bad UTF-8, oversized, blocked by content policy) should not permanently jam the queue. Track embed_attempts and route rows past 3 failures to a dead-letter table.

Common interview probes on change detection.

  • "Why hash the content instead of just trusting updated_at?" — spurious touches, ETL writes that bump updated_at without content change.
  • "How does your queue survive a worker crash mid-batch?" — FOR UPDATE SKIP LOCKED + transactional consume + retry-safe upsert.
  • "When do you switch from a view to a Kafka topic?" — throughput; roughly > 10K change events per hour on the Postgres side.
  • "How do you handle deletes?" — tombstone pattern with a deleted_at column; the vector-store delete is a separate sync.

Worked example — SHA-256 hash column and the embed_queue view

Detailed explanation. The simplest possible incremental refresh: add a sha256_hash column to the source table, compute it in Postgres or the ETL, and select the rows whose hash advanced past the embedded snapshot. The view + worker pattern handles corpora up to about 1M change events per day without any additional infrastructure.

  • The schema. documents(doc_id PK, content, sha256_hash, embedding, model_tag, updated_at, embedded_at).
  • The view. embed_queue AS SELECT ... WHERE embedded_at IS NULL OR embedded_at < updated_at.
  • The worker. SELECT ... FROM embed_queue FOR UPDATE SKIP LOCKED LIMIT 256.

Question. Design the schema, the view, the worker, and the retry-safe upsert for an incremental refresh on a 500K-doc corpus with 5% daily churn.

Input.

Parameter Value
Corpus 500K docs
Daily churn 5% (25K rows)
Change signal content hash + updated_at
Freshness SLA 15 minutes
Worker cadence every 60 seconds

Code.

-- Schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE documents (
  doc_id        BIGINT PRIMARY KEY,
  content       TEXT NOT NULL,
  sha256_hash   TEXT NOT NULL,
  embedding     vector(1536),
  model_tag     TEXT,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  embedded_at   TIMESTAMPTZ,
  embed_attempts INT NOT NULL DEFAULT 0
);

CREATE INDEX documents_embed_queue
  ON documents (updated_at)
  WHERE embedded_at IS NULL OR embedded_at < updated_at;

-- Trigger keeps sha256_hash in sync with content
CREATE OR REPLACE FUNCTION doc_hash_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.content IS DISTINCT FROM OLD.content OR OLD IS NULL THEN
    NEW.sha256_hash := encode(digest(NEW.content, 'sha256'), 'hex');
    NEW.updated_at  := now();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_doc_hash
  BEFORE INSERT OR UPDATE ON documents
  FOR EACH ROW EXECUTE FUNCTION doc_hash_trigger();

-- Queue view
CREATE OR REPLACE VIEW embed_queue AS
SELECT doc_id, content, sha256_hash
FROM   documents
WHERE  (embedded_at IS NULL OR embedded_at < updated_at)
  AND  embed_attempts < 3;
Enter fullscreen mode Exit fullscreen mode 
# Worker — every 60 s
import psycopg2, hashlib, openai
from psycopg2.extras import execute_batch

BATCH = 256
MODEL = "text-embedding-3-small"

def run_batch(conn):
    cur = conn.cursor()
    cur.execute("""
        SELECT doc_id, content, sha256_hash
        FROM   documents
        WHERE  embedded_at IS NULL OR embedded_at < updated_at
        ORDER  BY updated_at
        LIMIT  %s
        FOR UPDATE SKIP LOCKED
    """, (BATCH,))
    rows = cur.fetchall()
    if not rows:
        conn.rollback()
        return 0

    texts = [r[1] for r in rows]
    resp  = openai.embeddings.create(model=MODEL, input=texts)
    updates = [
        (item.embedding, f"{MODEL}@v1", rows[i][0])
        for i, item in enumerate(resp.data)
    ]
    execute_batch(cur, """
        UPDATE documents
        SET    embedding    = %s,
               model_tag    = %s,
               embedded_at  = now(),
               embed_attempts = 0
        WHERE  doc_id = %s
    """, updates)
    conn.commit()
    return len(rows)
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The documents table stores the content, a hash of the content, the vector, the model tag that produced the vector, and two timestamps: updated_at (bumped on content change) and embedded_at (bumped after a successful embed).
  2. The BEFORE INSERT / UPDATE trigger computes sha256_hash in Postgres itself. This guards against ETL writes that forget to compute the hash and against a bug in the application layer — the source of truth for "the content changed" is a Postgres invariant.
  3. The partial index on (updated_at) WHERE embedded_at IS NULL OR embedded_at < updated_at makes the queue query O(queue_depth), not O(corpus). On a 500K-doc corpus with a 25K/day queue, the index is 25K entries — fits in memory, scans in milliseconds.
  4. The worker runs every 60 seconds, selects up to 256 rows with FOR UPDATE SKIP LOCKED, calls the embedding API, and updates the vector + embedded_at in one transaction. If the API call fails, the transaction rolls back and the rows return to the queue.
  5. embed_attempts < 3 in the view guards against poison pills — a document that persistently fails is dropped from the queue after 3 attempts and a nightly job routes it to a dead-letter table for manual triage.

Output.

Cadence Queue depth (avg) Freshness p99 Cost / day
Nightly full re-embed n/a 24 h $12
Hourly incremental 1,000 60 min $0.60
60-second incremental 25 60 s $0.60

Rule of thumb. For any corpus over ~50K rows, the hash column + queue view + partial index pattern is the right default. The trigger keeps the hash trustworthy; the partial index keeps the queue query fast; FOR UPDATE SKIP LOCKED keeps the workers safe.

Worked example — CDC-triggered incremental refresh with Debezium + Kafka

Detailed explanation. For high-throughput corpora (millions of change events per day) or teams already running Kafka, CDC is the higher-leverage pattern. Debezium captures the Postgres write-ahead log, emits one Kafka message per row change, and a consumer batches messages, calls the embedding API, and upserts. Freshness is sub-second; throughput scales horizontally by consumer group size.

  • CDC source. Debezium Postgres connector reads WAL via logical decoding.
  • Kafka topic. cdc.documents — one message per row change.
  • Consumer. Kafka consumer group runs N workers; each drains a partition, batches 256, embeds, upserts.

Question. Wire Debezium → Kafka → embed consumer for a corpus with 500K change events per day. Show the Debezium config, the Kafka schema, and the consumer.

Input.

Component Value
Corpus size 20M docs
Change events / day 500,000
Freshness SLA 30 seconds
Kafka partitions 8
Consumer group size 4 workers

Code.

# Debezium Postgres connector config
name: pg-docs-connector
config:
  connector.class: io.debezium.connector.postgresql.PostgresConnector
  database.hostname: db.internal
  database.port: 5432
  database.user: debezium
  database.dbname: rag
  database.server.name: rag_pg
  table.include.list: public.documents
  plugin.name: pgoutput
  publication.autocreate.mode: filtered
  slot.name: debezium_documents
  # Emit only the fields we need for embedding
  transforms: unwrap
  transforms.unwrap.type: io.debezium.transforms.ExtractNewRecordState
  transforms.unwrap.drop.tombstones: false
Enter fullscreen mode Exit fullscreen mode 
# Embed consumer — 1 worker per Kafka partition
from kafka import KafkaConsumer
import hashlib, json, openai, psycopg2
from psycopg2.extras import execute_values

MODEL = "text-embedding-3-small"
BATCH = 256

def sha256(text: str) -> str:
    return hashlib.sha256(text.encode("utf-8")).hexdigest()

def consume():
    consumer = KafkaConsumer(
        "cdc.documents",
        bootstrap_servers="kafka:9092",
        group_id="embed-worker",
        auto_offset_reset="earliest",
        enable_auto_commit=False,
        max_poll_records=BATCH,
    )
    conn = psycopg2.connect("postgres://embed@db.internal/rag")
    for msgs in batches(consumer, BATCH):
        rows = []
        for m in msgs:
            payload = json.loads(m.value)
            if payload.get("__op") == "d":       # delete tombstone
                rows.append({"doc_id": payload["doc_id"], "op": "delete"})
                continue
            content = payload["content"]
            new_hash = sha256(content)
            if new_hash == payload.get("sha256_hash"):
                continue                          # hash matches → skip
            rows.append({
                "doc_id":      payload["doc_id"],
                "content":     content,
                "sha256_hash": new_hash,
                "op":          "upsert",
            })
        if rows:
            apply(conn, rows)
        consumer.commit()

def apply(conn, rows):
    upserts = [r for r in rows if r["op"] == "upsert"]
    deletes = [r["doc_id"] for r in rows if r["op"] == "delete"]

    if upserts:
        resp = openai.embeddings.create(
            model=MODEL,
            input=[r["content"] for r in upserts],
        )
        values = [
            (r["doc_id"], r["sha256_hash"], item.embedding, f"{MODEL}@v1")
            for r, item in zip(upserts, resp.data)
        ]
        execute_values(conn.cursor(), """
            INSERT INTO documents (doc_id, sha256_hash, embedding, model_tag, embedded_at)
            VALUES %s
            ON CONFLICT (doc_id) DO UPDATE
              SET sha256_hash = EXCLUDED.sha256_hash,
                  embedding   = EXCLUDED.embedding,
                  model_tag   = EXCLUDED.model_tag,
                  embedded_at = now()
        """, values, template="(%s, %s, %s, %s, now())")

    if deletes:
        conn.cursor().execute(
            "UPDATE documents SET embedding = NULL, model_tag = NULL WHERE doc_id = ANY(%s)",
            (deletes,),
        )

    conn.commit()
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Debezium tails the Postgres WAL via logical decoding on the debezium_documents replication slot. Every INSERT / UPDATE / DELETE against public.documents becomes a message on the cdc.documents Kafka topic. There is no polling; latency from row change to Kafka is sub-second.
  2. The ExtractNewRecordState transform unwraps the Debezium envelope, leaving a flat JSON payload (doc_id, content, sha256_hash, __op) that the consumer can process directly.
  3. The consumer runs in a group of 4 workers across 8 partitions — 2 partitions per worker. Kafka's partition assignment guarantees that no two workers process the same doc_id concurrently, eliminating write-conflict handling.
  4. The consumer batches up to 256 messages, filters out no-op events (hash unchanged), calls the OpenAI embedding API once for the whole batch, and upserts via execute_values with ON CONFLICT DO UPDATE. enable_auto_commit = False + explicit consumer.commit() after the DB commit gives exactly-once semantics at the consumer boundary.
  5. Deletes are handled by the same consumer via the Debezium tombstone (__op == 'd'). The vector is NULL'd; the tombstone in Postgres remains as an audit record.

Output.

Metric Polling every 60 s Debezium CDC
Freshness p99 60 s < 2 s
Source-DB query load 1440 queries/day 0 (WAL tail)
Throughput ceiling limited by query horizontal (partition ↑)
Operational cost trivial Kafka + Debezium infra

Rule of thumb. Move to CDC when either freshness needs to be sub-minute or when the source-DB load from polling becomes non-trivial. Under those thresholds, the queue-view pattern is cheaper to run and easier to reason about.

Worked example — nightly batch refresh for a slow-moving corpus

Detailed explanation. Not every corpus needs sub-second freshness. A legal knowledge base, a static help centre, a reference product taxonomy — these change once a week at most. For slow-moving corpora, a nightly batch job runs on the cheapest tier, aligns with the 24-hour batch pricing window, and eliminates the operational overhead of a streaming pipeline. Walk through the batch pattern.

  • The corpus. 2M docs; change rate ~500 rows/week; freshness SLA 24 hours is fine.
  • The tier. OpenAI batch API — 50% cheaper than real-time inference.
  • The cadence. One nightly job that submits the batch and polls for completion.

Question. Ship a nightly batch job that finds the changed rows, submits them to the OpenAI batch API, and upserts the results the next morning.

Input.

Parameter Value
Corpus size 2M docs
Weekly churn 500 rows (~70/day)
Freshness SLA 24 hours
Tier OpenAI batch API (50% off)

Code.

# Nightly batch job — submitted at 22:00; results polled at 06:00 next day
import json, tempfile, openai, psycopg2

MODEL = "text-embedding-3-small"

def build_batch_file(conn) -> str:
    """Write a JSONL file with one request per changed row."""
    cur = conn.cursor()
    cur.execute("""
        SELECT doc_id, content, sha256_hash
        FROM   documents
        WHERE  embedded_at IS NULL OR embedded_at < updated_at
        ORDER  BY doc_id
    """)
    f = tempfile.NamedTemporaryFile("w", suffix=".jsonl", delete=False)
    for doc_id, content, sha in cur:
        f.write(json.dumps({
            "custom_id": f"{doc_id}::{sha}",
            "method":    "POST",
            "url":       "/v1/embeddings",
            "body":      {"model": MODEL, "input": content},
        }) + "\n")
    f.close()
    return f.name

def submit_batch(path: str) -> str:
    upload = openai.files.create(file=open(path, "rb"), purpose="batch")
    batch  = openai.batches.create(
        input_file_id=upload.id,
        endpoint="/v1/embeddings",
        completion_window="24h",
    )
    return batch.id

def poll_and_apply(batch_id: str, conn):
    batch = openai.batches.retrieve(batch_id)
    if batch.status != "completed":
        return False
    output = openai.files.content(batch.output_file_id).text
    cur    = conn.cursor()
    for line in output.strip().splitlines():
        rec       = json.loads(line)
        custom_id = rec["custom_id"]
        doc_id, sha = custom_id.split("::", 1)
        embedding   = rec["response"]["body"]["data"][0]["embedding"]
        cur.execute("""
            UPDATE documents
            SET    embedding    = %s,
                   model_tag    = %s,
                   embedded_at  = now()
            WHERE  doc_id = %s AND sha256_hash = %s
        """, (embedding, f"{MODEL}@v1", int(doc_id), sha))
    conn.commit()
    return True
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The build step queries the same embed_queue predicate as the streaming pipeline — WHERE embedded_at IS NULL OR embedded_at < updated_at. Slow-moving or fast-moving, the change-detection SQL is identical; only the cadence differs.
  2. The batch file is JSONL — one line per row. The custom_id embeds both the doc_id and the sha256_hash. On apply, the sha guard prevents a stale batch result from clobbering a newer real-time embed (should the corpus later add streaming on top).
  3. Submission is a two-step upload + create. The completion_window = "24h" tag opts into batch-tier pricing — 50% off real-time. OpenAI processes the batch on their own schedule and posts results within 24 hours.
  4. The 06:00 poll retrieves the batch status; if completed, it downloads the output JSONL and applies the vectors. If not, the poll job re-schedules itself in an hour and retries.
  5. The sha256_hash = sha predicate in the UPDATE is the key idempotency guard: it ensures a batch result is only applied if the row's content hash still matches what was submitted. If a real-time job embedded the same row in the meantime, the batch result is silently dropped.

Output.

Approach Cost per 1M rows Freshness Ops overhead
Nightly real-time $8 ~24 h low
Nightly batch tier $4 ~24 h low
Streaming CDC + real-time $8 < 2 s high

Rule of thumb. For any corpus with freshness SLA > 6 hours, use the batch tier. The 50% savings are unconditional; the operational overhead is zero beyond a nightly cron.

Senior interview question on change detection strategies

A senior interviewer might ask: "Your corpus is a 20-million-row product catalogue with a 1% daily change rate. Freshness SLA is 5 minutes for prices and stock, 4 hours for descriptions, 24 hours for taxonomy. Walk me through the three-tier refresh architecture, the change queues, and how you keep the cost under $100/month."

Solution Using a three-tier change-detection pipeline (streaming + hourly + nightly batch) 

# Three-tier refresh architecture

# Tier 1 — STREAMING (freshness 5 min)     — CDC → Kafka → real-time embed
#   scope: price + stock + availability text
# Tier 2 — HOURLY (freshness 4 h)          — queue view + real-time embed
#   scope: product description + specifications
# Tier 3 — NIGHTLY BATCH (freshness 24 h)  — queue view + batch-tier API
#   scope: taxonomy + brand + category text
Enter fullscreen mode Exit fullscreen mode 
-- Split queue views by content field group
CREATE OR REPLACE VIEW embed_queue_stream AS
SELECT doc_id, price_and_stock_text AS content
FROM   products
WHERE  embed_stream_at IS NULL
   OR  embed_stream_at < price_and_stock_updated_at;

CREATE OR REPLACE VIEW embed_queue_hourly AS
SELECT doc_id, description_text AS content
FROM   products
WHERE  embed_desc_at IS NULL
   OR  embed_desc_at < description_updated_at;

CREATE OR REPLACE VIEW embed_queue_nightly AS
SELECT doc_id, taxonomy_text AS content
FROM   products
WHERE  embed_tax_at IS NULL
   OR  embed_tax_at < taxonomy_updated_at;
Enter fullscreen mode Exit fullscreen mode 
# Schedule
tier_stream:
  trigger: kafka topic cdc.products (Debezium)
  worker:  4 consumers, batch=256, target latency 5 min
  api:     real-time text-embedding-3-small

tier_hourly:
  trigger: cron 0 * * * *
  worker:  1 job, batch=256, target latency 4 h
  api:     real-time text-embedding-3-small

tier_nightly:
  trigger: cron 30 22 * * *
  worker:  submit batch → poll at 06:00
  api:     OpenAI batch API text-embedding-3-small (-50%)
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Tier Scope Daily rows Freshness Cost
Streaming price/stock text ~180K 5 min ~$3.60/day
Hourly description ~15K 4 h ~$0.30/day
Nightly batch taxonomy ~5K 24 h ~$0.05/day (batch)
Total ~200K mixed ~$4/day ≈ $120/mo

After the rollout, the pipeline hits every freshness SLA at its cheapest possible tier. The streaming layer costs the most per row but only handles the 1% of content that genuinely needs sub-minute freshness. The batch layer covers 60% of the change volume at half the price.

Output:

Surface Result
Price/stock freshness p99 3.5 min
Description freshness p99 45 min
Taxonomy freshness p99 22 h
Total monthly OpenAI cost $118
Old cost (single-tier real-time nightly full re-embed) $2,400

Why this works — concept by concept:

  • Match freshness SLA to tier — not every field needs the same cadence. Splitting the queue by content-field group and running each at its cheapest tier is a 20× cost lever with zero SLA loss.
  • Per-field embedded_at cursor — embed_stream_at, embed_desc_at, embed_tax_at are independent freshness cursors. A price change re-embeds the stream-tier vector without touching the hourly or nightly ones.
  • Batch tier for the tail — 60% of the change volume is taxonomy that changes once a week and does not need sub-day freshness. Send it to the batch API at 50% off; save $1,200/month.
  • CDC only where needed — streaming CDC has real infra overhead (Kafka, Debezium, consumer groups). Confine it to the fields that need it. The description and taxonomy tiers can run on plain cron + Postgres.
  • Cost — O(changed_rows_per_tier × per-tier price). The three-tier split matches the cost of each change event to its actual freshness requirement. The alternative (single-tier real-time on everything) is 20× more expensive with no SLA gain.

SQL
Topic — sql
SQL problems on change-queue views and CDC

Practice →

ETL Topic — etl ETL problems on multi-tier refresh cadence

Practice →

3. Cost accounting — dollars per doc

Every embedding request is billed in tokens, not documents — the senior mental model is cost = tokens × rate × tier_multiplier

The mental model in one line: embedding cost is tokens × rate × tier_multiplier, where the rate depends on the model, the tier multiplier is 1.0 for real-time inference and 0.5 for batch, and tokens is the sum of BPE-tokenized input across every document. Every other cost discussion — model choice, self-host vs managed, batch tier — is a variation on plugging different numbers into this formula.

Iconographic cost diagram — a gauge dial for cost per doc, a doc-stack showing 10M rows × $0.0001, and a batch-pricing tier ladder with real-time, batch (-50%), and self-host BGE/E5, on a light PipeCode card.

The four "must-answer" axes for cost.

  • Model choice. OpenAI text-embedding-3-small ($0.00002/1K tokens), text-embedding-3-large ($0.00013/1K), Cohere embed-v4 ($0.00010/1K), or a self-hosted BGE-large / E5-large / GTE-large. Each has a distinct quality-per-dollar profile.
  • Tier. Real-time (full price, sub-second) vs batch (half price, up to 24-hour turnaround). Refresh workloads should default to batch unless a freshness SLA forbids it.
  • Token accounting. Providers bill on the BPE token count of the input, not the document count. Average English text is roughly 1.3 tokens per word; long-form docs punch above their doc count on the invoice.
  • TCO for self-hosted. GPU rental (H100, A10, L4) + engineering time + Kubernetes overhead. Cheaper only when scale justifies — typically past ~100M embeddings per month.

Model pricing snapshot (2026).

  • OpenAI text-embedding-3-small. 1536 dims. $0.00002 / 1K tokens. The workhorse for RAG in 2026.
  • OpenAI text-embedding-3-large. 3072 dims. $0.00013 / 1K tokens. 6.5× the price for typically 5–10% better recall — worth it for legal, medical, or high-stakes retrieval; overkill for most consumer RAG.
  • Cohere embed-v4. Multiple dimension options. ~$0.00010 / 1K. Strong on multilingual and code.
  • Voyage AI voyage-3. ~$0.00006 / 1K. Competitive on English general knowledge.
  • Self-hosted BGE-large-en-v1.5. 1024 dims. Runs on a single L4 GPU at ~1000 tokens/sec/instance. TCO at scale is roughly $0.000005 / 1K including GPU + orchestration — 4× cheaper than OpenAI at ≥100M-embeddings-per-month scale.
  • Self-hosted E5-large-v2. 1024 dims. Similar profile to BGE; slightly stronger on retrieval benchmarks.

Batch pricing — the unconditional 50% off.

  • OpenAI batch API. Same models, 50% off, 24-hour completion window. For refresh workloads, this is free money.
  • Anthropic batch API. (Message inference; not embeddings, but the pattern is identical.)
  • Cohere batch mode. ~50% off real-time. Enable via header on the request.
  • Self-hosted. No tier concept; you own the hardware. But you can shift work to off-peak spot instances for a similar effective discount.

Token accounting — the number you actually pay for.

  • Rule of thumb. English text is ~1.3 tokens/word or ~4 chars/token. A 400-word doc is ~520 tokens.
  • Chunking effect. RAG systems often chunk documents into ~500-token chunks before embedding. A 4000-token doc becomes 8 embed requests — 8× the token count of a doc-level embed. Chunk strategy directly affects cost.
  • Locale multiplier. Non-English text (especially CJK) tokenizes at 2–4× the token count of the equivalent English content. Bill accordingly.
  • Provider tokenizer differs. OpenAI uses cl100k_base; Cohere uses its own BPE; Voyage differs again. Costs are model-specific even when the character count is identical.

Self-host TCO — when to break out of the managed API.

  • GPU. An L4 GPU on GCP is ~$0.50/hr on-demand, ~$0.20/hr spot. One L4 embeds ~1000 tokens/sec on BGE-large.
  • Throughput. 1000 tokens/sec × 3600 sec = 3.6M tokens/hour per GPU. 24×7 → 86M tokens/day → 2.6B tokens/month per GPU.
  • Cost. $0.20/hr × 720 hr = $144/month per L4 spot instance. $144 / 2.6B tokens ≈ $0.00000005 / token = $0.00005 / 1K tokens.
  • Crossover. OpenAI text-embedding-3-small at $0.00002 / 1K tokens vs self-host at $0.00005 / 1K on paper — but that ignores engineering time, orchestration overhead, and quality gap. Realistic crossover is ~100M embeddings/month (a very large corpus).

Common interview probes on cost.

  • "What does an embedding refresh cost for 10M docs?" — walk through the token × rate × tier math.
  • "When would you self-host instead of using OpenAI?" — throughput past ~100M embeddings/month + engineering team with GPU/K8s ops.
  • "What's the batch-tier discount?" — 50% off, 24-hour turnaround.
  • "How do you account for chunked docs?" — sum tokens across chunks; chunk count is a cost multiplier.

Worked example — cost math for a 10M-doc corpus with 1 refresh per month

Detailed explanation. A textbook cost problem. A 10-million-document corpus needs a full re-embed once per month (for a model bump, or as a periodic freshness reset). Average document is 500 tokens. Compare text-embedding-3-small, text-embedding-3-large, and self-hosted BGE-large on price. Show the batch-tier discount.

  • Total tokens. 10M docs × 500 tokens = 5B tokens per full refresh.
  • Real-time small. 5B × $0.00002 / 1K = $100.
  • Real-time large. 5B × $0.00013 / 1K = $650.
  • Batch small. $100 × 0.5 = $50.
  • Self-host BGE. 5B tokens / 3.6M tokens/hr = 1389 GPU-hours; at $0.20/hr spot = $278. Plus engineering / orchestration.

Question. Compare the four options on price and quality, and recommend the pick for a mid-size RAG stack that refreshes monthly.

Input.

Parameter Value
Corpus 10M docs
Avg tokens per doc 500
Refresh cadence monthly
Total tokens per refresh 5B
L4 spot price $0.20/hr
BGE throughput 1000 tokens/sec/GPU

Code.

def total_tokens(docs, tokens_per_doc):
    return docs * tokens_per_doc

def openai_cost(tokens, price_per_1k, batch=False):
    multiplier = 0.5 if batch else 1.0
    return tokens * price_per_1k / 1000 * multiplier

def self_host_cost(tokens, tokens_per_sec_per_gpu, gpu_hourly):
    gpu_hours = tokens / tokens_per_sec_per_gpu / 3600
    return gpu_hours * gpu_hourly

T = total_tokens(docs=10_000_000, tokens_per_doc=500)

results = {
    "OpenAI text-embedding-3-small realtime":  openai_cost(T, 0.00002),
    "OpenAI text-embedding-3-small batch":     openai_cost(T, 0.00002, batch=True),
    "OpenAI text-embedding-3-large realtime":  openai_cost(T, 0.00013),
    "OpenAI text-embedding-3-large batch":     openai_cost(T, 0.00013, batch=True),
    "Self-host BGE-large (L4 spot)":           self_host_cost(T, 1000, 0.20),
    "Self-host BGE-large (L4 on-demand)":      self_host_cost(T, 1000, 0.50),
}

for name, price in sorted(results.items(), key=lambda kv: kv[1]):
    print(f"{name:<50s} ${price:>7,.2f}")
Enter fullscreen mode Exit fullscreen mode 
Expected output (order matters):
OpenAI text-embedding-3-small batch                $  50.00
OpenAI text-embedding-3-small realtime             $ 100.00
Self-host BGE-large (L4 spot)                      $ 277.78
OpenAI text-embedding-3-large batch                $ 325.00
Self-host BGE-large (L4 on-demand)                 $ 694.44
OpenAI text-embedding-3-large realtime             $ 650.00
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Total tokens for one full refresh: 10M docs × 500 tokens = 5B tokens. This is the volume every provider sees; the price varies by model + tier.
  2. The cheapest option is OpenAI text-embedding-3-small on the batch tier at $50. For a corpus that only needs monthly refresh, waiting 24 hours for the batch to complete costs nothing.
  3. Self-hosted BGE on L4 spot instances is $278 — cheaper than text-embedding-3-large real-time but more expensive than text-embedding-3-small on either tier. At this scale (5B tokens/month), self-host does not win on price.
  4. The crossover point for self-host is roughly 20B tokens/month against text-embedding-3-small batch — 4× this corpus's monthly volume. Self-host only makes sense once you are re-embedding 40M+ docs per month or running a very high-throughput real-time inference workload where API rate limits become a bottleneck.
  5. Recommendation: text-embedding-3-small on batch tier at $50/month. Real-time is a 2× premium for zero benefit on a monthly refresh cadence; text-embedding-3-large is a 13× premium for a 5–10% quality bump — only worth it in domains where recall precision matters intensely.

Output.

Option Cost / month Notes
OpenAI 3-small batch $50 Recommended for monthly refresh
OpenAI 3-small real-time $100 For sub-day freshness
OpenAI 3-large batch $325 High-stakes retrieval only
Self-host BGE spot $278 Only wins at 4× this scale
Self-host BGE on-demand $694 Never wins at this scale

Rule of thumb. For monthly full-corpus refreshes under 100M docs, text-embedding-3-small on batch tier is nearly always the answer. Self-host is a decision that starts making sense only at very large corpora with sustained high throughput.

Worked example — incremental refresh vs full re-embed cost delta

Detailed explanation. Even after picking the cheapest model + tier, the incremental-vs-full split is another 20–50× cost lever. A 10M-doc corpus with 2% daily churn under a nightly full re-embed pays for 300M docs/month; under incremental it pays for 6M/month. Show the arithmetic and the break-even that justifies the extra infra complexity of an incremental pipeline.

  • Full nightly. 10M docs × 30 nights = 300M doc-embeddings/month.
  • Incremental daily. 200K docs × 30 nights = 6M doc-embeddings/month.
  • Ratio. 50× fewer tokens; 50× fewer dollars.

Question. For a 10M-doc corpus with 2% daily churn on text-embedding-3-small, quantify the monthly cost delta between nightly full and incremental refresh. Include batch-tier savings for both.

Input.

Parameter Value
Corpus 10M docs
Avg tokens per doc 500
Daily churn 2% (200K docs)
Model text-embedding-3-small ($0.00002/1K)

Code.

def monthly_cost(docs_per_night, tokens_per_doc, price_per_1k, batch=False, nights=30):
    tier = 0.5 if batch else 1.0
    tokens = docs_per_night * tokens_per_doc * nights
    return tokens * price_per_1k / 1000 * tier

# Full nightly re-embed
full_rt    = monthly_cost(10_000_000, 500, 0.00002)                    # $3000
full_batch = monthly_cost(10_000_000, 500, 0.00002, batch=True)        # $1500

# Incremental daily re-embed
inc_rt    = monthly_cost(200_000, 500, 0.00002)                        # $60
inc_batch = monthly_cost(200_000, 500, 0.00002, batch=True)            # $30

print(f"Nightly full real-time:      ${full_rt:>7,.2f}")
print(f"Nightly full batch:          ${full_batch:>7,.2f}")
print(f"Incremental daily real-time: ${inc_rt:>7,.2f}")
print(f"Incremental daily batch:     ${inc_batch:>7,.2f}")
print(f"Savings incremental vs full: {(1 - inc_batch/full_rt) * 100:.1f}%")
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The full nightly job re-embeds 300M doc-embeddings/month regardless of change rate. At 500 tokens/doc × $0.00002/1K, that is 150B tokens × $0.00002 = $3,000/month.
  2. The batch tier alone cuts this in half to $1,500/month. Free money if freshness SLA allows.
  3. The incremental job embeds only the 200K docs that changed each day — 6M/month. That is 3B tokens × $0.00002 = $60/month real-time or $30/month on batch.
  4. Incremental on batch tier vs full on real-time: $30 vs $3,000, a 100× reduction. Even against full-on-batch ($1,500 vs $30), the incremental win is 50×.
  5. The break-even that justifies incremental infrastructure: at ~1M docs, incremental infrastructure (hash column + queue + worker) pays back in a single month of avoided cost. Under that, the operational simplicity of nightly full might still win.

Output.

Approach Cost / month Ratio vs baseline
Nightly full, real-time $3,000 1× (baseline)
Nightly full, batch $1,500 2× cheaper
Incremental, real-time $60 50× cheaper
Incremental, batch $30 100× cheaper

Rule of thumb. Incremental refresh is a bigger cost lever than any model or tier choice. Ship it before you argue about which model to use.

Worked example — self-host crossover for a 200M-token/month workload

Detailed explanation. A team is considering moving from OpenAI to self-hosted BGE-large after their monthly bill crossed $2K. Compute the real crossover including engineering time, GPU orchestration, and quality delta. Not every "cheaper on paper" moment justifies the migration.

  • Current volume. 200M tokens/month on text-embedding-3-small.
  • Current cost. 200M × $0.00002 / 1K = $4,000/month real-time; $2,000 batch.
  • Self-host L4 spot. ~$56/month per L4 GPU handles ~2.6B tokens; team's 200M fits on one L4 comfortably.
  • Hidden costs. GPU orchestration, monitoring, model updates, on-call rota.

Question. Build the TCO table comparing OpenAI batch vs self-hosted BGE on a single L4 spot instance. Include the engineering cost of setup and ongoing ops.

Input.

Parameter Value
Monthly tokens 200M
OpenAI 3-small batch price $2,000/month
L4 spot price $0.20/hr × 720 hr = $144/month
Engineering setup cost 3 senior-eng-weeks = ~$15K one-time
Engineering ops cost 0.25 FTE ≈ $6K/month

Code.

def openai_cost(monthly_tokens, price_per_1k, batch=True):
    tier = 0.5 if batch else 1.0
    return monthly_tokens * price_per_1k / 1000 * tier

def self_host_tco(gpu_monthly, eng_ops_monthly, setup_amort_months=12, setup_cost=15_000):
    return gpu_monthly + eng_ops_monthly + setup_cost / setup_amort_months

TOKENS = 200_000_000

managed = openai_cost(TOKENS, 0.00002, batch=True)
self    = self_host_tco(gpu_monthly=144, eng_ops_monthly=6_000)

print(f"OpenAI 3-small batch:       ${managed:>8,.2f}/mo")
print(f"Self-host BGE-large TCO:    ${self:>8,.2f}/mo")
print(f"Delta:                      ${managed - self:>8,.2f}/mo")
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. On sticker price alone, self-hosted BGE looks like a slam-dunk: $144/month for the GPU vs $2,000/month for OpenAI. That is the number that gets the "let's self-host" ticket filed.
  2. But engineering ops are not zero. A GPU-backed inference service needs on-call coverage, model updates, GPU node maintenance, monitoring, and the ability to roll back a bad model deploy. Realistic ongoing cost is ~0.25 FTE, or ~$6K/month.
  3. Setup cost — model containerisation, benchmarking, latency tuning, integration with the existing pipeline — is roughly 3 senior-engineer-weeks. Amortised over 12 months, that is $1,250/month.
  4. Realistic TCO comparison: OpenAI $2,000 vs self-host $7,394 ($144 GPU + $6,000 ops + $1,250 amortised setup). Self-hosting loses by 3.7×.
  5. The crossover for a mid-sized team is roughly 3B tokens/month against text-embedding-3-small batch — 15× the current volume. At that scale, the OpenAI bill would be $30K/month and the self-host TCO would still be around $7K/month, a real win. Under that scale, staying on managed is the cheaper answer.

Output.

Option Monthly cost Notes
OpenAI 3-small batch $2,000 Simple, no ops
Self-host BGE (paper) $144 Ignores everything
Self-host BGE (realistic TCO) $7,394 GPU + 0.25 FTE + amortised setup
Crossover volume ~3B tokens/month 15× current

Rule of thumb. The paper cost of self-hosting is always alluring. The realistic TCO — including a fractional FTE and amortised setup — is what actually determines the crossover. Under ~3B tokens/month, managed APIs almost always win.

Senior interview question on cost accounting

A senior interviewer might ask: "You're on a call with the CFO who has flagged a $12K/month OpenAI embeddings bill. The corpus is 30M docs, currently refreshed nightly on text-embedding-3-small. Walk me through the cost audit — what you measure, what you cut, and where the savings actually come from."

Solution Using a four-lever cost audit — incremental + batch tier + right-sized model + tiered cadence 

Cost audit — from $12K/month to under $500

Lever 1 — INCREMENTAL
  Measure: daily churn rate on the corpus
  Fact:    2.5% daily churn measured over 30 days
  Change:  full nightly → hash-driven incremental
  Savings: 40× reduction ($12K → $300)

Lever 2 — BATCH TIER
  Measure: freshness SLA per content field
  Fact:    corpus is documentation; 24 h freshness is fine
  Change:  real-time API → batch API (24 h window)
  Savings: 50% ($300 → $150)

Lever 3 — RIGHT-SIZED MODEL
  Measure: recall@10 on 3-small vs 3-large on golden set
  Fact:    3-small = 0.88; 3-large = 0.89; delta is noise
  Change:  keep 3-small (was already; would have saved 6.5× if on 3-large)
  Savings: n/a (was already right-sized)

Lever 4 — TIERED CADENCE
  Measure: per-field freshness requirement
  Fact:    only titles need 15-min freshness; body 4 h; taxonomy 24 h
  Change:  three-tier queue with three cadences
  Savings: another 30% on the incremental cost ($150 → $105)

Bottom line: $12K/month → $105/month, 99% saved
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Lever From To Cumulative bill Cumulative savings
Baseline full nightly, real-time $12,000 0%
Incremental 2.5% churn queue $300 97.5%
Batch tier 24 h window OK $150 98.75%
Right-sized model (already small) $150 98.75%
Tiered cadence per-field cadence $105 99.1%

After the audit, the pipeline runs 99% cheaper with no loss of retrieval quality and (if anything) better freshness on the fields that matter — because the CDC-driven streaming tier covers the sub-hour SLAs the nightly full re-embed never actually hit.

Output:

Metric Before After
Monthly OpenAI cost $12,000 $105
Docs re-embedded / day 30M 750K (2.5% churn, mixed tier)
Freshness on titles 12 h 5 min (stream tier)
Freshness on body 12 h 4 h (hourly tier)
Freshness on taxonomy 12 h 24 h (nightly batch)
Retrieval recall@10 0.88 0.88

Why this works — concept by concept:

  • Incremental is the biggest lever — a 40× reduction in one config change. Nothing else on the cost audit comes close.
  • Batch tier is free savings — 50% off, unconditionally, if the SLA permits. Any refresh workload that does not need sub-day freshness should be on batch.
  • Right-sizing the model — the recall delta between text-embedding-3-small and text-embedding-3-large is typically 1–5% on general knowledge, 5–10% on specialised domains. Pay for large only when the domain justifies it.
  • Tiered cadence — matching the refresh cadence to the per-field freshness requirement is the last 10–30% of savings. Streaming the fields that need it; batching the fields that don't.
  • Cost — audit runs in a day; savings compound monthly. The four levers combine multiplicatively: 40× × 2× × 1× × 1.4× = 112× total reduction in the extreme case.

SQL
Topic — sql
SQL problems on cost accounting and token audits

Practice →

Optimization Topic — optimization Optimization problems on embedding cost trade-offs

Practice →

4. Model versioning + migration

Every vector needs a model_tag — without it, model migrations are a full-corpus outage; with it, they are a shadow-column rollout

The mental model in one line: every row that stores a vector must also store the exact model_tag (name + version) that produced the vector, so a mixed-model corpus is queryable and migrations are reversible. Skip this column and the next time OpenAI ships a new embedding model, the team discovers there is no way to tell which vectors are on which model — and the "migration" becomes a full-corpus re-embed with no rollback path.

Iconographic model versioning diagram — a vector table with an id, vector, and highlighted model_tag column with values 'ada-002@v1' and 'text-emb-3-small@v2', a migration ramp arrow from ada-002 to text-embedding-3-large with a dual-write chip, and a rollback card with a reverse-arrow, on a light PipeCode card.

The four "must-answer" axes for versioning.

  • Per-row model tag. Store model_tag = 'text-embedding-3-small@v1' on every row. The tag is the atom of versioning; every migration operation queries and updates by tag.
  • Migration strategy. Full re-embed into a shadow column, dual-write for a validation window, cut over reads, drop the old column. Never in-place — you need a rollback path.
  • Dual-write. During migration, every incoming write embeds against both the old and new model and writes both vectors. If the new model turns out to be worse, reads flip back to the old.
  • Rollback. Keeping the old column for 2–4 weeks after cutover is cheap insurance. Once the new model is validated on production traffic, drop the column and reclaim the storage.

The model_tag column — schema pattern.

  • Format. <model_name>@<version>. Examples: text-embedding-ada-002@v1, text-embedding-3-small@v2, bge-large-en-v1.5@self.
  • Index. CREATE INDEX ON documents (model_tag) — the migration job filters by tag to find rows on the old model.
  • Dimension. Different models produce different-dimensional vectors. Either store multiple vector columns (embedding_1536, embedding_3072) or use a wide-enough column (pgvector allows dimension mismatch across rows if the column is untyped, but for typed columns, plan the schema up front).
  • Cross-model queries. A retrieval query only makes sense within one model_tag. Always filter WHERE model_tag = current_model in retrieval SQL, or you compare a 3-small query vector to an ada-002 document vector and the cosine similarity is meaningless.

Migration strategy — four steps.

  • Step 1 — shadow column. ALTER TABLE documents ADD COLUMN embedding_v2 vector(1536), ADD COLUMN model_tag_v2 TEXT. Nothing changes in the read path yet; only the schema is prepared.
  • Step 2 — backfill. Background job walks every row, re-embeds under the new model, populates embedding_v2 and model_tag_v2. This is the expensive step — full-corpus re-embed at the new model's rate.
  • Step 3 — dual-write + validation. All new writes populate both embedding and embedding_v2. Reads still hit embedding (old). Run recall@k on both against the golden query set for a validation window (7–14 days).
  • Step 4 — cutover + drop. Rename embedding → embedding_old, embedding_v2 → embedding, update the read query to use the renamed column. Wait 2–4 weeks with the old column still present as a rollback path. Once confidence is high, ALTER TABLE ... DROP COLUMN embedding_old.

Dual-write pattern — the "safe migration" invariant.

  • On every write during migration. embedding = embed(content, old_model); embedding_v2 = embed(content, new_model). Two API calls per write; short-term cost overhead.
  • Cutoff. Once every row has both columns populated (backfill + dual-write until all pre-migration rows are refreshed), the two vectors coexist for every row.
  • Read flip. Change the retrieval query to use embedding_v2. Roll out behind a feature flag so you can flip back at request granularity.
  • Cost during migration. Roughly 2× steady-state cost for the migration window (backfill + dual-write). Budget for this; it is not free but it is bounded.

Rollback — the invariant that lets you sleep.

  • Keep the old column. For 2–4 weeks after cutover, embedding_old is untouched but present. Rollback is a one-line schema swap.
  • Version the migration. Every migration in the version-control system has a name (m025_upgrade_to_text_embedding_3_large); the reverse migration (m025_down.sql) does the opposite. Roll forward and back like any other schema change.
  • Behavioural check. Keep the recall@k drift monitor running through the migration window. If the new model's recall drops on production traffic (not just the golden set), flip back.
  • Kill switch. A feature flag at the retrieval layer (use_new_embeddings) lets an on-call engineer switch back to the old vectors in < 60 seconds without a redeploy.

Common interview probes on versioning.

  • "Every vector needs what column?" — model_tag.
  • "How do you migrate 10M vectors to a new model without downtime?" — shadow column + backfill + dual-write + cutover.
  • "How do you roll back if the new model is worse?" — old column kept for 2–4 weeks + feature flag + swap.
  • "What happens to a query if the corpus is mid-migration?" — filter WHERE model_tag = current_model; mixed reads are meaningless.

Worked example — migrating ada-002 to text-embedding-3-large

Detailed explanation. A 5-year-old RAG stack still runs on text-embedding-ada-002 (1536-d) — the OpenAI original. The team plans to migrate to text-embedding-3-large (3072-d) to pick up the recall improvement on their financial-document corpus. Different dimensions means a new column, not an in-place upgrade. Walk through the four-step migration.

  • Old model. ada-002, 1536-d, $0.00010/1K tokens.
  • New model. text-embedding-3-large, 3072-d, $0.00013/1K tokens.
  • Corpus. 2M docs, 400 tokens/doc.
  • Migration cost. 2M × 400 × $0.00013 / 1K = $104 (batch tier: $52). Trivial.

Question. Ship the four-step migration with SQL DDL, backfill job, dual-write logic, and cutover.

Input.

Parameter Value
Old model text-embedding-ada-002 (1536-d)
New model text-embedding-3-large (3072-d)
Corpus size 2M docs
Backfill tier batch API (50% off)

Code.

-- Step 1 — shadow column
ALTER TABLE documents
  ADD COLUMN embedding_v2  vector(3072),
  ADD COLUMN model_tag_v2  TEXT;

CREATE INDEX documents_v2_hnsw
  ON documents USING hnsw (embedding_v2 vector_cosine_ops)
  WHERE embedding_v2 IS NOT NULL;

-- Step 4 — cutover (executed only after validation)
BEGIN;
  ALTER TABLE documents RENAME COLUMN embedding TO embedding_old;
  ALTER TABLE documents RENAME COLUMN model_tag TO model_tag_old;
  ALTER TABLE documents RENAME COLUMN embedding_v2 TO embedding;
  ALTER TABLE documents RENAME COLUMN model_tag_v2 TO model_tag;
COMMIT;

-- 4 weeks later — drop the old column
ALTER TABLE documents
  DROP COLUMN embedding_old,
  DROP COLUMN model_tag_old;
Enter fullscreen mode Exit fullscreen mode 
# Step 2 — backfill via batch API
import openai, psycopg2, json, tempfile

NEW_MODEL = "text-embedding-3-large"

def build_backfill_file(conn):
    cur = conn.cursor("cursor_backfill")
    cur.itersize = 1000
    cur.execute("""
        SELECT doc_id, content
        FROM   documents
        WHERE  embedding_v2 IS NULL
        ORDER  BY doc_id
    """)
    f = tempfile.NamedTemporaryFile("w", suffix=".jsonl", delete=False)
    for doc_id, content in cur:
        f.write(json.dumps({
            "custom_id": str(doc_id),
            "method":    "POST",
            "url":       "/v1/embeddings",
            "body":      {"model": NEW_MODEL, "input": content},
        }) + "\n")
    f.close()
    return f.name

# Step 3 — dual-write for the migration window
def embed_and_write(conn, doc_id, content):
    old = openai.embeddings.create(model="text-embedding-ada-002", input=content).data[0].embedding
    new = openai.embeddings.create(model=NEW_MODEL,                 input=content).data[0].embedding
    conn.cursor().execute("""
        UPDATE documents
        SET    embedding    = %s,
               model_tag    = 'text-embedding-ada-002@v1',
               embedding_v2 = %s,
               model_tag_v2 = %s,
               embedded_at  = now()
        WHERE  doc_id = %s
    """, (old, new, f"{NEW_MODEL}@v1", doc_id))
    conn.commit()
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Step 1 (shadow column): the ALTER adds embedding_v2 at the new dimension (3072) and model_tag_v2. Nothing in the read path changes; existing retrieval queries still hit embedding. An HNSW index on embedding_v2 is built with a WHERE clause so it only indexes populated rows during backfill.
  2. Step 2 (backfill): a background job iterates every row, submits a batch to the OpenAI batch API, and writes the results into embedding_v2 + model_tag_v2. Batch tier makes this $52 instead of $104 for the whole 2M-doc corpus.
  3. Step 3 (dual-write + validation): during the migration window (2 weeks after backfill completes), every incoming write embeds both models. Retrieval queries still read embedding but a shadow retrieval job runs the same queries against embedding_v2 and reports recall@k for comparison against the golden query set.
  4. Step 4 (cutover): once the shadow recall@k is proven to match or exceed the old, a single transactional rename swaps the columns. Zero-downtime — the retrieval query's SELECT ... FROM documents WHERE embedding <-> ... now runs against the new vectors.
  5. Rollback: the old column is preserved for 4 weeks. If the new model shows worse behaviour on production traffic (not just the golden set), rename back — one transaction, instant rollback. After 4 weeks, drop the old column and reclaim the ~24 GB (2M × 3072 × 4 bytes) of storage.

Output.

Phase Duration Read path Write path Cost
Shadow column seconds embedding embedding 0
Backfill 24 h (batch) embedding embedding $52
Dual-write 2 weeks embedding both 2× normal
Cutover seconds embedding_v2 (renamed) new only 0
Rollback window 4 weeks new new 0
Drop old seconds new new -0 (reclaim 24 GB)

Rule of thumb. Every model migration follows the same four steps: shadow → backfill → dual-write → cutover, with rollback via column preservation. Only the model names change; the pattern is identical.

Worked example — mixed-model corpus and read-time filtering

Detailed explanation. During a long migration or a slow-rolling model bump, the corpus is genuinely mixed — some rows are on the old model, some on the new. Retrieval that ignores model_tag compares vectors from different spaces and returns garbage. The fix is a WHERE model_tag = current_model predicate on every retrieval query. Walk through the pattern.

  • The bug. A cosine-similarity search over vectors from two different models returns semantically arbitrary results — the two model spaces are unrelated.
  • The fix. Every retrieval query filters WHERE model_tag = current_model.
  • The cost. A partial index on (model_tag) WHERE model_tag = current_model keeps the filter free.

Question. Show the retrieval SQL that safely queries a mid-migration corpus, plus the index that keeps it fast.

Input.

Parameter Value
Corpus 2M docs, mid-migration
Rows on old model 60%
Rows on new model 40%
Current model text-embedding-3-small@v2

Code.

-- Partial HNSW index scoped to the current model
CREATE INDEX documents_current_hnsw
  ON documents USING hnsw (embedding vector_cosine_ops)
  WHERE model_tag = 'text-embedding-3-small@v2';

-- Retrieval query — always filter by model_tag
SELECT doc_id,
       content,
       1 - (embedding <=> $1::vector) AS cosine_similarity
FROM   documents
WHERE  model_tag = 'text-embedding-3-small@v2'
ORDER  BY embedding <=> $1::vector
LIMIT  10;
Enter fullscreen mode Exit fullscreen mode 
# App-side — pin the current model tag centrally
CURRENT_MODEL_TAG = "text-embedding-3-small@v2"

def retrieve(query_text: str, k: int = 10):
    q_vec = openai.embeddings.create(model="text-embedding-3-small", input=query_text).data[0].embedding
    with psycopg2.connect(...) as conn, conn.cursor() as cur:
        cur.execute("""
            SELECT doc_id, content, 1 - (embedding <=> %s::vector) AS score
            FROM   documents
            WHERE  model_tag = %s
            ORDER  BY embedding <=> %s::vector
            LIMIT  %s
        """, (q_vec, CURRENT_MODEL_TAG, q_vec, k))
        return cur.fetchall()
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The partial HNSW index is scoped to WHERE model_tag = current_model. During migration, this index only covers the 40% of rows already on the new model; queries against those rows are as fast as if the whole corpus were on the new model.
  2. The retrieval SQL always includes WHERE model_tag = current_model. This guarantees the vector-space math is meaningful — comparing a 3-small query vector to a 3-small document vector, never cross-model.
  3. During migration, a query might return fewer than 10 results because the model-filtered subset is smaller. This is acceptable — better to return N < 10 correct results than 10 semantically meaningless ones.
  4. Rows not yet on the current model are effectively invisible to retrieval until the backfill completes. This is the trade-off of a long migration: some content is temporarily un-retrievable. Prioritise the backfill order (e.g., by traffic recency) to minimise this window.
  5. Once migration is complete and model_tag = current_model for every row, the partial index becomes a full index and the migration filter becomes a no-op (though the SQL still includes it for safety on the next migration).

Output.

Query Rows returned Correctness
No filter 10 (mixed model) garbage
model_tag filter (during migration) up to 10 (40% subset) correct
model_tag filter (post migration) 10 (100% subset) correct

Rule of thumb. Every retrieval query in a production pipeline should include WHERE model_tag = current_model — even if the corpus is currently on a single model. It costs nothing when the corpus is unified and it saves the migration.

Worked example — rollback path when a new model regresses

Detailed explanation. A team migrates a 10M-doc corpus from text-embedding-3-small to text-embedding-3-large for the recall gain. Three days after cutover, the on-call product engineer reports "search results feel less relevant" — anecdotal but consistent. The golden set doesn't show the regression, but production traffic does. Walk through the rollback with a feature flag + column preservation.

  • The signal. User feedback / click-through rate drop on production traffic; golden set is clean.
  • The suspect. A domain shift on production queries the golden set does not cover.
  • The rollback. Flip feature flag; retrieval goes back to embedding_old; investigate offline.

Question. Implement the feature-flag-gated retrieval that lets on-call flip back to the old vectors in under 60 seconds without a redeploy.

Input.

Component Value
Feature flag use_new_embeddings (LaunchDarkly / Unleash / Postgres row)
Old column embedding_old (kept for 4 weeks)
New column embedding (current)
Rollback SLA 60 seconds from decision to effect

Code.

import os, psycopg2, openai

def current_flag() -> bool:
    # Simplest: read from a small config table; cache 5 s
    return feature_flag_client.evaluate("use_new_embeddings", default=True)

def retrieve(query_text: str, k: int = 10):
    use_new = current_flag()

    if use_new:
        q_model = "text-embedding-3-large"
        col     = "embedding"
        tag_col = "model_tag"
        tag_val = "text-embedding-3-large@v1"
    else:
        q_model = "text-embedding-3-small"
        col     = "embedding_old"
        tag_col = "model_tag_old"
        tag_val = "text-embedding-3-small@v2"

    q_vec = openai.embeddings.create(model=q_model, input=query_text).data[0].embedding

    sql = f"""
        SELECT doc_id, content, 1 - ({col} <=> %s::vector) AS score
        FROM   documents
        WHERE  {tag_col} = %s
        ORDER  BY {col} <=> %s::vector
        LIMIT  %s
    """
    with psycopg2.connect(...) as conn, conn.cursor() as cur:
        cur.execute(sql, (q_vec, tag_val, q_vec, k))
        return cur.fetchall()
Enter fullscreen mode Exit fullscreen mode 
-- Rollback runbook — one command
UPDATE feature_flags
SET    value = FALSE,
       updated_at = now()
WHERE  key = 'use_new_embeddings';

-- Retrieval now hits embedding_old with the old query model
-- Old column is intact for 4 weeks; roll-forward is the reverse UPDATE
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The feature flag is evaluated per-request with a 5-second cache — a UPDATE to the flag table propagates to all app servers within 5 seconds. Rollback effect is under 60 seconds without a code deploy.
  2. When the flag is TRUE (default), the retrieval query uses embedding (new column) with the new model's query vector. When the flag is FALSE, it uses embedding_old with the old model's query vector. Both paths coexist because the migration preserved both columns.
  3. The model_tag_old predicate is critical: it filters the old-column query to only rows still on the old model. During the 4-week rollback window, model_tag_old values on rows written during the new-model era are NULL — those rows are correctly filtered out.
  4. On rollback, the on-call flips the flag, watches retrieval quality metrics for 15 minutes, and if the click-through rate recovers, files a ticket to investigate why the golden set missed the regression. If it does not recover, the problem was not the model change.
  5. Once the investigation completes and the new model is either fixed or accepted, either roll forward (flag → TRUE) or accept the rollback and skip the migration until a better model ships. The 4-week rollback window is enough to make an informed decision.

Output.

Event Flag value Read path Rollback time
Pre-migration TRUE (only column) embedding (old-model) n/a
Post-migration TRUE embedding (new-model) n/a
Regression detected FLIP to FALSE embedding_old (old-model) < 60 s
Roll-forward after fix FLIP to TRUE embedding (new-model) < 60 s

Rule of thumb. Never migrate without a feature-flag-gated read path. The dual-write + column preservation makes the flag possible; without it, rollback is a redeploy or a full re-embed. Sixty-second rollback is the standard for any production retrieval system.

Senior interview question on model versioning

A senior interviewer might ask: "You have a 20M-doc RAG stack on text-embedding-ada-002. Your CTO wants to migrate to text-embedding-3-large for the quality improvement. Walk me through the four-step migration plan, how you'd budget the cost, and what the rollback story looks like."

Solution Using shadow-column migration with dual-write, feature-flag cutover, and 4-week rollback window 

20M-doc migration plan — ada-002 → text-embedding-3-large

Week 1 — Schema
  ALTER TABLE documents ADD embedding_v2 vector(3072), model_tag_v2 TEXT
  CREATE partial HNSW index on embedding_v2

Week 2–3 — Backfill (batch API)
  Submit 20M rows to OpenAI batch API in daily chunks of 3M
  Total cost: 20M × 400 tokens × $0.00013/1K × 0.5 (batch) = $520
  Total wall clock: ~7 days

Week 4 — Dual-write validation
  Every incoming write embeds both models
  Shadow retrieval job scores golden set + production query sample on both
  Compare recall@10; require ≥ parity for cutover

Week 5 — Cutover
  Rename columns transactionally
  Retrieval query now uses embedding (new)
  Feature flag `use_new_embeddings` defaults TRUE

Weeks 5–8 — Rollback window
  Keep embedding_old for 4 weeks
  Monitor production traffic recall + click-through
  Flip flag if any regression detected

Week 9 — Drop old column
  ALTER TABLE documents DROP embedding_old, DROP model_tag_old
  Reclaim ~120 GB storage
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Week Phase Cost Rollback exposure
1 Shadow column 0 none
2–3 Backfill $520 full — no cutover yet
4 Dual-write + validate 2× write cost full — reads still on old
5 Cutover 0 flag flip → old
5–8 Rollback window 0 flag flip → old
9 Drop old column 0 none — migration final

After the 9-week rollout, the entire 20M-doc corpus is on text-embedding-3-large, storage is reclaimed, and retrieval recall@10 has improved from 0.87 to 0.91 on the golden set. The total OpenAI cost of the migration was $520 for the backfill plus ~$40 for the dual-write window — negligible against the recall gain.

Output:

Surface Result
Corpus size 20M docs
Migration wall clock 9 weeks
Migration cost $560 total
Recall@10 before 0.87
Recall@10 after 0.91
Rollback SLA achieved 60 s (never exercised)

Why this works — concept by concept:

  • Shadow column, not in-place — new dimension means new column. Never truncate the old vector to force in-place; you lose the rollback path.
  • Batch tier for backfill — the whole 20M-doc backfill is $520 on batch vs $1,040 real-time. Batch is the right tier for a one-shot migration where 24-hour turnaround is fine.
  • Dual-write validation window — a week of dual-writes plus a shadow retrieval job on production queries catches regressions the golden set misses. This is where the "does the new model actually improve quality?" question gets answered on real traffic.
  • Feature-flag cutover — a single UPDATE against a flag table is safer than a code deploy. Sixty-second rollback is the standard.
  • Cost — total migration budget is $560 for a 20M-doc corpus. The recall improvement pays back in engagement lift within days. Migrations are cheap; migrations without rollback paths are the expensive ones.

SQL
Topic — sql
SQL problems on model_tag columns and migration DDL

Practice →

ETL Topic — etl ETL problems on shadow-column backfill jobs

Practice →

5. Drift monitoring + freshness

Retrieval quality decays silently — a golden query set + recall@k trend is the only signal that catches drift before users do

The mental model in one line: embedding drift is the slow decay of retrieval quality caused by corpus vocabulary shifts, query-distribution shifts, or model staleness, and the only reliable way to detect it is a golden query set scored on recall@k as a trend, not a point-in-time snapshot. Every other drift-monitor pattern — cluster silhouette scores, embedding-norm histograms, ANN recall probes — is either a proxy for or a supplement to the recall@k trend.

Iconographic drift monitoring diagram — a golden query-set card with 5 query-chip rows and expected-doc glyphs, a trend-chart card showing recall@k dropping from 0.92 to 0.78 with a red threshold band under 0.85, and an alert card with a red flag reading 'drift alert · re-embed', on a light PipeCode card.

The four "must-answer" axes for drift.

  • Drift signal. Recall@k against a golden query set. k is typically 5 or 10; the golden set is 100–500 hand-curated query→relevant-doc pairs. Trend over time, not a single value.
  • Golden query set. Curated by product / domain experts. Covers head queries (top 20% of traffic), long tail (uncommon but important intents), and adversarial cases (queries that were previously failures).
  • Freshness SLA per corpus tier. Head content (product page titles) — minutes. Body content (descriptions, docs) — hours. Long tail (archived content) — days. Alert on freshness violations.
  • Escalation. When drift crosses threshold, the on-call runbook is: reproduce → determine if content-drift or model-drift → re-embed the affected corpus tier or begin a model migration.

Building the golden query set.

  • Sourcing. 100 head queries from production logs (top 20% of traffic), 200 long-tail queries sampled across corpora, 50 hand-crafted adversarial cases where retrieval previously regressed.
  • Labelling. For each query, mark 1–3 documents as "relevant" (correct answer). Use domain experts; treat this as a data-labelling task with quality review.
  • Refresh cadence. Add ~10% new queries every quarter to reflect current traffic shape. Retire stale queries whose expected docs no longer exist.
  • Coverage. Ensure the golden set covers every content vertical (products, docs, help centre, blog, etc.) and every language the system supports.

Recall@k — the metric that matters.

  • Definition. For each golden query, count how many of the labelled-relevant docs appear in the top-k results. Average across queries.
  • k choice. k=10 is standard for RAG. k=5 for higher-precision surfaces; k=20 for exploratory search.
  • Threshold. Alert when the trailing 7-day average drops below the 30-day baseline by more than 5% relative (e.g. 0.90 → 0.85).
  • Not just recall. Also track MRR (mean reciprocal rank) — did the correct doc appear at position 1? Recall@k captures presence; MRR captures ranking quality.

Freshness SLA per corpus tier.

  • Head tier. Product titles, prices, availability. Freshness SLA: 5 minutes. Any row where now() - embedded_at > 5 minutes is a violation.
  • Body tier. Product descriptions, help articles, docs. Freshness SLA: 4 hours.
  • Long-tail tier. Archived content, historical records. Freshness SLA: 24 hours.
  • Alert. Percentage of rows past their tier's freshness SLA. Aim < 1%; page at > 5%.

Escalation runbook when drift fires.

  • Step 1. Reproduce — run the alerting query batch offline; confirm the drop is real, not a monitoring artefact.
  • Step 2. Split — is the drop concentrated in one corpus tier, one language, one query cluster? Grouped recall@k analysis pinpoints the affected slice.
  • Step 3. Classify — content drift (corpus vocabulary shifted) or model drift (query distribution shifted away from what the model was trained on)?
  • Step 4a — content drift. Trigger a re-embed of the affected slice on the current model; verify recall recovers.
  • Step 4b — model drift. Begin a model-migration plan to a newer embedding model; run the shadow-column pattern.
  • Step 5. Post-mortem — expand the golden set with the queries that surfaced the drift.

Common interview probes on drift.

  • "How do you detect embedding drift?" — golden query set + recall@k trend, not a point-in-time metric.
  • "How big should the golden set be?" — 100–500 pairs; refreshed quarterly.
  • "What threshold do you alert on?" — 5% relative drop in trailing 7-day recall@k vs 30-day baseline.
  • "Content drift or model drift — how do you tell?" — grouped analysis by corpus slice + model version.

Worked example — building the golden query set and nightly recall@k monitor

Detailed explanation. The concrete implementation of a drift monitor: a golden_queries table with query text and expected doc IDs, a nightly job that runs each query through retrieval, computes recall@10, writes the result to a recall_at_k_history table, and alerts if the trend drops. Walk through the schema and the runner.

  • Golden set. 200 hand-labelled query → relevant doc IDs.
  • Cadence. Nightly job.
  • Metric. Recall@10 averaged across the golden set.
  • Storage. History table for trend visualisation.

Question. Design the schema, the nightly runner, and the alert threshold for a drift monitor.

Input.

Component Setting
Golden set size 200 queries × 1–3 relevant docs each
k 10
Cadence nightly 03:00
Alert trailing 7d avg < 30d avg × 0.95

Code.

CREATE TABLE golden_queries (
  query_id     BIGSERIAL PRIMARY KEY,
  query_text   TEXT NOT NULL,
  relevant_ids BIGINT[] NOT NULL,
  corpus_tier  TEXT NOT NULL,          -- 'head', 'body', 'longtail'
  language     TEXT NOT NULL,
  created_at   TIMESTAMPTZ DEFAULT now(),
  retired_at   TIMESTAMPTZ
);

CREATE TABLE recall_at_k_history (
  run_id       BIGSERIAL PRIMARY KEY,
  ran_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  model_tag    TEXT NOT NULL,
  corpus_tier  TEXT,
  language     TEXT,
  k            INT NOT NULL,
  recall       NUMERIC(4,3) NOT NULL,
  mrr          NUMERIC(4,3),
  query_count  INT NOT NULL
);
Enter fullscreen mode Exit fullscreen mode 
# Nightly runner — 03:00 UTC
import psycopg2, openai
from statistics import mean

MODEL   = "text-embedding-3-small"
MODEL_TAG = f"{MODEL}@v2"
K       = 10

def retrieve_top_k(cur, q_vec):
    cur.execute("""
        SELECT doc_id
        FROM   documents
        WHERE  model_tag = %s
        ORDER  BY embedding <=> %s::vector
        LIMIT  %s
    """, (MODEL_TAG, q_vec, K))
    return [row[0] for row in cur.fetchall()]

def recall_and_mrr(retrieved, relevant):
    relevant_set = set(relevant)
    hit = [i for i, d in enumerate(retrieved) if d in relevant_set]
    recall = len(set(retrieved) & relevant_set) / len(relevant_set)
    mrr    = (1 / (hit[0] + 1)) if hit else 0.0
    return recall, mrr

def run_drift_monitor():
    conn = psycopg2.connect("postgres://drift@db.internal/rag")
    cur  = conn.cursor()
    cur.execute("SELECT query_id, query_text, relevant_ids, corpus_tier, language FROM golden_queries WHERE retired_at IS NULL")
    rows = cur.fetchall()

    scores_by_tier: dict = {}
    for qid, qtext, rel, tier, lang in rows:
        q_vec = openai.embeddings.create(model=MODEL, input=qtext).data[0].embedding
        top   = retrieve_top_k(cur, q_vec)
        recall, mrr = recall_and_mrr(top, rel)
        scores_by_tier.setdefault(tier, []).append((recall, mrr))

    for tier, pairs in scores_by_tier.items():
        avg_recall = mean(r for r, _ in pairs)
        avg_mrr    = mean(m for _, m in pairs)
        cur.execute("""
            INSERT INTO recall_at_k_history
              (model_tag, corpus_tier, k, recall, mrr, query_count)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (MODEL_TAG, tier, K, avg_recall, avg_mrr, len(pairs)))
    conn.commit()

def check_alert(conn):
    cur = conn.cursor()
    cur.execute("""
        WITH recent AS (
          SELECT corpus_tier, AVG(recall) AS r7
          FROM   recall_at_k_history
          WHERE  ran_at > now() - INTERVAL '7 days'
          GROUP  BY corpus_tier
        ),
        baseline AS (
          SELECT corpus_tier, AVG(recall) AS r30
          FROM   recall_at_k_history
          WHERE  ran_at BETWEEN now() - INTERVAL '30 days' AND now() - INTERVAL '7 days'
          GROUP  BY corpus_tier
        )
        SELECT b.corpus_tier, r.r7, b.r30
        FROM   recent r
        JOIN   baseline b USING (corpus_tier)
        WHERE  r.r7 < b.r30 * 0.95
    """)
    for tier, r7, r30 in cur.fetchall():
        page_oncall(f"embedding drift on {tier}: 7d={r7:.3f} vs 30d={r30:.3f}")
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The golden_queries table stores the curated set with corpus_tier, language, and a retired_at column that lets stale queries be soft-deleted without a hard row delete.
  2. The nightly runner iterates every non-retired query, embeds the query text with the current model, retrieves the top-10 documents from pgvector, and computes recall@10 and MRR against the labelled relevant_ids.
  3. Results are grouped by corpus_tier so head-tier and long-tail drift can be tracked independently — a drift concentrated in one tier is a signal about that tier's content, not the model overall.
  4. Each nightly result is written to recall_at_k_history — the trend table. Grafana or a simple dashboard plots the trend by tier, model_tag, and language.
  5. The alert query compares the trailing 7-day average to the 30-day baseline; a 5% relative drop pages the on-call. The 7d vs 30d comparison filters out day-to-day noise while catching genuine regressions.

Output.

Tier 30d baseline 7d recent Alert?
head 0.94 0.93 no
body 0.88 0.85 yes (5% drop)
longtail 0.75 0.72 yes (4% drop → warn)

Rule of thumb. Drift is a trend, not a snapshot. Ship the history table and the trend chart before you obsess over the absolute recall number.

Worked example — freshness SLA violations per corpus tier

Detailed explanation. Recall@k catches quality drift; a separate freshness monitor catches the case where the pipeline stalls and rows go stale. Track now() - embedded_at per tier and alert when the fraction of stale rows exceeds a per-tier threshold. This is the "did the pipeline actually run last night?" monitor.

  • Head tier. SLA: embedded_at > now() - 5 minutes for 99% of rows.
  • Body tier. SLA: embedded_at > now() - 4 hours for 99% of rows.
  • Long-tail tier. SLA: embedded_at > now() - 24 hours for 99% of rows.

Question. Write the SQL that computes freshness violation rate per tier and the alert threshold.

Input.

Tier Freshness SLA Alert threshold
head 5 min > 5% violated
body 4 h > 5% violated
longtail 24 h > 5% violated

Code.

CREATE OR REPLACE VIEW freshness_by_tier AS
SELECT
  corpus_tier,
  COUNT(*) AS total_rows,
  SUM(CASE
        WHEN corpus_tier = 'head'     AND embedded_at < now() - INTERVAL '5 minutes'  THEN 1
        WHEN corpus_tier = 'body'     AND embedded_at < now() - INTERVAL '4 hours'    THEN 1
        WHEN corpus_tier = 'longtail' AND embedded_at < now() - INTERVAL '24 hours'   THEN 1
        WHEN embedded_at IS NULL                                                        THEN 1
        ELSE 0
      END) AS violated_rows,
  ROUND(
    SUM(CASE
          WHEN corpus_tier = 'head'     AND embedded_at < now() - INTERVAL '5 minutes'  THEN 1
          WHEN corpus_tier = 'body'     AND embedded_at < now() - INTERVAL '4 hours'    THEN 1
          WHEN corpus_tier = 'longtail' AND embedded_at < now() - INTERVAL '24 hours'   THEN 1
          WHEN embedded_at IS NULL                                                        THEN 1
          ELSE 0
        END)::NUMERIC * 100.0 / COUNT(*),
    2
  ) AS violation_pct
FROM   documents
GROUP  BY corpus_tier;

-- Alert query — anything > 5% violation triggers
SELECT corpus_tier, violation_pct
FROM   freshness_by_tier
WHERE  violation_pct > 5.0;
Enter fullscreen mode Exit fullscreen mode 
# Alert scraper — every 5 min
import psycopg2

def scrape_freshness():
    conn = psycopg2.connect("postgres://drift@db.internal/rag")
    cur  = conn.cursor()
    cur.execute("SELECT corpus_tier, violation_pct FROM freshness_by_tier")
    for tier, pct in cur.fetchall():
        emit_metric(f"embed.freshness_violation_pct.{tier}", pct)
        if pct > 5.0:
            page_oncall(f"freshness SLA violation on {tier}: {pct}% stale")
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. The freshness_by_tier view computes, per tier, the total row count and the count of rows past their tier's SLA. The CASE arms encode the per-tier threshold; the aggregation is a single scan.
  2. The 5% alert threshold assumes some rows will always be transiently past SLA (mid-refresh). Anything past 5% is a signal that the refresh pipeline has stalled or is unable to keep up with churn.
  3. embedded_at IS NULL is counted as a violation — a row with no vector is a row the retrieval layer cannot use. This catches new documents that were ingested but never embedded.
  4. The scraper runs every 5 minutes (matching the head tier's SLA cadence). Head-tier stalls are caught within 10 minutes; body and long-tail stalls have longer natural intervals.
  5. When the alert fires, the on-call runbook is: (a) check the change-queue depth (SELECT COUNT(*) FROM embed_queue); (b) check the worker's logs for API errors; (c) check the OpenAI status page for provider incidents; (d) if the queue is deep and workers are healthy, scale out the workers.

Output.

Tier Total Violated Violation % Alert?
head 500,000 3,000 0.6% no
body 3,000,000 220,000 7.3% yes
longtail 6,500,000 65,000 1.0% no

Rule of thumb. Ship the freshness monitor before the recall monitor. Freshness catches the pipeline-stalled case; recall catches the pipeline-running-but-degrading case. Together they cover the whole failure surface.

Worked example — root-causing a recall@k drop after a corpus expansion

Detailed explanation. A synthetic incident: recall@10 on the body tier drops from 0.88 to 0.82 over 10 days. No model changes; the pipeline is healthy. Investigation shows the corpus grew 30% due to a bulk import of a new product line. The new vocabulary was outside the golden set's coverage. Walk through the diagnosis and the fix.

  • The signal. Recall@10 body-tier drop of 6 points over 10 days.
  • The suspect. Corpus content drift — new vocabulary the golden set does not cover.
  • The fix. Expand the golden set + re-verify.

Question. Walk through the diagnostic queries and the golden-set expansion process.

Input.

Signal Value
Recall@10 body-tier (30d avg) 0.88
Recall@10 body-tier (7d avg) 0.82
Corpus size 10d ago 6M rows
Corpus size today 7.8M rows
Ingestion event +1.8M rows in a bulk import of new product line

Code.

-- Diagnostic 1 — did the corpus grow?
SELECT DATE_TRUNC('day', embedded_at) AS day, COUNT(*) AS new_rows
FROM   documents
WHERE  corpus_tier = 'body'
  AND  embedded_at > now() - INTERVAL '14 days'
GROUP  BY day
ORDER  BY day;

-- Diagnostic 2 — recall@10 broken down by golden-query cluster
SELECT gq.corpus_tier,
       gq.language,
       COUNT(*)                        AS queries,
       AVG(rh.recall)                  AS avg_recall
FROM   golden_queries gq
JOIN   recall_at_k_history rh ON rh.corpus_tier = gq.corpus_tier
WHERE  rh.ran_at > now() - INTERVAL '7 days'
  AND  gq.retired_at IS NULL
GROUP  BY gq.corpus_tier, gq.language;

-- Diagnostic 3 — new-product-line queries that likely miss
SELECT DISTINCT SUBSTRING(content, 1, 80) AS snippet
FROM   documents
WHERE  corpus_tier = 'body'
  AND  embedded_at > now() - INTERVAL '14 days'
  AND  content LIKE '%new-product-line%'   -- domain-specific
LIMIT  20;
Enter fullscreen mode Exit fullscreen mode 
# Golden set expansion — add 30 queries from the new domain
new_pairs = [
    ("how do I set up the new-product-line-model-x",   [12034, 12089]),
    ("new-product-line-model-x troubleshooting steps",  [12080, 12093]),
    # ... 28 more
]

def expand_golden_set(pairs, tier="body", lang="en"):
    conn = psycopg2.connect("postgres://drift@db.internal/rag")
    cur  = conn.cursor()
    for query_text, relevant_ids in pairs:
        cur.execute("""
            INSERT INTO golden_queries (query_text, relevant_ids, corpus_tier, language)
            VALUES (%s, %s, %s, %s)
        """, (query_text, relevant_ids, tier, lang))
    conn.commit()
Enter fullscreen mode Exit fullscreen mode

Step-by-step explanation.

  1. Diagnostic 1 confirms the corpus grew 30% in the last 10 days, concentrated on a specific ingestion day. This is a strong signal that the drop coincides with new content.
  2. Diagnostic 2 shows the drop is concentrated in the body/en slice — not language-wide, not tier-wide. Something specific to English body content.
  3. Diagnostic 3 samples the new content and confirms it is a new product line with vocabulary the pipeline had never seen before.
  4. The root cause: the golden set was calibrated on the pre-expansion corpus. Post-expansion, queries about the new product line have no golden entries, and the queries the golden set does have now compete against 30% more documents — some of which are marginally similar to the golden queries and displace true relevant docs from the top-10.
  5. The fix: expand the golden set with 30 queries covering the new product line, plus verify the recall metric on the expanded set. If recall recovers, drift was a coverage gap. If not, the model itself has degraded on the new vocabulary and a model migration is the answer.

Output.

Diagnostic Finding
Corpus size +30% in last 10 days
Recall by slice body/en dropped, others stable
Content sample new product line vocabulary
Root cause golden-set coverage gap
Fix expand golden set + rerun monitor
Recall after fix 0.87 (recovered)

Rule of thumb. Every corpus expansion needs a corresponding golden-set expansion. A drift alert that coincides with a corpus growth event is usually a coverage gap, not a model failure. Check the golden set first.

Senior interview question on drift monitoring

A senior interviewer might ask: "You have a production RAG pipeline that has been stable for a year. Suddenly the CS team is filing bugs about search quality. Walk me through your first-24-hour investigation — what you measure, what you rule in and out, and where the fix likely lives."

Solution Using a golden-set trend, a freshness scan, and a corpus growth diff 

First-24-hour drift investigation runbook

Hour 0-1 — Confirm the signal
  1. Sample 20 recent CS bug reports
  2. Reproduce each query against production
  3. Compare top-10 results to CS's expected answers
  4. Score: what fraction of CS bugs are genuine misses?

Hour 1-4 — Rule out infra
  1. Freshness monitor: any tier over 5% violation?
  2. Change-queue depth: is the pipeline behind?
  3. OpenAI status page: any provider incidents?
  4. Vector store health: HNSW index rebuild? Recent reindex?

Hour 4-8 — Golden-set trend
  1. Pull recall@k history for last 90 days
  2. Overlay corpus size, model version, index rebuild events
  3. Identify the inflection point — when did the drop start?
  4. Group by corpus tier and language to localise

Hour 8-12 — Content vs model classification
  1. If drop is in one tier + one language: content drift (coverage or bulk ingest)
  2. If drop is uniform: model drift (query distribution shifted)
  3. If drop is bimodal (queries split): mixed — needs both fixes

Hour 12-24 — Immediate fix + follow-up
  Content drift → expand golden set + re-embed affected slice + verify recovery
  Model drift  → begin shadow-column migration plan; feature-flag rollback ready
  Post-mortem  → add new golden queries; update runbook; freshness alert calibration
Enter fullscreen mode Exit fullscreen mode

Step-by-step trace.

Hour Action Rule in / out
0-1 CS bug reproduction Confirm real vs perception
1-4 Infra scan (freshness / queue / provider) Rule out pipeline stall
4-8 Golden-set trend Localise the drop
8-12 Content vs model classify Pick the right fix
12-24 Fix + follow-up Recovery + prevention

After the 24-hour investigation, the team has a documented root cause, a fix in flight, and a golden-set expansion queued. The runbook itself is the artefact — the next drift alert takes 4 hours instead of 24 because the questions are already answered.

Output:

Signal Finding Root cause Fix
Freshness 0.5% (clean)
Queue depth 40 (normal)
Provider no incident
Trend inflection 8 days ago
Slice body/en only Content drift Golden set + slice re-embed
Recall after fix 0.87 (recovered)

Why this works — concept by concept:

  • Freshness before recall — the cheapest cause to rule out is a stalled pipeline. Check freshness and queue depth before assuming model drift.
  • Trend, not point-in-time — a single day's recall number is noise. The 7d-vs-30d trend catches genuine regressions and ignores day-to-day variance.
  • Slice analysis — grouping by tier, language, and query cluster localises the drop. A uniform drop points to model; a concentrated drop points to content.
  • Golden-set as living document — every drift investigation ends with a golden-set expansion. The set gets sharper over time; the next drift is caught faster.
  • Cost — drift monitoring costs one API call per golden query per night (200 × $0.00002 × 30 = $0.12/month) plus one Prometheus scrape. The avoided cost of a delayed drift response — a week of degraded search — is a multi-order-of-magnitude win.

SQL
Topic — sql
SQL problems on golden query sets and recall@k trends

Practice →

Optimization
Topic — optimization
Optimization problems on drift alerts and freshness SLA

Practice →

Cheat sheet — embeddings refresh recipes

  • Hash-based change-queue schema. documents(doc_id PK, content, sha256_hash, embedding vector(D), model_tag, updated_at, embedded_at, embed_attempts). A BEFORE INSERT/UPDATE trigger keeps sha256_hash in sync. A partial index on (updated_at) WHERE embedded_at IS NULL OR embedded_at < updated_at keeps the queue scan O(queue_depth). The embed_queue view selects the changed rows; the worker takes LIMIT 256 FOR UPDATE SKIP LOCKED and upserts. Poison-pill guard: exclude rows where embed_attempts >= 3.

  • OpenAI batch embed call. Build a JSONL file with one {"custom_id": doc_id, "method": "POST", "url": "/v1/embeddings", "body": {"model": "text-embedding-3-small", "input": text}} per row. openai.files.create(purpose="batch") → openai.batches.create(endpoint="/v1/embeddings", completion_window="24h"). Poll openai.batches.retrieve(batch_id).status until completed. Batch tier is unconditionally 50% off; use it whenever freshness SLA > 24 h.

  • Cost estimator template. monthly_cost = docs × avg_tokens_per_doc × runs_per_month × price_per_1k / 1000 × tier_multiplier. Plug in $0.00002/1K for text-embedding-3-small, $0.00013/1K for text-embedding-3-large, 0.5 for batch tier. For self-hosted: gpu_hours × gpu_hourly where gpu_hours = tokens / (tokens_per_sec × 3600) — BGE-large on L4 does ~1000 tokens/sec. Realistic self-host TCO also includes ~0.25 FTE ($6K/month) plus amortised setup ($1250/month for 3 senior-engineer-weeks over 12 months).

  • Model version column pattern. Every row stores model_tag = '<model_name>@<version>' (e.g. text-embedding-3-small@v2). Retrieval SQL always filters WHERE model_tag = current_model — mixed-model retrieval is meaningless. Migrations are shadow-column (ADD embedding_v2 vector(new_dim)) → backfill (batch API) → dual-write for 1–2 weeks → cutover via transactional RENAME → 4-week rollback window → DROP old column. Feature-flag-gated read path enables 60-second rollback.

  • Recall@k drift monitor query. golden_queries(query_id, query_text, relevant_ids INT[], corpus_tier, language) stores the 100–500 curated pairs. Nightly runner embeds each query_text, runs SELECT doc_id FROM documents WHERE model_tag = current_model ORDER BY embedding <=> $1 LIMIT 10, computes recall = |retrieved ∩ relevant| / |relevant| and MRR, writes to recall_at_k_history(model_tag, corpus_tier, recall, mrr, ran_at). Alert: trailing 7-day avg < 30-day avg × 0.95.

  • Freshness SLA per tier. Head tier (titles / prices / stock): 5 minutes. Body tier (descriptions / docs): 4 hours. Long-tail (archived): 24 hours. Alert on > 5% row-count past SLA — SELECT corpus_tier, COUNT(*) FILTER (WHERE embedded_at < now() - tier_sla) * 100.0 / COUNT(*) FROM documents GROUP BY corpus_tier.

  • Three-tier refresh cadence. Streaming (CDC → Kafka → real-time API) for head tier; hourly cron + real-time API for body tier; nightly batch API for long-tail. Per-field embed_stream_at / embed_desc_at / embed_tax_at cursors so a change to one field re-embeds only that tier's vector.

  • Self-host crossover. OpenAI text-embedding-3-small batch tier vs self-hosted BGE-large-en-v1.5 on L4 spot. Realistic crossover is ~3B tokens/month once 0.25 FTE ops + amortised setup are included. Under that scale, managed API wins on TCO. Over that scale (and with an existing GPU-ops team), self-host wins by 3–5×.

  • Dual-write during migration. Every incoming write embeds against both old_model and new_model, populates both embedding and embedding_v2. Duration: 1–2 weeks — long enough for the recall@k monitor to compare on production traffic. Cost overhead: 2× normal write cost for the window. Cutover: transactional RENAME to swap columns. Rollback: feature flag flips read path back to embedding_old.

  • GDPR erasure via tombstone. deleted_at TIMESTAMPTZ column; app writes UPDATE documents SET deleted_at = now() on erasure request. embed_delete_queue view selects WHERE deleted_at IS NOT NULL AND embedding IS NOT NULL. Sync job NULLs the embedding column and calls the vector-store delete API. deleted_at stays populated forever as audit trail; embedding IS NULL is the "processed" flag.

  • Poison-pill dead letter. Rows that fail 3 embed attempts route to embed_dead_letter(doc_id, last_error, attempts, first_failed_at, last_failed_at). Nightly triage: reasons include oversized content (chunk further), content-policy violations (skip + flag), provider bug (retry after provider fix), corrupted UTF-8 (repair pipeline). Never leave the queue jammed on one poison pill.

  • Query-side model consistency. The query is embedded with the same model as the corpus. retrieve(q_text) reads current_model from config, embeds q_text with it, and filters WHERE model_tag = current_model in the SQL. Any mismatch — old query model against new corpus model, or vice versa — returns semantically arbitrary results.

  • Batch size for the API. OpenAI accepts up to 2048 inputs per embeddings request but the per-request throughput drops past ~256. Batch = 256 is the sweet spot: high enough for the network overhead to amortise, low enough for retries on partial failures to be cheap.

  • Vector store sync. pgvector — same table, one UPDATE. Pinecone — index.upsert([(id, vec, metadata)]). Weaviate — client.batch.add_data_object(...). All three support batch mode; use it. Always include model_tag as metadata so cross-model filtering works at the vector-store layer, not just the SQL layer.

Frequently asked questions

Do I need to re-embed my corpus periodically?

Yes, but only the rows that changed. Full-corpus re-embed nightly is the anti-pattern that runs up a 5-figure OpenAI bill for zero benefit. The right pattern is a content-hash + embedded_at cursor: WHERE embedded_at IS NULL OR embedded_at < updated_at. This turns a linear-in-corpus job into a linear-in-churn job — typically a 50× cost reduction with identical freshness. Full-corpus re-embed is only justified when (a) the embedding model itself changes (a migration event), (b) the content pre-processing pipeline changes (new chunking, new normalisation), or (c) as a periodic freshness reset every ~6 months to purge any accumulated inconsistencies. Otherwise, the incremental hash-driven refresh is the whole story.

How do I detect changed documents?

Three signals, in ascending order of freshness and complexity. Content hash + updated_at — the cheapest option, works with a plain Postgres cron job. A BEFORE INSERT/UPDATE trigger computes sha256_hash = digest(content, 'sha256'), and the embed worker selects WHERE embedded_at < updated_at AND sha256_hash != last_embedded_hash. CDC via Debezium — Postgres logical decoding streams every row change to Kafka; a consumer batches and embeds. Sub-second freshness; scales horizontally. ETL delta table — for stacks already on dbt or Airflow, a nightly changed_docs model provides the queue. Choose based on freshness SLA and existing infrastructure: hash + cron under 1M change events/day; CDC over that or when sub-minute freshness matters.

What does an embedding refresh cost?

monthly_cost = tokens × price_per_1k / 1000 × tier_multiplier. On OpenAI text-embedding-3-small at $0.00002/1K tokens, batch tier (50% off), a 5M-doc corpus with 400 tokens/doc and a 2% daily churn costs about $12/month. The same corpus on nightly full re-embed at real-time pricing is $600/month — a 50× cost delta from switching to incremental. Model choice adds another 6.5× spread: text-embedding-3-large at $0.00013/1K is 6.5× more expensive than text-embedding-3-small for a 5–10% recall gain. The right defaults: text-embedding-3-small on batch tier for the base cost, incremental change detection for the primary cost lever, and per-field tiered cadence for the last 30% of savings. Self-hosted BGE/E5 only breaks even at ~3B tokens/month once realistic TCO (GPU + 0.25 FTE + amortised setup) is included.

Should I self-host embedding models?

Only past ~3B tokens/month, or when API rate limits become a real bottleneck. The paper cost of self-hosting BGE-large on an L4 spot GPU is ~$0.00000005 per token — a fraction of OpenAI's text-embedding-3-small batch price. But realistic TCO includes ~0.25 FTE for on-call, model updates, and GPU orchestration (~$6K/month), plus amortised setup of ~3 senior-engineer-weeks. That is ~$7,000/month floor for self-hosting anything. Under 3B tokens/month, OpenAI batch is cheaper on TCO. Over that scale — a 50M-doc corpus refreshed weekly, or a high-throughput real-time inference workload — self-hosting starts to make real financial sense. The other reason to self-host is data-locality: some regulated industries (healthcare, defence) forbid sending customer content to external APIs, in which case the TCO conversation is moot.

When do I upgrade to a new embedding model?

Trigger on one of three signals. Recall@k improvement on the golden set. Benchmark the new model against the current on the same golden set; if recall@10 improves by 3+ points, the migration is likely worth it. Cost reduction. If a new model is materially cheaper for equivalent quality (e.g. text-embedding-3-small was cheaper than ada-002 at launch), migrate. Deprecation. Providers eventually deprecate old models; migrate before the deprecation window closes. The migration pattern is always the same four steps: shadow column → backfill on batch tier → dual-write validation window → transactional cutover, with a 4-week rollback window via column preservation and a feature-flag-gated read path. Total migration cost for a 20M-doc corpus is roughly $500 on batch tier — a small fraction of the recall or cost win that motivated the migration.

How do I monitor embedding drift?

A golden query set + a nightly recall@k trend. Curate 100–500 hand-labelled (query_text, relevant_doc_ids) pairs covering head queries, long tail, and adversarial cases. A nightly job embeds each query with the current model, retrieves the top-10 from pgvector filtered by model_tag = current_model, computes recall@10 and MRR against the labelled relevants, and writes the result to a recall_at_k_history table. Alert on trailing_7d_avg < trailing_30d_avg × 0.95 — a 5% relative drop that survives day-to-day noise. Group the metric by corpus_tier and language so a drop can be localised to a slice. Pair the recall monitor with a freshness monitor — now() - embedded_at > tier_sla — to catch the case where the pipeline stalled instead of the vectors degraded. Together, freshness + recall cover the whole failure surface: pipeline-not-running and pipeline-running-but-degrading.

Practice on PipeCode

  • Drill the SQL practice library → for the change-queue, hash-diff, and model-tag-filter problems senior interviewers love.
  • Rehearse on the ETL practice library → for the incremental refresh, CDC-triggered, and tiered-cadence pipelines that keep the OpenAI bill under control.
  • Sharpen the tuning axis with the optimization practice library → for the cost-tier, batch-sizing, and drift-alert problems.
  • Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis intuition (incremental, cost, versioning, drift) against real graded inputs.

Lock in embeddings-refresh muscle memory

Provider docs explain the API. PipeCode drills explain the decision — when to switch from nightly full to incremental, when to move to batch tier, when to migrate to a new model, when to alert on recall@k drift. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →

Top comments (0)