Skip to main content

Import models and activate versions

Import a complete ONNX artifact, score transactions, and observe a deliberately different candidate version.

The practical problem and model​

A data-science team may train outside the database and want SQL users to score rows with the resulting artifact. This example embeds a small, complete ONNX model directly in SQL, avoiding a missing file or external artifact download.

The artifact computes a logistic score: sigmoid(0.7 × amount_norm + 0.2 × age_norm − 0.4 × txn_count_norm + 0.5 × risk_score + bias). The initial bias is 0.1; the candidate changes it to 0.6. That deliberate difference makes version activation observable.

The Studio tutorial's full name mentions ONNX, PyTorch, and sklearn. The runnable and validated path here is ONNX. PyTorch and sklearn require their own runtime support and compatible artifacts; their names do not mean those integrations were exercised by this script.

Before you run​

Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.

Studio: Tutorial: Importing Pre-trained Models (ONNX / PyTorch / sklearn). Download the complete SQL.

ONNX inference is built into the hosted service, so there is nothing to install. The SQL embeds the complete model artifact. See imported and remote models.

Step 1: Create six scoring inputs​

The ONNX signature uses FLOAT, matching its float32 tensor inputs. The normalized values must retain the same meaning as the artifact's training-time inputs.

CREATE CATALOG IF NOT EXISTS tutorial;

CREATE SCHEMA IF NOT EXISTS tutorial.imported;

CREATE OR REPLACE TABLE tutorial.imported.transactions (
    txn_id          BIGINT,
    customer_id     BIGINT,
    amount_norm     FLOAT,    -- txn amount / 1000  (small=0, large=1)
    age_norm        FLOAT,    -- account age (years) / 5
    txn_count_norm  FLOAT,    -- txns in last 30d / 100
    risk_score      FLOAT,    -- prior risk-model output, in [0, 1]
    occurred_at     TIMESTAMP
);

INSERT INTO tutorial.imported.transactions VALUES
    (1, 1001, 0.12, 0.85, 0.04, 0.97, TIMESTAMP '2026-05-10 09:14:22'),
    (2, 1002, 0.45, 0.20, 0.60, 0.10, TIMESTAMP '2026-05-10 09:17:01'),
    (3, 1003, 0.92, 0.05, 0.02, 0.88, TIMESTAMP '2026-05-10 09:18:55'),
    (4, 1004, 0.05, 0.10, 0.98, 0.05, TIMESTAMP '2026-05-10 09:22:18'),
    (5, 1005, 0.78, 0.40, 0.18, 0.55, TIMESTAMP '2026-05-10 09:25:33'),
    (6, 1006, 0.02, 0.30, 0.85, 0.10, TIMESTAMP '2026-05-10 09:29:07');

Step 2: Import and score the initial artifact​

The base64 literal is the complete artifact. The batch query scores six transactions, and the filtered query keeps scores at least 0.5.

CREATE OR REPLACE MODEL tutorial.imported.fraud_onnx
    (FLOAT, FLOAT, FLOAT, FLOAT)
RETURNS FLOAT
USING FRAMEWORK 'onnx'
AS FROM 'CAgSFWdub2stdHV0b3JpYWwtYTAxMS12MjrJAQoeCgVpbnB1dAoBVxIKbWF0bXVsX291dCIGTWF0TXVsCiEKCm1hdG11bF9vdXQKAWISC3ByZV9zaWdtb2lkIgNBZGQKHgoLcHJlX3NpZ21vaWQSBm91dHB1dCIHU2lnbW9pZBIJZnJhdWRfY2xmKhsIBAgBEAFCAVdKEDMzMz/NzEw+zczMvgAAAD8qDQgBEAFCAWJKBM3MzD1aFQoFaW5wdXQSDAoKCAESBgoACgIIBGIWCgZvdXRwdXQSDAoKCAESBgoACgIIAUIECgAQDQ==';

SHOW MODEL VERSIONS tutorial.imported.fraud_onnx;

SELECT tutorial.imported.fraud_onnx(0.12, 0.85, 0.04, 0.97) AS p_fraud;

SELECT
    txn_id,
    customer_id,
    amount_norm, age_norm, txn_count_norm, risk_score,
    tutorial.imported.fraud_onnx(
        amount_norm, age_norm, txn_count_norm, risk_score
    ) AS p_fraud
FROM tutorial.imported.transactions
ORDER BY p_fraud DESC;

SELECT txn_id, customer_id, p_fraud
FROM (
    SELECT
        txn_id, customer_id,
        tutorial.imported.fraud_onnx(
            amount_norm, age_norm, txn_count_norm, risk_score
        ) AS p_fraud
    FROM tutorial.imported.transactions
) scored
WHERE p_fraud >= 0.5
ORDER BY p_fraud DESC;

Step 3: Stage and activate a different candidate​

VERSION 'candidate' stages the second artifact. Inspect the version list before and after activating LATEST, then compare the same scalar input again.

CREATE MODEL tutorial.imported.fraud_onnx VERSION 'candidate'
    (FLOAT, FLOAT, FLOAT, FLOAT)
RETURNS FLOAT
USING FRAMEWORK 'onnx'
AS FROM 'CAgSFWdub2stdHV0b3JpYWwtYTAxMS12MjrJAQoeCgVpbnB1dAoBVxIKbWF0bXVsX291dCIGTWF0TXVsCiEKCm1hdG11bF9vdXQKAWISC3ByZV9zaWdtb2lkIgNBZGQKHgoLcHJlX3NpZ21vaWQSBm91dHB1dCIHU2lnbW9pZBIJZnJhdWRfY2xmKhsIBAgBEAFCAVdKEDMzMz/NzEw+zczMvgAAAD8qDQgBEAFCAWJKBJqZGT9aFQoFaW5wdXQSDAoKCAESBgoACgIIBGIWCgZvdXRwdXQSDAoKCAESBgoACgIIAUIECgAQDQ==';

SHOW MODEL VERSIONS tutorial.imported.fraud_onnx;

ALTER MODEL tutorial.imported.fraud_onnx ACTIVATE VERSION 'LATEST';

SHOW MODEL VERSIONS tutorial.imported.fraud_onnx;

SELECT tutorial.imported.fraud_onnx(0.12, 0.85, 0.04, 0.97) AS activated_p_fraud;

What to check in the results​

The first scalar score is about 0.694873. The initial batch contains six rows, and the 0.5 filter returns transaction IDs 3, 5, 1, 2 in descending score order.

After candidate activation, the same scalar input scores about 0.789680. The version listing should show the newly activated version. The example explicitly changes the artifact, so an unchanged score would be a useful warning that activation or loading did not do what you expected.

Adapt it to real data​

Validate artifact format, tensor shapes, numeric types, feature ordering, and preprocessing as a single serving contract. Compare database outputs with the training framework on a fixed reference set before promoting a new version.

Use managed artifact storage for realistic model sizes. In a shared workflow, activate a specific reviewed version rather than assuming LATEST still names your candidate after concurrent registrations. Plan rollback and observe serving behavior after promotion.

ONNX inference · Multiple inference runtimes

All tutorials · Setup and troubleshooting

Last updated on