BigQuery SOX Compliance Checklist: 12 Proven Controls

A BigQuery SOX compliance checklist succeeds or fails on four IT general control (ITGC) domains: access control, change management, operations, and monitoring. Everything an external auditor will test against your BigQuery estate maps to one of those four, and every one of them can be evidenced directly from Google Cloud primitives — IAM policies, Cloud Audit Logs, INFORMATION_SCHEMA, and Cloud KMS. This checklist walks through the twelve controls we implement for financial-reporting datasets in BigQuery, with the exact SQL and configuration each control needs and the evidence query an auditor will accept.

Scope note before we start: SOX (Sarbanes–Oxley Act of 2002, Sections 302 and 404) does not certify databases — it certifies internal control over financial reporting (ICFR). BigQuery lands in scope the moment a dataset feeds a number that appears in a financial statement: revenue marts, billing pipelines, order-to-cash aggregates, close automation. Google's own infrastructure controls are covered by its SOC 2 Type II reports (issued quarterly, auditable via Compliance Reports Manager); everything above the infrastructure line — who can read the revenue table, who changed the transformation SQL, how long the access trail is retained — is yours, and that is what this checklist covers.

BigQuery SOX Compliance Checklist: The Control Architecture

The twelve controls form three layers: preventive controls on the data itself (IAM, policy tags, row-level security, CMEK, VPC Service Controls), detective controls on activity (audit log pipeline, access monitoring, job history review), and process controls around change (declarative infrastructure, SQL change management, retention and recovery, evidence generation). The diagram below shows how they fit together.

Layer 1 — Preventive Controls

1. Inventory and label in-scope datasets

You cannot control what you have not scoped. Every SOX engagement starts with a dataset inventory that separates financial-reporting datasets from everything else, because the controls below are expensive to apply estate-wide and auditors only test in-scope objects. Use dataset labels as the scoping mechanism — they are queryable, enforceable in policy, and visible in billing exports.

-- Inventory candidate in-scope datasets and their labels
SELECT
  catalog_name              AS project_id,
  schema_name               AS dataset_id,
  option_value              AS labels
FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE option_name = 'labels';

# Label a dataset as SOX in-scope (reversible, no downtime)
bq update --set_label sox_scope:in_scope \
          --set_label data_owner:finance_engineering \
          finance_prod:revenue_reporting

2. Enforce least-privilege IAM — no primitive roles

The single most common SOX finding we see in BigQuery estates is a primitive role (roles/editor, roles/owner) granted at project level, silently conferring write access to every financial table. The control: all access to in-scope datasets flows through Google Groups mapped to job functions, using predefined BigQuery roles (roles/bigquery.dataViewer, roles/bigquery.dataEditor, roles/bigquery.jobUser) or narrower custom roles — never primitive roles, never individual user grants.

# Evidence query: find primitive-role grants on the project
gcloud projects get-iam-policy finance-prod \
  --flatten="bindings[].members" \
  --filter="bindings.role:(roles/owner OR roles/editor OR roles/viewer)" \
  --format="table(bindings.role, bindings.members)"

Expected output on a passing control is an empty table (or break-glass accounts only, documented and alerted). Anything else is a segregation-of-duties exception the auditor will sample. Pair this with quarterly access reviews: export the IAM policy per dataset, have the data owner attest, retain the attestation. Segregation of duties in BigQuery terms means the humans who write transformation SQL do not hold dataEditor on production financial datasets — deployment happens through a service account owned by the CI pipeline (control 9).

3. Column-level security with policy tags

SOX scoping frequently overlaps PII and payment data. BigQuery's column-level access control attaches policy tags from a Data Catalog taxonomy to individual columns; at query time, reading a tagged column requires the Fine-Grained Reader role (datacatalog.categoryFineGrainedReader) on that tag. One policy tag per column, taxonomy and table colocated in the same region, and dynamic data masking available on top — masked readers get nulls, hashes, or defaults instead of a permission error, which keeps dashboards alive while protecting the raw value.

-- Verify which columns carry policy tags in an in-scope dataset
SELECT
  table_name,
  column_name,
  policy_tags
FROM `finance_prod.revenue_reporting`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE ARRAY_LENGTH(policy_tags.names) > 0
ORDER BY table_name, column_name;

4. Row-level security for entity and regional segregation

Where a single revenue table serves multiple legal entities, row-level access policies enforce entity segregation inside the table rather than through fragile view sprawl:

CREATE ROW ACCESS POLICY entity_emea_only
ON `finance_prod.revenue_reporting.fct_revenue`
GRANT TO ('group:finance-emea@example.com')
FILTER USING (legal_entity = 'EMEA');

Two operational caveats we state in every engagement: row access policies silently filter rows (users see a subset, not an error), so reconciliation jobs must run as an identity with full-table access; and policies do not apply to time-travel reads by users with bigquery.rowAccessPolicies.overrideTimeTravelRestrictions-adjacent bypass paths — audit who holds table-level admin rights.

5. Customer-managed encryption keys (CMEK)

BigQuery encrypts everything at rest by default, but default encryption gives you no key custody evidence. CMEK puts the key-encryption key in your Cloud KMS keyring: you control rotation, you control revocation, and disabling the key renders the dataset unreadable — a demonstrable termination control. Grant the BigQuery encryption service account (bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com) the roles/cloudkms.cryptoKeyEncrypterDecrypter role, colocate the key with the dataset region (a US multi-region dataset needs a us keyring), and set rotation ≤ 90 days for in-scope data.

# Current vs proposed: rotation period on the SOX keyring
# parameter: rotation-period | current: none (manual) | proposed: 90d | applies: next rotation
gcloud kms keys update sox-bq-key \
  --keyring=sox-keyring --location=us \
  --rotation-period=90d \
  --next-rotation-time=2026-09-01T00:00:00Z

6. Perimeter controls: VPC Service Controls and organization policies

IAM answers "who may read"; it does not answer "where may the data go." A VPC Service Controls perimeter around the financial projects blocks exfiltration paths IAM cannot see — bq mk --transfer_config into an external project, result extraction to an out-of-perimeter bucket, cross-project table copies. Complement it with organization policies: domain-restricted sharing (constraints/iam.allowedPolicyMemberDomains) so no grant can name an identity outside your Workspace domain, and disable public dataset access. These two org policies alone close the "analyst shares revenue table with personal Gmail" finding that appears in a depressing share of first-year audits.

Layer 2 — Detective Controls

7. The audit log pipeline: export, retain, lock

This is the control auditors spend the most time on, and the one with a hard deadline in it. Per Cloud Audit Logs behavior: Admin Activity, System Event, and Policy Denied logs are always on; Data Access logs are disabled by default across Google Cloud except for BigQuery, where they are enabled by default — every query, every table read, every export lands in BigQueryAuditMetadata (use it, not the legacy AuditData format — see the BigQuery audit logs reference).

The trap is retention. Per Cloud Logging quotas, the _Required bucket holds Admin Activity logs for a non-configurable 400 days, but Data Access logs land in _Default with 30-day retention. SOX audit workpaper retention is seven years. Thirty days of query history does not survive an audit cycle, let alone seven years — so route the logs into BigQuery itself via a log sink, and lock the bucket:

# 1. Sink BigQuery data-access logs into a dedicated audit dataset
gcloud logging sinks create sox-bq-audit-sink \
  bigquery.googleapis.com/projects/audit-prod/datasets/bq_audit_logs \
  --log-filter='resource.type="bigquery_dataset" OR
    protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"' \
  --use-partitioned-tables

# 2. Grant the sink writer identity dataEditor on the audit dataset
#    (printed as writerIdentity by the command above)

# 3. Alternative/parallel: raise _Default retention (1–3650 days, configurable)
#    parameter: retention-days | current: 30 | proposed: 2555 (7y) | applies: immediately
gcloud logging buckets update _Default --location=global --retention-days=2555

The audit dataset lives in a separate project with its own IAM (the people being audited must not hold write access to their own trail — that is the whole point), CMEK-encrypted, with the sink's partitioned tables giving you cheap seven-year storage on long-term pricing.

8. Access monitoring: the queries that answer “who touched revenue”

With the sink in place, the auditor's favorite question becomes a query. Who read the revenue tables, when, from where, and did any service account behave anomalously:

-- Who accessed in-scope tables in the last quarter (from the audit sink)
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS principal,
  JSON_VALUE(protopayload_auditlog.metadataJson,
             '$.tableDataRead.reason')                    AS read_reason,
  resource.labels.dataset_id                              AS dataset_id,
  COUNT(*)                                                AS access_count,
  MIN(timestamp)                                          AS first_access,
  MAX(timestamp)                                          AS last_access
FROM `audit-prod.bq_audit_logs.cloudaudit_googleapis_com_data_access`
WHERE resource.labels.dataset_id = 'revenue_reporting'
  AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY principal, read_reason, dataset_id
ORDER BY access_count DESC;

For interactive investigation inside the retention window, INFORMATION_SCHEMA.JOBS gives you 180 days of job history without any pipeline:

-- Jobs that wrote to in-scope tables outside the deployment service account
SELECT
  user_email,
  job_id,
  statement_type,
  destination_table.dataset_id,
  destination_table.table_id,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE destination_table.dataset_id = 'revenue_reporting'
  AND statement_type IN ('INSERT', 'UPDATE', 'DELETE', 'MERGE',
                         'CREATE_TABLE_AS_SELECT', 'TRUNCATE_TABLE')
  AND user_email != 'deploy-sa@finance-prod.iam.gserviceaccount.com'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
ORDER BY creation_time DESC;

A passing control returns zero rows: nothing writes to financial tables except the pipeline identity. Wire the same predicate into a log-based alert so an out-of-band write pages someone the day it happens, not the quarter it is sampled.

9. Change management: declarative infrastructure and SQL under version control

Section 404 auditors test change management harder than access control, because manual hotfixes to revenue logic are where restatements come from. The control has three parts. First, schema and infrastructure are declarative — Terraform owns datasets, IAM bindings, CMEK wiring — so every change is a reviewed pull request with an approver who is not the author:

resource "google_bigquery_dataset" "revenue_reporting" {
  dataset_id                      = "revenue_reporting"
  location                        = "US"
  default_partition_expiration_ms = null   # financial data: no silent expiry

  default_encryption_configuration {
    kms_key_name = google_kms_crypto_key.sox_bq_key.id
  }

  labels = {
    sox_scope  = "in_scope"
    data_owner = "finance_engineering"
  }
}

Second, transformation SQL (dbt, Dataform, or plain scheduled queries) lives in the same review gate — no console-edited scheduled queries on in-scope datasets. Third, the audit logs closes the loop: every google.cloud.bigquery.v2.JobService.InsertJob with a DDL statement type against an in-scope dataset should reconcile 1:1 with a merged pull request. That reconciliation, run quarterly, is the change-management evidence.

10. Retention and recoverability: time travel is not a backup

BigQuery's time travel window is 2–7 days (default 7), with a fixed 7-day fail-safe behind it. That is an operational undo, not a SOX retention control — fourteen days of recoverability does not support a seven-year evidence obligation. The control set: time travel pinned to 7 days on in-scope datasets (state it explicitly, don't inherit defaults), scheduled table snapshots at period close so every reported number has a frozen source, and no default table expiration on financial datasets (an expiry policy silently deleting revenue history is a control failure you find at the worst possible moment).

-- Snapshot the revenue fact table at quarter close (zero-copy until divergence)
CREATE SNAPSHOT TABLE `finance_prod.period_close.fct_revenue_2026_q2`
CLONE `finance_prod.revenue_reporting.fct_revenue`
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-07-01 00:00:00+00';

Standing caveat: rehearse the restore. A quarterly drill that recovers a snapshot into a scratch dataset and reconciles row counts against the close report is ten minutes of work and the difference between a backup strategy and a backup hope. Test every procedure here in a non-production project before applying it to production, and keep your DR posture current.

Layer 3 — Process and Evidence

11. Quarterly access review with attestation

Auditors sample quarters; the control must therefore fire quarterly without heroics. Export per-dataset IAM (dataset ACLs and project bindings), diff against the previous quarter, route additions to the data owner for attestation, and retain the signed attestation alongside the IAM export in the audit project. The whole loop is scriptable with bq show --format=prettyjson plus a scheduled query over the audit sink for SetIamPolicy events — the grant history is already in your logs:

-- All IAM changes on in-scope datasets this quarter
SELECT
  timestamp,
  protopayload_auditlog.authenticationInfo.principalEmail AS changed_by,
  resource.labels.dataset_id,
  protopayload_auditlog.methodName
FROM `audit-prod.bq_audit_logs.cloudaudit_googleapis_com_activity`
WHERE protopayload_auditlog.methodName LIKE '%SetIamPolicy%'
  AND timestamp >= TIMESTAMP '2026-04-01 00:00:00+00'
ORDER BY timestamp;

12. Evidence pack: make the audit boring

The final control is meta: everything above produces artifacts on a schedule, into one place. Our standard evidence pack per quarter — IAM exports and attestations, the zero-row output of the unauthorized-write query, DDL-to-PR reconciliation, KMS rotation history, restore-drill log, and Google's SOC 2 Type II report pulled from Compliance Reports Manager for the infrastructure layer. When the evidence generates itself, the audit costs days instead of weeks.

The BigQuery SOX compliance checklist at a glance

Use this table as the working artifact: walk it quarterly, and require that every row can produce its evidence on demand. In our experience the first pass of a BigQuery SOX compliance checklist fails on rows 2, 7, and 9 — primitive roles, 30-day log retention, and console-edited scheduled queries — so start there if audit season is close.

Version boundaries and honest edges

Everything above reflects Google Cloud behavior as of August 2026: BigQuery Data Access logs on by default, _Default bucket at 30 days (configurable 1–3650), _Required fixed at 400 days, time travel 2–7 days with a 7-day fail-safe, INFORMATION_SCHEMA.JOBS at 180 days. Retention defaults and log formats have changed before; re-verify against the linked documentation before you certify a control on them.

This BigQuery SOX compliance checklist covers the BigQuery-native control surface — it does not cover upstream pipeline controls (your Kafka/Datastream/Fivetran layer needs its own change management), application-level controls in your ERP, or the entity-level controls your auditors test outside IT entirely. And SOX applicability is a determination for your auditors and counsel, not your database team: we are engineers, and this is engineering guidance, not legal advice.

Where this fits in a broader governance program

SOX controls on BigQuery rarely stand alone — they usually arrive alongside a broader push to make analytics platforms auditable, the same discipline we describe in our fractional CDO real-time analytics playbook and apply to regulated retail estates in the modern retail data stack. If you are staring down your first SOX cycle on BigQuery — or your auditors just handed you a findings list — MinervaDB's fractional Chief Data Officer and database governance practice implements exactly this control set, evidence pipeline included. As always: test every control in a staging project before touching production, and keep a rehearsed DR posture behind every retention promise.