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 :
CREATE TABLE departments(dep_id varchar, dep_name varchar);
INSERT INTO departments VALUES
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:
I completely forgot about this one until I was looking at my repo today and noticed a gap for week 30. Definitely the most I’ve ever used snowSQL as I’m far more a fan of Snowpark for Python, but a good challenge all the same!