Translating a product catalog with an LLM looks like a batch job: read rows, call the model, write rows back. It stops looking like that the first time a supplier re-uploads the same feed and you pay again to translate thousands of descriptions that did not change by a single byte.
I have run LLM pipelines that translate and adapt product catalogs for international audiences, and the part nobody warns you about is that almost none of the difficulty is in the prompt. It is in deciding what "the same input" means, and then letting that definition own your storage.
The constraint: catalogs are re-imported, not edited
A catalog that you own in your own database is a set of rows that get updated. A catalog that arrives from suppliers, parsers or partner feeds is a set of snapshots that get replaced. Nobody sends you a reliable updated_at. Titles come back with a different amount of whitespace, a different dash character, a supplier-side capitalization change, or a marketing suffix appended and later removed. The row identity is stable; the content identity is not.
If you drive translation off row identity, you either retranslate everything on every import, or you trust a dirty flag that is wrong often enough to ship stale copy to production. If you drive it off a hash of the whole item, one changed field invalidates every field on that item, including the long description that costs the most to translate.
So the unit of work is not the item and not the import. It is one field, in one target locale, under one prompt and one model.
Key the output by normalized content
The key has to include everything that can change the output. That is the source text, the target locale, the kind of field (a title and a bullet list want different instructions), the prompt version, the model identifier, and the glossary revision if you pin brand terms that must not be translated.
Normalization matters as much as hashing. Feeds are noisy, and you want cosmetic noise to collapse into the same key while real edits do not.
import hashlib
import unicodedata
import re
WHITESPACE = re.compile(r"\s+")
def normalize(text: str) -> str:
text = unicodedata.normalize("NFKC", text)
text = text.replace("\u00a0", " ")
text = WHITESPACE.sub(" ", text)
return text.strip()
def content_key(
*,
text: str,
field_kind: str,
target_locale: str,
prompt_version: str,
model: str,
glossary_version: str,
) -> str:
payload = "\x1f".join(
[
normalize(text),
field_kind,
target_locale,
prompt_version,
model,
glossary_version,
]
)
return hashlib.sha256(payload.encode("utf-8")).hexdigest()
Two details are worth being deliberate about. Use a separator that cannot appear in the parts, so that a locale ending in a digit and a prompt version starting with one cannot collide into the same string. And do not normalize case: a supplier shouting a title in capitals is a real difference that a translator should see.
Normalization is a one-way decision. Whatever you fold away today, you cannot recover from the key tomorrow, so keep it to things that are genuinely cosmetic. Trimming and whitespace collapsing are safe. Stripping punctuation is not.
The store is the pipeline
Once the key exists, the translation service has no scheduling logic left in it. It is a lookup, a call on miss, and an insert.
CREATE TABLE translation (
content_key TEXT PRIMARY KEY,
target_locale TEXT NOT NULL,
field_kind TEXT NOT NULL,
model TEXT NOT NULL,
prompt_version TEXT NOT NULL,
source_text TEXT NOT NULL,
output_text TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX translation_locale_idx ON translation (target_locale, field_kind);
async def translate_field(conn, client, *, text, field_kind, locale) -> str:
key = content_key(
text=text,
field_kind=field_kind,
target_locale=locale,
prompt_version=PROMPT_VERSION,
model=MODEL,
glossary_version=GLOSSARY_VERSION,
)
row = await conn.fetchrow(
"SELECT output_text FROM translation WHERE content_key = $1", key
)
if row is not None:
return row["output_text"]
output = await client.translate(
text=text, field_kind=field_kind, locale=locale
)
await conn.execute(
"""
INSERT INTO translation (
content_key, target_locale, field_kind, model,
prompt_version, source_text, output_text
)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (content_key) DO NOTHING
""",
key, locale, field_kind, MODEL, PROMPT_VERSION, text, output,
)
return output
ON CONFLICT DO NOTHING is doing real work here. Two workers processing two items that happen to share a title — variants of the same product, or a supplier who copies boilerplate across a category — will race on the same key. Both calls are already paid for by then; the point is that neither write fails and neither overwrites the other.
The same property makes the import job restartable with no bookkeeping at all. Kill it halfway, run it again, and it resumes at cost proportional to what is left rather than to what exists.
Fan out per field, persist per field
The second failure mode is subtler than cost. An item has a title, a short description, a long description, a few attributes. If you translate them in one call and one of them trips a content filter or times out, the naive pipeline throws away the whole item and retries all of it. Do that inside a retry loop and a single stubborn field will burn the budget for every field around it, forever, on every run.
Translate fields independently and persist each success on its own.
import asyncio
async def translate_item(conn, client, item, locale) -> dict:
fields = [
("title", item.title),
("short_description", item.short_description),
("long_description", item.long_description),
]
async def one(field_kind, text):
if not text:
return field_kind, None
try:
value = await translate_field(
conn, client, text=text, field_kind=field_kind, locale=locale
)
except TranslationFailed:
return field_kind, None
return field_kind, value
results = await asyncio.gather(*(one(k, t) for k, t in fields))
return {k: v for k, v in results if v is not None}
A partially translated item is a normal state, not an error state. The rendering layer falls back to the source locale for any field that is missing, and the next run retries only the gaps. That is the difference between a pipeline that converges over a few runs and one that oscillates.
What you must not do is cache the failure under the content key. A timeout is a property of the attempt, not of the text. If you want to stop hammering a field that fails deterministically, count attempts in a separate table keyed by the same content key, and let the reader decide when to give up. Keep the two concerns apart: one table is what the model said, the other is how the infrastructure behaved.
Prompt version is a migration, not a config value
Because the prompt version is inside the key, bumping it invalidates every cached output for every locale. That is correct — a changed prompt means changed output — but it means the version string is one of the most expensive variables in the system. Treat editing it the way you treat a schema migration: it happens deliberately, it gets reviewed, and you know what it will cost before you merge it.
The upside is that rollback is free. Nothing was overwritten. Point the pipeline back at the previous prompt version and the old outputs are still sitting there under their own keys, ready to be served again.
What I would do differently
I would put prompt_version and model in the key from the very first commit. It is obvious in hindsight and easy to skip when there is exactly one prompt and one model, and the moment you add a second one you are looking at a table of outputs you cannot attribute and therefore cannot trust.
I would separate "translated" from "approved" earlier. Machine output and human-reviewed output are different lifecycle states, and once a reviewer has touched a string you need the pipeline to never quietly replace it. Overloading one column with both meanings is the kind of shortcut that costs you a reviewer's afternoon the first time a re-import lands.
I would also keep the source text in the row, as above, even though it is redundant with the key. Hashes are not reversible, and the day you need to audit why a locale reads strangely, having the exact input that produced the output is worth far more than the storage it takes.
Close
The useful reframe is that an LLM call is a pure function of its inputs, and every input that is not the text — the locale, the field kind, the prompt, the model, the glossary — belongs in the key. Once that is true, the batch job disappears. What is left is a cache with a slow, expensive miss path, and caches are a problem the industry solved a long time ago.
Originally published on polycratia.com — where I write about payment systems, crypto rails and marketplace backends.
