Restrict data access using analysis rules

This document provides general information about analysis rules inGoogleSQL for BigQuery.

What is an analysis rule?

An analysis rule enforcespoliciesfor sharing data. A policy represents a condition that needs to be met before aquery can be run. With BigQuery, you can enforce an analysis rule ona view, using adata clean room or byapplying the analysis rule directly to the view. When you enforce ananalysis rule, you enforce that everyone querying that view must abide by thatanalysis rule on the view. If the analysis rule is satisfied, the query producesoutput that satisfies the analysis rule. If the query doesn't satisfy theanalysis rule, an error is produced.

Supported analysis rules

The following analysis rules are supported:

• Aggregation threshold analysis rule: Enforces theminimum number of distinct entities that must be present in a dataset.You can enforce this rule on a view, using DDL statements ordata clean rooms. This rule supports the aggregation threshold policyand the join restriction policy.

• Differential privacy analysis rule: Enforces aprivacy budget, which limits the data that is revealed to a subscriber whenthe data is protected withdifferential privacy.You can enforce this rule on a view, using DDL statements ordata clean rooms. This rule supports the differential privacy policyand the join restriction policy.

• List overlap analysis rule: Overlapping rows can onlybe queried after a join operation, which conforms to the rule. You canenforce this rule on a view, using DDL statements or data clean rooms.This rule supports the join restriction policy.

Aggregation threshold analysis rule

An aggregation threshold analysis rule enforces the minimum number ofdistinct entities that must contribute to an output row of a query, so thatthe output row is included in the query result.

When enforced, the aggregation threshold analysis rule groups data acrossdimensions, while ensuring the aggregation threshold is met. It counts thenumber of distinct privacy units (represented by the privacy unit column) foreach group, and only outputs the groups where the distinct privacy unit countsatisfies the aggregation threshold.

A view that includes this analysis rule must include theaggregation threshold policyand can optionally include thejoin restriction policy.

Define an aggregation threshold analysis rule for a view

You can define an aggregation threshold analysis rule for a view in adata clean room or with theCREATE VIEW statement:

CREATEORREPLACEVIEWVIEW_NAMEOPTIONS(privacy_policy='''{      "aggregation_threshold_policy": {        "threshold" :THRESHOLD,        "privacy_unit_column": "PRIVACY_UNIT_COLUMN"      },      "join_restriction_policy": {        "join_condition": "JOIN_CONDITION",        "join_allowed_columns":JOIN_ALLOWED_COLUMNS      }    }''')ASQUERY;

Definitions:

• aggregation_threshold_policy: The aggregation threshold policy for theaggregation threshold analysis rule.

  • VIEW_NAME: The path and name of the view.

  • THRESHOLD: The minimum number of distinct privacy unitsthat need to contribute to each row in the query results. If a potentialrow doesn't satisfy this threshold, that row is omitted from thequery results.

  • PRIVACY_UNIT_COLUMN: Represents the privacy unit column. Aprivacy unit column is a unique identifier for a privacy unit.Aprivacy unit is a value from the privacy unit columnthat represents the entity in a set of data that is being protected.

    You can use only one privacy unit column,and the data type for the privacy unit column must begroupable.

    The values in the privacy unit column cannot be directly projectedthrough a query, and you can use onlyanalysis rule-supported aggregate functionsto aggregate the data in this column.

• join_restriction_policy (optional): The optional join restriction policyfor the aggregation threshold analysis rule.

  • JOIN_CONDITION: The type of join restriction to enforceon a view. This can be one of the following values:

    • JOIN_ALL: All columns injoin_allowed_columns must beinner joined upon for this view to be queried.

    • JOIN_ANY: At least one column injoin_allowed_columns must bejoined upon for this view to be queried.

    • JOIN_BLOCKED: This view can't be joined along any column.Don't setjoin_allowed_columns in this case.

    • JOIN_NOT_REQUIRED: A join is not required to query this view. If ajoin is used, only the columns injoin_allowed_columns can beused.

  • JOIN_ALLOWED_COLUMNS: The columns that can be part of ajoin operation.

• QUERY: The query for the view.

Example:

In the following example, an aggregation threshold analysis rule is created ona view calledExamView.ExamView references a table calledExamTable:

CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}}')AS(SELECT*FROMmydataset.ExamTable);

To review theprivacy_policy syntax forCREATE VIEW, see theOPTIONS list inCREATE VIEW.

Update an aggregation threshold analysis rule for a view

You can change the aggregation threshold analysis rule for a view in adata clean room or with theALTER VIEW statement:

ALTERVIEWVIEW_NAMESETOPTIONS(privacy_policy='''{    "aggregation_threshold_policy": {      "threshold" :THRESHOLD,      "privacy_unit_column": "PRIVACY_UNIT_COLUMN"    }  }''')

For more information about the values you can set for the privacy policiesin the preceding syntax, seeDefine an aggregation threshold analysis rule for a view.

Example:

In the following example, an aggregation threshold analysis rule is updatedon a view calledExamView.

ALTERVIEWmydataset.ExamViewSETOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 50, "privacy_unit_column": "last_name"}}');

To review theprivacy_policy syntax forALTER VIEW, seetheOPTIONS list inALTER VIEW SET OPTIONS.

Query an aggregation threshold analysis rule–enforced view

You can query a view that has an aggregation threshold analysis rule withtheAGGREGATION_THRESHOLDclause. The query must include aggregation functions, and you can use onlyaggregation threshold-supported aggregate functionsin this query.

Example:

In the following example, an aggregation threshold analysis rule is queried ona view calledExamView:

SELECTWITHAGGREGATION_THRESHOLDtest_id,COUNT(DISTINCTlast_name)ASstudent_countFROMmydataset.ExamViewGROUPBYtest_id;/*---------+---------------* | test_id | student_count | +---------+---------------+ | P91     | 3             | | U25     | 4             | *---------+---------------*/

The aggregation threshold analysis rule can also optionally includethe join restriction policy. To learn how to use thejoin restriction policy with an analysis rule, seeJoin restriction policy in analysis rules.

To review additional examples for theAGGREGATION_THRESHOLD clause, seeAGGREGATION_THRESHOLD clause.

Differential privacy analysis rule

The differential privacy analysis rule enforces a privacy budget, which limitsthe data that is revealed to a subscriber when the data is protected withdifferential privacy.A privacy budget prevents any subscriber from querying shared data when the sumof all queries' epsilon or delta reaches the total epsilon or total delta value.You can use this analysis rule in a view.

A view that includes this analysis rule must include thedifferential privacy policyand can optionally include thejoin restriction policy.

Parameter-driven privacy budgeting for differential privacy is inpreview while differential privacy enforcement in BigQuery data clean rooms is nowgenerally available (GA).

This preview product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

To provide feedback or request support for features in preview, send an email tobq-dcr-feedback@google.com.

Define a differential privacy analysis rule for a view

You can define a differential privacy analysis rule for a view in adata clean room or with theCREATE VIEW statement:

CREATEORREPLACEVIEWVIEW_NAMEOPTIONS(privacy_policy='''{      "differential_privacy_policy": {        "privacy_unit_column": "PRIVACY_UNIT_COLUMN",        "max_epsilon_per_query":MAX_EPSILON_PER_QUERY,        "epsilon_budget":EPSILON_BUDGET,        "delta_per_query":DELTA_PER_QUERY,        "delta_budget":DELTA_BUDGET,        "max_groups_contributed":MAX_GROUPS_CONTRIBUTED      },      "join_restriction_policy": {        "join_condition": "JOIN_CONDITION",        "join_allowed_columns":JOIN_ALLOWED_COLUMNS      }    }''')ASQUERY;

Definitions:

• differential_privacy_policy: The differential privacy policy for thedifferential privacy analysis rule.

  • PRIVACY_UNIT_COLUMN: Thecolumnthat identifies the entity in a dataset that is protected using aprivacy analysis rule. This value is a JSON string.

  • MAX_EPSILON_PER_QUERY: Determines the amount of noise addedto the results per query and prevents the totalepsilonfrom being reached by a single query. This value is a JSON number from0.001 to1e+15.

  • EPSILON_BUDGET: Theepsilonbudget that represents the total epsilon that can be used across alldifferentially private queries on the view.This value must be larger thanMAX_EPSILON_PER_QUERY, and is aJSON number from0.001 to1e+15.

  • DELTA_PER_QUERY: The probability that any row in the resultfails to be epsilon-differentially private.This value is a JSON number from1e-15 to1.

  • DELTA_BUDGET: Thedeltabudget, which represents the total delta that can be used across alldifferentially private queries on the view. This value must be larger thanDELTA_PER_QUERY, and is a JSON number from1e-15 to1000.

  • MAX_GROUPS_CONTRIBUTED (optional): Limits thenumber of groupsto which an entity in a privacy unit column can contribute. This valuemust be a non-negative JSON integer.

• join_restriction_policy (optional): The optional join restriction policyfor the differential privacy analysis rule.

  • JOIN_CONDITION: The type of join restriction to enforceon a view. This can be one of the following values:

    • JOIN_ALL: All columns injoin_allowed_columns must beinner joined upon for this view to be queried.

    • JOIN_ANY: At least one column injoin_allowed_columns must bejoined upon for this view to be queried.

    • JOIN_BLOCKED: This view can't be joined along any column.Don't setjoin_allowed_columns in this case.

    • JOIN_NOT_REQUIRED: A join is not required to query this view. If ajoin is used, only the columns injoin_allowed_columns can beused.

  • JOIN_ALLOWED_COLUMNS: The columns that can be part of ajoin operation.

• QUERY: The query for the view.

Example:

In the following example, a differential privacy analysis rule is created ona view calledExamView.ExamView references a table calledExamTable:

CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"differential_privacy_policy": {"privacy_unit_column": "last_name", "max_epsilon_per_query": 1000.0, "epsilon_budget": 10000.1, "delta_per_query": 0.01, "delta_budget": 0.1, "max_groups_contributed": 2}}')AS(SELECT*FROMmydataset.ExamTable);-- NOTE: Delta and epsilon parameters are set very high due to the small-- dataset. In practice, these should be much smaller.

To review theprivacy_policy syntax forCREATE VIEW, see theOPTIONS list inCREATE VIEW.

Update a differential privacy analysis rule for a view

You can change the differential privacy analysis rule for a view in adata clean room or with theALTER VIEW statement:

ALTERVIEWVIEW_NAMESETOPTIONS(privacy_policy='''{    "differential_privacy_policy": {      "privacy_unit_column": "PRIVACY_UNIT_COLUMN",      "max_epsilon_per_query":MAX_EPSILON_PER_QUERY,      "epsilon_budget":EPSILON_BUDGET,      "delta_per_query":DELTA_PER_QUERY,      "delta_budget":DELTA_BUDGET,      "max_groups_contributed":MAX_GROUPS_CONTRIBUTED    }  }''')

For more information about the values you can set for the privacy policiesin the preceding syntax, seeDefine a differential privacy analysis rule for a view.

In the following example, a differential privacy analysis rule is updatedon a view calledExamView.

ALTERVIEWmydataset.ExamViewSETOPTIONS(privacy_policy='{"differential_privacy_policy": {"privacy_unit_column": "last_name", "max_epsilon_per_query": 0.01, "epsilon_budget": 1000.0, "delta_per_query": 0.05, "delta_budget": 0.1, "max_groups_contributed": 2}}');-- NOTE: Delta and epsilon parameters are set very high due to the small-- dataset. In practice, these should be much smaller.

To review theprivacy_policy syntax forALTER VIEW, seetheOPTIONS list inALTER VIEW SET OPTIONS.

Query a differential privacy analysis rule–enforced view

You can query a view that has a differential privacy analysis rule with theDIFFERENTIAL_PRIVACY clause. To review the syntax and additional examplesfor theDIFFERENTIAL_PRIVACY clause, seeDIFFERENTIAL_PRIVACY clause.

In the following example, a differential privacy analysis rule is queried ona view calledExamView. The differentially privatedata should be successfully returned fromExamView becauseepsilon,delta,andmax_groups_contributed all satisfy the conditions of the differentialanalysis rule inExamView.

-- Query an analysis–rule enforced view called ExamView.SELECTWITHDIFFERENTIAL_PRIVACYtest_id,AVG(test_score)ASaverage_test_scoreFROMmydataset.ExamViewGROUPBYtest_id;-- Results will vary./*---------+--------------------* | test_id | average_test_score | +---------+--------------------+ | P91     | 512.627693163311   | | C83     | 506.01565971561649 | | U25     | 524.81202728847893 | *---------+--------------------*/

In the following query, the query is blocked with an error becauseepsilon in theDIFFERENTIAL_PRIVACY clause is higher thanmax_epsilon_per_query inExamView:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"differential_privacy_policy": {"privacy_unit_column": "last_name", "max_epsilon_per_query": 10.01, "epsilon_budget": 1000.0, "delta_per_query": 0.01, "delta_budget": 0.1, "max_groups_contributed": 2}}')AS(SELECT*FROMmydataset.ExamTable);-- NOTE: Delta and epsilon parameters are set very high due to the small-- dataset. In practice, these should be much smaller.

After you've created your view, briefly wait, and thenrun the following query:

-- Error: Epsilon is too high: 1e+20, policy for table mydataset.-- ExamView allows max 10.01SELECTWITHDIFFERENTIAL_PRIVACYOPTIONS(epsilon=1e20)test_id,AVG(test_score)ASaverage_test_scoreFROMmydataset.ExamViewGROUPBYtest_id;

Run the following query three times. On the third time, the query is blockedbecause the total epsilon used is30, butepsilon_budget inExamView only allows25.6:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"differential_privacy_policy": {"privacy_unit_column": "last_name", "max_epsilon_per_query": 10.01, "epsilon_budget": 25.6, "delta_per_query": 0.01, "delta_budget": 0.1, "max_groups_contributed": 2}}')AS(SELECT*FROMmydataset.ExamTable);-- NOTE: Delta and epsilon parameters are set very high due to the small-- dataset. In practice, these should be much smaller.

After you've created your view, briefly wait, and thenrun the following query three times:

-- Error after three query runs: Privacy budget is not sufficient for-- table 'mydataset.ExamView' in this query.SELECTWITHDIFFERENTIAL_PRIVACYOPTIONS(epsilon=10)test_id,AVG(test_score)ASaverage_test_scoreFROMmydataset.ExamViewGROUPBYtest_id;

A view that includes this analysis rule must only include thejoin restriction policy.

Define a list overlap analysis rule for a view

You can define a list overlap analysis rule for a view in adata clean room or with theCREATE VIEW statement:

CREATEORREPLACEVIEWVIEW_NAMEOPTIONS(privacy_policy='''{      "join_restriction_policy": {        "join_condition": "JOIN_CONDITION",        "join_allowed_columns":JOIN_ALLOWED_COLUMNS      }    }''')ASQUERY;

Definitions:

• join_restriction_policy: The join restriction policy for thelist overlap analysis rule.

  • JOIN_CONDITION: The type of list overlap to enforceon a view. This can be one of the following values:

    • JOIN_ALL: All columns injoin_allowed_columns must beinner joined upon for this view to be queried.

    • JOIN_ANY: At least one column injoin_allowed_columns must bejoined upon for this view to be queried.

  • JOIN_ALLOWED_COLUMNS: The columns that can be part of ajoin operation.

• QUERY: The query for the view.

Example:

In the following example, a list overlap analysis rule is created ona view calledExamView.ExamView references a table calledExamTable:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"join_restriction_policy": {"join_condition": "JOIN_ANY", "join_allowed_columns": ["test_id", "test_score"]}}')AS(SELECT*FROMmydataset.ExamTable);

Update a list overlap analysis rule for a view

You can change the list overlap analysis rule for a view with adata clean room or with theALTER VIEW statement:

ALTERVIEWVIEW_NAMESETOPTIONS(privacy_policy='''{    "join_restriction_policy": {      "join_condition": "JOIN_CONDITION",      "join_allowed_columns":JOIN_ALLOWED_COLUMNS    }  }''')

For more information about the values you can set for the privacy policyin the preceding syntax, seeDefine a list overlap analysis rule for a view.

Example:

In the following example, a list overlap analysis rule is updatedon a view calledExamView.

ALTERVIEWmydataset.ExamViewSETOPTIONS(privacy_policy='{"join_restriction_policy": {"join_condition": "JOIN_ALL", "join_allowed_columns": ["test_id", "test_score"]}}');

To review theprivacy_policy syntax forALTER VIEW, seetheOPTIONS list inALTER VIEW SET OPTIONS.

Query a list overlap analysis rule–enforced view

You can perform a join operation on a view that has alist overlap analysis rule.To review the syntax for theJOIN operation, seeJoin operation.

Block a join operation with no overlap

You can block a join operation if it doesn't include at least oneoverlap with a required column.

Example:

In the following query, a view calledExamView isjoined with a table calledStudentTable. Becausethe view contains theJOIN_ANY list overlap analysis rule, at leastone overlapping row fromExamView andStudentTable is required. Becausethere is at least one overlap, the query runs successfully.

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"join_restriction_policy": {"join_condition": "JOIN_ANY", "join_allowed_columns": ["test_score", "last_name"]}}')AS(SELECT*FROMmydataset.ExamTable);-- Query a view called ExamView and a table called StudentTable.SELECT*FROMmydataset.ExamViewINNERJOINmydataset.StudentTableUSING(test_score);/*------------+-----------+---------+-------------* | test_score | last_name | test_id | last_name_1 | +------------+-----------+---------+-------------+ | 490        | Ivanov    | U25     | Ivanov      | | 500        | Wang      | U25     | Wang        | | 510        | Hansen    | P91     | Hansen      | | 550        | Silva     | U25     | Silva       | | 580        | Devi      | U25     | Devi        | *------------+-----------+---------+-------------*/

Block an inner join operation without entire overlap

You can block a join operation if it doesn't include an overlapwith all required columns.

Example:

In the following example, a join operation is attempted on a view calledExamView and a table calledStudentTable, but the query fails. The failure occursbecause theExamView list overlap analysis rule requires joining on allcolumns present in the join restriction policy. Because the table calledStudentTable doesn't contain thesecolumns, not all rows overlap and an error is produced.

-- Create a view that includes ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"join_restriction_policy": {"join_condition": "JOIN_ALL", "join_allowed_columns": ["test_score", "last_name"]}}')AS(SELECT*FROMmydataset.ExamTable);-- Query error: Joining must occur on all of the following columns-- [test_score, last_name] on table mydataset.ExamView.SELECT*FROMmydataset.ExamViewINNERJOINmydataset.StudentTableUSING(last_name);

Use a join restriction policy with another policy

The join restriction policy can be used with other policies in theaggregation threshold and differential privacy analysis rules. However, onceyou've used a join restriction policy with another policy, you can'tchange that other policy afterwards.

Example:

In the following example, a join restriction policy is used in anaggregation threshold analysis rule:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy":{"threshold": 3, "privacy_unit_column": "last_name"}, "join_restriction_policy": {"join_condition": "JOIN_ANY", "join_allowed_columns": ["test_id", "test_score"]}}')AS(SELECT*FROMmydataset.ExamTable);

Block a join operation with no required column

You can block a join operation if it doesn't include at least onerequired column. To do this include the following parts in your list overlapanalysis rule:

"join_restriction_policy":{"join_condition":"JOIN_ANY","join_allowed_columns":["column_name",...]}

Example:

In the following query, the query is blocked with an error becausethe query does not contain any join operations on thetest_score ortest_id column inExamView andStudentTable:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}, "join_restriction_policy": {"join_condition": "JOIN_ANY", "join_allowed_columns": ["test_score", "test_id"]}}')AS(SELECT*FROMmydataset.ExamTable);-- Query error: Joining must occur on at least one of the following columns-- [test_score, test_id] on table mydataset.ExamView.SELECT*FROMmydataset.ExamViewINNERJOINmydataset.StudentTableUSING(last_name);

To get the preceding query to run, in theUSING clause, replacelast_namewithtest_score.

Block a query with no join operation

If the query must have a join operation, you can block the query if no joinoperation is present by using one of the followinglist overlap analysis rules:

"join_restriction_policy":{"join_condition":"JOIN_NOT_REQUIRED"}

"join_restriction_policy":{"join_condition":"JOIN_NOT_REQUIRED","join_allowed_columns":[]}

Example:

In the following query, the query is blocked because there is nojoin operation withExamViewin the query:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}, "join_restriction_policy": {"join_condition": "JOIN_NOT_REQUIRED"}}')AS(SELECT*FROMmydataset.ExamTable);-- Query error: At least one allowed column must be specified with-- join_condition = 'JOIN_NOT_REQUIRED'.SELECT*FROMmydataset.ExamView;

Block a query with no join operation and no required column

If the query must have a join operation and the join operation must have atleast one required column, include the following parts in your list overlapanalysis rule:

"join_restriction_policy":{"join_condition":"JOIN_NOT_REQUIRED","join_allowed_columns":["column_name",...]}

Example:

In the following query, the query is blocked because thejoin operation does not include a column in theExamViewjoin_allowed_columns array:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}, "join_restriction_policy": {"join_condition": "JOIN_NOT_REQUIRED", "join_allowed_columns": ["test_score"]}}')AS(SELECT*FROMmydataset.ExamTable);-- Query error: Join occurring on a restricted column.SELECT*FROMmydataset.ExamViewINNERJOINmydataset.StudentTableUSING(last_name);

To get the preceding query to run, in theUSING clause, replacelast_namewithtest_score.

Block all join operations

You can block all join operations. To do this, only include the following partsin your list overlap analysis rule:

"join_restriction_policy":{"join_condition":"JOIN_BLOCKED",}

Example:

In the following query, the query is blocked because there is ajoin operation with a view calledExamView:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}, "join_restriction_policy": {"join_condition": "JOIN_BLOCKED"}}')AS(SELECT*FROMmydataset.ExamTable);-- Query error: Join occurring on a restricted column.SELECT*FROMmydataset.ExamViewINNERJOINmydataset.StudentTableUSING(last_name);

To get the preceding query to run, remove theINNER JOIN operation.

Block an inner join operation without all required columns

You can block an inner join operation if it doesn't include allrequired columns. To do this, include the following parts in your list overlapanalysis rule:

"join_restriction_policy":{"join_condition":"JOIN_ALL","join_allowed_columns":["column_name",...]}

Example:

In the following query, the query is blocked with an error becausethe query does not includetest_score in the join operation withthe view calledExamView:

-- Create a view that includes a table called ExamTable.CREATEORREPLACEVIEWmydataset.ExamViewOPTIONS(privacy_policy='{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}, "join_restriction_policy": {"join_condition": "JOIN_ALL", "join_allowed_columns": ["test_score", "last_name"]}}')AS(SELECT*FROMmydataset.ExamTable);-- Query error: Joining must occur on all of the following columns-- [test_score, last_name] on table mydataset.ExamView.SELECT*FROMmydataset.ExamViewINNERJOINmydataset.StudentTableUSING(last_name);

To get the preceding query to run, replaceUSING (last_name)withUSING (last_name, test_score).

Example tables

Several examples in this document reference two tables calledExamTable andStudentTable.ExamTable contains a list of test scores produced by studentsandStudentTable contains a list of students and their test scores.

To test the examples in this document, first add the following sample tables toyour project:

-- Create a table called ExamTable.CREATEORREPLACETABLEmydataset.ExamTableAS(SELECT"Hansen"ASlast_name,"P91"AStest_id,510AStest_scoreUNIONALLSELECT"Wang","U25",500UNIONALLSELECT"Wang","C83",520UNIONALLSELECT"Wang","U25",460UNIONALLSELECT"Hansen","C83",420UNIONALLSELECT"Hansen","C83",560UNIONALLSELECT"Devi","U25",580UNIONALLSELECT"Devi","P91",480UNIONALLSELECT"Ivanov","U25",490UNIONALLSELECT"Ivanov","P91",540UNIONALLSELECT"Silva","U25",550);-- Create a table called StudentTable.CREATEORREPLACETABLEmydataset.StudentTableAS(SELECT"Hansen"ASlast_name,510AStest_scoreUNIONALLSELECT"Wang",500UNIONALLSELECT"Devi",580UNIONALLSELECT"Ivanov",490UNIONALLSELECT"Silva",550);

Limitations

Analysis rules have the following limitations:

• If you've already added an analysis rule to a view, you can't switchbetween aggregation threshold analysis rules anddifferential privacy analysis rules.

An aggregation threshold analysis rule has the following limitations:

• You can only usesupported aggregate functionsin a query on anaggregation threshold analysis rule–enforced view.
• You can't add an aggregation threshold analysis rule to a materialized view.
• If you use an aggregation threshold analysis rule–enforced view in anaggregation threshold query, they must both havethe same value for the privacy unit column.
• If you use an aggregation threshold analysis rule–enforced view in anaggregation threshold query, the threshold in thequery must be greater than or equal to the threshold in the view.
• Time travel is disabled on anyview that has an aggregation threshold analysis rule.

A differential privacy analysis rule has the following limitations:

• Once a privacy budget is exhausted for a view, that view can't be used andyou must create a new view.

A list overlap analysis rule has the following limitations:

• If you combine an aggregation threshold analysis rule or adifferential privacy analysis rule with a list overlap analysis rule andyou don't place theprivacy_unit_column as ajoin_allowed_columnin the list overlap analysis rule, you might not be able to join anycolumns in certain situations.

Pricing

• There is no additional cost to attach an analysis rule to a view.
• StandardBigQuery pricing for analysis applies.