11 Nov 19 · thendreks · #Snowflake ·   Bookmark   ×

Best Practices for Schema / Roles / Grants on Snowflake

Does anyone have a list of best practices for creating initial schemas, databases, roles, permissions etc in Snowflake? Thanks!

thendreks

posted on 11 Nov 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds




I had worked for several fortune 100 clients for over a decade and here's a simple pattern i have seen across

Creating a database / schema:

- If you are a small / medium sized company, Use one VPC and have the database as environment and one schema for each mart

- If you are a large organization you can have separate VPC for Dev, IT, QV and Prod. And have a database for each of your marts. Under the database, you will have different schemas, one for _DATA, one for _STAGE and one for _VIEWS


Naming databases / schemas / db objects:


Its easier to get carried away by standards. Remember, you are building the data warehouse for your internal users and instead of obsessing over standards, try to simplify things and use names that are easier and resonate with your end users.


- Use simple names and don't stuff too many dead characters (because you know standards)

- Keep view names the same as table names with a _V suffix

- For views with business logic, name them with _BV suffix

- Avoid long names for your objects. Try to be creative and name your objects with less than 30 characters. I had bad experience with tools like SAS crashing with names over 30 characters


Setting up Roles:


- 1 Developer role - who has access to do development work in DEV, Read access in IT & QV

- 1 QAS / Tester role - who has read access in IT and QV

- 1 Support Role - Who has Read / write access in DEV / IT / QV and Read access to Production

- N Line of business (LOB) Roles - Each role has access to its own data marts

This is what i recommend


Database Naming Conventions:

Retail_DB_Acquisition

Retail_DB_Reporting

Retail_DB_EDW

Retail_DB_Analytics

Service Accounts Naming Conventions:

SVC_APPNAME_ENVIROMENT

SVC_TABLEAU_QA

SVC_TALEND_PROD

ROLES:

RETAIL_ROLE_READ

RETAIL_ROLE_OPERATE

RETAIL_ROLE_OWNER

Warehouse

RETAIL_WH_ANALYTICS_XS

RETAIL_WH_ETL_XS

RETAIL_WH_TABLEAU_XS

RETAIL_WH_ANALYTICS_M

RETAIL_WH_ETL_M

RETAIL_WH_TABLEAU_M

TABLES

RETAIL.CUSTOMERS_STAGING

S3 STAGE:

department_name_bucket_name

RETAIL_S3_DEV_OPS  ---> S3:\DEV-OPS


Thanks for sharing this @roshankp.

Your naming standard is very intuitive and so simple to follow. This is something i always insist. User experience takes precedence over everything. Make sure your users can easily understand and relate to the names

daanalytics

I found the following from Hashmap Inc. very informative:

https://medium.com/hashmapinc/heres-your-day-1-and-2-checklist-for-snowflake-adoption-e0e7ff8f105a

Appreciate the link. Looks like an interesting read. Have bookmarked


Community Software by Hittly
Copied