Projected increases in the intensity and frequency of droughts in the 21st century are expected to cause a significant negative impact on terrestrial gross primary productivity (GPP). Yet, the relative impact of soil water supply (indicated by soil moisture) and atmospheric water demand (indicated by vapour pressure deficit, VPD) on GPP remains debated, primarily due to their strong covariations, the presence of confounding factors, and unresolved causal relationships among the interconnected hydrometeorological drivers of GPP. In this study, using a causality-guided explainable artificial intelligence framework, we show that soil moisture is the dominant regulator of water stress, surpassing the role of VPD, when and where soil water supply limits ecosystem functions. Temporally, we use in-situ flux tower data to demonstrate that soil moisture dominates the GPP response during periods of insufficient soil water supply. Spatially, we assess the global spatial patterns of satellite sun-induced chlorophyll fluorescence (a proxy for GPP) in water-limited regions and demonstrate that they are mostly dominated by soil moisture. Conversely, VPD plays a greater role in controlling the temporal and spatial variations in GPP than soil moisture when and where soil water supply is not limited. The relative role of soil moisture and VPD is modulated by plant adaptation to long-term climatological aridity and plant functional types. Our findings reconcile the ongoing debate over the impacts of soil and atmospheric dryness on ecosystem functions. They provide crucial insights into how terrestrial ecosystems respond to increasing aridity and more frequent droughts, particularly given the potential ecosystem shifts from energy to water limitation.