Snowflake Connectors

Overview

Snowflake connectors let a manager attach one or more known external systems (catalogued by MT2Data) to a tenant and run SQL against them through the Colmeia API. Authentication to Snowflake uses key-pair JWT — Colmeia generates the keypair when the connector is created, displays the public key for one-time enrollment by the Snowflake account administrator, and signs JWTs from the dashboard side so your scripts only ever use your Colmeia tenant bearer.

Each connector is keyed by a Snowflake source slug drawn from a static catalog curated by MT2Data (currently: solinftec). A tenant can attach at most one connector per source slug, with its own keypair, account, role, warehouse, database, and schema defaults. The slug is the path segment used in the data URL.

Base URL: https://colmeia.mt2data.cloud/api/sn

Setup (one-time, in the dashboard)

1. Sign in at https://colmeia.mt2data.cloud/login.

2. Open the tenant page → Integrations.

3. Scroll to the Snowflake Connectors section → click + Add Snowflake connection.

4. Fill the form:

   Field	Required	Description
   Snowflake source	Yes	Dropdown of catalogued external systems available for this tenant. Pick the source you want to integrate (currently Solinftec). Sources already connected for this tenant are disabled in the dropdown. The selected source's slug becomes the path segment in the data URL (/api/sn/data/:tenantId/:sourceId/query). Picking a source may pre-fill the Warehouse / Database / Schema / Role fields — those defaults are suggestions you can edit.
   Account locator	Yes	Bare Snowflake account locator (not org-account form). Get it by running SELECT CURRENT_ACCOUNT() in Snowflake — returns something like PS07064. The ORG-ACCOUNT form (e.g. SOLINFTEC-EASTUS2) is rejected by JWT auth on non-AWS-US regions; use the locator. For Azure / non-AWS-US accounts you may need region-qualified form LOCATOR.REGION.CLOUD (e.g. PS07064.EAST-US-2.AZURE) — try the bare locator first.
   Account URL	Yes	Snowflake URL for your account (e.g. https://ps07064.east-us-2.azure.snowflakecomputing.com).
   Username (LOGIN_NAME)	Yes	The user's LOGIN_NAME (not its NAME), uppercased. Snowflake's JWT verifier maps the sub claim to login_name, not the user identifier. To get it, run DESC USER "<your_user>" in Snowflake and copy the LOGIN_NAME row value verbatim. If the user was created without an explicit LOGIN_NAME, this defaults to the user's NAME in uppercase.
   Warehouse	No	Default warehouse for queries that don't override it. Must have AUTO_RESUME = TRUE — programmatic access cannot wake a suspended warehouse. Check with SHOW WAREHOUSES LIKE '<wh>'; admin must run ALTER WAREHOUSE <wh> SET AUTO_RESUME = TRUE if it's false.
   Role	No	Default role.
   Database / Schema	No	Default database and schema.

5. Submit. Colmeia generates a fresh RSA-2048 keypair tied to this connector and shows you an ALTER USER … SET RSA_PUBLIC_KEY=… command. The status reads Pending admin enrollment.

6. Forward the ALTER USER … command to the Snowflake account administrator (see the message template below). They run it once. Key-pair JWT authentication is not subject to DUO/MFA, so no human interaction is needed afterwards.

7. After the admin confirms enrollment, return to the connector card and click Test connection. Status flips to Connected and the connector is ready for queries.

You can later Disable (pauses queries while keeping the row), Enable, or Revoke (deletes the connector and its private key from Colmeia) a connector at any time. Revoking inside Colmeia does not revoke the public key on the Snowflake side — ask the admin to run ALTER USER "<USER>" UNSET RSA_PUBLIC_KEY; if you also want to remove it there.

Worked example — Solinftec

The dashboard accepts these values:

After saving, the dashboard shows the data URL as /api/sn/data/<your-tenant-id>/solinftec/query.

After submit, Colmeia shows a command similar to:

ALTER USER "<YOUR_USER>" SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOC...';

(The base64 payload is unique to this connector.)

Admin enrollment message (template)

Send to the Snowflake administrator by a secure channel:

Hello,

To allow programmatic access from Colmeia using key-pair JWT authentication (which is not subject to DUO MFA), please run the following SQL once as SECURITYADMIN or ACCOUNTADMIN. Note: the ALTER USER quoted identifier below must match the user's NAME (case-sensitive). If the user was created with an explicit LOGIN_NAME that differs from the NAME, replace "<YOUR_USER>" with the actual NAME shown by DESC USER.

ALTER USER "<YOUR_USER>" SET RSA_PUBLIC_KEY='<base64 shown in the Colmeia dashboard>';

Then confirm the fingerprint and the LOGIN_NAME:

DESC USER "<YOUR_USER>";

The RSA_PUBLIC_KEY_FP value should match the fingerprint shown on the Colmeia connector card. Please also reply with the user's LOGIN_NAME row value — that's what Colmeia must use in the connector's Username field (Snowflake's JWT verifier matches the sub claim against LOGIN_NAME, not NAME).

Additional checks:

1. If the warehouse used by this user has AUTO_RESUME = FALSE, please enable auto-resume so programmatic queries can wake it:

   SHOW WAREHOUSES LIKE '<WH_NAME>';
   -- if auto_resume = false:
   ALTER WAREHOUSE <WH_NAME> SET AUTO_RESUME = TRUE;
   

2. Confirm that any AUTHENTICATION POLICY attached to this user includes 'KEYPAIR_JWT', and that no NETWORK POLICY blocks Cloudflare egress (egress IPs are not fixed). If a network policy is active, NETWORK_POLICY_EVALUATION = NOT_ENFORCED on the auth policy resolves it.

Thanks.

Query API

POST /api/sn/data/:tenantId/:sourceId/query

Execute a free-form SQL statement against the connector's Snowflake account.

Path parameters:

Authentication — either:

Authorization: Bearer <tenant-secret>

or a dashboard session cookie:

Cookie: colmeia_session=<session-token>

Request body:

Simple query

curl -s \
  "https://colmeia.mt2data.cloud/api/sn/data/YOUR_TENANT_ID/YOUR_SOURCE_ID/query" \
  -H "Authorization: Bearer <tenant-secret>" \
  -H "Content-Type: application/json" \
  -d '{ "query": "SELECT CURRENT_TIMESTAMP() AS now, CURRENT_USER() AS user" }'

The body fields warehouse, database, schema, role, and timeout are all optional. Omit them and the query runs with whatever defaults you set on the connector. The Snowflake user's DEFAULT_NAMESPACE, DEFAULT_ROLE, and DEFAULT_WAREHOUSE also fill in any gaps. Most queries don't need any of these fields.

Query with per-request overrides

Use the optional body fields only when a single query needs to run against a non-default warehouse / database / schema / role, or with a custom timeout. The override applies to that one call — the connector's defaults are untouched.

curl -s \
  "https://colmeia.mt2data.cloud/api/sn/data/YOUR_TENANT_ID/YOUR_SOURCE_ID/query" \
  -H "Authorization: Bearer <tenant-secret>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT field_id, name, area_hectares FROM solinftec_bi.ss.fields LIMIT 10",
    "warehouse": "SSWH_LARGE",
    "database": "SOLINFTEC_BI",
    "schema": "SS",
    "role": "SSRL_0307",
    "timeout": 120
  }'

Common reasons to override:

• warehouse — heavy query needs a bigger compute (e.g. switch from X-Small default to LARGE for this call only).
• database / schema — query targets tables outside the connector's default namespace.
• role — need a role with different grants for a specific query.
• timeout — long-running query that may exceed the default 60s server-side execution timeout.

Multiline query

Save the SQL to a file (query.sql), then use jq to build the JSON payload:

-- query.sql
SELECT
    field_id,
    name,
    crop_type,
    area_hectares,
    last_visit_at
FROM your_database.public.fields
WHERE last_visit_at >= '2026-01-01'
ORDER BY last_visit_at DESC
LIMIT 100

curl -s \
  "https://colmeia.mt2data.cloud/api/sn/data/YOUR_TENANT_ID/YOUR_SOURCE_ID/query" \
  -H "Authorization: Bearer <tenant-secret>" \
  -H "Content-Type: application/json" \
  -d "$(jq -n --rawfile q query.sql '{"query": $q}')"

jq handles newlines, quotes, and special characters automatically. Install with brew install jq or apt install jq.

Database, schema, role, and warehouse fall back to whatever you configured on the connector. Override any of them per request by adding the matching field to the JSON body. Table and column names depend on the schema provisioned to your Snowflake account — consult your data provider for the catalog.

Response:

{
  "success": true,
  "data": {
    "columns": [
      { "name": "NOW",  "type": "timestamp_ltz" },
      { "name": "USER", "type": "text" }
    ],
    "data": [
      { "NOW": "1779132580.643000000", "USER": "YOUR_USER" }
    ]
  }
}

Values are returned as a string-typed array from Snowflake; numeric, boolean, and NULL columns are cast to JS-native types in the response.

Date/time encoding (important):

Snowflake's REST API does not return ISO strings for date/time columns. The encoding depends on the column type — be defensive when parsing:

Always branch on the columns[i].type value before parsing.

Error responses:

Listing your connectors

Existing connectors for a tenant are visible in the dashboard's Snowflake Connectors section. Each card shows the catalogued source name, source slug, account, user, warehouse, current status, and the data path — the same slug you put in the URL above.

Programmatic discovery: GET /api/data/:tenantId/connections lists every active connector for the tenant. Snowflake entries carry "snowflake": true, the source_id slug, and the data_path URL. See middleware-api.md for the full payload shape.

A tenant can attach at most one connector per source slug — Colmeia enforces uniqueness of (tenant, source_id). To connect to multiple external systems that all happen to expose Snowflake, those systems each need their own slug in the catalog (contact MT2Data to register additions).

Notes on the keypair

• The private key never leaves Colmeia. You do not download, copy, or paste it anywhere.
• The public key is shown only on the connector card. Re-display by reopening the card while the connector is in Pending admin enrollment status.
• Revoking a connector permanently destroys the private key. Re-creating a connector generates a fresh keypair and requires a new ALTER USER … SET RSA_PUBLIC_KEY=… from the admin.
• One Snowflake user can have only one public key enrolled at a time. If you need to rotate, ask the admin to overwrite the previous one with the new value shown in the dashboard.