Week 30 – Intermediate

This week, we’ll be looking into a challenge that will make any salesperson salivate :

The instant “database-in-a-box” or a general-use template:
We want to be able to use SnowSQL to point it towards this script on the 1 end, and the target snowflake instance on the other, and create an instant useable database.

Create a Primary Script (in a format of your choice), that creates the following if executed :
– 4 databases, named: Development, Testing, Acceptance, and Production
– 1 additional schema in every database (besides Public) called security
– 1 additional schema in Development called ‘dev_user’
– 3 users: security_user, dev_user, regular_user

The default values for the users :
password = abc123
secondary roles should be enabled

– 2 additional roles called dev_role and security_role
– dev_role should be able to create and query all (future) tables, views, and schemas in every database, except for changing anything in the security schemas.
– security_role should be able to create and query all (future) tables, views, and schemas in every database.
– dev_user should have the dev_role , security_user should have the security_role
– the role public is only allowed to query Production.Public
– 1 SMALL warehouse called default_wh

Create a secondary script that has the following :

The database-in-a-box should call on this Secondary Script if you point it to the right path (argument).
The path to the Secondary Script should NOT be hardcoded in the Primary Script

BONUS POINTS :

As a bonus challenge, try to create the same script that is more malleable ;
The default values like passwords, warehouse names, and database names are currently static but, can you create the option to defer to the default if no alternative is given?

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge