BigQuery’s column-level encryption is more about who can decrypt than how it’s encrypted.
Let’s say you have a table my_dataset.customer_data with a column credit_card_number that you need to protect. You’ve ingested it, and now you want to encrypt it.
-- Original table schema
-- CREATE TABLE my_dataset.customer_data (
-- customer_id INT64,
-- name STRING,
-- credit_card_number STRING
-- );
-- Add a new column for encrypted data
ALTER TABLE my_dataset.customer_data
ADD COLUMN credit_card_number_encrypted BYTES;
-- Encrypt the data and populate the new column
UPDATE my_dataset.customer_data
SET credit_card_number_encrypted = TO_BYTES(AES_ENCRYPT(credit_card_number, 'your-secret-key-here'))
WHERE TRUE;
-- Optionally, drop the original plaintext column
-- ALTER TABLE my_dataset.customer_data DROP COLUMN credit_card_number;
Now, credit_card_number_encrypted holds the ciphertext. To query it, you need to decrypt:
SELECT
customer_id,
name,
-- Decrypt the data for display or further processing
AES_DECRYPT(FROM_BYTES(credit_card_number_encrypted), 'your-secret-key-here') AS decrypted_credit_card_number
FROM
my_dataset.customer_data
WHERE
customer_id = 12345;
This looks like standard encryption, right? The magic is that BigQuery doesn’t store your secret key. You provide it at query time. This means the encryption/decryption happens in the BigQuery engine when it processes your query, using the key you supplied. BigQuery itself never sees or stores the key.
This distinction is critical for compliance and security. If BigQuery managed the keys, it would need robust key management capabilities and would inherently have access to your plaintext data during query execution. By making you supply the key per query, it shifts the responsibility and control entirely to you.
The AES_ENCRYPT and AES_DECRYPT functions in BigQuery use the Advanced Encryption Standard (AES) algorithm, a widely adopted symmetric encryption standard. You can choose between AES-128, AES-192, and AES-256. The key length you use (128, 192, or 256 bits) determines the strength of the encryption. For example, AES_ENCRYPT(credit_card_number, 'your-secret-key-16bytes') uses AES-128. If your key is longer, it will be truncated. If it’s shorter, it will be padded. It’s best practice to use a key of the exact required length for clarity and to avoid unexpected behavior.
The TO_BYTES and FROM_BYTES functions are used because AES_ENCRYPT returns BYTES and AES_DECRYPT expects BYTES. The result of AES_ENCRYPT is raw binary data, which is then stored in a BYTES column. When you decrypt, you feed that raw BYTES back into AES_DECRYPT.
When you run a query that involves AES_DECRYPT, the BigQuery execution engine will fetch the encrypted BYTES from storage. Then, using the secret key you provided in the query string, it performs the decryption operation. The decrypted plaintext is then returned as part of the query result. This means the plaintext data only exists in memory during the query execution and is never persisted in BigQuery’s storage without explicit re-encryption.
The real power and the most surprising aspect is how this ties into IAM. You can grant a user or service account the bigquery.dataViewer role on a dataset, but they still won’t be able to decrypt the credit_card_number_encrypted column if they don’t have the secret key. Conversely, someone with no BigQuery permissions can still decrypt the data if they have access to the secret key and can execute a query against the table (e.g., via a shared view that exposes the encrypted column). The security boundary is the key itself, not just IAM roles.
This approach is often referred to as "client-side encryption" conceptually, even though the encryption/decryption happens within the BigQuery service. The "client" in this case is the query itself, which supplies the key.
The next step you’ll likely encounter is how to manage these secret keys securely, especially when you have multiple users or applications needing to decrypt the data.