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
Field | Example | Description |
---|---|---|
schema_type | SNOWFLAKE | Specifies the connector schema type, in this case "Snowflake". |
table | analytics.events | The name of the table to sync, prefixed with the schema name if applicable. |
user | your_user | Access account username. |
password | pAssw0rd1! | Access account password. |
account | snowflake-access | Your Snowflake account identifier. See Account Identifiers. |
database | movielens | The 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
Field | Example | Description |
---|---|---|
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_schema | public | The name of the schema that contains table to sync. |
role | read_only_role | The name of the role to use when connecting to the database. |
warehouse | your_warehouse | The 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