Transparent tiles are a WP8.1 feature. So, first of all, you'll need that update.
If you already have it, then some of those tiles (the core tiles) become transparent when you chose a background photo by going to settings -> start+theme.
As for the other apps, they'll need to be supported by the developers. Indie developers are being relatively quick to update the apps and allow the transparency effect. Others, like Facebook, Twitter, Instagram, Vine etc will take a while longer IF they ever do it at all.
For those, you can go to apps like "Skinery Themes" or "Transparency Tiles" to get transparent tiles for those apps. The only thing is, those will not be live tiles. But if you can live without Live Tiles now that you have a notification centre, then get those