Setting up Snowflake — the exact grant statements we run

[slloyd]

– Snowflake
grant all on database RAW to role LOADER;
grant create on database ANALYTICS to role TRANSFORMER;

Hi, the statement grant create on database ANALYTICS to role TRANSFORMER; fails for me with the following error:

SQL compilation error: error line 1 at position 6 Invalid privilege CREATE.

Should it read grant create schema on database ANALYTICS...?

[claire]

Thank you! Just updated!

[slloyd]

– Snowflake
create role LOADER;
grant all on warehouse LOADING to role TRANSFORMER;
create role TRANSFORMER;
grant all on warehouse TRANSFORMING to role TRANSFORMER;

Hi, I don’t have the ability to send a direct message yet, but I noticed the first grant should be to
...role LOADER rather than ...role TRANSFORMER.

Thanks so much for posting the use case article on snowflake and this walkthrough. It really helped me a lot.

[dazlari]

Hi Claire,
is there anything about this architecture that needs mentioning specifically for testing updates to models?
For example, would I have a test version of the ANALYTICS database with a test Warehouse running only during testing? or is there something I’m missing. Testing doesn’t get a mention in the use case either so I’m guessing it’s done in dbt some other way.

[claire]

Hey there @dazlari. There’s no extra steps required for running the dbt test command. Have you added any tests to your project yet / run them? The workflow might make a little more sense if you do that

[dazlari]

Not that far yet, but I have prepared our environment in the last few days based on this approach. Up until now we’ve been running the lot (data+views) out of one DB and one Warehouse but this structure is very appealing. I’m still getting some initial ‘off the shelf’ dbt modules together and my head around prepping our own, but the thought of deploying some test views crept up and I couldn’t see where they fit in. I’ll keep reading - thanks!

[dazlari]

Hi Clair, I’ve done more research on this and found the following in the docs which I think is still relevant to this topic:

We recommend using different schemas within one data warehouse to separate your environments.
…
If you have multiple dbt users writing code, it often makes sense for each user to have their own development environment. A pattern we’ve found useful is to set your dev target schema to be dbt_<username> .

This is more like what I was expecting to find, but I didn’t use the term ‘environment’ in my original question. Not sure if you wanted to bridge this concept in this article.

[dazlari]

I thought I should add the following in answering my own questions. As I’m relatively new here and have struggled to find a few of the the signposts (its a mix between docs and discourse!). I started with docs, found the above, and eventually moved on to the following which I would now consider required reading:

How we structure our dbt projects

This next post ( based on another users question ), gives further insight into how to operate this environment.

What are the dbt commands you run in your production deployment of dbt?

I’m sure there’s a lot more out there, but you’ve written a lot of great stuff Claire!!

[namdnguyen]

Thanks for sharing, @claire! This is incredibly helpful.

Looks like there is one typo in step 6, line 6 of the Snowflake code block. Should it be “tables” instead of “table”?

@claire

grant select on all table in database analytics to role reporter;

[claire]

Thanks, all fixed!

[gclunies]

Hi @claire!

I’ve been using this guide to setup Snowflake to be used with dbt-learn. By far the best resource for this, thanks for putting this together!

I followed all of the steps listed in your guide above (and am following the dbt-learn videos), but when it came to dbt run-ing I was getting the following error.

I ended up having to explicitly grant all on database analytics to role transformer; in order to get things working.

I thought that…

Since transformer is creating all the relations within the analytics database, that role will have all privileges on these relation automatically.

… this wouldn’t be necessary.

Any thoughts on why I had to run this additional step?

[claire]

Hmm, did your transformer role create the analytics database, or did a different role?

[gclunies]

Hmmm… I think this is the culprit. But still a bit confused…

Step 0 is creating the databases, and Step 1 is creating the roles. So I don’t see how transformer could have created analytics (since the role does not exist yet). Seems I’m not understanding something here.

I ran Step 0 in Snowflake itself under the ACCOUNTADMIN role so you’re definitely right that analytics was not created by transformer. Maybe this is part of the issue? Admittedly… this Snowflake setup was/is entirely new to me so it mainly came down trial-and-error.

Should steps 0 and 1 be reversed? Still not clear to me how you would run Step 0 and explicitly state to create analytics with the transformer role.

[leoebfolsom]

Thank you @claire ! As a novice to warehouse setup, I found this incredibly helpful (same goes for the other related/linked articles you and @jerco have written). Even on my one-person team, I now have users for both superuser leo and regular leo!

I have a question about steps 6 and 7 (I’ll only go through step 6).

In step 6, you write:

@claire

If you do this before you have any data loaded, you can run:

grant usage on database raw to role transformer;
grant usage on future schemas in database raw to role transformer;
grant select on future tables in database raw to role transformer;
grant select on future views in database raw to role transformer;

If you already have data loaded in the raw database, make sure also you run the following to update the permissions

grant usage on all schemas in database raw to role transformer;
grant select on all tables in database raw to role transformer;
grant select on all views in database raw to role 

Because I completed the fundamentals course, my raw database already exists and contains data.

So, when I read:

If you do this before you have any data loaded, you can run:

I assumed that I would not run the subsequent four lines of code, because I have data loaded.

But then, when I read the next part:

If you already have data loaded in the raw database, make sure also you run the following to update the permissions

I thought … huh? What do you mean, “make sure you also run”? Was I supposed to have run some of the previous lines?

I had assumed that

grant usage on all schemas in database raw to role transformer;

would be the first line of code I’d run in step 6, because I skipped the previous bit (as instructed, since I already have data loaded).

Exactly which lines of code should I run if I already have data loaded in my raw database?

What I’ve done is the following, a mishmash of the instructions in step 6:

grant usage on database raw to role transformer;
grant usage on all schemas in database raw to role transformer;
grant select on all tables in database raw to role transformer;
grant select on all views in database raw to role transformer;

When I try to run any of the future statements in step 6 (or in step 7), I receive this error:

SQL access control error: Insufficient privileges to operate on database 'RAW'

I understand that the future statements are important:

Everything else should be inherited automatically thanks to those future grants.

And I’m assuming I should run them even if I already have data loaded into my raw database … right?

Apologies for how verbose this is, but I hope it’s clear what I’m confused about! Please redirect me to Slack or somewhere else if there’s a better place for such questions.

Thank you again for the excellent post.

[leoebfolsom]

@claire

auto_resume = false

Hi! This auto_resume line in the create warehouse loading command caused issues for me when trying to set up a Fivetran connection. The Fivetran error message specifically indicated that auto_resume had to be set to true:

"Default Warehouse Test: You must specify AUTO_RESUME = TRUE for LOADING"

Once I updated the auto_resume property (alter warehouse loading set auto_resume=true;), the connection was successful!

I’m curious if auto_resume has to be false when using Fivetran, or if it has to do with my specific setup.

Thanks!

Leo

[gomezn]

Has anyone implemented the model where you create object and functional roles and grant roles to other roles? Can you share pros/cons?

Exploring RBAC (roles) in Snowflake & Snowflake Inspector - Hashmap Megabytes - Ep 14

[eugene.kim]

I know it’s been awhile and you’ve probably found a workaround but just wanted to add my thoughts since I ran into this as well.

The “grant *** on all” statements give you privileges to existing objects so if you have them, you need to run them.

The “grant *** on future” statements give you privileges on all future objects so that also needs to be run. The order shouldn’t matter.

What does matter though, is that when running the future grants, you need to use the role securityadmin. This is because only securityadmin and accountadmin have that privilege.

Read more here:

Snowflake Community

[Sara]

Hey @claire! I just wanted to double-check the grants in section 7 for an existing analytics database. Should the last 2 lines grant to the role ‘reporter’ rather than ‘transformer’, in line with the rest of the section?

grant usage on all schemas in database analytics to role reporter;
grant select on all tables in database analytics to role reporter;
grant select on all views in database analytics to role reporter;

Thanks!

[leoebfolsom]

Thanks Eugene!! I don’t have time to dig into this right now, but I’ll return to your comment next time I’m setting up Snowflake. Much appreciated.

[Jung]

Eeguh bo myun jeon hwa jo… nae ga jeong mal jeong mal mee an hae