It's definitely different for junior positions, when someone hasn't had enough time to build up a breadth of experience yet and getting to a useful depth of understanding in at least one area is more important.
FWIW, I also think it's different at elite levels. If you actually are the person who had to, say, develop a data storage system for Facebook at a scale no-one had ever done before, you're probably a database specialist with skills and knowledge in that area far beyond any generalist's. But these positions are rare, as is the need to have someone with that level of specialist skills on your team.
For seniors at a high but not exceptional level -- that is, the most senior people you're likely to find in most organisations doing web development -- it's definitely normal for someone to develop both breadth and depth of skill and understanding throughout their career. No doubt many front-end developers (in the programming sense) do also have some level of ability with design work, and many back-end developers (ditto) do also have some level of ability with database design. There's certainly nothing wrong with that, and it's certainly helpful to have that level of understanding whether it's to use the skills directly or just to communicate more effectively with a colleague who has responsibility for that aspect of the work.
The only area where perhaps we would slightly disagree is in the idea that being a senior front-end or back-end developer necessarily requires advanced skills in specific related areas as well. The industry is huge and the number of related skills that are potentially useful is large, and which ones are useful for a particular person playing a particular role can vary widely.
For example, I've met someone who is very capable when it comes to writing server-side code yet who as far as I know has never touched a database in well over a decade. Their speciality is interfaces for network-connected devices. While a recent project for them might well have had a snazzy SPA on the front-end and a super-dooper GraphQL API running server-side, what is behind that API is probably nothing like a SQL or NoSQL database at all. On the other hand, they probably know a lot more about calling local C APIs from server-side languages like JS or Python than most. I would certainly call that person a senior level developer -- I doubt there are more than a few thousand people in the entire world with the combination of skills and experience they have, maybe not even that many -- but it seems they would not clear the bar for you because they lack significant database expertise?
At least in companies I've been, someone who can code but cannot design a database, that's a junior backend position, no questions asked. Likewise, someone who can code, but can't do UI/UX design work, that's a junior design position, no questions asked.
I think one issue is that if someone has depth in algorithms, computer architectures, and similar, becoming a competent DBA isn't too much work. SQL isn't rocket science, and neither is how databases are organized. If you don't understand B-trees, have background in what sorts of operations to nanoseconds, microseconds, or milliseconds, cache hierarchies, and so on, that's a pretty generally-applicable back-end data set, and you can pick up databases in a few months. That's something I'd expect a senior back-end engineer to have done at some point in their career. If not, I'd hire them in a more junior role, and then advance them quickly. Coincidentally, with that skillset, you can also design something new, like that Facebook back-end. That's also what FAANG interviews try to screen for, although they've had the narrowing effect that people try to master FAANG interview questions, without necessarily that depth.
In line with that, I also don't think positions like the Facebook one you described are rare. A lot of positions I've seen require that level of background. I also think you underestimate the number of people at that level.
"Of course there are many non-programming skills involved as well, but I don't accept your premise that a __junior__ back-end developer should necessarily know how to design a database or that a __junior__ front-end developer should necessarily know how to do all the design work for a UI."
I agree with this for a junior developer working on a simple project:
"Plenty of organisations have people dedicated to their design and branding, and that might be shared across all media, so again it's clearly not always the case that someone doing front-end programming must also be able to handle those aspects in order to be competent or useful."
But a senior developer working on a complex project should be able to work a couple of levels up and down wherever they are in the stack:
* If building a complex web app (e.g. Google Docs an online game, etc.), there isn't a clear separation here.
* On simpler projects, even just implementing corporate branding from a designer, little pieces of cross-cutting work come up.
* Even where there isn't cross-cutting work, being able to leverage synergies requires people to be familiar with both domains. If a designer comes with a design, and the engineer can modify it to be 90% as good (or sometimes 110% as good) for 50% of the effort, that's a conversation a senior front-end developer should be able to hold with the designer on relatively level ground (e.g. know the jargon, constraints, modes of thought, etc.).
There are jobs for full-stack engineers, and I agree with your current comment ("it seems entirely reasonable to me that a project might want to have the same team responsible for all programming"), but that's a few steps divorced from your original claim ("'ve seen this argument many times ... I've never really understood it. It's just programming, like any other software project. Sure, you might use different languages and each might have its own tool chain, but unless you're using radically different languages or tools in the two cases, those are probably superficial differences.") Since you said you never understood this argument, I thought I would elucidate.
I've worked with really good front-end people, and really good back-end people, and it's an entirely different skillsets. Beyond the (relatively junior) skill of being able to write code and understand a "for" loop, the more senior level stuff goes off in two deep and very different directions.