I still use it for making PDFs from Quark files. I write PostScript using an old Xitron PPD and distill it. With InDesign I do a straight export. I've also used Distiller successfully in fixing problem PDFs generated elsewhere, even from InDesign.
The original reason for the Adobe Acrobat Distiller was to provide a convenient means by which PDF files could be produced by any application that was capable of printing using standard, system PostScript drivers. The PostScript route was chosen since for early PDF, PostScript was relatively easily converted to PDF (as opposed to QuickDraw for old MacOS and GDI for Windows).
Our assumption was that over time, as PDF gained popularity and as PDF eclipsed PostScript in terms of richness of the imaging model (i.e., ICC color management, transparency, etc.), applications would generate PDF directly. That is exactly what happened relatively early on with Illustrator, Photoshop, and InDesign. Corel was actually also an early adopter of direct PDF generation. Quark was very late to the game, creating PDF via conversion of PostScript “under the covers” until relatively recent versions. And just the other day, a legacy Adobe application, FrameMaker, began to support direct PDF creation without going through the PostScript route.
That having been said, it is Adobe's intention to continue to distributing and supporting Acrobat Distiller as part of the Acrobat product, but certainly not enhancing it in any way. Why? Primarily to support PDF creation for the very many applications, especially non-graphic arts applications and especially on Windows, that provide no native PDF creation capability and may provide direct PostScript output (see Michael Jahn's LaTex comment above).
We do often see postings of hacks for “fixing” PDF files by refrying PDF by printing PDF to PostScript and recreating PDF from such PostScript via Distiller. Typically this shows ignorance in terms of simple fixups available within Acrobat Pro itself via the built-in Print Production and Preflight capabilities. And ironically, this refrying technique often adds more problems than it pretends to resolve (loss of ICC color management, flattening of transparency, loss of searchability, etc., etc.)!
For those of you who follow my contributions here and on Adobe User Forums should already know, Adobe most strongly recommends direct PDF creation from Adobe applications (and especially using PDF/X-4 settings for print) and that we know of no advantage to creation of PDF for such applications via distillation of PostScript. Other than very irrational and unsupported demands from some printers (Luddites?) for PDF generated via distillation of PostScript, there is no good reason for graphic arts users to ever get involved with Distiller.
I use Distiller on a daily basis. As others have said, it is a problem solver, more than anything. Now, work we design in house doesn't ever need this, but a lot of our work comes in already "designed." Some of our customers are using Adobe CC/etc, but there are a handful of ones that are using some permutation of MS Office or god forbid some kind of shareware program. I don't know what it is about Office or the shareware programs and how they are rendering PDFs, but those files are the ones that will cause RIP, font, and or transparency problems. Saving back as PS and making a new PDF with Distiller generally does the trick. I also find that if we have to make a type change using Enfocus Pitstop, using Distiller after this process prevents font issues...sometimes Pitstop will look okay on screen, but when printed weird things happen.
I also use Distiller to a quick way to dumb down PDF proofs for emails...as sometimes when they are using one of the aforementioned goofy layout programs, they create large files...and Distiller can shrink them down fairly non-destructively (or destructively, if I really need to compress it to get through their email server).