Fix GCP BigQuery Dynamic Mask Errors

When working with GCP BigQuery, you may encounter a configuration error that prevents your data pipeline or messaging system from working. This guide explains the most common mistake with dynamic mask and shows the exact fix.

A Common Mistake

Storing sensitive data in plaintext without dynamic data masking, allowing all users to see full values (e.g., full credit card numbers).

The incorrect command:

SELECT cc_number FROM payments WHERE id = 12345
# Result: 4111111111111111

Error output:

Full credit card number is visible to the user. Even users with limited roles see unmasked data. If the query result is shared or logged, the complete card number is exposed. Compliance violation (PCI-DSS requires masking).

The Correct Approach

The right way to configure dynamic mask in GCP BigQuery:

CREATE OR REPLACE VIEW my_project:my_dataset.payments_masked AS
SELECT
  CASE
    WHEN SESSION_USER() IN ('auditor@example.com')
    THEN cc_number
    ELSE CONCAT(SUBSTR(cc_number, 1, 6), '******', SUBSTR(cc_number, -4))
  END as cc_number_masked
FROM payments

Successful result:

View created.
Regular user:
SELECT cc_number_masked FROM payments_masked
Result: 411111******1111
Auditor:
SELECT cc_number_masked FROM payments_masked
Result: 4111111111111111 (full number)

How to Prevent This

Use dynamic data masking (DDM) with policy tags for automated masking. DDM applies masking rules based on the user's role. Common masks: SHA256 (hash), LAST_4 (show last 4 chars), DEFAULT_MASK (XXXX). DDM is applied at query time without modifying stored data.

FAQ

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro. Secure your cloud with DodaTech.