Planetary-scale crop water balance engine
Skills - BigQuery, Python, JavaScript UDFs, CMIP6, Penman-Monteith, FAO-56, pipeline architecture, cloud cost optimisation
A daily soil water balance model for the world's farmland. It tells companies how water stress will affect the crops in their supply chains, every year to 2070, under every major emissions pathway. I researched the science, designed the model and built the cloud pipeline that runs it, end to end. Then I redesigned how it moves and stores data, cutting cloud storage costs by 94% and a full global run from days to about 14 hours.
- Built solo, science to production
- ~4 months of development
- BigQuery · Python · JavaScript
- FAO-56 · SWAT · CMIP6
The science
Every field is a bucket
The model tracks how much water sits in the root zone of each crop, every day, at every location. Rain fills the bucket. The sun, wind and dry air pull water out through the soil and leaves. Anything the soil cannot hold runs off the surface or drains below the roots, at a rate limited by the tightest soil layer the roots have reached.
As the crop grows, its roots reach deeper and the bucket gets bigger. When the bucket runs low, the plant closes its pores to save water and stops growing. Counting those days, for every crop, location, year and climate future, is what turns weather into supply-chain risk.
The daily recurrence, following FAO-56 and the land phase of the SWAT hydrology model:
\[ SM_t = SM_{t-1} + P_t - \text{Runoff}_t - \text{Drainage}_t - ET_{a,t} \]
Try it
Run a season yourself
This is a deliberately tiny version of the engine: one crop, one field, one 180-day season, using the same daily equations. The real model runs this loop about 3.4 million times per climate scenario, for 56 years each.
Illustrative: synthetic weather and generic crop and soil parameters, not real data.
The atmosphere
How thirsty is the air?
The pull of the atmosphere on a field is the reference evapotranspiration, \(ET_o\): the water a standard, well-watered grass surface would lose in a day. I implemented the FAO-56 Penman-Monteith equation from first principles over seven daily CMIP6 climate variables and satellite elevation, for every location and every day to 2070: over 570 billion calculations.
Net radiation: incoming sunlight minus reflection (albedo 0.23) minus heat radiated back from the surface.
Vapour pressure deficit, the drying power of the air, from daily temperature extremes and humidity.
Wind speed at 2 m, converted from the 10 m climate model output with a logarithmic wind profile.
Psychrometric constant, which depends on air pressure and therefore on each location's altitude.
Slope of the saturation vapour pressure curve: how fast the air's capacity for water rises with temperature.
Radiation: from climate model output to energy available for evaporation
CMIP6 reports downwelling shortwave and longwave radiation in W/m², converted to MJ/m²/day (× 0.0864):
\[ R_{ns} = (1 - 0.23)\times 0.0864\, R_{sds} \qquad R_{nl} = 0.0864\,R_{lds} - \varepsilon\,\sigma\,(T_{mean}+273.15)^4 \qquad R_n = R_{ns} - R_{nl} \]Soil heat flux \(G\) is taken as zero at a daily time step, as FAO-56 recommends: daytime heating is balanced by night-time cooling.
Altitude: air pressure and the psychrometric constant
\[ P = 101.3\left[\frac{293 - 0.0065\,z}{293}\right]^{5.26} \qquad \lambda = 2.501 - 0.002361\,T_{mean} \qquad \gamma = \frac{c_p\,P}{\epsilon\,\lambda} = \frac{0.001013\,P}{0.622\,\lambda} \]Elevation \(z\) comes from SRTM satellite data at each 5-arcminute location, so mountain farms correctly lose water differently from lowland ones.
Wind: 10 m to crop height
\[ u_2 = u_{10}\,\frac{\ln\!\left(\frac{2.0 - 0.08}{0.01476}\right)}{\ln\!\left(\frac{10.0 - 0.08}{0.01476}\right)} = 0.748\,u_{10} \]Humidity: the Tetens equation, and why not Clausius-Clapeyron
\[ e^\circ(T) = 0.6108\,\exp\!\left(\frac{17.27\,T}{T + 237.3}\right) \qquad e_s = \frac{e^\circ(T_{max}) + e^\circ(T_{min})}{2} \qquad e_a = e_s\,\frac{RH}{100} \]The integrated Clausius-Clapeyron equation treats latent heat as constant. Tetens' empirically fitted constants absorb its temperature dependence, so it is more accurate across the −40 to 50 °C range of real farmland, and it is the FAO-56 standard.
The soil and the crop
From soil texture to a living root zone
Soil physics. From SoilGrids, I took water content at field capacity and wilting point, sand, clay, bulk density and gravel across six depth layers down to 2 m. Each layer's drainage rate comes from the Cosby et al. (1984) pedotransfer function, corrected for impermeable gravel:
\[ K_{sat,i} = 24 \times 25.4 \times 10^{\,-0.6 + 1.26\,\text{sand}_i - 0.64\,\text{clay}_i}\,(1 - \text{gravel}_i) \]
On any day, drainage out of the root zone is limited by the slowest layer the roots have reached.
Crop biology. Each crop moves through four growth stages. Its water use (the crop coefficient \(K_c\)) and rooting depth \(Z_r\) are interpolated day by day, from latitude-adjusted planting dates, so the bucket grows as the roots do:
\[ ET_c = K_c\,ET_o \qquad TAW_t = \sum_{\text{layers reached}} AWC \]
Below the stress threshold, water uptake falls in proportion to what remains, \(ET_a = K_s\,K_c\,ET_o\). A day counts as viable only if temperature, moisture and waterlogging are all within the crop's limits.
The data
Six global datasets, one grid
Each source came with its own resolution, units and conventions. I harmonised them onto a single 5-arcminute global grid (about 9 km), masked to the ~920,000 cells where the target crops are actually grown.
The engineering problem
A trillion-row explosion
Expanding every location into the crops grown there turns 19 billion rows of weather per scenario into 69 billion rows of daily soil moisture. My first working version stored every stage: across all 35 scenario runs, that was 2.1 trillion rows and 634 TB written to disk, costing over $14,000 a month just to keep, and then read back again to compute annual results. An earlier design, which updated tables in hundreds of sequential passes, would have scanned more than 18 PB.
How
Seven engineering decisions
Stream daily straight into annual
Soil moisture is a recurrence, so the simulation walks through time anyway. I rewrote it as a JavaScript UDF inside BigQuery that keeps running annual totals and emits one row per year. The daily tables are never written.
Keep one layer on purpose
Computing evapotranspiration inline too would have cut first-year cost from ~$10,600 to under $400. I kept those tables: they don't depend on the crop, so new crops run in under an hour without re-reading the climate archive, and modellers can audit intermediate values.
Read the weather once
Running crops one at a time re-scanned the same global weather for each crop. Joining every crop in a region to a single read of its weather removed the repetition.
Design for the memory ceiling
Nested per-location objects blew BigQuery's 512 MB worker limit over dense breadbaskets like the US Midwest. Passing one series at a time as flat, primitive arrays let millions of series run in parallel.
Batch the planet by geography
A single global query spilled 62 TB to disk and failed. Geohash batches sized to fit in memory, with tables clustered by location first, so each regional query skips over 95% of storage.
Scope it scientifically
Seven CMIP6 models chosen to span the full range of climate sensitivity (cold to warm, wet to dry) instead of all 31, and the globe masked to land where target crops actually grow.
Make long runs safe
Python orchestration with checkpoints, resume, exponential backoff and write-quota-aware concurrency; idempotent slice replacement so years can be extended or corrected without rebuilding; free dry runs that price every query first; billion-row audit queries.
Results
Before and after
| Earlier designs | Final architecture | |
|---|---|---|
| Data stored | 680 TB | 46 TB |
| Monthly storage cost | $14,236 | $854 |
| One-off compute, all 35 runs | $1,664 | $367 |
| First-year total cost | $172,502 | $10,614 |
| Worker memory | > 512 MB (crashing) | < 2 MB |
| Shuffle spill to disk | 62 TB | 0 |
| Full global run | Days (original multi-pass design) | ~14 hours on 4 workers |
Four months, one engineer
How it came together
Research and methodology
Built the scientific basis from FAO-56 crop water guidelines, the SWAT land-phase water balance and Cosby soil hydraulics; defined growth stages, stress zones and the viable-day index.
Data foundations
Ingested and harmonised crop maps, six soil layers, elevation, crop parameters and water stress onto one global grid; built latitude-adjusted growing seasons for each crop and location.
Atmospheric engine
Implemented Penman-Monteith over daily CMIP6 projections for 7 models, 4 scenarios and a historical baseline, with reference lookups so new models onboard without code changes.
Simulation engine
Wrote the daily rainfed and irrigated soil water loop, then solved memory, shuffle and quota failures to run it at planetary scale.
Optimisation and validation
Re-architected to stream daily results into annual totals, cut storage costs by 94%, and validated the 1.1 billion-row output with audit queries, provenance logs and documented column metadata.
Stack
This page describes the science and architecture at a high level. It contains no proprietary code, client data or company-specific detail.