My understanding is that the hindbrain herniation (aka Chiari Malformation Type II) is the main cause of cognitive trouble in people with SB. But it's worth noting that it's very far from universal in causing that. Most people with SB are basically normal cognitively assuming they get good early intervention (VP shunt, PT, OT, etc.). Some early cognitive development can be slower as a knock on effect of not being able to move around as much as a baby and toddler, and thus less able to explore the environment, etc.
Source: I'm the parent of a toddler with spina bifida. She's completely on track cognitively and with fine motor skills so far. She's way behind with gross motor skills due to her inability to move her legs very much.
Our daughter was a particularly severe case too, and these interventions seem to have helped a lot. For the first four years she’d hold her breath every time she was upset, and need CPR, but we got her breathing again every time, so we don’t think there’s any brain damage. If we’d missed once, maybe I’d be telling a different story now. Thank goodness her head grew!