Snowflake Driver

The Snowflake driver streams OAS tag data directly into a Snowflake table using the Snowpipe Streaming REST API v2. There are no staging files, no COPY INTO job and no warehouse running for ingestion. Rows are appended over HTTPS and are queryable within seconds of leaving the engine.

Overview

Every row the driver sends carries an offset. Snowflake remembers the last offset it committed for the channel, and on reconnect the driver asks where it got to and resumes from there. A driver disable, an engine restart or a machine reboot therefore neither loses rows nor repeats them.

Because Snowflake rows are columns rather than documents, this driver has no message format to design. The table is the contract. A Column Mapping names each column, its Snowflake type and which tag property fills it, and the mapping editor generates the matching CREATE TABLE statement for you.

The mapping is also ISA-95 aware: any column can be filled from the tag's position in an equipment hierarchy, such as Enterprise, Site, Area, Work Center, Work Unit, all resolved by walking the groups enclosing the tag. An ISA-95 Lite preset lays out a unified namespace table in one click.

Minimum Configuration Requirements

These are the driver fields that need a value before the driver will connect. Everything else on the driver has a working default.

Pipe, Channel Name, Account Host Override and Ingest Host Override are left blank on a normal installation. Each is described under Configuration.

Requirements

• An OAS installation licensed for the Snowflake option
• A Snowflake account with Snowpipe Streaming available
• A Snowflake user, role and target table, created by a Snowflake administrator
• Outbound HTTPS from the OAS engine to your Snowflake account host and streaming host

Preparing Snowflake

OAS does not browse or create objects in your account. These steps are performed in Snowflake, by a Snowflake administrator.

Account identifier

The account identifier is the whole subdomain of your Snowflake URL: everything before .snowflakecomputing.com.

https://abcdefg-xy12345.snowflakecomputing.com
        └─────────────┘
        the Account Identifier

In Snowsight, the Snowflake web interface, go to Admin → Accounts, open the ... menu on your account and choose Manage URLs.

User and role

Manually create a Role and a User in Snowsight, or use the SQL below to generate them. The names OAS_STREAM_ROLE and OAS_SVC below are examples and used throughout this documentation. Substitute your own.

CREATE ROLE  IF NOT EXISTS OAS_STREAM_ROLE;
CREATE USER  IF NOT EXISTS OAS_SVC
    DEFAULT_ROLE = OAS_STREAM_ROLE
    MUST_CHANGE_PASSWORD = FALSE;

GRANT ROLE OAS_STREAM_ROLE TO USER OAS_SVC;
ALTER USER OAS_SVC SET TYPE = SERVICE;

Target table

The Snowflake Driver needs a target database, schema, and table for writing data. In the OAS Configuration, you will be mapping data values to Snowflake table columns. As a convenience feature, the OAS Configuration Application will provide the SQL statement to create the table within the configured database and schema based on the specified columns and data types.

In the example below, the most basic OAS Tag properties are mapped to table columns, and the tool provides convenient presets for filling in column mappings, including a preset for ISA-95 style Equipment Hierarchy fields.

Additionally, there are Snowflake Metadata Columns you can map that assist with tracking data through Snowpipe. These are highly recommended, and they use specific Column Names that cannot be altered.

OAS Configuration Application - Column Mapping Interface

OAS Configuration Application - Generated Table Creation SQL

If the table already exists in SNOWFLAKE, leave it alone and point the driver at it, then make the Column Mapping match its columns rather than the other way round. OAS never alters or drops an existing table.

Grants

Specific minimum privileges are required for the connected user account to write data into Snowflake from OAS. These are USAGE on the DB and schema, and INSERT on the table. The following is how you can apply these in SQL.

GRANT USAGE  ON DATABASE OAS_DB                         TO ROLE OAS_STREAM_ROLE;
GRANT USAGE  ON SCHEMA   OAS_DB.OAS_DATA                TO ROLE OAS_STREAM_ROLE;
GRANT INSERT ON TABLE    OAS_DB.OAS_DATA.OAS_TAG_VALUES TO ROLE OAS_STREAM_ROLE;

The pipe

Snowflake creates a default pipe for every table, named <TABLE>-STREAMING, when the channel is first opened. Leave the driver's Pipe field blank to use it. Set a name only where a custom pipe performs in-flight transformation. A custom pipe needs MATCH_BY_COLUMN_NAME = CASE_SENSITIVE and GRANT OPERATE ON PIPE to the role.

Authentication

The two options for authentication between OAS and Snowflake are Key Pair (JWT) and Programmatic Access Token (PAT). The following describes the various features and limitations of each type. It is highly recommended to use a Key Pair in a production environment as there is no expiration. This will reduce the chances of the driver failing unexpectedly.

Key pair

If you do not already have a key pair, use Generate Key… beside Private Key Path in the driver. It writes to the Private Key PEM field or to a file on the engine machine - the Browse… button browses the engine machine, not your workstation - with an optional passphrase that is filled in for you.

The dialog then produces two things:

1. The ALTER USER statement with the public key already in it, BEGIN/END markers stripped. It is shown once. Copy it before closing the dialog.
2. The key's fingerprint, for comparing against Snowflake.

To generate a key pair yourself instead:

# unencrypted
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -outform PEM -nocrypt -out rsa_key.p8

# encrypted
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -outform PEM -v2 aes-256-cbc -out rsa_key.p8

Register the public key as one line with the markers removed:

ALTER USER OAS_SVC SET RSA_PUBLIC_KEY='MIIBIjANBgkq…IDAQAB';

Key rotation uses RSA_PUBLIC_KEY_2 and does not interrupt streaming.

Supported key formats

The last form is what openssl genrsa -aes256 produces, and .NET cannot read traditional OpenSSL encryption. Convert it:

openssl pkcs8 -topk8 -in rsa_key.pem -out rsa_key.p8

The fingerprint does not change, so the key does not need re-registering.

Verifying the fingerprint

DESC USER OAS_SVC;
SELECT VALUE FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE PROPERTY = 'RSA_PUBLIC_KEY_FP';

The value must match the fingerprint shown by the Generate Key dialog, character for character. Identifiers in these statements must be unquoted.

Programmatic access token

In Snowsight, open the user under Admin → Users & Roles → Users, find Programmatic access tokens and choose Generate new token. The secret is displayed once and cannot be retrieved - copy it immediately.

A service user additionally needs a network policy, or an authentication policy carrying PAT_POLICY = ( NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED ). Key-pair authentication has no such requirement.

The User field disappears when this option is selected, because the token identifies its own user.

Configuration

Below is a comprehensive list of each configuration option on the Snowflake Driver with a description of its purpose and what effect it will have.

Connection

Target

Publishing

There is no Publish Selected Tags switch on this driver - publishing is all it does, so the switch is hidden.

Batching and transport

Rows accumulate and are sent when any of three limits is reached.

Test Connection

Test Connection performs exactly three checks and reports each separately, along with the account host it built, the ingest host Snowflake returned and the User-Agent it sent.

Column mapping

Sources

Each column takes a source, and that source - with the column's format - is what actually fills the row.

The ISA-95 levels available are Enterprise, Site, Site ID, Area, Work Center, Work Center Type, Work Unit, Work Unit Type, Work Unit ID, Equipment Module, Equipment Module ID, Control Module and Control Module ID.

Formats

Timestamp columns take ISO 8601, Unix seconds, Unix milliseconds, ticks, or a custom .NET format string. Value columns take as-is, force to string, parse as numeric, round to integer, true/false, 0/1, or a custom pair of boolean words. Timestamps are sent as UTC.

Presets

Two presets are built in, served from the engine so that the editor and the driver cannot hold different ideas of what they mean.

Default - four columns matching the table above:

The identifier column carries the full tag path, not the short name: names are not unique across groups, and using one as a row key silently merges different tags.

ISA-95 Lite - 24 flattened columns: timestamp, namespace path, tag name, group path, all thirteen ISA-95 levels, three typed value columns, data type, quality, units and source protocol. The hierarchy is flattened one column per level because Snowflake columns are flat and a dotted column name would have to be quoted forever after. The typed value columns (value_double, value_string, value_boolean) are split rather than sharing one VARIANT because Snowflake sub-columnarizes a VARIANT only along paths that are never null.

A preset row is byte for byte an ordinary row. Applying a preset is an editor action, not a mode - afterwards you can edit, add and remove columns freely. Clearing the mapping means no columns, not "fall back to Default".

Ingestion metadata columns

+ Metadata Columns adds three entries whose name, type and source are locked in the editor.

Snowflake recommends these on any streaming table, and they are what makes gap detection possible in SQL:

SELECT STREAM_OFFSET,
       LAG(STREAM_OFFSET) OVER (PARTITION BY PIPE_ID, CHANNEL_ID
                                ORDER BY STREAM_OFFSET) AS previous_offset
FROM   <db>.<schema>.<table>
QUALIFY STREAM_OFFSET <> previous_offset + 1;

They are locked because that query names them literally, and STREAM_OFFSET has to stay numeric or previous_offset + 1 fails outright. The driver binds them by source rather than by name, so a mapping that arrived through a CSV import or a hand edit still works.

SQL View

SQL View shows the CREATE TABLE statement the mapping describes, generated by the engine, so that what you hand a DBA is the same statement the driver would have used itself.

Table creation

Create Table If Missing is off by default. Ticked, the driver creates the target table from the column mapping on connect if it is not there.

• It needs CREATE TABLE on the schema - a privilege streaming alone does not require.
• OAS never alters or drops an existing table. A table whose columns do not match the mapping is left exactly as it is, and reported by preflight.
• If the table cannot be created the driver stops, reports the error and sends nothing.

Store and Forward

Tick Enable Store and Forward. Rows that cannot be sent are written to disk and replayed when the connection returns, oldest first, always. Buffer files are plain UTF-8 NDJSON with a .sfb extension, in the directory set under Configure → Options → Store and Forward.

Last In First Out is deliberately not offered on Snowflake. Snowpipe Streaming is an ordered protocol - a channel's rows are identified by a monotonic offset - so replaying the newest file first would land recent rows ahead of older ones under offsets that say the opposite.

A buffered row carries no offset of its own. It takes one when it is finally sent, which is what makes it land after whatever Snowflake actually kept.

Validation and row errors

An append returning success means Snowflake accepted the batch, not that it kept it. Rows are validated asynchronously against the table, so a wrong column mapping streams happily and writes nothing at all. Two mechanisms close that hole, and they catch different faults.

Rejected rows cannot be resent. Fix the mapping or the table.

Preflight

On every connect - and again after any mapping change - the driver compares the mapped column names against the table's actual columns and reports the ones that are missing, by name. A mapping change does not reconnect the driver, so a connect-only check would leave the alarm latched after the operator fixed the very thing it complained about.

Preflight is advisory and never blocks a connect. Reading table metadata needs a privilege that streaming does not, so a least-privilege credential that streams perfectly well can be refused it. An unreadable answer is treated as "could not check", never as "the table has no columns". The comparison is case-insensitive, because Snowflake upper-cases unquoted identifiers, and it compares names only, never types.

Validation

Validation polls the channel's status and reports what Snowflake actually did with the rows.

A one-shot check runs whether or not validation is enabled, about ten seconds after the first append of each connect. That is why a broken mapping is reported on the first attempt rather than never. It honours the On Row Errors setting even though that field is hidden while validation is off.

Five optional metric tags can be written on each poll:

The counters are cumulative for the life of the channel, and the channel outlives the driver. The driver therefore measures errors from the moment it connects, so old rejections are not re-raised on every enable. A count that has gone backwards means a new channel.

Delivery guarantees

The channel carries the memory of what has been committed, so the driver does not delete its channel when it stops. It drops a channel only when abandoning its identity - a change of account, host, credential, database, schema, table, pipe, channel name, compression or request timeout - because that channel then names something it will never write to again.

Verify delivery with both checks together, since a duplicate and a loss cancel out in a gap count alone:

SELECT COUNT(*)                      AS rows_kept,
       COUNT(DISTINCT STREAM_OFFSET) AS distinct_offsets,
       MIN(STREAM_OFFSET)            AS first_offset,
       MAX(STREAM_OFFSET)            AS last_offset
FROM   <db>.<schema>.<table>
WHERE  CHANNEL_ID = '<channel>';

rows_kept equal to distinct_offsets, and last_offset - first_offset + 1 equal to both, is the proof. Observed commit latency is typically 5 to 7 seconds.

Operating the driver

Comm status. The usual Comm Bad, Comm Good and Comm Buffering driver tags behave as they do on every driver, so alarms need no new configuration.

System errors are latched per category and clear themselves when the condition goes away. Licensing, configuration problems, preflight mismatches and row rejections each report with the driver name, the target table and, where Snowflake supplied one, its own message.

Logging is opt-in. Tick Configure → Options → System Logging → Log Snowflake Communications to record the driver's activity to Log/OASTransactions-Driver-<name>-<date>.txt. It is off by default, so turn it on before trying to diagnose anything from the log.

One line in that log is worth knowing about. When the resolved column mapping changes, the driver writes:

Snowflake rows will be built from N mapped columns: ...

That is the receipt proving a mapping edit actually reached the running engine. It is written only when the mapping changes, so its absence after an edit means the edit did not arrive.

Troubleshooting

Limits and caveats

• Write only. The driver never reads from Snowflake into a tag.
• Flat rows. Snowflake columns are flat and there is no nested output. Use a VARIANT column if you need structure inside one field.
• No second target. An outage is ridden out by Store and Forward, not by streaming to a different table. A deployment with genuine multi-region Snowflake uses Snowflake's own Client Redirect, which needs Account Host Override, not a second target here.
• Idle channels. Snowflake decides how long an idle streaming channel is retained, and can change that. The driver reopens and resumes from the committed offset, so an idle period costs nothing - but no claim is made about how long a channel survives untouched.
• Two engines, one channel name. They will disconnect each other.
• No account browsing. Database, Schema and Table are text fields, by design.
• No role selection. The driver uses the user's default role.
• Credential storage. The private key and any access token are stored in the driver configuration alongside every other driver property, and travel over the configuration API. That API should be on TLS. Encrypting the private key adds little when the key is pasted into the configuration, because the passphrase sits beside it; it is worth doing when the key lives in an external file.

Related How-To guides

• Data Transfer guides - move data between IoT platforms and devices.
• IoT Connectors - the rest of the cloud, broker and streaming connectors.