Skip to main content

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

Required fields

FieldExampleDescription
schema_typeSNOWFLAKESpecifies the connector schema type, in this case "Snowflake".
tableanalytics.eventsThe name of the table to sync, prefixed with the schema name if applicable.
useryour_userAccess account username.
passwordpAssw0rd1!Access account password.
accountsnowflake-accessYour Snowflake account identifier. See Account Identifiers.
databasemovielensThe name of the database that contains table to sync.

database. | replication_key | updated_at | The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs. Note if your column name is uppercase, you should convert to lowercase. | unique_keys | ["productId"] | Specify a list of columns that uniquely identify a row in the table, if duplicate rows are inserted with these keys, the latest row will be used.

Optional fields

FieldExampleDescription
columns["productId", "color", "brand", "stockLevel"]The name of the columns you wish to sync from Snowflake into Shaped. If not specified, all columns will be synced. Note if your column name(s) are uppercase, you should convert to lowercase.
database_schemapublicThe name of the schema that contains table to sync.
roleread_only_roleThe name of the role to use when connecting to the database.
warehouseyour_warehouseThe name of the warehouse to use when connecting to the database.

Dataset Creation Example

Below is an example of a Snowflake dataset connector configuration:

name: your_snowflake_dataset
schema_type: SNOWFLAKE
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