How the Kansas cattle market data was built
The pipeline and the decisions behind it
Pipeline overview
On a phone, swipe the diagram sideways.
The data comes from three USDA Market News reports (the Kansas Weekly Cattle Auction Summary, the Kansas Direct Cattle report and the Kansas Direct Hay report) and the U.S. Drought Monitor.
The site is static in between weekly rebuilds. Evidence queries the database when it builds and ships the results as files.
Designed to cost almost nothing
The only bill for this project is the domain, about $11 a year. Everything else is on a free plan.
| Tool | How it's used | The limit | Impact |
|---|---|---|---|
| Neon (Postgres) | The database | 5 GB of monthly network transfer, and the database sleeps when idle | Each build loads only the columns the reports use, cutting the data pulled per rebuild by about 80%. Dashboards read at build time, so a sleeping database never breaks them. |
| GitHub Actions | The four weekly loads and the weekly rebuild trigger | Free on public repos | A scheduled job does the recurring work, so no server stays running. |
| Netlify | Builds and hosts the dashboards | 300 credits a month, 15 per deploy | The site rebuilds once a week, not on every change, and edits are batched into a few deploys. |
| Evidence | Builds the dashboards | Open source | A static site: nothing runs per visitor. |
Decisions
One workflow per source. Each of the four reports has its own script and its own GitHub Actions workflow, staggered an hour apart on Monday mornings. If one source breaks, the others still load, and the failing run is easy to find.
Safe to re-run. Every table has a key generated from the columns that identify a row, and the loads insert or update on that key. Running a load twice, or re-loading a week that USDA revised, never creates duplicates.
A read-only login for the site. The dashboards read with a role that can only SELECT. The loads use a separate login that can write. A leaked dashboard credential cannot change the data.
Rebuild after the data loads. New data is published by a rebuild every Tuesday at noon UTC, a scheduled GitHub workflow that triggers a Netlify build hook. It rebuilds whatever is in the database, so adding a new source needs no change to it.
Things that went wrong
Drought percentages that stack. The Drought Monitor's columns are cumulative: "D1" is the share of Kansas in moderate drought or worse, and it already includes D2 and above. Stacking them as published would count the same land several times. The chart subtracts each level from the one before it to get separate bands.
A free database that was turned off. This project's database was on Supabase's free plan first. When the project was paused and could not be restored, I recreated the tables on Neon from the schema file and reloaded the data from the public reports. The longer version of that story is in the ranch demo's write-up.
Nearly out of data transfer. After working on this project for a few weeks I realized I had used 82% of Neon's 5 GB monthly network transfer. Every rebuild, and every time I refreshed the data while working, downloaded the full tables, including a raw copy of each USDA record and a long report narrative that no report uses. Those two columns were about 80% of the bytes. The queries now load only the columns the reports use, which takes a rebuild from about 167 MB to about 30 MB.
The code
The loading scripts are in the ag-data-pipeline repository and the dashboards are in ag-data-evidence, both on GitHub.