Snowflake

Run queries on Snowflake

Overview

The Snowflake tool allows you to run SQL queries on a Snowflake database.

Key Features

• SNOWFLAKE_RUN_QUERY
  • Execute SQL queries on Snowflake.
  • Optionally override the warehouse, database, schema, and role for the query.
  • Retrieve results in a structured format (columns and rows).

Authentication

Create a SNOWFLAKE secret with your account identifier, username, and password. Only password authentication is supported (key-pair and OAuth are not supported yet).

• Account: Your Snowflake account identifier (e.g. myorg-myaccount). You can read it from your Snowsight URL: https://app.snowflake.com/<org>/<account>/… → use <org>-<account>.
• User: Snowflake username to connect with.
• Password: Password for the user.

Note: Treat credentials as sensitive information and never commit them to public repositories.

Recommended: create a dedicated service user

Snowflake enforces MFA for human users, which blocks plain password logins from tools. Create a dedicated user with TYPE = LEGACY_SERVICE and grant it access explicitly. Every level of the hierarchy (warehouse → database → schema → table) needs its own grant — missing any one of them results in Object '…' does not exist or not authorized:

CREATE ROLE JINBA_ROLE;
CREATE USER JINBA_FLOW_USER
  PASSWORD = '<strong-password>'
  TYPE = LEGACY_SERVICE
  DEFAULT_ROLE = JINBA_ROLE
  MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE JINBA_ROLE TO USER JINBA_FLOW_USER;

GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE JINBA_ROLE;
GRANT USAGE ON DATABASE <db> TO ROLE JINBA_ROLE;
GRANT USAGE ON SCHEMA <db>.<schema> TO ROLE JINBA_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA <db>.<schema> TO ROLE JINBA_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <db>.<schema> TO ROLE JINBA_ROLE;

Warehouse, Database, Schema, and Role

These determine which compute resource and namespace a query runs against. They are not part of the secret — pass them per-step as inputs so the same secret can be reused across warehouses, databases, or roles:

• Warehouse: The virtual warehouse (compute cluster) to run the query on.
• Database / Schema: The namespace the query executes against.
• Role: The security role to use for the session. Defaults to the user’s default role if omitted.

Each run opens a fresh session — USE WAREHOUSE / USE SCHEMA statements executed in Snowsight do not carry over. Either set these inputs or fully qualify table names (db.schema.table) in the query.

Large Integers

Integers in dedicated NUMBER/INTEGER columns are returned as exact values — as a regular JSON number when safe (up to 2^53), or as a string when larger, so no precision is lost. Integers nested inside VARIANT/OBJECT/ARRAY columns do not get this protection and may lose precision beyond 2^53, since Snowflake’s driver parses semi-structured content as plain JSON numbers. If you need exact large integers from semi-structured data, cast them to a string in the query (e.g. data:big_id::string) before selecting.

Example: Run Snowflake Query

- id: run_query
  input:
    - name: connection
      value: "{{secrets.MY_SNOWFLAKE_SECRET}}"
    - name: query
      value: "SELECT * FROM sales_db.public.orders LIMIT 10;"
    - name: warehouse
      value: "COMPUTE_WH"
    - name: database
      value: "SALES_DB"
    - name: schema
      value: "PUBLIC"
  tool: SNOWFLAKE_RUN_QUERY