Having played with it a bunch more now, here are my thoughts.
First, desecrating an altar has a couple of effects.
- It gives you 25 piety with whatever god you are currently worshipping.
- It gives you the "Indulgence" effect, which makes you immune to the next punishment that your current god would give you for dropping below 0 piety.
- It permanently destroys the altar that you descrate (so you can't switch to that god later)
- It gives you the punishment of the god whose altar you destroyed
So here's some tips on how to use it effectively
Agnostic's Collar - As Dave pointed out, the easiest way is to have the Agnostic's Collar item. This makes you immune to the punishment of the god whose altar you destroyed. This effectively gives you a free 50 piety with the god of your choice. The collar is relatively cheap right now if you find it in a shop, and can also be stored in your locker. After you've desecrated the other two altars, you can convert the collar for a decent number of conversion points. Dave's right, 50 piety plus conversion points is VERY powerful.
Heretic - One of the preparations from the Church building now allows you to start a dungeon with one altar desecrated. This gives you the 25 piety and the Indulgence effect, without the negative effect from the altar's god. The downside is that you don't get to choose which altar is desecrated, so you'll only have 2 altars left available.
Know the Penalties - If you aren't using one of the above techniques to avoid penalties, it's important to know what the punishment effects are for each of the gods. Some of them can be marginalized with certain builds or situations, while other builds or situations can be completely devastating. The wiki has information on each penalty (but it may be out of date as they are still being balanced constantly).
Desperate Times... - I'm finding myself using desecration mostly as an emergency piety boost. It might be because I need a particular boon from my current god, or because I'm about to take an action that would drop me below 0 piety and I really want to avoid my current god's punishment. Desecration is an important tool to keep in mind, and if you know the penalties, you can often choose an altar to desecrate that won't hurt you too much, especially if you only have the boss left to fight.
Early Boost - Dave also mentioned this, but 25 piety early in the game can give you some nice options for rapid level ups. Of course this requires you locating two altars early on. Glowing Guardian can be a good candidate for an early desecration if you haven't picked up glyphs or items yet (you will lose your starting potions, so go ahead and use them). Mystera can also work if you aren't planning on using magical attacks at all.