brainforge-internal-reconciliation-plan

Brainforge Internal Snowflake: Reconciliation Plan

Status: Draft – pending audit completion and approval
Prerequisite: Complete brainforge-internal-snowflake-audit.md first

---

1. Summary of Audit Findings

Fill after running audit. Reference the audit document.

• Roles: Summary of gaps (missing, naming differences)
• Databases: Summary of gaps
• Warehouses: Summary of gaps
• Service users: Summary of gaps

---

2. Recommended Approach

Choose one:

• Option A: Run governance scripts as-is

  • Run infrastructure-setup.sql (creates DBs/warehouses if missing)
  • Run rbac-setup.sql (idempotent; creates roles and grants)

• Option B: Adapt scripts to existing naming

  • If internal uses different names (e.g. ROLE_DEV_READ vs role_dev_read), update scripts or add migration steps

• Option C: Incremental reconciliation

  • Create only missing databases/warehouses
  • Create only missing roles and grants
  • Leave existing objects unchanged where they already match intent

Selected approach: Fill after review

---

3. Step-by-Step Commands

3.1 Prerequisites

• ACCOUNTADMIN or SECURITYADMIN access
• Snowflake CLI configured (snow sql works)
• Backup or snapshot of current state (if required)

3.2 Single Entry Point (Recommended)

# From repo root - runs infrastructure, RBAC, default warehouse, and audit:
./knowledge/engineering/data-platform/scripts/run-reconciliation.sh
 
# Dry run (print commands without executing):
./knowledge/engineering/data-platform/scripts/run-reconciliation.sh --dry-run
 
# For client instances:
./knowledge/engineering/data-platform/scripts/run-reconciliation.sh --connection <client-connection-name>

3.3 Manual Steps (alternative)

If running steps individually:

# Step 1: Infrastructure
snow sql -c brainforge-internal -f standards/03-knowledge/engineering/setup/snowflake/infrastructure-setup.sql
 
# Step 2: RBAC (roles and grants)
snow sql -c brainforge-internal -f standards/03-knowledge/engineering/setup/snowflake/rbac-setup.sql
 
# Step 3: Set default warehouse for existing human users
snow sql -c brainforge-internal -f knowledge/engineering/data-platform/scripts/set-default-warehouse-users.sql
 
# Step 4: Role access verification
./knowledge/engineering/data-platform/scripts/audit-snowflake-internal.sh --roles-only

Service users: rbac-setup.sql creates roles and grants only. Service users (ingest, transform, report) are in create-service-users.sql. Run that after adding RSA keys (see key-pair-authentication.md).

3.4 Default Warehouse for Users

Edit set-default-warehouse-users.sql to add ALTER USER statements for each human user. Internal users (e.g. uttam) are pre-listed. For clients, add their usernames before running reconciliation.

3.5 Post-Reconciliation Verification

./knowledge/engineering/data-platform/scripts/audit-snowflake-internal.sh --roles-only

Verify all 15 roles pass. DESC USER uttam should show DEFAULT_WAREHOUSE = warehouse_developer.

3.6 Running on a Client Instance

1. Connection: Configure snow connection for the client (e.g. snow connection add or ~/.snowflake/connections.toml).
2. User list: Edit set-default-warehouse-users.sql to add client human usernames.
3. Run: ./run-reconciliation.sh --connection <client-connection-name>
4. Service users: Run create-service-users.sql separately when RSA keys are ready.

---

4. Rollback Considerations

• Databases: Dropping databases removes all data. Do not drop if data exists.
• Roles: Revoking roles from users is reversible; dropping roles may require re-granting.
• Warehouses: Dropping warehouses stops queries; recreate if needed.

---

5. Test Run Results

Completed 2026-02-13. All steps passed.

Step	Status	Notes
infrastructure-setup.sql	Pass	Created warehouse_developer; others already existed
rbac-setup.sql	Pass	Created missing roles (role_prod_marts_read, role_raw_write, role_developer, role_data_modeler, role_data_analyst, role_streamlit_creator)
set-default-warehouse-users.sql	Pass	Set uttam default_warehouse = warehouse_developer
audit —roles-only	Pass	All 15 roles passed

Fixes applied (pre-test): Replaced session variables with literals in infrastructure-setup.sql; split service user creation to create-service-users.sql; removed RSA_PUBLIC_KEY placeholder from rbac-setup.sql.

---

6. Sign-Off Checklist

• Audit document completed
• Reconciliation approach selected
• Commands reviewed
• Approval from [name]
• Reconciliation executed
• Role access tests passed