Brainforge Knowledge

brainforge-internal-snowflake-audit

4 min read

Brainforge Internal Snowflake: Actual State vs. Governance Scripts

Date: 2026-02-13
Source: Snowflake CLI (snow sql -c brainforge-internal), key-pair auth
Governance reference: snowflake

This document compares what exists in the Brainforge Internal Snowflake account with what the governance scripts (infrastructure-setup.sql, rbac-setup.sql) expect. Use it to plan reconciliation.

Scripts we ran (internal cleanup): internal-cleanup-2026-02 – folder containing the drop-users and set-default-warehouse scripts run for the Feb 2026 user/role cleanup, plus a README with run order and CLI examples.

1. Roles

Exists today (from SHOW ROLES)

RoleNotes
ACCOUNTADMINSystem
SYSADMINSystem
SECURITYADMINSystem
USERADMINSystem
ORGADMINSystem
PUBLICSystem
ROLE_DEV_READAccess role
ROLE_DEV_WRITEAccess role
ROLE_STAGING_READAccess role
ROLE_STAGING_WRITEAccess role
ROLE_PROD_READAccess role
ROLE_PROD_WRITEAccess role
ROLE_INGESTService functional
ROLE_TRANSFORMService functional
ROLE_REPORTService functional
ROLE_DEVELOPERHuman aggregate (dev + staging + prod read)
ROLE_ABC_DEVELOPERABC-specific
RILL_SERVICE_ROLERill
ABC_SERVICE_USER_READER_ROLEABC
AUTOFILLMENT_EXECUTORSystem
SNOWFLAKE_LEARNING_ROLESystem

Governance expects

RoleIn Internal?Gap
role_dev_readYesPass (use works)
role_dev_writeYesPass
role_staging_readYesPass
role_staging_writeYesPass
role_prod_readYesPass
role_prod_writeYesPass
role_prod_marts_readNoMissing – role access test FAIL
role_raw_writeNoMissing – role access test FAIL
role_ingestYesPass
role_transformYesPass
role_reportYesPass
role_developerNoMissing – role access test FAIL
role_data_modelerNoMissing – role access test FAIL
role_data_analystNoMissing – role access test FAIL
role_streamlit_creatorNoMissing – role access test FAIL

Summary – roles: After reconciliation (2026-02-13), all 15 governance roles exist and pass access tests.

2. Warehouses

Exists today (from SHOW WAREHOUSES IN ACCOUNT)

Session used WAREHOUSE_TRANSFORM; full list was truncated in CLI output. At least WAREHOUSE_TRANSFORM exists and is in use.

Governance expects

WarehousePurpose
warehouse_ingestETL loads
warehouse_transformdbt jobs (scheduled / CI)
warehouse_reportBI queries
warehouse_developerDefault for all human users (ad-hoc, Snowsight, local dbt)

Summary – warehouses: WAREHOUSE_TRANSFORM confirmed. Verify warehouse_ingest, warehouse_report, and warehouse_developer via Snowsight (Admin → Warehouses). All human users should use warehouse_developer as default.

3. Databases

Exists today (from SHOW DATABASES IN ACCOUNT)

DatabasePurpose
RAWLanding pad; includes SOURCE_DB, SLACK_DB, SEC, DLT as schemas
DEV_INTERMEDIATEDev intermediate
DEV_MARTSDev marts
STG_INTERMEDIATEStaging intermediate
STG_MARTSStaging marts
PROD_INTERMEDIATEProd intermediate
PROD_MARTSProd marts; includes BRAINFORGE_RILL, BRAINFORGE_RILL_MANUFACTURING, BRAINFORGE_RILL_SALES
SOURCE_DB, SECStill exist (cloned to RAW); cannot drop while in outbound shares
ABC_LOGS, AI_AGENT_TRACKING, etc.Other

Governance expects

DatabasePurpose
rawLanding pad for extracted data
DEV_intermediateDevelopment intermediate layer
STG_intermediateStaging intermediate layer
PROD_intermediateProduction intermediate layer
DEV_martsDevelopment marts
STG_martsStaging marts
PROD_martsProduction marts

Summary – databases: All seven governance databases exist (raw, DEV/STG/PROD intermediate and marts). Naming matches.

4. Service Users

Exists today (from SHOW USERS)

Output truncated in CLI. Verify in Snowsight (Admin → Users) for service_user_ingest, service_user_transform, service_user_report.

Governance expects

UserDefault RolePurpose
service_user_ingestrole_ingestETL
service_user_transformrole_transformdbt
service_user_reportrole_reportBI tools

Summary – service users: Not fully captured in this run; confirm in UI.

5. Current Session (at audit time)

  • Account: MY64480
  • User: UTTAM
  • Warehouse: WAREHOUSE_TRANSFORM
  • Role: ACCOUNTADMIN
  • Database: None

6. Users and roles

Source: snowflake.account_usage.users and snowflake.account_usage.grants_to_users (role grants to users).
Note: grants_to_users can lag; run SHOW GRANTS TO USER <name> for live state before applying changes.

6.1 Policy (internal)

  • Account admins (ACCOUNTADMIN): UTTAM, SAMROBERTS, AWAISHKUMAR, RICOREJOSO. Default warehouse = warehouse_developer; default role stays ACCOUNTADMIN.
  • Everyone else (human): CASIEAVILES, DEMILADEAGBOOLA, MUSTAFARAJA, AMBERSIRULIN. Default warehouse = warehouse_developer; default role = role_developer (grant role_developer, set default_role and default_warehouse).
  • Dropped users (removed): HENRYZHAO, MIGUELDEVEYRA, PATRIKDEVLIN, BRIANGONZALES, EXAMPLE_USER, RILL_SERVICE_USER. Run drop-users-internal.sql to remove them. If any own objects, transfer ownership first then re-run.
  • Service users: SERVICE_USER_INGEST, SERVICE_USER_REPORT, ABC_SERVICE_USER_READER, INTERNAL_SERVICE_USER_READER (and SERVICE_USER_TRANSFORM if present) keep their existing role/warehouse; no change in this pass. PRANAVNARAHARI remains for now (no role/warehouse change unless you add one later).

6.2 Current state (after applying scripts)

UserDefault warehouseDefault roleNotes
UTTAMwarehouse_developerACCOUNTADMINAccount admin
SAMROBERTSwarehouse_developerACCOUNTADMINAccount admin
AWAISHKUMARwarehouse_developerACCOUNTADMINAccount admin
RICOREJOSOwarehouse_developerACCOUNTADMINAccount admin
CASIEAVILESwarehouse_developerrole_developerHuman
DEMILADEAGBOOLAwarehouse_developerrole_developerHuman
MUSTAFARAJAwarehouse_developerrole_developerHuman
AMBERSIRULINwarehouse_developerrole_developerHuman
SERVICE_USER_INGESTwarehouse_ingestrole_ingestService
SERVICE_USER_REPORTwarehouse_reportrole_report or ROLE_PROD_READService
ABC_SERVICE_USER_READER(as needed)role_prod_marts_readService
INTERNAL_SERVICE_USER_READERwarehouse_transformROLE_PROD_WRITEService (consider revoking ACCOUNTADMIN separately)
PRANAVNARAHARI——Unchanged for now
SNOWFLAKE——System

6.3 Scripts to apply

  1. drop-users-internal.sql
    Drops: henryzhao, migueldeveyra, patrikdevlin, briangonzales, example_user, rill_service_user. If a user owns objects, transfer ownership to an admin (e.g. uttam) or SYSADMIN first, then re-run.

  2. set-default-warehouse-users.sql

    • Account admins (uttam, samroberts, awaishkumar, ricorejoso): ALTER USER ... SET DEFAULT_WAREHOUSE = warehouse_developer only.
    • Everyone else (casieaviles, demiladeagboola, mustafaraja, ambersirulin): GRANT ROLE role_developer TO USER ... then ALTER USER ... SET DEFAULT_WAREHOUSE = warehouse_developer and SET DEFAULT_ROLE = role_developer.

  3. Order
    Run drop-users-internal.sql first, then set-default-warehouse-users.sql.

  4. Verification
    SHOW GRANTS TO USER <name> and confirm default_warehouse / default_role in Snowsight or snowflake.account_usage.users.

7. Recommendations (post-reconciliation)

  1. Roles: Done via reconciliation; all 15 roles exist.
  2. Databases: Governance DBs present. SOURCE_DB, SLACK_DB, SEC, DLT content cloned into RAW; BRAINFORGE_RILL cloned into PROD_MARTS. SLACK_DB, DLT, BRAINFORGE_RILL dropped.
  3. Warehouses: warehouse_developer, warehouse_ingest, warehouse_transform, warehouse_report in use.
  4. Service users: Create via create-service-users.sql when RSA keys are ready.

8. Suggested cleanups (optional)

ItemAction
SOURCE_DB, SECStill exist (cloned to RAW). Blocked: FEMA_SHARE is primary in a replication relationship. See README-drop-source-db-sec.md. Resolve replication, then run drop-source-db-sec-internal.sql.
MFA authentication policyDone (2026-03-02): enforce-mfa-internal.sql created PROD_intermediate.SECURITY.mfa_required, applied to account. Service users get mfa_optional.
Rill accessDocumented: Rill uses service_user_report with role_report per governance.md. role_report has role_prod_marts_read → PROD_MARTS.BRAINFORGE_RILL*.
ABC_LOGS, AI_AGENT_TRACKINGDecide: keep as standalone DBs or move into RAW as schemas for consistency.
Legacy rolesDropped via drop-non-governance-roles.sql: RILL_SERVICE_ROLE, ABC_SERVICE_USER_READER_ROLE, ROLE_ABC_DEVELOPER, ROLE_DEVELOPER, SNOWFLAKE_LEARNING_ROLE. Affected users granted role_prod_marts_read or role_developer.
Dropped usersdrop-users-internal.sql removes: HENRYZHAO, MIGUELDEVEYRA, PATRIKDEVLIN, BRIANGONZALES, EXAMPLE_USER, RILL_SERVICE_USER. Account admins: UTTAM, SAMROBERTS, AWAISHKUMAR, RICOREJOSO; everyone else (human): warehouse_developer + role_developer via set-default-warehouse-users.sql.
Audit docRe-run audit-snowflake-internal.sh --audit-only periodically and refresh “Exists today” tables.

9. How this was verified

  • CLI: snow sql -c brainforge-internal or snow sql with env vars
  • Script: knowledge/engineering/data-platform/scripts/audit-snowflake-internal.sh
  • Queries used:
    • SHOW ROLES;
    • SHOW WAREHOUSES IN ACCOUNT;
    • SHOW DATABASES IN ACCOUNT;
    • SHOW USERS;
    • SELECT CURRENT_WAREHOUSE(), CURRENT_ROLE(), CURRENT_DATABASE();
  • Limitation: Run as ACCOUNTADMIN or SECURITYADMIN for complete visibility.

Graph View

Backlinks