I find myself wondering why the init is supposed to figure out what the computer is to be doing. That kind of "automagical" behavior is what drove me bonkers when trying to use Windows for more than a games console...
The problems arise when it doesn't automatically do what you want it to do and you don't have a good way of fixing it yourself. I'm not in a position to understand init systems so I will be in this camp if there's ever any problem with any init system. What matters to me, as the end user, is how likely it is to work automatically.
If systemd 'just works', then what's the problem? If systemd is broken... well, that's a problem, isn't it. But from what I've seen a lot (though not all, of course) of the arguments against it are highly political/philosophical and aren't based on whether it's actually working or not.
So if your init system is handling hotplug events, then that's some bad design. systemd doesn't stoop that low, though it still has plenty of mistakes - like putting the INI parser in PID1. As an example, even Apple put launchd's XML parser as a separate process!
This after having udev exist as a independent project for a decade.
These days the only way to download and install udev manually is to download the whole of systemd and then extract udev from that. Something that caused Gentoo to fork udev into eudev.
Honestly, I'll take a dozen 5-line unit files over a single "portable" init script that implements its own utility library, or yet another "simple" init script that's 90% standard boilerplate and still doesn't work because the 10% doing the real work doesn't check PID files correctly ("I can't just kill all processes called foo?") ...
Anyway, the point is that systemd models system states declaratively, solves the dependencies and executes the necessary steps required to reach that state, all without explicitly telling it what to do. If your dependencies are declared correctly, there is little danger of misbehaviour. The advantage to this is that parallelization is free and you never need explicit waits. In practice of course there will be misbehaving units and incomplete dependency declarations, but I think as admins gain experience with systemd those kinds of issues will drastically decrease. You'll still be able to run plain old scripts from unit files. I do that on my home server to start containers. since the contained process doesn't background itself and just prints to stdout and stderr, when started via systemd all its output will go into the journal and the tail shows up when I do "service container@foo status".
Analogous to SQL, I suppose something like "systemctl explain" might be useful. There's list-dependencies, which gets halfway there.
See the 'On Upstart' section here [1].
A recent failure of systemd i have seen was related to NFS. For some reason systemd would yank the network down before dismounting NFS.
If you don't unmount NFS before taking down the network, it will sit there forever waiting for that final server response.
Another example was where while mount would happily ignore a flawed fstab entry but mount the rest, systemd would bail on the whole mount task and so halt the boot process.
This isn't specific to systemd – I had to deploy workarounds to the same bug in RHEL, Debian, Ubuntu, FreeBSD, etc.
I've never messed with systemd configuration too much, but is it much more complicated than a make-style "how do I get there from here? OK lets do that"?