Snowflake v2

Provides integration with Snowflake using the Snowflake SQL API for parameterized queries, stored procedures, SQL scripts, 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.

These policies apply to every relevant mutation using the Connection and cannot be overridden in a Node's Options, even with the same value. They do not govern Delete or SQL issued through Query or Run Stored Procedure. Use separate Connections when Workflows need different policies.

Connection labels use PascalCase. Existing Connection profiles retain their stored credential keys, including Client ID.

Setup Notes

1. 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.
2. Create or identify the Snowflake user and role for the Connection. Grant the role named in Role to the user named in UserName. 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>;

3. 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.

4. Obtain the client ID and client secret from the Snowflake security integration.
5. Create a Snowflake Connection in the Flowgear Console. Enter Url, AccountIdentifier, UserName, Warehouse, Database, Schema, ClientId, ClientSecrets, and Role exactly as configured in Snowflake.
6. Click Connect your Account, then complete the Snowflake sign-in and consent flow. Flowgear stores the returned AccessToken and RefreshToken on the Connection.
7. 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, calling stored procedures, and inserting, updating, deleting, or merging table rows. Table templates are generated from accessible base-table metadata in the configured Schema. Query also offers Blank Query, and Run Stored Procedure offers templates for accessible procedure overloads.

Each table has a Default template named {Method} {TableName} and an Advanced template named {Method} {TableName} (Advanced) for Insert, Update, and Merge. Both use the same Method and enforce the Connection policies. Advanced expands table-specific Options and CustomParameters. Default Insert also exposes Options.MatchFields when the Connection requires SkipExisting. Refresh templates after changing Connection policies, and edit or remove generated expression examples before use.

Query

Executes a Snowflake SQL statement or script and streams the returned rows. See SQL scripts for script limits and result metadata.

Run Stored Procedure

Executes a stored procedure using a generated CALL statement. Select the template for the required overload and supply its typed CustomParameters. This Method supports the same parameter binding, SQL scripts, and result streaming as Query.

Insert

Inserts connected items into an exact Snowflake table.

Update

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

Delete

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

Merge

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

Usage Notes

• Use CustomParameters for external values in Query and Run Stored Procedure. Keys may include or omit @; reference them as @Name in SQL. Every supplied parameter must appear in the SQL; references only in comments do not consume binding positions.
• 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.
• With default Connection policies and empty Options and CustomParameters, Insert and Update group same-shape items into batches and return aggregate row counts for each batch. A change in item Properties starts a new batch. Repeated Update keys also start a new batch to preserve input order. Flowgear.Request 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. Informational Snowflake constraints do not guarantee that keys are unique.

SQL scripts

Query and Run Stored Procedure execute a script in one request with shared session context. A script may contain at most one SELECT query, including one starting with WITH. Subqueries, CTEs, and UNION within that query are allowed. Multiple separate SELECT queries are rejected before SQL is submitted; use a separate Node invocation for each query.

Script results stream in statement order, including provider status rows. Each row includes the zero-based Flowgear.StatementIndex to identify its statement. Flowgear is reserved for this metadata. Single-statement results remain untagged, including statements ending in a semicolon and comments.

The Node waits for script completion and retrieves the first statement's first result partition before returning its stream. Later result retrieval occurs as the stream is consumed. A failing statement stops the script and raises the provider error. Earlier effects are not automatically rolled back; use explicit transactions where needed.

Named values remain SQL API bindings in scripts. Snowflake may reject script bindings; the Node reports provider errors and never substitutes values into SQL text.

Mutation options

Insert, Update, and Merge accept the following Options. Names are case-sensitive. Unknown, inapplicable, null-valued, or Connection policy options are rejected. Omit an option to use its default behavior.

Field lists use CSV strings, for example, CUSTOMER_ID,COMPANY_NAME. Separator spaces and empty entries are accepted; quote names containing commas or significant surrounding whitespace. Existing array inputs are also accepted.

Missing fields differ from explicit nulls. Missing Insert fields remain omitted so database defaults apply. Missing Update fields remain untouched. Supplied nulls follow the Connection's NullBehavior for direct update mappings; assignment expressions define their own null behavior.

Field lists select available source fields without creating missing values. SourceExpressions can create a field absent from the input. Assignment expressions override direct mappings and can supply fields independently of field lists. Generated columns cannot be assignment destinations, and assignment expressions cannot change matching keys.

Use input."FIELD" for raw source conversion, source."FIELD" for transformed assignments, and target."FIELD" where target values are supported. SQL fragments must be individual expressions or predicates. Unquoted semicolons and positional ? placeholders are rejected. Use CustomParameters for external values, referenced as @Name. Unlike query parameters, unused mutation parameters are allowed because enabled branches and expressions can differ.

Advanced templates include an example expression for a writable non-key column. Edit or remove it before running the Node. Replace or remove COLUMN_NAME when no suitable column exists. Review suggested required fields, comparison fields, and readback fields for your table and Workflow.

Matching and returned values

Matching keys must identify at most one target row. Ambiguous matches and failed required-field or concurrency checks fail and roll back the configured batch. Unmatched Update keys are successful zero-row results. Duplicate checks do not create a database uniqueness constraint; concurrent inserts need enforced uniqueness or external coordination.

ReturnFields reads stored values within the write transaction and must find exactly one target row. Insert therefore needs supplied business keys in MatchFields, even when the returned value is a generated ID. An input identifiable only by its generated ID cannot use readback. Skipped or failed items do not include ReturnedFields.

Transactions and responses

Non-empty Options or CustomParameters, or a non-default applicable Connection policy, enable configured mutations. These use temporary staging tables and require permission to create temporary tables in the configured schema. Staging occurs before the transaction; target writes, outcome checks, and readback occur within it.

PerBatch commits each internally sized batch. A later failure leaves earlier commits intact. AllOrNothing buffers the complete invocation and executes one transaction in one SQL API request. Inputs exceeding the row or serialized request limits are rejected before target writes. Requests must fit within 1 MB, including generated SQL and bound data; Insert and Update batching also respects Snowflake's 200,000-row VALUES limit. Batch sizing is automatic and is not exposed as an option.

Configured mutations emit one response per input item, in input order, after its batch commits. Responses include Flowgear.IsSuccess, Flowgear.Message, the original input in Flowgear.Request, actual row counts, and Outcome (Inserted, Updated, Skipped, or NotMatched). ReturnedFields is present only when requested and a write occurred. Insert and Update echo a one-item array; Merge echoes an object. Mutation CustomParameters are excluded from response metadata.

The first mutation request completes before the Node returns its stream; failure raises an error. Later request failures produce Flowgear.IsSuccess=false, the error in Flowgear.Message, the original request, and zero RowsAffected, then processing continues. Configured transaction failures produce an error response for each item in the failed batch. Cancellation propagates. AllOrNothing uses the first and only request, so failure raises an error and rolls back its target writes. A transport failure can leave the commit outcome unknown; failed writes are not automatically replayed.

Examples

Query with external values

Set Query to:

SELECT CUSTOMER_ID, COMPANY_NAME
FROM CUSTOMERS
WHERE CUSTOMER_ID = @customer_id

Set CustomParameters to:

{
  "customer_id": 123
}

SQL script with shared session context

Set Query to:

SET v = 2;
SELECT ID, NAME FROM CUSTOMERS WHERE ID = $v

The two statements share session context. Use Flowgear.StatementIndex to distinguish the SET status rows from the SELECT results.

Merge customers with audit values

Enable UpdateOnlyWhenChanged on the Connection and keep Atomicity set to PerBatch. Select the Advanced Merge template for your customer table and set KeyFields to CUSTOMER_ID. The table must contain the columns referenced below; adjust the field lists and expressions to match your schema.

Connect customer objects to Items, then set Options to:

{
  "RequiredFields": "CUSTOMER_ID,CUSTOMER_CODE,COMPANY_NAME",
  "InsertFields": "CUSTOMER_ID,CUSTOMER_CODE,COMPANY_NAME,SOURCE_IS_DELETED",
  "UpdateFields": "CUSTOMER_CODE,COMPANY_NAME,SOURCE_IS_DELETED",
  "CompareFields": "CUSTOMER_CODE,COMPANY_NAME,SOURCE_IS_DELETED",
  "InsertCondition": "NOT source.\"SOURCE_IS_DELETED\"",
  "InsertExpressions": {
    "FG_CREATED_AT": "TO_TIMESTAMP_NTZ(@sync_at)",
    "FG_UPDATED_AT": "TO_TIMESTAMP_NTZ(@sync_at)",
    "FG_RUN_ID": "@run_id"
  },
  "UpdateExpressions": {
    "FG_UPDATED_AT": "TO_TIMESTAMP_NTZ(@sync_at)",
    "FG_RUN_ID": "@run_id"
  },
  "ReturnFields": "CUSTOMER_ID,FG_CREATED_AT,FG_UPDATED_AT,FG_RUN_ID"
}

Set CustomParameters to:

{
  "sync_at": "2026-09-23 12:00:00.123456",
  "run_id": "customer-sync-123"
}

This configuration preserves creation timestamps on updates, keeps audit values out of change detection, skips inserting unmatched soft-deleted customers, and returns stored audit values after each write. It does not delete target rows that are absent from Items.