Snowflake

Preparation

To allow Shaped to connect to your Snowflake database, you need to create a read-only user and share those credentials through the Create Dataset endpoint. You can create a read-only user on a Snowflake database instance with the following commands:

-- 1. Create a role that will be used to grant access to the specific table.
CREATE ROLE read_only_role;

-- 2. Create a user and grant the read-only role to the user.
CREATE USER read_only_user
  PASSWORD = '<password>'
  DEFAULT_ROLE = read_only_role
  MUST_CHANGE_PASSWORD = FALSE;

-- 3. Grant the newly created role to the user.
GRANT ROLE read_only_role TO USER read_only_user;

-- 4. Grant the SELECT permission on the specific table to the read-only role.
GRANT SELECT ON TABLE <database_name>.<schema_name>.<table_name> TO ROLE read_only_role;

-- 5. Grant USAGE permission on the schema and database containing the table.
GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO ROLE read_only_role;
GRANT USAGE ON DATABASE <database_name> TO ROLE read_only_role;

Replace password, database_name, schema_name, and table_name with the appropriate values for your Snowflake account.

Dataset Configuration

Below are the fields required for the Snowflake dataset connector:

Field	Example	Description
schema_type	SNOWFLAKE	Specifies the connector schema type, in this case "Snowflake".
config.table	events	The name of the table to sync.
config.user	your_user	Access account username.
config.password	pAssw0rd1!	Access account password.
config.account	snowflake-access	Your Snowflake account identifier. See Account Identifiers.
config.database	movielens	The name of the database that contains table to sync.
config.database_schema	public	Optional. The name of the schema that contains table to sync.
config.role	read_only_role	Optional. The name of the role to use when connecting to the database.
config.warehouse	your_warehouse	Optional. The name of the warehouse to use when connecting to the database.
config.replication_key	updated_at	The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs.

Dataset Creation Example

Below is an example of a Snowflake dataset connector configuration:

dataset_name: your_snowflake_dataset
schema_type: SNOWFLAKE
config:
    table: movies
    user: your_user
    password: pAssw0rd1!
    account: snowflake-access
    database: movielens
    replication_key: updated_at

The following payload will create a Snowflake dataset and begin syncing data from Shaped using the Shaped CLI.

shaped create-dataset --file dataset.yaml