How we standardized SNIIM wholesale prices

We turn 47 SNIIM reports (25 distinct source schemas) into a single canonical model: one fact table, shared dimensions and explicit unit and data-quality rules.

Generated from sniim-extractor/src/core/canonical.py — do not edit by hand. Regenerate with sniim-extractor/.venv/bin/python scripts/build_standardization_note.py.

Why standardize

SNIIM publishes 47 independent reports: daily bulletins per wholesale market for agricultural, livestock and fisheries products. Each report ships as its own table, with column names, units and assumptions that change between sectors and sometimes within a sector.

Unstandardized, those tables are not an analysis surface: the price arrives in a column called Precio Mín in one bulletin, Pmín in another and Pechuga_Mín($/kg) in a third; the same column mixes prices per caja, manojo, piece or kilogram; and 0.00 means "not reported", not "free".

The canonical model resolves those differences once, in the pipeline, so one query works across all three sectors and every price can be compared per kilogram.

Before and after

Four real reports, each in its source shape, and the rows the pipeline produces. Values are illustrative; the transformation is the same one scripts/build_catalog.py runs on the full data.

frutas_y_hortalizas — Agricultural

The same observation in prices (one row per price component):

destino_pescados_marinos — Fisheries

The same observation in prices (one row per price component):

aves_pollo_pza — Livestock

The same observation in prices (one row per price component):

azucar — Agricultural

The same observation in prices (one row per price component):

Column mapping

Table generated from the core.canonical.ENDPOINTS registry. Every source column lands in a canonical field or in a measure with its price_kind. standardization-note.json carries the column-by-column mapping for all 47 tables.

Agricultural (5 endpoints)

Livestock (22 endpoints)

Fisheries (20 endpoints)

Unit and price rules

Presentation → unit → price per kilo

parse_presentation maps the published presentation to a unit (kg, caja, manojo, docena, litro, …). kg_per_unit is only filled when the bulletin states a weight — Caja de 10 kg, Caja con 48 latas de 425 gr — or when the presentation is the kilogram. With a weight, price_per_kg = price_mxn / kg_per_unit; without one, price_per_kg is NULL and presentations.conversion_note says why.

Market → state, name and kind

parse_market splits the state prefix (Q.R., N.L., Michoacán) from the market or plant name and classifies the kind (mercado, rastro_planta, obrador, zona_metropolitana, nacional). The raw label is kept in market_aliases, so every row can be traced back to the exact bulletin text. The catalogue resolves 1,060 labels into 227 canonical markets.

Data quality

Rules are applied at build time and every affected value is recorded in dq_issues; nothing is corrected silently.

Compatibility views

The 47 original table names remain available as views rebuilt from observations + prices, with the same columns in the same order as the bulletin. scripts/verify_catalog.py --roundtrip compares every non-price column row by row: if a view drifts from the source, the build fails.

The result in numbers

Counts from the endpoint registry and the last built catalog (docs/catalog-stats.json, 2026-11-05):

Limits

The model does not invent weights: 90.5% of price rows convert to MXN/kg; the rest are piece, bunch, dozen or litre presentations where the bulletin publishes no weight. Those rows are kept with price_per_kg NULL and a conversion note.

This note describes the pipeline, not the quality of the upstream data: coverage gaps and SNIIM corrections are documented separately in _manifest.json and the data_gaps ledger.