Snowflake

Provides integration with Snowflake using the Snowflake SQL API for parameterized queries and table mutations.

Revision History

0.0.0.4 - Initial release.

Connection

Use the Connection to configure the Snowflake account context and OAuth credentials used by all methods.

Property	Type	Description
`Url`	String	The exact HTTPS base URL for the Snowflake account, e.g. `https://<account_identifier>.snowflakecomputing.com`.
`Account Identifier`	String	The Snowflake account identifier. This is not the OAuth client ID.
`User Name`	String	The Snowflake user associated with the OAuth Connection.
`Warehouse`	String	The case-sensitive Snowflake warehouse used for SQL statements.
`Database`	String	The case-sensitive Snowflake database used for SQL statements.
`Schema`	String	The case-sensitive Snowflake schema used for table discovery and mutations.
`Client ID`	String	The OAuth client ID issued by the Snowflake security integration.
`Client Secrets`	Masked	The OAuth confidential-client secret issued by the Snowflake security integration.
`Role`	String	The case-sensitive Snowflake role used for authorization and SQL statements.
`Access Token`	Masked	The OAuth bearer token used for Snowflake SQL API requests. Flowgear populates this after authorization.
`Refresh Token`	Masked	The OAuth refresh token used to renew the access token. Flowgear populates this after authorization.

Setup Notes

Create a Snowflake OAuth security integration for a custom confidential client. Enable refresh tokens, register the Flowgear OAuth callback URI, and allow the role that the Connection will use. The Node sends an S256 PKCE challenge, so you can enable PKCE enforcement on the integration.
Create or identify the Snowflake user and role for the Connection. Grant the role named in Role to the user named in User Name. Ensure that this user completes the authorization flow and can consent to the role through the security integration.

If you need a dedicated user and role, run the following statements with a Snowflake role that can create users and roles. Replace each angle-bracket placeholder with an identifier from your environment.

-- Create a dedicated least-privilege role for the Flowgear Connection.
CREATE ROLE IF NOT EXISTS <ROLE>;

-- Create an interactive user that can complete the OAuth authorization flow.
-- Omit PASSWORD and MUST_CHANGE_PASSWORD when authentication is managed by your identity provider.
CREATE USER IF NOT EXISTS <USER>
  TYPE = PERSON
  PASSWORD = '<TEMPORARY_PASSWORD>'
  DEFAULT_ROLE = <ROLE>
  DEFAULT_WAREHOUSE = <WAREHOUSE>
  DEFAULT_NAMESPACE = <DATABASE>.<SCHEMA>
  MUST_CHANGE_PASSWORD = TRUE;

-- Allow the user to activate the role requested by the Flowgear Connection.
GRANT ROLE <ROLE>
TO USER <USER>;

Grant the role access to the configured warehouse, database, schema, and tables. Snowflake requires access at every level of the object hierarchy. These grants use a specific table to keep access limited.

-- Allows the role to use the warehouse that executes SQL statements.
GRANT USAGE
ON WAREHOUSE <WAREHOUSE>
TO ROLE <ROLE>;

-- Allows the role to resolve objects in the configured database.
GRANT USAGE
ON DATABASE <DATABASE>
TO ROLE <ROLE>;

-- Allows the role to resolve objects in the configured schema.
GRANT USAGE
ON SCHEMA <DATABASE>.<SCHEMA>
TO ROLE <ROLE>;

-- Allows Query to read the table and makes the table available for discovery.
GRANT SELECT
ON TABLE <DATABASE>.<SCHEMA>.<TABLE_NAME>
TO ROLE <ROLE>;

Grant only the additional table privileges required by the methods used in your Workflow:

-- Insert requires INSERT, Update requires UPDATE, and Delete requires DELETE.
-- Merge requires INSERT and UPDATE because it can perform either operation.
-- Run only the statements required by the methods used in the Workflow.
GRANT INSERT
ON TABLE <DATABASE>.<SCHEMA>.<TABLE_NAME>
TO ROLE <ROLE>;

GRANT UPDATE
ON TABLE <DATABASE>.<SCHEMA>.<TABLE_NAME>
TO ROLE <ROLE>;

GRANT DELETE
ON TABLE <DATABASE>.<SCHEMA>.<TABLE_NAME>
TO ROLE <ROLE>;

Repeat the table grants for each table the Workflow must access. If the Workflow must access tables created later, use equivalent ON FUTURE TABLES IN SCHEMA <DATABASE>.<SCHEMA> grants according to your organization's access policy.

Obtain the client ID and client secret from the Snowflake security integration.
Create a Snowflake Connection in the Flowgear Console. Enter Url, Account Identifier, User Name, Warehouse, Database, Schema, Client ID, Client Secrets, and Role exactly as configured in Snowflake.
Click Connect your Account, then complete the Snowflake sign-in and consent flow. Flowgear stores the returned Access Token and Refresh Token on the Connection.
Test the Connection. The test confirms that the configured Schema exists and is accessible in the configured Database.

The OAuth authorization requests the refresh_token scope and the exact role supplied in Role. For Snowflake security integration options and client credential retrieval, see Configure Snowflake OAuth for custom clients. For user and role access, see GRANT ROLE and GRANT privileges to a role.

Methods

The Snowflake Node exposes methods for running parameterized SQL and inserting, updating, deleting, or merging table rows. Templates are generated from accessible base-table metadata in the configured Schema.

Query

Executes a Snowflake SQL statement and streams the returned rows.

Parameter	Type	Description
`Connection`	Connection	The Snowflake `Connection`.
`Query`	String	The SQL statement to execute. Use named `@Name` placeholders for external values.
`CustomParameters`	Object	Optional named SQL values. Keys can include or omit the leading `@` prefix.

Return	Type	Description
`Items`	Array	The rows returned by the query. Each row is emitted as an object.

Insert

Inserts connected items into an exact Snowflake table.

Parameter	Type	Description
`Connection`	Connection	The Snowflake `Connection`.
`TableName`	String	The exact, case-sensitive name of the target table in the configured `Schema`.
`Items`	Array	The rows to insert. Property names must match writable Snowflake columns exactly.

Return	Type	Description
`Response`	Array	One response per executed batch, including `Flowgear` status and request details, `RowsAffected`, `RowsInserted`, `Code`, `SqlState`, and `StatementHandle`.

Update

Updates connected items in an exact Snowflake table by using the supplied key fields.

Parameter	Type	Description
`Connection`	Connection	The Snowflake `Connection`.
`TableName`	String	The exact, case-sensitive name of the target table in the configured `Schema`.
`KeyFields`	String	Exact, comma-delimited key column names used to identify rows.
`Items`	Array	The rows to update. Each row must contain a non-null value for every key field.

Return	Type	Description
`Response`	Array	One response per executed batch, including `Flowgear` status and request details, `RowsAffected`, `RowsUpdated`, `Code`, `SqlState`, and `StatementHandle`.

Delete

Deletes rows from an exact Snowflake table by using the supplied key fields.

Parameter	Type	Description
`Connection`	Connection	The Snowflake `Connection`.
`TableName`	String	The exact, case-sensitive name of the target table in the configured `Schema`.
`KeyFields`	String	Exact, comma-delimited key column names used to identify rows.
`Items`	Array	Rows containing only the non-null key values for the rows to delete.

Return	Type	Description
`Response`	Array	One response per input item, including `Flowgear` status and request details, `RowsAffected`, `RowsDeleted`, `Code`, `SqlState`, and `StatementHandle`.

Merge

Updates a matching row or inserts a new row by using the supplied key fields.

Parameter	Type	Description
`Connection`	Connection	The Snowflake `Connection`.
`TableName`	String	The exact, case-sensitive name of the target table in the configured `Schema`.
`KeyFields`	String	Exact, comma-delimited key column names used to match rows.
`Items`	Array	The rows to merge. Each row must contain a non-null value for every key field.

Return	Type	Description
`Response`	Array	One response per input item, including `Flowgear` status and request details, `RowsAffected`, `RowsInserted`, `RowsUpdated`, `Code`, `SqlState`, and `StatementHandle`.

Usage Notes

Use CustomParameters for external values in Query. Every supplied parameter must be referenced by the SQL statement.
A CustomParameters must be @Name. Reference as @Name in Query.
Snowflake stage references such as @stage_name/path, @%table_name, and @~ remain unchanged when they do not match a CustomParameters entry. Use @@stage_name to keep a stage reference literal when a same-named CustomParameters entry is present.
Table, column, and KeyFields names are matched case-sensitively against Snowflake metadata.
Insert and Update group same-shape items into batches. A change in item Properties starts a new batch. Repeated Update keys also start a new batch to preserve input order.
Insert and Update return aggregate row counts for each executed batch. Their Flowgear.Request value contains the items included in that batch.
Delete accepts key fields only. Remove all non-key Properties from each input item.
Template key fields are suggestions based on primary or unique key metadata. Supply the exact KeyFields required by the Workflow.