You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit ff58e0a
Browse filesBrowse the repository at this point in the historyBrowse files
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]>
Copy file name to clipboardExpand all lines: .opencode/skills/snowflake-setup/SKILL.md
+6-5Lines changed: 6 additions & 5 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -68,13 +68,13 @@ Ask all blocking questions **in one batch** — do not proceed until answered.
68
68
69
69
**Blocking:**
70
70
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).
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.
78
78
79
79
**Optional (defaults available):**
80
80
@@ -161,9 +161,9 @@ Produce a single markdown plan with the following sections, in this order. For e
161
161
162
162
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`.
163
163
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).
165
165
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.
@@ -340,6 +340,7 @@ Regardless of mode or format, always generate a companion rollback SQL file per
340
340
- Never drops built-in roles or the `SNOWFLAKE` database
341
341
- SUSPEND tasks and UNSET policies/tags/monitors before dropping
342
342
- 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.
343
344
344
345
### 8. Execute per the User's Choice
345
346
@@ -398,7 +399,7 @@ Ask these upfront, before reading any references or generating any DDL. Present
398
399
399
400
Based on the answer to triage Q1, ask the appropriate detailed questions:
400
401
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)
402
403
-**`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.
Copy file name to clipboardExpand all lines: .opencode/skills/snowflake-setup/references/data-vault-patterns.md
+23-4Lines changed: 23 additions & 4 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -134,12 +134,31 @@ ANALYST_ROLE (consumers)
134
134
-- Note: no direct access to RAW_VAULT.* for consumers; forces mart usage
135
135
```
136
136
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:
138
147
139
148
```sql
140
-
REVOKEUPDATE, DELETEON ALL TABLES IN SCHEMA RAW_VAULT.HUBSFROM ROLE VAULT_LOADER_ROLE;
141
-
REVOKEUPDATE, DELETEON ALL TABLES IN SCHEMA RAW_VAULT.LINKSFROM ROLE VAULT_LOADER_ROLE;
142
-
REVOKEUPDATE, DELETEON ALL TABLES IN SCHEMA RAW_VAULT.SATELLITESFROM 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.
0 commit comments