SOC 2 for Data Engineering: Controls, Audit Logs & Evidence-Collection Pipelines

soc 2 for data engineering is the moment an abstract compliance framework turns into concrete work on your warehouse: who can read the PII column, how a terminated employee's grants get revoked, whether last Tuesday's pipeline deploy went through review, and how you prove all of it to an auditor without a week of screenshots. SOC 2 is not a product you install and not a certification you pass once — it is an independent attestation, written by a licensed CPA firm, that the controls you claim to run actually exist and actually operated over a window of time. For a data engineer, that window is measured against tables, grants, and query logs you already own.

The confusion that trips people up is treating SOC 2 as a security-team problem that lands in a slide deck. It is not. The controls that get sampled hardest — least-privilege access to the warehouse, an audit trail of every read against sensitive data, change management on the pipelines that move that data, and evidence that survives tampering — are all data-engineering surfaces. This guide walks through the four things an auditor (and an interviewer) will actually probe: the access controls you provision and deprovision, the warehouse audit logs that prove them, the change-management chain behind every pipeline deploy, and the evidence-collection pipelines that gather it all automatically. Each section pairs the concept with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the access-control practice library →, rehearse the monitoring and alerting side on the SLA-monitoring practice set →, and harden the correctness of your evidence tables on the data-quality practice set →.

---

On this page

• Why SOC 2 lands on the data engineer's desk in 2026
• Access controls data engineers own
• Warehouse audit logs & access history
• Change management for pipelines
• Evidence-collection pipelines
• Cheat sheet — SOC 2 evidence recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why SOC 2 lands on the data engineer's desk in 2026

SOC 2 is an attestation against the Trust Services Criteria — not a checklist you buy, and half the sampled controls live in your warehouse

The one-sentence invariant: SOC 2 is a CPA firm's opinion that the controls you described are suitably designed and — for a Type II — actually operated over a period, and the data platform is where most of those controls physically live. There is no "SOC 2 software" that makes you compliant; there is a set of controls you run, and an audit that samples evidence they ran. Understanding that shape is what separates a data engineer who can speak to auditors from one who forwards every question to the security team.

What SOC 2 actually is.

• An attestation, not a certification. The output is a report signed by a licensed CPA firm following AICPA attestation standards, expressing an opinion on your controls. You do not "get certified"; you receive a report you can share with customers under NDA.
• Scoped to a system. The report covers a defined system — for a data platform that usually means the warehouse, the pipelines, the orchestration, the access model, and the cloud infrastructure underneath. Anything out of scope is not covered.
• Driven by your own control descriptions. You write the controls (the "system description"); the auditor tests them. That is why vague controls hurt you — you will be tested against exactly what you claimed.

The five Trust Services Criteria (TSC).

• Security (the Common Criteria) — mandatory. Every SOC 2 includes it: logical access, change management, risk assessment, monitoring. This is where most data-engineering controls sit.
• Availability — optional. Uptime, backup, disaster recovery, capacity — relevant if you promise SLAs on data delivery.
• Processing Integrity — optional. Data is processed completely, accurately, and on time — a natural fit for pipeline correctness and data-quality checks.
• Confidentiality — optional. Information designated confidential is protected — encryption, access restriction, retention/disposal.
• Privacy — optional. Personal information is collected, used, retained, and disposed of per your notice — the strictest, and often deferred.

Type I vs Type II — the distinction interviewers love.

• Type I is a point in time. The auditor opines that controls are suitably designed as of a single date. It answers "do the right controls exist?" — a snapshot.
• Type II is a period. The auditor opines that controls operated effectively across a window, usually 3 to 12 months. It answers "did the controls actually run, every time, the whole period?" — and it is tested by sampling evidence across that window.
• Why it matters to a DE. Type II is the one customers ask for, and it is the one that forces you to retain evidence continuously. A control that worked once but has no audit trail across the period fails a Type II even if it was perfectly designed.

The controls a data engineer actually owns.

• Logical access — provisioning, least privilege, periodic access reviews, deprovisioning.
• Audit logging — warehouse query and access history proving who read and changed what.
• Change management — version control, peer review, and approvals on pipeline code.
• Encryption — at rest and in transit, usually inherited from the cloud warehouse but attested by you.
• Monitoring — pipeline failure alerting, anomaly detection, and control-monitoring dashboards.
• Evidence — the pipelines that gather all of the above into a form an auditor can sample.

What interviewers listen for.

• Do you say "SOC 2 is an attestation against the Trust Services Criteria," not "a certification you pass"? — senior signal.
• Do you name Security as the mandatory Common Criteria and the other four as scope-dependent? — required framing.
• Do you distinguish Type II as operating-effectiveness over a period from Type I's point-in-time design? — the classic discriminator.
• Do you frame your job as "owning the access, logging, change-management, and evidence controls," not "helping the security team"? — ownership signal.

Worked example — mapping one control to its evidence

Detailed explanation. The single most useful habit for SOC 2 is to stop thinking in vague policies and start thinking in control → evidence pairs. Every control you claim must produce an artifact an auditor can pull. Modeling that mapping as data — a small control catalog — is the mindset that makes the rest of this guide click, because each later section is just one row of this catalog turned into a real query.

Question. Express three data-platform controls as a catalog that pairs each control with the concrete evidence query or artifact that proves it operated.

Input.

control_id	control	trust criterion
CC6.1	Least-privilege access to the warehouse	Security
CC6.2	Access removed on termination	Security
CC8.1	Changes are peer-reviewed and approved	Security

Code.

control_catalog = {   # each control maps to the evidence that proves it ran
    "CC6.1": {
        "control": "Least-privilege access to the warehouse",
        "evidence": "SELECT grantee_name, role, privilege FROM account_usage.grants_to_users",
        "cadence": "quarterly access review",
    },
    "CC6.2": {
        "control": "Access removed on termination",
        "evidence": "grants anti-joined against the active-employee roster (0 orphans)",
        "cadence": "daily deprovisioning check",
    },
    "CC8.1": {
        "control": "Changes are peer-reviewed and approved",
        "evidence": "deployments LEFT JOIN approved_prs (0 unapproved prod deploys)",
        "cadence": "per deploy",
    },
}

for cid, c in control_catalog.items():
    print(cid, "->", c["evidence"])

Step-by-step explanation. Each key is a control identifier borrowed from the Security Common Criteria numbering (CC6.x is logical access, CC8.x is change management). Each value pins the evidence — an actual query or artifact — and a cadence that says how often the evidence is produced. The auditor does not accept "we use least privilege"; they accept the output of the grants query, sampled on the review date. Turning every control into a runnable evidence statement is exactly what an evidence-collection pipeline automates.

Output.

control_id	evidence the auditor samples
CC6.1	grant listing from account_usage.grants_to_users
CC6.2	anti-join showing 0 orphaned grants
CC8.1	join showing every prod deploy has an approved PR

Rule of thumb. If you cannot name the query or artifact that proves a control, you do not have a SOC 2 control — you have a policy. Every control must resolve to evidence you can pull on demand.

---

2. Access controls data engineers own

Provisioning, least privilege, and deprovisioning are the CC6 controls auditors sample first — and the warehouse is where they live

The Security criterion's logical-access controls (the CC6 family) are the ones every SOC 2 tests, and for a data team they resolve to warehouse grants. Say the loop in one breath: access is provisioned from an authoritative source, granted at least privilege through roles, reviewed periodically, and revoked the moment someone leaves. Break any link and you have a finding.

Provisioning from an authoritative source.

• Joiner-mover-leaver. Access should be driven by the HR system or an identity provider, not by ad-hoc Slack requests. A joiner gets a role, a mover's role changes, a leaver's access is revoked — each event tied to a record.
• Request and approval trail. Every grant should trace to an approved request (a ticket, an IdP group assignment) so the auditor can see why someone has access, not just that they do.

Least privilege through roles, not direct grants.

• RBAC, never user grants. In Snowflake, BigQuery, or Redshift, privileges attach to roles and roles attach to users. Granting SELECT directly to a user is un-auditable and un-revocable at scale; granting a role is both.
• Separate read, write, and admin. A read_raw role, a read_pii role gated behind a masking policy, and a loader role that can write are three different privileges. Nobody gets the union "just in case."
• Masking and row-access policies. Column masking and row-access policies let you grant a role table access while still restricting the sensitive columns or rows — least privilege at the cell level, and itself an attestable control.

Periodic access reviews.

• Quarterly is the common cadence. A reviewer confirms each grant is still needed. The evidence is the review record plus the grant snapshot it reviewed.
• The review must be actionable. "Looks fine" is not evidence; a diff showing what changed since last quarter, and any revocations that resulted, is.

Deprovisioning — the control that fails Type II most often.

• The gap is the finding. The dangerous window is between a termination date and the moment access is actually removed. Auditors sample terminated employees and check the revocation timestamp against the leave date.
• Automate it or lose it. Manual deprovisioning drifts; an automated daily check that anti-joins live grants against the active roster catches the orphan before the auditor does.

Worked example — a least-privilege role hierarchy in Snowflake

Detailed explanation. The clearest demonstration of least privilege is a role hierarchy where table privileges live on functional roles and people inherit only what they need. No human holds a direct table grant; a person is granted a role, and the role holds the privilege. That indirection is what makes access reviewable and revocable in one statement.

Question. Grant an analyst read access to a customers table but keep the email column masked, using roles rather than direct user grants.

Input. A customers table with an email PII column, and a user ada who should read it masked.

Code.

-- 1. Functional role holds the privilege, not the user.
CREATE ROLE IF NOT EXISTS read_customers;
GRANT USAGE ON DATABASE analytics TO ROLE read_customers;
GRANT USAGE ON SCHEMA analytics.core TO ROLE read_customers;
GRANT SELECT ON TABLE analytics.core.customers TO ROLE read_customers;

-- 2. Column masking policy — email is only unmasked for a privileged role.
CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('PII_ADMIN') THEN val
       ELSE REGEXP_REPLACE(val, '.+@', '****@') END;
ALTER TABLE analytics.core.customers
  MODIFY COLUMN email SET MASKING POLICY mask_email;

-- 3. The person inherits the role — never a direct table grant.
GRANT ROLE read_customers TO USER ada;

Step-by-step explanation. Step 1 puts every privilege on read_customers, so the grant is auditable as one row and revocable with one REVOKE ROLE. Step 2 attaches a masking policy to the email column: any role except PII_ADMIN sees ****@domain, so the table grant does not leak the PII. Step 3 grants the role to ada; she never receives a direct table privilege, so a reviewer sees exactly one line — "ada has read_customers" — instead of hunting through per-object grants.

Output.

principal	can read customers	sees raw email?
ada (via read_customers)	yes	no — masked ****@
PII_ADMIN	yes	yes
everyone else	no grant	n/a

Rule of thumb. If revoking a person's access takes more than one REVOKE ROLE, your access model is not least-privilege — privileges belong on roles, and people belong in roles.

SQL interview question on deprovisioning

Question. Your auditor samples five terminated employees and asks you to prove none of them retained warehouse access after their leave date. Write a query that lists any active grant whose grantee is no longer an active employee — the orphaned-access anti-join.

Solution Using an anti-join against the active-employee roster

Code.

-- grants_snapshot: current warehouse grants (grantee_name, role, granted_on)
-- hr_roster: employees with status and termination_date
SELECT
    g.grantee_name,
    g.role,
    r.termination_date,
    DATEDIFF('day', r.termination_date, CURRENT_DATE) AS days_orphaned
FROM grants_snapshot AS g
LEFT JOIN hr_roster AS r
       ON UPPER(g.grantee_name) = UPPER(r.warehouse_user)
WHERE r.warehouse_user IS NULL          -- grant with no matching employee at all
   OR r.status = 'terminated'           -- or a terminated employee still granted
ORDER BY days_orphaned DESC;

Step-by-step trace.

grantee	in roster?	status	verdict
ADA	yes	active	not returned (compliant)
LINUS	yes	terminated 2026-02-10	returned — orphaned 33 days
SVC_ETL	yes	active (service)	not returned
GHOST	no match	—	returned — grant with no owner

1. Every current grant is the driving side of a LEFT JOIN onto the HR roster keyed on the warehouse username.
2. WHERE r.warehouse_user IS NULL catches grants whose grantee is not in the roster at all — the un-owned accounts auditors hate.
3. r.status = 'terminated' catches grantees who are in the roster but have left — the deprovisioning gap.
4. days_orphaned quantifies exposure so the worst offenders sort to the top and the remediation is prioritized.

Output:

grantee_name	role	termination_date	days_orphaned
LINUS	read_customers	2026-02-10	33
GHOST	loader	(none)	(unknown owner)

Why this works — concept by concept:

• Anti-join — a LEFT JOIN plus WHERE right IS NULL (widened here to include terminated rows) returns exactly the grants that should not exist; it is the canonical "find what is missing from the allowed set" pattern.
• Authoritative roster — the HR/IdP roster is the source of truth for "who is an employee," so access correctness is defined against it, not against a hand-maintained list.
• Deprovisioning gap — days_orphaned turns a boolean finding into a measured risk window, which is exactly the metric a Type II auditor tests against the leave date.
• Service accounts — un-matched grantees surface non-human accounts that need a named owner, closing the "who owns GHOST?" question before it becomes a finding.
• Cost — O(grants) with a hash join on the roster; a nightly run over a few thousand grants is milliseconds and produces continuous deprovisioning evidence.

Access
Topic — access-control
Least-privilege and access-review problems

Practice →

Quality Topic — data-validation Roster-reconciliation and validation problems

Practice →

---

3. Warehouse audit logs & access history

Query and access history are the evidence that access controls actually held — logging is what turns a policy into proof

An access policy you cannot observe is a policy you cannot attest. The Security criterion's monitoring controls demand that you can answer "who read this, who changed that, when, and from where" for the whole audit period. Modern warehouses hand you this for free through account-usage views — the trick is knowing which view proves which control, and respecting their latency and retention. Say it plainly: audit logs are the operating-effectiveness evidence; without them a well-designed control still fails a Type II.

The log surfaces you actually cite.

• LOGIN_HISTORY. Every authentication attempt, success or failure, with client IP and method. Proves the authentication and MFA-enforcement controls and surfaces brute-force patterns.
• QUERY_HISTORY. Every statement run, by whom, against what, with duration and bytes scanned. The backbone of "who ran what" evidence.
• ACCESS_HISTORY. The strong one for confidentiality: per-query, the exact objects and columns read and written. This is what proves who touched a PII column, not just who queried the table.
• GRANTS_TO_USERS / GRANTS_TO_ROLES. Point-in-time snapshots of the access model itself — the evidence behind the access-review control from section 2.

Column-level access is the confidentiality proof.

• Table-level is not enough. "User X selected from customers" does not prove whether they read the masked email. ACCESS_HISTORY records the columns actually referenced, so you can prove column-level confidentiality.
• Masking and row-access policies leave a trail too. Applying and altering a policy is itself a change auditors can see, tying the confidentiality control back to change management.

Latency and retention — the trap.

• Account-usage views lag. Snowflake's ACCOUNT_USAGE views can be delayed (often up to ~2–3 hours for access history), so a real-time control cannot depend on them; an evidence-collection job must account for the lag.
• Retention is finite. ACCOUNT_USAGE retains history for a bounded period (on the order of a year for many views, less for some). A Type II window can exceed retention, so you must export logs to durable storage or you will have gaps exactly when the auditor samples an old date.
• Other platforms, same idea. BigQuery exposes Cloud Audit Logs (Admin Activity and Data Access) and INFORMATION_SCHEMA.JOBS; Redshift has STL/SVL system tables and CloudTrail. The pattern is identical: system views prove access, and you export them before they age out.

Worked example — reading who queried a sensitive table

Detailed explanation. The everyday audit query answers "show me every access to this table in the review window." It is the first thing you run when an auditor points at a sensitive object, and the shape — filter QUERY_HISTORY by object and time — is the foundation the harder column-level query builds on.

Question. List every user who queried analytics.core.customers in the last 90 days, with how many times and when they last touched it.

Input. QUERY_HISTORY rows over the last 90 days referencing various tables.

Code.

SELECT
    user_name,
    COUNT(*)              AS query_count,
    MAX(start_time)       AS last_accessed
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP)
  AND query_text ILIKE '%analytics.core.customers%'
GROUP BY user_name
ORDER BY query_count DESC;

Step-by-step explanation. The WHERE clause bounds the window to the 90-day review period and filters to statements referencing the target table. GROUP BY user_name collapses each principal to one row; COUNT(*) is their access frequency and MAX(start_time) is the recency an auditor asks for. Filtering on query_text is the quick approximation — the precise, column-aware version uses ACCESS_HISTORY, which the interview question below demands.

Output.

user_name	query_count	last_accessed
ADA	42	2026-09-12 08:15
BI_SERVICE	310	2026-09-14 23:59
LINUS	3	2026-02-09 17:40

Rule of thumb. QUERY_HISTORY proves that a table was queried; when the question is which columns were read, you must move to ACCESS_HISTORY — text matching cannot prove column-level confidentiality.

SQL interview question on column-level access history

Question. An auditor wants proof of exactly which principals read the email PII column of customers in the last 90 days. Table-level history is not enough. Write the query using Snowflake ACCESS_HISTORY.

Solution Using LATERAL FLATTEN over ACCESS_HISTORY

Code.

SELECT
    ah.user_name,
    cols.value:columnName::STRING AS column_read,
    COUNT(*)                      AS reads,
    MAX(ah.query_start_time)      AS last_read
FROM snowflake.account_usage.access_history AS ah,
     LATERAL FLATTEN(input => ah.base_objects_accessed) AS obj,
     LATERAL FLATTEN(input => obj.value:columns)        AS cols
WHERE ah.query_start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP)
  AND obj.value:objectName::STRING = 'ANALYTICS.CORE.CUSTOMERS'
  AND cols.value:columnName::STRING = 'EMAIL'
GROUP BY ah.user_name, column_read
ORDER BY reads DESC;

Step-by-step trace.

access_history row	object	columns array	matches email?
q1 by ADA	CUSTOMERS	[id, name, email]	yes — 1 read
q2 by ADA	CUSTOMERS	[id, name]	no
q3 by BI_SERVICE	CUSTOMERS	[id, email]	yes — 1 read
q4 by LINUS	ORDERS	[order_id]	no — wrong object

1. ACCESS_HISTORY.base_objects_accessed is a semi-structured array of the base objects each query touched; the first FLATTEN yields one row per object.
2. Each object carries a nested columns array; the second FLATTEN yields one row per column actually referenced — this is the column-level granularity table-level history cannot give.
3. The WHERE filters to the target object and the EMAIL column, so only genuine reads of the PII column survive.
4. Grouping by user and column produces per-principal read counts and recency — precisely the confidentiality evidence the auditor asked for.

Output:

user_name	column_read	reads	last_read
BI_SERVICE	EMAIL	118	2026-09-14 23:59
ADA	EMAIL	7	2026-09-11 10:02

Why this works — concept by concept:

• base_objects_accessed — ACCESS_HISTORY records the resolved base objects and columns per query, so it sees through views and SELECT * to the real columns read — the only reliable source for column-level proof.
• LATERAL FLATTEN — flattening the nested object and column arrays turns one query row into one row per column, letting a normal GROUP BY count column-level access.
• Column-level confidentiality — the result names the exact principals who read email, which is what the Confidentiality criterion requires and what table-level history can only approximate.
• Export before retention — because ACCESS_HISTORY ages out, this query must run inside an evidence job that persists results, or the proof vanishes before the audit.
• Cost — O(rows × columns-per-query) over the flattened set; scanning a bounded 90-day window keeps it cheap, and pushing the object filter down limits the scan.

Access
Topic — access-control
Column-level access and audit-trail problems

Practice →

Audit Topic — event-log Event-log and access-history query problems

Practice →

---

4. Change management for pipelines

Every pipeline change must trace to a reviewed, approved, controlled deploy — CC8 is the control auditors reconstruct from your git and CI history

The Security criterion's change-management control (CC8.1) asks a simple question with expensive consequences: can you prove that every change to production went through review and approval? For a data team, "production" is the pipeline code, the transformations, and the schema. The auditor reconstructs the control from version control and CI, so the control is only as good as the trail those systems leave. Say it in one line: an approved, peer-reviewed pull request is the unit of controlled change, and a prod deploy without one is the finding.

The change-control chain.

• Version control is the system of record. All pipeline and transformation code lives in git; nothing reaches production except through a merge. A change with no commit is an un-auditable change.
• Peer review is the control. A pull request reviewed and approved by someone other than the author is the evidence. The approval, the reviewer identity, and the timestamp are the artifact.
• CI/CD gates the deploy. The deployment pipeline should refuse to ship a branch that was not merged through an approved PR, and it should record what it shipped — commit SHA, PR number, actor, time.

Segregation of duties.

• Author is not approver. The person who wrote the change cannot be the one who approves it — the review is meaningless otherwise, and auditors check for self-approval.
• Approver is not necessarily deployer, and deploys are automated. Manual, un-logged production access is the anti-pattern; an automated deploy from an approved merge removes the human who could bypass the control.
• Break-glass is logged. Emergency changes happen; the control is not "never," it is "every emergency change is logged, justified, and reviewed after the fact."

The evidence is a join.

• A deployments table. Each production deploy records deploy_id, commit_sha, pr_number, deployed_by, deployed_at. This is your change log.
• An approvals table. Each merged PR records pr_number, author, approved_by, approved_at. Pulled from the git host's API.
• The control test is the reconciliation. Left-join deploys to approvals; any deploy with no approved PR, or where approver equals author, is a change-control exception — the exact thing a Type II samples.

Worked example — recording a deploy with its approval lineage

Detailed explanation. The control produces evidence only if the deploy step writes it down. The cleanest pattern is a deploy job that, at ship time, records the commit, the PR it came from, and who triggered it into a deployments table. That single insert is what a whole quarter of change-control evidence is built on.

Question. At deploy time, capture the change-control lineage of a pipeline release into a deployments table so it can later be reconciled against approvals.

Input. A release of commit 9f3a1c merged via PR 412, deployed by the CI service account.

Code.

import hashlib, datetime as dt

def record_deploy(conn, commit_sha, pr_number, actor):
    row = {
        "deploy_id": hashlib.sha1(f"{commit_sha}{pr_number}".encode()).hexdigest()[:12],
        "commit_sha": commit_sha,
        "pr_number": pr_number,
        "deployed_by": actor,
        "deployed_at": dt.datetime.utcnow().isoformat(),
        "environment": "prod",
    }
    conn.execute(
        """INSERT INTO change_control.deployments
           (deploy_id, commit_sha, pr_number, deployed_by, deployed_at, environment)
           VALUES (%(deploy_id)s, %(commit_sha)s, %(pr_number)s,
                   %(deployed_by)s, %(deployed_at)s, %(environment)s)""",
        row,
    )
    return row

record_deploy(conn, commit_sha="9f3a1c", pr_number=412, actor="ci-deployer")  # CI-only, never a human shell

Step-by-step explanation. The deploy job — not a human — calls record_deploy as its final step, so the evidence is a byproduct of shipping, impossible to forget. It captures the commit_sha and the pr_number that carried it, plus the acting identity and a UTC timestamp. Because CI is the only path to prod, the deployments table becomes a complete census of production changes, and the pr_number is the foreign key that lets the auditor's reconciliation join to the approvals table.

Output.

deploy_id	commit_sha	pr_number	deployed_by	environment
3d9e1a77c0b2	9f3a1c	412	ci-deployer	prod

Rule of thumb. If a deploy can happen without writing a row to the deployments table, your change-control evidence has holes — make the evidence write a mandatory step of the deploy, not a manual afterthought.

SQL interview question on change-control exceptions

Question. Prove that every production deploy in the audit window came from a peer-approved PR, and that no change was self-approved. Write the query that returns the change-control exceptions.

Solution Using an anti-join with a segregation-of-duties check

Code.

SELECT
    d.deploy_id,
    d.pr_number,
    d.deployed_by,
    a.author,
    a.approved_by,
    CASE
        WHEN a.pr_number IS NULL              THEN 'no approved PR'
        WHEN a.approved_by = a.author         THEN 'self-approved'
    END AS exception_type
FROM change_control.deployments AS d
LEFT JOIN change_control.approved_prs AS a
       ON d.pr_number = a.pr_number
WHERE d.environment = 'prod'
  AND d.deployed_at >= DATE '2026-01-01'
  AND (a.pr_number IS NULL OR a.approved_by = a.author)
ORDER BY d.deployed_at;

Step-by-step trace.

deploy	pr_number	approved?	author=approver?	verdict
dep_1	412	yes	no	compliant — not returned
dep_2	419	none in approvals	—	returned — no approved PR
dep_3	421	yes	ada = ada	returned — self-approved
dep_4	430	yes	no	compliant — not returned

1. Every prod deploy in the window is the driving side of a LEFT JOIN onto approved PRs, keyed on pr_number.
2. a.pr_number IS NULL catches deploys whose PR was never approved (or never existed) — an uncontrolled change.
3. a.approved_by = a.author catches the segregation-of-duties violation where the author approved their own change.
4. Only exceptions survive the WHERE; an empty result set is the passing evidence, and any rows are the exact deploys the auditor will drill into.

Output:

deploy_id	pr_number	exception_type
dep_2	419	no approved PR
dep_3	421	self-approved

Why this works — concept by concept:

• Change-control chain — modeling deploys and approvals as two joinable tables turns a fuzzy policy ("we review changes") into a testable reconciliation with a boolean outcome per deploy.
• Anti-join for missing approvals — the LEFT JOIN ... IS NULL pattern isolates deploys with no approval, the same shape as the deprovisioning check, reused for a different control.
• Segregation of duties — comparing approved_by to author encodes the "author is not approver" rule directly in SQL, so self-approval cannot hide.
• Empty-set-is-passing — a control whose passing state is "zero exceptions" is easy to monitor continuously and easy to alert on the moment a violation appears.
• Cost — O(deploys) with a hash join on pr_number; trivial to run per deploy as a gate and nightly as evidence.

Reliability
Topic — reliability
Change-control and deployment-safety problems

Practice →

SLA Topic — sla-monitoring Monitoring and control-status problems

Practice →

---

5. Evidence-collection pipelines

Automated evidence is what makes a Type II survivable — a pipeline that snapshots controls into an immutable, tamper-evident ledger

A Type II audit does not sample your controls on one lucky day; it samples them across months, and it expects the evidence to have existed at the time, not to be reconstructed afterward. Collecting that evidence by hand — screenshots, spreadsheets, quarterly scrambles — is where audits go to die. The senior move is to treat evidence like any other pipeline output: scheduled jobs snapshot each control's evidence into an append-only, tamper-evident store, so the audit is a query, not a project.

What an evidence-collection pipeline gathers.

• Access snapshots. Nightly captures of GRANTS_TO_USERS / GRANTS_TO_ROLES so every point-in-time access state exists for the whole window, not just today.
• Access-review artifacts. The quarterly review's input snapshot, the reviewer, and the resulting revocations, stored together.
• Log exports. ACCESS_HISTORY, QUERY_HISTORY, and LOGIN_HISTORY exported before they age out of retention.
• Control-test results. The anti-join outputs from sections 2–4 (orphaned grants, PII reads, change-control exceptions), each run and stored with its timestamp so "the control ran and passed on this date" is itself evidence.

Immutability — what makes evidence trustworthy.

• Append-only, never update. Evidence tables are insert-only. An evidence store you can UPDATE or DELETE is one an auditor cannot trust; the control is only credible if the record cannot be quietly changed.
• Hash-chaining for tamper-evidence. Each row stores a hash of its own contents plus the previous row's hash. Altering any historical row breaks the chain from that point forward, so tampering is detectable without trusting the database alone.
• WORM / Object Lock storage. Exported evidence lands in write-once-read-many object storage (S3 Object Lock in compliance mode, GCS retention lock) so even an administrator cannot delete it before its retention expires.

Continuous control monitoring.

• A control that reports its own status. Instead of proving controls at audit time, each control emits a PASS/FAIL with its evidence on every run, feeding a monitoring dashboard.
• Alert on transition, not on state. The signal that matters is a control flipping from PASS to FAIL — an orphaned grant appearing, a self-approved deploy landing — which routes to on-call the same day, shrinking the exposure window the auditor would otherwise measure.

Worked example — an append-only evidence snapshot

Detailed explanation. The building block of an evidence pipeline is a job that runs a control's evidence query and appends the result with a run timestamp — never overwriting the previous run. Keeping every run is what gives you a point-in-time record for any date the auditor picks.

Question. Snapshot today's grant listing into an append-only evidence_grants table so that every past day's access state is preserved for the audit window.

Input. The current output of the grants query, captured on run date 2026-09-15.

Code.

import datetime as dt

def snapshot_grants(conn):
    run_ts = dt.datetime.utcnow().isoformat()
    conn.execute(
        """INSERT INTO evidence.evidence_grants
           (captured_at, grantee_name, role, privilege)
           SELECT %(ts)s, grantee_name, role, privilege
           FROM   snowflake.account_usage.grants_to_users
           WHERE  deleted_on IS NULL""",     # only currently-live grants
        {"ts": run_ts},
    )
    # NOTE: no UPDATE / DELETE anywhere — the table is insert-only.

Step-by-step explanation. The job stamps every captured row with a single captured_at for the run, then inserts the live grant set as of that moment. Because the table is only ever inserted into, run N+1 never disturbs run N — the history of "who had what access on any given day" accumulates. Filtering deleted_on IS NULL captures the live state; the accumulation of daily snapshots is what lets the auditor ask "show me access on 2026-06-30" months later.

Output.

captured_at	grantee_name	role	privilege
2026-09-15T00:00	ADA	read_customers	SELECT
2026-09-15T00:00	BI_SERVICE	read_raw	SELECT
2026-09-14T00:00	ADA	read_customers	SELECT

Rule of thumb. Evidence tables grow forever and change never — the day you find yourself wanting to UPDATE an evidence row is the day your evidence stops being evidence.

Python interview question on tamper-evident evidence

Question. An auditor asks how you would prove your evidence ledger has not been altered after the fact. Design an append-only evidence table where any tampering with a historical row is detectable, and show how the check works.

Solution Using a hash-chained immutable ledger

Code.

import hashlib, json

def row_hash(prev_hash: str, payload: dict) -> str:
    body = prev_hash + json.dumps(payload, sort_keys=True)
    return hashlib.sha256(body.encode()).hexdigest()

def append_evidence(ledger: list[dict], payload: dict) -> dict:
    prev = ledger[-1]["row_hash"] if ledger else "GENESIS"
    entry = {"seq": len(ledger), "payload": payload,
             "prev_hash": prev, "row_hash": row_hash(prev, payload)}
    ledger.append(entry)                 # append-only
    return entry

def verify(ledger: list[dict]) -> int | None:
    prev = "GENESIS"
    for e in ledger:
        if e["prev_hash"] != prev or e["row_hash"] != row_hash(prev, e["payload"]):
            return e["seq"]              # first tampered sequence, or None if intact
        prev = e["row_hash"]
    return None

Step-by-step trace.

seq	payload	prev_hash	row_hash	chain ok?
0	grants@day1	GENESIS	h0	yes
1	grants@day2	h0	h1	yes
2	grants@day3 (tampered)	h1	h2' ≠ recompute	breaks here
3	grants@day4	h2 (stale)	h3	fails — prev mismatch

1. Each entry's row_hash is sha256(prev_hash + payload), so every row cryptographically depends on the entire history before it.
2. append_evidence only ever appends and always chains onto the last row's hash — there is no code path that rewrites a prior entry.
3. verify recomputes each hash from the previous one; if an attacker edits day3's payload, its recomputed hash no longer matches the stored row_hash, and every subsequent link fails too.
4. verify returns the first broken seq, pinpointing exactly where the ledger was tampered — evidence that the evidence itself is intact.

Output:

ledger state	verify() returns	meaning
untouched	None	chain intact — evidence trustworthy
row 2 edited	2	tamper detected at seq 2

Why this works — concept by concept:

• Append-only ledger — insert-only semantics remove the "quiet update" attack entirely; combined with WORM storage, even a privileged operator cannot rewrite history.
• Hash chaining — binding each row to the previous hash makes the ledger tamper-evident: changing any row invalidates every row after it, so a single stored final hash attests the whole chain.
• Deterministic serialization — json.dumps(..., sort_keys=True) guarantees the same payload always hashes identically, so a legitimate re-verification never produces a false tamper alarm.
• Continuous verification — running verify on a schedule turns tamper-evidence into tamper-detection, alerting the day the chain breaks rather than at audit time.
• Cost — append is O(1) and verification is O(n) over the ledger; a nightly full verify of a year of daily snapshots is a few hundred hashes, negligible against the audit assurance it buys.

SLA
Topic — sla-monitoring
Control-monitoring and alerting problems

Practice →

Quality Topic — data-quality Immutable-ledger and integrity-check problems

Practice →

---

Cheat sheet — SOC 2 evidence recipes

List all warehouse grants (access evidence).

SELECT grantee_name, role, privilege, granted_on
FROM   snowflake.account_usage.grants_to_users
WHERE  deleted_on IS NULL
ORDER BY grantee_name;

Quarterly access review (diff against last snapshot).

SELECT grantee_name, role
FROM   evidence.evidence_grants
WHERE  captured_at = (SELECT MAX(captured_at) FROM evidence.evidence_grants)
EXCEPT
SELECT grantee_name, role
FROM   evidence.evidence_grants
WHERE  captured_at = :previous_review_snapshot;   -- rows added since last review

Column-level PII access (confidentiality evidence).

SELECT ah.user_name, c.value:columnName::STRING AS col, COUNT(*) reads
FROM   snowflake.account_usage.access_history ah,
       LATERAL FLATTEN(input => ah.base_objects_accessed) o,
       LATERAL FLATTEN(input => o.value:columns) c
WHERE  o.value:objectName::STRING = 'ANALYTICS.CORE.CUSTOMERS'
  AND  c.value:columnName::STRING = 'EMAIL'
GROUP BY 1, 2;

Change-control exceptions (CC8 evidence).

SELECT d.deploy_id, d.pr_number
FROM   change_control.deployments d
LEFT JOIN change_control.approved_prs a ON d.pr_number = a.pr_number
WHERE  d.environment = 'prod'
  AND (a.pr_number IS NULL OR a.approved_by = a.author);   -- empty = passing

Immutable evidence table (insert-only, Object Lock target).

CREATE TABLE evidence.evidence_grants (
  captured_at   TIMESTAMP_NTZ,
  grantee_name  STRING,
  role          STRING,
  privilege     STRING
);
-- Grant only INSERT + SELECT to the evidence role; never UPDATE/DELETE.
GRANT INSERT, SELECT ON evidence.evidence_grants TO ROLE evidence_writer;

Trust criterion -> control -> evidence.

Trust criterion	Control a DE owns	Evidence artifact
Security (CC6)	Least privilege + deprovisioning	grants snapshot + orphan anti-join
Security (CC7)	Monitoring / audit logging	ACCESS_HISTORY column-read export
Security (CC8)	Change management	deploy ⋈ approved-PR reconciliation
Confidentiality	PII access restriction	masked columns + column-access log
Processing Integrity	Pipeline correctness	data-quality check results, stored

---

Frequently asked questions

What is SOC 2 and which parts do data engineers own?

SOC 2 is an independent attestation, performed by a licensed CPA firm against the AICPA Trust Services Criteria, that an organization's controls are suitably designed and — in a Type II — operated effectively over a period. It is a report you share with customers, not a certification you pass once. Data engineers own the controls that live in the data platform: logical access (least privilege, provisioning, deprovisioning), audit logging, change management on pipeline code, encryption of data at rest and in transit, and the evidence-collection that proves all of them.

What is the difference between SOC 2 Type I and Type II?

A Type I report opines that controls are suitably designed as of a single point in time — a snapshot that answers "do the right controls exist?" A Type II report opines that those controls operated effectively across a window, typically 3 to 12 months, and is tested by sampling evidence throughout that period. Type II is the one customers usually require, and it is the reason you must retain evidence continuously rather than assembling it at audit time.

What are the five Trust Services Criteria?

They are Security, Availability, Processing Integrity, Confidentiality, and Privacy. Security — the Common Criteria — is mandatory in every SOC 2 and covers logical access, change management, risk assessment, and monitoring. The other four are included only if they are in scope for what you promise customers: Availability for uptime and recovery, Processing Integrity for complete and accurate processing, Confidentiality for protecting designated confidential data, and Privacy for handling personal information per your notice.

Which warehouse audit logs prove access controls?

In Snowflake, LOGIN_HISTORY proves authentication, QUERY_HISTORY proves who ran what, and ACCESS_HISTORY proves the exact objects and columns each query read or wrote — the last one is what evidences column-level confidentiality. GRANTS_TO_USERS and GRANTS_TO_ROLES snapshot the access model itself for access reviews. BigQuery exposes the same idea through Cloud Audit Logs and INFORMATION_SCHEMA.JOBS, and Redshift through its STL/SVL system tables plus CloudTrail; in every case you export the logs before they age out of retention.

How do you automate SOC 2 evidence collection?

You build scheduled pipelines that snapshot each control's evidence into an append-only store: nightly grant snapshots, log exports before retention lapses, and the control-test queries (orphaned grants, PII reads, change-control exceptions) run and stored with a timestamp. Each run records a PASS/FAIL plus the underlying evidence, feeding a control-monitoring dashboard that alerts the moment a control flips to failing. The audit then becomes a query over the evidence store rather than a quarterly screenshot scramble.

What makes an audit table "immutable" for SOC 2?

Three properties. It is append-only — the evidence role has INSERT and SELECT but never UPDATE or DELETE, so records cannot be quietly changed. It is tamper-evident through hash-chaining, where each row hashes its contents plus the previous row's hash, so altering any historical row breaks the chain and is detectable. And it is written to write-once-read-many storage (S3 Object Lock, GCS retention lock) so even an administrator cannot delete it before its retention period expires.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every SOC 2 control above, from the least-privilege role hierarchy and the orphaned-grant anti-join to the column-level access-history query and the hash-chained evidence ledger, maps to a hands-on practice room where you write the query against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you prove this control operated over the whole audit period?" holds up under a senior interviewer's depth probes.

Practice access-control problems now →
SLA-monitoring drills →