BigQuery data access to two different users without duplicating data

Question

We have two types of users and two projects.

users:

• Admin who should access all the data on a Table
• Users who should be able to query just some columns of a Table As you see in the following diagram, the access data should be in this way without duplicating data:

As it is not possible to give access to users based on a Column (and not even based on a Table because access control on BigQuery is just on the Dataset level), the easiest solution which comes to mind is to:

• Duplicate data in two different datasets or project, one with all data, and another one with those columns which second group can query.

How we can find a solution without duplicating data?

Answer 1

Views are actually the perfect solution to your problem. You simply restrict the columns each group sees with each view in the underlying SQL of the view.

For more details, see:

• https://cloud.google.com/bigquery/docs/share-access-views
• BigQuery Authorized View over multiple projects