so we built structure to solve this - it's a sql editor like datagrip but it allows you to build datasets that you can reuse for new analysis. so, in your case you define your relationships once with SQL, in this case 'all drugs', and then you can build a model 'all drugs dispensed' that reuses the 'all drugs' dataset... and the views / tables are linked and organized so you dont have to recreate them every time.
https://www.youtube.com/watch?v=V1tmeFgWMLs
it doesn't solve the problem of your data being scattered across multiple databases (you can send your data to a data warehouse with tools like fivetran), or an export to google drive - but it does solve the hard problem of keeping your analytics organized in a sane way so you dont have to rebuild the same sql queries over and over again, or have a bunch of various SQL definitions of the same thing.