Castled empowers you to utilize your Snowflake’s comprehensive customer data (Customer 360) to enhance engagement across various channels, including Email, SMS, Push Notifications, and In-app Notifications. This integration allows for a more targeted and effective communication strategy with your customers.

Permission Details

Snowflake’s permission model is intricate, offering multiple configurations for access control. The script provided outlines the essential permissions for Castled to operate effectively, adhering to Snowflake’s recommended practices for establishing a read-only role within a role hierarchy.

  1. Create a dedicated role CASTLED_ROLE.
  2. Create a dedicated user CASTLED.
  3. Give READ ONLY access to CASTLED user to enable access to all the existing as well as future tables, views and functions of the required schemas in your database.
  4. Create a book keeping schema CASTLED with in your database and grant complete access to the CASTLED user.
-----------------------------------ROLE CREATION STARTS---------------------------------------------------------
-- Create a role for the CASTLED user
    CREATE ROLE CASTLED_ROLE;
-----------------------------------ROLE CREATION ENDS-----------------------------------------------------------


-----------------------------------USER CREATON STARTS----------------------------------------------------------
-- Create the CASTLED user
    CREATE USER CASTLED WITH DEFAULT_ROLE = CASTLED_ROLE PASSWORD = '<STRONG_PASSWORD>';

-- Grant the role CASTLED_ROLE to the CASTLED user
    GRANT ROLE CASTLED_ROLE TO USER CASTLED;
-----------------------------------USER CREATON ENDS------------------------------------------------------------


----IF YOU DECIDE YOU USE A WAREHOUSE OTHER THAN COMPUTE_WH, CREATE YOUR WAREHOUSE AS BELOW
-----------------------------------WAREHOUSE CREATON STARTS----------------------------------------------------------
CREATE WAREHOUSE IF NOT EXISTS "<your_warehouse>" warehouse_size = xsmall warehouse_type = standard auto_suspend = 60 auto_resume = true initially_suspended = true;
 -----------------------------------WAREHOUSE CREATON ENDS------------------------------------------------------------


-----------------------------------GRANT WAREHOUSE ACCESS STARTS------------------------------------------
-- Grant Usage on the warehouse to CASTLED_ROLE. This will allow the CASTLED user to run all the required queries in this warehouse.
-- Mention your warehouse name, if you are using COMPUETE_WH mention COMPUTE_WH
 GRANT USAGE ON WAREHOUSE "<your_warehouse>" TO ROLE CASTLED_ROLE;

-- Grant OPERATE privilege on the warehouse to CASTLED_ROLE. This is required to start/stop the warehouse and also to abort any running queries in the warehouse.
-- Mention your warehouse name, if you are using COMPUETE_WH mention COMPUTE_WH
 GRANT OPERATE ON WAREHOUSE "<your_warehouse>" TO ROLE CASTLED_ROLE;
-----------------------------------GRANT WAREHOUSE ACCESS ENDS------------------------------------------

-- Allow CASTLED_ROLE to access your database
GRANT USAGE ON DATABASE "<your_database>" TO ROLE CASTLED_ROLE;

-- Allow CASTLED_ROLE to access your schema
-- GRANT access to all the schemas used in the query, if the query spans multiple schemas
GRANT USAGE ON SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;

-- Grant READ ONLY access to CASTLED_ROLE for all the existing tables in your schema
GRANT SELECT ON ALL TABLES IN SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;

-- Grant READ ONLY access to CASTLED_ROLE for all the future tables created in your schema
GRANT SELECT ON FUTURE TABLES IN SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;

-- Grant READ ONLY access to CASTLED_ROLE for all the existing views in your schema
GRANT SELECT ON ALL VIEWS IN SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;

-- Grant READ ONLY access to CASTLED_ROLE for all the future views created in your schema
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;

-- Grant READ ONLY access to CASTLED_ROLE for all the existing functions in your schema
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;

-- Grant READ ONLY access to CASTLED_ROLE for all the future functions created in your schema
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "<your_database>"."<your_schema>" TO ROLE CASTLED_ROLE;
-----------------------------------GRANT TABLE READ ONLY ACCESS ENDS--------------------------------------------


-----------------------------------CREATE AND GRANT BOOK KEEPING ACCESS STARTS----------------------------------
-- Create a book keeping schema castled to store sync state
 CREATE SCHEMA "<your_database>"."castled";

-- Give the CASTLED_ROLE role full access to the book keeping schema
  GRANT ALL PRIVILEGES ON SCHEMA "<your_database>"."castled" TO ROLE CASTLED_ROLE;
-----------------------------------CREATE AND GRANT BOOK KEEPING ACCESS ENDS-------------------------------------

Connector Details

To set up a new connector for Snowflake, it is essential to gather the following details. Note that all fields are Case Sensitive in Snowflake.

  • Name Assign a unique name to identify the configured warehouse.
  • Account Name: The account name of your configured warehouse. For the classic Snowflake interface, this is the initial part of your Snowflake URL, e.g., accountname from https://accountname.snowflakecomputing.com. For the newer Snowflake interface, the account name is derived by combining segments from the URL, for example account name would be xrhiaft-oi86934 for https://app.snowflake.com/xrhiaft/oi86934.
  • Warehouse Name: Specify the compute warehouse to be used. Default compute warehouse is COMPUTE_WH.
  • Database Name: Indicate the Snowflake database where your data is stored.
  • Schema Name: Mention the specific Snowflake schema within the database that houses your data.
  • Database User: Username for database access.
  • Database Password: Password for database access.