Snowflake

Premium Connector

This connector requires the Standard Plan or higher.

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 Table 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.

Table Configuration

Required fields

Field	Example	Description
schema_type	SNOWFLAKE	Specifies the connector schema type, in this case "Snowflake".
table	sample_users	The table name to sync (table only; do not include the schema prefix).
database_schema	PUBLIC	The Snowflake schema that contains the table.
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.
replication_key	updated_at	The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs. Use lowercase Singer catalog names (e.g. updated_at, not UPDATED_AT).

Optional fields

Field	Example	Description
columns	["updated_at", "product_id", "color"]	Columns to sync. If omitted, all columns are synced. When set, must include replication_key. Use lowercase catalog names.
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.
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.
batch_size	100000	The number of rows to fetch from the database in each batch, changing this can improve throughput for large tables. The default is 10000.

Table Creation Example

Below is an example of a Snowflake table connector configuration:

name: your_snowflake_dataset
schema_type: SNOWFLAKE
table: movies
database_schema: PUBLIC
user: your_user
password: pAssw0rd1!
account: snowflake-access
database: movielens
replication_key: updated_at

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

shaped create-table --file dataset.yaml