This is going to be a long post. :)

This is partly why a lot of ECS demos have a lot of homogeneous elements (they share all components in common). For example particle systems have long been written in a data oriented manner when running on the CPU. So if you implement it in the ECS style you can just run through the arrays in order and its all good. Or Unity's city sim example. But games tend to have much more heterogeneous entities (they share less or few components in common).

The most obvious example I can think of to dispel the myth of ECS's inherent DoDness is an ECS wherein each component storage is a linked list with each element individually allocated. Even iterating through the homogeneous entity example is likely to be extremely slow in comparison to flat arrays. So there is nothing about the pattern that demands it be implemented in a data-oriented manner.

But back to a more heterogeneous example. I'm going to try to explain it generally because I think a worked version would be enormous and maybe cloud things more? Typically component storage is indexed by the entity ID. You want to look up the component in the storage associated with a particular ID. If all your storages are flat arrays where the entity ID is just an index into the array the more heterogeneous your entities the more gaps you will have to iterate over and correspondingly more memory your game will take up. This isn't great for cache locality or memory usage and we have to iterate over every entity for all systems to find the valid ones.

So the next step uses a dense array and a secondary backing array that is indexed by the entity id. So we can keep our components packed nicely but still look them up easily. Instead of iterating over all the entities for every system we can find the shortest component storage for the set of components the system uses and iterate directly over that and lookup the other components in their storages by the current entity ID. Now we iterate over potentially many fewer entities but essentially do a random lookup into the other component storages for each one. So we're introducing cache misses for the benefit of less things to iterate over.

So what we want is the benefits of blazing through arrays without the downsides of them being pretty sparse and ideally minimizing cache misses. Which is why the concept of an Archetype was invented. If we keep our components in flat arrays but crucially change our storage so we're not keeping flat arrays of every component but keeping separate component storages for each archetype of entity we have right now.

Going from:

AAAAAAAAAA
BBBBBBBBBB
CCCCCCCCCC

To:

(ABC)
A
B
C

(AB)
AAA
BBB

(AC)
AAAAA
CCCCC

(C)
CCCCC

If we have a system that just iterates C's it can find all the archetype storages and iterate straight through the C array for them one by one. So ideally we only pay a cache miss when we change archetype, have good cache locality and are iterating the minimum set. Similarly a system that uses components A and C will only iterate the archetype storage of ABC and AC and blaze straight through the A and C arrays of each. Same deal.

This comes at a cost of making adding and removing components from an entity more expensive.

We're also ignoring interacting with other components or the world and how that might work. For example we might want to do damage to another entity entirely. Or we might want to look up the properties of the piece of ground we're stood on. So there is a whole other layer of places we can ruin all this good work by wanting to access stuff pretty randomly. Relationships in games tend to be spatial and stuff tends to move around so it's hard to see a general case solution to the problem.

Then there is other axis to think on like ease of creating the game, how flexible it is to change the game, iteration speed, designer friendliness and so on. Rarely IME has the gameplay code itself been the bottleneck outside of stupid mistakes.

In games this level of optimization is really great when you do have a big mostly homogenous set of things. Then it's well worth the time to structure your data for efficient memory access. City Sims, games like Factorio and so on are classic examples.