Snowflake Data Share

Introduction

Snowflake allows you to share a databases, schemas, tables and views with Kubit through a Secure Data Share . With this approach Kubit acts as a Consumer Account and can query data that has been imported with the Provider Account, but cannot perform any of the DML tasks that are allowed in a full account, such as data loading, insert, update, and similar data manipulation operations.

Steps

Requirements

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 account does not match any combination in the table below we can provision a new matching account in a matter of hours.

1. Preparation

Identify which resources will be shared with Kubit.

• Databases and Schemas

• Tables

• (Secure) Views

2. Create a Data Share

Here is an example script (sharing to nk15162 on US West Coast)

SQLTerraform

use role accountadmin;
create share <data_share>;

3. Grant Usage Permissions

SQL

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

📘 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

Best Practices

Clustering Keys

1. Kubit works with time-series data so clustering by date of the fact tables is essential for performance for tables bigger than 1TB.

2. Since most Kubit reports also filter by event name it is beneficial to add the event name as a second cluster column.

Here's Snowflake's reference for Clustering Keys .

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:

SQL

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:

SQL

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:

SQL

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:

SQL

SHOW GRANTS ON VIEW <view_name>;

Avoid select * in shared view definitions

Using select * in your view definitions may lead to query failures once the references table schema changes. Here's a typical error message:

View definition for 'Database.Schema.Resource_Name' declared X column(s), but view query produces Y column(s).

Please do not use select * in shared view definition in order to prevent such errors.