Well, if you use the local file system in Staging, you will end up having paths that will not include your CDN path when you sync them to PROD. This will require those links to be updated again. Having your STAGING server configured may not be an option, but unless you do it, your PROD server will always treat the content as generic text and sync whatever it is, the relative path in that case (not the CDN path).
As for the paths, using the direct link (not the /getmedia/.. path) will be better for SEO, but you will encounter an issue when it's synced because those files will not be on the local disk in your PROD environment. The "Permanent URL" (/getmedia/...) is great for portability (because the same path will work no matter where it is moved to assuming the file is synced to the server), however, it doesn't offer great SEO for the URL.
Because Content Staging is involved, there isn't a great solution that's not going to involve some sort of code / data update. I see you have a couple of options:
Update your STAGING server to store fiels in Azure Storage. This shoudl be a different storage account (or maybe just a container) from your PROD server. Configure your site for "Permanent URLs". Reference your media libraries using the /getmedia/ path. After the file / content is sync to the PROD server, you will want to go and update all of the paths to the media to reference the PROD account / container. You might be able to do this with a few SQL scripts if isolated the fields you needed to fix.
Leave your STAGING server using the local file system and direct paths. Once you sync the files / content, go through the same process above of updating the values to the CDN path. This may be a lot tougher because you will have to isolate the paths to identify them, rather just looking for the STAGING Storage name or something.
I was going to say you could not use staging on your media library files, however, this will defeat the purpose of syncing the content so it solves nothing.
The use of the CDN and Staging definitely complicates things and I'm still trying to come up with a good solution, other than what I listed.