Does anyone have a list of best practices for creating initial schemas, databases, roles, permissions etc in Snowflake? Thanks!
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 - 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 Retail_DB_Acquisition Retail_DB_Reporting Retail_DB_EDW Retail_DB_Analytics SVC_APPNAME_ENVIROMENT SVC_TABLEAU_QA SVC_TALEND_PROD RETAIL_ROLE_READ RETAIL_ROLE_OPERATE RETAIL_ROLE_OWNER RETAIL_WH_ANALYTICS_XS RETAIL_WH_ETL_XS RETAIL_WH_TABLEAU_XS RETAIL_WH_ANALYTICS_M RETAIL_WH_ETL_M RETAIL_WH_TABLEAU_M RETAIL.CUSTOMERS_STAGING department_name_bucket_name RETAIL_S3_DEV_OPS ---> S3:\DEV-OPS |
Post Comment