The main goal of physical repair of the defect in utero is actually to reduce the incidence of hydrocephalus and hindbrain herniation, which are very common in people with Spina Bifida. The existing fetal surgery reduces the incidence of hydrocephalus from about 80% to about 40%. The improvement in leg and bowel/bladder function is actually a secondary benefit.
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.
Another sb parent here, my kid is seven now, she’s also on track intellectually. We got the decompression surgery for the Chiari II a few months after she was born, and the VP shunt even earlier than that. Aside from some stammering (which her non SB sister also has, so I suspect it’s hereditary), and weirdness with foods (OT has helped a lot) she’s totally on track intellectually.
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!