Processing millions of food records taught me that data quality is rarely one clever cleaning function. It is a chain of small contracts: what a number means, which identifier owns a record, what a missing value means, and what an incremental update is allowed to change.

I learned this while building DietlyAPI, a nutrition API backed by more than 4.2 million indexed food records. Much of the catalog originates from Open Food Facts, a valuable worldwide crowdsourced database. That scale and openness are useful, but they also expose every awkward case a data pipeline eventually encounters: mixed units, incomplete labels, placeholder barcodes, duplicate products, implausible nutrition values, and partial updates.

This article explains the patterns that made the pipeline safer. The examples are simplified, but the failure modes are real.

Food pipeline architecture x class=

Validation happens at more than one boundary in this pipeline — ingestion protects structure, serving enforces trust, converted here from the original mermaid flowchart.

The Pipeline Is a Series of Trust Boundaries

My first mistake was thinking about the import as one operation:

Read a source file, clean each row, and insert it.

In production, there are several separate decisions:

  1. Can the source record be parsed?
  2. Can its fields be mapped to a stable internal schema?
  3. Is the record identifiable across future imports?
  4. Are its values plausible enough to store?
  5. Is it complete and trustworthy enough to rank highly or publish?
  6. Can a later partial update safely modify it?

Treating those questions as one boolean is_valid check loses useful information. A record with a name and barcode but no calories may still be worth retaining. A record with impossible calories should not appear in a “popular foods” response. A partial daily update should not delete fields simply because the source omitted them.

The important detail is that validation happens at more than one boundary. Ingestion protects the database from malformed structure. Serving and publishing apply stricter quality rules appropriate to their users.

Lesson 1: Define the Unit Contract Before Writing Transformations

Nutrition sources frequently mix:

  • values per 100 grams;
  • values per serving;
  • grams, milligrams, and micrograms;
  • kilocalories and kilojoules;
  • numbers and human-readable strings such as 1 cup (240 g).

If these representations leak into the application layer, every consumer must guess what each number means. That guarantees inconsistent calculations.

Dietly’s internal contract stores nutrient values per 100 grams. Serving information is separate metadata:

Snippet 5 lines

Code example — copy the snippet, then match it to your project.

calories_kcal  = energy per 100 gprotein_g      = protein per 100 gsodium_mg      = sodium per 100 gserving_size_g = optional weight of one stated servingserving_desc   = original display text, such as "1 cup (240 g)"

That separation matters. A serving_size_g of 30 does not mean the stored calories are already scaled to 30 grams. Consumers can calculate a serving explicitly:

Python 2 lines

Python example — skim the functions, then copy what you need.

def for_serving(per_100g: float, serving_size_g: float) -> float:    return per_100g * serving_size_g / 100

Unit conversion should occur once, close to ingestion:

Lesson 1: Define the Unit Contract Before Writing Transformations: excerpt of this Python example. This is a shortened excerpt of a 14-line script.
def to_milligrams(value, unit):
    if value is None:
        return None

    normalized = (unit or "g").lower()
    if normalized == "mg":
        return value
    if normalized in {"µg", "mcg", "ug"}:
…

The remaining 6 lines stay in the interactive article so this page remains a written walkthrough rather than a raw Python dump.

The final line is deliberately conservative. Silently guessing an unfamiliar unit creates a valid-looking wrong value, which is harder to detect than a null.

The same rule applies to failed parsing:

Python 7 lines

Python example — skim the functions, then copy what you need.

def parse_number(raw):    if raw is None or not str(raw).strip():        return None    try:        return float(raw)    except ValueError:        return None

In nutrition data, zero is a claim. Null means “not known.” Converting missing values to zero makes incomplete products appear complete and can produce dangerously confident downstream calculations.

Lesson 2: Validate Relationships, Not Only Individual Columns

A schema can confirm that calories are numeric, but it cannot tell you whether 6,000 kcal per 100 grams is credible. Simple range checks catch many broken rows:

FieldPlausible range
calories_kcal0 – 900
protein_g0 – 100
fat_g0 – 100
carbs_g0 – 100
fiber_g0 – 100
sugar_g0 – 100
serving_size_g0 – 2000
Lesson 2: Validate Relationships, Not Only Individual Columns: excerpt of this Python example. This is a shortened excerpt of a 17-line script.
RANGES = {
    "calories_kcal": (0, 900),
    "protein_g": (0, 100),
    "fat_g": (0, 100),
    "carbs_g": (0, 100),
    "fiber_g": (0, 100),
    "sugar_g": (0, 100),
    "serving_size_g": (0, 2000),
…

The remaining 9 lines stay in the interactive article so this page remains a written walkthrough rather than a raw Python dump.

However, many bad records contain values that are individually believable but mutually inconsistent. Relational checks are more powerful:

Lesson 2: Validate Relationships, Not Only Individual Columns: excerpt of this Python example (part 2). This is a shortened excerpt of a 29-line script.
def plausibility_failures(food):
    failures = []

    if (
        food.get("sugar_g") is not None
        and food.get("carbs_g") is not None
        and food["sugar_g"] > food["carbs_g"] + 0.5
    ):
…

The remaining 21 lines stay in the interactive article so this page remains a written walkthrough rather than a raw Python dump.

These tolerances are intentionally broad. Food labels round values, fiber and alcohol complicate energy calculations, and source conventions vary. The purpose is not to “correct” every label mathematically. It is to catch extreme contradictions before they are promoted, summarized, or used to generate authoritative-looking content.

That led to another useful distinction:

  • Hard structural checks decide whether a record can enter storage.
  • Quality gates decide whether it can appear in high-trust surfaces.
  • Ranking signals decide which acceptable record should appear first.

A sparse record may remain searchable without being selected for a featured-food endpoint. Keeping these policies separate avoids throwing away potentially useful data.

Lesson 3: Identity Is Not the Same as a Barcode

It is tempting to use a barcode as the universal product key. In real data, that fails for several reasons:

  • some records have no barcode;
  • scanner noise and hand-entered placeholders exist;
  • the same source record may be updated while keeping its source identifier;
  • different sources can use different identifiers for the same food;
  • similar products are not necessarily the same product.

Dietly uses source provenance as the idempotency key:

SQL 2 lines

SQL example — read the query, then copy it into your warehouse.

CREATE UNIQUE INDEX idx_foods_source_id    ON foods (source, source_id);

This answers a narrow but essential question: “Have I already imported this exact source record?” It does not pretend to solve global entity resolution.

Known placeholder barcode patterns are removed rather than used for lookups. Returning no barcode match is safer than returning a confidently wrong product.

Product deduplication then becomes a separate serving-layer concern. Search candidates can be grouped using a normalized name key — case-folding, punctuation removal, and collapsing repeated words — and the best row can be selected using signals such as:

  • presence of an image;
  • serving information;
  • complete core macros;
  • realistic ranges;
  • number of populated nutrient fields;
  • source confidence.

This approach does not claim that all duplicates disappear. Instead, it prevents weak duplicates from dominating common queries while preserving the original rows and their provenance.

Lesson 4: Partial Updates Are More Dangerous Than Full Imports

The most instructive failure appeared in the incremental pipeline. During one update, eight already-published food pages lost their calorie values and dropped out of the page build. The import had completed successfully; the data had still become worse.

A full export contains a broad set of fields. A daily delta may contain only the fields that are currently present upstream. If an upsert blindly assigns every incoming field, an omitted value becomes SQL NULL and can erase good data already stored.

The unsafe version looks reasonable:

SQL 4 lines

SQL example — read the query, then copy it into your warehouse.

ON CONFLICT (source, source_id) DO UPDATE SET    calories_kcal = EXCLUDED.calories_kcal,    protein_g     = EXCLUDED.protein_g,    fat_g         = EXCLUDED.fat_g;

But it treats “not included in this update” as “delete the existing value.”

The safer policy for Dietly’s source is to preserve stored nutrition when a delta omits it:

Lesson 4: Partial Updates Are More Dangerous Than Full Imports: excerpt of this SQL example. This is a shortened excerpt of a 17-line script.
ON CONFLICT (source, source_id) DO UPDATE SET
    name          = EXCLUDED.name,
    calories_kcal = COALESCE(EXCLUDED.calories_kcal, foods.calories_kcal),
    protein_g     = COALESCE(EXCLUDED.protein_g, foods.protein_g),
    fat_g         = COALESCE(EXCLUDED.fat_g, foods.fat_g),
    carbs_g       = COALESCE(EXCLUDED.carbs_g, foods.carbs_g),
    image_url     = COALESCE(EXCLUDED.image_url, foods.image_url),
    updated_at    = NOW()
…

The remaining 9 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

There are two protections here.

First, COALESCE encodes the meaning of a missing delta field. This policy is source-specific: if an upstream system supports explicit deletion, it should send a deletion marker rather than relying on null.

Second, IS DISTINCT FROM avoids rewriting unchanged rows. At millions of records, unnecessary updates create write-ahead-log traffic, dead tuples, index churn, and disk pressure. Idempotency is an operational feature, not only a correctness property.

The delta cursor is committed after each successfully processed file. If a job stops halfway through a series, it resumes from the last committed file instead of replaying the entire history or skipping uncommitted work.

Lesson 5: Preserve Provenance All the Way to the API

Once several sources share one table, it becomes easy to flatten away where a value came from. That makes later debugging and trust decisions much harder.

Each Dietly row retains fields such as:

Snippet 5 lines

Code example — copy the snippet, then match it to your project.

sourcesource_idconfidencecreated_atupdated_at

Provenance supports practical questions:

  • Which source produced this suspicious value?
  • Can the record be re-imported deterministically?
  • Should one source rank above another for this query?
  • Which rows were affected by yesterday’s delta?
  • What attribution or license applies downstream?

Confidence is best treated as a ranking input, not proof that a value is correct. A high-confidence source can still contain an error, while an incomplete crowdsourced record can still be useful.

Open Food Facts data is available under the Open Database License, so attribution and downstream license obligations also need to survive the journey from source to product.

Lesson 6: Test the Failure Policy, Not Just the Happy Path

Row counts and successful job exits are weak evidence of pipeline health. A pipeline can finish successfully after replacing thousands of values with null.

The highest-value tests in this system target invariants:

  • importing the same record twice does not create a duplicate;
  • an omitted delta nutrient preserves the stored value;
  • a changed nutrient updates the stored value;
  • an unchanged record is not rewritten;
  • placeholder barcodes cannot produce a false lookup;
  • values outside realistic ranges cannot enter high-trust responses;
  • public response fields remain backward-compatible.

For SQL generation, even a focused regression test can prevent a repeat:

Snippet 4 lines

Code example — copy the snippet, then match it to your project.

def test_partial_updates_preserve_nutrition():    sql = UPSERT_SQL.upper()    for column in ("CALORIES_KCAL", "PROTEIN_G", "FAT_G", "CARBS_G"):        assert f"COALESCE(EXCLUDED.{column}" in sql

In addition, record rejection or suppression reasons as categories rather than a single invalid count:

Snippet 6 lines

Code example — copy the snippet, then match it to your project.

missing_nameinvalid_numberplaceholder_barcodeoutside_rangeenergy_macro_mismatchpartial_update_preserved

Their trends reveal upstream schema changes faster than inspecting random rows. A sudden jump in invalid_number, for example, may indicate a delimiter or unit change rather than a genuine decline in data quality.

A Practical Checklist

Before calling a large ingestion pipeline reliable, I now ask:

  1. Does every numeric field have a documented unit and reference basis?
  2. Are null, zero, deletion, and omission distinct states?
  3. Is the idempotency key tied to source identity?
  4. Are structural validation, quality gating, and ranking separate?
  5. Do checks cover relationships between fields?
  6. Can partial updates erase existing values?
  7. Do unchanged upserts avoid physical rewrites?
  8. Is source provenance retained in storage and responses?
  9. Can interrupted incremental jobs resume safely?
  10. Do tests reproduce the pipeline’s previous failures?

At 4.2 million records, rare edge cases stop being rare. A one-in-a-million parsing issue is no longer hypothetical, and a harmless-looking upsert can become millions of unnecessary writes.

The central lesson was simple: reliable data quality does not mean making every source row perfect. It means making uncertainty explicit, containing bad values, preserving what is already known, and ensuring that retries produce the same result.

That is less glamorous than the word “pipeline” sometimes suggests. It is also what makes the pipeline dependable.

Related reading: DietlyAPI · Open Food Facts · Open Database License