BettrData Customer Portal

Search

Home

Release Notes

User Docs

Customer Portal
Customer Portal
/
📖
User Docs
/User Guide
User Guide
/BD Select
BD Select
/
Setting Up Permissions for Snowflake BettrData Import and Select User for Snowflake

Setting Up Permissions for Snowflake BettrData Import and Select User for Snowflake

In order to output data from Bettrdata to Snowflake, you will need a user that has the correct permissions to push the data. This includes,

  • Usage of the intended database, schema, and warehouse
  • Ability to select, delete, insert, or update rows on the intended table (s)
    • If just a audience builder user this can be just select, but include future views and tables for database/schema that this will use
    • Make sure to manually grant the views and tables. They do not appear to do it automatically
  • Ability to create a stage, write, and read on the target database.schema.
    • This is for the user to push data to Snowflake. It is not needed for the BD Select user
    • The stage that used by BettrData is “bd_load_stage”

Warning

All service users MUST be set to type “LEGACY_SERVICE”. See below for example

Example

In this example, we are creating a role and user to both push data from Bettrdata to Snowflake and from Snowflake to bettrdata. For clients that own their own Snowflake and have the experience with Snowflake, alter this as needed for either/or/both the import and selection of data with Bettrdata

Provide credential and login details to Bettrdata if Bettrdata cannot create

CREATE DATABSE DEMO;
CREATE SCHEMA STAGING;
CREATE SCHEMA PROD;

USE SCHEMA STAGING;

-- BASE ROLE
CREATE ROLE DEMO_BD_ROLE;

-- WAREHOUSES
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DEMO_BD_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH_MED_DEMO TO ROLE DEMO_BD_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH_XLARGE_DEMO TO ROLE DEMO_BD_ROLE;

-- DATABASE SCHEMA USAGE
GRANT USAGE ON DATABASE DEMO TO ROLE DEMO_BD_ROLE;
GRANT USAGE ON ALL SCHEMAS IN DATABASE DEMO TO ROLE DEMO_BD_ROLE;

-- CREATE SELECT, DELETES, INSERT, UPDATE ON TABLES
GRANT CREATE TABLE ON SCHEMA DEMO.STAGING TO ROLE DEMO_BD_ROLE;
GRANT SELECT,DELETE,INSERT,UPDATE  ON ALL TABLES IN DATABASE DEMO TO ROLE DEMO_BD_ROLE;
GRANT SELECT,DELETE,INSERT,UPDATE  ON FUTURE TABLES IN DATABASE DEMO TO ROLE DEMO_BD_ROLE;

-- SELECT ON VIEWS
GRANT SELECT ON ALL VIEWS IN SCHEMA DEMO.STAGING TO ROLE DEMO_BD_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA DEMO.PROD TO ROLE DEMO_BD_ROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE DEMO TO ROLE DEMO_BD_ROLE;

-- STAGES
CREATE STAGE IF NOT EXISTS DEMO.STAGING.bd_load_stage;
GRANT CREATE STAGE ON SCHEMA DEMO.STAGING TO ROLE DEMO_BD_ROLE;
GRANT READ ON STAGE bd_load_stage TO ROLE DEMO_BD_ROLE;
GRANT WRITE ON STAGE bd_load_stage TO ROLE DEMO_BD_ROLE;

-- LET ACCOUNT ADMIN HAVE IT
GRANT ROLE DEMO_BD_ROLE TO ROLE ACCOUNTADMIN;

-- CREATE USER DEMO 

CREATE USER DEMO_BD_USER
	PASSWORD             = 'XXXXXXXX'
	LOGIN_NAME           = 'DEMO_BD_USER'
	DISPLAY_NAME         = 'DEMO_BD_USER'
	EMAIL                = 'someone@client.com'
	MUST_CHANGE_PASSWORD = FALSE
	DEFAULT_ROLE         = DEMO_READ
	DEFAULT_WAREHOUSE = COMPUTE_WH_MED_DEMO
	DEFAULT_NAMESPACE = PUBLIC
	TYPE = LEGACY_SERVICE
  ;

-- FOR TOP LEVEL ROLE
GRANT ROLE DEMO_BD_ROLE TO USER DEMO_BD_USER;