Fintel AnalyticsBigQuery Dynamic Data Masking in dbt: The Access Pattern That Breaks at Scale
Building data masking logic inside dbt models is one of the most quietly dangerous patterns in a growing warehouse. Here's why it breaks — and how to fix it.
BigQuery dynamic data masking lets you enforce column-level data hiding at the query engine layer — independently of who wrote the dbt model querying it. When teams build masking logic inside dbt SQL instead, they create a security posture that looks controlled in development and quietly falls apart in production as the model graph grows.
This post is about a specific, recurring mistake: treating dbt as the right place to handle data access control, and the predictable ways that decision compounds into a security and maintenance liability. If you are a data engineer, analytics engineer, or technical leader responsible for a shared BigQuery warehouse running on dbt, read this before your next access review.
Why Teams Build Masking Logic Inside dbt Models
The pattern usually starts innocently. A stakeholder requests that PII — email addresses, phone numbers, account numbers — be hidden from a particular analyst group. The data engineer on the team reaches for the tool they are most comfortable with: dbt. They add a CASE statement or a NULLIF expression to a staging model:
SELECT
user_id,
CASE
WHEN '{{ var("analyst_group") }}' = 'restricted'
THEN NULL
ELSE email
END AS email
FROM {{ source('raw', 'users') }}
This feels clean. It is version-controlled. It is testable. It is, on the surface, exactly what good dbt engineering looks like.
The problem is that this logic is compiled at model build time, not enforced at query time. The masking is baked into a specific materialised table or view. Any downstream model that queries the raw source directly — or any analyst with direct table access — bypasses it entirely. The dbt model does not become a security boundary. It becomes a false sense of one.
A pattern we see repeatedly when auditing data stacks at growth-stage companies: a team has built what they believe is a PII-safe analytics layer in dbt, but the raw BigQuery dataset has not had its IAM permissions tightened. The masking logic lives in the transformation, not in the engine. Anyone with roles/bigquery.dataViewer on the raw dataset reads the unmasked data directly.
📺 Watch: Use Snowflake’s Pivot Function with a Dynamic List of Columns
What BigQuery Dynamic Data Masking Actually Does (And Why It Is Different)
BigQuery's Dynamic Data Masking (DDM) operates at the query execution layer, not the transformation layer. When a masking policy is applied to a column via a policy tag, BigQuery intercepts the query at runtime and rewrites the result set based on the identity of the requesting principal — before any data leaves the engine.
BigQuery supports dynamic data masking at query time. You can define masking policies directly on columns — meaning a SELECT can show only partial names, masked IDs, or hashed emails, while the raw data stays safe in storage. Policies can adapt based on a user's access level, letting analysts see the format they need without giving them everything.
This is architecturally different from a dbt CASE statement. The enforcement happens regardless of which tool, which model, or which ad-hoc query accesses the column. You can use data masking to selectively obscure column data for user groups, while still allowing them access to the column. Crucially, unlike with column-level access control alone, you do not need to modify existing queries by excluding the columns that the user cannot access.
The four masking rule types available in BigQuery are:
- NULL — returns NULL for unauthorised users
- Default value — returns the data type default (empty string, zero)
- Hash — SHA-256 hash of the original value, useful for consistent joining without exposure
- Email mask — preserves the domain, masks the local part (e.g.
u****@domain.com)
BigQuery supports dynamic data masking at the column level, allowing you to selectively obscure column data for user groups while still allowing them access to the column. Data masking is built on top of column-level access control and uses policy tags and data policies. Unlike column-level access control alone, masked users do not get a "permission denied" — they see obscured data instead.
This last point matters operationally. Returning an error breaks downstream dashboards and analyst workflows. Returning a masked value keeps the pipeline running while preserving the security boundary.
How the dbt Anti-Pattern Compounds Over Time
The reason this pattern is dangerous — rather than just imperfect — is the way it compounds as the dbt project grows.
Stage 1: A single staging model with CASE masking. Harmless in isolation. The team knows where the logic lives.
Stage 2: Intermediate models build on top of the staging model. Now the masked value propagates downstream. But if someone adds a new staging model that reads from the same raw source without the CASE logic — perhaps copying from another model in a hurry — the masking disappears from that branch of the DAG silently.
Stage 3: Direct table access from BI tools or ad-hoc queries. Holistics, Looker, a data analyst running SQL in the BigQuery console — all of these bypass dbt entirely. If the IAM permissions on the raw table are broader than they should be (and in our experience, they usually are), the dbt masking layer is completely irrelevant.
Stage 4: A new dataset or region is added. The dbt masking logic does not automatically apply to the new source. Someone has to remember to add it. They will not always remember.
A capital-markets client we worked with had exactly this failure mode. They had built what their team described as a "PII-safe" dbt layer for their customer data warehouse. After an access audit, we identified seventeen raw tables in two BigQuery datasets that had allAuthenticatedUsers reader access at the dataset level — a legacy configuration from an early prototype environment that had never been tightened. Every masking CASE statement in every dbt model was functionally decorative.
This is not an edge case. It is the default trajectory when masking is treated as a transformation problem rather than an infrastructure problem.
If you are not sure where your own pipeline sits on this spectrum, explore how Fintel Analytics approaches data engineering and warehouse security for growth-stage businesses — we run access audits and data stack reviews as a core part of our engagement process.
How to Implement BigQuery Dynamic Data Masking Correctly Alongside dbt
The right architecture separates responsibilities clearly: dbt handles transformation logic, BigQuery handles access enforcement. Here is how to wire them together.
Step 1: Create a Policy Tag Taxonomy
In the Google Cloud Data Catalog (now part of Dataplex), create a taxonomy and the policy tags you need. Common examples:
Taxonomy: Data Sensitivity
├── PII
│ ├── email_address
│ ├── phone_number
│ └── national_id
└── Financial
├── account_number
└── transaction_amount
Policy tags are the labels you attach to columns. The masking rule is then defined as a Data Policy that references the tag.
Step 2: Apply Policy Tags in dbt Using Column Metadata
dbt does not natively apply BigQuery policy tags — but you can drive them via Terraform or gcloud, referencing the same column names documented in your dbt schema YAML. Some teams use dbt's persist_docs and a post-hook to apply tags programmatically. The key principle: the source of truth for which columns carry which policy tag should live in your dbt schema YAML, even if the application mechanism is external.
A workable pattern:
-- schema.yml
models:
- name: stg_users
columns:
- name: email
description: "User email address — PII:email_address policy tag applied at BigQuery layer"
- name: phone_number
description: "User phone — PII:phone_number policy tag applied at BigQuery layer"
This keeps your dbt project as the documentation layer while BigQuery enforces the policy.
Step 3: Create Masking Rules and Bind Them to Roles
For each policy tag, define a Data Policy in BigQuery specifying which masking rule applies and which IAM principal (user or group) receives the masked view versus the raw view:
-- Terraform example (simplified)
resource "google_bigquery_datapolicy_data_policy" "email_mask" {
data_policy_id = "email_null_mask"
policy_tag = "projects/.../taxonomies/.../policyTags/email_address"
data_policy_type = "DATA_MASKING_POLICY"
data_masking_policy {
predefined_expression = "EMAIL_MASK"
}
}
The role roles/bigquerydatapolicy.maskedReader is then granted to analyst groups. Data engineers and pipelines requiring the raw value receive roles/bigquerydatapolicy.fineGrainedReader.
Step 4: Combine With Row Access Policies for Multi-Tenant Data
For companies running multi-tenant data (regional data, per-client datasets, or role-scoped financial data), combine Dynamic Data Masking with BigQuery Row Access Policies. When a user runs a query against a table, BigQuery identifies all row access policies for which the user is a grantee, then logically ORs those filter expressions together and appends them as a WHERE clause to the query. If a user is not a grantee of any policy, they see zero rows — not an error, just an empty result set.
Combining both controls means a user in the EMEA analyst group sees only EMEA rows, and within those rows, PII columns are masked to the hash or email-mask format. Data masking is applied on top of row-level security — for example, if there is a row access policy applied on location = "US" and location is masked, then users are able to see rows where location = "US" but the location field is masked in the results.
This layered model is what a genuinely governed data warehouse looks like. It is also what passes a VC due diligence data audit — something we see growth-stage companies scramble to retrofit at Series A when they have been building with dbt CASE statements as their security layer.
For related reading on how dbt views interact with BigQuery's security model, see our post on dbt Authorized Views in BigQuery: Fix Broken Access Control, which covers the authorized view pattern that complements — but does not replace — native DDM enforcement.
What Does This Cost If You Get It Wrong?
The business case for fixing this is not abstract. The global average cost of a data breach dropped to $4.44 million in 2025, a 9% decrease from the all-time high of $4.88 million in 2024 — but that average conceals significant variance. The average cost per compromised record stands at $160 (IBM Cost of a Data Breach Report, 2025), and for a company with 500,000 customer records sitting in an under-secured raw BigQuery dataset, that exposure runs to $80 million before regulatory fines are added.
For fintech and payments companies in particular, the regulatory dimension compounds the technical risk. GDPR fines can reach 4% of annual global turnover. PCI-DSS scope creep from improperly controlled cardholder data environments creates audit exposure that goes far beyond the data engineering team.
97% of AI-related breaches lacked proper access controls — a figure from IBM's 2025 report that reflects a broader truth: access control is consistently the control that teams deprioritise, retrofit under pressure, and regret skipping.
The fix is not expensive. Implementing a BigQuery policy tag taxonomy, binding masking rules via Terraform, and documenting the column-level controls in dbt schema YAML is a one-to-two sprint effort for a team that knows what they are doing. The ongoing maintenance cost is lower than maintaining CASE statement masking logic scattered across a dbt project, because the enforcement layer is centralised and does not drift when new models are added.
For additional context on how BigQuery's column-level encryption interacts with the masking layer in high-security environments, see our post on BigQuery Column-Level Encryption in dbt: Protect Sensitive Data.
Frequently Asked Questions
Q: Can dbt apply BigQuery dynamic data masking policy tags directly?
A: Not natively as of 2026. dbt does not have a built-in mechanism to apply BigQuery policy tags to columns at materialisation time. The recommended approach is to apply policy tags via Terraform or gcloud, referencing the column names documented in your dbt schema YAML. Some teams use dbt post-hooks with ALTER TABLE statements, but this creates a maintenance coupling that is fragile across environments.
Q: What is the difference between dbt column masking with CASE statements and BigQuery dynamic data masking?
A: dbt CASE masking is applied at transformation time — it is compiled into a specific model and bypassed by any query that touches the raw source directly. BigQuery Dynamic Data Masking is enforced by the query engine at runtime, regardless of which tool or query path accesses the column. Engine-layer enforcement cannot be bypassed by changing a query; transformation-layer masking can.
Q: Do BigQuery row access policies work with dbt incremental models?
A: Yes, but with caveats. Row access policies are enforced at query time, so they apply when dbt reads from a source table. The risk is that if your incremental model reads from a table with a row access policy and your dbt service account is not granted the correct grantee role, the incremental run will return zero rows without raising an error — silently truncating your model. Always test with a restricted service account in a staging environment before applying row access policies to tables your dbt pipelines depend on.
Q: How do I audit which BigQuery columns currently have policy tags applied?
A: Query INFORMATION_SCHEMA.COLUMN_FIELD_PATHS filtered by data_type or cross-reference against the Data Catalog API. For a dbt project, compare the column list in your schema YAML against the output of the Dataplex policy tag API to find columns that are documented as sensitive but have no tag applied. This gap analysis is usually the most revealing part of an access audit.
Q: Should we use authorised views or dynamic data masking for column-level security in BigQuery?
A: These solve overlapping but distinct problems. Authorised views are useful when you need to share a specific curated dataset with an external team or a BI tool without granting underlying table access. Dynamic Data Masking is better for enforcing column-level controls across an entire table for different user groups without duplicating the table or maintaining separate views. For most dbt-managed warehouses, DDM is the lower-maintenance and more scalable option for PII and sensitive field control.
The companies that reach us having built masking logic inside their dbt models are not doing something reckless — they are doing what felt natural with the tool they had. But the gap between "this looks controlled" and "this is controlled" is exactly where data exposure risk accumulates quietly. At Fintel Analytics, we have run access architecture reviews and implemented production-grade BigQuery Dynamic Data Masking alongside dbt for fintech, payments, and e-commerce businesses at every growth stage — and we have seen firsthand what it costs to retrofit this after a due diligence process or a near-miss audit finding. If your warehouse is growing faster than your access governance is, that is a solvable problem, and the time to solve it is before someone else finds the gap.