DataMasque Portal

Google BigQuery connections

Preview feature. BigQuery connections are available only after an admin has enabled preview features on the Settings page. While preview features are off, the BigQuery connection type is not offered, existing BigQuery connections are hidden, and runs against them are refused.

A DataMasque connection for BigQuery masks the tables of one dataset in place, authenticating with a Google Cloud service account key.

Masking process

DataMasque reads each table in the ruleset with one query ordered by the table's key, so the same ruleset and run secret mask the same rows to the same values on every run. The query writes the key columns and every column the ruleset reads, such as masked columns, hash_columns sources and the columns in an if condition, to a temporary table in the same dataset, named with the prefix dm_read_, which DataMasque reads and then drops. DataMasque masks the rows and loads the masked rows into a staging table in the same dataset, named with the prefix dm_stage_. It then applies them to the table with a single MERGE on the table's key, and drops the staging table. A temporary or staging table left behind by a run that is stopped by force or crashes expires about a day after its last activity.

Required privileges

Grant the service account the following roles:

  • roles/bigquery.jobUser on the project, to run queries and load jobs.
  • roles/bigquery.dataEditor on the dataset, to read and update the tables, to create and drop the temporary and staging tables, and to create the run history table.

These roles also cover the connection test and schema discovery.

A read-only connection runs discovery tasks only and DataMasque refuses every task that writes, so grant it roles/bigquery.dataViewer on the dataset and roles/bigquery.jobUser on the project instead.

Connection parameters

For BigQuery connections, the following fields are required:

Project ID The Google Cloud project that holds the dataset, for example my-project-123, or example.com:my-project for a domain-scoped project. Queries run, and are billed, in this project.
Dataset The BigQuery dataset to mask.
Location The dataset's location, for example US or australia-southeast2.
Service account key The contents of the service account's JSON key file, or the ARN of an AWS Secrets Manager secret that holds it. The key is stored encrypted and is never shown again. Leave the field blank when editing a connection to keep the stored key.

The following field is optional:

Maximum bytes billed A cap, in bytes, on every BigQuery job DataMasque runs. BigQuery fails a job that would bill more, without charging for it.

Before a BigQuery connection can be tested or saved, you must tick the checkbox acknowledging that original data stays recoverable through time travel.

To keep the key in AWS Secrets Manager, store it as described for Google Cloud Storage connections: the whole key file as a Plaintext secret, referenced by its ARN alone with no ["private_key"] suffix, and readable by the DataMasque instance's IAM role through secretsmanager:GetSecretValue.

Cost

With on-demand pricing, BigQuery bills each query for the data it scans. Each MERGE is billed for the columns it references plus the full size of the table it writes, however few rows change. DataMasque keeps the number of jobs low:

  • Reading a table bills one query, for the key columns and every column the ruleset reads.
  • Recording the run in the run history table bills about 10 MiB.
  • Each mask_table task writes its table with one MERGE, so two tasks on the same table bill it twice.
  • In-data discovery reads only the columns it profiles, and samples tables of 1 GiB or more rather than reading them in full.
  • Schema discovery bills about 10 MiB to list a dataset's tables and about 60 MiB, over several jobs, for each batch of 100 tables. The largest of those jobs bills about 30 MiB.

Set Maximum bytes billed to stop a run before an unexpectedly large job is billed. Set it to at least the size of the largest table you mask, or that table's MERGE fails. BigQuery bills at least 10 MiB for each table a job reads, so set it to at least 30 MiB (31457280 bytes): below that, schema discovery and every MERGE fail.

Masking rewrites each table, and BigQuery keeps the previous version in time travel and fail-safe for up to 14 days. Datasets on the physical storage billing model are billed for that storage.

Limitations

  • Tables with require_partition_filter set are not supported. Schema discovery reports them without in-data discovery.
  • Tables with more than 4,000 partitions are not supported, because BigQuery's MERGE updates at most 4,000 partitions.
  • Tables with row access policies that hide rows from the service account are not supported.
  • A table must not be written to while it is masked. If it changes during the run, it is refused and left unchanged.
  • A table with rows in its streaming buffer is refused until BigQuery has moved them out of it.
  • Large tables take time to mask: DataMasque logs a warning with the table's size and an estimate of how long it will take for tables of 10 GiB or more.
  • A mask_table task with a where is not supported, nor is a row limit, whether from the max_rows run option or the licence.
  • The mask_unique_key, truncate_table, build_temp_table and run_sql tasks are not supported.
  • Each table is masked by one worker, so the workers parameter has no effect.
  • Each connection masks the tables of one dataset.
  • Subsetting is not supported.
  • BigQuery keeps a masked table's original values recoverable for up to 14 days after masking. See Time travel.

Time travel

BigQuery keeps earlier versions of every table, so a masked table's original values remain recoverable after masking:

  • through time travel, for the dataset's time travel window of 48 to 168 hours, and
  • through fail-safe, for a further 7 days.

Original values can therefore remain recoverable for up to 14 days after masking. Time travel cannot be disabled, and dropping or replacing the table does not avoid it.

To shorten the time travel window to the minimum of 48 hours, run the following before masking, replacing my_dataset with the dataset's name:

ALTER SCHEMA my_dataset SET OPTIONS (max_time_travel_hours = 48);

When a run masks a dataset, DataMasque logs a warning naming that dataset's time travel window.

Supported data types

The following BigQuery data types can be masked: STRING, INT64, NUMERIC, BIGNUMERIC, FLOAT64, BOOL, DATE, DATETIME, TIME, TIMESTAMP and BYTES.

Columns of any other type, such as STRUCT, ARRAY, JSON and GEOGRAPHY, cannot be masked. A ruleset that masks one is refused for that table, and the table is not masked. Columns of these types that the ruleset does not mask are left as they are.

Key columns

BigQuery does not enforce keys, so DataMasque writes masked rows back on the key given in the ruleset. Set key to columns whose values are unique and never NULL; a table with a repeated or NULL key value is refused and left unchanged. A key column of type FLOAT64, STRUCT, ARRAY, JSON or GEOGRAPHY is refused. A key column can be masked, but its masked values must be unique, or the table is refused and left unchanged.

BigQuery declares primary and foreign keys as NOT ENFORCED, and DataMasque reads the ones declared in the dataset. When a unique-preserving mask covers a column that a declared foreign key references, the referencing columns are masked to the same values, as on other databases. Each referencing table must have a declared primary key, or the referenced table is refused and left unchanged.

During a run the temporary table holds the original values of the key columns and of every column the ruleset reads, such as masked columns, hash_columns sources and the columns in an if condition, and the staging table holds each masked row's original key values. Neither carries the target table's policy tags or row access policies.