--- name: snowflake-access-requests-analytics description: Handle Snowflake access requests for analytics. --- # Handle Snowflake Access Requests for Analytics Snowflake access request in Delphi and(or) Orchard accounts. ## Purpose There are on demand requests coming from analytics to start using Snowflake. Usually these requests come from a access request form. As a result Jira ticket is created and assigned to developer. For each team we create a separate role with its own warehouses, tag and other resources. ## Notes - Don't add anything, if it is not specified in the skill. - Add new data either to the end of file or section, but not in the middle of file. - If something already exists, then don't add it, but just tell about it. - Use examples as templates, but change details according to input parameters and created resources for the team. For example, don't add new roles if they are not specified. ## Input parameters 1. Role name: name of the role to create, e.g. SME_ANALYTICS_PHILIPPINES. 1. Only users: boolean flag to determine if we need to create only users without any other resources. It is optional parameter, by default it is false, which means that we will create all needed resources. 1. Base part: Either country or team name. Quite often it is used 2-letters country code, e.g. UK. If it is not country specific, then we use team name, e.g. US_RCA. If base part is not specified and role name includes country, then we can get base part from role name: in case of country, transform it to 2-letters country code. 1. Users data: list of users to create with their details: first name, last name, email. 1. Create Jira ticket: boolean flag to determine if we need to create Jira ticket. It is optional parameter, by default it is false, which means that we will not create Jira ticket. ## Planned Workflow Areas Working directory for Delphi: `prod/snowflake/delphi/`. Working directory for Orchard: `prod/snowflake/orchard`. Use these folders according dependin on whether we ask to create resources in Delphi or Orchard account. 1. TODO 2. TODO ## 1 Create users High-level steps: (this is just description, don't do them yet, we will automate them later) - If user doesn’t yet exist, then create a ticket for DevOps (SYS project) to add her to Snowflake Active Directory (AD). DevOps ticket should be linked to current ticket, as a result we could track its status. - Specify roles and default role - Specify default warehouse with SMALL size - By default don’t assign dev_engineering, exploration_user role. - Specify team, it is the name of the tag that will be attached to user First check that user with such email doesn't exist in working users file: check by email or user key. If user exists, then don't add him, but show warning about that. **Parameters details:** - Team is the name of the tag that will be attached to user. - default_wh is the default warehouse, it should have SMALL size. - Key for user should be in format: first letter of first name + last name in upper case without spaces, e.g. NPAIREEKRENG for Prapaphan Paireekreng. - default_role, roles should be specified according to created role for the team, e.g. SME_ANALYTICS_PHILIPPINES. ### For Delphi: Append to the end of `prod/snowflake/delphi/users.tf` file user details to variable "users", add new user to the end of "users" variables. Get values from input parameters. Example - use it as template for new users, but change details according to input parameters and created role and warehouses for the team: ``` "NPAIREEKRENG" = { email = "ning.paireekreng@sonymusic.com" first_name = "Prapaphan" last_name = "Paireekreng" default_role = "SME_ANALYTICS_PHILIPPINES" default_wh = "SME_ANALYTICS_PH_SMALL_WAREHOUSE" roles = [ "SME_ANALYTICS_PHILIPPINES" ] team = "sme-analytics-philippines" }, ``` ## 2 Create a dedicated role for the team Description: Usually each team member should have 1 role, which grants all needed permissions. Firs check that role with such name doesn't exist in working roles file. If it exists, then don't add it. ### For Delphi: Append to the end of `prod/snowflake/delphi/roles/terraform.tfvars` file role details to variable "roles", add it to end of "roles" section. **Parameters details**: - Add to 'granted_account_roles': "LUMINATE_SHARE_READ" role 3 roles for warehouses - add to 'granted_database_roles': Access to shared schema: "SME_ANALYTICS.DB_SHARED_SCHEMA_READWRITE", Access to country or team specific schema - use base part logic similar to warehouses, e.g. "SME_ANALYTICS.DB_PH_SCHEMA_READWRITE" for Philippines. Example - use it as template for new role, but change details according to input parameters and created warehouses for the team: ``` { name = "SME_ANALYTICS_TAIWAN", granted_account_roles = [ "LUMINATE_SHARE_READ", # warehouses "SME_ANALYTICS_TW_SMALL_WAREHOUSE_WAREHOUSE_ACCESS", "SME_ANALYTICS_TW_MEDIUM_WAREHOUSE_WAREHOUSE_ACCESS", "SME_ANALYTICS_TW_LARGE_WAREHOUSE_WAREHOUSE_ACCESS", ] granted_database_roles = [ "SME_ANALYTICS.DB_SHARED_SCHEMA_READWRITE", "SME_ANALYTICS.DB_TW_SCHEMA_READWRITE", ] }, ``` ## 3 Create dedicated warehouses for the team Description: - Create 3 warehouses: small, medium and large. - Warehouse name should include size and team it belongs to. In case of countries, we use 2-letters code, e.g. US, UK, BR. - Specify team, it is the name of the tag that will be attached to warehouse Users should have access **only** to their team warehouses, as a result we could track costs appropriately. First check that warehouses with such name don't exist in working warehouses file. If it exists, then don't add it. ### For Delphi: Append to the end of `prod/snowflake/delphi/warehouses/warehouses.tf` file warehouse details, add it to the end of file. Warehouse name format: SME_ANALYTICS_{country_or_basepart}_{size}_WAREHOUSE. It should include size and team it belongs to. In case of countries, we use 2-letters code, e.g. US, UK, BR. If it is not country specific, then use base part from input arguments. Team is the name of the tag that will be attached to warehouse. Example - use it as template for new warehouses, but change details according to input parameters: ``` # SME Poland { name = "SME_ANALYTICS_PL_SMALL_WAREHOUSE" size = "SMALL" auto_resume = true auto_suspend = 60 comment = "SME Poland (PL) Analytics Warehouse - Small" max_cluster_count = 1 statement_timeout_in_seconds = 3600 team = "sme-analytics-poland" }, { name = "SME_ANALYTICS_PL_MEDIUM_WAREHOUSE" size = "MEDIUM" auto_resume = true auto_suspend = 60 comment = "SME Poland (PL) Analytics Warehouse - Medium" max_cluster_count = 1 statement_timeout_in_seconds = 3600 team = "sme-analytics-poland" }, { name = "SME_ANALYTICS_PL_LARGE_WAREHOUSE" size = "LARGE" auto_resume = true auto_suspend = 60 comment = "SME Poland (PL) Analytics Warehouse - Large" max_cluster_count = 1 statement_timeout_in_seconds = 3600 team = "sme-analytics-poland" }, ``` ## 4 Create tags ### For Delphi: Tags are defined in `prod/snowflake/delphi/tags/main.tf` file. Look at `snowflake_tag` resource. If our tag doesn't exist, then create a new one. Create in the end of file. Tag name: lower case role name and replace _ to -. ## 5 Create schema for the role Create schema in SME_ANALYTICS database and give permissions for the role to access its own schema and SHARED schema. ## For Delphi: Working file is `prod/snowflake/delphi/databases/sme-analytics/variables.tf` file. Add it to the end of "schemas" variable. First check that schema with such name doesn't exist in working schemas file. If it exists, then don't add it. Example - use it as template for new schema, but change details according to input parameters: ``` { name = "PH", is_managed = false, create_roles = true }, ``` ## Create Jira ticket for DevOps (SYS project) to add users to Snowflake Active Directory (AD) Do it only, if Create Jira ticket is true. Do this step in the end, after you created all other resources. Do it only if you created new user. Do it if Jira MCP is installed, otherwise say that it is missing. **Steps:** 1. Clone Jira ticket: https://theorchard.atlassian.net/browse/SYS-28457 1. Set title: "Add users to Snowflake AD" 1. Remove Assignee 1. Set description to the following template: ``` Please, add following users to Snowflake {account} AD: {emails} ``` Specify emails for all new users that you created. Add each email in new line. For {account} specify either Delphi or Orchard depending on where you created users. 1. Provide Jira ticket link.