When you connect Snowflake, Simplesat copies your responses into a table in the database and schema you choose. The table stays current as new responses come in and existing ones change, and it covers every survey in your workspace.

Before you start

The Snowflake integration is available on every Simplesat plan. To connect it and switch it on, you need the Manage integrations permission, which Owner and Admin have by default.

In Snowflake, set up a user for Simplesat with:

A key pair, which it connects with instead of a password.
A default role with the privileges below.
A default warehouse.
A default namespace set to the database and schema the table goes in.

The role needs USAGE on the warehouse, the database and the schema, and CREATE TABLE on the schema. If the table already exists and a different role created it, the role also needs SELECT, INSERT and UPDATE on it.

If the default namespace is a different database or schema from the one in the integration's settings, each new or changed response makes your warehouse copy all your responses into the table again, instead of updating one row.

You'll also need OpenSSL on your computer to create the key pair.

Integration security, permissions and data flow covers what a connected tool can read and write.

Connect Snowflake

The examples below use simplesat for the user, SIMPLESAT_ROLE for the role, REPORTING_WH for the warehouse and ANALYTICS.FEEDBACK for the database and schema. Use your own names.

In a terminal, create the key pair:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

rsa_key.p8 is the private key and rsa_key.pub is the public key. The private key is left unencrypted because there's nowhere to enter a passphrase when you connect.

In Snowflake, as a role that owns the user, give the user the public key. Use the key's text without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines, and without line breaks:

ALTER USER simplesat SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...'

Set the user's default role, warehouse and namespace:

ALTER USER simplesat SET DEFAULT_ROLE = 'SIMPLESAT_ROLE' DEFAULT_WAREHOUSE = 'REPORTING_WH' DEFAULT_NAMESPACE = 'ANALYTICS.FEEDBACK'

Find the key's fingerprint: run DESC USER simplesat and note the value of RSA_PUBLIC_KEY_FP, including the SHA256: at the start. To get your account identifier, run this and note the result:

SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME()

Then, in Simplesat:

On the Integrations page, open Snowflake and click Authenticate.
Fill in Public key and Private key with the two keys, each without its first and last lines. Fill in Fingerprint with the key's fingerprint, Username with the user's name and Account identifier with the account identifier. Click Authenticate.
When the page shows Authenticated with Snowflake, click Activate integration on Sync feedback data to Snowflake.
Fill in its settings and click Save:

Setting	What to enter
Database	The database the table goes in
Schema	The schema the table goes in
Table name	The table's name. The default is `simplesat responses`. If no table has that name, Simplesat creates it.

Heads up: The Snowflake page also lists Sync feedback data to Snowflake (polling). It's meant to add the past day's new responses once a day, but currently every run fails and it adds nothing, so leave it off.

What lands in Snowflake and when

The table

Each response goes to Snowflake at least five minutes after it comes in. The first response after you activate the integration creates the table in the Database and Schema from your settings. All the existing responses in your workspace, from every survey, are then copied in, which takes a while if you have a lot of history.

If you change Table name to a name that doesn't exist yet, the next response creates that table and copies all your responses into it.

When rows are added and updated

Each response has one row, matched on id. A new response adds a row. When a response changes, its row is updated, for example when the customer answers another question later or a rating is edited.

Only active responses go to Snowflake. Withheld, hidden and spam responses stay out.

Tagging a response in Simplesat doesn't update its row. The tag reaches Snowflake the next time the response itself changes.

What each row holds

Column	Type	What it holds
`id`	BIGINT	The response ID
`survey`	VARIANT	The survey's ID and name
`tags`	ARRAY	The customer's or the response's tags, depending on the row (see below)
`created`, `modified`	TIMESTAMP_NTZ	When the response was created and last changed, in UTC
`ip_address`	VARCHAR	The respondent's IP address
`ticket`	VARIANT	The ticket's ID in Simplesat and in your helpdesk, its subject and its ticket fields
`team_members`	VARIANT	Always empty
`customer`	VARIANT	The customer's IDs, name, email, company, tags and attributes
`answers`	VARIANT	Answers, each with the question, the rating or choice, the comment, the sentiment, and the follow-up question and answer. Which answers depends on the row (see below)
`source`	VARCHAR	Where the response came from

Rows differ in which answers and tags they hold:

Rows copied in when the table is created hold every answer, and the customer's tags rather than the response's.
Rows written after that, for new and changed responses, hold only one answer, and the response's tags.

Replace the key or change the user

A public key set with ALTER USER ... SET RSA_PUBLIC_KEY doesn't expire. If it's removed or replaced, or the user is disabled, though, every sync fails while the page still shows Authenticated with Snowflake.

A user can hold two public keys, so you can add the new key before you remove the old one. Create a new key pair, then give the user the new public key in its second slot:

ALTER USER simplesat SET RSA_PUBLIC_KEY_2='MIIBIjANBgkqh...'

Run DESC USER simplesat and note the value of RSA_PUBLIC_KEY_2_FP, including the SHA256: at the start. Then, in Simplesat:

On the Snowflake page, click Revoke access.
Click Authenticate and enter the new private key, new public key and new fingerprint, along with the user's name and the account identifier.
Click Activate integration on Sync feedback data to Snowflake. Reconnecting leaves it off, with its settings in place, until you do. Responses that arrive while it's off aren't added later, so do these three steps in one go.

Once it's active again, remove the old key:

ALTER USER simplesat UNSET RSA_PUBLIC_KEY

To connect a different user or Snowflake account, set up its key and defaults as in Connect Snowflake, then follow the Simplesat steps above with its details.

Troubleshooting

Each new response makes my warehouse copy every response again

This happens when the user's default namespace is a different database or schema from the one in the integration's settings. Run DESC USER simplesat and check DEFAULT_NAMESPACE. Set it to match your settings:

ALTER USER simplesat SET DEFAULT_NAMESPACE = 'ANALYTICS.FEEDBACK'

Nothing has reached my Snowflake table since a certain date

Start with the user. If its public key was removed or replaced, or the user was disabled, every sync fails while the page still shows Authenticated with Snowflake. Reconnect with a working key pair: click Revoke access, then Authenticate, then Activate integration.

If that's not it, check that the user's default warehouse and default role still exist, and that the role still has its privileges. If none of that explains it, contact us.

Responses that arrived while syncs were failing aren't added later.

The table hasn't appeared in my schema

The table appears with the first response after you activate the integration, at least five minutes after that response comes in. If a response has come in since then, check that:

Sync feedback data to Snowflake is the active integration, not the polling one.
Database, Schema and Table name are filled in and saved.
The user has a default warehouse and a default role, and the role has USAGE on the warehouse, database and schema, and CREATE TABLE on the schema.

My Snowflake rows are missing answers or team members

Rows copied in when the table is created hold every answer. Rows written after that, for new and changed responses, hold only one answer. team_members is always empty.

The polling integration hasn't added anything to my table

Sync feedback data to Snowflake (polling) currently fails on every run. Turn it off and use Sync feedback data to Snowflake.

FAQ

Can I connect with a password instead of a key pair?

No. Simplesat connects to Snowflake only with a key pair.

Does the integration create the database, schema or table?

It creates the table if it doesn't exist. The database, schema and warehouse have to exist already.

How do I read the VARIANT columns?

Put a colon between the column name and the element, and use dots for anything below that. For example, customer:email returns the customer's email, and survey:name returns the survey's name. Add ::VARCHAR to get the value without quotes. Element names are case-sensitive.

If you kept the default table name, it has a space, so put it in double quotes exactly as written: "simplesat responses".

Which responses go into the table when it's created?

All the existing responses in your workspace, from every survey. After that, new and changed responses are added or updated as they come in.

Snowflake integrations