The thing standing in the way of a scheme that allows for O(1) access is the imbalanced nature of the trie. The contract storage being separate from the main account trie makes it impossible to evenly distribute the trie data across the network without some unknown amount of complexity/coordination cost. Myself and others have spent a decent amount of time trying to figure out a scheme that works and everything I looked into was a dead end.
When the Verkle trie work lands and we have a unified and balanced trie we, we can then relatively easy map contiguous chunks of the state to the DHT keyspace, which then allows for key/value style lookups (aka O(1) access).
The concept of snapshots however doesn’t really apply directly to the state network design since it’s a distributed environment and we don’t expect nodes to house more than maybe a few 100MB of state data