← Back to Home

Case Study — Data Pipeline

Ranch Data
Pipeline

A lightweight, low-cost data pipeline built for a working cattle ranch. Turns a spreadsheet into a live dashboard for under $5/month.

Industry
Agriculture / Ranching
Stack
Sheets → Supabase → Metabase
Monthly Cost
~$5
Type
Personal Project
01
The Problem

A working ranch with no visibility into its own data

The client runs a cattle operation tracking hundreds of animals across births, purchases, sales, vaccinations, pregnancies, and deaths. All of it lived in notebooks and memory.

The goal was a system that gave them real visibility into their herd without adding complexity to their daily workflow. The client is comfortable with spreadsheets, not databases.

  • Data entry needed to stay in a familiar tool — Google Sheets
  • Small, infrequent updates — not a high-volume streaming problem
  • Cost had to be minimal — this is a small operation
  • No technical sophistication on the client side to maintain infrastructure

The Solution

A four-layer pipeline

📋
Entry Layer
Google Sheets
Client enters cattle events (births, vaccinations, sales) in a formatted spreadsheet with dropdowns and validation.
⚙️
Sync Layer
Apps Script
CattleSync.gs runs on a manual trigger, validates rows, batches them, and upserts to Supabase via the REST API.
🗄️
Storage Layer
Supabase
PostgreSQL database stores the event log. A SQL view surfaces the current state of each animal for dashboards.
📊
Visualization
Metabase
Self-hosted on Railway. Dashboards show herd composition, pregnancy tracking, calf crop rates, and financial summaries.

The event log model is append-only, every cattle event is a new row, never an overwrite. A DISTINCT ON PostgreSQL view reconstructs the current state of each animal from its history.

Technical Decisions

Why these tools, not those tools

Data Entry

"Why Google Sheets instead of a custom form or app?"

The client already uses spreadsheets daily. A custom UI would introduce training overhead and a support burden. Sheets with dropdowns and validation gives 90% of the data quality benefit for zero adoption cost.

Sync

"Why a manual sync trigger instead of automatic?"

Updates happen in infrequent batches — a few dozen rows after a working day. A manual button keeps the client in control and avoids the complexity and cost of a real-time sync layer that would be overkill here.

Database

"Why Supabase over a simpler option like Airtable?"

Real PostgreSQL means real SQL — window functions, views, and DISTINCT ON for the current-state query. Airtable's query layer would have hit a ceiling quickly. Supabase's free tier also has no row limits that matter here.

Visualization

"Why self-host Metabase instead of using a managed BI tool?"

Metabase on Railway costs ~$5/month and gives full dashboard functionality. Managed BI tools at this feature level start at $50–200/month. For a small operation, that's the difference between viable and not.

Live Dashboards

What the data looks like

The dashboards below are connected to real data generated from the pipeline. All data is anonymized demo data modeled after a real ranch operation.

Tech Stack

Full stack breakdown

Layer Tool Why Cost
Entry Google Sheets Familiar to client, zero training needed. Dropdowns and validation enforce data quality. Free
Sync Google Apps Script Runs inside Sheets, no external infrastructure. Batched upserts keep it fast and within execution limits. Free
Database Supabase Real PostgreSQL with a REST API. Free tier handles this volume comfortably. SQL views handle current-state logic. Free
Visualization Metabase on Railway Full-featured BI tool. Self-hosting on Railway keeps cost minimal while enabling public dashboard sharing. ~$5/mo
Hosting Netlify Static site hosting for this portfolio. Continuous deployment from GitHub, custom domain, free SSL. $11/yr