Snowflake

Use this page to let agents in Refactor read data in your Snowflake account. Agents can run queries. They cannot change anything.

Setup needs two people, who may be the same person:

• In Refactor: someone who will manage the data source. Any member can create one.
• In Snowflake: an admin who can create users and roles and grant access to the data. One recommended step needs an account administrator.

What gets created#

Everything is created in your Snowflake account, by your admin, and you stay in control of it.

• A role that can only read. It is granted the databases, schemas, tables and views you choose, and nothing else. What Refactor can see is exactly what you grant this role.
• A service user that holds that role. A service user is Snowflake's kind of user for applications: it has no password and cannot sign in to Snowflake's website.
• A key pair, made by Refactor. Refactor keeps the private key, encrypted, and shows you only the public key. You register the public key on the service user. Nobody at your organization or at Refactor ever sees, copies or sends a private key.

No Snowflake key, token or password is ever placed in a workspace. Each query goes from Refactor's own servers to your Snowflake account over HTTPS.

One data source is one Snowflake account, one role and one warehouse. To give different work different data, create more than one data source, each with its own role.

Before you start#

Decide three things with your Snowflake admin:

You also need your account identifier, in the form organization-account, for example acme-analytics. Your Snowflake admin can find it in the account's details in Snowflake. You can also paste the address of your Snowflake account from a browser, and Refactor reads the identifier from it.

Set it up#

1. Start the data source in Refactor#

1. Open Data sources in the sidebar and click New data source.
2. Choose Snowflake.
3. Enter a name, your account identifier, and the names you will use in Snowflake for the service user, the role and the warehouse. Use names that need no double quotes in Snowflake: letters, digits and underscores. Under the fields, Refactor says where it will connect and as whom.
4. If the role can read health data, tick This data source contains health data. See Health data.
5. Click Create.

The data source opens as Waiting for setup. Refactor has created the key pair, and under Set up in Snowflake the page shows the commands below with your names and the public key already filled in, along with the key's fingerprint. Each block has a Copy commands button.

2. Create a role that can only read#

Your Snowflake admin runs these, as SECURITYADMIN or another role that can create roles and grant access to this data. Repeat the middle block for each database and schema.

CREATE ROLE IF NOT EXISTS REFACTOR_READER;

GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE REFACTOR_READER;

GRANT USAGE ON DATABASE <database> TO ROLE REFACTOR_READER;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE REFACTOR_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA <database>.<schema> TO ROLE REFACTOR_READER;
GRANT SELECT ON ALL VIEWS  IN SCHEMA <database>.<schema> TO ROLE REFACTOR_READER;

To include tables and views created later, add:

GRANT SELECT ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE REFACTOR_READER;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA <database>.<schema> TO ROLE REFACTOR_READER;

Grant this role nothing else: no stored procedures, no functions, no other roles.

3. Create the service user#

Run as USERADMIN or higher. The command on the data source's page already contains the public key.

CREATE USER IF NOT EXISTS REFACTOR_SVC
  TYPE = SERVICE
  COMMENT = 'Refactor: read-only queries';

ALTER USER REFACTOR_SVC SET
  RSA_PUBLIC_KEY = '<public key from Refactor>'
  DEFAULT_ROLE = REFACTOR_READER
  DEFAULT_WAREHOUSE = <warehouse>
  DEFAULT_SECONDARY_ROLES = ();

GRANT ROLE REFACTOR_READER TO USER REFACTOR_SVC;

If the user already exists, CREATE USER IF NOT EXISTS changes nothing, not even the key. The ALTER USER line is why the user ends up the same either way: it sets the key and the defaults every time. It replaces whatever key was in RSA_PUBLIC_KEY, so anything else that signs in as this user with that key stops working. Use a user that only Refactor signs in as.

DEFAULT_SECONDARY_ROLES = () matters. Without it, Snowflake lets a user act with every role it holds at once, not only the one named here.

4. Turn off exports for this user (recommended)#

Run as ACCOUNTADMIN.

ALTER USER REFACTOR_SVC SET PREVENT_UNLOAD_TO_INLINE_URL = TRUE;
ALTER USER REFACTOR_SVC SET PREVENT_UNLOAD_TO_INTERNAL_STAGES = TRUE;

In Snowflake, a user that can read a table can also export what it reads to a file, unless these two settings are on. Refactor refuses every export command itself before it reaches Snowflake; these settings make Snowflake refuse them too. The data source works without this step. After a test, its page shows Export settings in Snowflake as On, Off or Could not be read, as information only.

5. Check it in Snowflake#

DESCRIBE USER REFACTOR_SVC;
SHOW GRANTS TO ROLE REFACTOR_READER;
SHOW GRANTS TO ROLE PUBLIC;

• RSA_PUBLIC_KEY_FP should be exactly the fingerprint Refactor shows.
• The role's grants should list only what you meant to share.
• Every role in Snowflake can also do whatever PUBLIC can, so look at what PUBLIC holds in your account.

6. Test the connection#

Back in Refactor, click Test connection on the data source's page. The test signs in as the service user and runs three small checks, so it can start a suspended warehouse. On success it says which user, role and warehouse it connected as, and the data source shows Ready.

Limit time and cost in Snowflake (optional)#

Refactor stops each query after a set time. These settings add limits on your side that Refactor cannot raise.

-- A warehouse only Refactor uses, so its limits affect nothing else.
CREATE WAREHOUSE IF NOT EXISTS REFACTOR_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

-- No query on this warehouse runs longer than five minutes.
ALTER WAREHOUSE REFACTOR_WH SET STATEMENT_TIMEOUT_IN_SECONDS = 300;

-- A monthly spending limit. Run as ACCOUNTADMIN.
CREATE RESOURCE MONITOR REFACTOR_LIMIT WITH CREDIT_QUOTA = <credits> FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE REFACTOR_WH SET RESOURCE_MONITOR = REFACTOR_LIMIT;

A suspended warehouse starts when a query needs it, so a query from an agent can start yours.

If the test fails#

If your Snowflake account restricts where connections come from, the test fails at the first check. See Not supported yet.

Attach it and use it#

A Snowflake data source is shared, renamed, archived and restored like any other. See Data sources. Archiving keeps the key, so a restored data source connects again.

Once Test connection has passed, attach it from the data source's page with Attach, like any other data source. See Data sources.

• Choose a workspace, or a service so that every engagement of the service gets it.
• It is always attached Read only. There is no folder or write option to choose.
• Name in the drive is the short name the agent uses for it, for example claims.
• You need to manage the data source and be able to edit the workspace or service.
• Detach takes it away from the agent at its next query.
• A data source marked as containing health data attaches only where network access is Added sites only. See Health data.

In a workspace with the data source attached, ask the agent a question about the data. It lists the tables it can see, writes a query, runs it and works with the result.

A result larger than the inline limit comes back as its first rows, marked as cut. When the whole result is needed, the agent saves it as a file in the workspace, Parquet by default or CSV, up to the file limit under Limits. The file is copied from Snowflake through your organization's own storage bucket, which keeps the copy only for the length of the download.

If the person who attached it loses access to the data source or leaves the organization, the attachment shows Not working with the reason, and the agent's next query is refused with the same reason. Someone who manages the data source can use Re-attach as me.

How the agent reaches it#

Nothing about your Snowflake account is placed in the workspace: no address, no key, no password and no driver. The agent runs one command with the query. The command asks Refactor's own servers, which check the query, send it to your account as the service user, and return the result. This works the same at every network access setting, because the workspace never connects to Snowflake itself.

Browse it in the console#

Anyone who can read the data source can see what its role can see, on the data source's page under Browse:

1. Click Browse tables. The databases the role can see are listed.
2. Choose a database, then a schema, then a table or view. Each list is read from Snowflake when you choose the level above it.
3. The table's columns are shown with their type, whether a value may be empty, and their comment.
4. Click Preview rows to see the first rows, at most the data source's preview limit (100 unless its managers changed it). Values are shown exactly as Snowflake returned them; NULL and an empty value are shown differently.

The address of the page names the table you chose, so you can send a link that opens the same table.

• Each filter box narrows the list you have. When a list is longer than one answer holds, type in the filter and press Enter to search Snowflake for the rest.
• Listing databases, schemas, tables and columns does not start your warehouse: it reads Snowflake's own catalogue. A preview is a query and runs on the warehouse.
• Every list and preview is a statement in the data source's history, with your name.
• If the data source cannot connect, Browse says why instead of showing an empty list.
• A data source marked as containing health data shows its structure but no rows. See Health data.

See it in a workspace#

In a workspace, open Data sources in the left bar. It lists every data source the workspace's agent can reach: those attached to the workspace, and those attached to its service, marked through the service. Each shows its status:

• Ready: the agent can query it.
• Needs setup: its setup in Snowflake is not finished, or Refactor needs a new key. Someone who manages it finishes it on its page.
• Stopped, with the reason: for example, the person who attached it no longer has access, or it holds health data and the workspace's network access was opened. The agent's queries are refused until it is fixed.

Click a data source to open it in its own tab:

• Connection: its status, account, service user, role and warehouse. A data source marked as containing health data lists its rules here.
• Browse: the same databases, schemas, tables, views and columns as on its page, and Preview rows where previews are allowed. A data source marked as containing health data shows its structure but no rows.
• Recent queries in this workspace: the newest statements run on it from this workspace, newest first: the query, who ran it or which agent, when, how many rows, and how it ended. Only people who manage the data source see these, as on its History page. Full history opens that page narrowed to this workspace.

Open its page goes to the data source's own page. From the list, + attaches a data source you manage to this workspace, and the ✕ on a row detaches it, for the same people who can do so from the data source's page. You see nothing in the workspace that you could not see on the data source's page: one you cannot read is listed only as A data source you cannot open.

What agents can and cannot do#

Agents can:

• Run a SELECT query, including one that starts with WITH.
• List databases, schemas, tables, views and columns (SHOW, DESCRIBE, or a query on information_schema).
• Ask Snowflake how it would run a query (EXPLAIN).
• Wait for a query that takes a while, and cancel one. A query that runs longer than the data source's limit is stopped by Snowflake.

One query is one statement. The agent gets the column names and types and the rows exactly as Snowflake returned them, so long numbers and timestamps are not rounded. When a result is larger than the inline limit the agent gets the first rows and is told plainly that the result was cut and how many rows there are in all.

Refactor refuses everything else before it reaches Snowflake, even if the role would allow it:

• Anything that changes data or structure: INSERT, UPDATE, DELETE, MERGE, CREATE, ALTER, DROP, TRUNCATE, GRANT.
• Exports and file commands: COPY INTO, PUT, GET, LIST, REMOVE.
• CALL, so no stored procedure can be run.
• USE, SET and anything else that changes session settings.
• More than one statement in a single request.

The Snowflake connector for Python, JDBC and the Snowflake command line cannot be used from a workspace. They need a password or key, and a workspace has none.

Limits#

People who manage the data source can change these under Limits on its page at any time, with Change limits. A change applies to the next query. A limit you set in Snowflake, such as the warehouse timeout above, always applies as well.

What is recorded#

Refactor keeps a history of every query sent through a data source, including the ones it refused:

• who asked (the person, or the engagement when nobody was present), in which workspace, and when;
• the text of the query;
• how long it took and how many rows came back;
• whether it ran, was refused, was cancelled or timed out, and why it was refused or failed, in Refactor's or Snowflake's words;
• Snowflake's own query ID, so your admin can find the same query in Snowflake's query history;
• whether the result was saved to the workspace as a file.

Test connection is in the history too, one line for each check it runs, so a setup that did not work shows what Snowflake said.

The people who manage the data source read the history on its page: History, with filters for who, which workspace, the dates and the state, and every query's text and Snowflake query ID to copy. People who can only use the data source do not see it.

A query nobody waited for (a stopped command, a workspace that went away) is checked with Snowflake after its time limit has passed, and its history shows how it ended. If Snowflake no longer knows, the history says the outcome is not known.

The data a query returns is never recorded. Query results go to the workspace and to the agent's model, and nowhere else.

Each query carries a tag that names Refactor and the data source, so your admin can filter Snowflake's query history to Refactor's queries.

Marking a data source as containing health data, and removing that mark, are recorded on your organization's audit record, with who and when.

Creating, testing, attaching, detaching, rotating the key, changing the limits and removing a data source are recorded on your organization's audit record, each as its own kind of entry you can filter by.

Health data#

When you create a Snowflake data source you say whether it contains health data, with the This data source contains health data box. The answer is per data source: one with patient-level tables does, one with de-identified views may not. Refactor does not check the answer. It is your statement about the data the role can read.

A data source marked as containing health data:

• can only be created or marked in an organization set up for health data. Elsewhere the box is not offered and the reason is shown; if your organization is not set up for health data, do not connect a role that can read it. See Organizations that handle health data;
• can only be attached to a workspace or service whose network access is Added sites only. Trusted sites is not enough. If that network is later opened, the data source stops working there until it is restricted again, and the place it is attached says why;
• has no row preview in the console. Only its structure is shown;
• like every data source, never has the returned data recorded. Query text is kept.

The data source's page has a Health data panel that shows whether it is marked, who marked it and when.

Marking a data source does not keep health data away from the agent: query results still reach the workspace and the agent's model. That is what attaching it is for. See What is recorded and grant the role only the data the work needs.

Query results reach the workspace and the agent's model. Grant the role only the data the work needs.

Rotate the key#

1. On the data source's page, under Key, click Rotate key, then Make a new key. Refactor makes a new key pair and shows one command, which registers the new public key as the user's second key:

   sqlCopy

   ALTER USER REFACTOR_SVC SET RSA_PUBLIC_KEY_2 = '<new public key from Refactor>';
   

2. Run the command in Snowflake, then click Test connection. Refactor switches to the new key only once the test passes. Until then the old key keeps working, so nothing is interrupted.

3. Refactor then shows the command that removes the old key:

   sqlCopy

   ALTER USER REFACTOR_SVC UNSET RSA_PUBLIC_KEY;
   

The next rotation uses the two key slots the other way round. Refactor always shows the right command.

Remove it#

1. On the data source's page, under Remove, click Remove…, type the data source's name and click Remove data source. Refactor erases the stored key and can no longer connect. This cannot be undone: to connect again, create a new data source.

2. In Snowflake, drop the user and the role. Refactor cannot do this step for you.

   sqlCopy

   DROP USER IF EXISTS REFACTOR_SVC;
   DROP ROLE IF EXISTS REFACTOR_READER;
   

The data source's page shows these two commands before and after you remove it.

Dropping the user in Snowflake alone also stops access. The data source's page then shows Cannot connect after the next test.

Not supported yet#

• Writing to Snowflake. Data sources are read only.
• Snowflake's own drivers and tools in a workspace: the Python connector, JDBC, ODBC and the Snowflake command line.
• Signing in with your own Snowflake login. Every query runs as the service user, with the role you granted it.
• Accounts that restrict where connections come from. If your Snowflake account has a network policy, Refactor cannot connect yet, because it does not call from fixed addresses.
• Private connectivity (AWS PrivateLink and similar).