It depends on what type of spray foam you use. Closed cell foam is rated to be a moisture barrier. It tends to be more expensive but is worth it to get a moisture barrier and insulation in one. In some scenarios where you need much thicker insulation to hit your desired R value they might start with closed cell for the moisture barrier and then switch to open cell since it is cheaper.
The problem is air escaping from the hot side through "cracks" or holes and causing condensation as it reaches the cold side.
In nordic standards, there must be an air-column between the insulation and the outer "cold" layer, to ventilate out any moisture that might get trapped there. There must also be a moisture barrier between the "hot" side and the layer of insulation, typically a PE-sheet, to prevent air from leaking into the insulation.
Since the wood expands/contracts with changes in temperature and humidity, filling compartments in wood constructions with foam does not guarantee air tight barriers.
Closed cell foam will trap moisture underneath it. When sprayed on wood which is naturally moist that water will have nowhere to go. Any delaminations of the foam from the substrate will form pockets where wet will concentrate, and as the foam breaks down it becomes acidic.
If I were to sprayfoam something I would only consider using open cell foam. If I were to use other impervious zero-perm insulation materials like rockwool I'd only do so with dimple board to allow air underneath. The small loss of efficiency is a necessary tradeoff for giving the moisture which will always be there a path to escape.
Spray foam doesn't remove the need for a designing a proper insulation and moisture barrier system for the building. If you spray foam an interior wall with closed cell foam you most likely will add something to allow the other side of the wood to breath.
Choosing where your moisture barrier line lies is typically easy in new construction but does get tricky with retrofit situations. It sounds like the biggest issue from the article is that they are taking what were vented attics and converting them to non-vented attics with spray foam. The issue isn't really the spray foam, the issue is converting an attic without proper understanding of venting and moisture barriers.
