Ok, so, does it fit..
A dwarf using a hammer to pound a pipe into a damp stone in order to create a faucet. I think it could work, but, in order to keep it balanced with other forms of getting water, I think it needs a bit of work.
First, I don't think it is over powered because of two things:
If you tap into a lake, it can/will run out of water.
If you tap into an aquifer, it requires that you first manage to successfully punch through the aquifer. And, supposing you manage that, it also requires that you clear a little bit of room around the faucet for it to be of any practical use.
So, practically speaking this would only have a major impact on the early part of the game. To be of any real use in the end game would require that the player already has some form of water transport system in place and simply wants a way to tap into it without flooding the fortress. So, I don't really see that as an unreasonable request.
The parts I think would need to be addressed specifically are:
A faucet item would have to be created.
Would the faucet be attached before or after the pipe section was installed? If it was attached prior to the pipe then how do you get rid of the trash in the pipe from the installation(all the crap that fills up the pipe as it is being driven into the stone)?
Alternatively, I could see it working really well like this, while reserving the potential for FUN:
Create the pipe section.
Create Mechanism x 2
Create Drill
Drill hole in wall(water begins to flow into the fortress, but at a vastly slower rate than is normal for a breached aquifer)
Pipe Fitted to Hole
Mechanism One ("Faucet") fitted to pipe (Water flow stops at this point, and the Dwarves can clean up the mess, but the water in the pipe is inaccessible)
Mechanism Two is used as a link between "Faucet" and pipe.
So this could in theory be accomplished using items that are already in the game. When the faucet is successfully completed, the wall section becomes what is essentially a floodgate with a greatly reduced flow, that can be used for filling buckets and what not. The tap could be auto resetting, so that it normally switches on when a Dwarf uses it, and switches off when he doesn't. Normally I wouldn't even bother with that, but it leaves some interesting possibilities open for FUN in the form of some mischievous ass running through your fort leaving all the faucets on.
The only major code items I can see with this are perhaps flags for controlling the flow and setting it up for filling buckets and what not, but I think you could get away with using the Well mechanics for that. The auto-switching might need a bit of work too.
Seeing as how this does not traverse Z levels, I do not see how this would imbalance the game in any significant way.
On other thing that would need to be addressed is whether the water that comes out is filtered or not, as is the case with the well and the screw pump. I personally think that those mechanics need a bit of tweaking, but that is up to Toady.