Skip to content

Snowflake

This document provides the specific information relevant to creating Snowfake connections. To understand the overall connection and registration flow see Connections Overview

Create a Snowflake connection

Create a new Snowflake connection from the Connections Page.

Snowflake connection example

You will need to input these properties:

  • Account: Your Snowflake account name can be found in your Snowflake url <accountname>.snowflakecomputing.com .
  • Host: You only need to input this if you're using a custom hostname that different to the standard Snowflake one listed above.
  • Warehouse: The Snowflake Warehouse you wish to connect to, if none is specified the default will be used.
  • Username file: Link to your Snowflake username secret file. For more info see Providing Secure Credentials.
  • Password file: Link to your Snowflake username secret file. For more info see Providing Secure Credentials.
  • Default Database: Specify a default Snowflake database for this connection. This will be used to test the connection.

Warning

Snowflake object names are sensitive to case, so please specify your database and schema name in the correct case.

Querying data in Snowflake

Okera's Gateway connector pushes down full queries (including joins and aggregations) to Snowflake, while enforcing the complete access policy, as well as audit log entries. Note this connector is designed for data read/SELECT queries and not INSERT operations or DDL operations on the underlying Snowflake database.

Users, such as data analysts, can connect their favorite SQL tool (e.g. DBeaver, Tableau, Looker) via Okera’s ODBC/JDBC endpoint, and their queries will be transparently sent to Snowflake, after being authorized and audited by Okera (and if the user does not have permission to access the data they are trying to access, the query will be rejected). With this connector you get the benefit of Snowflake's native performance scale and Okera's complete policy and auditing capabilities.

Okera's Snowflake connector currently supports these BI tools:

  • DBeaver
  • Tableau
  • Looker
  • PowerBI

Note

The entire query must only access data in Snowflake and in a single connection. Any queries joining other data (e.g. S3) to Snowflake will not be pushed down. Some predicates will still be pushed down by Okera’s scan, but not the full set.

Okera Snowflake Integration

Supported privacy functions

We broadly support mask, mask_ccn, null, sha2, tokenize, and zero as privacy functions for snowflake pushdown. You will need to run the script we provide to ensure these functions are installed in Snowflake.

Warning

These functions should not be shared with end users and only a Snowflake admin should create/use them.

Datatype limitations

  • mask, mask_ccn only supported for STRING,CHAR, VARCHAR types
  • tokenize supported for CHAR, VARCHAR, STRING and INT types. tokenize preserves format and referential integrity for INT and STRING
  • TIMESTAMP and DATE support yet to be implemented for all the functions

Known SQL incompatibilities:

  • TRY_CAST functions not supported yet.
  • LIMIT ALL not supported in snowflake.
  • Wildcards from two tables which have same column name would throw error. Example, if both a and b had same column names in each, the below query would throw duplicate column error SELECT a.*, b.* FROM
  • Main table aliases referenced in sub-queries do not work (due to a Snowflake limitation). For example, the t.* below does not work
    SELECT EXISTS(SELECT t.* FROM okera.tpch_test_snowflake.region)
    FROM okera.tpch_test_snowflake.nation t