Connecting Snowflake

Axiome connects to Snowflake as a service user that signs in with a key pair. Axiome generates the pair for your workspace and keeps the private half; the Snowflake user carries the public half. Nobody types a password or handles a private key. Queries run through Snowflake's SQL API, on a warehouse you choose, under a role that only reads.

What you'll need

• Your account identifier, orgname-accountname — in Snowsight, your name › Account › View account details. An account locator such as xy12345.eu-west-3.aws, or the account URL, works too.
• The database to analyze. A connection reads one database; within it, the schemas you tick.
• Someone who can run a script as ACCOUNTADMIN. In a standard schema only a role with the MANAGE GRANTS privilege may grant on tables created later, which the script does.

Set up a role, a warehouse and a user

The connection form writes the script for you — with the names you typed, the schemas you ticked and Axiome's public key — under Setup script, with Copy the script. Run all of it in a Snowsight worksheet, as ACCOUNTADMIN. It can run again: it only creates what is missing, and sets the key and the settings each time.

With the form's default names, a database ANALYTICS and the schemas MARTS and FINANCE ticked, it reads:

-- Axiome's access to Snowflake. Run all of it, as ACCOUNTADMIN; it can run again.
USE ROLE ACCOUNTADMIN;

-- A small warehouse that suspends after a minute idle (Snowflake's default of ten minutes bills ten minutes per wake).
CREATE WAREHOUSE IF NOT EXISTS AXIOME_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

-- Axiome's role: it reads, and never writes.
CREATE ROLE IF NOT EXISTS AXIOME_READER;
GRANT USAGE ON WAREHOUSE AXIOME_WH TO ROLE AXIOME_READER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE AXIOME_READER;
-- What the role reads: the schemas ticked in Axiome's form.
GRANT USAGE ON SCHEMA ANALYTICS.MARTS TO ROLE AXIOME_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.MARTS TO ROLE AXIOME_READER;
GRANT SELECT ON ALL VIEWS IN SCHEMA ANALYTICS.MARTS TO ROLE AXIOME_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.MARTS TO ROLE AXIOME_READER;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS.MARTS TO ROLE AXIOME_READER;
GRANT USAGE ON SCHEMA ANALYTICS.FINANCE TO ROLE AXIOME_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.FINANCE TO ROLE AXIOME_READER;
GRANT SELECT ON ALL VIEWS IN SCHEMA ANALYTICS.FINANCE TO ROLE AXIOME_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.FINANCE TO ROLE AXIOME_READER;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS.FINANCE TO ROLE AXIOME_READER;

-- The user Axiome signs in as, for Axiome only: setting its key replaces any key it had.
CREATE USER IF NOT EXISTS AXIOME_SVC
  TYPE = SERVICE
  DEFAULT_ROLE = AXIOME_READER
  DEFAULT_WAREHOUSE = AXIOME_WH;
-- The key, and the session settings Axiome's SQL relies on.
ALTER USER AXIOME_SVC SET
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEF…IDAQAB'
  DEFAULT_SECONDARY_ROLES = ()
  QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE
  TIMEZONE = 'UTC'
  DATE_INPUT_FORMAT = 'AUTO'
  TIMESTAMP_INPUT_FORMAT = 'AUTO'
  TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_NTZ'
  WEEK_START = 0;
GRANT ROLE AXIOME_READER TO USER AXIOME_SVC;

What each part is for:

• A warehouse of its own: X-Small, suspended after 60 seconds idle. Snowflake bills a warehouse by the second, at least a minute each time it resumes, and by default keeps it running ten minutes after the last query — the same alerts then cost about eight times more. To use a warehouse you already size for BI, name it in the form instead and set its AUTO_SUSPEND yourself. See What it costs.
• A role that only reads. It has USAGE on the warehouse, the database and the ticked schemas, and SELECT on their tables and views, those created later included. Snowflake has no read-only transaction: the role is what keeps Axiome from writing, so grant it nothing else.
• A user for Axiome only. TYPE = SERVICE is Snowflake's type for a program: it signs in with a key, never with a password or MFA. Setting its key replaces any key it carried, so don't share the user with another tool.
• The session settings Axiome's SQL relies on, set on the user, since a request can't set most of them:
  • QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE — under TRUE, every name Axiome reads back comes back in upper case. Test connection refuses a user that has it.
  • DEFAULT_SECONDARY_ROLES = () — only the connection's role applies, never another role granted to the user. Test refuses a user whose secondary roles are in use.
  • TIMEZONE = 'UTC' — every request asks for UTC anyway.
  • DATE_INPUT_FORMAT, TIMESTAMP_INPUT_FORMAT, TIMESTAMP_TYPE_MAPPING and WEEK_START — Axiome's own queries don't depend on them, but SQL written by hand, such as Axi's, reads them. Test notes a user whose values differ.

Run the script before the first test: until the user carries the key, Snowflake refuses Axiome.

Narrowing the grants to some schemas

Test connection lists the schemas the role can see, so the very first script, run before any schema is ticked, grants the whole database instead: USAGE on all its schemas, and SELECT on all its tables and views, present and future. To keep the role to the schemas you tick, revoke those grants, then run the form's script again — it now grants schema by schema:

USE ROLE ACCOUNTADMIN;
REVOKE USAGE ON FUTURE SCHEMAS IN DATABASE ANALYTICS FROM ROLE AXIOME_READER;
REVOKE SELECT ON FUTURE TABLES IN DATABASE ANALYTICS FROM ROLE AXIOME_READER;
REVOKE SELECT ON FUTURE VIEWS IN DATABASE ANALYTICS FROM ROLE AXIOME_READER;
REVOKE USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS FROM ROLE AXIOME_READER;
REVOKE SELECT ON ALL TABLES IN DATABASE ANALYTICS FROM ROLE AXIOME_READER;
REVOKE SELECT ON ALL VIEWS IN DATABASE ANALYTICS FROM ROLE AXIOME_READER;

A schema that has future grants of its own ignores the database's: there, grant SELECT ON FUTURE TABLES (and views) in the schema itself, as the per-schema script does.

A resource monitor

A resource monitor caps what the warehouse may spend in a month. The script doesn't create one; this one notifies at 80% of 20 credits and suspends the warehouse at 100%:

USE ROLE ACCOUNTADMIN;
CREATE RESOURCE MONITOR IF NOT EXISTS AXIOME_MONITOR
  WITH CREDIT_QUOTA = 20 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE AXIOME_WH SET RESOURCE_MONITOR = AXIOME_MONITOR;

Once the monitor suspends the warehouse, every dashboard, alert and Axi question on the connection fails until the next month or a higher quota: size it from What it costs.

Add the connection

1. Go to Settings → Workspace → Data, click Add a connection and choose Snowflake in the source catalog.
2. Under General, name the connection.
3. Under Credentials, enter the Account. The line under it names the host Axiome will connect to. User, Role and Warehouse start as AXIOME_SVC, AXIOME_READER and AXIOME_WH, the script's names; enter the Database. Names read as Snowflake reads them: unquoted, in upper case; in double quotes, exactly as written.
4. Under Axiome's public key, click Generate the key — once per workspace: every Snowflake connection of the workspace uses the same key.
5. Under Setup script, click Copy the script and run it in Snowsight, as ACCOUNTADMIN (above).
6. Click Test connection at the top of the page. Test signs in and reads Snowflake's own metadata — it never wakes the warehouse — and lists the schemas the role can see. When something is wrong, it says what, most fundamental first, each with the SQL that fixes it (Troubleshooting).
7. Under Schemas, tick the ones to analyze — see below.
8. Click Create connection.

Nothing in a Snowflake connection is secret: the private half of the key is the workspace's, kept in Google Cloud Secret Manager and used server-side only. To change the account, user, role, warehouse or database later, open the connection's Settings.

Schemas

Test lists the schemas of the database that the role can see (INFORMATION_SCHEMA aside). Tick at least one. As on PostgreSQL:

• The first one you tick is the primary schema (pick another under Primary schema). Its tables keep their plain names.
• Tables of the other schemas get a semantic model named after both — FINANCE__BUDGETS for BUDGETS in FINANCE, shown as finance__budgets.
• Axiome reads schemas named with letters, digits, _ and $ only: a schema whose name needs double quotes can't be ticked.
• Another database is another connection.

See Datasets and schemas for searching, selecting several at once and changing the primary.

How your tables read in Axiome

Axiome reads the schema with SHOW commands — tables, views, columns, primary and foreign keys — which run on Snowflake's cloud services and never wake the warehouse, however often the background refresh runs. Along with the columns, it reads the table and column comments, which become the generated models' descriptions, and the declared keys, which become their joins.

• Names. Snowflake stores an unquoted name in upper case, and Axiome keeps it exactly so — the SQL it writes always quotes the exact name. A name in upper case shows in lower case: ORDERS and ORDER_DATE read as orders and order_date in editors and in Axi's answers. A quoted name, such as "Order Date", shows as it is, and so do two names that would read the same (ORDERS beside a quoted "orders").
• Types. Numbers, text, booleans, dates and timestamps are fields like any other; DATE and TIMESTAMP columns are time dimensions. A TIME column is a category, since no calendar grain fits a time of day. VARIANT, OBJECT, ARRAY, GEOGRAPHY, GEOMETRY and VECTOR columns are listed on the table page but not offered as fields.
• Time zones. Every request runs in UTC. A TIMESTAMP_TZ value is read at its UTC instant and a TIMESTAMP_LTZ in UTC, so a day is a UTC day, as on BigQuery; a TIMESTAMP_NTZ is read as written. Weeks start on Monday (or on your workspace's first day of the week), never on the account's WEEK_START.
• Semantic views are not listed as tables.
• dbt. A dbt project that builds on Snowflake imports like any other, its names bound the way Snowflake stores them: see Import from dbt.

What Axiome runs on your warehouse

Every request Axiome sends names its role, warehouse and database, runs in UTC, holds a single statement and carries a time limit — 30 seconds for widgets and Axi's queries, 20 seconds for value lookups. Past its limit, Axiome cancels the statement in Snowflake.

Tagged. Every request carries a query tag, so you can tell Axiome's queries apart in QUERY_HISTORY and in Snowsight's cost views — {"app":"axiome","surface":"dashboard","connection":"…"}, with your connection's Axiome id. It holds no names and no email addresses. The surface says what the query served:

Surface	What runs
dashboard	Widgets and their data, a table's Preview, metric runs in the Data section, the charts in Axi's answers
axi	What Axi's tools run, the summaries and alert checks Axi starts included
alert	Scheduled alert checks and reports, and an alert's Send a test
summary	An AI summary someone generates or refreshes
forecast	The history a forecast or a KPI projection reads
lookup	A filter's value list, a column's profile, a value search, a key check
schema · test	The schema read, and Test connection

To read Axiome's last week of queries, as a role that sees the account's usage (it runs on your own warehouse):

SELECT START_TIME, TRY_PARSE_JSON(QUERY_TAG):surface::STRING AS SURFACE,
       TOTAL_ELAPSED_TIME / 1000 AS SECONDS, WAREHOUSE_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME = 'AXIOME_SVC' AND START_TIME > DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;

Answered from the result cache. The SQL Axiome writes doesn't change between two runs of the same widget: a relative period such as last 30 days is written as its dates. So when the data hasn't changed, Snowflake answers a repeat from its result cache, for up to 24 hours, without the warehouse.

Waiting in line. An X-Small warehouse runs eight queries at once; past that, Snowflake queues them, and time spent queued counts against the time limit. Axi searches a value in three columns at a time, so its searches don't fill the warehouse. A team whose dashboards time out at busy hours needs a larger warehouse, or a multi-cluster one (Enterprise edition).

Read-only, whoever writes the SQL. Axi's own SQL runs under the same role, one statement at a time, and is checked as Snowflake reads it before it is sent.

What it costs

Test connection and the schema reads never wake the warehouse: they run on Snowflake's cloud services, which Snowflake bills only past a tenth of the day's warehouse compute. Everything else runs on the warehouse, and a suspended warehouse resumes for it — billed by the second from then until it suspends again, and for at least a minute. With the script's warehouse (X-Small, one credit an hour, suspended after 60 seconds):

What Axiome does	Warehouse time
Test connection, the schema read and its background refresh	none
A query whose answer is in the result cache	none
A dashboard opened while the warehouse sleeps	about 70–100 seconds: its queries, then the minute the warehouse waits before suspending
An hourly alert, on a warehouse otherwise idle	about 30–40 minutes a day
An alert every 30 minutes	up to 1–1.5 hours a day
An alert every 15 minutes	up to 2–3 hours a day

Alerts that check every 15 or 30 minutes, or hourly, check on the clock — on the hour, the half-hour, the quarter-hour — so a workspace's alerts that are due together share one wake. On a Snowflake connection, the alert editor says what a 15- or 30-minute check costs before you save it. With Snowflake's default of ten minutes before suspending, each wake bills ten minutes instead of one.

Network policies

Axiome's servers don't connect from fixed addresses yet, so there is no address to allowlist: a network policy that applies to Axiome's user refuses it, and Test says Your account's network policy refuses Axiome's address.

A policy set on the account applies to every user that has none of its own, and Snowflake applies a user's own policy instead of the account's. So with a policy on the account, Axiome's user connects only under a policy of its own that allows any address — it still signs in with Axiome's key alone. Whether that suits your account is your security team's call. Connecting over PrivateLink isn't supported.

Rotating the key

One key serves every Snowflake connection of the workspace. To replace it — on a schedule your security policy sets, or because you think it exposed:

1. Open a Snowflake connection's Settings (or a new Snowflake connection's page) and, under Axiome's public key, click Regenerate.

2. Read the confirm: it says how many Snowflake connections stop signing in until their users carry the new key. Confirm with Regenerate. Axiome stops using the old key at once and deletes its private half.

3. Set the new key on the user of every Snowflake connection, as ACCOUNTADMIN — run each connection's setup script again, which holds the new key, or:

   ALTER USER AXIOME_SVC SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEF…IDAQAB';

Between the two steps, the workspace's Snowflake dashboards, alerts and Axi questions fail with the key hint, so run the line right away. Two admins can't rotate twice by accident: an admin still shown the old key gets the new one back instead of replacing it again.

Troubleshooting

Test connection words what it found in your language and, where SQL fixes it, gives the line to run (Copy), adapted to your names:

Test says	Cause	Fix
No Snowflake account answers at …	The account identifier names no account	Copy the identifier from Snowsight: your name › Account › View account details
Snowflake refused Axiome's key for …	The user doesn't carry the key, doesn't exist, or is in another account. An account URL turns an account name's underscores into hyphens	Run the setup script, or the ALTER USER … SET RSA_PUBLIC_KEY line Test gives. Repeated failures lock the user for a while: ALTER USER AXIOME_SVC SET MINS_TO_UNLOCK = 0;
Generate Axiome's key first…	The workspace has no key yet	Generate the key, then run the setup script
The role … isn't granted to …, or doesn't exist	The script didn't run, or another role was typed	GRANT ROLE AXIOME_READER TO USER AXIOME_SVC;
The role … can't use the warehouse …	The warehouse doesn't exist, or the role has no USAGE on it	GRANT USAGE ON WAREHOUSE AXIOME_WH TO ROLE AXIOME_READER;
The role … can't see the database …	The database doesn't exist, or the role has no USAGE on it	GRANT USAGE ON DATABASE ANALYTICS TO ROLE AXIOME_READER;
The role … sees no schema in …, or can't see these schemas	No USAGE on the schemas	The GRANT USAGE ON SCHEMA lines Test gives
Settings of … that Axiome's SQL can't work under	QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE, or secondary roles in use	The ALTER USER line Test gives
Worth aligning (a note; the test passes)	WEEK_START, the input formats or TIMESTAMP_TYPE_MAPPING differ from the script's	The ALTER USER line, for SQL written by hand
Your account's network policy refuses Axiome's address	A network policy applies to the user	See Network policies
Snowflake didn't answer within 20 seconds	A slow or busy account	Test again

Once connected:

Symptom	Likely cause
A widget or Axi says the role lacks a privilege on a table	The table is in a schema with future grants of its own, or was created by a role the grants don't reach: GRANT SELECT ON TABLE … TO ROLE AXIOME_READER;. Axi's message names the grant
Widgets time out at busy hours	Queries waited for the warehouse: time queued counts against the 30-second limit. Size up the warehouse, or give Axiome its own
A table or a column is missing	Its schema isn't ticked, or the role can't SELECT it. A VARIANT, OBJECT, ARRAY, GEOGRAPHY, GEOMETRY or VECTOR column isn't offered as a field
A date turned into text — by a formula's TOTEXT, or in Axi's own SQL — reads in another format	The user's DATE_OUTPUT_FORMAT (or a timestamp's): ALTER USER AXIOME_SVC UNSET DATE_OUTPUT_FORMAT;
Every query stops at once	The resource monitor suspended the warehouse, or the key was regenerated and the users don't carry it yet