Skip to main content
This guide connects canonic to a Snowflake account from scratch: a key pair for headless authentication, a role that can only read, a connection, and a first bootstrapped semantic layer with a query. It uses a small throwaway database, so you can follow it on a trial account without touching real data.
The Snowflake connector needs the snowflake extra: pip install 'canonic[snowflake]'. Without it, a Snowflake connection fails with an install hint instead of a stack trace.

​
What you need

  • A Snowflake account and a user that can create roles and grant privileges, typically ACCOUNTADMIN on a trial account.
  • Your account identifier, in the form orgname-accountname (or an account locator with a region such as xy12345.eu-central-1). In Snowsight, click your name at the bottom left, then Account and View account details.
  • A warehouse. A trial account has COMPUTE_WH.
  • openssl on your machine.
All SQL below runs in a Snowsight SQL worksheet. Open one under Projects and Worksheets (newer interfaces call this Workspaces), pick the role in the role menu at the top right, and run the whole script with Run All, because a single run only executes the current statement.

​
Why key-pair auth

canonic connects without a human in the loop, so it cannot answer an interactive prompt. Snowflake can require multi-factor authentication for password sign-ins of regular users, which a headless client cannot complete. Key-pair authentication has no such prompt, and the private key never leaves your machine. The setup wizard offers key-pair auth too, or you can write the connection yourself as shown below. For automation, prefer a dedicated service user with its own key over your personal login, see the Snowflake documentation on service users.
1

Create the key pair

In a terminal, generate an unencrypted PKCS8 private key and its public key:
rsa_key.p8 is your private key. Keep it outside any repository and never share it. If you prefer an encrypted key, generate it with a passphrase instead of -nocrypt and point credentials_ref at the passphrase later.
2

Register the public key with your user

Copy the public key without its header and footer lines and without line breaks:
pbcopy is macOS. On Linux, print the value and copy it by hand. Then run this in a worksheet with your login name and the copied key:
RSA_PUBLIC_KEY_FP in the output must now hold a value. Your login name is what SELECT CURRENT_USER(); returns.
3

Create a read-only role

Snowflake has no read-only session flag, so the safety of a connection is the role it runs as. canonic also parses every statement and refuses anything that is not a single SELECT, but the role is the part Snowflake itself enforces. Create one that can only read what canonic should see:
Replace the warehouse, database and schema with your own. To cover tables created later, also grant SELECT ON FUTURE TABLES IN SCHEMA ... and the same for views.
4

Add the connection

Add the connection to canonic.yaml. database and schema set the session defaults, schemas limits what introspection returns, and read_only_role is the role the session uses:
With key-pair auth there is no credentials_ref, unless the private key is encrypted. Then it points at the passphrase, for example credentials_ref: env:SNOWFLAKE_KEY_PASSPHRASE. You can also create the connection from the command line, see canonic connection add.
5

Test the connection

A successful test confirms the account, the key and the warehouse. A failure prints Snowflake’s own message, see Troubleshooting.

​
Bootstrap the semantic layer

With the connection in place, let canonic draft semantic sources from the live schema:
Introspection reads INFORMATION_SCHEMA for tables, views, column types and row estimates, and SHOW PRIMARY KEYS and SHOW IMPORTED KEYS for declared keys. What you get depends on those keys:
  • A table with a primary key becomes a source with grain taken from the key, and a foreign key becomes a many_to_one join. These are accepted automatically on the first run.
  • A table or view without a primary key becomes a proposal with an empty grain and grain_draft: true. canonic does not guess a grain. Review it with canonic review and fill in the grain yourself.
  • A VARIANT, OBJECT or ARRAY column is recorded with type json and is not turned into a dimension.
Snowflake never enforces primary and foreign keys, so they only help canonic when someone declared them. A role only sees keys on objects it can access. If the key queries themselves fail, introspection logs a warning and continues without keys. Either way, every grain without a key then needs a manual review. The generated sources are named after the table in the case Snowflake stores it, for example ORDERS with table: SHOP.ORDERS, and carry the measure row_count plus total_<COLUMN> sums for numeric columns that are not keys. Measures are not metrics yet. To query one, give it a metric contract:
sl compile shows the SQL canonic generates for the Snowflake dialect, including quoted identifiers and, when a dimension lives on a joined source, the join. For a question no metric covers, canonic sql runs a read-only statement directly:

​
Identifier case

canonic quotes every identifier it emits. Snowflake stores an unquoted name in upper case, so CREATE TABLE orders (...) creates a table called ORDERS, and "orders" in quotes then refers to a different, non-existent object.
  • Introspection returns names exactly as Snowflake stores them, so a source generated by canonic ingest already matches.
  • When you write a semantic source by hand, use the stored case: table: ORDERS, name: ID, and expr: sum(AMOUNT).
  • An object created with a quoted lower-case name, such as CREATE TABLE "orders", keeps that case, and you must write it that way.
If a query fails with Object ... does not exist or not authorized although the table is there, a case mismatch is the most likely cause, after missing privileges.

​
Session settings and limits

Every session sets QUERY_TAG to canonic, which makes canonic’s statements easy to find in Snowflake’s query history.

​
Troubleshooting

​
Clean up

If you created throwaway objects for this guide, remove them in a worksheet:

​
Running the connector tests

Contributors can run the Snowflake tests without an account, since the unit tests use a fake driver and an optional smoke test uses the in-process emulator fakesnow. The live tests in tests/connectors/test_snowflake_live.py need a real account. Run tests/connectors/fixtures/snowflake_live_setup.sql once in a worksheet. It creates the CANONIC_TEST database with sample tables, a view, declared keys and a CANONIC_RO role. Then export the variables the tests read and run them. They are skipped when CANONIC_TEST_SNOWFLAKE_ACCOUNT is unset:
Use CANONIC_TEST_SNOWFLAKE_PASSWORD instead of the key path for password auth, and CANONIC_TEST_SNOWFLAKE_PRIVATE_KEY_PASSPHRASE for an encrypted key.