From memory, the Fremen knew that the sandworms were the source of the spice melange but until the arrival of planetogist Pardot Kynes nobody else knew and this knowledge did not become widespread until later. Yet spice had already been an essential commodity to the imperium for over 1000 years. Given the utter dependence on it by so many influential groups and persons, they must have wondered where it came from, if only to establish whether it was a renewable resource and under what conditions it could be optimally harvested. What did people think before the worm-spice relationship was understood?
In brief, nobody seems to know or, astoundingly, care all that much. Everyone seems to think that it's a unique biological reaction that takes place on Arrakis.
“You’ll learn about the funeral plains,” she’d said, “about the wilderness that is empty, the wasteland where nothing lives except the spice and the sandworms.
Even the Imperial planetologist feigns ignorance.
“It is mostly dry land biology and botany . . . some geological work–core drilling and testing. You never really exhaust the possibilities of an entire planet.”
“Do you also investigate the spice?”
Kynes turned, and Paul noted the hard line of the man’s cheek. “A curious question, my Lord.”
“Bear in mind, Kynes, that this is now my fief. My methods differ from those of the Harkonnens. I don’t care if you study the spice as long as I share what you discover.” He glanced at the planetologist. “The Harkonnens discouraged investigation of the spice, didn’t they?”
He does admit that worms defend the spice, but he avers that there's a connection between them.
“Ah-h, the worms,” the Duke said. “I must see one sometime.”
“You may see one today,” Kynes said. “Wherever there is spice, there are worms.” “Always?” Halleck asked.
“Is there relationship between worm and spice?” the Duke asked.
Kynes turned and Paul saw the pursed lips as the man spoke. “They defend spice sands. Each worm has a–territory. As to the spice . . . who knows? Worm specimens we’ve examined lead us to suspect complicated chemical interchanges within them. We find traces of hydrochloric acid in the ducts, more complicated acid forms elsewhere. I’ll give you my monograph on the subject.”
In the prequel novels we learn that the Harkonnen have had control of the spice for much of its known lifetime and that they were easily fooled by Pardot Kynes and his son.
Now, the groundcar approached a village tucked into the rocky foothills; it was called Bilar Camp on their terrain maps. Pardot continued to talk about melange and its peculiar properties. “They found spice too soon on Arrakis. It deflected scientific inquiry. It was so useful right from the outset that no one bothered to probe its mysteries.”
Liet turned to look at him. “I thought that was why you were assigned here in the first place— to understand the spice.”
“Yes . . . but we have more important work to do. I still report back to the Imperium often enough to convince them I’m working at my job . . . though not very successfully.” Talking about the first time he’d been to this region, he drove toward a cluster of dirty buildings the color of sand and dust.
Dune: House Harkonnen
At the time of Dune and the later books the knowledge of where the Spice truly originates is never particularly widespread but the choice of POV characters makes it feel that way because most of them know the truth. During the time of Dune everyone knows that the final stages of Spice formation occur deep in the sands of Arrakis but no-one really cares what those stages are or even what the precursor conditions are until Kynes starts to study the planet in greater detail and is welcomed and assisted by the Fremen in ways that outsiders never have been. They know that the spice is a renewable resource because they have been harvesting it for millennia. The interesting thing is that once knowledge of the Spice must have been more widespread.
We learn in Children of Dune that the Makers, great and small, (the Sand Trout and the Worms they grow into) were introduced to Arrakis, by humans. When Leto II and Ghanima Atreides search their genetic memories the last time before they leave each other to enact their plan for the future of humanity they discover that some of their ancestors remember a time when Arrakis was "...once a water world like Caladan. That was before the Sand Trout came." It's never made clear how the Fremen arrival on Arrakis relates to this event. It is also never clear whether the Makers are natural or engineered.