Snowflake

Pre-requisites

The only pre-requisite is that a user exists with permissions to create and write to a table in the specified database.

It is recommended that this user only has write-only access to the table.

Setting up a write-only user

The following are example commands to create a user with write-only access:

-- Creating user
create user user1;

-- Creating a role
create role write_only comment = 'This role has writeonly access';

-- Granting writeonly access to the role on selected warehouse, schemas and tables
grant usage on database VALIDIO_DB to role write_only;
grant usage on schema VALIDIO_DB.DEMO_DATA to role write_only;
grant create stage on schema VALIDIO_DB.DEMO_DATA to role write_only;
grant create table on schema VALIDIO_DB.DEMO_DATA to role write_only;
grant select on all tables in schema VALIDIO_DB.DEMO_DATA to role write_only;
grant usage on warehouse COMPUTE_WH to role write_only;
grant select on future tables in schema VALIDIO_DB.DEMO_DATA to role write_only;

-- Set the users role
grant role write_only to user user1;
alter user user1 set default_role = write_only;

-- Set a password for the user
alter user user1 set password = 'Validio2022';

Snowflake destination configuration parameters

⚠️

Validio currently supports unquoted identifiers for the following fields:

  • Role
  • Warehouse name
  • Database name
  • Schema name
  • Table name

Please let us know if you have a use-case which require quoted identifiers!

Field Required Notes
Name Identifier for the connector. Used when setting up pipelines.
Account Snowflake account identifier including '.snowflakecomputing.com' suffix.
Role Role of the user account that has access to destination where data should be egressed to. Must be a valid identifier.
Warehouse name Name of the Snowflake warehouse to egress data to. Must be valid Snowflake identifier.
Database name Name of the database to egress data to. Must be valid Snowflake identifier.
Username Username of the account that will be used by the Validio platform to access the Snowflake table.
Password Password of the account that will be used by the Validio platform to access the Snowflake table.
Schema name Schema that the table resides in. Must be a valid identifier.
Table name The name of the table to write to. This will be created automatically by the Validio platform. Must be a valid identifier.

Destination format

Column name Type Description
_validio_uuid VARCHAR Unique ID to identify the record.
_validio_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp() Time that the record was written.
_validio_is_anomaly BOOLEAN Indicate whether the datapoint is an anomaly.
_validio_applied_filters VARIANT JSON list of applied filters to the datapoint and whether it was detected as an anomaly.
_validio_datapoint VARIANT The data contained in the processed datapoint.

The _validio_applied_filters will resemble a JSON list of objects indicating what filters were applied and whether it was detected as an anomaly.

[
  {
    "filter_id": "<FILTER_ID>",
    "is_anomaly": "<TRUE |FALSE>"
  }
]

E.g.
[
  {
    "filter_id": "FTR_abcdefghijklm",
    "is_anomaly": false
  },
  {
    "filter_id": "FTR_mlkjihgfedcba",
    "is_anomaly": true
  }
]