Cost effective setup for decentralized users with BigQuery as the data warehouse
I work at a national healthcare organization where health facilities submit patient data through an in-house system. We then have an ELT pipeline to take the raw data from this system to BigQuery. Data is cleaned weekly by national-level analysts either within BQ using SQL or RStudio (using BigRQuery package, depending on the preference of the analyst for each dataset). Both raw and clean datasets are stored in BigQuery.
To ensure uniform numbers between national and sub-national levels (the level between our national office and the health facility), we want to make the clean data accessible to analysts working at the sub-national office. There are 20 sub-national offices. National and sub-national analysts use the clean data to make weekly static reports, dashboards, and ad hoc reports per request.
Is it cost effective to provide BQ access to the sub-national level? Or should we put it in a separate storage, like CloudSQL? We use GCP infrastructure so we are limited to Google services.