Snowflake

Connecting Snowflake & Facet

We need a few key pieces of information to connect Facet to your tables in Snowflake.

  • Snowflake account URL, e.g. <ACCOUNT_NAME>.snowflakecomputing.com
  • Table Information
    • Database
    • Schema
    • Table name
  • Username
  • Password
  • User Role
  • Warehouse

Though you can use an existing warehouse and user, we recommend that you set up a new warehouse, user role, and user for use exclusively with Facet. This guide details the steps to create those entities in the Snowflake web console. Each step includes the equivalent SQL commands, if you'd prefer to handle the creation via script.

Snowflake Account and Table Information

Facet will be reading data from already existing tables which your company is maintaining. The first step is to document the host of the Snowflake account. The host takes the form of <account_name>.snowflakecomputing.com. The next set of items to document are the Database, Schema, and Table names we’ll be connecting into. For getting started, you will only need the name of 1 table.

From the Snowflake Web Console, select Databases and then click the name of the database you are interested in. We’ll be clicking on the SSB database.

From here, you have all the information you need. Collect the name of the Table Name and the Schema. These will be used in later steps.

Creating a Warehouse

As you create your first workspace, you will be specifying the compute warehouse to be used for the queries. We suggest creating a new warehouse specifically for use with the Facet workspace, though if you prefer, you can use a previously created warehouse. You can read through Snowflake documentation about warehouses to help you get started if you are new to Snowflake. Even new Snowflake users can follow the steps below to get started.

To create a warehouse, click on Warehouses -> Create.

Next you will see the Create Warehouse modal, and you will need to choose Name and Size. You can leave the rest of the items as the default, or you can modify them to best fit your needs based on your dataset, and number of concurrent users. For getting started, we suggest starting with a “Medium” with a maximum cluster size of 2. For optimizing the performance, we suggest referring to this document here.

CREATE WAREHOUSE FACET_WAREHOUSE WITH WAREHOUSE_SIZE = 'MEDIUM' 
  WAREHOUSE_TYPE = 'STANDARD' 
  AUTO_SUSPEND = 600 
  AUTO_RESUME = TRUE 
  MIN_CLUSTER_COUNT = 1 
  MAX_CLUSTER_COUNT = 2 
  SCALING_POLICY = 'STANDARD' 
  COMMENT = 'Warehouse to be used with Facet workspaces';

Creating a Role

When creating a new Role, it is best to run these commands from a worksheet rather than creating a role with the “Create Role” dialog in the UI. When using the Snowflake Web Console to create the role, there are additional steps you’ll need to take from the worksheets, so it’s best to do it all at once.

This role will be attached to the user you’ve created for Facet and it gives just enough permissions for reading data from your database. We recommend not reusing this role for other operations.

  1. Click on the tab for Worksheets
  2. Select YOUR_DATABASE under database objects
  3. Change role to ACCOUNTADMIN
  4. Create a new role using the following command:
CREATE ROLE "FACET_READ_ONLY"
  1. Grant access to the virtual warehouse:
GRANT USAGE ON WAREHOUSE "FACET_WAREHOUSE" TO ROLE "FACET_READ_ONLY";
  1. Grant access to the database:
GRANT USAGE ON DATABASE "YOUR_DATABASE" TO ROLE "FACET_READ_ONLY";
  1. Grant access to the schema
GRANT USAGE ON SCHEMA "YOUR_DATABASE"."YOUR_SCHEMA" TO ROLE "FACET_READ_ONLY";
  1. Grant access to the table. You can explicitly grant access to tables, or grant access to all tables in the schema, or you can grant access to all future tables in the schema.
GRANT SELECT ON TABLE "YOUR_DATABASE"."YOUR_SCHEMA"."YOUR_TABLE" to ROLE "FACET_READ_ONLY";

Or grant access to all existing tables in the schema

GRANT SELECT ON ALL TABLES ON SCHEMA YOUR_DATABASE.YOUR_SCHEMA to role FACET_READ_ONLY;
GRANT SELECT ON ALL TABLES IN DATABASE "YOUR_DATABASE" to ROLE "FACET_READ_ONLY";

Lastly, If you want to ensure access to all future tables in the schema, run the following commands.

GRANT SELECT ON FUTURE TABLES IN SCHEMA YOUR_DATABASE.YOUR_SCHEMA to role FACET_READ_ONLY;
GRANT SELECT ON FUTURE TABLES IN DATABASE YOUR_DATABASE to role FACET_READ_ONLY;

Creating a User

For connecting Facet to your Snowflake instance, we recommend creating a new user with access only to the tables which you would like to be visualized in a Facet workspace. To create a new user, click on AccountUsersCreate

From the Create User modal, the first screen is General , you will need to create a User Name, a password, and to check the box for “Force Password Change”. Adding a comment is optional. Click Next

The next screen is Advanced in order to give more detailed information about the user. This is purely optional. You can either fill these out, or directly click Next

The last screen is Preferences where you specify default information for Warehouse, Namespace and Role. The only required option is the Role, and this needs to be set as the same Role you created in the previous step. Click Finish.

CREATE USER FACET PASSWORD = '********' 
    COMMENT = 'User for Facet' 
  DEFAULT_ROLE = "FACET_READ_ONLY";
GRANT ROLE "FACET_READ_ONLY" TO USER FACET;

Testing the User

Before creating your Facet workspace, you should check inside of Snowflake that the user you just created has the appropriate permissions and can make queries into the tables you will need.

First, log out of your standard user, and log back into Snowflake using the credentials you have just created. Next, go the Worksheets tab in order to run a test query. Before running a query, you should be able to see the Database information on the left hand side of the screen. Find the database you’ve granted access to and click the name of the database. Next click the name of the Schema. You should now be able to see a list of tables. You can run a test query by right clicking the name of the table, and pressing Preview Data.

Now, from the Worksheets section on the right, choose the Role, Warehouse, Database and Table names from the dropdown. You should run a single SQL command to ensure that explicitly with the information provided you can query the table. An example command is SELECT COUNT(*) FROM “TABLE”. This can be any SQL command of your choice.

Next Steps

Now you are ready to proceed to app.facetdata.com and create your first workspace!


Did this page help you?