2026-02-03_snowflake-actual-vs-sop

CTA Snowflake: Actual State vs. Governance SOP

Date: February 3, 2026
Source: Snowflake CLI (snow sql -c cta) verification
SOP reference: 2026-02-03_snowflake-governance-dbt-setup.md

This document compares what exists in the CTA Snowflake account today with what the governance SOP describes. Use it to plan changes or align the SOP to current naming.

---

1. Roles

Exists today (from INFORMATION_SCHEMA.APPLICABLE_ROLES)

Role	Notes
ACCOUNTADMIN	System
CI_CD_ROLE	Custom (CI/CD)
OKTA_PROVISIONER	SSO
ORGADMIN	System
PUBLIC	System
ROLE_DEVELOPER	Custom: dev_write + staging_write + prod_read (per comment in SHOW ROLES)
ROLE_DEV_READ	Custom
ROLE_DEV_WRITE	Custom
ROLE_PROD_READ	Custom
ROLE_PROD_WRITE	Custom
ROLE_STAGING_READ	Custom (equivalent to PREP_READ in SOP)
ROLE_STAGING_WRITE	Custom (equivalent to PREP_WRITE in SOP)
SECURITYADMIN	System
SNOWFLAKE_ANALYSTS	System
SNOWFLAKE_LEARNING_ROLE	System
SYSADMIN	System
USERADMIN	System

SOP expects (names only)

Role	In CTA?	Gap
DEV_READ	Yes, as ROLE_DEV_READ	Naming: CTA uses ROLE_ prefix
DEV_WRITE	Yes, as ROLE_DEV_WRITE	Same
PREP_READ	Yes, as ROLE_STAGING_READ	Naming: CTA uses STAGING not PREP
PREP_WRITE	Yes, as ROLE_STAGING_WRITE	Same
PROD_READ	Yes, as ROLE_PROD_READ	Same
PROD_WRITE	Yes, as ROLE_PROD_WRITE	Same
ANALYST	No	Aggregate read-only role not present
DATA_ENGINEER	Yes, as ROLE_DEVELOPER	Naming only
DBT_SERVICE	No	Service role for GitHub Actions not present
ETL_SERVICE	No	Service role for loaders not present

Summary – roles: Environment read/write roles exist with a ROLE_ prefix and STAGING instead of PREP. ANALYST, DBT_SERVICE, and ETL_SERVICE do not exist. ROLE_DEVELOPER is the developer aggregate role.

---

2. Warehouses

Verified

• Current session warehouse: WAREHOUSE_TRANSFORM (confirmed via CURRENT_WAREHOUSE()).
• SHOW WAREHOUSES and SHOW WAREHOUSES IN ACCOUNT returned results but the Snowflake CLI rendered the table in a wrapped format, so a full list of warehouse names/sizes was not captured in this run. At least WAREHOUSE_TRANSFORM exists and is in use.

SOP expects

• DEV_XS, PREP_S, PROD_M, PROD_L, LOADING_XS (with sizes and auto-suspend as in the SOP).

Summary – warehouses: At least one warehouse exists (WAREHOUSE_TRANSFORM). Whether the SOP’s named set (DEV_XS, PREP_S, PROD_M, etc.) exists was not fully verified; an account admin can run SHOW WAREHOUSES IN ACCOUNT in Snowflake UI or with higher privileges to confirm.

---

3. Databases

Exists today (from SHOW DATABASES IN ACCOUNT)

Names inferred from the truncated output (column “name” in the SHOW output):

Database	Likely purpose
AMS_DATA_SHARE_V2	Shared data
ARCHIVED_MARKETING_DATA	Archive
ASNSTF_REMEMBERSAWS_AMS_DATA_SHARE	REMEMBERS / AMS share
BF_SANDBOX	Sandbox
CI_CD_DB_INTERMEDIATE	CI/CD intermediate layer
CI_CD_DB_MARTS	CI/CD marts
CI_CD_DB_STAGING	CI/CD staging
DEFAULT_DBT_DB	Default dbt database
DEV_INTERMEDIATE	Development intermediate (current DB in session)
DEV_MARTS	Development marts
STG_INTERMEDIATE	Staging intermediate
STG_MARTS	Staging marts
STG_STAGING	Staging staging layer
SNOWFLAKE_SAMPLE_DATA	Sample data
TEMP_LEARNING_DB	Learning/temp
USER$UTTAM@BRAINFORGE.AI	User-specific DB
WEBHOOKS	Webhooks
STREAMLINE_APPS	Apps
… (others in full SHOW output)

SOP expects

• RAW – raw/source data
• PREP (or staging) – staging environment
• PROD – production

CTA uses DEV_* and STG_* and CI_CD_DB_* naming instead of RAW/PREP/PROD. No database named RAW, PREP, or PROD was seen in the excerpt.

Summary – databases: CTA uses DEV_, STG_, and CI_CD_DB_* databases. The SOP’s RAW / PREP / PROD naming is not in use; either the SOP should be updated to match CTA’s naming or new databases should be created to match the SOP.

---

4. Current session (connection “cta”)

• Warehouse: WAREHOUSE_TRANSFORM
• Role: ROLE_DEVELOPER
• Database: DEV_INTERMEDIATE

So the “cta” Snowflake CLI connection is using a developer role and dev intermediate database, consistent with development use.

---

5. Recommendations

1. Roles

   • Either adopt the SOP’s role names (e.g. DEV_READ, PREP_READ) and migrate, or update the SOP to document CTA’s current names (ROLE_DEV_READ, ROLE_STAGING_READ, etc.) and add the missing ANALYST, DBT_SERVICE, and ETL_SERVICE (or their CTA equivalents).
   • Add DBT_SERVICE (or equivalent) and ETL_SERVICE (or equivalent) if you use GitHub Actions and separate loaders.

2. Databases

   • Align the SOP with CTA’s database naming (DEV_, STG_, CI_CD_DB_*) or create RAW/PREP/PROD and migration plan.
   • Clarify in the SOP which database is “production” (e.g. STG_* vs PROD).

3. Warehouses

   • Have an account admin run SHOW WAREHOUSES IN ACCOUNT (or equivalent) and list all warehouse names and sizes.
   • Map existing warehouses (e.g. WAREHOUSE_TRANSFORM) to SOP names (DEV_XS, PREP_S, PROD_M, etc.) or update the SOP to match current names and sizes.

4. Naming

   • CTA uses ROLE_ prefix and STAGING instead of PREP. The SOP can be updated to a “CTA variant” section that documents this.

---

6. How this was verified

• CLI: snow sql -c cta
• Queries used:
  • SHOW ROLES;
  • SELECT DISTINCT ROLE_NAME FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY ROLE_NAME;
  • SHOW WAREHOUSES; / SHOW WAREHOUSES IN ACCOUNT;
  • SHOW DATABASES IN ACCOUNT;
  • SELECT CURRENT_WAREHOUSE(), CURRENT_ROLE(), CURRENT_DATABASE();
• Limitation: Current user has ROLE_DEVELOPER; warehouse and database listing may be partial if role cannot see all account objects. An ACCOUNTADMIN run would give a complete picture.