Mexico Jobs Analytics Pipeline

Situation

Raw job-listing snapshots are worthless until they're clean, queryable, repeatable analytics assets. Manual reporting is slow and inconsistent. The constraint was free infrastructure: no server, no hosting cost.

Task

Turn periodic raw snapshots into curated datasets + bilingual reports + a public docs site, reproducibly and without manual intervention, on infrastructure that costs nothing to run.

Action

• A 6-stage pipeline on ephemeral GitHub Actions runners: each run checks out the full upstream snapshot history, rebuilds an authoritative DuckDB store from scratch (CREATE OR REPLACE, no incremental state), and exports Parquet sidecars alongside it.
• A single schema-locked API call returns both English and Spanish narrative in one response, with a headline and three bullets per language. Two render passes consume the same object to produce bilingual Markdown and HTML reports that cannot drift from each other.
• A strict MkDocs build gates the GitHub Pages deploy, blocking publication on any warning. Runs on a weekly cron plus a monthly rollup; a Cloud Run delivery path is built and contract-tested but GitHub Actions is the live path.

How it works

Click to zoom

Six stages on ephemeral CI: workspace → curate (DuckDB authoritative, CREATE OR REPLACE) → report (one schema-locked OpenAI call → EN/ES) → site → strict MkDocs deploy gate. Cloud delivery is built but dashed; GitHub Actions is the live path.

Result

Twenty of twenty scheduled runs have succeeded since 2026-03-30, about fifteen weeks fully unattended (16 weekly + 4 monthly bilingual bundles, GitHub-API verified). The latest run rebuilt 13,152 raw job snapshots (2026-03-22 to 2026-07-13) into 3 curated DuckDB tables and distilled the closed week (2026-W28) into 342 curated jobs, published as a bilingual report and a public CSV. Backed by 78 tests on Python 3.11.

Learning

DuckDB-in-CI made compute trivially reproducible. Rebuilding the whole store from upstream history every run was deterministic and debuggable for free at ~9.6k rows, with no server to host Postgres on anyway. But I learned state has to live somewhere: the stateless rebuild quietly turned my 'archive' into 'latest issue only,' so the public history evaporated and took ten backfill runs to restore. Next time the compute stays stateless, but a small hosted DB or a committed-state branch keeps the archive.