Connect Snowflake

To connect Snowflake to CoIsland you need an account identifier, a user, a programmatic access token (PAT) for that user, a role and a warehouse. CoIsland sends each monitor’s SQL to Snowflake’s SQL API with that token, straight from your Mac, with no CoIsland server in between. Here is every step, from a read-only role to your first alert.

Before you connect Snowflake

Have these ready, or ask your Snowflake administrator for them:

• A role that can read what you watch. CoIsland runs only SELECT, WITH and SHOW; the role decides what those can see.
• A warehouse. Each check can wake it. An X-Small one with a short auto-suspend keeps checks cheap: at the default of one check every 15 minutes, it suspends between them.
• A user allowed to use a token. Snowflake’s rules for programmatic access tokens:
  • Your own user needs no special privilege to generate one. For another user, you need OWNERSHIP or MODIFY PROGRAMMATIC AUTHENTICATION METHODS on it.
  • By default, a person can generate a token without a network policy, but can only sign in with it when a network policy applies to the user or the account. An administrator can relax this with an authentication policy whose PAT_POLICY sets NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED.
  • A token expires after 15 days unless you choose otherwise, 365 days at most. Its role restriction and expiry cannot be changed later, and a user holds 15 tokens at most.

To see whether a network policy applies, run both lines and read the value column:

SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;
SHOW PARAMETERS LIKE 'network_policy' IN USER jdoe;

Set up a read-only role and warehouse

Run this once with a role that can create warehouses and roles and grant on your data, such as ACCOUNTADMIN. Replace analytics.sales with the schema you want to watch and jdoe with your user.

-- A small warehouse for checks
CREATE WAREHOUSE IF NOT EXISTS coisland_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- A role that can only read
CREATE ROLE IF NOT EXISTS coisland_reader;
GRANT USAGE ON WAREHOUSE coisland_wh TO ROLE coisland_reader;
GRANT USAGE ON DATABASE analytics TO ROLE coisland_reader;
GRANT USAGE ON SCHEMA analytics.sales TO ROLE coisland_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.sales TO ROLE coisland_reader;
GRANT SELECT ON ALL VIEWS IN SCHEMA analytics.sales TO ROLE coisland_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.sales TO ROLE coisland_reader;

-- The user the token belongs to
GRANT ROLE coisland_reader TO USER jdoe;

Future grants need the MANAGE GRANTS privilege, which ACCOUNTADMIN and SECURITYADMIN have. To watch failed tasks as well, add GRANT MONITOR EXECUTION ON ACCOUNT TO ROLE coisland_reader; so the role can read the history of every task.

Create a programmatic access token

This is the Snowflake PAT setup itself. Do it in Snowsight or with SQL.

In Snowsight

1. Sign in to Snowsight and select Governance & security › Users & roles.
2. Select your user.
3. Under Programmatic access tokens, select Generate new token.
4. Give it a name (letters, digits and underscores, such as COISLAND) and a comment.
5. Choose how many days until it expires.
6. Choose One specific role (recommended) and pick COISLAND_READER.
7. Select Generate, then copy or download the token. Snowflake never shows it again.

With SQL

Signed in as yourself, this creates the same token and returns it in the token_secret column, the only place it ever appears:

ALTER USER ADD PROGRAMMATIC ACCESS TOKEN coisland
  ROLE_RESTRICTION = 'COISLAND_READER'
  DAYS_TO_EXPIRY = 90
  COMMENT = 'CoIsland monitors on my Mac';

A token restricted to one role signs in with that role only, so every check CoIsland runs is bounded by what COISLAND_READER can read.

Add the Snowflake connector in CoIsland

Open Settings › Connectors, click + (Add Connector) and choose Snowflake. Fill in the form:

• Account identifier: orgname-accountname, as in your Snowsight address app.snowflake.com/orgname/accountname. Snowsight also shows it under the account selector, View account details.
• User: the Snowflake user the token belongs to, such as JDOE.
• Connector name: what monitors call it. CoIsland suggests one from the account (myorg-myaccount gives myorg_myaccount): letters, digits, _ and -, starting with a letter.
• Programmatic access token: the token you copied, with no spaces or line breaks.
• Role: the role the token is restricted to, COISLAND_READER.
• Warehouse: where monitors run, COISLAND_WH.
• Database and Schema (optional): where a monitor’s unqualified table names resolve, such as ANALYTICS and SALES. A monitor can set its own.

Already use the Snowflake CLI or Cortex Code? Import from Snowflake CLI, at the top of the form, fills every field, the token included, from one of your connections (see Import from the Snowflake CLI).

Click Test connector: CoIsland signs in, asks Snowflake for CURRENT_USER(), CURRENT_ROLE() and CURRENT_WAREHOUSE(), and shows Connected with the answer or Failed with Snowflake’s reason. Nothing is saved until Add Connector.

Where CoIsland saves it

Add Connector keeps the connector’s name, account, user, role, warehouse, database and schema in ~/Library/Application Support/CoIsland/connectors.json, and the token in your login Keychain, never in a file: the same place as every other connector. The first Snowflake connector becomes the default: a monitor that names no connector runs on it.

CoIsland never writes to ~/.snowflake. A connector added, edited or deleted in CoIsland does not change what the Snowflake CLI and Cortex Code see, and theirs do not change CoIsland’s.

Import from the Snowflake CLI

If you use the Snowflake CLI or Cortex Code, CoIsland brings your connections in, reading ~/.snowflake (or $SNOWFLAKE_HOME) and never writing to it:

• Once, the first time CoIsland starts with no Snowflake connector of its own, it copies every connection that signs in with a programmatic access token, under the same name, with its token (from token, token_file_path or password), role, warehouse, database and schema. The Snowflake CLI’s default connection (default_connection_name in config.toml) becomes CoIsland’s default. Monitors that name those connections run on them unchanged. It does not run again, so a connector you delete later stays deleted.
• Any time after, Import from Snowflake CLI in the Snowflake form lists the connections CoIsland does not have yet. Choosing one fills the form, the token included; Test connector and Add Connector then work as for typed values.

Without a connections.toml, CoIsland reads the [connections.*] tables of config.toml, as the Snowflake CLI does; the two are never merged. A connection can be imported when it signs in with a programmatic access token:

[analytics_prod]
account = "myorg-myaccount"
user = "JDOE"
authenticator = "PROGRAMMATIC_ACCESS_TOKEN"
token_file_path = "/Users/jdoe/.snowflake/analytics_prod.token"
role = "COISLAND_READER"
warehouse = "COISLAND_WH"
database = "ANALYTICS"
schema = "SALES"

• CoIsland reads account (or host), user, authenticator, token, token_file_path or password, role, warehouse, database and schema.
• Connections that use single sign-on (externalbrowser), a key pair (SNOWFLAKE_JWT) or a password are not imported: the form names them under Import from Snowflake CLI. Add a token connector next to them.
• Cortex Code’s own setup puts the token in password with no authenticator line. CoIsland reads that as a password connection: add the connector from the form with the same token.
• A token renewed later with the Snowflake CLI is not picked up: paste it into the connector (Update Token).

Write your first Snowflake monitor

Open Settings › Monitors, click + (New Monitor), then choose Snowflake and Custom SQL. Give it a title, pick your connector under Runs on, and write the SQL. CoIsland reads its first word and refuses anything but SELECT, WITH and SHOW before it reaches Snowflake.

SELECT order_id, customer_id, amount, placed_at
FROM analytics.sales.orders
WHERE amount > 10000
  AND placed_at > DATEADD('hour', -24, CURRENT_TIMESTAMP())

Test runs it once and shows what matches, without saving or alerting. Leave Alert when on New rows appear and Check every on 15 minutes. Under Advanced, set Row identity to ORDER_ID, so an order whose values change does not count as new. Then click Create Monitor.

Because it alerts on new rows, its first check records what matches and raises nothing. After that, each new order over 10,000 reaches the island as an alert you can hand to an agent such as Cortex Code, which runs on its own connections in ~/.snowflake (see Agents).

Every monitor, of every connector, is a .sql file in ~/.coisland/watches. This one reads:

-- name: Big orders
-- connector: myorg_myaccount
-- every: 15m
-- alert: new-rows
-- key: ORDER_ID
-- sound: Glass

SELECT order_id, customer_id, amount, placed_at
FROM analytics.sales.orders
WHERE amount > 10000
  AND placed_at > DATEADD('hour', -24, CURRENT_TIMESTAMP())

Watch files lists every header key. For failed tasks, the Snowflake alerts guide builds a monitor on TASK_HISTORY step by step.

SELECT start_time, query_tag, warehouse_name, total_elapsed_time, execution_status
FROM TABLE(analytics.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(RESULT_LIMIT => 1000))
WHERE query_tag LIKE 'coisland:%'
ORDER BY start_time DESC;