Data platform · Generative AI

Market price intelligence platform

Ingests Colombia's weekly wholesale price bulletins, forecasts 52 weeks per product, and has a model write the reading of each series.

Repo: mercaldas-precios-mercado
Context: Mercaldas
Role: Sole engineer, design to operation
Status: In production since 2025

The problem

The national statistics office publishes wholesale prices as weekly bulletins built for humans, not machines. Buyers had no outside benchmark, so internal prices were negotiated blind.

Stack

Python
Polars
Delta Lake
XGBoost
LightGBM
FastAPI
HTMX
Anthropic API

What it does

Hundreds
Products covered, with years of price history each
52 weeks
Forecast horizon, model chosen by multi-horizon MAPE
Internal tool to product
Now authenticated and internet-facing, used in supplier negotiation
Cached by hash
An AI reading is never paid for twice on unchanged data

Try it yourself

Interactive

Market price intelligencemercaldas-precios-mercado · demo buildSimulation · synthetic data

Product

Latest
$2,006
COP / kg
26 weeks
−13.9%
vs. six months ago
Forecast +26
$2,122
end of horizon
Volatility
2.1%
largest weekly move

Market price (DANE)
Forecast
Forecast interval

Table view

Week	Market price (DANE)	Forecast
w-51	$2,034	n/a
w-50	$2,033	n/a
w-49	$2,045	n/a
w-48	$2,083	n/a
w-47	$2,088	n/a
w-46	$2,110	n/a
w-45	$2,140	n/a
w-44	$2,154	n/a
w-43	$2,167	n/a
w-42	$2,198	n/a
w-41	$2,207	n/a
w-40	$2,239	n/a
w-39	$2,241	n/a
w-38	$2,247	n/a
w-37	$2,248	n/a
w-36	$2,274	n/a
w-35	$2,314	n/a
w-34	$2,310	n/a
w-33	$2,345	n/a
w-32	$2,335	n/a
w-31	$2,362	n/a
w-30	$2,389	n/a
w-29	$2,360	n/a
w-28	$2,330	n/a
w-27	$2,321	n/a
w-26	$2,331	n/a
w-25	$2,313	n/a
w-24	$2,268	n/a
w-23	$2,221	n/a
w-22	$2,201	n/a
w-21	$2,185	n/a
w-20	$2,143	n/a
w-19	$2,149	n/a
w-18	$2,131	n/a
w-17	$2,093	n/a
w-16	$2,052	n/a
w-15	$2,043	n/a
w-14	$2,009	n/a
w-13	$1,978	n/a
w-12	$1,986	n/a
w-11	$1,974	n/a
w-10	$1,988	n/a
w-9	$1,965	n/a
w-8	$1,949	n/a
w-7	$1,958	n/a
w-6	$1,961	n/a
w-5	$1,976	n/a
w-4	$1,966	n/a
w-3	$1,954	n/a
w-2	$1,959	n/a
w-1	$1,988	n/a
w0	$2,006	$2,006
+1	n/a	$1,936
+2	n/a	$1,954
+3	n/a	$1,972
+4	n/a	$1,989
+5	n/a	$2,007
+6	n/a	$2,025
+7	n/a	$2,042
+8	n/a	$2,058
+9	n/a	$2,074
+10	n/a	$2,089
+11	n/a	$2,103
+12	n/a	$2,116
+13	n/a	$2,128
+14	n/a	$2,138
+15	n/a	$2,146
+16	n/a	$2,153
+17	n/a	$2,158
+18	n/a	$2,161
+19	n/a	$2,163
+20	n/a	$2,162
+21	n/a	$2,160
+22	n/a	$2,156
+23	n/a	$2,150
+24	n/a	$2,142
+25	n/a	$2,133
+26	n/a	$2,122

Written reading

In the real platform a model reads the series and writes this paragraph once; the result is cached against a hash of the data it was given, so the same series is never paid for twice. Here the wording is fixed and the figures inside it are computed from the series above, which is why the prose and the chart never disagree.

The real platform covers hundreds of products with several years of history and caches each written reading by payload hash. This miniature carries three invented series; nothing here comes from DANE or from Mercaldas.

Context

Colombia's statistics office publishes wholesale agricultural prices every week through SIPSA. Public, authoritative, free, and built for a person to read: sheet names and column positions move between releases, units are declared per sheet, and history exists only as a pile of separate files. No API, no contract, no warning when it changes.

The business problem was simpler than the data problem. Buyers negotiated with no external reference for what the market was doing.

Architecture

From a weekly PDF bulletin to a price reading the buyers act on

Ingestion assumes drift, because the source drifts. The parser resolves structure by content rather than position. Unit normalisation happens per sheet, not once globally. Everything lands in Bronze exactly as received, so a wrong parsing assumption is a reprocess, not a re-scrape of data that may be gone.

Delta Lake over flat Parquet, for one reason: incremental upserts. A weekly bulletin frequently restates prior weeks. With plain Parquet that means rewriting partitions and hand-rolling merge logic. With Delta it is a transactional merge. That is the feature that justified the dependency.

Polars throughout. On a shared machine, its lazy execution does the work in a fraction of the memory a pandas-shaped pipeline needs.

Forecast selection by multi-horizon MAPE. A model that is excellent at week one and useless at week thirty is worse than one that is merely good throughout, because a buyer planning a season reads the far end of the curve.

FastAPI with HTMX, no single-page app. Partial updates over the wire with server-rendered HTML: no separate frontend build, no duplicated client state, no framework upgrade treadmill for a team of one.

The hard part

Making the AI layer economical. A model writing the reading of each series is what turns a chart into something a buyer acts on. It is also a per-view cost on data that does not change. So readings are cached in Delta Lake, keyed by a hash of the payload the model was given. Identical input means the answer already exists. Keying on the payload rather than the product means the cache invalidates itself correctly when the data moves, with no expiry heuristic to tune. I documented the pattern as reusable.

The unit bug is the one I tell in interviews. Milled rice prices were ingested an order of magnitude off, for a long stretch of history, because that product's sheet declares its unit differently from every other sheet. Nothing crashed. The series looked like a series. It was just wrong, in a way that would have quietly poisoned any negotiation leaning on it.

Finding it meant diffing against an external reference. A series can be perfectly self-consistent and perfectly wrong. The fix was per-sheet normalisation plus a full historical backfill.

Trade-offs

No orchestrator yet, on purpose. The weekly run is a manual sequence. Adding Dagster is about a day of work, and it is deliberately not done: ingest is still the part most likely to need a human when the source drifts, and automating around an unstable boundary buys unattended failure rather than unattended success.

Cached readings can go stale. The cache key is the data, not the model version. Right default at this cost profile, and the escape hatch is a version component in the key.

Results

Hundreds of products, several years of history each, forecast 52 weeks ahead, with hundreds of tests in CI. This year it moved from an internal tool to an authenticated, internet-facing application used in supplier negotiation.

Business figures are published as ranges. The company is named, the exact numbers are not mine to publish.

Technical metrics

Hundreds
Automated tests running in CI
Delta Lake
Chosen over flat Parquet for incremental upserts
HTMX
Reactive UI without the weight of a single-page app

Mercaldas is named as the employer, but business figures on this page are published as ranges, orders of magnitude or relative percentages. Exact revenue, margin and volume figures, source code and screenshots of real operating data are not published.