Snowflake Stored Procedure transfer object

The Snowflake Stored Procedure transfer object calls a stored procedure in the Snowflake AI Data Cloud. Input parameters, return values, and, if applicable, a result set can be used as destinations or sources within the flow.

Dialog Overview

General Properties

Property	Description
Data Source	All Snowflake connections created in the Plug-ins section are listed here. If the connection you want is not yet available, you can make it available in the Plug-ins section as a “Snowflake AI Data Cloud” plug-in .
Stored Procedure	Stored procedure to be executed.
Columns/Parameters	Input and return parameters of the procedure that are to be connected to the transfer object as sources or destinations.
Query the result set	If return values in the form of a result set are expected, they can be read using “Query result set” and then made available as a source to the transfer object.

Special Features in the OPC Router

• The OPC Router reads the available parameters of the procedure and makes them available as usable elements in the transfer object.
• When “Query result set” is executed, the OPC Router calls the procedure once to determine the structure of the result set. This call takes place within a transaction that is subsequently rolled back.
• To determine the structure, the OPC Router calls the procedure with default parameters (NULL; for parameters that do not allow the NULL character, e.g., 0 for numeric values or an empty string for strings).

note

The procedure must be transaction-safe and must not return any errors during the standard call for structure detection.

Example

The following stored procedure writes a telemetry measurement value to the MACHINE_TELEMETRY table and returns a status based on the temperature. The transfer object shown above is configured for this procedure.

CREATE OR REPLACE PROCEDURE PRODUCTION.SP_LOG_TELEMETRY(
    MACHINE_ID  VARCHAR,
    TEMPERATURE FLOAT,
    PRESSURE    FLOAT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    INSERT INTO MACHINE_TELEMETRY (MACHINE_ID, TEMPERATURE, PRESSURE, INGESTED_AT)
        VALUES (:MACHINE_ID, :TEMPERATURE, :PRESSURE, CURRENT_TIMESTAMP());

    IF (:TEMPERATURE > 80) THEN
        RETURN 'WARN_HIGH_TEMP';
    ELSE
        RETURN 'OK';
    END IF;
END;
$$;

In the transfer object, you bind the three input parameters (MACHINE_ID, TEMPERATURE, PRESSURE) as targets; the return value is available as a source. The procedure is transaction-safe: During the standard call for structure recognition, only a data record with default values is inserted and then rolled back, and OK is returned.