Snowflake

Introduction

Snowflake allows you to share a databases, schemas, tables and views with Kubit through a Secure Data Share.

Steps

1. Create the view: or select which databases, tables and/or schema
2. Create a share: give it a name
3. Grant permissions: grant usage permissions
4. Share it with Kubit's Snowflake account depending on your Snowflake region (please confirm with Kubit support first)

Note that both the provider and consumer accounts of the Secure Data Share need to be on the same cloud infrastructure and in the same region. In case your Snowflake is elsewhere we can provision a new matching account in a matter of hours. Here is an example script (sharing to nk15162 on US West Coast):

use role accountadmin;
create share <data_share>;
grant usage on database <DB> to share <data_share>;
grant usage on schema <SCHEMA> to share <data_share>;
grant select on all tables in schema <SCHEMA> to share <data_share>;
grant select on view <VIEW> to share <data_share>;
alter share data share set accounts = nk15162; -- use Account Locator

resource "snowflake_share" "kubit" {
  name     = "kubit_share"
  comment  = "Shared with Kubit"
  accounts = ["KUBIT_EUROPE_CENTRAL_1_AWS"] // use Account Name
}

Snowflake also offers a web interface to review and manage data shares. For details on this topic, please review Snowflake’s documentation here. Please also note, the following cases which require additional configuration. Here is how to:

• Mask sensitive data during sharing
• Share data from multiple databases

👍

Query Performance

It is strongly recommended to use the proper Clustering Keys in your database tables to improve query performance. Typically at least the event date column and event name should be part of the clustering key since they are part of the filters in most queries.

Benefits

With Secure Data Share, no actual data is copied or transferred between accounts. All sharing is accomplished through Snowflake’s unique services layer and metadata store. This is an important concept because it means that shared data does not take up any extra storage for either the Provider or Consumer. Since the same underlying data is being shared, the data is guaranteed to be live and without delay.

More importantly, the computing environment is completely separated. The Consumer (Kubit) must create and use their own Virtual Data Warehouse and pay for it themselves. There is absolutely no impact on the Provider’s cost, performance or SLA for their Snowflake services.

Additionally, using Snowflake’s built-in roles and permissions capabilities, access to data share can be controlled and governed using the access controls already in place for your Snowflake account and the data therein. Access can be restricted and monitored the same exact way as your own data.

Snowflake Secure Data Share allows your data engineers to share a database, schema, table or view to Kubit with read-only permission and real-time live data. A view can be used to join multiple tables across different databases and add constraints to expose which columns. You are the Provider while Kubit is the Consumer of the share.

Troubleshooting

A view or function being shared cannot reference objects from other databases.

The error could be due:

1. A missing privilege "REFERENCE_USAGE" on the database where the objects reside which is referenced in the view. Solution:

grant reference_usage on database <database_name> to share <data_share>;

2. An object from a database on top of an INBOUND share can not be directly added or referenced to an object in the OUTBOUND share. There is no solution to this limitation - you can't share an INBOUND share directly. You have to copy the data into a table in order to use it in an OUTBOUND Share.
3. If the referenced object or the shared object in the OUTBOUND share has a making policy applied, where the masking policy resides in a database other than the shared object. Solution:

grant reference_usage on database <masking_policy_database> to share <data_share>;

4. You're referencing more than one database in your OUTBOUND share. You can review all the references with the following function:

select * from table(get_object_references(database_name=>'DB', schema_name=>'SCHEMA', object_name=>'VIEW'));

Solution: Please verify the required USAGE privileges were granted. See step 3 of this guide.

Recreate a shared view

When you recreate a view, even with the CREATE OR REPLACE statement, existing grants might be lost. Please, make sure to include the COPY GRANTS parameter. Once the new view is created you can review its grants with the following command:

SHOW GRANTS ON VIEW <view_name>;

Please do not use select * in shared view definitions

Using select * in your view definitions may lead to query failures once the references table schema changes. Please do not use select * in shared view definition in order to prevent such errors.