Stepwells are most effective when adjacent to both a Farm and a Holy Site. A yield of +2 Food, +1 Faith and +1 Housing is a strong yield in the Ancient Era, when they first become available. Even with just Farm adjacency, +2 Food and +1 Housing is exactly twice as good a bonus as offered by a Farm until Feudalism is researched.
The Housing bonus is particularly helpful. Most tile improvements such as Farms and Camps only provide +0.5 Housing; Stepwells providing +1 means you can grow cities to much larger sizes much sooner. Once you have Sanitation in the Industrial Era, they'll offer +2 Housing - as much as a Sewer - reducing your need for Neighborhoods.
By the time both Feudalism and Replaceable Parts are researched, the Food output of Farms will greatly surpass that of Stepwells. However, their far greater Housing bonus means that you should still feel free to build them if you have the space.
Civilopedia entry Edit
A "stepwell" is a well or pool that can be reached by a set of steps. Although found in other regions, stepwells (known by a variety of names) are prevalent in India and Southeast Asia. The first rock-cut stepwells in India date from around 200 BC, and by 600 AD they were common throughout the Gujarat region; scholars believe that "cylindrical brick-lined wells" were a product of the early Bronze Age Indus Valley civilization. Many of the ancient Indian settlements had stepwells, and the elaborate ornamentation – some as detailed as those of any Hindu temple – show they were highly thought of by the citizenry. Not surprising, given the scarcity of clean water in the area. Alas, the British overseers of the Raj found the hygiene of the wells less than desirable, and so installed pumps and pipes to replace the stepwells in their holdings. Far less picturesque, but certainly more efficient.