Category: Data Engineer

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.

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

TL;DR

• → Treating data import as one boolean is_valid check throws away useful information; structural validity, plausibility, and publish-readiness are separate questions that deserve separate gates.
• → Store nutrient values in one consistent unit contract (per 100g) and keep serving size as separate metadata, so no consumer has to guess what a number means.
• → Null, zero, and “omitted from this update” are three different states — collapsing them into one makes incomplete records look complete and produces false-confidence calculations downstream.
• → Relational checks (does sugar exceed total carbs? does the stated calorie count match the macro math?) catch bad records that individually pass simple range checks.
• → Partial updates are more dangerous than full imports: a naive upsert that blindly assigns every incoming field can silently null out good data the source simply didn’t include that day.
• → At 4.2 million records, rare edge cases stop being rare — the highest-value tests target invariants like “an omitted delta nutrient preserves the stored value,” not just a successful job exit code.

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:

calories_kcal  = energy per 100 g
protein_g      = protein per 100 g
sodium_mg      = sodium per 100 g
serving_size_g = optional weight of one stated serving
serving_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:

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:

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"}:
        return value / 1000
    if normalized in {"g", ""}:
        return value * 1000

    # Unknown is not the same as zero.
    return None

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:

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:

Field	Plausible range
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

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),
}

def outside_range(record):
    failures = []
    for field, (low, high) in RANGES.items():
        value = record.get(field)
        if value is not None and not low <= value <= high:
            failures.append(f"{field}:outside_range")
    return failures

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

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
    ):
        failures.append("sugar_exceeds_carbohydrate")

    if (
        food.get("saturated_fat_g") is not None
        and food.get("fat_g") is not None
        and food["saturated_fat_g"] > food["fat_g"] + 0.5
    ):
        failures.append("saturated_fat_exceeds_total_fat")

    macros = ("protein_g", "carbs_g", "fat_g")
    if food.get("calories_kcal") and all(food.get(x) is not None for x in macros):
        estimated = (
            food["protein_g"] * 4
            + food["carbs_g"] * 4
            + food["fat_g"] * 9
        )
        stated = food["calories_kcal"]
        if abs(estimated - stated) > max(120, stated * 0.5):
            failures.append("energy_macro_mismatch")

    return failures

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:

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:

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:

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()
WHERE foods.name IS DISTINCT FROM EXCLUDED.name
   OR foods.calories_kcal IS DISTINCT FROM
      COALESCE(EXCLUDED.calories_kcal, foods.calories_kcal)
   OR foods.protein_g IS DISTINCT FROM
      COALESCE(EXCLUDED.protein_g, foods.protein_g)
   OR foods.fat_g IS DISTINCT FROM
      COALESCE(EXCLUDED.fat_g, foods.fat_g)
   OR foods.carbs_g IS DISTINCT FROM
      COALESCE(EXCLUDED.carbs_g, foods.carbs_g);

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:

source
source_id
confidence
created_at
updated_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:

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:

missing_name
invalid_number
placeholder_barcode
outside_range
energy_macro_mismatch
partial_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