I have the following scenario: I would like to use column level encryption (decryption tbh) in BigQuery for (de)pseudonymizing PII data. I have to do client side encryption and then move the data to cloud. Then I would like to use Google’s capabilities to decrypt the data on-the-fly (only for those with the proper priviledges). My first plan was to use Google’s envelope encryption for this, storing the KEK in Cloud KMS and the encrypted DEK with the data. Our security team did not approved this and insisted that we store the DEK on-premise.

I came up with the following solution:

Encryption:

• Do the column level encryption on-premise with the on-prem stored encrypted DEK (but we need to access Cloud KMS for the KEK)

Decryption:

• I would write a BQ remote function that would call a cloud function that accesses the encrypted DEK from on-prem and also the KEK from KMS and decrypts the data

There are tables with 200M+ records that would be decrypted, and I’m afraid this solution is not efficient (200M+ cloud function calls and KMS crypto operations?). Could you advise some simpler or more efficient solution?

Thanks for your advice!