Secure data collaboration with query templates in BigQuery data clean rooms

In today's data-driven landscape, the ability to collaborate securely and efficiently is paramount. BigQuery data clean rooms provide a robust and secure environment for multiple parties to share, join, and analyze data without compromising sensitive information. Building on this foundation, today, we’re announcing BigQuery data clean room query templates in preview, bringing a new level of control, security, and ease of use to your clean room collaborations. In this post, we explore how these templates can transform your data collaboration workflows.

What are query templates? 

Query templates allow data clean room owners to create fixed, reusable queries that run against specific BigQuery tables. These templates accept input parameters and return only the resulting rows, allowing users to gain insights without accessing the raw data. Query templates allow data clean room owners to create fixed, reusable queries that run against a specific controlled environment, reducing the risk of data exfiltration.

1. Strengthened data leakage prevention: Open-ended exploration within a clean room raises data clean room owner concerns about unintended data exposure. Restricting queries through pre-defined templates significantly reduces the potential for sensitive data breaches while still allowing users to query data in a self-serve manner.  

2. Simplified user onboarding: To ease adoption for users with limited technical expertise, clean rooms utilize simplified query templates that providers can create on behalf of subscribers. This is crucial as many data providers have subscribers who lack proficiency in complex privacy-focused SQL.

3. Analytical consistency: Get consistent analytical results through controlled query execution. Without this control, enforcing data analysis rules and adhering to privacy regulations can be challenging.

4. Customizable query templates: Data owners and contributors can design and publish custom, approved queries suited to specific clean room applications. These templates, powered by BigQuery's table-valued functions (TVFs), let you input entire tables or selected fields, and receive a table as the output.

Using query templates in BigQuery data clean rooms 

You can use query templates to facilitate different forms of data collaboration within a clean room, for example:

Single-direction sharing
A data publisher creates a query template so that subscribing partners can only run queries defined by the publisher. Query template creators ultimately “self-approve” since no other contributor is added to the clean room.

Example scenario:
Steve, a data clean room owner, creates a data clean room called Campaign Analysis and adds a my_campaign dataset with a campaigns table. Steve configures metadata controls to ensure only the metadata schema is visible and subscribers cannot access the source data. Steve then creates a query template by defining a table-valued function from campaigns, restricting all subscribers of the linked dataset to only execute the TVF by parsing their own tables to gain insights on their company’s campaign.

Template syntax:

Since Steve has appropriate permissions to the campaigns table (e.g. BigQuery Data Owner), he can immediately self-approve the query template after submitting it for review.

Collaborative sharing 
A clean room owner invites a trusted contributor to propose queries to be run against each other’s data. Both parties can safely propose queries by viewing metadata schemas only, without accessing the underlying shared data. When a query definition references data that does not belong to the template proposer, the template can only be approved by that data’s owner.

Example scenario:
Sally, a clean room owner, invites Yoshi, a clean room contributor, to Campaign Analysis. Yoshi can create query templates that query their data along with the owner’s data.

TVF syntax:

In this example, since Yoshi did not add (and therefore does not own) the campaigns table, once the query template is submitted for approval, only Sally can approve it. This includes the analysis rule thresholds set by Yoshi. To use the query template, Yoshi would subscribe to the clean room and invoke the TVF. Yoshi passes her own table with a field called company_id as the table parameter, and can execute the privacy SQL defined in the query template. Note here that Yoshi does NOT NEED to add their data to the clean room.

Now let’s say Yoshi also adds to the clean room a my_transactions dataset with a transactions table and a products table. Yoshi also configures metadata controls to ensure only the metadata schema is visible and subscribers cannot access the source data. 

Sally can now also propose various query templates to join her own data to the transactions table by viewing the table’s metadata schema. A couple examples could be:

Template syntax:

Example of using join within Query Templates:

Note: Only multiple tables owned by the same party can be referenced within the TVF query syntax. See query template limitations for more details.

In this example, since Sally did not add (and therefore does not own) the transactions table, once the query template is submitted for approval, only Yoshi can approve. This includes the analysis rule thresholds set by Sally. To use the query template, Sally would subscribe to the clean room and invoke the TVF. Sally passes her own table with a field called user_ID as the table parameter, and can execute the privacy SQL defined in the query template. Note here that Sally does NOT NEED to add her data to the clean room.

Since query templates are built using table-valued functions, publishers can be rest assured that query definitions (logic) are not visible to subscribers. Subscribers just see what type of parameters are accepted as input (table name or field), and can only execute TVFs defined in approved query templates. Additionally, data publishers have the ability to ensure the underlying data added to the clean room is not shared with subscribers.

What makes BigQuery query templates different?

BigQuery query templates are a powerful addition to a data analyst’s toolbox, providing a number of benefits: 

1. Enhanced security: Query templates allow data contributors to limit and control the queries executed in a clean room, thereby reducing the risk of accidental or intentional exposure of sensitive data and limit exposure to unnecessary shared data (e.g. you don't have to share data to the clean room, just add the schema)

2. Improved governance: By predefining queries, you can better enforce data analysis rules to help support compliance with privacy regulations.

3. Simplified onboarding: Subscribers who may not be technically proficient in SQL — especially using differential privacy and aggregation threshold sql syntax — can easily use pre-built query templates to gain insights from the data.

4. Consistent analytical outcomes: With query templates, subscribers use predefined queries, which helps to deliver consistent analytical outcomes.

5. Streamlined workflows: Query templates save time and effort by standardizing queries for common insights, eliminating the need to explain custom queries to external collaborators.

6. Faster reporting: With pre-written queries, subscribers can quickly generate reports from the clean room, streamlining their workflow.

7. Flexible collaboration: Query templates can support single-direction sharing and multi-party collaboration with approval workflow.

Ready to get started? To learn more about query templates in BigQuery data clean rooms, check out the documentation here.