A lot of organisations turn to BigQuery as a datawarehouse solution. But as the amount of tables increases and the number of users grows, it is easy to lose track of what data is accessed by whom. Authorized Views can help organize and control access to your sensitive data. They don’t require you to change your logical dataset design and still enable your applications and analysts to use the full power of BigQuery.
Let’s assume you have dataset that contains tables with customer transactions. Included in the transaction record are several nested products, including product details, prices and discounts. It also includes a column with customer information. This is a table design that makes perfect sense, as BigQuery performs best when your data is denormalized.
Access in BigQuery is usually defined on a dataset level. You grant a user or role access to an entire dataset and all tables in that dataset. However, you don’t want to expose the personal information of the customer to a pipeline that creates association rules between products, or to a business analyst who creates revenue reports with Google Data Studio.
With an authorized view, you can create a SQL view on the source data that excludes the private data, and authorize that view. If you put this view in a separate dataset (or even project), you can then set permissions for your user to access the view, without exposing the private data. If you change the view, you need to authorize it again. As a view is just a SQL query, you have the option to restrict columns or aggregate data to protect sensitive information.