Quick Summary
This insight explains how our team applied data warehouse security best practices while preparing a client’s analytics warehouse for a SOC 2 Type II audit they had already failed once. It covers why controls that were correctly configured still produced no usable evidence, the six practices we implemented before fieldwork and the artifact each one generated, how those controls map to the Trust Services Criteria an auditor actually tests, the audit log retention limit that nearly cost the client their entire observation window, and the sequencing we now recommend from day one of any Type II engagement.
Why Strong Warehouse Controls Still Fail a SOC 2 Audit
A US-based B2B SaaS client came to Bacancy Technology after their first SOC 2 Type II attempt did not clear. Enterprise deals sat in procurement waiting on the report, and the readiness assessment had returned six findings against their analytics warehouse.
Nothing on that list was a missing control, which is what surprised their engineering lead most. Encryption at rest was on by default. Role-based access control, which grants permissions to roles rather than to individuals, was configured. Multi-factor authentication was enabled for the analytics team.
The problem was that none of it was provable. A SOC 2 Type I report checks whether a control exists on a single date. Type II checks whether it kept operating across an observation window of six to twelve months, and the auditor tests that by pulling evidence from random dates across the period. A control you configured last Tuesday produces nothing an auditor can sample from last March.
That gap is what most teams get caught by, and the cost of getting cloud data warehouse security wrong keeps climbing. The global average cost of a data breach reached $4.99 million in 2026, a 12% year-over-year increase and a record high across the twenty-one years IBM has published the figure.
What follows is the set of data warehouse security best practices we applied, what each one produced for the auditor, and the retention limit that nearly cost the client their observation window.
Data Warehouse Security Gaps the SOC 2 Readiness Assessment Flagged
The six findings clustered into a pattern we see often. The controls were real. The operating record behind them was not.
Over-provisioned roles: Grants had been issued directly to users rather than through roles, accumulating over three years of onboarding. Eleven people held privileges that let them read columns containing personally identifiable information (PII), and only four needed to. Nobody could produce an approval record for any of them.
Unowned service accounts: Seven accounts drove ingestion, transformation, and BI refresh. They authenticated with static passwords, had no named owner, no rotation history, and no MFA path. Two had been created for pipelines that were decommissioned the previous year and were still active.
Access reviews with no artifact: The team genuinely reviewed access each quarter. They did it in a standup, verbally. There was no reviewer name, no timestamp, no record of what was decided, and no ticket linking a flagged account to its removal. From the auditor’s position, the review had not happened.
Log coverage gaps: Query history was available but had never been exported. More on why that mattered further down.
This pattern is not unusual, and it maps onto what breach data now shows. The 2026 Verizon Data Breach Investigations Report found that vulnerability exploitation has overtaken stolen credentials as the top breach entry point at 31%. That is the first time credentials have lost that position in the report’s nineteen-year history.
Third-party involvement in breaches jumped 60% year over year to 48% of all breaches. The report traces most of the headline incidents back to absent MFA, poor credential rotation, or missing least privilege. All three described the warehouse we had just been handed.
We scoped the remediation against the Trust Services Criteria the auditor would test, working alongside the client’s SOC 2 compliance services partner. That framing changed how we selected data warehouse security best practices: each one had to produce an artifact, not just a better configuration.
Need Help Building a Warehouse That Passes Its Next Audit?
Whether you’re setting up role hierarchies, masking policies, or audit log retention, our data warehouse development services team can build the controls and evidence trail your auditors will need.
Top 6 Data Warehouse Security Best Practices We Implemented Before SOC 2 Fieldwork
The client ran Snowflake. At Bacancy Technology we have applied the same data warehouse security best practices on Amazon Redshift, BigQuery, and Databricks, so the platform-specific implementation is noted alongside each one.
1. Enforce MFA on Human Users and Key-Pair Authentication on Service Accounts
Interactive MFA on a pipeline account tends to either break the pipeline or get bypassed. The two user types need different treatment.
How we did it: We split the user population before touching authentication. Human users got MFA through the identity provider. Service users got key-pair authentication, where the service proves its identity with a cryptographic key instead of a password.
In Snowflake, we set authentication policies using the user TYPE property, which distinguishes PERSON from SERVICE. That applies MFA enforcement to humans without stranding automation.
The equivalents elsewhere: workload identity federation on BigQuery instead of downloadable service account keys, OAuth service principals on Databricks rather than personal access tokens, and federated IAM authentication through the identity provider on Redshift.
Every service account then got a named owner and a rotation schedule. The two orphaned accounts were removed.
The auditor sampled this through the authentication policy configuration export, backed by a monthly report flagging any account still running without MFA or key-pair auth.
2. Rebuild the Warehouse Role Hierarchy on Least Privilege
Direct grants to users are, in our experience, the most common reason a warehouse fails an access review. They do not surface in a role listing and they survive job changes.
How we did it: We rebuilt on a two-tier pattern. Access roles hold privileges on objects and nothing else. Functional roles map to a job function and inherit from access roles. Users are granted functional roles only, never privileges directly.
The practical gain is that when someone changes teams, you swap one role instead of auditing dozens of individual grants. Standing privileged access dropped by roughly two thirds.
We covered this structure in more depth in our work on Snowflake data access control across multiple teams.
What this produced for the audit was a population list, meaning the complete record of who held which privilege, that the auditor could pull for any date and reconcile against the approved role definitions.
3. Apply Dynamic Data Masking and Row Access Policies to PII Columns
Applying column protection table by table means coverage degrades as the schema grows.
How we did it: We classified PII columns with object tags, which are labels you attach to a column so policies can find it later. We then attached a masking policy to the tag rather than to individual columns, so any new column carrying that tag inherits masking automatically.
Row access policies restricted tenant data using a mapping table keyed to the querying role. BigQuery achieves the same through policy tags and column-level security. Databricks Unity Catalog uses column masks and row filters.
Tag-based classification is also what makes the control auditable, because the list of tagged columns becomes your inventory of where sensitive data actually lives. This layer sits alongside broader data governance services work.
The evidence here was the set of masking policy definitions plus the list of objects each policy was applied to. Together they show both what the rule is and where it actually landed.
4. Restrict Inbound Access With Network Policies and Private Connectivity
An allowlist is only as good as the exceptions nobody audits.
How we did it: We set an account-level network policy allowing traffic only from the corporate VPN range and the CI runner egress IPs. We then moved BI tool traffic onto private connectivity, which keeps that traffic off the public internet entirely.
One detail catches people out: precedence runs in three levels, not two. A user-level policy overrides the account-level policy rather than stacking with it, and a policy attached to a security integration overrides both.
A single permissive policy at either level undoes the account restriction, and it will not surface unless you query for it. We audited both levels and removed all four user-level overrides we found.
For the auditor, that meant the network policy definitions plus a standing exception list showing every user-level override and why it exists.
5. Run Quarterly Access Reviews With Timestamped Attestation
This was the finding that mattered most, because the practice already existed and only the record was missing.
How we did it: We replaced the verbal standup with a generated review packet listing every user, functional role, and privilege, exported on a fixed quarterly date. Each row required a named reviewer to mark keep, modify, or revoke, with a timestamp.
Revocations opened a ticket that had to close within ten business days. That window matters more than people expect. In our experience, finding inappropriate access is expected and rarely a problem on its own. What draws a finding is the gap between spotting it and removing it.
We wired the review into the client’s existing identity and access management services workflow, so the record was produced automatically each quarter instead of depending on someone remembering.
Each cycle left a timestamped attestation, meaning a signed record of who reviewed what and what they decided, with a linked remediation ticket for anything revoked.
6. Monitor Query and Egress Activity for Anomalous Access
Knowing a role had permission is not the same as knowing it was used.
How we did it: We enabled access history logging and exported it to the client’s SIEM, the central system where security logs are collected and alerted on.
We then set alerts on three conditions: bulk export above a row threshold, first-time access to a tagged PII column by any role, and authentication from outside the network policy range.
Alerts went to the security channel. A written triage process defined who investigated, within what time, and how the outcome was recorded. The client’s DevSecOps function ran it.
The auditor tested this against the alert configuration and the triage log, which showed that alerts were investigated and closed rather than simply generated.
Mapping the Controls to Trust Services Criteria
The auditor tests controls against criteria, not against your architecture diagram. This is how the six data warehouse security best practices mapped across, and it is the document we handed over at the start of fieldwork.
| Control Implemented
| SOC 2 Criterion
| Evidence Artifact
| Sampling Cadence
|
|---|
| MFA on human users, key-pair on service accounts
| CC6.1
| Authentication policy config export
| Monthly
|
| Least-privilege role hierarchy
| CC6.3
| Grant population list
| Any sample date
|
| Masking and row access policies on PII
| CC6.1, CC6.7
| Policy definitions plus applied-object list
| Any sample date
|
| Network policies and private connectivity
| CC6.6
| Policy definitions, override exception list
| Quarterly |
| Quarterly access review
| CC6.2, CC6.3
| Signed attestation with reviewer and timestamp
| Quarterly |
| Query and egress monitoring
| CC7.2
| Alert config plus triage log
| Full window
|
Data Warehouse Audit Log Retention Limits That Break a 12-Month Observation Window
The client assumed their query logs covered the observation window because the logs were there whenever they checked. They were, and they always would be, because the retention window moves forward with you.
A rolling 365 days only survives a twelve-month audit if fieldwork begins the day the window closes, and it rarely does. The auditor samples March, and March has aged out.
Here is what the major platforms actually retain, and where each one breaks:
- Snowflake
ACCOUNT_USAGE views retain 365 days on a rolling basis. The earliest months expire before fieldwork begins, column-level read history requires Enterprise Edition, and latency of two to three hours on the views that matter here rules them out as an alerting layer.
- Snowflake
INFORMATION_SCHEMA views retain seven days to six months depending on the view, which is too short for any observation window.
- Amazon Redshift retains nothing unless you configure delivery to S3 or CloudWatch. Audit logging is off by default, so a missed setup step means no evidence at all.
- BigQuery is the strongest default of the four. Data Access audit logs are disabled by default across Google Cloud services with one exception, and BigQuery is that exception, where they cannot be disabled at all. The audit exposure sits elsewhere: caller identities, IP addresses, and some resource names are redacted from BigQuery audit logs unless certain conditions are met, and retention still depends on how your log buckets are configured.
- Databricks system tables carry finite retention that varies table to table, so coverage ends up uneven across the account.
What we put in place:
- Daily incremental export of login history, query history, access history, and grant snapshots
- A dedicated audit schema on object storage with a seven-year lifecycle policy
- A backfill of everything still inside the 365-day boundary
The build took under two weeks. Do this on day one of the observation window, because it is among the cheapest controls on this list and the only one you cannot fix retroactively.
What Made the Second SOC 2 Attempt Pass
The client cleared fieldwork on the second pass with no exceptions noted, and the report unblocked the enterprise deals that had been waiting on it.
None of what we did was unusual. The six data warehouse security best practices above are standard, and most teams have some version of them running already.
What changed the outcome was treating every control as something that had to leave a trail, then confirming the trail would still exist on the day an auditor went looking for it.
If your warehouse is heading into a Type II window, the sequence that has saved our clients the most rework is to fix log retention first, rebuild roles second, and formalise the access review third. Everything else can be remediated inside the window. Retention cannot.
Bacancy Technology provides data warehouse consulting services that map existing controls against the criteria your auditor will test and assess where the evidence gaps sit before the observation window opens, which is when they are still inexpensive to close.
Frequently Asked Questions (FAQs)
SOC 2 is principles-based and does not mandate specific technologies. Encryption at rest and in transit is the standard way organizations satisfy the confidentiality and logical access criteria, and the major cloud warehouses enable it by default. What auditors test is whether you can evidence it, which includes key management records if you use customer-managed keys.
Longer than your observation window, and longer than your platform’s default. Snowflake Account Usage views retain 365 days on a rolling basis, which does not survive a twelve-month window once fieldwork delay is factored in. Export logs to durable storage from the first day of the window and retain for at least two years so you have margin for the following cycle.
Most warehouse controls map to the CC6 series covering logical and physical access, particularly CC6.1 through CC6.3 for authentication, authorization, and least privilege, plus CC6.6 for boundary protection and CC6.7 for restricting data movement. Monitoring controls fall under CC7.2. Confidentiality criteria apply if that category is in scope for your report.
Quarterly is the cadence we see most often, but the criteria themselves are principles-based and do not mandate a review frequency. What matters is consistency, because missing a single cycle inside a Type II window becomes a control finding. You are tested against whatever cadence your policy commits to, so commit to one you can sustain.
Yes, if it has a named owner, a documented purpose, privileges scoped to that purpose, key-based authentication rather than a static password, and a rotation record. Shared accounts tend to fail audits when nobody owns them, not because they are shared. Unowned and unrotated service accounts are among the findings we run into most often in cloud warehouse environments.
