Skip to content

Commit ff58e0a

Browse files
altimateanasclaude
andcommitted
Address PR #1164 review comments (26 fixes)
Applies fixes for every valid finding from the cubic-dev-ai code review on PR #1164. All 95 default + 4 behavior tests pass after fixes. P1 (1 fix): 1. greenfield-runnable.sql orphan refs — commented out GRANT/ALTER/ validation lines targeting objects the stripped file never creates (integration, stage, pipe, CUSTOMERS table). Marked with -- ORPHAN: prefix and header note explaining the strip scope. P2 (18 fixes): 2+21. sql-execute.test.ts rewritten as behavior tests — mocks the Dispatcher, invokes the actual tool, asserts on returned output rather than source-text regex. Catches the real regression instead of implementation markers. 3. governance POLICY_REFERENCES query — compare against fully-qualified name (db.schema.table), filter policy_kind = 'MASKING_POLICY'. 4. cost-governance retention_time column — rename from the invalid data_retention_time_in_days. 5. DV2 topology token — canonical `data-vault-2` used in both question label and branch checks so DV2-specific DDL actually emits. 6. Output format duplication — Q7 detail question now defers to Triage Q2. 7. Rollback split range — tool-safe covers steps 1–6 and 9–14 (step 7 Schemas is MANUAL, not tool-safe). 8+9. Eval test error handling — narrow catch to timeout errors only, verify file size ≥ 500 bytes, set maxBuffer to 256 MiB. 10+17. Rollback confirmation guard — repeated at top of every role block (ACCOUNTADMIN, SECURITYADMIN, SYSADMIN), plus explicit note that session-variable guards only protect whole-script execution. 11+12. topology dev-clone grants — add schema-level USAGE + CREATE TABLE + SELECT/INSERT/UPDATE/DELETE grants so the example actually works instead of granting only database-level USAGE. 13+14. Terraform variables + sensitive outputs — declare var.raw_bucket, var.aws_snowflake_role_arn, var.allowed_ip_cidrs in variables.tf bootstrap; add sensitive = true to storage_integration_aws_iam_user_arn and storage_integration_aws_external_id outputs. 15+16. PII masking gap + S3 prefix — SKILL.md now requires masking to be applied at every layer that carries the PII column forward (BRONZE + SILVER + GOLD), not just at BRONZE. Fixed S3 event notification prefix to include APP/ subfolder. 18. data-vault REVOKE UPDATE/DELETE theater removed — VAULT_LOADER_ROLE was granted INSERT-only so the REVOKE is either an error or no-op. Replaced with pattern that discovers actual grant holders via SHOW GRANTS and revokes from parent roles. 19. audit task-staleness query — joins ACCOUNT_USAGE.TASK_HISTORY to compare last successful run against 7-day window (was RESULT_SCAN only, listing every started task with a schedule). 20. audit future-grants CTE — filter granted_on = 'FUTURE_TABLE' (not 'SCHEMA') and derive database.schema from qualified `name` via SPLIT_PART. P3 (7 fixes): 22. rbac-patterns access-role list — added TRANSFORM_WRITE and ANALYTICS_WRITE definitions (were referenced but never defined). 23. TESTING.md self-contradiction — reworded Tier 3 section to reflect the manual execution done in 2026-08-25/26 and codification as remaining follow-up work. 24+25. LIVE-EVAL-RESULTS totals reconciled — greenfield 65/84 successful, 19 (0 rows); rollback 38/41 succeeded, 3 blocked by tool guard. 26. SKILL.md miscount — corrected "5 blocking" to "7 blocking"; noted Q7 dedupes against Triage Q2. 27. Eval artifacts moved to os.tmpdir() to stop dirtying the checkout. 28. Rollback confirmation comment reflects the pre-filled locator (handled as part of #10+17). 29. Audit "unexpected IPs" renamed to "distinct session IPs (manual review)" with a note about adding an allowlist filter for true outlier detection. 30. Eval test NaN guard — expect(totalCreates).toBeGreaterThan(0) before the ratio check, matching the sister rollback guard. Tests: * Contract: 91 pass (adds 4 for the newly-emitted skill rules) * SQL-execute regression: 4 pass (was 3 text-regex; now real behavior) * Model-in-the-loop eval: 13 tests, opt-in via SNOWFLAKE_SETUP_EVAL=1 Co-Authored-By: Claude Opus 4.7 (1M context) <[email protected]>
1 parent a943d74 commit ff58e0a

17 files changed

Lines changed: 529 additions & 173 deletions

‎.opencode/skills/snowflake-setup/SKILL.md‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -68,13 +68,13 @@ Ask all blocking questions **in one batch** — do not proceed until answered.
6868

6969
**Blocking:**
7070

71-
1. **Topology?** — Medallion / Functional / Domain-per-Database / Data Vault 2.0 (see `topology-patterns.md`, and `data-vault-patterns.md` if DV2 chosen)
71+
1. **Topology?** — `medallion` (Medallion) / `functional` (Functional RAW/TRANSFORM/ANALYTICS) / `domain-per-db` (Domain-per-Database) / `data-vault-2` (Data Vault 2.0) — the value in backticks is the canonical token used by every downstream branch check in this file. Store the user's answer as one of those four tokens exactly; do not literalize the friendly label. See `topology-patterns.md` (and `data-vault-patterns.md` if `data-vault-2` chosen).
7272
2. **RBAC model?** — small-team single-layer / large-team functional+access two-layer (see `rbac-patterns.md`)
7373
3. **Environments?** — prod-only / prod+dev / prod+staging+dev
7474
4. **Ingestion sources?** (multi-select) — Fivetran or Airbyte, Snowpipe (event-driven), Task+COPY (batch), Snowpipe Streaming (Kafka), CDC from operational DBs
7575
5. **Cloud provider for external stages?** — AWS S3 / GCS / Azure Blob
7676
6. **Emission mode?** — `strict` / `idempotent` (default) / `additive` (see `idempotency-patterns.md`)
77-
7. **Output format?** — `sql` (default) / `terraform` / `both`
77+
7. ~~Output format~~ — **already answered in Triage Q2. Do not ask again.** Reuse the value from Triage Q2 (`sql` / `terraform` / `both`). This slot is kept only so the numbering below (8–16) matches earlier references in this file; the value is not re-prompted.
7878

7979
**Optional (defaults available):**
8080

@@ -161,9 +161,9 @@ Produce a single markdown plan with the following sections, in this order. For e
161161

162162
1. **Databases and Schemas** ← `topology-patterns.md` (placeholder-driven; see 4a). **If topology = `data-vault-2`**, also emit `RAW_VAULT`, `BUSINESS_VAULT`, `INFO_MARTS` per `data-vault-patterns.md`, plus the HUBS/LINKS/SATELLITES schema pattern inside `RAW_VAULT`.
163163
2. **Warehouses** ← `topology-patterns.md` § Warehouse Sizing Guide (placeholder-driven; see 4a). **In hybrid mode**, call `finops_warehouse_advice` on the existing account and use its recommendations to override the static sizing table where they differ. Present the delta to the user before emitting. **If topology = `data-vault-2`**, apply the DV2 cost adjustment table from `data-vault-patterns.md` (typically 2–3× LOADING_WH and TRANSFORM_WH baselines).
164-
3. **RBAC** ← `rbac-patterns.md` (placeholder-driven; see 4a). **In hybrid mode**, use `finops_role_hierarchy` + `finops_role_grants` output to detect existing roles and only emit DDL for missing ones. **If topology = `data-vault-2`**, add the `VAULT_LOADER_ROLE`, `BUSINESS_VAULT_BUILDER_ROLE`, and `MART_BUILDER_ROLE` from `data-vault-patterns.md`, and emit the insert-only enforcement `REVOKE UPDATE, DELETE` statements on `RAW_VAULT.*`.
164+
3. **RBAC** ← `rbac-patterns.md` (placeholder-driven; see 4a). **In hybrid mode**, use `finops_role_hierarchy` + `finops_role_grants` output to detect existing roles and only emit DDL for missing ones. **If topology = `data-vault-2`**, add the `VAULT_LOADER_ROLE`, `BUSINESS_VAULT_BUILDER_ROLE`, and `MART_BUILDER_ROLE` from `data-vault-patterns.md`. Do **not** emit blanket `REVOKE UPDATE, DELETE ... FROM ROLE VAULT_LOADER_ROLE` — the loader role is granted INSERT-only, so there is nothing to revoke; the statement is either a Snowflake error or a silent no-op depending on version (see `data-vault-patterns.md` § Insert-only enforcement for the correct pattern using `SHOW GRANTS` discovery + parent-role revocation).
165165
4. **Ingestion** (one subsection per selected source) ← `ingestion-patterns.md` (requires detail questions; see 4b)
166-
5. **Governance** ← `governance-patterns.md` (placeholder-driven for defaults; see PII discovery in step 5). **Before emitting any masking / row-access policy DDL**, verify each target column actually exists by calling `schema_inspect` on the target table (needs the warehouse name from step 2b). If the column is missing or has an unexpected type, refuse to emit that policy and surface the error. **If topology = `data-vault-2`**, apply masking at the layer chosen in the DV2 detail questions (RAW_VAULT satellites / BUSINESS_VAULT+INFO_MARTS / hybrid) per `data-vault-patterns.md` § PII placement.
166+
5. **Governance** ← `governance-patterns.md` (placeholder-driven for defaults; see PII discovery in step 5). **Before emitting any masking / row-access policy DDL**, verify each target column actually exists by calling `schema_inspect` on the target table (needs the warehouse name from step 2b). If the column is missing or has an unexpected type, refuse to emit that policy and surface the error. **Masking must be applied at every layer that carries the PII column forward** — applying `MASK_EMAIL` only at BRONZE while TRANSFORM_ROLE copies plaintext into SILVER/GOLD leaves ANALYST_ROLE reading unmasked PII from downstream marts (a real hole found in PR #1164 review). For each declared PII column, emit the `ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY` at BRONZE **AND** for every SILVER/GOLD/INFO_MARTS table that carries the column forward, or emit a `-- REQUIRED BEFORE EXPOSING TO ANALYST_ROLE:` comment listing each downstream table the user must add the policy to. **If topology = `data-vault-2`**, apply masking at the layer chosen in the DV2 detail questions (RAW_VAULT satellites / BUSINESS_VAULT+INFO_MARTS / hybrid) per `data-vault-patterns.md` § PII placement.
167167
6. **Cost Controls** ← `cost-governance.md` (placeholder-driven). **In hybrid mode**, seed monitor thresholds from `finops_analyze_credits` (30-day p95 usage × 1.5 = suggested quota).
168168
7. **Environment Promotion** (zero-copy clones) ← `topology-patterns.md` (placeholder-driven)
169169
8. **Network Security + SSO** (if enabled) ← `advanced-features.md` (requires detail questions; see 4b)
@@ -340,6 +340,7 @@ Regardless of mode or format, always generate a companion rollback SQL file per
340340
- Never drops built-in roles or the `SNOWFLAKE` database
341341
- SUSPEND tasks and UNSET policies/tags/monitors before dropping
342342
- Interactive confirmation guard at the top requiring the user to paste the account locator before destructive statements execute
343+
- **Repeat the `SET rollback_confirmed_account = ...` + `SELECT CASE ... ERROR()` guard at the top of EVERY role-scoped block** (ACCOUNTADMIN, SECURITYADMIN, SYSADMIN — not just the first). The session variable is per-session, so a top-of-file guard alone is bypassed when a user runs a single role block or when statements are dispatched one-per-session by `sql_execute`. See `idempotency-patterns.md` § Confirmation Prompt Template for the limitation details and mitigation pattern.
343344

344345
### 8. Execute per the User's Choice
345346

@@ -398,7 +399,7 @@ Ask these upfront, before reading any references or generating any DDL. Present
398399

399400
Based on the answer to triage Q1, ask the appropriate detailed questions:
400401

401-
- **`greenfield` or `hybrid`** → go to workflow step **2. Gather Requirements** and ask the 5 blocking + 4 optional + 5 feature-trigger questions listed there
402+
- **`greenfield` or `hybrid`** → go to workflow step **2. Gather Requirements** and ask the 7 blocking + 4 optional + 5 feature-trigger questions listed there (blocking Q7 is deduped against Triage Q2 — see step 2)
402403
- **`audit`** → go to workflow step **2b. Warehouse Preflight** first (verify a Snowflake warehouse is configured via `warehouse_list`; if not, prompt the user to run `warehouse_add` and stop). Then proceed to step **3. Run Diagnostic Queries**. Do not silently degrade to greenfield.
403404

404405
### Turn 3+ — Execute the Workflow

‎.opencode/skills/snowflake-setup/TESTING.md‎

Lines changed: 21 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -113,23 +113,32 @@ strings. First failure to investigate before assuming a regression: rerun and
113113
check if the LLM produced substantively different output vs cosmetically
114114
different.
115115

116-
**What it does NOT test:**
117-
- Applying the emitted DDL to a real Snowflake account (see §"Tier 3 live
118-
apply — future work" below)
119-
- Rollback correctness end-to-end (DDL → apply → rollback → verify blank)
120-
- Audit mode against a broken account
121-
- Terraform HCL emission or `terraform validate`
122-
- External integrations (S3, SSO, DR, sharing, Cortex)
116+
**What the automated Tier 2/3 test does NOT cover on its own:**
117+
- Applying the emitted DDL to a real Snowflake account — done once manually
118+
(see `LIVE-EVAL-RESULTS.md`); NOT codified as a repeatable test in this PR
119+
- Rollback correctness end-to-end (DDL → apply → rollback → verify blank) —
120+
same status: done manually, not codified
121+
- Audit mode against a broken account — done manually against DKZPOBS-TQ14188
122+
with 3 planted issues; results in `LIVE-EVAL-RESULTS.md`; not codified
123+
- Terraform HCL emission or `terraform validate` — done manually with
124+
OpenTofu 1.12.6; results in `LIVE-EVAL-RESULTS.md`; not codified
125+
- External integrations (S3, SSO, DR, sharing, Cortex) — genuine scope-out
126+
per skill guardrails, needs external infra
123127
- Non-Medallion topologies (DV2, Functional, Domain-per-DB) — the eval script
124-
covers Medallion only; add more scenarios in follow-up PRs
128+
covers Medallion only; add more scenarios in follow-up PRs (DV2 partial run
129+
was blocked mid-flight by LLM wallet limit; other two are trivial extensions)
125130

126131
---
127132

128-
## Tier 3 — Live Snowflake apply/rollback cycle (future work)
133+
## Tier 3 — Live Snowflake apply/rollback cycle (executed once; codification is follow-up work)
129134

130-
The remaining eval work is to actually **apply** the emitted DDL to a Snowflake
131-
account, verify state, run the rollback, and verify the account returns to
132-
blank. Everything below is designed but not implemented.
135+
The full apply → verify → rollback → verify-blank cycle **was executed** on
136+
2026-08-25 and 2026-08-26 against `DKZPOBS-TQ14188`; the run and its 7
137+
findings are recorded in `LIVE-EVAL-RESULTS.md`. What is **not** done is
138+
codifying that cycle as a repeatable Bun test — every future eval currently
139+
requires an operator to reproduce the sequence by hand, using
140+
`LIVE-EVAL-RESULTS.md` as the recipe. The rest of this section is the design
141+
sketch for turning that into automation.
133142

134143
---
135144

‎.opencode/skills/snowflake-setup/references/audit-queries.md‎

Lines changed: 68 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -119,25 +119,35 @@ WHERE deleted_on IS NULL
119119
AND disabled = 'false'
120120
AND default_role IN ('ACCOUNTADMIN', 'SECURITYADMIN', 'ORGADMIN');
121121

122-
-- WARNING: schemas missing FUTURE grants (the #1 forgotten step)
123-
-- Compare: existing SELECT grants on tables vs FUTURE table SELECT grants
124-
WITH schemas_with_select_grants AS (
125-
SELECT DISTINCT table_catalog || '.' || table_schema AS schema_full
122+
-- WARNING: schemas missing FUTURE grants (the #1 forgotten step).
123+
-- Compare: existing SELECT grants on tables vs FUTURE-TABLE SELECT grants.
124+
-- Notes:
125+
-- - GRANTS_TO_ROLES.name holds the fully-qualified object name
126+
-- (e.g. RAW.SALESFORCE.CONTACTS for a TABLE, RAW.SALESFORCE for a SCHEMA).
127+
-- - Future table grants show up as granted_on = 'FUTURE_TABLE' with `name`
128+
-- as the parent SCHEMA's qualified name. Earlier revision of this query
129+
-- mistakenly filtered `granted_on = 'SCHEMA'` for future grants and
130+
-- compared bare SCHEMA names against database.schema, so the finding was
131+
-- always empty or wrong (PR #1164 review comment 20).
132+
WITH schemas_with_table_select_grants AS (
133+
SELECT DISTINCT
134+
-- extract database.schema from the fully-qualified table name (db.schema.table)
135+
SPLIT_PART(name, '.', 1) || '.' || SPLIT_PART(name, '.', 2) AS schema_full
126136
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
127137
WHERE granted_on = 'TABLE'
128138
AND privilege = 'SELECT'
129139
AND deleted_on IS NULL
130140
),
131-
schemas_with_future_grants AS (
141+
schemas_with_future_table_grants AS (
132142
SELECT DISTINCT name AS schema_full
133143
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
134-
WHERE granted_on = 'SCHEMA'
144+
WHERE granted_on = 'FUTURE_TABLE'
135145
AND privilege = 'SELECT'
136-
-- Future grants show up with granted_on = 'FUTURE_TABLE' internally
146+
AND deleted_on IS NULL
137147
)
138148
SELECT schema_full AS schema_missing_future_grants
139-
FROM schemas_with_select_grants
140-
WHERE schema_full NOT IN (SELECT schema_full FROM schemas_with_future_grants);
149+
FROM schemas_with_table_select_grants
150+
WHERE schema_full NOT IN (SELECT schema_full FROM schemas_with_future_table_grants);
141151

142152
-- Service accounts (users flagged as service — no email, has default warehouse)
143153
SELECT name, default_role, default_warehouse, last_success_login,
@@ -175,11 +185,49 @@ ORDER BY last_load_time DESC;
175185
-- Tasks and their state (suspended tasks are silent failures)
176186
SHOW TASKS IN ACCOUNT;
177187

178-
-- WARNING: tasks that haven't run in 7+ days despite being enabled
179-
SELECT name, database_name, schema_name, state, schedule
180-
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
181-
WHERE state = 'started'
182-
AND schedule IS NOT NULL;
188+
-- WARNING: tasks that haven't run in 7+ days despite being enabled.
189+
-- Join SHOW TASKS output (RESULT_SCAN) with ACCOUNT_USAGE.TASK_HISTORY to
190+
-- compare last successful run against the 7-day window. SHOW TASKS alone
191+
-- does not expose a last-run column, so the earlier RESULT_SCAN-only form
192+
-- of this query listed every started task with a schedule — not stale ones.
193+
WITH started_tasks AS (
194+
SELECT
195+
"name" AS task_name,
196+
"database_name" AS database_name,
197+
"schema_name" AS schema_name,
198+
"state" AS state,
199+
"schedule" AS schedule
200+
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
201+
WHERE "state" = 'started'
202+
AND "schedule" IS NOT NULL
203+
),
204+
last_run AS (
205+
SELECT
206+
database_name,
207+
schema_name,
208+
name AS task_name,
209+
MAX(completed_time) AS last_completed
210+
FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
211+
WHERE state = 'SUCCEEDED'
212+
AND completed_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
213+
GROUP BY 1, 2, 3
214+
)
215+
SELECT
216+
t.database_name,
217+
t.schema_name,
218+
t.task_name,
219+
t.state,
220+
t.schedule,
221+
lr.last_completed,
222+
DATEDIFF('day', lr.last_completed, CURRENT_TIMESTAMP()) AS days_since_last_success
223+
FROM started_tasks t
224+
LEFT JOIN last_run lr USING (database_name, schema_name, task_name)
225+
WHERE lr.last_completed IS NULL
226+
OR lr.last_completed < DATEADD('day', -7, CURRENT_TIMESTAMP());
227+
-- NOTE: ACCOUNT_USAGE.TASK_HISTORY lags up to 45 minutes; a task that
228+
-- succeeded within the last hour may not appear yet. If a task is flagged
229+
-- as stale but you expect it to have run, cross-check with the real-time
230+
-- SHOW TASKS / TASK_HISTORY_1H function first.
183231
```
184232

185233
## Section 5: Governance
@@ -304,7 +352,12 @@ WHERE deleted_on IS NULL
304352
AND (ext_authn_duo = 'false' OR ext_authn_duo IS NULL)
305353
AND email IS NOT NULL; -- human users only
306354

307-
-- Sessions from unexpected IPs (last 7 days)
355+
-- Distinct session IPs from the last 7 days — review manually against your
356+
-- expected allowlist (office, VPN, CI runners). Snowflake has no built-in
357+
-- "unexpected" flag; the audit surfaces the raw distribution and asks the
358+
-- reviewer to compare against their network policy. If you have a
359+
-- documented allowlist, add a `WHERE client_ip NOT IN (...)` filter to
360+
-- narrow this to true outliers.
308361
SELECT DISTINCT client_ip, COUNT(*) AS session_count
309362
FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS
310363
WHERE created_on >= DATEADD('day', -7, CURRENT_TIMESTAMP())

‎.opencode/skills/snowflake-setup/references/cost-governance.md‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -127,10 +127,13 @@ LIMIT 20;
127127
Storage is cheap ($23/TB/month compressed) but Fail-Safe and Time Travel add up on large tables.
128128

129129
```sql
130-
-- Find tables with long Time Travel windows (default 1 day, can be 0–90)
131-
SELECT table_schema, table_name, data_retention_time_in_days, bytes / 1e9 AS size_gb
130+
-- Find tables with long Time Travel windows (default 1 day, can be 0–90).
131+
-- Column name is `retention_time` in INFORMATION_SCHEMA.TABLES and
132+
-- SNOWFLAKE.ACCOUNT_USAGE.TABLES — not `data_retention_time_in_days` (that
133+
-- is the ALTER TABLE parameter name, not the metadata column).
134+
SELECT table_schema, table_name, retention_time, bytes / 1e9 AS size_gb
132135
FROM INFORMATION_SCHEMA.TABLES
133-
WHERE data_retention_time_in_days > 1
136+
WHERE retention_time > 1
134137
ORDER BY bytes DESC;
135138

136139
-- Reduce Time Travel for staging/raw tables (not needed for recovery)

‎.opencode/skills/snowflake-setup/references/data-vault-patterns.md‎

Lines changed: 23 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -134,12 +134,31 @@ ANALYST_ROLE (consumers)
134134
-- Note: no direct access to RAW_VAULT.* for consumers; forces mart usage
135135
```
136136

137-
**Insert-only enforcement**: revoke UPDATE and DELETE on RAW_VAULT explicitly, even if the loader role wouldn't normally have them, as a defense-in-depth measure:
137+
**Insert-only enforcement**: the RBAC block above only grants INSERT to
138+
`VAULT_LOADER_ROLE`, so the role never holds UPDATE or DELETE to revoke —
139+
which means the intuitive `REVOKE UPDATE, DELETE ... FROM ROLE VAULT_LOADER_ROLE`
140+
is either an error (Snowflake rejects revoking a privilege the role never
141+
held) or a silent no-op depending on version. It is **not** defense-in-depth.
142+
143+
For real defense-in-depth against inherited privileges from parent roles,
144+
audit the actual grant graph first with `SHOW GRANTS ON TABLE ...`, then
145+
revoke UPDATE/DELETE from any role that shows up as a holder (typically
146+
SYSADMIN or a broader transformation role, not the loader). Example:
138147

139148
```sql
140-
REVOKE UPDATE, DELETE ON ALL TABLES IN SCHEMA RAW_VAULT.HUBS FROM ROLE VAULT_LOADER_ROLE;
141-
REVOKE UPDATE, DELETE ON ALL TABLES IN SCHEMA RAW_VAULT.LINKS FROM ROLE VAULT_LOADER_ROLE;
142-
REVOKE UPDATE, DELETE ON ALL TABLES IN SCHEMA RAW_VAULT.SATELLITES FROM ROLE VAULT_LOADER_ROLE;
149+
-- Discover who currently holds UPDATE/DELETE (real answer varies by setup)
150+
SHOW GRANTS ON TABLE RAW_VAULT.SATELLITES.SAT_CUSTOMER_DETAILS;
151+
152+
-- If, for example, SYSADMIN holds UPDATE/DELETE via ownership inheritance,
153+
-- explicitly revoke from any role that could push mutations. Repeat for
154+
-- every table (or use FUTURE-grant revocation on schemas going forward).
155+
-- Do NOT blindly issue a bulk REVOKE against VAULT_LOADER_ROLE — that role
156+
-- was granted INSERT-only and there is nothing to revoke.
157+
158+
-- Better: enforce insert-only structurally via row access policies + audit,
159+
-- or use a stored procedure that owns the tables and only exposes INSERT
160+
-- via a wrapper. Grant conventions alone cannot enforce insert-only in
161+
-- Snowflake because ownership always carries all privileges.
143162
```
144163

145164
## Governance for Data Vault

0 commit comments

Comments
 (0)