How to Enable Encryption in BigQuery

Why enable encryption in BigQuery?

Customer-managed encryption keys (CMEK) give you control over who can decrypt data, satisfy compliance rules, and allow independent key rotation—something the default Google-managed encryption cannot do.

How to specify a customer-managed key when creating a table?

Attach the OPTIONS(encryption_key="projects/.../cryptoKeys/...") clause to the CREATE TABLE statement. BigQuery will write all blocks using the supplied Cloud KMS key.

SQL syntax?

The table-level syntax works for permanent tables, partitioned tables, and materialized views. You can mix other OPTIONS in the same clause.

How to encrypt an existing table?

Run ALTER TABLE ... SET OPTIONS(encryption_key="projects/.../cryptoKeys/..."). The command rewrites data files in place without needing to unload and reload.

How to set a dataset default encryption key?

Use the bq CLI or REST API to call datasets.patch with the defaultEncryptionConfiguration.kmsKeyName field. New tables inherit the dataset key automatically.

Best practices for key management?

Create one key per environment (prod, staging) in the same region as the dataset, grant the bigquery.admin role the Cloud KMS cryptoKeyEncrypterDecrypter permission, and rotate keys on a scheduled cadence.

What pitfalls should I avoid?

Avoid cross-region keys, missing IAM permissions, and forgetting to update scheduled jobs that write to new encrypted tables.

Practical example: Encrypting ecommerce orders

The query below creates an encrypted Orders table and then updates it to a new key.

-- create with CMEK
CREATE TABLE `shop.Orders` (
id INT64,
customer_id INT64,
order_date DATE,
total_amount NUMERIC
) OPTIONS (
encryption_key = 'projects/shop-sec/locations/us/cryptoKeys/prod-key'
);

-- rotate to a new key
ALTER TABLE `shop.Orders`
SET OPTIONS (
encryption_key = 'projects/shop-sec/locations/us/cryptoKeys/prod-key-v2'
);


Common mistakes and fixes

Missing KMS permission: Grant the BigQuery service account roles/cloudkms.cryptoKeyEncrypterDecrypter on the key.

Key region mismatch: Ensure key and dataset share the same Cloud region; otherwise BigQuery rejects the query.

FAQ

Does CMEK affect query performance?

No measurable impact; encryption and decryption are hardware-accelerated.

Can I remove encryption later?

Yes. Use ALTER TABLE ... SET OPTIONS (encryption_key=NULL) to revert to Google-managed keys.

How often can I rotate keys?

You can rotate anytime; existing data stays under the old key until you run ALTER TABLE ... SET OPTIONS again.