A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Snowflake Role Naming Convention

22 April 2024 Don-Rickman (0)

Snowflake Role Naming Convention 

Core Principles

Within a Snowflake account, every role, warehouse, and database name must be unique. This requires including an environment indicator (e.g., PROD, TEST, DEV) in your naming convention, especially when multiple environments exist in the same account.

1. Environment Prefix

All roles should begin with an environment prefix to clearly identify which environment they belong to:

  • PROD_ - Production
  • DEV_ - Development
  • TEST_ - Testing/QA
  • UAT_ - User Acceptance Testing

2. Role Type Categories

Following best practices, I recommend organizing roles into three main categories: Access Roles (controlling access to databases), Functional Roles (granted to human users), and Service Roles (for automated processes and services).

2.1 System Roles (Built-in)

  • Keep standard Snowflake system roles as-is:
    • ACCOUNTADMIN
    • SECURITYADMIN
    • USERADMIN
    • SYSADMIN

2.2 Access Roles

Format: <ENV>_<DATABASE>_<SCHEMA>_<ACCESS_LEVEL>

Examples:

  • PROD_EDW_MAIN_FULL - Full access to the MAIN schema in EDW database
  • DEV_ANALYTICS_FINANCE_READ - Read-only access to FINANCE schema in ANALYTICS database
  • TEST_REPORTING_SALES_WRITE - Write access to SALES schema in REPORTING database

Common access levels:

  • READ - SELECT privileges
  • WRITE - INSERT, UPDATE, DELETE privileges
  • FULL - All privileges including DDL operations
  • OWNER - Ownership privileges

2.3 Functional Roles (for Users)

Format: <ENV>_<FUNCTION>_<TEAM/DEPT>

Examples:

  • PROD_ANALYST_FINANCE - For finance analysts in production
  • DEV_ENGINEER_DATA - For data engineers in development
  • PROD_SCIENTIST_DATA - For data scientists in production
  • TEST_ADMIN_DBA - For database administrators in testing

2.4 Service Roles

Format: <ENV>_SVC_<SERVICE_NAME>_<PURPOSE>

Examples:

  • PROD_SVC_DBT_ETL - For dbt service in production performing ETL
  • DEV_SVC_AIRFLOW_LOAD - For Airflow service in development environment
  • PROD_SVC_POWERBI_READ - For PowerBI service in production with read access
  • TEST_SVC_TABLEAU_REPORTING - For Tableau service in test environment for reporting

2.5 Security Roles

Format: <ENV>_SEC_<SECURITY_FUNCTION>

Examples:

  • PROD_SEC_PII_MASK - For PII data masking in production
  • PROD_SEC_AUDIT_LOG - For audit logging in production
  • DEV_SEC_COMPLIANCE_CHECK - For compliance checking in development

2.6 Administrative Extension Roles

Format: <ENV>_<ADMIN_TYPE>_ADMIN

Examples:

  • PROD_DATA_ADMIN - For data administrators in production
  • DEV_SECURITY_ADMIN - For security administrators in development

3. Role Hierarchy

Following a structured tier-based approach helps maintain a scalable role hierarchy. This can be organized in three tiers: Tier 1 (Access Roles for specific objects), Tier 2 (Business/Functional Roles), and Tier 3 (User Roles).

The hierarchy should follow:

  1. System roles at the top (ACCOUNTADMIN, etc.)
  2. Administrative extension roles
  3. Functional/Business roles
  4. Access roles at the bottom level

This hierarchical structure ensures each role in the hierarchy inherits the privileges of its parent role while also allowing specific privileges at each level.

4. Service Accounts

For service accounts, always follow the principle of least privilege, assigning only the minimum necessary permissions needed for the service to function. Create custom roles tailored specifically to each service's needs and avoid using broad administrative roles.

5. Implementation Guidelines

  1. Documentation: Maintain thorough documentation of all roles and their purposes
  2. Automation: Script role creation and privilege assignments to ensure consistency
  3. Regular Audits: Regularly audit roles to identify dormant or underutilized roles, roles with excessive privileges, and roles that may have accumulated unnecessary access over time.
  4. Role Depth: Avoid excessively deep role hierarchies as they can impact performance and make access management complex.

6. Integration with Your Existing Naming Conventions

This proposal aligns with your current naming patterns:

  • App services: cmg-<purpose>-<env>-<region> → Snowflake service roles: <ENV>_SVC_<SERVICE_NAME>_<PURPOSE>
  • SQL Server: <purpose>-<env>-<region>-sql → Snowflake database: <PURPOSE>_<ENV>_DB
  • Streams: stream-<process>-<source table> → Snowflake stream: STREAM_<PROCESS>_<SOURCE_TABLE>
  • Azure resources: <purpose>-<environment>-<region>-<resource-type> → Snowflake resources: <ENV>_<PURPOSE>_<RESOURCE_TYPE>

7. Schema Naming Best Practices

For schemas, consider using purpose-indicating prefixes like:

  • LND: Landing schemas for newly ingested data
  • RAW: Raw staging areas
  • INT: Integration areas where data is combined and cleaned
  • MRT: Data marts with conformed data for reporting
  • WRK: Workbench schemas providing sandboxes for analysts

Would you like me to provide specific examples tailored to your organization's use cases, or would you prefer more details on any particular section of this proposal?

Tags :

Add comment