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.
You will need to input these properties:
- Account: Your Snowflake account name can be found in your Snowflake url
- 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.
CREATE DATACONNECTION snowflake_connection CXNPROPERTIES ( 'connection_type'='JDBC', 'driver'='snowflake', 'host'='my-host.com', 'port'='3306', 'user'='awsps:///my-username', 'password'='awsps:///my-password', 'jdbc.db.name'='OKERA_DEMO', 'jdbc.schema.name'='marketing' -- optional, depends on engine )
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:
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.
Supported privacy functions¶
We broadly support
zero as privacy functions for snowflake pushdown.
You will need to run the script we provide to ensure these functions are installed in Snowflake.
These functions should not be shared with end users and only a Snowflake admin should create/use them.
mask_ccnonly supported for
tokenizepreserves format and referential integrity for
DATEsupport yet to be implemented for all the functions
Known SQL incompatibilities:
TRY_CASTfunctions not supported yet.
LIMIT ALLnot 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