What happens to URL alias when a node is unpublished? We have many pages where we set "Generate automatic URL alias" to true and false. Then we will eventually unpublish some of the pages. Looking at Webmaster tools, we have found the URLs are still indexed and cached so when any unpublished page is crawled again it is generating a 404. 

Given that in the Path settings we have set the Update action set to "Create new alias. Delete old alias", we need assistance in figuring out what happens to url aliases once the page is unpublished in the scenario where "Generate automatic URL alias" is set to true or false.
Nothing happens and nothing should.

An alias only exists on the URL level. It simply aliases "/blog/2018-01-23/my-awesome-article" to "/node/27", does a lookup for what when a request is coming in and that's it. After that, the routing and entity (access) systems are taking over to figure out what to display and who is allowed to see it.

If you unpublished node 27, then it still exists and admins can still access it, using that alias. In theory, it wouldn't even care if node 27 is deleted, it would still de-alias it to "/node/27" and then the routing system would return a 404 page as there is no node 27 anymore. This actually doesn't happen with nodes because there is code that listens on nodes being deleted and deletes aliases pointing to them as well.

It also doesn't matter if you use a manual alias or generated one using pathauto, that only differs while saving the node and telling pathauto if it should generate one dynamically or not.

But that's actually not your question, because what you're really asking is how  google updates its index when you unpublish content. And what you describe is exactly what should happen, eventually google will re-index your content, see that it no longer exists and removes it from its index. There isn't really anything else you can do directly. You could use a sitemap.xml to tell Google index your content more or less frequently but that's about it.