This does not explain RPM packages, title is clickbait. But i'll try my hand at it...
RPM files are a compressed CPIO archive with some magic flags and an embedded key-value store.
When installed or uninstalled, rpms execute various arbitrary stages to manage changes to an operating system before, during, and after install or uninstall. So they introduce not only file changes (including changes to the system's RPM database and its index/lock), but also arbitrary system state changes. Fun!
RPM uses a global package database/index/lock to track what is installed and the dependencies. This can sometimes get corrupt, and then you may have to remove the lock and rebuild it.
(edit: this partly applies to yum) Dependency resolution is crap, because dependency resolution is not dependent on a merkle tree or a transaction log. It's more of a lame recursive DAG that cares more about "what does this system currently have and what can I find in the package repo", versus "what was this thing actually built with/for and does this make sense at all to install". Packages are pretty much never built with a "base operating system" kind of dependency resolution, so it's possible to install a package from a completely different distro/version that was based on the one you are using. If you're lucky you won't be able to install the wrong package because of the recursive dependencies, but not everyone is lucky, and not all packages are built properly. It's also possible to create recursive dependencies so an installed package cannot be uninstalled.
A .spec file defines what and how to build packages. A .srpm contains the source code to the application and the .spec file (handy!). A .rpm file is just one of potentially several packages that can result from building a .spec file, and the source can also be spread along multiple files. It's common for patches to be included in the .srpm and used at build time.
A package repository typically contains both compiled binary packages organized by architecture (.rpm) and source packages (.srpm). If you add or remove a package from a repository, you need to re-generate the metadata files that the repo uses to communicate changes to tools like yum, or yum will have no idea about what you added/removed.
RPM is actually fairly portable. A single .srpm file can build packages for Solaris, Windows, HP-UX, Linux, FreeBSD, etc. It can be a very good compliment to whatever the native packaging is, as long as you keep all packaged files in a unique file tree (like /opt/my_pkgs/).