I understand that git uses SHA-1 to come up with a hash given the contents of the file. However, I still cannot see how git 'unpacks' this 40 character hash into a full file which could be very large. It seems like magic that it can store such a small amount of data (40 characters) and then use this to provide arbitrarily large file.
Is there something I am missing here?
It doesn't. The hash is only used as a key to lookup the data. The full data is stored on disk (zlib compressed).
See e.g. files .git/objects/xx/xxxx... – the file path is the hash, the content of the files is the tag/commit/tree/blob content.
The question How is the Git hash calculated? has very detailed explanations.