Skip to content

Use Snowflake BI Gateway Enforcement

Important

This method of enforcement is not supported for SaaS customers.

Okera's BI Gateway (pushdown processing) enforcement mechanism pushes down full queries (including joins and aggregations) to Snowflake, while enforcing the complete access policy, as well as audit log entries. Note that this enforcement mechanism 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 using Okera’s ODBC/JDBC endpoint, and their queries are transparently sent to Snowflake, after being authorized and audited by Okera. If the user does not have permission to access the data they are trying to access, the query is rejected. With this enforcement method, 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., Amazon 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

Select Use of the BI Gateway Enforcement Mechanism

To select the BI Gateway enforcement mechanism for Snowflake, okera.policy_sync.enabled=false must be specified as an advanced option in Okera's connection definition for Snowflake. See Create a Snowflake Connection.

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 administrator should create or use them.

Data Type Limitations

  • mask, mask_ccn are 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 are not supported.
  • LIMIT ALL functions are not supported in Snowflake.
  • Wildcards from two tables that have the same column name cause errors. For example, if both a and b have same column names in each, the following query causes a duplicate column error: SELECT a.*, b.* FROM
  • Main table aliases referenced in subqueries 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