Importing Data from Snowflake into Assets

Snowflake is where a lot of organizations keep their operational metrics e.g equipment registries, meter readings, customer records, work order history. This post walks through getting that data into Assets using OnLink.

We’ll cover: authenticating to Snowflake, executing the query through the SQL API, and mapping the result set to Asset attributes.

Snowflake Authentication

OnLink supports OAuth authentication to Snowflake.

Snowflake’s SQL API accepts three authentication methods — OAuth, key-pair (JWT), and workload identity federation. OnLink uses OAuth, which means you configure an OAuth integration in Snowflake, OnLink obtains a token, and that token rides along on every API request.

Reference: Authenticating to the server — Using OAuth

What Snowflake needs

1. Set up OAuth in Snowflake. Snowflake supports both Snowflake OAuth (built-in authorization server) and External OAuth (Okta, Entra ID, Ping, and other providers). See Snowflake’s Introduction to OAuth for creating the security integration and obtaining a token.

2. Verify the token works before wiring it into OnLink:

   snow connection test --account <account_identifier> --user <user> --authenticator=oauth --token=<oauth_token>
   

3. Send the token on every request. Snowflake expects these headers:

   Authorization: Bearer <oauth_token>
   X-Snowflake-Authorization-Token-Type: OAUTH
   

   The X-Snowflake-Authorization-Token-Type header is optional — Snowflake will inspect the token and infer the type — but setting it explicitly avoids ambiguity and makes troubleshooting easier.

What to configure in OnLink

Endpoint Configuration

Executing the Snowflake Query

Once authentication is in place, OnLink submits the query as an HTTP POST to Snowflake’s SQL API statements endpoint.

Reference: Submitting a request to execute SQL statements

The endpoint

POST https://<account_identifier>.snowflakecomputing.com/api/v2/statements

Replace <account_identifier> with your Snowflake account identifier — for example, myorg-myaccount.

The request body

Snowflake expects a JSON body describing the statement and its execution context:

{
  "statement": "select * from T where c1=?",
  "timeout": 10,
  "database": "TESTDB",
  "schema": "TESTSCHEMA",
  "warehouse": "TESTWH",
  "role": "TESTROLE",
  "bindings": {
    "1": {
      "type": "FIXED",
      "value": "123"
    }
  }
}

Walking through each field:

On bindings: each binding needs a type and a value, and the value is always a string — even for numbers. FIXED is the binding type for integers; TEXT for strings and for dates you want Snowflake to auto-parse; REAL for floats; BOOLEAN for true/false. Use bind variables rather than string-concatenating values into the statement — it’s cleaner and it avoids injection problems when the value comes from an Asset field or a user parameter.

On case sensitivity: this is the single most common cause of “object does not exist” errors. If you created your database as CREATE DATABASE TestDB without quotes, Snowflake stored it as TESTDB. Put TESTDB in the config, not TestDB.

Adding the config to OnLink

OnLink takes the request body as a single-line configuration value. Multi-line JSON won’t parse. So the body above becomes:

config:http_body={"statement":"select * from T where c1=?","timeout":10,"database":"TESTDB","schema":"TESTSCHEMA","warehouse":"TESTWH","role":"TESTROLE","bindings":{"1":{"type":"FIXED","value":"123"}}}

To flatten your own JSON, paste it into a converter such as jsonformatter.org/json-to-one-line and copy the single-line output into the config:http_body value.

Tip: Compose and validate your JSON in expanded form first, confirm the query works with a curl call against Snowflake, and only then flatten it. Debugging a broken single-line string is unpleasant.

Verifying the call outside OnLink

Before trusting it to a scheduled import, run the same request by hand:

curl -i -X POST \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer <oauth_token>" \
    -H "X-Snowflake-Authorization-Token-Type: OAUTH" \
    -H "Accept: application/json" \
    -H "User-Agent: OnLink/1.0" \
    -d "@request-body.json" \
    "https://<account_identifier>.snowflakecomputing.com/api/v2/statements"

Mapping Query Results to Asset Fields

Snowflake returns results in a structured JSON envelope: resultSetMetaData describes the columns, and data holds the rows as arrays of string values in column order.

Example query

SELECT CUSTOMERID, CUSTOMERNAME, SITEADDRESS, METERSERIAL, INSTALLDATE, STATUS
FROM TESTDB.TESTSCHEMA.CUSTOMER_ASSETS
WHERE REGION = ?

Example response

{
  "resultSetMetaData": {
    "numRows": 2,
    "format": "jsonv2",
    "rowType": [
      { "name": "CUSTOMERID",   "type": "fixed" },
      { "name": "CUSTOMERNAME", "type": "text" },
      { "name": "SITEADDRESS",  "type": "text" },
      { "name": "METERSERIAL",  "type": "text" },
      { "name": "INSTALLDATE",  "type": "date" },
      { "name": "STATUS",       "type": "text" }
    ]
  },
  "data": [
    ["10241", "Northgate Utilities", "412 Marlin Ave, Tampa FL", "MTR-88213", "2021-06-14", "ACTIVE"],
    ["10242", "Bayside Holdings",    "77 Pier Rd, Clearwater FL", "MTR-88907", "2022-11-02", "ACTIVE"]
  ],
  "code": "090001",
  "statementHandle": "01b2c3d4-0000-1a2b-0000-abcd0000e1f2"
}

OnLink mapping

OnLink maps each Snowflake column to an Asset field using key: syntax — the Snowflake column name on the left, the Asset field on the right:

key:customerid=Customer ID
map:customername=Customer Name
map:siteaddress=Site Address
map:meterserial=Serial Number
map:installdate=Installation Date
map:status=Asset Status

Read left to right: take the value in the customerid column and write it to the Asset field named Customer ID.

Applied to the first row of the result set:

Putting It Together

The full configuration for the example above:

Endpoint

https://<account_identifier>.snowflakecomputing.com/api/v2/statements

OAUTH Connection

config:http_header={"Authorization":"Bearer <oauth_token>","X-Snowflake-Authorization-Token-Type":"OAUTH","Content-Type":"application/json","Accept":"application/json"}

Body

config:http_body={"statement":"SELECT CUSTOMERID, CUSTOMERNAME, SITEADDRESS, METERSERIAL, INSTALLDATE, STATUS FROM CUSTOMER_ASSETS WHERE REGION = ?","timeout":10,"database":"TESTDB","schema":"TESTSCHEMA","warehouse":"TESTWH","role":"TESTROLE","bindings":{"1":{"type":"TEXT","value":"SOUTHEAST"}}}

Field mapping

key:customerid=Customer ID
map:customername=Customer Name
map:siteaddress=Site Address
map:meterserial=Serial Number
map:installdate=Installation Date
map:status=Asset Status

Troubleshooting

Give OnLink a Try

If you have use cases that need data from Snowflake to Assets, give OnLink a try.

References

• OnLink Documentation
• Snowflake — Authenticating to the server (OAuth)
• Snowflake — Submitting a request to execute SQL statements
• Snowflake — Introduction to OAuth
• Snowflake — SQL API reference
• JSON to single line converter