Farm Data Platform Engineering: APIs, Storage & Orchestration

Every pipeline on this site assumes its inputs are already on disk: an orthomosaic that has been downloaded, a yield file that has been exported from a monitor, a boundary shapefile someone dropped into a folder. That assumption holds for a pilot on three fields and collapses somewhere around the fiftieth. At that point the hard part is no longer the raster algebra — it is knowing which flights exist, which have already been processed, where their pixels live, which field they belong to, and whether the copy you are about to compute an index from is the same copy the agronomist looked at last week. This section covers the platform layer that answers those questions: the APIs that supply machine and satellite data, the storage schemas that hold it, and the scheduled Python jobs that keep it current without ever ingesting the same scene twice.

It is written for the engineer who owns the pipeline in production — the one who gets paged when a prescription is late because a token expired at 2 a.m., or when a season of yield data lands twice and every zone map shifts. The techniques here are ordinary data engineering, but the constraints are not: field work is seasonal and bursty, satellite revisit is irregular and cloudy, machine data arrives days after the operation that produced it, and the identity of the thing all of it hangs off — the field — is unstable in ways no other domain tolerates.

The diagram below shows the four layers this section builds, and which topic covers each.

1. Data and Input Layer Overview

The platform layer handles four families of input, and each has a different failure mode.

Machine and management-system data

Telemetry and agronomic records reach you through a management system’s web API — John Deere Operations Center, Climate FieldView, Trimble Ag, CNH — as JSON over HTTPS with OAuth 2 authorisation. The payloads are records, not files: field definitions with boundary geometry, operations (planting, application, harvest) with start and end timestamps, and measurement series sampled along the machine’s path. Three structural traits matter. Boundaries arrive as GeoJSON in EPSG:4326 with no metre-based CRS anywhere in the payload, so every area calculation you do on them needs a projection step of the kind described in understanding CRS in precision agriculture. Collections are paginated and the page cursor is only valid for minutes. And the data is late: an operation finished on Tuesday may not be synchronised from the display until Friday, so a pipeline that pulls “yesterday’s operations” every morning will silently miss most of the season. Machine data APIs for management-system integration covers the authorisation flow, the pagination contract, and the “pull by modification time, not event time” pattern that fixes the lateness problem.

Satellite scenes

Public optical archives — Sentinel-2, Landsat 8/9 — are catalogued as SpatioTemporal Asset Catalog (STAC) items: a JSON document per scene describing its footprint, acquisition datetime, cloud cover estimate, and one HTTP URL per band asset. The assets themselves are cloud-optimized GeoTIFFs, which means you can read a single field’s window out of a 110 km × 110 km tile without downloading the tile. The structural trait that dominates design here is sparsity in time: a five-day nominal revisit becomes an effective revisit of two to three usable scenes per month once cloud is filtered, and the gaps are not random — they cluster in exactly the humid weeks when disease pressure makes imagery most valuable. Satellite imagery APIs and STAC catalogues covers search, asset selection and the windowed read pattern.

Drone and orthomosaic outputs

Flight products arrive as files, usually from a photogrammetry step outside your pipeline: an orthomosaic per flight, sometimes a digital surface model, and a capture log. Compared with satellite data they are dense in space (2–8 cm ground sampling) and sparse and irregular in time. Their platform problem is identity and provenance rather than access — two flights over the same field on the same day at different altitudes are different artefacts, and the processing settings that produced the mosaic determine whether its pixels are comparable with last month’s. The pixel-level workflow lives in orthomosaic stitching workflows and batch and async orthomosaic processing; what this section adds is where those outputs live and how they are indexed once produced.

Weather and agronomic covariates

Gridded weather — hourly temperature, precipitation, reference evapotranspiration — arrives as a regular raster time series, often NetCDF or Zarr, at 1–10 km resolution. It is coarse relative to a field, dense in time, and unlike the other three families it is continuous: there is a value for every field on every day of the season, which makes it the natural spine for a feature table. Weather and agronomic data integration covers aggregation to the field polygon and the accumulation logic behind growing degree days.

2. Core Concepts and Theory

The field is the join key, and it is not stable

Everything a farm platform stores is ultimately attached to a field in a season. That makes field identity the single most load-bearing concept in the design, and it is far weaker than it looks. Growers split a 160-acre field into two 80s when a lease changes; two neighbouring fields are farmed as one when the fence comes out; a management system re-mints its identifier when a boundary is edited; the same physical ground appears under three different names in three different systems.

The workable model separates three things. A canonical field is a row you own, with a surrogate primary key, a boundary geometry, and a validity interval. An external identifier is a row in a crosswalk table pointing at the canonical field, carrying the source system, that system’s identifier, and when it was last seen. A field-season is the unit that carries agronomy — crop, planting date, target population — because those change annually while the ground does not. Matching an incoming boundary to a canonical field is a spatial operation, not a string comparison: reproject both to a metre-based CRS, compute intersection over union, and treat anything above roughly 0.9 as the same field, 0.3–0.9 as a split or merge needing review, and below that as new. PostGIS schema design for farm data works through the tables and constraints that make this hold under concurrent loads.

Idempotency is the property that makes retries safe

A pipeline that is safe to re-run is a pipeline you can debug. The property you need is that running a task twice produces the same state as running it once — for ingestion, that means an artefact is fetched, stored and registered at most once no matter how many times the task fires. This is not achieved by checking whether a file exists; object stores are eventually consistent about listings, and a half-written object looks exactly like a finished one. It is achieved with a manifest: a table whose unique key is the artefact’s natural identity (source, external identifier, and content checksum), written in the same transaction that publishes the data, and consulted before any network fetch.

The pattern generalises beyond ingestion. Index computation, zonal aggregation and prescription export all benefit from the same discipline: name the output deterministically from its inputs, write to a staging location, and make the final publication a single atomic step. Writing idempotent tasks for imagery ingestion implements it end to end.

Push the filter to the data

Farm data is large in aggregate and small per query. A season of Sentinel-2 over one 60-hectare field is a few hundred megabytes of relevant pixels inside several terabytes of tiles; a decade of yield points for one farm is a few million rows inside a table of billions. Every layer of the platform therefore earns its keep by not moving data: a STAC search filters by footprint and date before a single byte of imagery is read, a range request pulls one internal tile of a cloud-optimized GeoTIFF instead of the file, a GiST index on the boundary column turns a full table scan into a bounded lookup, and season partitioning lets the planner skip nine-tenths of the partitions outright. The formats that make this possible are compared in GeoPackage vs GeoParquet vs Shapefile for farm data, and their raster equivalents in cloud-optimized storage for field imagery.

Schedule against the agronomic calendar

A daily cron is the wrong scheduler for a system whose workload is concentrated in six weeks of the year. Ingestion cadence should follow the source (satellite revisit is every five days at the equator and more often at high latitudes; machine data should be polled by modification time several times a day during operations and weekly outside them), while derived products should be triggered by data arrival, not by the clock. The orchestration patterns — sensors, backfills, catch-up semantics and the seasonal cadence table — are in orchestrating seasonal pipelines with Airflow.

3. Python Stack and Environment

The platform layer adds a handful of libraries to the geospatial stack used elsewhere on the site. The pins below are the combination this section’s code is written against.

Two environment caveats carry over from the rest of the site and one is new. First, GDAL is still the load-bearing native dependency: install rasterio and geopandas from wheels that bundle their own GDAL, or install everything from conda-forge, but never mix the two in one environment — a rasterio wheel with a bundled GDAL 3.8 alongside a conda libgdal 3.6 produces PROJ database errors that read like CRS bugs and are not. Second, PROJ needs its data directory; in a slim container image, PROJ_DATA must point at the grid files or datum transformations silently fall back to ballpark accuracy, which is the failure described in debugging CRS and projection errors.

The new one is GDAL’s HTTP configuration. Windowed reads over object storage are only fast if GDAL is told how to behave, and the defaults are conservative:

import os

# Set before the first rasterio.open() against a remote object.
os.environ.update({
    "GDAL_DISABLE_READDIR_ON_OPEN": "EMPTY_DIR",   # do not list the whole prefix on open
    "CPL_VSIL_CURL_ALLOWED_EXTENSIONS": ".tif,.TIF,.tiff",
    "GDAL_HTTP_MULTIPLEX": "YES",
    "GDAL_HTTP_VERSION": "2",
    "VSI_CACHE": "TRUE",
    "VSI_CACHE_SIZE": str(64 * 1024 * 1024),       # 64 MB per-file block cache
    "GDAL_CACHEMAX": "512",                        # MB, block cache shared per process
})

Without GDAL_DISABLE_READDIR_ON_OPEN, opening one asset in a bucket prefix containing thousands of objects issues a listing request per open; on a busy Sentinel-2 backfill that single variable is the difference between an hour and most of a day.

4. Architectural Patterns

Ingestion, validation, publication, derivation

The pipeline shape used throughout this site — ingestion → validation → processing → export — acquires one extra distinction at the platform layer: the difference between published and derived data. Published data is the immutable record of something that happened outside your system: a scene was acquired, a combine crossed a field, a flight was flown. Derived data is anything your code computed from it: an index raster, a management zone, a prescription. The rule that keeps a platform debuggable is that published data is never edited in place and derived data is always reproducible from it. If a masking bug is found in September, you fix the code and recompute — you do not patch the outputs, because the outputs are not the record.

Concretely this means three storage areas. A staging prefix where fetched bytes land before validation, keyed by run identifier so two concurrent runs cannot collide. A published prefix keyed by the artefact’s natural identity — s2/{tile}/{date}/{band}.tif, flights/{field_id}/{flight_ts}/ortho.tif — written once and never rewritten. And a derived prefix keyed by both the inputs and the code version — ndvi/v3/{field_id}/{date}.tif — so that recomputing with a new algorithm version writes alongside the old rather than over it, and a dashboard can be pointed at the new version only after it has been checked.

Vectors in the database, pixels in the object store

Boundaries, operations, telemetry points, zone polygons and the manifest belong in PostGIS: they are queried by attribute and geometry together, they are small enough that indexes fit in memory, and they need transactions. Imagery does not belong there. A PostGIS raster column works, but every terabyte of pixels lands in your backup window, your replication stream and your restore time, all to support access patterns — read a window, read a band, read a time series — that object storage with cloud-optimized GeoTIFFs serves better and cheaper.

The join between the two is a metadata table: one row per raster artefact with its footprint geometry, acquisition timestamp, band description, code version and object key. Queries then run in two steps — find candidate artefacts in PostGIS by space and time, then read only those objects’ windows — which is exactly the “push the filter to the data” principle applied across a storage boundary.

Bulk loading telemetry without melting the database

Yield monitor and application telemetry arrive as tens of thousands of points per field-operation, and the naive GeoDataFrame.to_postgis() with default settings will insert them one round trip at a time. The production pattern is a COPY into an unlogged staging table, followed by an insert-select that applies the geometry construction, the field match and the deduplication in one statement:

import io
import geopandas as gpd
from sqlalchemy import create_engine, text

def load_telemetry(gdf: gpd.GeoDataFrame, operation_id: str, dsn: str) -> int:
    """COPY a telemetry GeoDataFrame into staging, then merge into the partitioned table."""
    assert gdf.crs is not None and gdf.crs.to_epsg() == 4326, "telemetry must be EPSG:4326 on load"
    assert {"ts", "yield_dry", "geometry"} <= set(gdf.columns), "missing required columns"

    buf = io.StringIO()
    out = gdf.assign(wkt=gdf.geometry.to_wkt())[["ts", "yield_dry", "wkt"]]
    out.to_csv(buf, index=False, header=False)
    buf.seek(0)

    engine = create_engine(dsn, future=True)
    with engine.begin() as conn:
        raw = conn.connection.driver_connection
        with raw.cursor() as cur:
            cur.execute("CREATE TEMP TABLE stage_tel (ts timestamptz, yield_dry double precision, wkt text) ON COMMIT DROP")
            with cur.copy("COPY stage_tel (ts, yield_dry, wkt) FROM STDIN WITH (FORMAT csv)") as cp:
                cp.write(buf.read())
        inserted = conn.execute(text("""
            INSERT INTO telemetry_point (operation_id, ts, yield_dry, geom)
            SELECT :op, s.ts, s.yield_dry, ST_SetSRID(ST_GeomFromText(s.wkt), 4326)
            FROM stage_tel s
            ON CONFLICT (operation_id, ts) DO NOTHING
        """), {"op": operation_id}).rowcount
    return inserted

The ON CONFLICT DO NOTHING against a unique key of operation and timestamp is what makes a re-run harmless. Loading GeoDataFrames into PostGIS with GeoAlchemy2 covers the type mapping, SRID handling and the batch-size tuning behind this.

Where the rest of the site plugs in

The platform layer is deliberately boring so that the interesting parts stay portable. A scene fetched here is the input to band math and raster algebra unchanged; a cleaned telemetry table is the input to spatial interpolation for yield data; a zone raster derived from either is what variable-rate export to ISOXML turns into a task file. None of those steps should know whether their input came from an API, a bucket or a laptop.

5. Automated QA/QC Gates

Platform bugs are quiet. A pipeline that ingests nothing because a token expired looks identical, from the outside, to a pipeline with nothing to ingest — until an agronomist asks why the map has not changed in three weeks. The gates below are the ones worth failing loudly on.

Freshness, per source and per field. Every source has an expected maximum age. Assert it on a schedule rather than trusting the absence of errors:

from datetime import datetime, timedelta, timezone
from sqlalchemy import create_engine, text

MAX_AGE = {"sentinel2": timedelta(days=12), "machine_api": timedelta(days=3),
           "weather": timedelta(days=2)}

def assert_sources_fresh(dsn: str, now: datetime | None = None) -> None:
    now = now or datetime.now(timezone.utc)
    engine = create_engine(dsn, future=True)
    with engine.connect() as conn:
        rows = conn.execute(text(
            "SELECT source, max(promoted_at) AS latest FROM ingest_manifest GROUP BY source"
        )).all()
    seen = {r.source: r.latest for r in rows}
    stale = []
    for source, budget in MAX_AGE.items():
        latest = seen.get(source)
        if latest is None or now - latest > budget:
            stale.append(f"{source}: {'never ingested' if latest is None else f'{(now - latest).days} d old'} (budget {budget.days} d)")
    assert not stale, "stale sources — " + "; ".join(stale)

Note the deliberate treatment of a missing source as stale rather than absent. The most common production incident in this layer is a source that quietly stops producing rows, and a GROUP BY over what did arrive will never notice.

Schema and CRS on every load. Nothing enters the published area without passing the same three assertions used elsewhere on the site: the geometry column is valid and in the declared SRID, the raster has the expected band count and dtype, and the footprint falls inside the operational bounding box. A scene whose footprint sits 400 km from any field is not a scene worth publishing — it is a sign the search filter was wrong.

Referential integrity of the field crosswalk. Assert that every external identifier maps to exactly one canonical field and that no canonical field has two active identifiers from the same source. Both violations are silent and both corrupt every downstream aggregate:

-- Must return zero rows.
SELECT source, external_id, count(DISTINCT field_id) AS n
FROM field_external_id
WHERE valid_to IS NULL
GROUP BY source, external_id
HAVING count(DISTINCT field_id) > 1;

Manifest against reality. Once a week, list the published prefix and compare it with the manifest. Objects present but unregistered mean a promotion crashed between the copy and the commit; manifest rows without objects mean something deleted published data. Both need a human.

A gate that has rejected nothing proves nothing. Feed each validator a deliberately broken input in the test suite — a boundary in EPSG:4326 declared as UTM, a two-band raster where five are expected, a manifest insert that repeats a checksum — and assert that it raises. A validation function that has never rejected anything is an assertion about your luck, not your data.

6. Scaling and Performance

Three numbers drive nearly every design decision in this layer, measured on a farm-scale workload of 400 fields averaging 45 hectares with a season of Sentinel-2 and a weekly weather aggregation.

Windowed reads beat downloads by an order of magnitude. Reading a single field’s window from a remote cloud-optimized GeoTIFF costs two to four HTTP range requests — one for the header, one or two for the tiles that intersect the window — and typically 60–200 ms. Downloading the full 10 m band tile that contains it is 60–120 MB and 8–20 s. Across 400 fields and 30 acquisition dates the difference is roughly 40 minutes of windowed reads against a day and a half of downloads, and the download path also needs the storage to put them.

Concurrency, not parallelism, is the bottleneck. These jobs are I/O bound. A thread pool of 16–32 workers issuing range requests saturates a gigabit link long before the CPU matters, and pushing past ~64 concurrent requests usually trips the object store’s per-prefix rate limit and produces 503s that your retry logic then amplifies. The Dask patterns in parallel tile processing with Dask apply once the pixels are local; getting them there is a job for bounded concurrency and backoff.

Partition the telemetry table or pay for it every query. A single telemetry_point table reaches a billion rows after a few seasons on a large operation. Declarative partitioning by season, with a GiST index on geometry per partition, keeps a field-season query at a few tens of milliseconds; unpartitioned, the same query is a multi-second index scan whose working set no longer fits in cache. The trade-off is that queries which cannot be pruned — anything filtering only by geometry, with no season predicate — get slower, because the planner must touch every partition. Design the query patterns first, then the partition key.

The table below summarises how the storage choices behave on the three access patterns that matter.

7. Conclusion

The platform layer is what turns a set of correct scripts into a system that is still correct in August. Four decisions carry most of the weight: model the field as an owned entity with a crosswalk rather than trusting any source’s identifier; make every ingestion task idempotent through a manifest with a real unique constraint; keep vectors in PostGIS and pixels in cloud-optimized objects with a metadata table joining them; and schedule by source cadence and data arrival rather than by a daily clock. Everything else in this section is detail hanging off those four.

Each topic below implements one of them in production-ready Python. Start with machine data APIs for management-system integration if your data arrives from a grower’s account, or with satellite imagery APIs and STAC catalogues if it arrives from an archive; both feed the same storage and orchestration patterns covered in the remaining topics.

Frequently Asked Questions

Do I need a database at all if everything is files in a bucket? For a single farm, no — a directory convention and a small metadata file per artefact will get you a long way. The threshold is concurrency: as soon as two processes can publish at the same time, or as soon as you need “which flights over field X between May and July” answered in milliseconds rather than by listing a prefix, you need an index with transactions. PostGIS is the cheapest thing that provides both, and it is the same engine your boundary queries already need.

How should I handle a management system that rate-limits me mid-backfill? Treat the rate limit as a normal control-flow outcome, not an error. Respect Retry-After when it is present, back off exponentially with jitter when it is not, cap total attempts, and make the unit of work small enough that losing one is cheap. Because ingestion is idempotent, a backfill that dies halfway can simply be re-run — the manifest turns the already-fetched portion into a sequence of no-ops.

What is the right granularity for a derived-product version? Version the algorithm, not the run. A path like ndvi/v3/{field_id}/{date}.tif means every output produced by version 3 of the index code is addressable and comparable, and a bug fix that changes values bumps to v4 and recomputes into a fresh prefix. Bumping on every run instead produces an unqueryable pile of near-identical outputs, and reusing one path forever makes it impossible to tell which pixels an old prescription was based on.

Related

• Machine Data APIs for Management-System Integration — OAuth 2 authorisation, pagination contracts, and pulling operations by modification time
• Satellite Imagery APIs & STAC Catalogues — searching archives by footprint and date, then reading only the pixels a field needs
• PostGIS Schema Design for Farm Data — canonical fields, external-identifier crosswalks, season partitioning and spatial indexes
• Orchestrating Seasonal Pipelines with Airflow — scheduling against the agronomic calendar, sensors, catch-up and backfill semantics
• Weather & Agronomic Data Integration — gridded weather aggregated to field polygons, growing degree days and rainfall accumulation
• Cloud-Optimized Storage for Field Imagery — COG layout, Zarr index stacks, object-key design and lifecycle policies