After having used for years TDSs, in my department we are slowly moving towards sourcing the data from BigQuery.
This has definitely incredible advantages from a speed perspective, however I was wondering how can we ensure that the data is consistent across different workbooks.
When you use TDSs, ideally you would have one TDS for each macro area (sales, purchase orders, etc.) and potentially a few workbooks tapping from the same TDS.
With this setup, the day you find out that there is some information in the sales TDS that actually needs to be excluded or included (e.g. sample sales), the TDS can be changed and all the workbook making use of that TDS would show consistent data.
Unless we are not using BQ in the intended way, I find that with BQ there are risks of inconsistencies across different workbooks pertaining to the same macro area, as each and every workbook would have its own connection to the BQ table where e.g. sales data lives and each and every workbook should then have the same statement:
where sales_type <> 'Sample'
If down the line we would need to make sure that also 'Gift' sales_type needs to be excluded, we would need to be extremely careful and go into each and every workbook to update the code.
Thus, is there a way to have one "master" BQ connection to a BQ table where we ETL the data how we need to and then have all the workbooks refer to this "master" BQ connection so to ensure that consistency is guaranteed?
Any suggestion? Or is the question not understandable?